Skip to content

Instantly share code, notes, and snippets.

@mcandre

mcandre/postgresql-backup-and-restore.md

Last active October 6, 2022 22:24
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save mcandre/ea2f82249c71126b8d6605e229b33407 to your computer and use it in GitHub Desktop.
Save mcandre/ea2f82249c71126b8d6605e229b33407 to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL Backup and Restore
Raw
postgresql-backup-and-restore.md

POSTGRESQL BACKUP AND RESTORE

$ pg_dumpall --clean -U <user> -f backup.sql
$ psql -U <user> -f backup.sql

Warnings

  • A PostgreSQL user with administrative privileges is required.
  • Using pg_dump instead of pg_dumpall results in role privileges being reset to nothing.
  • If you forget the --clean / -c flag to pg_dumpall, then the backup SQL script will not be idempotent, and will collide and error with any existing databases.
  • Using pg_restore instead of psql will reject the plain SQL backup format.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment