Backup, Restore & Security

Logical backups with pg_dump

# one database, custom format (compressed, supports selective restore)
pg_dump -U postgres -d school -Fc -f school.dump

# plain SQL, human-readable
pg_dump -U postgres -d school -f school.sql

# all databases
pg_dumpall -U postgres -f full.sql

# schema only / data only
pg_dump -U postgres -d school --schema-only -f schema.sql
pg_dump -U postgres -d school --data-only   -f data.sql

# specific tables
pg_dump -U postgres -d school -t students -t enrollments -f tables.sql

# parallel dump, faster on large databases (directory format only)
pg_dump -U postgres -d school -Fd -j 4 -f school_dump_dir

pg_dump produces a consistent snapshot without blocking writers, using the same MVCC machinery that powers normal reads — no --single-transaction flag needed the way MySQL requires one.

Restoring

# custom format
pg_restore -U postgres -d school school.dump
pg_restore -U postgres -d school -j 4 school.dump     # parallel restore

# plain SQL
psql -U postgres -d school -f school.sql

# create the database first if restoring from scratch
createdb -U postgres school
pg_restore -U postgres -d school school.dump

-Fc (custom format) is usually the right default: it is compressed, supports parallel dump and restore, and pg_restore --list lets you inspect or selectively restore objects from it.

Physical backups

pg_basebackup -U replicator -h primary -D /backup/base -Fp -Xs -P

pg_basebackup copies the whole data directory and is the foundation of point-in-time recovery and streaming replication. It scales better than pg_dump for very large databases, but restoring it always recovers the entire cluster, not one database or table.

Point-in-time recovery (PITR)

Continuous WAL archiving lets you restore to any moment, not just the last full backup:

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /archive/%f'

pg_basebackup -D /backup/base -Fp -Xs   # a base backup, taken periodically

# recovery.signal + postgresql.conf on the restored copy:
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-17 14:29:00'

This is how you recover from "someone ran DELETE without a WHERE at 14:30" — restore the base backup, then replay WAL up to 14:29. Tools like pgBackRest and WAL-G automate the whole archive/restore/PITR workflow and are the standard choice in production.

Backup discipline

1. Follow 3-2-1: three copies, two media types, one off-site.
2. Test the restore — an untested backup is a guess. Restore to a scratch server on a schedule.
3. Automate and alert on failure.
4. Keep a retention ladder: daily for two weeks, weekly for three months, monthly for a year.
5. Encrypt backup files — they contain everything the database does.

Security checklist

Access

• Use scram-sha-256 in pg_hba.conf, never trust or md5, for any network-reachable role.
• No application connects as a superuser; least privilege for every role.
• Bind to localhost or a private network; never expose port 5432 to the internet.
• REQUIRE SSL-equivalent: set sslmode=require (or verify-full) on the client connection string.

Application

• Use parameterised queries everywhere; string concatenation is how SQL injection happens.
• Let database constraints back up application-level validation.
• Hash passwords with bcrypt or Argon2 in the application — never store them in plain SQL columns.

Data

• Encrypt at rest (filesystem or cloud-provider encryption) and require TLS in transit.
• Use row-level security for multi-tenant data as a second line of defence, not the only one.
• Consider pgcrypto for encrypting specific sensitive columns.

Operations

• Apply minor version updates promptly; they often contain security fixes.
• Review role grants periodically with \du and \dp, and drop accounts for people who have left.
• Enable log_statement or pgaudit for privileged operations, and monitor pg_stat_activity for unexpected connections.