Backup and Restore PostgreSQL database
I am not quite sure why phpPGAdmin doesn’t have “import” feature and only have “export”, but… who cares?
Dump Individual DBs with pg_dump
PostgreSQL provides the utility program pg_dump for dumping individual DBs:
pg_dump dbname > outfile
pg_dump writes its results to the standard output.
pg_dump is a regular PostgreSQL client application.
This means that you can do this backup procedure from any remote host that has access to the DB.
pg_dump does not operate with special permissions. You must have read access to all tables that you want to back up.
Large objects (blobs) are not dumped by default.
Restoring the dump
The text files created by pg_dump are intended to be read in by the psql program:
psql dbname < infile
Note: The DB dbname will not be created by this command, you must create it yourself before.
psql and pg_dump support options for controlling the DB server location and the user names.
Tricks for large DBs:
Compressed dumps:
pg_dump dbname | gzip > filename.gz
Reload with
createdb dbname gunzip -c filename.gz | psql dbname
split allows you to split the output into pieces that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte:
pg_dump dbname | split -b 1m - filename
Reload with
createdb dbname cat filename* | psql dbname
Dumping a Complete Cluster with pg_dumpall
pg_dumpall is a utility for dumping all PostgreSQL DBs of a cluster into one file. It also dumps global objects that are common to all DBs: pg_dumpall > outfile The resulting dumps can be restored with psql.
Example “dump and reload all DBs”:
$ pg_dumpall >cluster.dump
Reload the DBs (into an empty cluster space as the server will not accept duplicate DB name) with:
$ psql -f cluster.dump template1
File system level backup
An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database cluster with whatever method you prefer for doing file system backups, for example:
tar -cf backup.tar /home/fred/databases/postgresql/data
The database server must be shut down before.
It will not work to restore only certain individual tables or databases from their respective files or directories, because the information contained in these files must be combined with the commit log files pg_clog/*, which contain the commit status of all transactions.
The file system backup will likely be larger than an SQL dump, because a pg_dump does not need to dump the contents of indexes for example, just the commands to recreate them.