Postgresql daily backup

backuppostgresql

Is there any simple way to backup all your local databases daily (same postgresql server)?
I found some bash script that I can use but they seem very complex for such a job.

Any easy suggestions ?

Best Answer

Since my databases are fairly small I "cheat" and use logrotate to do my postgresql-backups. Something like this in /etc/logrotate.d/postgresql-backup :

/var/backups/postgresql-dump.sql {
        daily
        nomissingok
        rotate 30
        compress
        delaycompress
        ifempty
        create 640 root adm
        dateext
        postrotate
                /usr/bin/sudo -u postgres /usr/bin/pg_dumpall --clean > /var/backups/postgresql-dump.sql
        endscript
}

This gives me a conveniently available complete dump, no more than a day old, and automatically compresses and rotates previous dumps.

Obviously this won't scale with larger databases, but for my purpose it's just about perfect. (I also use BackupPC to handle off-site backups which include the local postgresql-backups)

Related Topic