Using pg_dump

The pg_dump command is one of the most important commands in PostgreSQL. It can be used to create textual as well as binary backups. Every system administrator will get in touch with this command once in a while. Here is how it works.

Creating textual dumps

Creating a textual dump is how most administrators digging into PostgreSQL get started. This is an essential, yet easy-to-manage, task.

The idea behind pg_dump is simple; it connects to the database that should be saved and transforms the content to a text format. Here is an example of its use:

pg_dump test > /backup/dump.sql

The simplest form of a backup is to send the SQL output created by pg_dump directly to a plain text file.

It is important to mention that a dump is always consistent. Internally, the dump is a large transaction in isolation level repeatable read. A dump represents a snapshot of data, which means that if you start a dump and it takes an hour to complete, then the changes in that one hour will not be included...