book

Learn PostgreSQL

by Luca Ferrari, Enrico Pirozzi

October 2020

Beginner

650 pages

13h 25m

English

Packt Publishing

Read now

Unlock full access

Learn PostgreSQL
Why subscribe?
About the authorsAbout the reviewersPackt is searching for authors like you
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
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
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
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

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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Introducing PostgreSQL 13's new featuresReplicationAdministrationpsqlPerformanceBackup toolsUpgrading to PostgreSQL 13SummaryReferences
Leave a review - let other readers know what you think

Overview

Dive into the world of PostgreSQL, one of the most powerful and versatile open-source relational databases! This book guides you through all the essentials of PostgreSQL version 12 and 13, from installation to high-performance database deployments. You'll learn how to design schemas, perform database operations efficiently, and implement advanced functionalities.

What this Book will help me do

Install, configure, and monitor a PostgreSQL server for optimal performance.
Implement SQL and PL/pgSQL scripts to build complex database solutions.
Analyze and optimize database schemas and indexes for efficiency.
Secure a PostgreSQL database and manage roles and permissions effectively.
Set up high-availability configurations through replication techniques.

Author(s)

None Ferrari and Enrico Pirozzi are seasoned database professionals with extensive experience in PostgreSQL. They bring practical expertise and a real-world perspective to the subject, ensuring you get hands-on knowledge and apply it effectively. Their approachable writing style simplifies even the most complex database concepts.

Who is it for?

This book is perfect for database professionals, developers, or tech enthusiasts looking to gain mastery over PostgreSQL. Whether you are new to PostgreSQL or have a fundamental understanding of databases, you'll find this book highly insightful in achieving your database management goals.