Logical backups with SQL dumps

#

SQL dumps consist of a file containing SQL commands that can be fed back to a PostgreSQL server to recreate a database in the exact state it was in at the time the dump was taken.

For a deeper understanding of SQL dumps and PostgreSQL backup strategies, we recommend reading the official PostgreSQL documentation on SQL dumps.

Prerequisites

#
  • Running PostgreSQL server
  • Environment variables: PGHOST, PGPORT, PGUSER, PGPASSWORD

Back Up Single Database

# 
$ export PGUSER=xxx
$ export PGPORT=5432
$ export PGHOST=xxx
$ export PGPASSWORD=xxx

pg_dump <dbname> | plakar at /var/backups backup stdin:dump.sql

Restore Single Database

# 
$ export PGUSER=xxx
$ export PGPORT=5432
$ export PGHOST=xxx
$ export PGPASSWORD=xxx

$ plakar at /var/backups cat <SNAPSHOT_ID>:dump.sql | psql -X <dbname>

List snapshots:

$ plakar at /var/backups ls

Back Up Entire Cluster

#

Use pg_dumpall to include all databases, roles, and tablespaces:

$ export PGUSER=xxx
$ export PGPORT=5432
$ export PGHOST=xxx
$ export PGPASSWORD=xxx

$ pg_dumpall | plakar at /var/backups backup stdin:dump.sql

Restore Entire Cluster

# 
$ export PGUSER=xxx
$ export PGPORT=5432
$ export PGHOST=xxx
$ export PGPASSWORD=xxx

$ plakar at /var/backups cat <SNAPSHOT_ID>:dump.sql | psql -X

Considerations

#

Compression

#
  • Do not compress dumps manually
  • Plakar automatically deduplicates and compresses data
  • Pre-compressed dumps prevent effective deduplication

Pages

Solutions

Resources

Newsletter