Backup and Restore Postgres Filles


 Been long since I use mysql, ever since I tried postgres, I'm hooked. Postgres got so many feature and more stable, to me at least. JSON field, HStore, and no more stupid corrupt table. In my Mysql time, I had to recreate index on my innodb table so many times because sometimes its broken without knowing why. Postgres sub queries also is better and PostGIS is a win!

 But I do miss that easier replication on Mysql thought, well..who cares? Django can use multi db.

 So, to backup Postgres, I usually make a cron using pg_dumpall and upload it on my backup server.

Backup and restore single database, eg: Klinik

$ pg_dump klinik > klinik.sql

But, I usually gzip the file because my database can get to several GB.

$ pg_dump klinik | gzip -c > klinik.sql.gz

To restore it :

$ gunzip klinik.sql.gz # if you gzip it
$ psql -d klinik -f klinik.sql

or the lazy way :

$ psql klinik < klinik.sql

Backup and restore all database

to backup:

$ pg_dumpall > alldb.sql

or backup and gzip,

$ pg_dumpall | gzip -c > alldb.sql.gz

to restore it

$ psql -f alldb.sql postgres

Backup and restore from different server

If you dont want to keep the sql files, you can always import it from another server eg: from webserver host to localhost

$ pg_dump -h webserver klinik | psql -h localhost klinik

for more updated syntax/feature, you can always check official manual at :

Share this Post:

Related Posts: