Show Databases
$ psql -U postgres -l
Make a backup using pg_dump
$ pg_dump -U postgres sample > sample.dump.sql
or to compress the database during export you can also do
$ pg_dump -U postgres sample | gzip -c > sample.dump.sql.gz
Restore database
$ gunzip sample.dump.sql.gz $ psql -U postgres -d sample -f sample.dump.sql
Dump all databases
Another option is to use the pg_dumpall command. As the name suggests, it dumps each database and preserves cluster-wide data such as users and groups. You can use it as follows:
$ pg_dumpall -U postgres > all.dbs.sql
or
$ pg_dumpall -U postgres | gzip -c > all.dbs.sql.gz
Restore all databases
$ psql -U postgres -f all.dbs.sql postgres
No tags