Backup and restore All Versions

Examples

• 0

  Backing up one database

  edit move example delete share

  0

  pg_dump -Fc -f DATABASE.pgsql DATABASE
  

  The -Fc selects the "custom backup format" which gives you more power than raw SQL; see pg_restore for more details. If you want a vanilla SQL file, you can do this instead:

  pg_dump -f DATABASE.sql DATABASE
  

  or even

  pg_dump DATABASE > DATABASE.sql
  

  edited Jul 24 at 4:39

  

  
• 0

  Backing up the whole cluster

  edit move example delete share

  0

  $ pg_dumpall -f backup.sql
  

  This works behind the scenes by making multiple connections to the server once for each database and executing pg_dump on it.

  Sometimes, you might be tempted to set this up as a cron job, so you want to see the date the backup was taken as part of the filename:

  $ postgres-backup-$(date +%Y-%m-%d).sql
  

  However, please note that this could produce large files on a daily basis. Postgresql has a much better mechanism for regular backups - WAL archives

  The output from pg_dumpall is sufficient to restore to an identically-configured Postgres instance, but the configuration files in $PGDATA (pg_hba.conf and postgresql.conf) are not part of the backup, so you'll have to back them up separately.

  postgres=# SELECT pg_start_backup('my-backup');
  postgres=# SELECT pg_stop_backup();
  

  To take a filesystem backup, you must use these functions to help ensure that Postgres is in a consistent state while the backup is prepared.

  edited Jul 24 at 4:39

  

  
• 0

  Restoring backups

  edit move example delete share

  0

  psql < backup.sql
  

  A safer alternative uses -1 to wrap the restore in a transaction. The -f specifies the filename rather than using shell redirection.

  psql -1f backup.sql
  

  Custom format files must be restored using pg_restore with the -d option to specify the database:

  pg_restore -d DATABASE DATABASE.pgsql
  

  The custom format can also be converted back to SQL:

  pg_restore backup.pgsql > backup.sql
  

  Usage of the custom format is recommended because you can choose which things to restore and optionally enable parallel processing.

  You may need to do a pg_dump followed by a pg_restore if you upgrade from one postgresql release to a newer one.

  edited Jul 24 at 4:39