book

Learn PostgreSQL - Second Edition

by Luca Ferrari, Enrico Pirozzi

October 2023

Intermediate to advanced

744 pages

15h 51m

English

Packt Publishing

Read now

Unlock full access

Who this book is forWhat this book coversTo get the most out of this bookGet in touch
Technical requirementsPostgreSQL at a glanceA brief history of PostgreSQLWhat’s new in PostgreSQL 16?PostgreSQL release policy, version numbers, and life cycleExploring PostgreSQL terminologyInstalling PostgreSQLWhat to installInstalling PostgreSQL from binary packagesUsing the book’s Docker imagesInstalling PostgreSQL on GNU/Linux Debian, Ubuntu, and derivativesInstalling PostgreSQL on Fedora LinuxInstalling PostgreSQL on FreeBSDInstalling PostgreSQL from sourcesInstalling PostgreSQL via pgenvSummaryReferencesLearn more on Discord
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 parametersSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsIntroduction 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 rolesInspecting pg_hba.conf rulesIncluding other files in pg_hba.confSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsUsing the Docker imageConnecting the databaseCreating and managing databasesCreating a databaseManaging databasesIntroducing schemasPostgreSQL and the public schemaThe search_path variableThe correct way to start workingListing all tablesMaking a new database from a modified templateDropping tables and databasesDropping tablesDropping databasesMaking a database copyConfirming the database sizeThe psql methodThe SQL methodBehind the scenes of database creationManaging 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 dataSummaryVerify your knowledge ReferencesLearn more on Discord
Technical requirementsExploring the SELECT statementUsing the like clauseUsing ilikeUsing distinctUsing limit and offsetUsing subqueriesSubqueries and the IN/NOT IN conditionSubqueries and the EXISTS/NOT EXISTS conditionLearning about joinsUsing INNER JOININNER JOIN versus EXISTS/INUsing LEFT JOINSUsing RIGHT JOINUsing FULL OUTER JOINUsing LATERAL JOINAggregate functionsUNION/UNION ALLEXCEPT/INTERSECTUsing UPSERTUPSERT – the PostgreSQL wayLearning the RETURNING clause for INSERTReturning tuples out of queriesUPDATE related to multiple recordsMERGEExploring UPDATE ... RETURNINGExploring DELETE ... RETURNINGExploring CTEsCTE conceptCTE in PostgreSQL since version 12CTE – use casesQuery recursionRecursive CTEsSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsUsing basic statement window functionsUsing the PARTITION BY function and WINDOW clauseIntroducing some useful functionsThe ROW_NUMBER functionThe ORDER BY clauseFIRST_VALUELAST_VALUERANKDENSE_RANKThe 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_pointSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsExploring 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 overviewDropping functionsDeclaring function parametersIN/OUT parametersFunction volatility categoriesControl structureConditional statementsIF statementsCASE statementsLoop statementsThe record typeException handling statementsSecurity definerSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsExploring 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 eventsINSERT rulesDELETE rulesUPDATE rulesManaging triggers in PostgreSQLTrigger syntaxTriggers on INSERTThe TG_OP variableTriggers on UPDATE / DELETEEvent triggersAn example of an event triggerSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsBasic conceptsRange partitioningList partitioningHash partitioningTable inheritanceDropping tablesExploring declarative partitioningList partitioningRange partitioningPartition maintenanceAttaching a new partitionDetaching an existing partitionAttaching an existing table to the parent tableThe default partitionPartitioning and tablespacesA simple case studySummaryVerify your knowledgeReferencesLearn more on Discord

Technical requirementsUnderstanding rolesProperties related to new objectsProperties related to superusersProperties related to replicationProperties related to RLSChanging 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 overviewACLsDefault ACLsKnowing the default ACLsGranting and revoking permissionsPermissions related to tablesColumn-based permissionsPermissions related to sequencesPermissions related to schemasALL objects in the schemaPermissions related to programming languagesPermissions related to routinesPermissions related to databasesOther GRANT and REVOKE statementsAssigning the object ownerInspecting ACLsRLSRole password encryptionSSL connectionsConfiguring the cluster for SSLConnecting to the cluster via SSLSummaryVerify your knowledgeReferencesLearn more on Discord
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: WALsWALsWALs as a rescue method in the event of a crashCheckpointsCheckpoint configuration parameterscheckpoint_timeout and max_wal_sizeCheckpoint throttlingManually issuing a checkpointVACUUMManual VACUUMAutomatic VACUUMSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsIntroducing 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 derivativesInstalling pgxnclient on Fedora Linux and Red Hat-based distributionsInstalling 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 pgxnclientRemoving a manually compiled extensionCreating your own extensionDefining an example extensionCreating extension filesInstalling the extensionCreating an extension upgradePerforming an extension upgradeSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsExecution of a statementExecution stagesThe optimizerNodes that the optimizer usesSequential nodesParallel nodesWhen 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 optionsExamples of query tuningANALYZE and how to update statisticsAuto-explainSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsIntroduction to loggingWhere to logWhen to logWhat to logExtracting information from logs – pgBadgerInstalling pgBadgerConfiguring PostgreSQL logging for pgBadger usageUsing pgBadgerScheduling pgBadgerImplementing auditingInstalling PgAuditConfiguring PostgreSQL to exploit PgAuditConfiguring PgAuditAuditing by sessionAuditing by roleSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsIntroducing types of backups and restoresExploring logical backupsDumping a single databaseRestoring a single databaseLimiting the amount of data to backupCompressionDump formats and pg_restorePerforming a selective restoreDumping a whole clusterParallel backupsBackup automationThe COPY commandExploring physical backupsPerforming a manual physical backuppg_verifybackupStarting the cloned clusterRestoring from a physical backupBasic concepts behind PITRSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsCluster configurationInspecting all the configuration parametersFinding configuration errorsNesting configuration filesConfiguration contextsMain configuration settingsWAL settingsMemory-related settingsProcess information settingsNetworking-related settingsArchive and replication settingsVacuum and autovacuum-related settingsOptimizer settingsStatistics collectorModifying the configuration from a live systemConfiguration generatorsMonitoring the clusterInformation about running queries and sessionsInspecting 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_statementsSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsExploring basic replication conceptsPhysical replication and WALsThe wal_level directivePreparing the environment setup for streaming replicationManaging streaming replicationBasic concepts of streaming replicationAsynchronous replication environmentThe wal_keep_segments optionThe slot wayThe pg_basebackup commandAsynchronous replicationReplica monitoringSynchronous replicationPostgreSQL settingsCascading replicationDelayed replicationPromoting a replica server to a primarySummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsUnderstanding the basic concepts of logical replicationComparing logical replication and physical replicationExploring a logical replication setup and new logical replication features on PostgreSQL 16Logical replication environment settingsThe replica rolePrimary server – postgresql.confReplica server – postgresql.confThe pg_hba.conf fileLogical replication setupMonitoring logical replicationRead-only versus write-allowedDDL commandsDisabling logical replicationMaking a logical replication using a physical replication instanceSummaryVerify your knowledgeReferencesLearn more on Discord
Technical requirementsExploring the pg_trgm extensionUsing foreign data wrappers and the postgres_fdw extensionDisaster recovery with pgbackrestBasic conceptsEnvironment set upThe exchange of public keysInstalling pgbackrestConfiguring pgbackrestThe repository configurationUsing pgbackrest with object store supportThe PostgreSQL server configurationThe postgresql.conf fileThe pgbackrest.conf fileCreating and managing continuous backupsCreating the stanzaChecking the stanzaManaging base backupsManaging PITRMigrating from MySQL/MariaDB to PostgreSQL using pgloaderSummaryVerify your knowledgeReferences

Overview

Learn PostgreSQL, a comprehensive guide to mastering PostgreSQL 16, takes readers on a journey from the fundamentals to advanced concepts, such as replication and database optimization. With hands-on exercises and practical examples, this book provides all you need to confidently use, manage, and build secure and scalable databases.

What this Book will help me do

Master the essentials of PostgreSQL 16, including advanced SQL features and performance tuning.
Understand database replication methods and manage a scalable architecture.
Enhance database security through roles, schemas, and strict privilege management.
Learn how to personalize your experience with custom extensions and functions.
Acquire practical skills in backup, restoration, and disaster recovery planning.

Author(s)

Luca Ferrari and Enrico Pirozzi are experienced database engineers and PostgreSQL enthusiasts with years of experience using and teaching PostgreSQL technology. They specialize in creating learning content that is practical and focused on real-world situations. Their writing emphasizes clarity and systematically equips readers with professional skills.

Who is it for?

This book is perfect for database professionals, software developers, and system administrators looking to develop their PostgreSQL expertise. Beginners with an interest in databases will also find this book highly approachable. Ideal for readers seeking to improve their database scalability and robustness. If you aim to hone practical PostgreSQL skills, this guide is essential.