pg_basebackup
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
- PostgreSQL 15
- new
COPY
subprotocol implemented (commit cc333f32) - option
-t
/--target
added (commit 3500ccc3) - option
--compress
extended to accept a compression method and an optional compression level (commit 5c649fe1) - option
--compress
can acceptclient-gzip
orserver-gzip
as well asgzip
(commit 0ad80329) - option
--compress
can acceptlz4
orserver-lz4
(commit dab29847) - option
--compress
can acceptclient-lz4
(commit 751b8d23) - option
--compress
can acceptzstd
,client-zstd
orserver-zstd
(commit 7cf085f0) - option
--compress
can accept(client|server)-zstd:workers=N
to provide parallel backup using multiple zstd workers (commit 51c0d186)
- new
- PostgreSQL 13
- backup manifests generated for base backups, which can be verified with the new utility
pg_verifybackup
(commit 0d8c9c12) - total estimated backup size displayed by default (commit fab13dc5)
- option
--no-estimate-size
added (commit fab13dc5)
- backup manifests generated for base backups, which can be verified with the new utility
- PostgreSQL 12
- option
--write-recovery-conf
will now write its configuration settings topostgresql.auto.conf
(commit 2dedf4d9)
- option
- PostgreSQL 11
- option
--create-slot
added, to automatically create a replication slot specified with the--slot
option (commit 3709ca1c)
- option
- PostgreSQL 10
- PostgreSQL 9.6
- option
--slot
added to reserve a replication slot before the backup is started (commit 0dc848b0)
- option
- PostgreSQL 9.5
- able to use a tablespace mapping file when using tar format (commit 72d422a5)
- PostgreSQL 9.4
- option
--xlogdir
added to specify thepg_xlog
directory location (commit d1b88f6b) - option
-T
/--tablespace-mapping
added for relocating tablespaces on the destination server (commit fb05f3ce) - option
--max-rate
added to enable throttling of backup transfer rate (commit ef5856fd)
- option
- PostgreSQL 9.3
--write-recovery-conf
option added, which creates a basic recovery.conf file (commit 915a29a1)--xlog-method
able to handle streaming timeline switches (commit 0b632913)-d
/--dbname
option added to accept conninfo strings (commit aa05c37e)
- PostgreSQL 9.2
- able to make base backups from standby servers (commit 8366c780)
- PostgreSQL 9.1
- added (commit 048d148f)
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
References
- PostgreSQL documentation: pg_basebackup