jwallace.us

tech, tunes, and other stuff

Backup and Restore a PostgreSQL DB

Very simple.

First you will need to log into as the db admin user account.  On Linux the default database admin user is “postgres”.

To back up:

To back up the database (structure+data):

pg_dump -U postgres DBNAME -f dbname.sql

To back up the database (structure only):

pg_dump -s -U postgres DBNAME -f dbname.sql

To back up the only the structure for a specific schema:

pg_dump -s -U postgres -n schema_name DBNAME -f dbname.sql

To restore:

psql -U postgres -d DBNAME -f ./dbname.sql

Thats all folks!