PostgreSQL Backup Setup: pg_dump and pg_dumpall

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.