Book Image

PostgreSQL 11 Administration Cookbook

By : Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala
Book Image

PostgreSQL 11 Administration Cookbook

By: Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala

Overview of this book

PostgreSQL is a powerful, open source database management system with an enviable reputation for high performance and stability. With many new features in its arsenal, PostgreSQL 11 allows you to scale up your PostgreSQL infrastructure. This book takes a step-by-step, recipe-based approach to effective PostgreSQL administration. The book will introduce you to new features such as logical replication, native table partitioning, additional query parallelism, and much more to help you to understand and control, crash recovery and plan backups. You will learn how to tackle a variety of problems and pain points for any database administrator such as creating tables, managing views, improving performance, and securing your database. As you make steady progress, the book will draw attention to important topics such as monitoring roles, backup, and recovery of your PostgreSQL 11 database to help you understand roles and produce a summary of log files, ensuring high availability, concurrency, and replication. By the end of this book, you will have the necessary knowledge to manage your PostgreSQL 11 database efficiently.
Table of Contents (19 chapters)
Title Page
Copyright and Credits
About Packt


PostgreSQL is an advanced SQL database server; it is available on a wide range of platforms and is fast becoming one of the world's most popular server databases with an enviable reputation for performance, stability, and an enormous range of advanced features. PostgreSQL is one of the oldest open source projects; it is completely free to use and was developed by a diverse worldwide community. Most of all, it just works!

One of the clearest benefits of PostgreSQL is that it is open source, meaning that you have a permissive license to install, use, and distribute PostgreSQL without paying anyone any fees or royalties. Additionally, PostgreSQL is well known as a database that stays up for long periods, and requires little or no maintenance. Overall, PostgreSQL provides a very low total cost of ownership.

PostgreSQL 11 Administration Cookbook offers the information you need to manage your live production databases on PostgreSQL. The book contains direct insights into PostgreSQL replication and recovery features from the main author and the 2ndQuadrant team. This hands-on guide will assist developers who are working on live databases, and who are supporting web or enterprise software applications using Java, Python, Ruby, and .NET from any development framework. It's easy to manage your database when you've got PostgreSQL 11 Administration Cookbook at hand.

This practical guide gives you quick answers to common questions and problems, and builds on the author's experience as a trainer, user, and core developer of the PostgreSQL database server.

Each technical aspect is broken down into short recipes that demonstrate solutions with working code, and then explain how and why that works. The book is intended to be a desk reference for both new users and technical experts.

The book covers all the latest features available in PostgreSQL 11. Soon you will be running a smooth database with ease! 

Who this book is for

This book is for system administrators, database administrators, architects, developers, and anyone with an interest in planning or running a live production database. This book is most suited to those who have some technical experience.

What this book covers

Chapter 1, First Steps, introduces you PostgreSQL 11; it explains how to download and install PostgreSQL 11, 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.

Chapter 2Exploring 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 the biggest tables are, how many rows a table has, how to estimate rows in a table, and how to understand object dependencies.

Chapter 3Configuration, 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 4Server 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 5Tables 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 6Security, 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 7Database 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 8Monitoring 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 it 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 9Regular 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 10Performance 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.



Chapter 11Backup 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 12Replication 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.

To get the most out of this book

In order for this book to be useful, you need access to a PostgreSQL client that is allowed to execute queries on a server. Ideally, you'll also be the server administrator. Full client and server packages for PostgreSQL are available for most popular operating systems at All the examples here are executed at the Command Prompt, usually running the PSQL program. This makes them applicable to most platforms. It's straightforward to do most of these operations by using a GUI tool for PostgreSQL, such as pgAdmin or OmniDB:

  • pgAdmin:
  • OmniDB:

Download the color images

We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here:

Conventions used

There are a number of text conventions used throughout this book.

CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "Copy the data files (excluding the pg_wal directory)."

A block of code is set as follows:

       ENCRYPTED PASSWORD 'changeme';

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

SELECT *FROM mytable
 WHERE  (col1, col2, … ,colN) IN
 (SELECT col1, col2, … ,colN
  FROM mytable
  GROUP BY col1, col2, … ,colN
HAVING count(*) > 1);

Any command-line input or output is written as follows:

$ postgres --single -D /full/path/to/datadir postgres

Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "Select System info from the Administration panel."


Warnings or important notes appear like this.


Tips and tricks appear like this.


In this book, you will find several headings that appear frequently (Getting ready, How to do it..., How it works..., There's more..., and See also).

To give clear instructions on how to complete a recipe, use these sections as follows:

Getting ready

This section tells you what to expect in the recipe and describes how to set up any software or any preliminary settings required for the recipe.

How to do it…

This section contains the steps required to follow the recipe.

How it works…

This section usually consists of a detailed explanation of what happened in the previous section.

There's more…

This section consists of additional information about the recipe in order to make you more knowledgeable about the recipe.

See also

This section provides helpful links to other useful information for the recipe.

Get in touch

Feedback from our readers is always welcome.

General feedback: Email [email protected] and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at [email protected].



Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit, selecting your book, clicking on the Errata Submission Form link, and entering the details.

Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit


Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!

For more information about Packt, please visit