Dump Databases in PostgreSQL

A database dump contains a record of the table structure and/or the data from a database and is usually in the form of a list of SQL queries. You can dump the database if you want to preserve information from databases and if you have problems with your database you can restore all data from the dump. In PostgreSQL you can use pg_dump command:
pg_dump dbname > outfile .

To restore the file, use:
psql dbname < infile.

Infile is the same file with the output file from the dump command.

pg_dump will not block users to access the database. One of the features of this command is that it can output the dump in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications even on other SQL database products.

If you want to dump a database from one server to another, use this command:
pg_dump -h host1 dbname | psql -h host2 dbname

With pg_dump command you can dump only one database at a time. If you want to dump more than one database, use pg_dumpall command. pg_dumpall backs up each database in a given cluster and also preserves cluster-wide data such as role and tablespace definitions.

To dump the databases use:
pg_dumpall > outfile

and to restore the databases use
psql -f infile postgres.

When you restore the databases, you should have superuser access and that is required to restore the role and tablespace information.

When dumping a large database it can be problematic because the dimension of the dump file can be bigger than the maximum size permitted by your system. For that you can use a few workarounds:

- use compressed dumps: pg_dump dbname | gzip > filename.gz

Restore the dump with:
createdb dbname
gunzip -c filename.gz | psql dbname

- use split command that allows to divide the dump file into small pieces:
pg_dump dbname | split -b 3m - filename

With this command you will split the dump file into 3MB files. Restore the dump file with:
createdb dbname
cat filename* | psql dbname