Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been trying to figure out how I can completely backup my PostgreSQL database. I have not seen any resource that explains how to do a complete backup.

I am aware of the pg_dump and pg_dumpall utilities. The syntax is simple, but the details are not very clear.

It appears that pg_dump only backs "some" tables for a particular db and none of any type of configuration info. I am guessing that things like permissions and many other things are not backed up. So in the event of a total re-install of a PostgreSQL db, the dump backup would not work without significant manual configuration and setup.

pg_dumpall backs-up some more things than pg_dump but again no clear info is provided in the Postgres docs about what else is left out from the backup dump and needs to be backed-up in addition to.

I need to back up all PostgreSQL configuration info, tables, indexes, permissions, schemes, etc. In the event of a complete catastrophe, provided that I reinstall the same version of PostgreSQL I should not have to reconfigure anything.

I don't care for example if the indexes are re-built from scratch of if the backup already stores the indexes... stuff like this doesn't matter to me... I just want the db to be in the exact same state after the restore as it was at time of backup.

I'd appreciate if someone could list the things needed to be backed-up in order to achieve a complete backup. Of course, I'd appreciate if you would recommend some tools along with some procedure for the various things on the list. Please explain which items on the grand list the tool & procedure you recommend covers.

share|improve this question

migrated from stackoverflow.com Feb 28 '12 at 23:27

This question came from our site for professional and enthusiast programmers.

    
You should ask this question on pgsql-general@postgresql.org –  Mike Christensen Feb 28 '12 at 22:55
    
Why? There are many questions about PostgreSQL backup here. In particular about pg_dump and pg_dumpall. They don't cover the aspect I am asking however. –  user440297 Feb 28 '12 at 22:56
add comment

2 Answers 2

Actually, pg_dump backs up all the data, permissions, indexes and whatnot that constitute the database. What is not backed up are the PostgreSQL server's configuration files, globals (roles and tablespaces), and its actual on-disk files and layout.

If you want to back up everything, you should use pg_dump to dump each of your databases (I recommend the custom format), and then grab the config files in your $PGDATA directory: postgresql.conf, pg_hba.conf and pg_ident.conf. If you use tablespaces, you'll need to also keep track of where they link to in the pg_tblspc subdirectory of $PGDATA. I would not use pg_dumpall, it's harder to restore from. The rest of the files in $PGDATA don't need to concern you, since a hot copy will probably look defective to PostgreSQL anyway.

Amendment: you should also do pg_dumpall -g to get your roles and tablespaces backed up as well. Your restore strategy is then as follows:

  1. initdb -D
  2. start up PostgreSQL
  3. psql < (file with output of pg_dumpall -g)
  4. createdb for each database
  5. pg_restore -d (pg_dump file) for each database
share|improve this answer
    
Thanks. A little confused. You say pg_dump backs up all data. I've been seeing references to something called "globals" which apparently pg_dumpall backs up but pg_dump does not. Not sure what "globals" is. –  user440297 Feb 28 '12 at 23:30
    
Good point. Added. –  Daniel Lyons Feb 28 '12 at 23:53
add comment

Given what you ask I would say pg_dump is not the tool you want. You want to do hot copy with point in time recovery which will allow you to take the database back to any specific point in time will all the config files in one place.

Here is what you do, check the configuration you must have enough wal kept for this to work, so if the backup starts at 1am and finishes at 3 am you must still have the WAL from 1am around at 3am (technically there are ways round this, but for simplicity lets go with this).

When your ready to run the backup as a super user run "SELECT pg_start_backup('backup_label');" (you can call it anything really, it does not have to be 'backup_label', this puts the database into backup mode).

When this returns copy all of the data files somewhere (maybe use tar or something to put them into a single archive). In this case all means all, you need to backup the entire base database directory, any tablespaces you have and the WAL/pg_xlog directory (sometimes people put these outside of the main data directory, can help performance). The WAL directory must be backed up last though.

When your done as a super user run "SELECT pg_stop_backup();" (this takes the database out of backup mode).

Restoring is easy just put all the files you copied back in exactly the same location with exactly the same version of Postgres and fire the database up. Postgres will see that its in an inconsistent state and initiate the recovery process which will take you back to a consistent point in time at the end of the backup and as the configuration files are held in the data directory they will be copied too. If you want you can keep more WAL too this allows you to take the database forwards to any point in time you wish. So if your backup was for 3am and you wanted to restore to 6:15 as long as you have all of the WAL from the end of your backup until 6:15 you can use this to advance the database to 6:15. You will need to create a recovery.conf to do this with the settings listed here: http://www.postgresql.org/docs/9.3/static/recovery-target-settings.html

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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