PostgreSQL Database Backup Takes Too Long

While giving a training course on PostgreSQL, the problem arose that a pg_dump of their database would take too long. The database contains more or less nothing else than BLOBs (PDF files). Currently only 500MB, but there are 45GB of archived files (from the last 10 years or so) and new ones will be added on a daily basis. So at least 50GB (older files could be removed) will be stored in the database. Doing a test dump with pg_dump takes approximately 3 minutes for 500MB (on the given system) - which means 50GB will take somewhere around 5 hours. That is definitely too long, as the backup will need to run every night and additional file system backups (with IBM Tivoli) need to be performed as well.

Taggings:

Submitted by Philipp Krenn on Sun, 11/02/2008 - 17:18
1 answer

File System Level Backup

The solution we found where file system level backups, they are faster by a factor of 5 to 10.

These have two restrictions:

  • The database server must be shut down while performing the backup. This is no problem as the database is only needed during office hours, so shutting down the server at 22:00, performing the backup, and restarting the server is no problem.
  • Only full backups and restorations are possible - partially restoring data does not work. - Not a problem as there is little daily activity except documents being added. The files themselves are very important (health insurance data), but if half a day is lost the files can be added again the next day and nothing important is lost.

    • This method can be extended by creating incremental backups (rsync is your friend) or creating snapshots, if the operating system permits it (ZFS has been reported to work well).

Submitted by Philipp Krenn on Sat, 11/15/2008 - 16:59

Taggings: