How to make backups of PostgreSQL? (depesz.com)

📔 note Aug. 21, 2025 Database PostgreSQL

depesz posted a simple Posgres backup script that's much easier to use and reason about. I found it after feeling a bit overwhelmed by the PostgreSQL Wiki Backup scripts. There's also a more recent, efficient version.

Original despez backup script:

#!/usr/bin/env bash
# common
top_level_backup_dir=/var/backups/pg
# common

cd "$top_level_backup_dir" || exit 1

backup_dir=$( date +%Y-%m-%d )
mkdir -p "$backup_dir" || exit 1
cd "$backup_dir"

# Make actual backup files
pg_dumpall -r > roles.dump
psql -qAtX -c "select datname from pg_database where datallowconn order by pg_database_size(oid) desc" | \
    xargs -d'\n' -I{} -P2 pg_dump -Fc -f pg-{}.dump {}

# Retention policy
cd "$top_level_backup_dir" || exit 1
find . -mindepth 2 -maxdepth 2 -type f -regex '\./[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]/.*dump' -mtime +6 -delete
find . -mindepth 1 -maxdepth 1 -type d -regex '\./[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' -empty -delete

Further simplified

I'm currently using restic to do all my backups. Restic does it's own incremental snapshots, so I think it'll be a bit more efficient to let restic manage "versioning" the snapshots than this script rather than date-time stamped backups. This script is also going to use the directory format, which seems like it could be a bit more efficient for restic.

#!/usr/bin/env bash

# Exit on error, treat unset variables as errors, and fail pipelines on first error.
set -euo pipefail

# --- Configuration ---
BACKUP_DIR="/var/backups/BACKUP_DIR"
PG_USER="postgres" # Define the user for clarity and easy modification

# --- Create the Database Dump ---
echo "Creating PostgreSQL dump in $BACKUP_DIR..."
rm -rf "$BACKUP_DIR"
mkdir -p "$BACKUP_DIR" # Create the directory after rm

# Use the -U flag to specify the user for dumping global objects
echo "Dumping global objects..."
pg_dumpall -U "$PG_USER" --file="$BACKUP_DIR/globals.sql" --globals-only

# Use the -U flag for psql to get the database list, and for pg_dump to dump each database
echo "Dumping individual databases..."
psql -U "$PG_USER" -qAtX -c "select datname from pg_database where datallowconn" | \
    xargs -d'\n' -I{} -P2 pg_dump -U "$PG_USER" -Fd -f "$BACKUP_DIR/{}" --clean {}

echo "PostgreSQL dump complete."

A few more details

Skip password prompts with a `.pgpass` file

**Step 1: Create a .pgpass file

The file must be:
1. Named .pgpass
2. Located in the home directory of the user running the script.
The format of each line in the file is: hostname:port:database:username:password

# Create a .pgpass file in the home directory
nano ~/.pgpass

Example .pgpass content to use the default postgres user for any connection. Wildcard (*) to match any value.

Example .pgpass content:

# hostname:port:database:username:password
*:*:*:postgres:YourPostgresUserPassword

Step 2: Set Secure Permissions

PostgreSQL will completely ignore the .pgpass file if its permissions are too open.

Use the chmod command to set the correct permissions:

chmod 0600 ~/.pgpass

Restoring from the backups

To restore from those backup files, you'll use the pg_restore command.

Step 1: Restore Global Objects (Roles & Tablespaces)

The globals.sql file is a plain SQL script with all the users (roles). You restore it using the standard psql client. You should run this as a PostgreSQL superuser (like postgres).

psql -f /path/to/your/pg_dump_dir/globals.sql postgres

Step 2: Create the Target Databases

pg_restore does not create the databases; it only restores data into existing databases.

For each database you want to restore, run createdb.

# Example for a database named 'app_db' owned by 'app_user'
createdb -O app_user app_db

Repeat this for every database dump folder you have.

Step 3: Restore Each Database with pg_restore

Now you can use pg_restore to populate each empty database. The great advantage of the directory format is that you can restore in parallel, which is much faster.

# -d specifies the target database
# -j specifies the number of concurrent jobs (e.g., 2)
# The final argument is the path to the backup *directory* for that database

pg_restore --dbname=app_db --jobs=2 /path/to/your/pg_dump_dir/app_db

Repeat this pg_restore command for each database created in Step 2.