Dumping and restoring Postgres databases


This is a little cheatsheet on Postgres SQL dumping and restoring commands

Dumping commands

# dump without username
pg_dump DATABASE
 
# dump using a username and database
pg_dump -Uusername -d DATABASE

Restoring comands

# create user if needed (with password)
createuser -P username
 
# drop and create the database
dropdb DATABASE
createdb DATABASE
 
# restore it from .sql dump file
psql --set ON_ERROR_STOP=on DATABASE < INFILE.sql