What this book covers
Chapter 1, First Steps, introduces you to PostgreSQL 16; it explains how to download and install PostgreSQL 16, connect to a PostgreSQL server, enable server access to the network or remote users, use graphical administration tools or the psql query and scripting tool, change your password securely, avoid hardcoding your password, use a connection service file, and troubleshoot a failed connection. This chapter also covers how to use PostgreSQL in the cloud, including Kubernetes, and with TPA (Trusted Postgres Architect).
Chapter 2, Exploring the Database, shows how to identify the version of the database server you are using, and the server uptime. It helps you locate the database server files, the database server message log, and the database’s system identifier. It explains how to list the databases on the database server, and it contains recipes to find out the number of tables in your database, how much memory and disk space is used by each database and table, what the biggest tables are, how many rows a table has, both exactly and in a quicker estimate, and how to understand object dependencies.
Chapter 3, Server Configuration, starts by showing where the documentation is; then it discusses how to plan a new database and how to view and change the settings of the database server in various ways: at the global level, at the database level, from within the application, in a session, and depending on what user is logged in. The chapter ends with three recipes on how to add, use, and manage PostgreSQL extensions.
Chapter 4, Server Control, includes recipes on how to start and stop the database server manually, and how to reload the server configuration. Regarding connections, we show how to prevent new ones, limit them per user, and terminate them to push a given user off the system. The chapter ends with some recipes on topics that are pertinent to scalability and resource allocation, such as the various multi-tenancy options based on separate instances, databases, or schemas, and setting up a connection pool using one or more PgBouncer instances.
Chapter 5, Tables and Data, begins with two recipes on database object names, followed by twelve recipes with solutions of practical problems such as duplicate rows, finding good candidates for unique constraints, generating test data and sample data, loading data from spreadsheets or flat files, applying large data changes, using partitioning to handle large data, consolidating data with the MERGE statement, and properly using JSON data types.
Chapter 6, Security, provides a security overview, followed by recipes on the PostgreSQL superuser, revoking user access to a table and granting user access to a table and to specific columns or rows.
We then discuss creating a new user, temporarily preventing a user from connecting, removing a user without dropping their data, checking whether all users have a secure password, giving limited superuser powers to specific users, auditing changes, knowing who is currently connected, integrating with LDAP, connecting using SSL, encrypting sensitive data, and setting up cloud security.
Chapter 7, Database Administration, starts with recipes on writing scripts where all commands either succeed or fail, or exit on the first error, use psql variables to store data or to conditionally alter the flow of the script, and set up a more useful prompt. We then see how to schedule maintenance jobs, perform actions on many tables, add and remove columns in tables, change the data type of a column, or the definition of an enumerative type, add constraints concurrently, add and remove schemas or tablespaces, and move objects between them, access objects in other databases, not only running PostgreSQL, enabling data updates on views, use materialized views, generated data columns, and data compression.
Chapter 8, Monitoring and Diagnosis, begins with an overview of monitoring, including on the cloud, and how to fetch relevant monitoring data from PostgreSQL, including from the logs. It provides recipes that answer questions such as whether a user is connected, what they are running, whether they are active or blocked, who they are being blocked by, whether anybody is using a specific table, when the table was last used, how much disk space is being used by temporary data and whether it is still in use or not, and why your queries could be slowing down. It also demonstrates how to monitor the progress of commands, monitor the PostgreSQL log, and produce a daily summary report, monitor PostgreSQL I/O statistics, kill a specific session, kill idle in-transaction sessions, and analyze the performance of your queries and track important metrics over time.
Chapter 9, Regular Maintenance, provides useful recipes on how to control automatic database maintenance, avoid auto-freezing, avoid transaction wraparound, offer solutions for heavy users of temporary tables, identify and fix bloated tables and indexes, maintain indexes, find unused indexes, carefully remove unwanted indexes, and plan maintenance.
Chapter 10, Performance and Concurrency, covers topics such as how to find slow SQL statements, collect regular statistics from pg_stat* views, discover what makes SQL slow, reduce the number of rows returned, simplify complex SQL, and speed up queries without rewriting them. It also delves into understanding why some queries do not use an index, how to force a query to use an index, and how to reap the benefits of parallel queries, understand JIT, use optimistic locking, and report performance problems. Additionally, you will learn about the new parallel query features, TABLESAMPLE and time-series partitioning.
Chapter 11, Backup and Recovery, provides useful information about the backup and recovery of your PostgreSQL database through recipes on how to understand and control crash recovery and how to plan backups. Additionally, you will learn about the hot logical backup of one database, the hot logical backup of all databases, the hot logical backup of all tables in a tablespace, the backup of database object definitions, the standalone hot physical database backup, the hot physical backup, and continuous archiving. It also includes topics such as the recovery of all databases, recovery to a point in time, the recovery of a dropped or damaged table, the recovery of a dropped or damaged database, the recovery of a dropped or damaged tablespace, how to improve the performance of backup/recovery, and incremental/differential backup and restore.
Chapter 12, Replication and Upgrades, explains that replication isn’t magic, although it can be pretty cool. It’s even cooler when it works, and that’s what this chapter is all about. This chapter covers replication concepts such as replication best practices, how to set up streaming log replication, both physical and logical, how to manage hot standby, synchronous replication, how to upgrade to a new minor release, in-place major upgrades, major upgrades online, setting up replication using the CloudNativePG Kubernetes operator and Trusted Postgres Architect (TPA), and Postgres Distributed with multiple writable nodes.
