Logical backups with pg_dump

This guide shows how to back up and restore PostgreSQL databases using the postgres:// connector. Logical backups produce portable, SQL-level snapshots via pg_dump and pg_dumpall that can be restored to a different PostgreSQL major version and allow selective restore of individual databases, schemas, or tables.

For full reference on connectors, all options, snapshot layout, and AWS IAM authentication, see the PostgreSQL integration reference.

• A running PostgreSQL server.
• A PostgreSQL superuser, or a user with sufficient privileges to run pg_dump and pg_dumpall.
• The following client tools available in $PATH: pg_dump, pg_dumpall, pg_restore, psql.

Install the PostgreSQL package:

$ plakar pkg add postgresql

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

Omit the database name to back up the entire cluster with pg_dumpall:

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

$ plakar at /var/backups ls

The target database must already exist:

$ plakar destination add mypgdst postgres://postgres:secret@db.example.com/myapp
$ plakar at /var/backups restore -to @mypgdst <snapshot_id>

To have Plakar create the database automatically:

$ plakar destination add mypgdst postgres://postgres:secret@db.example.com/myapp \
  recreate=true
$ plakar at /var/backups restore -to @mypgdst <snapshot_id>

$ plakar destination add mypgdst postgres://postgres:secret@db.example.com/
$ plakar at /var/backups restore -to @mypgdst <snapshot_id>

Do not enable compress=true unless necessary. Plakar deduplicates and compresses data automatically. Pre-compressed dumps produce an incompressible stream that reduces deduplication effectiveness across snapshots.

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 SQL dumps