Book Image

PostgreSQL Administration Essentials

Book Image

PostgreSQL Administration Essentials

Overview of this book

Table of Contents (14 chapters)

Performing backups


Now that you have seen how to import and export data in the PostgreSQL world, it is time to focus your attention on performing basic backups. The backbone of a classical, text-based backup is a program called pg_dump.

Handling pg_dump

The pg_dump is a command-line tool, easy to use, and is capable of extracting a database in a plain text format. Here is how the most simplistic backup of them all works:

$ pg_dump test > /tmp/dump.sql

The dump.sql file will contain a text representation of the database. If you happen to have data in your database, you will see that this data has been exported via COPY.

Note, pg_dump will return data as text. So, all on-board tools of your operating system are at your disposal. Here is how a dump can be compressed easily:

$ pg_dump test | gzip -c > /tmp/dump.sql.gz

Tip

A dump can be done during normal operations. There is no need to shut down the database to extract a dump. Internally, a dump is simply a large transaction in the transaction...