Logical backups with SQL dumps

Last reviewed: 2026-01-05 / Plakar v1.0.6

Overview

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