Database Administration - PostgreSQL: Up and Running - page 38

contains functions that didn't make it into the core. In particular, you need this
extension if you relied on the xlst_process function for processing XSL templates.
There are also a couple of old XPath functions only found in xml2.
Backup and Restore
PostgreSQL ships with two utilities for backup: pg_dump and pg_dumpall . You'll find
both in the bin folder. Use pg_dump to back up specific databases and pg_dumpall to
back up all databases and server globals. pg_dumpall needs to run under a superuser
account so that it has access to back up all databases. Most of the command-line options
for these tools exist both in GNU style (two hyphens plus word) and the traditional
single-letter style (one hyphen plus alphabetic character). You can use them inter‐
changeably, even in the same command. We'll be covering just the basics here; for a
more in-depth discussion, see the PostgreSQL documentation Backup and Restore .
As you wade through this section, you'll find that we often specify the port and host in
our examples. This is because we often run them via scheduled jobs ( pg_agent ) on a
different machine or we have several instances of PostgreSQL running on the same
machine, each running on a different port. Sometimes specifying the -h ( --host ) option
can cause problems if your service is set to listen only on local . You can safely leave
out the host if you are running the examples directly on the server.
You may also want to create a ~/.pgpass file to store all passwords. pg_dump and pg_dump
all don't have password options. Alternatively, you can set a password in the PGPASS
WORD environment variable.
Selective Backup Using pg_dump
For day-to-day backup, pg_dump is more expeditious than pg_dumpall because it can
selectively back up tables, schemas, and databases. pg_dump backs up to plain SQL, but
also compressed and TAR formats. Compressed and TAR backups can take advantage
of the parallel restore feature introduced in version 8.4. Because we believe you'll be
using pg_dump as part of your daily regimen, we have included a full dump of the help
in “Database Backup Using pg_dump” on page 195 so you can see the myriad of switches
in a single glance.
The next example shows a few common backup scenarios and corresponding pg_dump
options. They should work for any version of PostgreSQL.
To create a compressed, single database backup:
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb .backup mydb
To create a plain-text single database backup, including a CREATE DATABASE statement:
pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb .backup mydb