Does anyone know of a good way to back up postgres databases?

Question

I have a script that will produce daily rotated backups for mysql, but I can't find anything similar for postgres. I have also discovered that it has an online backup capability, which should come in handy since this is a production site.

Does anyone know of a program/script that will help me, or even a way to do it?

Thanks.

Solution 1

One way is to use pg_dump to generate a flat sql dump, which you can gzip or whatever. This is certainly the easiest option, as the results can be piped back in to psql to re-load a database, and since it can also export as plain text, you can look through or edit the data prior to restore if necessary.

The next method is to temporarily shut down your database (or if your filesystem supports atomic snapshots, in theory that might work) and backup your PostgreSQL data directory.

This page from the PostgreSQL site also explains how to do online backups and point-in-time recovery, which is definitely the most difficult to configure, but also the optimal method. The idea is that you perform a base backup (which you might do every day, couple of days or week) by running some special SQL (pg_start_backup and pg_stop_backup) and make a (filesystem-level) copy of your database directory. The database doesn't go offline during this time, and everything still works as normal. From then on, the database generates a Write Ahead Log (WAL) of any changes, which can then be pushed (automatically, by the database) to wherever you want. To restore, you take the base backup, load it into another database instance, then just replay all the WAL files. This way you can also do point-in-time recovery by not replaying all of the logs.

Solution 2

Here the scripts which you can use for backup postgresql by crontab

http://ithelpblog.com/os/linux/debian/best-way-to-backup-postgresql-database/

Regards, Kevin

Solution 3

For automated backups of both MySQL And Postrgres check out astrails-safe on github (or just "gem install astrails-safe --source=http://gems.github.com"). It uses mysqldump to backup MySQL and pg_dump to backup Postgres. It also knows how to backup plain files with tar and encrypt everything with GnuPG and upload to S3, or any Unix server with SFTP.

Solution 4

Since you specified databaseS, pg_dumpall will be far more useful to you. It dumps all databases and users to a sql file instead of just a single one.

Solution 5

I've just stumbled upon this little neat utility:

http://code.google.com/p/pg-rman/

From the documentation it looks promising, but I yet have to try it.

View more solutions

Comments

[Magnus Hagander]

Using atomic snapshots doesn't just "might work in theory". It works perfectly fine. But they need to be atomic across all filesystems that PostgreSQL has data on, including the pg_xlog directory.

[Stavros Korokithakis]

I usually use pg_dump to do my backups, but I was more looking for a complete solution that used pg_dump to create rotated backups, something like sourceforge.net/projects/automysqlbackup. It would be even better if the script used online backups, but I think it shouldn't be hard to do it myself and put it up on Launchpad or somewhere. Thank you for your answer.

[Vitaly Kushner]

for an automated way of doing pg_dump backups with public key/password encryption, rotation, and upload to S3 check out safe: github.com/astrails/safe