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.


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 <new data directory>
  2. start up PostgreSQL
  3. psql < (file with output of pg_dumpall -g)
  4. createdb for each database
  5. pg_restore -d <database> <pg_dump file> for each database

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

  1. 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.
  2. 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).


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

  • Isn't there pg_basebackup?
    – Asclepius
    Commented Nov 7, 2014 at 22:39
  • Yes your correct, and that may work depending on your intentions. pg_basebackup will put the database into backup mode and pull all of the data files, config and WAL to a new location, however if you want to do something a bit clever like tar and gzip the backup it will only work if you have one data directory (ie no extra tablespaces), you can see more here link look for --format Commented Nov 8, 2014 at 23:50

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