Continuing my notes publishing … How to backup/restore your PostgreSQL databases? when you recently get the responsibility of a web app? or you will renew your development environment? or you want to reproduce a production error in your machine? or you want to sleep better at night 😉 ? or ….? whatever.
Before to do anything always we should log as postgres user:
sudo su - postgres |
Then backup one database:
pg_dump database_name > /tmp/database_name.bak |
Or backup them all:
pg_dumpall > /tmp/backup_all.bak |
After that you probably want to download them from server to your machine:
scp your.server.com:/tmp/backup_all.bak . |
And finally you want to restore one database, but you will need to create the database previously:
createdb -T template0 database_name psql database_name < database_name.bak |
Maybe you also need to create a user and give it proper permissions:
psql create role user_name with createdb login password 'user_password'; |
Or restore them all:
psql -f backup_all.bak postgres |
This will create all databases and users for you.