What this book covers
Chapter 1, First Steps, introduces you to PostgreSQL 14; it explains how to download and install PostgreSQL 14, connect to a PostgreSQL server, enable server access to the network or remote users, use graphical administration tools, use PSQL query and scripting tools, change your password securely, avoid hardcoding your password, use a connection service file, and troubleshoot a failed connection. This chapter also covers how to access PostgreSQL in the cloud.
Chapter 2, Exploring the Database, demonstrates how to identify the version of the database server you are using, as well as 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 a database on the database server, and it contains recipes that let you know the number of tables in your database, how much disk space is used by the database and tables, what the biggest tables are, how many rows a table has, how to estimate rows in a table, and how to understand object dependencies.
Chapter 3, Server Configuration, explains topics such as Reading the Fine Manual (RTFM), how to plan a new database, how to change the parameters in your programs, the current configuration settings, the parameters that are at non-default settings, how to update the parameter file, how to set parameters for particular groups of users, the basic server configuration checklist, how to add an external module into the PostgreSQL server, and how to run the server in power-saving mode.
Chapter 4, Server Control, provides information about starting the database server manually, stopping the server quickly and safely, stopping the server in an emergency, reloading the server configuration files, restarting the server quickly, preventing new connections, restricting users to just one session each, and pushing users off the system. It contains recipes that help you choose a design for multi-tenancy, as well as recipes that explain how to use multiple schemas, give users their own private database, run multiple database servers on one system, and set up a connection pool.
Chapter 5, Tables and Data, guides you through the process of choosing good names for database objects. Additionally, it explains how to handle objects with quoted names, enforce the same name, maintain the same definition for columns, identify and remove duplicate rows, prevent duplicate rows, find a unique key for a set of data, generate test data, randomly sample data, load data from a spreadsheet, and load data from flat files.
Chapter 6, Security, provides recipes on revoking user access to a table, granting user access to a table, 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 DDL changes, auditing data changes, integrating with LDAP, connecting using SSL, and encrypting sensitive data.
Chapter 7, Database Administration, provides recipes on useful topics such as writing a script where all either succeed or fail, writing a psql script that exits on the first error, performing actions on many tables, adding and removing columns in tables, changing the data type of a column, adding and removing schemas, moving objects between schemas, adding and removing tablespaces, moving objects between tablespaces, accessing objects in other PostgreSQL databases, and enabling views to be updated.
Chapter 8, Monitoring and Diagnosis, provides recipes that answer questions such as whether the 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 why your queries could be slowing down. It also demonstrates how to investigate and report a bug, produce a daily summary report of log file errors, kill a specific session, and resolve an in-doubt prepared transaction.
Chapter 9, Regular Maintenance, provides useful recipes on how to control automatic database maintenance, avoid auto-freezing and page corruptions, avoid transaction wraparound, remove old prepared transactions, 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, speed up queries without rewriting them, understand why some queries are not using an index, force a query to use an index, use optimistic locking, and report performance problems. And, of course, you'll learn about the new parallel query features, tablesample, and time-series partitioning.
Chapter 11, Backup and Recovery, explains that backups are essential, although this topic is only covered very briefly. So, this chapter 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, replication best practices, how to set up file-based log shipping replication, how to set up streaming log replication, how to manage log shipping replication, how to manage hot standby, synchronous replication, how to upgrade to a new minor release, in-place major upgrades, major upgrades online, and logical replication and Postgres-BDR.