4

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.

4

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.