<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Backup and Restore</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.0.11 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Server Administration"
HREF="admin.html"><LINK
REL="PREVIOUS"
TITLE="Log File Maintenance"
HREF="logfile-maintenance.html"><LINK
REL="NEXT"
TITLE="File system level backup"
HREF="backup-file.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2007-02-02T03:57:22"></HEAD
><BODY
CLASS="CHAPTER"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.0.11 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="logfile-maintenance.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="maintenance.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="monitoring.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="backup-file.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="CHAPTER"
><H1
><A
NAME="BACKUP"
></A
>Chapter 22. Backup and Restore</H1
><DIV
CLASS="TOC"
><DL
><DT
><B
>Table of Contents</B
></DT
><DT
>22.1. <A
HREF="backup.html#BACKUP-DUMP"
><ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Dump</A
></DT
><DD
><DL
><DT
>22.1.1. <A
HREF="backup.html#BACKUP-DUMP-RESTORE"
>Restoring the dump</A
></DT
><DT
>22.1.2. <A
HREF="backup.html#BACKUP-DUMP-ALL"
>Using <SPAN
CLASS="APPLICATION"
>pg_dumpall</SPAN
></A
></DT
><DT
>22.1.3. <A
HREF="backup.html#BACKUP-DUMP-LARGE"
>Handling large databases</A
></DT
><DT
>22.1.4. <A
HREF="backup.html#BACKUP-DUMP-CAVEATS"
>Caveats</A
></DT
></DL
></DD
><DT
>22.2. <A
HREF="backup-file.html"
>File system level backup</A
></DT
><DT
>22.3. <A
HREF="backup-online.html"
>On-line backup and point-in-time recovery (PITR)</A
></DT
><DD
><DL
><DT
>22.3.1. <A
HREF="backup-online.html#BACKUP-ARCHIVING-WAL"
>Setting up WAL archiving</A
></DT
><DT
>22.3.2. <A
HREF="backup-online.html#BACKUP-BASE-BACKUP"
>Making a Base Backup</A
></DT
><DT
>22.3.3. <A
HREF="backup-online.html#BACKUP-PITR-RECOVERY"
>Recovering with an On-line Backup</A
></DT
><DT
>22.3.4. <A
HREF="backup-online.html#BACKUP-TIMELINES"
>Timelines</A
></DT
><DT
>22.3.5. <A
HREF="backup-online.html#BACKUP-ONLINE-CAVEATS"
>Caveats</A
></DT
></DL
></DD
><DT
>22.4. <A
HREF="migration.html"
>Migration Between Releases</A
></DT
></DL
></DIV
><A
NAME="AEN21280"
></A
><P
> As with everything that contains valuable data, <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
databases should be backed up regularly. While the procedure is
essentially simple, it is important to have a basic understanding of
the underlying techniques and assumptions.
</P
><P
> There are three fundamentally different approaches to backing up
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> data:
<P
></P
></P><UL
><LI
><P
><ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> dump</P
></LI
><LI
><P
>File system level backup</P
></LI
><LI
><P
>On-line backup</P
></LI
></UL
><P>
Each has its own strengths and weaknesses.
</P
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="BACKUP-DUMP"
>22.1. <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Dump</A
></H1
><P
> The idea behind the SQL-dump method is to generate a text 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.
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> provides the utility program
<A
HREF="app-pgdump.html"
>pg_dump</A
> for this purpose. The basic usage of this
command is:
</P><PRE
CLASS="SYNOPSIS"
>pg_dump <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
> > <TT
CLASS="REPLACEABLE"
><I
>outfile</I
></TT
></PRE
><P>
As you see, <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> writes its results to the
standard output. We will see below how this can be useful.
</P
><P
> <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> is a regular <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
client application (albeit a particularly clever one). This means
that you can do this backup procedure from any remote host that has
access to the database. But remember that <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
>
does not operate with special permissions. In particular, it must
have read access to all tables that you want to back up, so in
practice you almost always have to run it as a database superuser.
</P
><P
> To specify which database server <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> should
contact, use the command line options <TT
CLASS="OPTION"
>-h
<TT
CLASS="REPLACEABLE"
><I
>host</I
></TT
></TT
> and <TT
CLASS="OPTION"
>-p <TT
CLASS="REPLACEABLE"
><I
>port</I
></TT
></TT
>. The
default host is the local host or whatever your
<TT
CLASS="ENVAR"
>PGHOST</TT
> environment variable specifies. Similarly,
the default port is indicated by the <TT
CLASS="ENVAR"
>PGPORT</TT
>
environment variable or, failing that, by the compiled-in default.
(Conveniently, the server will normally have the same compiled-in
default.)
</P
><P
> As any other <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> client application,
<SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> 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 <TT
CLASS="OPTION"
>-U</TT
> option or set the
environment variable <TT
CLASS="ENVAR"
>PGUSER</TT
>. Remember that
<SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> connections are subject to the normal
client authentication mechanisms (which are described in <A
HREF="client-authentication.html"
>Chapter 19</A
>).
</P
><P
> Dumps created by <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> are internally consistent,
that is, updates to the database while <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> is
running will not be in the dump. <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> does not
block other operations on the database while it is working.
(Exceptions are those operations that need to operate with an
exclusive lock, such as <TT
CLASS="COMMAND"
>VACUUM FULL</TT
>.)
</P
><DIV
CLASS="IMPORTANT"
><BLOCKQUOTE
CLASS="IMPORTANT"
><P
><B
>Important: </B
> When your database schema relies on OIDs (for instance as foreign
keys) you must instruct <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> to dump the OIDs
as well. To do this, use the <TT
CLASS="OPTION"
>-o</TT
> command line
option. <SPAN
CLASS="QUOTE"
>"Large objects"</SPAN
> are not dumped by default,
either. See <A
HREF="app-pgdump.html"
>pg_dump</A
>'s reference page if you
use large objects.
</P
></BLOCKQUOTE
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="BACKUP-DUMP-RESTORE"
>22.1.1. Restoring the dump</A
></H2
><P
> The text files created by <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> are intended to
be read in by the <SPAN
CLASS="APPLICATION"
>psql</SPAN
> program. The
general command form to restore a dump is
</P><PRE
CLASS="SYNOPSIS"
>psql <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
> < <TT
CLASS="REPLACEABLE"
><I
>infile</I
></TT
></PRE
><P>
where <TT
CLASS="REPLACEABLE"
><I
>infile</I
></TT
> is what
you used as <TT
CLASS="REPLACEABLE"
><I
>outfile</I
></TT
>
for the <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> command. The database <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
> will not be created by this
command, you must create it yourself from <TT
CLASS="LITERAL"
>template0</TT
> before executing
<SPAN
CLASS="APPLICATION"
>psql</SPAN
> (e.g., with <TT
CLASS="LITERAL"
>createdb -T template0
<TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
></TT
>).
<SPAN
CLASS="APPLICATION"
>psql</SPAN
> supports options similar to <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
>
for controlling the database server location and the user name. See
<A
HREF="app-psql.html"
><SPAN
CLASS="APPLICATION"
>psql</SPAN
></A
>'s reference page for more information.
</P
><P
> Not only must the target database already exist before starting to
run the restore, but so must all the users who own objects in the
dumped database or were granted permissions on the objects. If they
do not, then the restore will fail to recreate the objects with the
original ownership and/or permissions. (Sometimes this is what you want,
but usually it is not.)
</P
><P
> Once restored, it is wise to run <A
HREF="sql-analyze.html"
><I
>ANALYZE</I
></A
> on each database so the optimizer has
useful statistics. An easy way to do this is to run
<TT
CLASS="COMMAND"
>vacuumdb -a -z</TT
> to
<TT
CLASS="COMMAND"
>VACUUM ANALYZE</TT
> all databases; this is equivalent to
running <TT
CLASS="COMMAND"
>VACUUM ANALYZE</TT
> manually.
</P
><P
> The ability of <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> and <SPAN
CLASS="APPLICATION"
>psql</SPAN
> to
write to or read from pipes makes it possible to dump a database
directly from one server to another; for example:
</P><PRE
CLASS="PROGRAMLISTING"
>pg_dump -h <TT
CLASS="REPLACEABLE"
><I
>host1</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
> | psql -h <TT
CLASS="REPLACEABLE"
><I
>host2</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
></PRE
><P>
</P
><DIV
CLASS="IMPORTANT"
><BLOCKQUOTE
CLASS="IMPORTANT"
><P
><B
>Important: </B
> The dumps produced by <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> are relative to
<TT
CLASS="LITERAL"
>template0</TT
>. This means that any languages, procedures,
etc. added to <TT
CLASS="LITERAL"
>template1</TT
> will also be dumped by
<SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
>. As a result, when restoring, if you are
using a customized <TT
CLASS="LITERAL"
>template1</TT
>, you must create the
empty database from <TT
CLASS="LITERAL"
>template0</TT
>, as in the example
above.
</P
></BLOCKQUOTE
></DIV
><P
> For advice on how to load large amounts of data into
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> efficiently, refer to <A
HREF="populate.html"
>Section 13.4</A
>.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="BACKUP-DUMP-ALL"
>22.1.2. Using <SPAN
CLASS="APPLICATION"
>pg_dumpall</SPAN
></A
></H2
><P
> The above mechanism is cumbersome and inappropriate when backing
up an entire database cluster. For this reason the <A
HREF="app-pg-dumpall.html"
><SPAN
CLASS="APPLICATION"
>pg_dumpall</SPAN
></A
> program is provided.
<SPAN
CLASS="APPLICATION"
>pg_dumpall</SPAN
> backs up each database in a given
cluster, and also preserves cluster-wide data such as users and
groups. The basic usage of this command is:
</P><PRE
CLASS="SYNOPSIS"
>pg_dumpall > <TT
CLASS="REPLACEABLE"
><I
>outfile</I
></TT
></PRE
><P>
The resulting dump can be restored with <SPAN
CLASS="APPLICATION"
>psql</SPAN
>:
</P><PRE
CLASS="SYNOPSIS"
>psql -f <TT
CLASS="REPLACEABLE"
><I
>infile</I
></TT
> template1</PRE
><P>
(Actually, you can specify any existing database name to start from,
but if you are reloading in an empty cluster then <TT
CLASS="LITERAL"
>template1</TT
>
is the only available choice.) It is always necessary to have
database superuser access when restoring a <SPAN
CLASS="APPLICATION"
>pg_dumpall</SPAN
>
dump, as that is required to restore the user and group information.
</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="BACKUP-DUMP-LARGE"
>22.1.3. Handling large databases</A
></H2
><P
> Since <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> allows tables larger
than the maximum file size on your system, it can be problematic
to dump such a table to a file, since the resulting file will likely
be larger than the maximum size allowed by your system. Since
<SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> can write to the standard output, you can
just use standard Unix tools to work around this possible problem.
</P
><DIV
CLASS="FORMALPARA"
><P
><B
>Use compressed dumps. </B
> You can use your favorite compression program, for example
<SPAN
CLASS="APPLICATION"
>gzip</SPAN
>.
</P><PRE
CLASS="PROGRAMLISTING"
>pg_dump <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
> | gzip > <TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
>.gz</PRE
><P>
Reload with
</P><PRE
CLASS="PROGRAMLISTING"
>createdb <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
>
gunzip -c <TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
>.gz | psql <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
></PRE
><P>
or
</P><PRE
CLASS="PROGRAMLISTING"
>cat <TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
>.gz | gunzip | psql <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
></PRE
><P>
</P
></DIV
><DIV
CLASS="FORMALPARA"
><P
><B
>Use <TT
CLASS="COMMAND"
>split</TT
>. </B
> The <TT
CLASS="COMMAND"
>split</TT
> command
allows you to split the output into pieces that are
acceptable in size to the underlying file system. For example, to
make chunks of 1 megabyte:
</P><PRE
CLASS="PROGRAMLISTING"
>pg_dump <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
> | split -b 1m - <TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
></PRE
><P>
Reload with
</P><PRE
CLASS="PROGRAMLISTING"
>createdb <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
>
cat <TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
>* | psql <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
></PRE
><P>
</P
></DIV
><DIV
CLASS="FORMALPARA"
><P
><B
>Use the custom dump format. </B
> If <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> was built on a system with the
<SPAN
CLASS="APPLICATION"
>zlib</SPAN
> compression library installed, the custom dump
format will compress data as it writes it to the output file. This will
produce dump file sizes similar to using <TT
CLASS="COMMAND"
>gzip</TT
>, but it
has the added advantage that tables can be restored selectively. The
following command dumps a database using the custom dump format:
</P><PRE
CLASS="PROGRAMLISTING"
>pg_dump -Fc <TT
CLASS="REPLACEABLE"
><I
>dbname</I
></TT
> > <TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
></PRE
><P>
A custom-format dump is not a script for <SPAN
CLASS="APPLICATION"
>psql</SPAN
>, but
instead must be restored with <SPAN
CLASS="APPLICATION"
>pg_restore</SPAN
>.
See the <A
HREF="app-pgdump.html"
>pg_dump</A
> and <A
HREF="app-pgrestore.html"
>pg_restore</A
> reference pages for details.
</P
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="BACKUP-DUMP-CAVEATS"
>22.1.4. Caveats</A
></H2
><P
> For reasons of backward compatibility, <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
>
does not dump large objects by default.<A
NAME="AEN21439"
></A
> To dump
large objects you must use either the custom or the tar output
format, and use the <TT
CLASS="OPTION"
>-b</TT
> option in
<SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
>. See the <A
HREF="app-pgdump.html"
>pg_dump</A
> reference
page for details. The
directory <TT
CLASS="FILENAME"
>contrib/pg_dumplo</TT
> of the
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> source tree also contains a program
that can dump large objects.
</P
><P
> Please familiarize yourself with the <A
HREF="app-pgdump.html"
>pg_dump</A
>
reference page.
</P
></DIV
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="logfile-maintenance.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="backup-file.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Log File Maintenance</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="admin.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>File system level backup</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
