<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <HTML ><HEAD ><TITLE >Backup and Restore</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 7.4.1 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 NAME="creation" CONTENT="2003-12-22T03:48:47"></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 7.4.1 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 <TT CLASS="COMMAND" >pg_dumpall</TT ></A ></DT ><DT >22.1.3. <A HREF="backup.html#BACKUP-DUMP-LARGE" >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="migration.html" >Migration Between Releases</A ></DT ></DL ></DIV ><A NAME="AEN18887" ></A ><P > As 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 two 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 ></UL ><P> </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 <SPAN CLASS="APPLICATION" >pg_dump</SPAN > for this purpose. The basic usage of this command is: </P><PRE CLASS="SYNOPSIS" >pg_dump <VAR CLASS="REPLACEABLE" >dbname</VAR > > <VAR CLASS="REPLACEABLE" >outfile</VAR ></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, you must have read access to all tables that you want to back up, so in practice you almost always have to be a database superuser. </P ><P > To specify which database server <SPAN CLASS="APPLICATION" >pg_dump</SPAN > should contact, use the command line options <VAR CLASS="OPTION" >-h <VAR CLASS="REPLACEABLE" >host</VAR ></VAR > and <VAR CLASS="OPTION" >-p <VAR CLASS="REPLACEABLE" >port</VAR ></VAR >. 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 <VAR CLASS="OPTION" >-U</VAR > 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 <VAR CLASS="OPTION" >-o</VAR > command line option. <SPAN CLASS="QUOTE" >"Large objects"</SPAN > are not dumped by default, either. See <SPAN CLASS="APPLICATION" >pg_dump</SPAN >'s command 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 <VAR CLASS="REPLACEABLE" >dbname</VAR > < <VAR CLASS="REPLACEABLE" >infile</VAR ></PRE ><P> where <VAR CLASS="REPLACEABLE" >infile</VAR > is what you used as <VAR CLASS="REPLACEABLE" >outfile</VAR > for the <TT CLASS="COMMAND" >pg_dump</TT > command. The database <VAR CLASS="REPLACEABLE" >dbname</VAR > 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 <VAR CLASS="REPLACEABLE" >dbname</VAR ></TT >). <SPAN CLASS="APPLICATION" >psql</SPAN > supports similar options to <SPAN CLASS="APPLICATION" >pg_dump</SPAN > for controlling the database server location and the user name. See its reference page for more information. </P ><P > If the objects in the original database were owned by different users, then the dump will instruct <SPAN CLASS="APPLICATION" >psql</SPAN > to connect as each affected user in turn and then create the relevant objects. This way the original ownership is preserved. This also means, however, that all these users must already exist, and furthermore that you must be allowed to connect as each of them. It might therefore be necessary to temporarily relax the client authentication settings. </P ><P > Once restored, it is wise to run <TT CLASS="COMMAND" >ANALYZE</TT > on each database so the optimizer has useful statistics. You can also run <TT CLASS="COMMAND" >vacuumdb -a -z</TT > to <TT CLASS="COMMAND" >ANALYZE</TT > all databases. </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 <VAR CLASS="REPLACEABLE" >host1</VAR > <VAR CLASS="REPLACEABLE" >dbname</VAR > | psql -h <VAR CLASS="REPLACEABLE" >host2</VAR > <VAR CLASS="REPLACEABLE" >dbname</VAR ></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 ><DIV CLASS="TIP" ><BLOCKQUOTE CLASS="TIP" ><P ><B >Tip: </B > Restore performance can be improved by increasing the configuration parameter <VAR CLASS="VARNAME" >sort_mem</VAR > (see <A HREF="runtime-config.html#RUNTIME-CONFIG-RESOURCE-MEMORY" >Section 16.4.2.1</A >). </P ></BLOCKQUOTE ></DIV ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="BACKUP-DUMP-ALL" >22.1.2. Using <TT CLASS="COMMAND" >pg_dumpall</TT ></A ></H2 ><P > The above mechanism is cumbersome and inappropriate when backing up an entire database cluster. For this reason the <SPAN CLASS="APPLICATION" >pg_dumpall</SPAN > 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 call sequence for <SPAN CLASS="APPLICATION" >pg_dumpall</SPAN > is simply </P><PRE CLASS="SYNOPSIS" >pg_dumpall > <VAR CLASS="REPLACEABLE" >outfile</VAR ></PRE ><P> The resulting dump can be restored with <SPAN CLASS="APPLICATION" >psql</SPAN >: </P><PRE CLASS="SYNOPSIS" >psql template1 < <VAR CLASS="REPLACEABLE" >infile</VAR ></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. 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. As <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 <VAR CLASS="REPLACEABLE" >dbname</VAR > | gzip > <VAR CLASS="REPLACEABLE" >filename</VAR >.gz</PRE ><P> Reload with </P><PRE CLASS="PROGRAMLISTING" >createdb <VAR CLASS="REPLACEABLE" >dbname</VAR > gunzip -c <VAR CLASS="REPLACEABLE" >filename</VAR >.gz | psql <VAR CLASS="REPLACEABLE" >dbname</VAR ></PRE ><P> or </P><PRE CLASS="PROGRAMLISTING" >cat <VAR CLASS="REPLACEABLE" >filename</VAR >.gz | gunzip | psql <VAR CLASS="REPLACEABLE" >dbname</VAR ></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 <VAR CLASS="REPLACEABLE" >dbname</VAR > | split -b 1m - <VAR CLASS="REPLACEABLE" >filename</VAR ></PRE ><P> Reload with </P><PRE CLASS="PROGRAMLISTING" >createdb <VAR CLASS="REPLACEABLE" >dbname</VAR > cat <VAR CLASS="REPLACEABLE" >filename</VAR >* | psql <VAR CLASS="REPLACEABLE" >dbname</VAR ></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. For large databases, this will produce similar dump sizes to using <TT CLASS="COMMAND" >gzip</TT >, but has the added advantage that the tables can be restored selectively. The following command dumps a database using the custom dump format: </P><PRE CLASS="PROGRAMLISTING" >pg_dump -Fc <VAR CLASS="REPLACEABLE" >dbname</VAR > > <VAR CLASS="REPLACEABLE" >filename</VAR ></PRE ><P> See the <SPAN CLASS="APPLICATION" >pg_dump</SPAN > and <SPAN CLASS="APPLICATION" >pg_restore</SPAN > 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 > <SPAN CLASS="APPLICATION" >pg_dump</SPAN > (and by implication <SPAN CLASS="APPLICATION" >pg_dumpall</SPAN >) has a few limitations which stem from the difficulty of reconstructing certain information from the system catalogs. </P ><P > Specifically, the order in which <SPAN CLASS="APPLICATION" >pg_dump</SPAN > writes the objects is not very sophisticated. This can lead to problems for example when functions are used as column default values. The only answer is to manually reorder the dump. If you created circular dependencies in your schema then you will have more work to do. </P ><P > For reasons of backward compatibility, <SPAN CLASS="APPLICATION" >pg_dump</SPAN > does not dump large objects by default.<A NAME="AEN19048" ></A > To dump large objects you must use either the custom or the TAR output format, and use the <VAR CLASS="OPTION" >-b</VAR > option in <SPAN CLASS="APPLICATION" >pg_dump</SPAN >. See the reference pages 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 <SPAN CLASS="CITEREFENTRY" ><SPAN CLASS="REFENTRYTITLE" >pg_dump</SPAN ></SPAN > 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 >