Physical backups with pg_basebackup

This guide shows how to back up and restore a PostgreSQL cluster using the postgres+bin:// connector. Physical backups capture the entire data directory via pg_basebackup and are faster to restore than logical dumps. They are version-locked (the backup must be restored with the same PostgreSQL major version) and do not support selective restore of individual databases or tables.

For full reference on all options and snapshot layout see the PostgreSQL integration reference.

• A running PostgreSQL server with wal_level = replica or higher in postgresql.conf.
• A PostgreSQL user with the REPLICATION privilege, or a superuser.
• pg_hba.conf allowing a replication connection from the backup host.
• pg_basebackup available in $PATH.

Install the PostgreSQL package:

$ plakar pkg add postgresql

Ensure pg_hba.conf includes an entry allowing replication connections from the backup host. For example, to allow local replication without a password:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   replication     all                                     trust

Restart PostgreSQL after making changes.

$ plakar source add mypg postgres+bin://replicator:secret@db.example.com
$ plakar at /var/backups backup @mypg

$ plakar at /var/backups ls

There is no dedicated destination connector for physical backups. Restore the files to a local directory using the standard filesystem restore, then start PostgreSQL against it:

$ plakar at /var/backups restore -to ./pgdata <snapshot_id>
$ docker run --rm \
  -v "$PWD/pgdata:/var/lib/postgresql/data" \
  postgres:<version>

Replace <version> with the same major PostgreSQL version that was running when the backup was taken.

To restore directly to a remote host via SFTP:

$ plakar at /var/backups restore -to sftp://user@host/var/lib/postgresql/data <snapshot_id>
# then on the remote host:
$ pg_ctl -D /var/lib/postgresql/data start

Physical backups must be restored with the same PostgreSQL major version. For cross-version restores, use a logical backup instead.

Do not restore into a data directory that is in use by a running PostgreSQL instance. Stop the server first, or restore to a fresh directory.

The examples above use /var/backups as the Kloset store. Any supported store backend can be used instead. See Create a Kloset store for details.

• PostgreSQL integration reference
• PostgreSQL integration GitHub
• PostgreSQL documentation on pg_basebackup