Back Up PostgreSQL Daily with Cron
To back up PostgreSQL every day, run pg_dump from cron and write a dated, compressed file. Store credentials in a ~/.pgpass file so no password appears in the crontab or process list.
The crontab line
Run crontab -e as the backup user and add (2 AM daily):
0 2 * * * /usr/bin/pg_dump -U myuser mydb | gzip > /backups/mydb-$(date +\%F).sql.gz
Note: in crontab the % sign must be escaped as \%, otherwise cron cuts the command at the first %.
Pass the password safely
Create ~/.pgpass with a line host:port:db:user:password and chmod 600 ~/.pgpass. pg_dump reads it automatically — no password in the crontab.
Rotate old backups
Add a second line to delete dumps older than 14 days: 0 3 * * * find /backups -name '*.sql.gz' -mtime +14 -delete.
FAQ
What cron command backs up PostgreSQL daily?
0 2 * * * pg_dump -U user db | gzip > /backups/db-$(date +\%F).sql.gz runs at 2 AM. Remember to escape % as \% in crontab.
How do I give pg_dump a password in cron?
Use a ~/.pgpass file (host:port:db:user:password) with chmod 600. pg_dump reads it automatically, keeping the password out of the crontab and process list.
Why does my pg_dump cron line stop at the date?
The % sign has special meaning in crontab. Escape each one as \% so $(date +\%F) works correctly.