book

Learn PostgreSQL

Name: Learn PostgreSQL
ISBN: 9781838985288

by Luca Ferrari, Enrico Pirozzi

October 2020

Beginner

650 pages

13h 25m

English

Packt Publishing

Read now

Unlock full access

Title Page
Copyright and Credits
Learn PostgreSQL
Dedication
About Packt
Why subscribe?
Contributors
About the authorsAbout the reviewersPackt is searching for authors like you
Preface
Who this book is forWhat this book coversTo get the most out of this bookDownload the example code filesDownload the color imagesConventions usedGet in touchReviews
Section 1: Getting Started
Introduction to PostgreSQL
Technical requirementsPostgreSQL at a glanceA brief history of PostgreSQLWhat's new in PostgreSQL 12?What's new in PostgreSQL 13?PostgreSQL release policy, version numbers, and life cycleExploring PostgreSQL terminologyInstalling PostgreSQL 12 or higherWhat to installInstalling PostgreSQL 12 from binary packagesInstalling PostgreSQL 12 on GNU/Linux Debian, Ubuntu, and derivativesInstalling PostgreSQL 12 on Linux FedoraInstalling PostgreSQL 12 on FreeBSDInstalling PostgreSQL from sourcesInstalling PostgreSQL via pgenvSummaryReferences
Getting to Know Your Cluster
Technical requirementsManaging your clusterpg_ctlPostgreSQL processesConnecting to the clusterThe template databasesThe psql command-line clientEntering SQL statements via psqlA glance at the psql commandsIntroducing the connection stringSolving common connection problemsDatabase "foo" does not existConnection refusedNo pg_hba.conf entryExploring the disk layout of PGDATAObjects in the PGDATA directoryTablespacesExploring configuration files and parametersSummaryReferences
Managing Users and Connections
Introduction to users and groupsManaging rolesCreating new rolesRole passwords, connections, and availabilityUsing a role as a groupRemoving an existing roleInspecting existing rolesManaging incoming connections at the role levelThe syntax of pg_hba.confOrder of rules in pg_hba.confMerging multiple rules into a single oneUsing groups instead of single rolesUsing files instead of single rolesSummaryReferences

Section 2: Interacting with the Database
Basic Statements
Technical requirementsSetting up our developing environmentCreating and managing databasesCreating a databaseManaging databasesListing all databasesMaking a new database from a modified templateDropping tables and databasesDropping tablesDropping databasesMaking a database copyConfirming the database sizeThe psql methodThe SQL methodCreating a databaseManaging tablesThe EXISTS optionManaging temporary tablesManaging unlogged tablesCreating a tableUnderstanding basic table manipulation statementsInserting and selecting dataNULL valuesSorting with NULL valuesCreating a table starting from another tableUpdating dataDeleting dataSummaryReferences
Advanced Statements
Exploring the SELECT statementUsing the like clauseUsing ilikeUsing distinctUsing limit and offsetUsing subqueriesUsing the IN/NOT IN conditionUsing the EXISTS/NOT EXISTS conditionLearning joinsUsing INNER JOININNER JOIN versus EXISTS/INUsing LEFT JOINSUsing RIGHT JOINUsing FULL OUTER JOINUsing SELF JOINAggregate functionsUNION/UNION ALLEXCEPT/INTERSECTUsing UPSERTUPSERT – the PostgreSQL wayLearning the RETURNING clause for INSERTReturning tuples out of queriesUPDATE related to multiple tablesExploring UPDATE .... RETURNINGDELETE .... RETURNINGExploring CTEsCTE conceptCTE in PostgreSQL 12CTE – some examplesQuery recursionRecursive CTEsSummaryReferences
Window Functions
Using basic statement window functionsUsing the PARTITION BY function and WINDOW clauseIntroducing some useful functionsThe ROW_NUMBER functionThe ORDER BY clauseFIRST_VALUELAST_VALUERANKDENSE_RANK The LAG and LEAD functionsThe CUME_DIST functionThe NTILE functionUsing advanced statement window functionsThe frame clauseROWS BETWEEN start_point and end_pointRANGE BETWEEN start_point and end_pointSummaryReferences
Server-Side Programming
Exploring data typesThe concept of extensibilityStandard data typesBoolean data typeNumeric data typeInteger typesNumbers with a fixed precision data typeNumbers with an arbitrary precision data typeCharacter data typeChars with fixed-length data typesChars with variable length with a limit data typesChars with a variable length without a limit data typesDate/timestamp data typesDate data typesTimestamp data typesThe NoSQL data typeThe hstore data typeThe JSON data typeExploring functions and languagesFunctionsSQL functionsBasic functionsSQL functions returning a set of elementsSQL functions returning a tablePolymorphic SQL functionsPL/pgSQL functionsFirst overviewDeclaring function parametersIN/OUT parametersFunction volatility categoriesControl structureConditional statementsIF statementsCASE statementsLoop statementsThe record typeException handling statementsSummaryReferences
Triggers and Rules
Exploring rules in PostgreSQLUnderstanding the OLD and NEW variablesRules on INSERTThe ALSO optionThe INSTEAD OF optionRules on DELETE / UPDATECreating the new_tags tableCreating two tablesManaging rules on INSERT, DELETE, and UPDATE events INSERT rulesDELETE rulesUPDATE rulesManaging triggers in PostgreSQLTrigger syntaxTriggers on INSERTThe TG_OP variableTriggers on UPDATE / DELETEEvent triggersAn example of an event triggerSummaryReferences
Partitioning
Basic conceptsRange partitioningList partitioningHash partitioningTable inheritanceDropping tablesExploring partitioning using inheritanceAn example of list partitioningCreating tablesCreating triggers and functions, and inserting dataCreating triggers and functions and updating dataExploring declarative partitioningList partitioningRange partitioningPartition maintenanceSummaryReferences
Section 3: Administering the Cluster
Users, Roles, and Database Security
Understanding rolesProperties related to new objectsProperties related to superusersProperties related to replicationProperties related to row-level securityChanging properties of existing roles: the ALTER ROLE statementRenaming an existing roleSESSION_USER versus CURRENT_USERPer-role configuration parametersInspecting rolesRoles that inherit from other rolesUnderstanding how privileges are resolvedRole inheritance overviewAccess control listsDefault ACLsKnowing default ACLsGranting and revoking permissionsPermissions related to tablesColumn-based permissionsPermissions related to sequencesPermissions related to schemasALL objects in the schemaPermissions related to languagesPermissions related to routinesPermissions related to databasesOther GRANT and REVOKE statementsAssigning the object ownerInspecting ACLsRow-level securityRole password encryptionSSL connectionsConfiguring the cluster for SSLConnecting to the cluster via SSLSummaryReferences
Transactions, MVCC, WALs, and Checkpoints
Technical requirementsIntroducing transactionsComparing implicit and explicit transactionsTime within transactionsMore about transaction identifiers – the XID wraparound problemVirtual and real transaction identifiersMulti-version concurrency controlTransaction isolation levelsRead uncommittedRead CommittedRepeatable ReadSerializableExplaining MVCCSavepointsDeadlocksHow PostgreSQL handles persistency and consistency: WALsWrite-Ahead Logs (WALs)WALs as a rescue method in the event of a crashCheckpointsCheckpoint configuration parameterscheckpoint_timeout and max_wal_sizeCheckpoint throttlingManually issuing a checkpointVACUUMManual VACUUMAutomatic VACUUMSummaryReferences
Extending the Database - the Extension Ecosystem
Introducing extensionsThe extension ecosystemExtension componentsThe control fileThe script fileManaging extensionsCreating an extensionViewing installed extensionsFinding out available extension versionsAltering an existing extensionRemoving an existing extensionExploring the PGXN clientInstalling pgxnclient on Debian GNU/Linux and derivatesInstalling pgxnclient on Fedora LinuxInstalling pgxnclient on FreeBSDInstalling pgxnclient from sourcesThe pgxnclient command-line interfaceInstalling extensionsInstalling the extension via pgxnclientInstalling the extension manuallyUsing the installed extensionRemoving an installed extensionRemoving an extension via pgxnclietRemoving a manually installed extension Creating your own extensionDefining an example extensionCreating extension filesInstalling the extensionCreating an extension upgradePerforming an extension upgradeSummaryReferences
Indexes and Performance Optimization
Technical requirementsExecution of a statementExecution stagesThe optimizerNodes that the optimizer usesSequential nodesSequential ScanIndex nodesJoin nodesParallel nodesGather nodesParallel scansParallel joinsParallel aggregationsWhen does the optimizer choose a parallel plan?Utility nodesNode costsIndexesIndex typesCreating an indexInspecting indexesDropping an indexInvalidating an indexRebuilding an indexThe EXPLAIN statementEXPLAIN output formatsEXPLAIN ANALYZEEXPLAIN optionsAn example of query tuningANALYZE and how to update statisticsAuto-explainSummaryReferences
Logging and Auditing
Technical requirementsIntroduction to loggingWhere to logWhen to logWhat to logA complete example of logging configurationExtracting information from logs – PgBadgerInstalling PgBadgerConfiguring PostgreSQL logging for PgBadger usageUsing PgBadgerScheduling PgBadgerImplementing auditingInstalling PgAuditConfiguring PostgreSQL to exploit PgAuditConfiguring PgAuditAuditing by sessionAuditing by roleSummaryReferences
Backup and Restore
Technical requirementsIntroducing various types of backups and restoresExploring logical backupsDumping a single databaseRestoring a single databaseLimiting the amount of data to back upDump formats and pg_restorePerforming a selective restoreDumping a whole clusterParallel backupsBackup automationExploring physical backupsPerforming a manual physical backuppg_verifybackupStarting the cloned clusterRestoring from a physical backupSummaryFurther reading
Configuration and Monitoring
Technical requirementsCluster configurationInspecting all the configuration parametersFinding configuration errorsNesting configuration filesConfiguration contextsMain configuration settingsWAL settingsMemory-related settingsProcess information settingsNetworking-related settingsArchive and replication settingsVacuum andautovacuum-related settingsOptimizer settingsStatistics collectorModifying the configuration from a live systemConfiguration generatorsMonitoring the clusterInformation about running queriesInspecting locksInspecting databasesInspecting tables and indexesMore statisticsAdvanced statistics with pg_stat_statementsInstalling the pg_stat_statements extensionUsing pg_stat_statementsResetting data collected from pg_stat_statementsTuning pg_stat_statementsSummaryFurther Reading
Section 4: Replication
Physical Replication
Exploring basic conceptsWALThe wal_level directivePreparing the environment setup for streaming replicationLearning WAL archiving and PITRPITR – the manual wayThe WAL archiveBasebackupRecoveryManaging streaming replicationBasic conceptReplication environmentThe wal_keep_segments optionThe slot wayThe pg_basebackup commandAsynchronous replicationReplica monitoringCascading replicationSynchronous replicationPostgreSQL settingsMaster serverStandby serverSummaryReferences
Logical Replication
Understanding basic conceptsComparing logical replication and physical replication Exploring logical replication setupLogical replication environment settingsThe replica roleMaster server – postgresql.confReplica server – postgresql.confThe pg_hba.conf fileLogical replication setupMonitoring logical replicationComparing physical replication and logical replicationSimulating on test versus bloating eliminationRead-only versus write allowedDDL commandsDisabling logical replicationSummaryReferences
Section 5: The PostegreSQL Ecosystem
Useful Tools and Extensions
Exploring the pg_trgm extensionUsing foreign data wrappers and the postgres_fdw extensionExploring the btree_gin extensionManaging the pgbackrest toolBasic conceptsEnvironment settingThe exchange of public keysInstalling pgbackrestConfiguring pgbackrestThe repository configuration The PostgreSQL server configurationThe postgresql.conf fileThe pgbackrest.conf fileCreating and managing continuous backupsCreating the stanzaChecking the stanzaManaging basebackupsManaging PITRSummaryReferences
Toward PostgreSQL 13
Introducing PostgreSQL 13's new featuresReplicationAdministrationpsqlPerformanceBackup toolsUpgrading to PostgreSQL 13SummaryReferences
Other Books You May Enjoy
Leave a review - let other readers know what you think

Content preview from Learn PostgreSQL

Section 3: Administering the Cluster

In this section, you will learn how to interact with the cluster in order to keep it under control, to tune it, and search for problems or bottlenecks. You will learn about various aspects such as user management, access control, transaction control, and various extensions that can be used with clusters.

This section contains the following chapters:

Chapter 10, Users, Roles, and Database Security
Chapter 11, Transactions, MVCC, WALs, and Checkpoints
Chapter 12, Extending the database: the extension ecosystem
Chapter 13, Indexes and Performance Optimization
Chapter 14, Logging and Auditing
Chapter 15, Backup and Restore
Chapter 16, Configuration and Monitoring

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781838985288

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Learn PostgreSQL

by Luca Ferrari, Enrico Pirozzi

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.