Home Explore Blog CI



postgresql

2nd chunk of `doc/src/sgml/backup.sgml`
f5eaf0aa202489d5cca887b2e8b4edaa7aecbe53e9e9a9e10000000100000fae
 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 output can
   generally be re-loaded into newer versions of <productname>PostgreSQL</productname>,
   whereas file-level backups and continuous archiving are both extremely
   server-version-specific.  <application>pg_dump</application> is also the only method
   that will work when transferring a database to a different machine
   architecture, such as going from a 32-bit to a 64-bit server.
  </para>

  <para>
   Dumps created by <application>pg_dump</application> are internally consistent,
   meaning, the dump represents a snapshot of the database at the time
   <application>pg_dump</application> began running. <application>pg_dump</application> 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 most forms of <command>ALTER TABLE</command>.)
  </para>

  <sect2 id="backup-dump-restore">
   <title>Restoring the Dump</title>

   <para>
    Text files created by <application>pg_dump</application> are intended to
    be read by the <application>psql</application> program using its default
    settings. The general command form to restore a text dump is
<synopsis>
psql -X <replaceable class="parameter">dbname</replaceable> &lt; <replaceable class="parameter">dumpfile</replaceable>
</synopsis>
    where <replaceable class="parameter">dumpfile</replaceable> is the
    file output by the <application>pg_dump</application> command. The database <replaceable
    class="parameter">dbname</replaceable> will not be created by this
    command, so you must create it yourself from <literal>template0</literal>
    before executing <application>psql</application> (e.g., with
    <literal>createdb -T template0 <replaceable
    class="parameter">dbname</replaceable></literal>).
    To ensure <application>psql</application> runs with its default settings,
    use the <option>-X</option> (<option>--no-psqlrc</option>) option.
    <application>psql</application>
    supports options similar to <application>pg_dump</application> for specifying
    the database server to connect to and the user name to use. See
    the <xref linkend="app-psql"/> reference page for more information.
   </para>

   <para>
    Non-text file dumps should be restored using the <xref
    linkend="app-pgrestore"/> utility.
   </para>

   <para>
    Before restoring an SQL dump, all the users who own objects or were
    granted permissions on objects in the dumped database must already
    exist. If they do not, 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.)
   </para>

   <para>
    By default, the <application>psql</application> script will continue to
    execute after an SQL error is encountered. You might wish to run
    <application>psql</application> with
    the <literal>ON_ERROR_STOP</literal> variable set to alter that
    behavior and have <application>psql</application>

Title: Restoring PostgreSQL Backups Created with pg_dump
Summary
This section describes how to restore PostgreSQL databases from backups created with pg_dump, including restoring text dumps using psql and non-text dumps using pg_restore, and considerations for user ownership and permissions, as well as error handling during the restoration process.