pg_basebackup is a core utility which makes a "base backup" - a binary snapshot - of a running PostgreSQL cluster's data directory via the 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 introduced 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_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
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
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.
pg_basebackup provides a
--progress option to dynamically display the progress of the base as a percentage and in kilobytes.
- PostgreSQL 13
- PostgreSQL 12
- PostgreSQL 11
--create-slotadded, to automatically create a replication slot specified with the
--slotoption (commit 3709ca1c)
- PostgreSQL 10
- PostgreSQL 9.6
--slotadded to reserve a replication slot before the backup is started (commit 0dc848b0)
- PostgreSQL 9.5
- PostgreSQL 9.4
- PostgreSQL 9.3
- PostgreSQL 9.2
- able to make base backups from standby servers (commit 8366c780)
- PostgreSQL 9.1
- added (commit 048d148f)
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
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
postgres=# SELECT * from pg_stat_progress_basebackup \x\g\x -[ RECORD 1 ]--------+------------------------- pid | 29070 phase | streaming database files backup_total | 2179629568 backup_streamed | 945447936 tablespaces_total | 1 tablespaces_streamed | 0