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
$ 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 : http://www.postgresql.org/docs/8.1/static/backup.html