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

Question

I have a script that will generate daily rotating backups for mysql, but I can not find anything like this for postgres. I also found that it has the ability to back up online, which should come in handy as it is a production site.

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

Thanks.

Answer 1

One way is to use pg_dump to create a flat sql dump that you can use gzip or something else. This is by far the easiest option, as the results can be returned back to psql to reload the database, and since it can also be exported as plain text, you can view or edit the data before recovery, if necessary.

The next method is to temporarily close your database (or if your file system supports atomic snapshots, this could theoretically work) and backup your PostgreSQL data directory.

This page from the PostgreSQL website also explains how to do online backup and restore at a specific point in time, which is by far the most difficult to set up, but also the best method. The idea is that you make a basic backup (which you can do every day, a couple of days or a week) by running a special SQL ( pg_start_backup and pg_stop_backup ) and make a copy (at the file system level) of your database, the database does not shut down during this time, and everything is working fine. From this moment, the database generates an Ahead (WAL) log of any changes that can then be pushed (automatically, through the database) to wherever you want. To restore, you take a basic backup, load it into another database instance, and then simply play all the WAL files. In this way, you can also perform recovery at a specific point in time without replaying all the logs.

Answer 2

Here are the scripts you can use to backup postgresql crontab

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

Regards, Kevin

Answer 3

For automated MySQL and Postrgres backups, choose safely for github attacks (or just " gem install astrails-safe --source=http://gems.github.com "). It uses mysqldump for MySQL backup and pg_dump for Postgres backup. He also knows how to backup simple tar files and encrypt everything using GnuPG and upload to S3 or any Unix server with SFTP.

Answer 4

Usually the way to do backups is to use pg_dump.

You should not copy files from the postgresql directory, as in mysql, because most likely you will not be able to use them (these files depend on the architecture, operating system and compilation options).

If pg_dump is not proven enough - this is what you should use. After you are in a situation where pg_dump cannot be used, you should ask yourself: why it cannot be used, and what you can do to use it again :)

When using pg_dump, you can choose a simple dump of the SQL file (-F p) or your own format (-F c). An SQL dump is easier to modify / modify, but the user-defined format is much more powerful and (starting from 8.4) loads faster because you can load it in many parallel workers, rather than sequentially.

Answer 5

Since you specified databaseS, pg_dumpall will be much more useful for you. It removes all the databases and users in the sql file, not just one.

Answer 6

You can also reset the PostgreSQL database using phpPgAdmin or pgAdmin III .

Answer 7

I just stumbled upon this little neat utility:

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

From the documentation, this looks promising, but I still have to try.

Answer 8

This is what I would do to backup the old database and restore

To back up your database

 pg_dump --format=c olddb_name > db_dump_file.dump 

To restore a backup

 pg_restore -v -d newdb_name db_dump_file.dump 

Read more about pg_dump and pg_restore