Base de Conhecimento

PostgreSQL provides several backup tools depending on your needs — from a simple single-database dump to full cluster backups. This guide covers the most common, reliable methods.

Prerequisites

• PostgreSQL installed
• Root or sudo access

Backing Up a Single Database

sudo -u postgres pg_dump myapp > myapp-backup.sql

Backing Up in Compressed Custom Format (Recommended)

sudo -u postgres pg_dump -Fc myapp > myapp-backup.dump

The custom format is compressed and supports selective restores — generally preferable to plain SQL for anything beyond a quick manual backup.

Backing Up All Databases (Cluster-Wide)

sudo -u postgres pg_dumpall > all-databases-backup.sql

Restoring from a Plain SQL Dump

sudo -u postgres psql myapp < myapp-backup.sql

Restoring from a Custom-Format Dump

sudo -u postgres createdb myapp_restored
sudo -u postgres pg_restore -d myapp_restored myapp-backup.dump

Restoring All Databases from pg_dumpall

sudo -u postgres psql < all-databases-backup.sql

Automating Daily Backups with Cron

sudo nano /usr/local/bin/backup-postgres.sh

#!/bin/bash
TIMESTAMP=$(date +%F)
BACKUP_DIR="/var/backups/postgresql"
mkdir -p "$BACKUP_DIR"
sudo -u postgres pg_dumpall | gzip > "$BACKUP_DIR/all-$TIMESTAMP.sql.gz"
find "$BACKUP_DIR" -type f -mtime +7 -delete

sudo chmod +x /usr/local/bin/backup-postgres.sh

sudo crontab -e

0 2 * * * /usr/local/bin/backup-postgres.sh >> /var/log/postgres-backup.log 2>&1

Selective Table Restore (Custom Format Only)

pg_restore -d myapp -t specific_table myapp-backup.dump

Verifying a Dump File

pg_restore --list myapp-backup.dump

This lists the dump's contents without actually restoring anything — a quick way to confirm the file isn't corrupted.

Testing a Restore

sudo -u postgres createdb restore_test
sudo -u postgres pg_restore -d restore_test myapp-backup.dump
sudo -u postgres dropdb restore_test

Common Errors

"pg_dump: error: connection to database failed" — verify you're running as the postgres user or providing correct connection credentials.

Restore fails with "role does not exist" — the target server is missing a role/user referenced in the dump; create it first or use pg_dumpall --globals-only to also capture roles.

Best Practices

• Prefer the custom format (-Fc) for anything beyond quick manual dumps
• Automate backups and store copies off-server
• Test restores periodically, not just backup creation

Related Articles

• How to Install PostgreSQL on Ubuntu & Debian
• How to Set Up Automated VPS Backups (rsync, cron & Off-Site Storage)
• How to Run MySQL, PostgreSQL & Redis in Docker Containers