Edit Rename Changes History Upload Download Back to Top

pg_dump and pg_dumpall

Dumping

PostgreSQL databases do not have to be shut down in order to dump them.

pg_dumpall | gzip > mydump.gz will dump all data to the file mydump.gz. The dumped data includes schema definitions and user data.

pg_dump -f mydb.dump -Z9 mydb makes a backup of the database called mydb to a file called mydb.dump and compresses the file to the maximum degree.

Restoring from a dumpall dump

When restoring a dumpall dump into completely clean installation of PostgreSQL it is best to temporarily set the pg-hba.conf to allow anyone to connect from anywhere with trust. so the lines in the file (at PostgreSQL 7.4) would be:

local   all         all                                             trust
host    all         all         127.0.0.1         255.255.255.255   trust

Don't leave it like this, though!

While logged in as the PostgreSQL superuser (postgres for Debian sarge):

Restoring from a pg_dump dump

Also, remember to edit the postgresql.conf file and set the tcpip option to true.


Edit Rename Changes History Upload Download Back to Top