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.