<!-- doc/src/sgml/backup.sgml -->

<chapter id="backup">
 <title>Backup and Restore</title>

 <indexterm zone="backup"><primary>backup</primary></indexterm>

 <para>
  As with everything that contains valuable data, <productname>PostgreSQL</productname>
  databases should be backed up regularly. While the procedure is
  essentially simple, it is important to have a clear understanding of
  the underlying techniques and assumptions.
 </para>

 <para>
  There are three fundamentally different approaches to backing up
  <productname>PostgreSQL</productname> data:
  <itemizedlist>
   <listitem><para><acronym>SQL</acronym> dump</para></listitem>
   <listitem><para>File system level backup</para></listitem>
   <listitem><para>Continuous archiving</para></listitem>
  </itemizedlist>
  Each has its own strengths and weaknesses; each is discussed in turn
  in the following sections.
 </para>

 <sect1 id="backup-dump">
  <title><acronym>SQL</acronym> Dump</title>

  <para>
   The idea behind this dump method is to generate a file with SQL
   commands that, when fed back to the server, will recreate the
   database in the same state as it was at the time of the dump.
   <productname>PostgreSQL</productname> provides the utility program
   <xref linkend="app-pgdump"/> for this purpose. The basic usage of this
   command is:
<synopsis>
pg_dump <replaceable class="parameter">dbname</replaceable> &gt; <replaceable class="parameter">dumpfile</replaceable>
</synopsis>
   As you see, <application>pg_dump</application> writes its result to the
   standard output. We will see below how this can be useful.
   While the above command creates a text file, <application>pg_dump</application>
   can create files in other formats that allow for parallelism and more
   fine-grained control of object restoration.
  </para>

  <para>
   <application>pg_dump</application> is a regular <productname>PostgreSQL</productname>
   client application (albeit a particularly clever one). This means
   that you can perform this backup procedure from any remote host that has
   access to the database. But remember that <application>pg_dump</application>
   does not operate with special permissions. In particular, it must
   have read access to all tables that you want to back up, so in order
   to back up the entire database you almost always have to run it as a
   database superuser.  (If you do not have sufficient privileges to back up
   the entire database, you can still back up portions of the database to which
   you do have access using options such as
   <option>-n <replaceable>schema</replaceable></option>
   or <option>-t <replaceable>table</replaceable></option>.)
  </para>

  <para>
   To specify which database server <application>pg_dump</application> should
   contact, use the command line options <option>-h
   <replaceable>host</replaceable></option> and <option>-p <replaceable>port</replaceable></option>. The
   default host is the local host or whatever your
   <envar>PGHOST</envar> environment variable specifies. Similarly,
   the default port is indicated by the <envar>PGPORT</envar>
   environment variable or, failing that, by the compiled-in default.
   (Conveniently, the server will normally have the same compiled-in
   default.)
  </para>

  <para>
   Like any other <productname>PostgreSQL</productname> client application,
   <application>pg_dump</application> will by default connect with the database
   user name that is equal to the current operating system user name. To override
   this, either specify the <option>-U</option> option or set the
   environment variable <envar>PGUSER</envar>. Remember that
   <application>pg_dump</application> connections are subject to the normal
   client authentication mechanisms (which are described in <xref
   linkend="client-authentication"/>).
  </para>

  <para>
   An important advantage of <application>pg_dump</application> over the other backup
   methods described later is that <application>pg_dump</application>'s