When upgrading or manipulating databases, it’s recommended to create a backup for your databases first.
There is the
pg_dumpall command dumping everything into one plain file. This is a bit old-school since we have
the more flexible Custom format for backups.
My goal is to create a custom format file for every database.
psql -AtU postgres -c "SELECT datname FROM pg_database WHERE NOT datistemplate"| \
while read f;
do pg_dump -Upostgres --format=c --file=$f.sqlc $f;
This creates a custom format dump for every database. Now you’d be able to restore each database individually.
pg_restore -d postgres --clean --create db.sqlc
--clean will delete an existing database with the same name and
--create will create the same database as in the backup.
If you want to restore everything the
pg_dumpall might be more suitable for you.