PostgreSQL Backup Setup: pg_dump and pg_dumpall

Losing a database due to a corrupted backup is every admin's nightmare. We've seen dozens of incidents where pg_dump silently created an empty file or the dump couldn't be restored because of connection errors. That's why in our practice every backup undergoes verification: pg_restore --list checks integrity, and we additionally test restoration on a staging environment.

A typical mistake is storing dumps on the same disk where the database runs. In case of disk failure you lose both the database and the backup. We always place backups in a separate object storage, for example S3, with a lifecycle policy configured. Our experience shows that 30% of production incidents with PostgreSQL are related to incorrect backups. We guarantee that after our setup you will be able to restore the database within one hour with an RPO of no more than 24 hours.

Why pg_dump remains the standard for backup

pg_dump is a logical dump utility that does not require stopping the database (pg_dump documentation). It works on any PostgreSQL version, and the dump is portable across versions (provided the target version >= source version). For databases up to 100 GB, this is the optimal choice. The key advantage is flexibility: you can selectively restore tables, schemas, or data.

However, pg_dump has limitations: a large dump can take hours, and if a failure occurs during execution, the dump will be incomplete. That's why we use the custom format (-Fc): it compresses data, supports parallel restore, and allows integrity checking. The custom format (-Fc) is about 5 times more compact than plain SQL, and restore with pg_restore -j 4 speeds up by 3-4 times on multi-core servers.

If you need to back up an entire cluster with roles and tablespaces, use pg_dumpall. But for a single database, pg_dump is faster and provides more options during restore.

Comparison of dump formats

Custom format (-Fc) is our default choice: compression 5-10x, pg_restore runs in multiple threads, you can restore only the needed tables.

PostgreSQL Documentation

Comparison of pg_dump and pg_dumpall

How we set up PostgreSQL backup: a 50 GB database case

Consider a real project: a 50 GB database, daily full dump, 30-day retention, S3 as remote storage. Requirement: RTO < 1 hour, RPO < 24 hours. Restoring a 50 GB database in -Fc format on a test stand with 4 cores takes 20 minutes.

We prepared a script with verification, upload to S3, and Telegram notification on failure. The script runs via cron, logs are written to a file and sent to the monitoring system.

Backup code:

#!/bin/bash
set -euo pipefail

DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
DB_NAME="production"
BACKUP_DIR="/backup/postgresql"
S3_BUCKET="s3://backups-company/postgresql"
RETENTION_DAYS=30

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"

mkdir -p "$BACKUP_DIR"

echo "Starting backup at $(date)" | tee "$LOG_FILE"

# Create dump
PGPASSFILE="${HOME}/.pgpass"
pg_dump \
  -Fc \
  -h "$DB_HOST" \
  -p "$DB_PORT" \
  -U "$DB_USER" \
  -d "$DB_NAME" \
  --no-password \
  -f "$BACKUP_FILE" 2>> "$LOG_FILE"

# Integrity check
pg_restore --list "$BACKUP_FILE" > /dev/null 2>&1 || {
  echo "ERROR: Backup integrity check failed" | tee -a "$LOG_FILE"
  exit 1
}

# Upload to S3
aws s3 cp "$BACKUP_FILE" "${S3_BUCKET}/${DB_NAME}_${DATE}.dump" \
  --storage-class STANDARD_IA \
  --server-side-encryption aws:kms 2>> "$LOG_FILE"

echo "Upload to S3 completed" | tee -a "$LOG_FILE"

# Clean local copies older than RETENTION_DAYS
find "$BACKUP_DIR" -name "*.dump" -mtime +${RETENTION_DAYS} -delete

echo "Backup completed successfully at $(date)" | tee -a "$LOG_FILE"

The --no-password flag forces pg_dump to read the password from ~/.pgpass, which is more secure than an environment variable. The .pgpass file must have permissions 600.

Example .pgpass content: localhost:5432:production:postgres:yourpassword

Monitoring backup age

To catch the moment when backups stop being created, we add a check in Nagios/Icinga:

#!/bin/bash
DB_NAME="production"
LAST_BACKUP_DATE=$(aws s3 ls s3://backups-company/postgresql/ \
  --recursive | grep "${DB_NAME}_" | sort | tail -1 | awk '{print $1" "$2}')
AGE_HOURS=$(( ($(date +%s) - $(date -d "$LAST_BACKUP_DATE" +%s)) / 3600 ))
if [ $AGE_HOURS -gt 25 ]; then
  echo "CRITICAL: Last backup is ${AGE_HOURS}h old"
  exit 2
elif [ $AGE_HOURS -gt 13 ]; then
  echo "WARNING: Last backup is ${AGE_HOURS}h old"
  exit 1
else
  echo "OK: Last backup is ${AGE_HOURS}h old"
  exit 0
fi

This script checks that the age of the latest dump does not exceed 25 hours. If no backup has been created for more than 13 hours — warning, more than 25 — critical. This approach lets you detect problems before the RPO is violated.

Why backup monitoring is important

Without monitoring, you only find out about a problem when you try to restore. By then it may be too late. We include in the setup not only age monitoring but also dump size: a sudden decrease in size may indicate an error in the script or database issues. Additionally, we configure alerts via Telegram or Slack.

Restoring a database from a dump with pg_restore

Restoring from a custom dump is done with a single command:

pg_restore -j 4 -d new_db -U postgres -h localhost production_backup.dump 

The -j 4 flag runs 4 parallel threads — this reduces restore time by 50-70% on multi-core systems. If you need to restore only one table, use -t table_name.

What is included in turnkey setup

• Audit of current backup scheme and RPO/RTO requirements
• Development of scripts with dump verification and upload to S3
• Integration with monitoring systems (Prometheus, Zabbix, Telegram)
• Documentation of the restore procedure (step-by-step instructions)
• Training for the on-call team
• Support for the first month after implementation

Process of work

1. Analysis: assess database size, RPO/RTO requirements, available storage.
2. Design: choose dump format, storage scheme (local + S3), schedule.
3. Implementation: write scripts, configure cron, integrate with S3.
4. Testing: perform a test restore on a staging environment, verify integrity.
5. Deployment: install in production, configure monitoring.
6. Documentation: describe the restore procedure, responsible contacts.

Timeline and cost

Setting up a basic scenario (pg_dump + S3 + monitoring) takes from 1 to 3 working days depending on complexity. Cost is calculated individually based on the scope of work and redundancy requirements. We guarantee that after our setup you will be able to restore the database following the instructions within 1 hour.

Common mistakes in backup setup

• Storing dumps on the same disk as the database.
• Using plain SQL without compression (dump takes a lot of space, restores slowly).
• Lack of dump verification.
• Password in plain text in cron (use .pgpass).
• No backup age monitoring.

Our team has more than 5 years of experience in PostgreSQL administration and has implemented over 50 backup projects for companies of various sizes. Contact us for an audit of your current backup scheme — we will propose the optimal solution. Order turnkey setup and get a recovery guarantee in 1 hour. Get a consultation on PostgreSQL backup setup today.