Incremental backup of postgresql database

Question

We run a database server running PostgreSQL with database size of around 130GB growing at a rate of 500MB per day. We use HP Data Protector software to take backups of the database.

The issue here is that our database is on a GFS mounted partition and the current backup software license does not facilitate taking backups from GFS, NFS mounted partitions.

So, as a workaround, we planned to copy the database to a local partition using pg_dump utility and take backups from the local partition using data protector software. But I am not sure whether we can take incremental backups using pg_dump. Full backup everyday is not possible for us.

Please suggest steps for taking incremental backup of a PostgreSQL database.

Answer 1

The best method to incremental backup Postgres is to use periodic hot physical backups and continuous WAL archiving.

http://www.postgresql.org/docs/9.1/static/continuous-archiving.html

The periodic physical backup can be done with rsync using the --copy-dest= option to take advantage of any previous copy reusing unchanged files.

This is a very effective way of backing up Postgres and can also be used to do Point In Time Recovery (PITR) in case of disaster.

Answer 2

I'd take a look at this SO Q&A titled: Best method for postgres incremental backup.

excerpt of accepted answer

Update: Check out Barman for an easier way to set up WAL archiving for backup.

You can use PostgreSQL's continuous WAL archiving method. First you need to set wal_level=archive, then do a full filesystem-level backup (between issuing pg_start_backup() and pg_stop_backup() commands) and then just copy over newer WAL files by configuring the archive_commandoption.

See that Q&A's answer for a run down of the advantages & disadvantages of this method.