Contents

pg_basebackup

A core utility which takes a snapshot of a PostgreSQL cluster's data directory

pg_basebackup is a core utility which makes a "base backup" - a binary snapshot - of a running PostgreSQL cluster's data directory via the streaming replication protocol. This base backup is typically used as the basis for a replicated version of the original cluster; for example, after copying a running cluster's data to a different server, a PostgreSQL instance can be started using the copied data as its data directory and configured as a replication standby. If appropriately configured, this standby will automatically synchronise any changes on the source server since the base backup was taken.

pg_basebackup was added in PostgreSQL 9.1.

Note that despite its name, pg_basebackup is not a backup utility per-se; regular database backups should be made using pg_dump or pg_dumpall, or better still using a utility such as Barman or pg_backrest. Also, pg_basebackup can only make snapshots of an entire cluster, not individual databases or objects. pg_dump or logical replication are more suitable tools for this task. (However, by using pg_basebackup to "kickstart" a replication standby it becomes possible to make backups from the standby and avoid the load pg_dump etc place on the primary database).

To be able to make a base backup, pg_basebackup requires a superuser or replication user login on the running cluster with appropriate connection permissions defined in pg_hba.conf . Sufficient walsender processes must be available (configuration setting max_wal_senders).

By default, pg_basebackup makes a one-to-one copy of all files and directories in the original cluster (option --format=plain), however it can also output copied data to tar files (option --format=tar). See the documentation for caveats on tablespace handling.

Note that the completed base backup will include any configuration files such as postgresql.conf and pg_hba.conf files contained in the original server's data directory. These files (along with recovery.conf in PostgreSQL 11 and earlier) may need to be adjusted or created before starting a PostgreSQL instance using the copied data directory.

Progress monitoring

pg_basebackup provides a --progress option to dynamically display the progress of the base as a percentage and in kilobytes.

From PostgreSQL 13, a progress reporting view pg_stat_progress_basebackup is available to monitor the process of the base backup on the originating server.

Change history

Examples

postgres@node:~> pg_basebackup -h node1 -U repuser -D /path/to/local/datadir --xlog --format=plain \
 --label=backup_2013-03-26 --progress --verbose
Password: 
transaction log start point: 6B/A40053E8
91977/9000384 kB (1%), 0/1 tablespace (node2-datadir/base/1)
transaction log end point: 6B/A44A7040
pg_basebackup: base backup completed

Note that after executing the command, there is often a delay of a few seconds before any activity becomes visible; this is usually because pg_basebackup is waiting for the next checkpoint to occur (a checkpoint can be forced with the option --checkpoint=fast).

While pg_basebackup is running, pg_stat_replication will look something like this:

postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+-----------------------------
pid              | 10770
usesysid         | 90766160
usename          | repuser
application_name | pg_basebackup
client_addr      | 192.168.1.33
client_hostname  | 
client_port      | 31933
backend_start    | 2013-03-27 02:34:53.29996+01
state            | backup
sent_location    | 0/0
write_location   | 
flush_location   | 
replay_location  | 
sync_priority    | 0
sync_state       | async

In PostgreSQL 13 and later, the progress of the base backup operation can be monitored on the originating server via the system catalogue view "pg_stat_progress_basebackup", e.g.:

postgres=# SELECT * FROM pg_stat_progress_basebackup \gx
-[ RECORD 1 ]--------+-------------------------
pid                  | 29070
phase                | streaming database files
backup_total         | 2179629568
backup_streamed      | 945447936
tablespaces_total    | 1
tablespaces_streamed | 0

Categories

Backup, Core utility, Management / adminstration, Replication

See also

pg_dump, pg_dumpall, pg_stat_progress_basebackup