How it works…

The pg_dump utility produces a single output file. This output file can use the split command to separate the file into multiple pieces, if required.

The pg_dump archive file, also known as the custom format, is lightly compressed by default. Compression can be removed or made more aggressive.

Even though, by default, pg_dump writes an SQL script directly to standard output, it is recommended to use the archive file instead, by enabling the custom format through the "-F c" option. As covered later in the chapter, backing up in the form of archive files gives you more flexibility and versatility when restoring. Archive files must be used with a tool called pg_restore.

The pg_dump utility runs by executing SQL statements against the database to unload data. When PostgreSQL runs an SQL statement, we take a snapshot of transactions that are currently running, which freezes our viewpoint of the database. The pg_dump utility can take a parallel dump of a single database using the snapshot export feature.

We can't (yet) share that snapshot across sessions connected to more than one database, so we cannot run an exactly consistent pg_dump in parallel across multiple databases.

The time of the snapshot is the only moment we can recover to—we can't recover to a time either before or after. Note that the snapshot time is the start of the backup, not the end.

When pg_dump runs, it holds the very lowest kind of lock on the tables being dumped. Those are designed to prevent DDL from running against the tables while the dump takes place. If a dump is run at the point at which other DDLs are already running, then the dump will sit and wait. If you want to limit the waiting time, you can do so by setting the --lock-wait-timeout option.

Since pg_dump runs SQL queries to extract data, it will have some performance impact. This must be taken into account when executing on a live server.

The pg_dump utility allows you to take a selective backup of tables. The -t option also allows you to specify views and sequences. There's no way to dump other object types individually using pg_dump. You can use some supplied functions to extract individual snippets of information from the catalog.

More details on these functions are available at https://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE

The pg_dump utility works against earlier releases of PostgreSQL, so it can be used to migrate data between releases.

When migrating your database from an earlier version, it is generally recommended to use pg_dump of the same version of the target PostgreSQL. For example, if you are migrating a PostgreSQL 9.3 database to PostgreSQL 10, you should use pg_dump v10  to remotely connect to the 9.3 server and back up the database.

As far as extensions are concerned, pg_dump is aware of any objects (namely tables and functions) that have been installed as part of an additional package, such as PostGIS or Slony. Thanks to that, they can be recreated by issuing appropriate CREATE EXTENSION commands instead of dumping and restoring them together with the other database objects. Extension support removes such difficulties when restoring from a logical backup, maintaining the list of additional tables that have been created as part of the software installation process. Look at the Managing installed extensions recipe in Chapter 3, Configuration, for more details.