Dumping and restoring Postgresql
This is useful when moving the data to a new server, or when upgrading.
To dump the whole postgresql: (run on old version of postgresql)
# note that we dump schema and data separately, because that's helpful if there are problems when restoring. # dumping the schema (-c means we add "cleaning" statements, in case we try to restore multiple times etc ...) pg_dumpall -cs > schema.sql # dumping data (-d means use "insert" statements .. this is slow but much more reliable/portable to other db's) pg_dumpall -ad > data.sql
to restore (run on old version of postgresql if upgrading)
su - postgres # we need to give a DB name (even though it will overwrite all), so we just use template1 (default postgresql DB) psql -e < schema.sql template1 psql -e < data.sql template1
If you want to dump just a specific database use the pg_dump instead of pg_dumpall option
after a restore it’s also good to ’clean’ the DB (vacuumdb -a -z)
Back to top
