KAHIBARO
Discord Login Register ☰
☰

11.11. Backup and Restore

Table of Contents

Why Backup and Restore Matter

Your database contains your users, orders, payments, logs, and more. If you lose it, your application is often useless.

Typical things that can go wrong:

Backups and restores let you:

**Always treat backups as critical infrastructure.
A backup that cannot be restored is the same as no backup.
You must regularly test restore procedures.**

In this chapter we focus on PostgreSQL specific tools and workflows, not generic backup theory.

Types of PostgreSQL Backups

There are two main categories of backups in PostgreSQL:

TypeTool examplesProsCons
Logical backuppg_dump, pg_dumpallPortable, flexible, selective, easy to inspectSlower on huge DBs, larger size, slower restore
Physical backuppg_basebackup, file system snapshotsFast for large DBs, exact copy, good for replicationTied to version and OS, less flexible, more complex

You will use logical backups most often as a backend developer, especially for:

Physical backups are more common in production setups handled by DevOps / DBAs, but you should know the basics.

Logical Backups with pg_dump

pg_dump creates a logical backup of a single PostgreSQL database. It does not back up the whole cluster, only one database at a time.

Basic pg_dump usage

Common options:

Example: simple SQL backup

This creates a text .sql file that can be opened in a text editor and restored with psql:

bash 
pg_dump -h localhost -p 5432 -U myuser -d mydb -F p -f mydb.sql

-F p means "plain" SQL.

Example contents of mydb.sql (simplified):

sql 
--
-- PostgreSQL database dump
--
CREATE TABLE public.users (
    id integer NOT NULL,
    email text NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT now()
);
INSERT INTO public.users (id, email, created_at) VALUES
(1, 'alice@example.com', '2024-01-01 10:00:00'),
(2, 'bob@example.com', '2024-01-02 12:00:00');

This is human readable and easy to move between PostgreSQL versions.

Password handling

pg_dump uses the same authentication as psql. Typical patterns:

bash 
PGPASSWORD='secret' pg_dump -h localhost -U myuser -d mydb -f mydb.sql

Do not hardcode passwords in scripts or share them.

Dumping only the schema or only the data

Often you want only the structure (tables, indexes) or only the contents.

bash 
pg_dump -h localhost -U myuser -d mydb --schema-only -f mydb_schema.sql
bash 
pg_dump -h localhost -U myuser -d mydb --data-only -f mydb_data.sql

Use cases:

Dumping specific tables

You can back up just a subset of tables:

bash 
pg_dump -h localhost -U myuser -d mydb \
  -t public.users \
  -t public.orders \
  -f users_orders.sql

The -t option can be repeated for multiple tables.

Excluding tables

Sometimes you want to dump everything except large log tables:

bash 
pg_dump -h localhost -U myuser -d mydb \
  -T public.audit_logs \
  -f mydb_without_logs.sql

Use -T to exclude tables by name pattern.

Dump formats: plain vs custom vs directory

pg_dump supports several formats:

FormatOptionDescriptionHow to restore
plain-F pText SQL filepsql
custom-F cCompressed binary, flexiblepg_restore
directory-F dDirectory with one file per table, flexiblepg_restore
tar-F tTar archive, less usedpg_restore

For regular developer usage, two formats are most important:

Plain SQL example

bash 
pg_dump -h localhost -U myuser -d mydb -F p -f mydb.sql

Restore:

bash 
psql -h localhost -U myuser -d targetdb -f mydb.sql

Custom format example (recommended for larger DBs)

bash 
pg_dump -h localhost -U myuser -d mydb -F c -f mydb.dump

Advantages of custom format:

Restore with pg_restore (shown later).

Directory format example

bash 
pg_dump -h localhost -U myuser -d mydb -F d -f mydb_backup_dir

This creates a directory mydb_backup_dir with many files. Useful for very large databases and parallel restore.

Including roles and privileges

By default, pg_dump includes object ownership and privileges for the database objects, but not the roles themselves. To back up all roles, you use pg_dumpall (covered later).

For a single database, you can:

bash 
pg_dump -h localhost -U myuser -d mydb \
  --no-owner --no-privileges \
  -f mydb.sql

This makes it easier to restore into another environment with different users.

Restoring Logical Backups

How you restore depends on the backup format.

Restoring plain SQL with psql

If you used -F p (or default) with pg_dump, you restore with psql.

Example: restore into an existing empty database

First create the target database:

bash 
createdb -h localhost -U myuser newdb

Then restore:

bash 
psql -h localhost -U myuser -d newdb -f mydb.sql

Or using shell redirection:

bash 
psql -h localhost -U myuser -d newdb < mydb.sql

If the backup file contains CREATE DATABASE ..., it may try to create the database itself. In that case, run:

bash 
psql -h localhost -U myuser -f mydb.sql

without specifying -d, so it connects to the default database (often postgres) and executes the CREATE DATABASE.

Restoring custom or directory backups with pg_restore

If you used -F c, -F d, or -F t with pg_dump, you restore with pg_restore.

Example: restore into a fresh database

  1. Create the database:
bash 
createdb -h localhost -U myuser newdb
  1. Run pg_restore:
bash 
pg_restore -h localhost -U myuser -d newdb mydb.dump

This recreates schemas, tables, data, and indexes.

Dropping existing objects before restore

If the target database already has tables, you may want to drop them first:

bash 
pg_restore -h localhost -U myuser -d newdb --clean mydb.dump

--clean adds DROP statements for objects before recreating them.

You can also use --if-exists to avoid errors if objects are missing:

bash 
pg_restore -h localhost -U myuser -d newdb --clean --if-exists mydb.dump

Selective restore with pg_restore

One nice feature of custom and directory formats is selective restore.

Restore only one table:

bash 
pg_restore -h localhost -U myuser -d newdb \
  -t public.users \
  mydb.dump

Exclude a table:

bash 
pg_restore -h localhost -U myuser -d newdb \
  -T public.audit_logs \
  mydb.dump

Or list the contents of a backup:

bash 
pg_restore -l mydb.dump

This prints an index of all objects in the dump file.

Parallel restore

You can speed up large restores with multiple jobs:

bash 
pg_restore -h localhost -U myuser -d newdb -j 4 mydb.dump

-j 4 means use 4 parallel jobs. This works with custom or directory formats, not with plain SQL.

Common restore problems and fixes

Problem: "role 'myuser' does not exist"

Your dump contains objects owned by myuser, but that role does not exist in the target cluster.

Quick fix: create the role before restore:

bash 
createuser -h localhost -U postgres myuser

More robust approach: on production, dump roles with pg_dumpall --globals-only (see later) and restore them first.

Problem: "permission denied for database"

User does not have privileges to create tables or other objects.

Fix: connect as a superuser (often postgres) or grant necessary privileges to the user.

Problem: "database 'mydb' already exists"

The dump script contains CREATE DATABASE mydb, but the DB already exists.

Options:

bash 
dropdb -h localhost -U postgres mydb
psql -h localhost -U postgres -f mydb.sql

Backing Up Entire Clusters with pg_dumpall

pg_dump handles a single database. pg_dumpall creates a logical backup of:

Dumping the whole cluster

bash 
pg_dumpall -h localhost -U postgres -f cluster.sql

This file contains:

Example snippet:

sql 
--
-- Roles
--
CREATE ROLE myuser LOGIN PASSWORD '********';
--
-- Databases
--
CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8';
\connect mydb
CREATE TABLE public.users (...);
INSERT INTO public.users ...;

Dumping only global objects

To back up only roles and tablespaces, not the database contents:

bash 
pg_dumpall -h localhost -U postgres --globals-only -f globals.sql

You often use this when setting up a new cluster:

  1. Create roles with globals.sql.
  2. Restore databases with individual pg_dump dumps.

Restoring pg_dumpall dumps

You restore pg_dumpall outputs with psql:

bash 
psql -h localhost -U postgres -f cluster.sql

This will re-create roles, databases, and then the data.

To restore globals only:

bash 
psql -h localhost -U postgres -f globals.sql

Physical Backups with pg_basebackup

Physical backups are exact copies of the data directory at the file system level. They are used for:

The main tool is pg_basebackup.

Warning about physical backups

Physical backups are more sensitive to PostgreSQL version, OS, and configuration.

Physical backups must be restored on the same major PostgreSQL version and compatible OS and architecture. They are not portable between different versions like logical backups.

Basic pg_basebackup usage

pg_basebackup connects to a running cluster and outputs a physical copy of the data directory.

Example:

bash 
pg_basebackup -h localhost -p 5432 -U replicator \
  -D /backups/base_2024_01_01 \
  -Ft -z -P

Explanation:

You must configure pg_hba.conf and server settings to allow replication connections to use pg_basebackup. This is usually done by DevOps or DBAs.

File system level backups

Another way to do physical backups is to stop PostgreSQL, copy the data directory, and start it again. This is not usually used in production because it requires downtime, but you may see it in simple setups.

Example rough steps:

  1. Stop PostgreSQL:
bash 
   sudo systemctl stop postgresql
  1. Copy the data directory:
bash 
   sudo cp -a /var/lib/postgresql/16/main /backups/main_backup
  1. Start PostgreSQL:
bash 
   sudo systemctl start postgresql

Restoring such a backup means replacing the data directory with this copy and ensuring permissions are correct.

Restoring from Physical Backups

Restoring a physical backup basically means:

  1. Stop PostgreSQL.
  2. Replace the data directory with the backup.
  3. Ensure permissions and ownership are correct.
  4. Start PostgreSQL again.

A very simplified example:

bash 
sudo systemctl stop postgresql
sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_old
sudo cp -a /backups/main_backup /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
sudo systemctl start postgresql

In serious production setups, this is usually combined with WAL archiving and point in time recovery, not just a single base backup copy.

Point in Time Recovery (Overview)

Point in time recovery (PITR) lets you restore the database to an exact time, for example "just before a bug deleted rows from the orders table."

PITR uses:

At a high level:

  1. You configure PostgreSQL to archive WAL files.
  2. You take a base backup with pg_basebackup or filesystem copy.
  3. If disaster happens, you restore the base backup.
  4. You replay WAL files up to a specific time.

Example recovery configuration in postgresql.conf:

conf 
restore_command = 'cp /wal_archive/%f "%p"'
recovery_target_time = '2024-01-10 15:23:00'

Then when PostgreSQL starts, it reads WAL files and stops at the target time.

As a backend developer, you usually do not implement PITR infrastructure yourself, but you should understand:

Do not rely on PITR without someone responsible for properly configuring and monitoring it.

Backup Strategies and Schedules

Having tools like pg_dump is not enough. You need a plan that fits your application.

Common backup strategy patterns

For small to medium applications, a common pattern is:

A simple daily logical backup script:

bash 
#!/bin/bash
set -e
DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/var/backups/postgres"
DB_NAME="mydb"
USER="backup_user"
mkdir -p "$BACKUP_DIR"
PGPASSWORD='secret' pg_dump -h localhost -U "$USER" -F c -d "$DB_NAME" \
  -f "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"
# Optional: compress older backups or upload to cloud

Combined with cron:

bash 
crontab -e

Add:

cron 
0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

This runs the backup every night at 02:00.

Example retention policy

You might keep:

You can implement this with a simple shell script that deletes old files based on filename date.

A good backup strategy must consider:

  • Backup frequency.
  • Retention period.
  • Off-site storage.
  • Restore time and complexity.
    Do not keep all backups on the same server only. If the server is lost, you lose both the database and the backups.

Testing Your Backups

Backups that have never been restored are not trustworthy.

You should regularly perform test restores:

  1. Spin up a temporary PostgreSQL instance (Docker is great for this).
  2. Restore your latest backup.
  3. Run basic checks:
    • All expected databases exist.
    • Key tables are present.
    • Simple queries return expected data.
  4. Optionally run your application in "staging" mode against the restored DB.

Example: restore test using Docker

  1. Run a fresh PostgreSQL container:
bash 
   docker run --name pg-test -e POSTGRES_PASSWORD=secret -p 5433:5432 -d postgres:16
  1. Copy the backup file inside the container (or mount as volume):
bash 
   docker cp mydb_latest.dump pg-test:/backup.dump
  1. Restore inside the container:
bash 
   docker exec -it pg-test bash -c "
     createdb -U postgres testdb && \
     pg_restore -U postgres -d testdb /backup.dump
   "
  1. Connect and run a quick check:
bash 
   psql -h localhost -p 5433 -U postgres -d testdb -c 'SELECT COUNT(*) FROM users;'

This verifies that:

Document this procedure in your project docs so it is easy to repeat.

Backup and Restore in Development Workflows

Beyond disaster recovery, backups are also useful for everyday development.

Cloning production data to staging

A common workflow:

  1. Create a logical backup from production:
bash 
   pg_dump -h prod-host -U backup_user -F c -d mydb -f prod_mydb.dump
  1. Transfer the file to staging environment.
  2. Restore into a staging database:
bash 
   createdb -h staging-host -U staging_user mydb_staging
   pg_restore -h staging-host -U staging_user -d mydb_staging prod_mydb.dump

If production has sensitive data, you should:

Sharing test data with teammates

You can capture a snapshot of your local development database and share it:

bash 
pg_dump -h localhost -U devuser -F c -d devdb -f devdb_template.dump

Your teammate restores:

bash 
createdb -h localhost -U devuser devdb
pg_restore -h localhost -U devuser -d devdb devdb_template.dump

Now you both work on the same starting data set.

Migrating between PostgreSQL versions

To move from PostgreSQL 14 to 16:

  1. Create a logical dump from the old version:
bash 
   pg_dump -h old-host -U postgres -F c -d mydb -f mydb_v14.dump
  1. Install PostgreSQL 16 and create a new empty database:
bash 
   createdb -h new-host -U postgres mydb
  1. Restore:
bash 
   pg_restore -h new-host -U postgres -d mydb mydb_v14.dump

Logical dumps handle many version differences automatically, while physical backups do not.

Security Considerations for Backups

Backups often contain all your sensitive data. You must secure them.

Where backups are stored

Do not leave backups:

Use:

Example:

bash 
mkdir -m 700 /var/backups/postgres
chown postgres:postgres /var/backups/postgres

Encryption

Consider encrypting backups at rest, especially for production data.

Options:

bash 
  pg_dump -h localhost -U backup_user -F c -d mydb \
    | gpg --symmetric --cipher-algo AES256 \
    -o mydb.dump.gpg

To restore:

bash 
gpg -d mydb.dump.gpg | pg_restore -h localhost -U postgres -d mydb

Keep encryption keys safe and backed up also, otherwise you cannot restore.

Access control

Also remember legal and compliance requirements, such as GDPR, which may affect how long you can keep user data.

Summary

Key ideas from this chapter:

With a solid understanding of these tools and principles, you can design backend systems that are much more resilient to mistakes, failures, and migrations.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3001 / 24h
Impressum / Imprint
Datenschutz / Data Policy