PostgreSQL Complete Database Backup 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 &amp; procedure you recommend covers.
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:


initdb -D &lt;new data directory&gt;
start up PostgreSQL
psql &lt; (file with output of pg_dumpall -g)
createdb for each database
pg_restore -d &lt;database&gt; &lt;pg_dump file&gt; for each database


Why would you rather use pg_dump than pg_dumpall for everything? For two basic reasons:


pg_restore has features that depend on the dump being in the custom format. The main one right now is parallel data restoration, which can significantly improve restore speed. pg_dumpall can only produce one big SQL script.
The script produced by pg_dumpall cannot be used to selectively restore databases. The script contains instructions to create this database, change to it, load the data, then create the next database. If you have several databases in your cluster it will be difficult to restore them individually from a script generated from pg_dumpall.


That said, if you have only one database in your cluster the above steps may be too much overhead for the amount you gain. Even a simple backup made with pg_dumpall is better than no backups at all. Just remember to get all the other stuff on disk mentioned above as well (postgresql.conf, pg_hba.conf and whatever changes to the layout you made for tablespaces).