Schedule PostgreSQL Backups with cron

You've spent weeks perfecting your PostgreSQL schema, and the data inside it is irreplaceable—yet without automated backups, one accidental DROP TABLE or a corrupted disk can erase months of work in seconds. Manually running pg_dump whenever you remember is not a backup strategy; it's a gamble. This lesson shows you how to schedule backups with cron and pg_dump, turning a fragile hope into a reliable, unattended process that runs every night while you sleep.

The problem this lesson solves

Database backups are like insurance—you don't appreciate them until disaster strikes. A hard drive fails, a misapplied migration deletes a critical table, or a script overwrites production data with test data. Without a recent, restorable backup, recovery means rebuilding from logs, backups from a colleague's laptop, or simply accepting the loss.

Manual backups are worse than none because they give you a false sense of security. You think you're covered because you ran pg_dump last Tuesday, but the backup file sits on the same server that just caught fire. And even if it survived, you'd have to remember the exact command, the file location, and the restoration procedure—under pressure.

The solution is automation: let the operating system's task scheduler (cron) run pg_dump on a regular schedule. This lesson gives you a battle-tested pattern that's simple enough for a single-node app and scalable enough for multi-database setups.

Core concept / mental model

Think of pg_dump as a snapshot camera for your database. It takes a consistent picture of the data and schema at a point in time and writes it to a file. Cron is the alarm clock that wakes up the camera every day at a set time, takes the photo, and files it in an archive.

The magic is in the combination:

• pg_dump handles the what — which database, what format, what data to include.
• cron handles the when — the schedule, the repetition, the log output.

A typical backup job looks like this in your mind's eye:

cron (every day at 02:00)
  └─ runs a shell script (backup.sh)
       └─ calls pg_dump → compressed .dump file
       └─ timestamps the file (backup_2025-04-01.dump)
       └─ cleans up old backups

The key mental shift: you're not writing a command anymore; you're writing a policy. The policy defines when to snapshot, where to store it, and how long to keep it. Cron enforces the policy without human intervention.

How it works step by step

Setting up scheduled PostgreSQL backups involves four logical steps that build on each other. Follow them in order to avoid surprises.

1. Verify pg_dump is installed and accessible. - Run pg_dump --version to confirm it's in your PATH. If not, install it via your package manager or the official PostgreSQL installer. - This is your safety check — a missing executable will silently fail inside cron.

2. Confirm PostgreSQL authentication works non-interactively. - Cron runs with a minimal environment, so your interactive password prompt won't work. - Set up a .pgpass file with the connection details, or use peer/ident authentication where the Postgres user matches your system user. - Example .pgpass: localhost:5432:mydb:pguser:secret (chmod 600).

3. Write a backup script that calls pg_dump. - Use a custom-format dump (.dump) for flexibility and compression. - Add a timestamp to the filename so each backup is unique. - Include cleanup logic to remove backups older than N days.

4. Schedule the script with cron. - Edit your user crontab with crontab -e. - Add a line with the schedule (minute, hour, day-of-month, month, day-of-week) plus the full path to your script. - Redirect output to a log file so you can verify cron ran successfully.

# Inside backup.sh
#!/bin/bash
BACKUP_DIR="/var/backups/postgres"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mkdir -p "$BACKUP_DIR"
pg_dump -U pguser -h localhost -Fc mydb > "$BACKUP_DIR/mydb_$TIMESTAMP.dump"
find "$BACKUP_DIR" -name "*.dump" -mtime +7 -delete

# crontab entry (runs every day at 02:00)
0 2 * * * /home/opc/backup.sh >> /var/log/pg_backup.log 2>&1

Hands-on walkthrough

Let's build the backup pipeline end-to-end. We'll assume a PostgreSQL database named mydb running on localhost, and a user pguser.

Step 1: Create the backup script.

Create a file named backup.sh in your home directory with the following content. Make it executable with chmod +x backup.sh.

#!/bin/bash
#!/bin/bash
# PostgreSQL backup with timestamp and rotation
BACKUP_DIR="/var/backups/postgres"
KEEP_DAYS=7
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Run pg_dump (custom format, compressed)
pg_dump -U pguser -h localhost -Fc mydb > "$BACKUP_DIR/mydb_$TIMESTAMP.dump"

# Check exit status
if [ $? -eq 0 ]; then
    echo "[$(date)] Backup successful: mydb_$TIMESTAMP.dump"
else
    echo "[$(date)] Backup FAILED for mydb"
fi

# Delete backups older than KEEP_DAYS
find "$BACKUP_DIR" -name "*.dump" -mtime +"$KEEP_DAYS" -delete

Step 2: Set up passwordless authentication.

Create ~/.pgpass with the connection details and lock down permissions.

# ~/.pgpass
localhost:5432:mydb:pguser:your_password

chmod 600 ~/.pgpass

Step 3: Test the script manually.

Run the script and verify the output file appears in your backup directory.

./backup.sh
ls -lh /var/backups/postgres/

Expected output:

[2025-04-01 14:30:00] Backup successful: mydb_20250401_143000.dump
-rw-r--r-- 1 opc opc 4.2K Apr  1 14:30 mydb_20250401_143000.dump

Step 4: Add the cron job.

Open your crontab and add the schedule. Use crontab -e.

# m h dom mon dow   command
0 2 * * * /home/opc/backup.sh >> /var/log/pg_backup.log 2>&1

Step 5: Verify cron is working.

Check the log after the scheduled time runs, or force a run by executing the script and inspecting the log.

cat /var/log/pg_backup.log

You should see the success message timestamped with the run time.

Compare options / when to choose what

While pg_dump + cron is the most portable and scheduler-agnostic approach, there are alternatives worth knowing. Here's a quick comparison to help you decide.

Pro tip: Start with cron + pg_dump. Once you outgrow it, move to a purpose-built tool like pgBackRest, but the cron pattern remains a solid fallback.

Troubleshooting & edge cases

Cron jobs fail silently more often than they succeed on the first try. Here are the classic pitfalls and how to fix them.

1. "Password authentication failed" or "no password supplied"

Cron's environment doesn't auto-load your shell profile, so PGPASSWORD or .pgpass might be missing.

• Solution: Use .pgpass (chmod 600) and place it in the cron user's home directory. Or set PGPASSWORD in the script (less secure).

2. "pg_dump: command not found"

pg_dump lives in a directory that isn't in cron's PATH (e.g., /usr/pgsql-15/bin).

• Solution: Use the full path in your script, e.g., /usr/pgsql-15/bin/pg_dump.

3. Cron jobs don't run at all

Check the cron service status and your crontab syntax.

systemctl status crond   # or cron
crontab -l

Also confirm the script is executable and starts with a shebang (#!/bin/bash).

4. Backup file is 0 bytes

The dump failed during the command, but the file was created. Always check the exit code before celebrating.

• Solution: Use the if [ $? -eq 0 ] pattern in your script, and log errors.

5. Timezone surprises

Cron uses the system timezone, which may differ from your application's timezone. You can set CRON_TZ at the top of the crontab.

CRON_TZ=America/New_York
0 2 * * * /home/opc/backup.sh

6. Disk fills up due to too many backups

The cleanup command failed or the find pattern is wrong.

• Solution: Test find with -print before adding -delete. Set KEEP_DAYS lower if needed.

What you learned & what's next

You've learned how to schedule backups with cron and pg_dump — a reliable, automated way to protect your PostgreSQL data. You now understand the core concept (cron as the scheduler, pg_dump as the snapshot tool), the step-by-step setup (script creation, .pgpass configuration, crontab entry), and how to troubleshoot common issues like missing paths and authentication failures.

You've also seen how this pattern compares to more advanced backup tools, giving you a clear decision path for your own environment.

Now that your backups are running on autopilot, the next logical step is to test your restore process. A backup is only as good as its ability to be restored. Check the next lesson in this track to learn how to validate your backups and practice disaster recovery with pg_restore.

Keep your backups scheduled, your data safe, and your confidence high.