book

PostgreSQL 16 Administration Cookbook

Name: PostgreSQL 16 Administration Cookbook
ISBN: 9781835460580

by Gianni Ciolli, Boriss Mejías, Jimmy Angelakos, Vibhor Kumar, Simon Riggs

December 2023

Intermediate to advanced

636 pages

14h 44m

English

Packt Publishing

Read now

Unlock full access

Preface
Who this book is forWhat this book coversTo get the most out of this bookGet in touch
First Steps
Introducing PostgreSQL 16What makes PostgreSQL different?RobustnessSecurityEase of useExtensibilityPerformance and concurrencyScalabilitySQL and NoSQL data modelsPopularityCommercial supportResearch and development fundingHow to get PostgreSQLHow to do it...How it works...There’s more…Connecting to the PostgreSQL serverGetting readyHow to do it...How it works…There’s more…See alsoEnabling access for network/remote usersHow to do it…How it works…There’s more…See alsoUsing the pgAdmin 4 GUI toolHow to do it…How it works…See alsoUsing the psql query and scripting toolGetting readyHow to do it…How it works…There’s more…See alsoChanging your password securelyHow to do it…How it works…Avoiding hardcoding your passwordGetting readyHow to do it…How it works…There’s more…Using a connection service fileHow to do it…How it works…There’s more...Troubleshooting a failed connectionHow to do it…There’s more…PostgreSQL in the cloudGetting readyHow to do it…How it works…There’s more…PostgreSQL with KubernetesGetting readyHow to do it...How it works…There’s more...PostgreSQL with TPAGetting readyHow to do it...There’s more
Exploring the Database
What type of server is this?How to do it…There’s more...What version is the server?How to do it…How it works…There’s more…What is the server uptime?How to do it…How it works…See alsoLocating the database server filesGetting readyHow to do it...How it works...There’s more…Locating the database server’s message logGetting readyHow to do it...How it works...There’s more...Locating the database’s system identifierGetting readyHow to do it…How it works…Listing databases on the database serverHow to do it…How it works...There’s more...How many tables are there in a database?How to do it...How it works…There’s more…How much disk space does a database use?How to do it...How it works...How much memory does a database currently use?How to do it…How it works…How much disk space does a table use?How to do it…How it works…There’s more…Which are my biggest tables?How to do it...How it works…How many rows are there in a table?How to do it…How it works...Quickly estimating the number of rows in a tableHow to do it…How it works…There’s more…Listing extensions in this databaseHow to do it…How it works…There’s more…See alsoUnderstanding object dependenciesGetting readyHow to do it…How it works…There’s more…
Server Configuration
Read the fine manual (RTFM)How to do it…How it works…There’s more…Planning a new databaseGetting readyHow to do it…How it works…There’s more…Setting the configuration parameters for the database serverGetting readyHow to do it…How it works…There’s more…Setting the configuration parameters in your programsHow to do it…How it works…There’s more…Finding the configuration settings for your sessionHow to do it…How it works…Finding parameters with non-default settingsHow to do it…How it works...There’s more...Setting parameters for particular groups of usersHow to do it…How it works…A basic server configuration checklistGetting readyHow to do it…There’s more…Adding an external module to PostgreSQLGetting readyHow to do it…Installing modules using a software installerInstalling modules from PGXNInstalling modules from source codeHow it works...Using an installed module/extensionGetting readyHow to do it…How it works...Managing installed extensionsHow to do it…How it works…There’s more…
Server Control
An overview of controlling the database serverStarting the database server manuallyGetting readyHow to do it…How it works…Stopping the server safely and quicklyHow to do it…How it works…See alsoStopping the server in an emergencyHow to do it…How it works…Reloading server configuration filesHow to do it…How it works…There’s more…Restarting the server quicklyHow to do it…There’s more…Preventing new connectionsHow to do it…How it works…Restricting users to only one session eachHow to do it…How it works…Pushing users off the systemHow to do it…How it works…Deciding on a design for multitenancyHow to do it…How it works…Using multiple schemasGetting readyHow to do it…How it works…Giving users their own private databasesGetting readyHow to do it…How it works…There’s more…See alsoRunning multiple servers on one systemGetting readyHow to do it…How it works…Setting up a connection poolGetting readyHow to do it…How it works…There’s more…Accessing multiple servers using the same host and portGetting readyHow to do it…There’s more…Running multiple PgBouncer on the same port to leverage multiple coresGetting readyHow to do it…How it works…
Tables and Data
Choosing good names for database objectsGetting readyHow to do it…There’s more…Handling objects with quoted namesGetting readyHow to do it...How it works…There’s more…Identifying and removing duplicatesGetting readyHow to do it…How it works…There’s more…Preventing duplicate rowsGetting readyHow to do it…How it works…There’s more...Duplicate indexesUniqueness without indexesA real-world example – IP address range allocationA real-world example – a range of timeFinding a unique key for a set of dataGetting readyHow to do it…How it works…Generating test dataHow to do it...How it works…There’s more…See alsoRandomly sampling dataHow to do it…How it works...Loading data from a spreadsheetGetting readyHow to do it...How it works...There’s more...Loading data from flat filesGetting readyHow to do it...How it works…There’s more…Making bulk data changes using server-side procedures with transactionsGetting readyHow to do it…There’s more…Dealing with large tables with table partitioningHow to do it…How it works…There’s more…Finding good candidates for partition keysGetting readyHow to do it…There’s more…Consolidating data with MERGEGetting readyHow to do it…There’s more...Deciding when to use JSON data typesGetting readyHow to do it…Example: moving sparse columns to JSONExample: expose JSON data using a viewThere’s more…
Security
An overview of PostgreSQL securityTypical user rolesThe PostgreSQL superuserHow to do it…How it works…There’s more…Other superuser-like attributesSee alsoRevoking user access to tablesGetting readyHow to do it…How it works…There’s more…Database creation scriptsDefault search pathSecuring viewsGranting user access to a tableGetting readyHow to do it…How it works…There’s more…Granting user access to specific columnsGetting readyHow to do it…How it works…There’s more…Granting user access to specific rowsGetting readyHow to do it…How it works…There’s more…Creating a new userGetting readyHow to do it…How it works…There’s more…Temporarily preventing a user from connectingGetting readyHow to do it…How it works…There’s more…Limiting the number of concurrent connections by a userRevoking a user’s database accessHow it works…Forcing NOLOGIN users to disconnectRemoving a user without dropping their dataGetting readyHow to do it…How it works…Checking whether all users have a secure passwordHow to do it…How it works…Giving limited superuser powers to specific usersGetting readyHow to do it…Assigning backup privileges to a userHow it works…There’s more…Auditing database accessGetting readyAuditing accessAuditing SQL statementsAuditing table accessManaging the audit logAuditing data changesAlways knowing which user is logged inGetting readyHow to do it…How it works…There’s more…Not inheriting user attributesIntegrating with LDAPGetting readyHow to do it…How it works…There’s more…Setting up the client to use LDAPReplacement for the User Name Map featureSee alsoConnecting using encryption (SSL / GSSAPI)Getting readyHow to do it…How it works…There’s more…Getting the SSL key and certificateSetting up a client to use SSLChecking server authenticityUsing SSL certificates to authenticateGetting readyHow to do it…How it works…There’s more…Avoiding duplicate SSL connection attemptsUsing multiple client certificatesUsing the client certificate to select a database userSee alsoMapping external usernames to database rolesGetting readyHow to do it…How it works…There’s more…Using column-level encryptionGetting readyHow to do it…How it works…There’s more…For really sensitive dataFor really, really, really sensitive dataSee alsoSetting up cloud security using predefined rolesGetting readyHow to do it…How it works…There’s more…
Database Administration
Writing a script that either succeeds entirely or fails entirelyHow to do it…How it works…There’s more…Writing a psql script that exits on the first errorGetting readyHow to do it…How it works…There’s more…Using psql variablesGetting readyHow to do it…How it works…There’s more…Placing query output into psql variablesGetting readyHow to do it…How it works…There’s more…Writing a conditional psql scriptGetting readyHow to do it…How it works…There’s more…Investigating a psql errorGetting readyHow to do it…There’s more…Setting the psql prompt with useful informationGetting readyHow to do it…How it works…Using pgAdmin for DBA tasksGetting readyHow to do it…How it works...There’s more…Scheduling jobs for regular background executionGetting readyHow to do it…How it works…There’s more…Performing actions on many tablesGetting readyHow to do it…How it works…There’s more…Adding/removing columns on a tableHow to do it…How it works…There’s more…Changing the data type of a columnGetting readyHow to do it…How it works…There’s more…Changing the definition of an enum data typeGetting readyHow to do it…How it works…There’s more…Adding a constraint concurrentlyGetting readyHow to do it…How it works…There’s more…Adding/removing schemasHow to do it…There’s more…Using schema-level privilegesMoving objects between schemasHow to do it…How it works…There’s more…Adding/removing tablespacesGetting readyHow to do it…How it works…There’s more…Putting pg_wal on a separate deviceTablespace-level tuningMoving objects between tablespacesGetting readyHow to do it…How it works…There’s more…Accessing objects in other PostgreSQL databasesGetting readyHow to do it…How it works…There’s more…Accessing objects in other foreign databasesGetting readyHow to do it…How it works…There’s more…Making views updatableGetting readyHow to do it…How it works…There’s more…Using materialized viewsGetting readyHow to do it…How it works…There’s more…Using GENERATED data columnsHow to do it…How it works…There’s more…Using data compressionGetting readyHow to do it…How it works…There’s more…
Monitoring and Diagnosis
Cloud-native monitoringProviding PostgreSQL information to monitoring toolsFinding more information about generic monitoring toolsReal-time viewing using pgAdminGetting readyHow to do it…Monitoring the PostgreSQL message logGetting readyHow to do it…How it works…There’s more…Checking whether a user is connectedGetting readyHow to do it…How it works…There’s more…Checking whether a computer is connectedHow to do it…There’s more…Repeatedly executing a query in psqlHow to do it…There’s more…Checking which queries are runningGetting readyHow to do it…How it works…There’s more…Catching queries that only run for a few millisecondsWatching the longest queriesWatching queries from psSee alsoMonitoring the progress of commandsGetting readyHow to do it…How it works…There’s more…Checking which queries are active or blockedGetting readyHow to do it…How it works…There’s more…Knowing who is blocking a queryGetting readyHow to do it…How it works…Killing a specific sessionHow to do it…How it works…There’s more…Using statement_timeout to clean up queries that take too long to runKilling idle in-transaction sessionsKnowing whether anybody is using a specific tableGetting readyHow to do it…How it works…There’s more...Knowing when a table was last usedGetting readyHow to do it…How it works...Monitoring I/O statisticsGetting readyHow to do it...How it works...There’s more…Usage of disk space by temporary dataGetting readyHow to do it…How it works…There’s more…Finding out whether a temporary file is in use anymoreLogging temporary file usageUnderstanding why queries slow downGetting readyHow to do it…How it works…There’s more…Do queries return significantly more data than they did earlier?Do queries also run slowly when they run alone?Is the second run of the same query also slow?Table and index bloatSee alsoAnalyzing the real-time performance of your queriesGetting readyHow to do it…How it works…There’s more…Tracking important metrics over timeGetting readyHow to do it…How it works…There’s more…
Regular Maintenance
Controlling automatic database maintenance Getting readyHow to do it…How it works…There’s more…See alsoAvoiding auto-freezingHow to do it…Removing issues that cause bloatGetting readyHow to do it…How it works…There’s more…Actions for heavy users of temporary tablesHow to do it…How it works…Identifying and fixing bloated tables and indexesGetting readyHow to do it…How it works…There’s more…Monitoring and tuning a vacuumGetting readyHow to do it…How it works…There’s more…Maintaining indexesGetting readyHow to do it…How it works…There’s more…Finding unused indexesHow to do it…How it works…Carefully removing unwanted indexesGetting readyHow to do it…How it works…Planning maintenanceHow to do it…How it works…There’s more…

Performance and Concurrency
Finding slow SQL statementsGetting readyHow to do it…How it works…There’s more…Finding out what makes SQL slowGetting readyHow to do it…There’s more…Locking problemsEXPLAIN optionsNot enough CPU power or disk I/O capacity for the current loadSee alsoReducing the number of rows returnedHow to do it…There’s more…Simplifying complex SQL queriesGetting readyHow to do it…There’s more…Using materialized viewsUsing set-returning functions for some parts of queriesSpeeding up queries without rewriting themHow to do it…Increasing work_memSetting recursive_worktable_factorMore ideas with indexesThere’s more…Time-series partitioningUsing a view that contains TABLESAMPLEIn case of many updates, set fillfactor on the tableRewriting the schema – a more radical approachDiscovering why a query is not using an indexGetting readyHow to do it…How it works…There’s more…Forcing a query to use an indexGetting readyHow to do it…There’s more…Using parallel queryHow to do it…How it works…Using Just-In-Time (JIT) compilationGetting readyHow it works…Creating time-series tables using partitioningHow to do it…How it works…There’s more…Using optimistic locking to avoid long lock waitsHow to do it…How it works…There’s more…Reporting performance problemsHow to do it…There’s more…
Backup and Recovery
Understanding and controlling crash recoveryHow to do it…How it works…There’s more…Planning your backupsHow to do it…There’s more…Hot logical backup of one databaseHow to do it…How it works…There’s more…See alsoHot logical backup of all databasesHow to do it…How it works…See alsoBackup of database object definitionsHow to do it…There’s more…A standalone hot physical backupGetting readyHow to do it…How it works…There’s more…Hot physical backups with BarmanGetting readyHow to do it…How it works…There’s more…Recovery of all databasesGetting readyHow to do it…Logical – from the custom dump taken with pg_dump -F cLogical – from the script dump created by pg_dump -F pLogical – from the script dump created by pg_dumpallPhysical – from a standalone backupPhysical – with BarmanHow it works…There’s more…Recovery to a point in timeGetting readyHow to do it…How it works…There’s more…See alsoRecovery of a dropped/damaged tableHow to do it…Logical – from the custom dump taken with pg_dump -F cLogical – from the script dumpPhysicalHow it works…See alsoRecovery of a dropped/damaged databaseHow to do it…Logical – from the custom dump -F cLogical – from the script dump created by pg_dumpLogical – from the script dump created by pg_dumpallPhysicalExtracting a logical backup from a physical oneGetting readyHow to do it…There’s more…Improving the performance of logical backup/recoveryGetting readyHow to do it…How it works…There’s more…Improving the performance of physical backup/recoveryGetting readyHow to do it...How it works…There’s more…See alsoValidating backupsGetting readyHow to do it…How it works…There’s more…
Replication and Upgrades
Replication conceptsTopicsBasic conceptsHistory and scopePractical aspectsData lossSingle-master replicationMultinode architecturesMulti-master replicationOther approaches to replicationReplication best practicesGetting readyHow to do it…There’s more…Setting up streaming replicationGetting readyHow to do it…How it works…There’s more…Setting up streaming replication securityGetting readyHow to do it…How it works…There’s more…Hot Standby and read scalabilityGetting readyHow to do it…How it works…Managing streaming replicationGetting readyHow to do it…There’s more…See alsoUsing repmgrGetting readyHow to do it…How it works…There’s more…Using replication slotsGetting readyHow to do it…There’s more…See alsoSetting up replication with TPAGetting readyHow to do it…How it works…There’s more...Setting up replication with CloudNativePGGetting readyHow to do it…How it works…There’s more…Monitoring replicationGetting readyHow to do it…There’s more…Performance and synchronous replication (sync rep)Getting readyHow to do it...How it works…There’s more…Delaying, pausing, and synchronizing replicationGetting readyHow to do it…There’s more…See alsoLogical replicationGetting readyHow to do it…How it works…There’s more…EDB Postgres DistributedGetting readyHow to do it…How it works...There’s more…Archiving transaction log dataGetting readyHow to do it…There’s more...See alsoUpgrading minor releasesGetting readyHow to do it…How it works…There’s more…Major upgrades in-placeGetting readyHow to do it…How it works…There’s more...Major upgrades onlineHow to do it…How it works...
Other Books You May Enjoy
Index

Overview

This cookbook is a comprehensive guide to mastering PostgreSQL 16 database administration. With over 180 practical recipes, this book covers everything from query performance and backup strategies to replication and high availability. You'll gain hands-on expertise in solving real-world challenges while leveraging the new and improved features of PostgreSQL 16.

What this Book will help me do

Perform efficient batch processing with Postgres' SQL MERGE statement.
Implement parallel transaction processes using logical replication.
Enhance database backups and recovery with advanced compression techniques.
Monitor and fine-tune database performance for optimal operation.
Apply new PostgreSQL 16 features for secure and reliable databases.

Author(s)

The team of authors, including Gianni Ciolli, Boriss Mejías, Jimmy Angelakos, Vibhor Kumar, and Simon Riggs, bring years of experience in PostgreSQL database management and development. Their expertise spans professional system administration, academic research, and contributions to PostgreSQL development. Their collaborative insights enrich this comprehensive guide.

Who is it for?

This book is ideal for PostgreSQL database administrators seeking advanced techniques, data architects managing PostgreSQL in production, and developers interested in mastering PostgreSQL 16. Whether you're an experienced DBA upgrading to PostgreSQL 16 or a newcomer looking for practical recipes, this book provides valuable strategies and solutions.

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: 9781835460580

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills