Difference between revisions of "How To:Backup Databases Through Dumps (the old way)"

From The Open Source Backup Wiki (Amanda, MySQL Backup, BackupPC)
Jump to navigationJump to search
(Added instructions for backing up small PostgreSQL databases and reference to continuous WAL archiving)
 
(No difference)

Revision as of 16:05, 6 January 2011

There are multiple ways to performs PostgreSQL backups using Amanda. Which one you can and should use depends on your situation.

Small DB, plenty of disk space

If you don't have a lot of data (less than a couple of gigabytes) in your PostgreSQL databases, you can simply dump the contents of the databases to a file before the Amanda backup is started. The dump file will be backed up using Amanda.

Note that when using this method a large file will be backed up every backup run.

Here is an example script for backing up the data of a PostgreSQL instance:

#!/bin/sh
# Prevent access to the data in the backup
umask 037
# Remove the backup from the day before yesterday to prevent disk space from filling up
/bin/rm /backups/PostgreSQL/postgresql-8.3-`date --date="2 days ago" +%F`.sql.gz 2>/dev/null
# Dump the contents of all databases
/usr/lib/postgresql/8.3/bin/pg_dumpall --port=5434 | /bin/gzip - > /backups/PostgreSQL/postgresql-8.3-`date +%F`.sql.gz

Add the PostgreSQL data directory to the exclude list to prevent backing up data twice. For example: /var/lib/postgresql/8.3/main

Incremental Backups

If you want a more incremental approach you can use continuous WAL archiving as described on How To:Use Amanda to Back Up PostgreSQL.