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
    
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

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

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

1 Answer

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

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.