begriffs open source - ai-pg/blob - full-docs/html/backup-dump.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>25.1. SQL Dump</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="backup.html" title="Chapter 25. Backup and Restore" /><link rel="next" href="backup-file.html" title="25.2. File System Level Backup" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">25.1. <acronym class="acronym">SQL</acronym> Dump</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="backup.html" title="Chapter 25. Backup and Restore">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="backup.html" title="Chapter 25. Backup and Restore">Up</a></td><th width="60%" align="center">Chapter 25. Backup and Restore</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="backup-file.html" title="25.2. File System Level Backup">Next</a></td></tr></table><hr /></div><div class="sect1" id="BACKUP-DUMP"><div class="titlepage"><div><div><h2 class="title" style="clear: both">25.1. <acronym class="acronym">SQL</acronym> Dump <a href="#BACKUP-DUMP" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="backup-dump.html#BACKUP-DUMP-RESTORE">25.1.1. Restoring the Dump</a></span></dt><dt><span class="sect2"><a href="backup-dump.html#BACKUP-DUMP-ALL">25.1.2. Using <span class="application">pg_dumpall</span></a></span></dt><dt><span class="sect2"><a href="backup-dump.html#BACKUP-DUMP-LARGE">25.1.3. Handling Large Databases</a></span></dt></dl></div><p>
   3    The idea behind this dump method is to generate a file with SQL
   4    commands that, when fed back to the server, will recreate the
   5    database in the same state as it was at the time of the dump.
   6    <span class="productname">PostgreSQL</span> provides the utility program
   7    <a class="xref" href="app-pgdump.html" title="pg_dump"><span class="refentrytitle"><span class="application">pg_dump</span></span></a> for this purpose. The basic usage of this
   8    command is:
   9 </p><pre class="synopsis">
  10 pg_dump <em class="replaceable"><code>dbname</code></em> &gt; <em class="replaceable"><code>dumpfile</code></em>
  11 </pre><p>
  12    As you see, <span class="application">pg_dump</span> writes its result to the
  13    standard output. We will see below how this can be useful.
  14    While the above command creates a text file, <span class="application">pg_dump</span>
  15    can create files in other formats that allow for parallelism and more
  16    fine-grained control of object restoration.
  17   </p><p>
  18    <span class="application">pg_dump</span> is a regular <span class="productname">PostgreSQL</span>
  19    client application (albeit a particularly clever one). This means
  20    that you can perform this backup procedure from any remote host that has
  21    access to the database. But remember that <span class="application">pg_dump</span>
  22    does not operate with special permissions. In particular, it must
  23    have read access to all tables that you want to back up, so in order
  24    to back up the entire database you almost always have to run it as a
  25    database superuser.  (If you do not have sufficient privileges to back up
  26    the entire database, you can still back up portions of the database to which
  27    you do have access using options such as
  28    <code class="option">-n <em class="replaceable"><code>schema</code></em></code>
  29    or <code class="option">-t <em class="replaceable"><code>table</code></em></code>.)
  30   </p><p>
  31    To specify which database server <span class="application">pg_dump</span> should
  32    contact, use the command line options <code class="option">-h
  33    <em class="replaceable"><code>host</code></em></code> and <code class="option">-p <em class="replaceable"><code>port</code></em></code>. The
  34    default host is the local host or whatever your
  35    <code class="envar">PGHOST</code> environment variable specifies. Similarly,
  36    the default port is indicated by the <code class="envar">PGPORT</code>
  37    environment variable or, failing that, by the compiled-in default.
  38    (Conveniently, the server will normally have the same compiled-in
  39    default.)
  40   </p><p>
  41    Like any other <span class="productname">PostgreSQL</span> client application,
  42    <span class="application">pg_dump</span> will by default connect with the database
  43    user name that is equal to the current operating system user name. To override
  44    this, either specify the <code class="option">-U</code> option or set the
  45    environment variable <code class="envar">PGUSER</code>. Remember that
  46    <span class="application">pg_dump</span> connections are subject to the normal
  47    client authentication mechanisms (which are described in <a class="xref" href="client-authentication.html" title="Chapter 20. Client Authentication">Chapter 20</a>).
  48   </p><p>
  49    An important advantage of <span class="application">pg_dump</span> over the other backup
  50    methods described later is that <span class="application">pg_dump</span>'s output can
  51    generally be re-loaded into newer versions of <span class="productname">PostgreSQL</span>,
  52    whereas file-level backups and continuous archiving are both extremely
  53    server-version-specific.  <span class="application">pg_dump</span> is also the only method
  54    that will work when transferring a database to a different machine
  55    architecture, such as going from a 32-bit to a 64-bit server.
  56   </p><p>
  57    Dumps created by <span class="application">pg_dump</span> are internally consistent,
  58    meaning, the dump represents a snapshot of the database at the time
  59    <span class="application">pg_dump</span> began running. <span class="application">pg_dump</span> does not
  60    block other operations on the database while it is working.
  61    (Exceptions are those operations that need to operate with an
  62    exclusive lock, such as most forms of <code class="command">ALTER TABLE</code>.)
  63   </p><div class="sect2" id="BACKUP-DUMP-RESTORE"><div class="titlepage"><div><div><h3 class="title">25.1.1. Restoring the Dump <a href="#BACKUP-DUMP-RESTORE" class="id_link">#</a></h3></div></div></div><p>
  64     Text files created by <span class="application">pg_dump</span> are intended to
  65     be read by the <span class="application">psql</span> program using its default
  66     settings. The general command form to restore a text dump is
  67 </p><pre class="synopsis">
  68 psql -X <em class="replaceable"><code>dbname</code></em> &lt; <em class="replaceable"><code>dumpfile</code></em>
  69 </pre><p>
  70     where <em class="replaceable"><code>dumpfile</code></em> is the
  71     file output by the <span class="application">pg_dump</span> command. The database <em class="replaceable"><code>dbname</code></em> will not be created by this
  72     command, so you must create it yourself from <code class="literal">template0</code>
  73     before executing <span class="application">psql</span> (e.g., with
  74     <code class="literal">createdb -T template0 <em class="replaceable"><code>dbname</code></em></code>).
  75     To ensure <span class="application">psql</span> runs with its default settings,
  76     use the <code class="option">-X</code> (<code class="option">--no-psqlrc</code>) option.
  77     <span class="application">psql</span>
  78     supports options similar to <span class="application">pg_dump</span> for specifying
  79     the database server to connect to and the user name to use. See
  80     the <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a> reference page for more information.
  81    </p><p>
  82     Non-text file dumps should be restored using the <a class="xref" href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle"><span class="application">pg_restore</span></span></a> utility.
  83    </p><p>
  84     Before restoring an SQL dump, all the users who own objects or were
  85     granted permissions on objects in the dumped database must already
  86     exist. If they do not, the restore will fail to recreate the
  87     objects with the original ownership and/or permissions.
  88     (Sometimes this is what you want, but usually it is not.)
  89    </p><p>
  90     By default, the <span class="application">psql</span> script will continue to
  91     execute after an SQL error is encountered. You might wish to run
  92     <span class="application">psql</span> with
  93     the <code class="literal">ON_ERROR_STOP</code> variable set to alter that
  94     behavior and have <span class="application">psql</span> exit with an
  95     exit status of 3 if an SQL error occurs:
  96 </p><pre class="programlisting">
  97 psql -X --set ON_ERROR_STOP=on <em class="replaceable"><code>dbname</code></em> &lt; <em class="replaceable"><code>dumpfile</code></em>
  98 </pre><p>
  99     Either way, you will only have a partially restored database.
 100     Alternatively, you can specify that the whole dump should be
 101     restored as a single transaction, so the restore is either fully
 102     completed or fully rolled back. This mode can be specified by
 103     passing the <code class="option">-1</code> or <code class="option">--single-transaction</code>
 104     command-line options to <span class="application">psql</span>. When using this
 105     mode, be aware that even a minor error can rollback a
 106     restore that has already run for many hours. However, that might
 107     still be preferable to manually cleaning up a complex database
 108     after a partially restored dump.
 109    </p><p>
 110     The ability of <span class="application">pg_dump</span> and <span class="application">psql</span> to
 111     write to or read from pipes makes it possible to dump a database
 112     directly from one server to another, for example:
 113 </p><pre class="programlisting">
 114 pg_dump -h <em class="replaceable"><code>host1</code></em> <em class="replaceable"><code>dbname</code></em> | psql -X -h <em class="replaceable"><code>host2</code></em> <em class="replaceable"><code>dbname</code></em>
 115 </pre><p>
 116    </p><div class="important"><h3 class="title">Important</h3><p>
 117      The dumps produced by <span class="application">pg_dump</span> are relative to
 118      <code class="literal">template0</code>. This means that any languages, procedures,
 119      etc. added via <code class="literal">template1</code> will also be dumped by
 120      <span class="application">pg_dump</span>. As a result, when restoring, if you are
 121      using a customized <code class="literal">template1</code>, you must create the
 122      empty database from <code class="literal">template0</code>, as in the example
 123      above.
 124     </p></div><p>
 125     After restoring a backup, it is wise to run <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> on each
 126     database so the query optimizer has useful statistics;
 127     see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="24.1.3. Updating Planner Statistics">Section 24.1.3</a>
 128     and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</a> for more information.
 129     For more advice on how to load large amounts of data
 130     into <span class="productname">PostgreSQL</span> efficiently, refer to <a class="xref" href="populate.html" title="14.4. Populating a Database">Section 14.4</a>.
 131    </p></div><div class="sect2" id="BACKUP-DUMP-ALL"><div class="titlepage"><div><div><h3 class="title">25.1.2. Using <span class="application">pg_dumpall</span> <a href="#BACKUP-DUMP-ALL" class="id_link">#</a></h3></div></div></div><p>
 132     <span class="application">pg_dump</span> dumps only a single database at a time,
 133     and it does not dump information about roles or tablespaces
 134     (because those are cluster-wide rather than per-database).
 135     To support convenient dumping of the entire contents of a database
 136     cluster, the <a class="xref" href="app-pg-dumpall.html" title="pg_dumpall"><span class="refentrytitle"><span class="application">pg_dumpall</span></span></a> program is provided.
 137     <span class="application">pg_dumpall</span> backs up each database in a given
 138     cluster, and also preserves cluster-wide data such as role and
 139     tablespace definitions. The basic usage of this command is:
 140 </p><pre class="synopsis">
 141 pg_dumpall &gt; <em class="replaceable"><code>dumpfile</code></em>
 142 </pre><p>
 143     The resulting dump can be restored with <span class="application">psql</span>:
 144 </p><pre class="synopsis">
 145 psql -X -f <em class="replaceable"><code>dumpfile</code></em> postgres
 146 </pre><p>
 147     (Actually, you can specify any existing database name to start from,
 148     but if you are loading into an empty cluster then <code class="literal">postgres</code>
 149     should usually be used.)  It is always necessary to have
 150     database superuser access when restoring a <span class="application">pg_dumpall</span>
 151     dump, as that is required to restore the role and tablespace information.
 152     If you use tablespaces, make sure that the tablespace paths in the
 153     dump are appropriate for the new installation.
 154    </p><p>
 155     <span class="application">pg_dumpall</span> works by emitting commands to re-create
 156     roles, tablespaces, and empty databases, then invoking
 157     <span class="application">pg_dump</span> for each database.  This means that while
 158     each database will be internally consistent, the snapshots of
 159     different databases are not synchronized.
 160    </p><p>
 161     Cluster-wide data can be dumped alone using the
 162     <span class="application">pg_dumpall</span> <code class="option">--globals-only</code> option.
 163     This is necessary to fully backup the cluster if running the
 164     <span class="application">pg_dump</span> command on individual databases.
 165    </p></div><div class="sect2" id="BACKUP-DUMP-LARGE"><div class="titlepage"><div><div><h3 class="title">25.1.3. Handling Large Databases <a href="#BACKUP-DUMP-LARGE" class="id_link">#</a></h3></div></div></div><p>
 166     Some operating systems have maximum file size limits that cause
 167     problems when creating large <span class="application">pg_dump</span> output files.
 168     Fortunately, <span class="application">pg_dump</span> can write to the standard
 169     output, so you can use standard Unix tools to work around this
 170     potential problem.  There are several possible methods:
 171    </p><p><strong>Use compressed dumps. </strong>
 172      You can use your favorite compression program, for example
 173      <span class="application">gzip</span>:
 174
 175 </p><pre class="programlisting">
 176 pg_dump <em class="replaceable"><code>dbname</code></em> | gzip &gt; <em class="replaceable"><code>filename</code></em>.gz
 177 </pre><p>
 178
 179      Reload with:
 180
 181 </p><pre class="programlisting">
 182 gunzip -c <em class="replaceable"><code>filename</code></em>.gz | psql <em class="replaceable"><code>dbname</code></em>
 183 </pre><p>
 184
 185      or:
 186
 187 </p><pre class="programlisting">
 188 cat <em class="replaceable"><code>filename</code></em>.gz | gunzip | psql <em class="replaceable"><code>dbname</code></em>
 189 </pre><p>
 190     </p><p><strong>Use <code class="command">split</code>. </strong>
 191      The <code class="command">split</code> command
 192      allows you to split the output into smaller files that are
 193      acceptable in size to the underlying file system. For example, to
 194      make 2 gigabyte chunks:
 195
 196 </p><pre class="programlisting">
 197 pg_dump <em class="replaceable"><code>dbname</code></em> | split -b 2G - <em class="replaceable"><code>filename</code></em>
 198 </pre><p>
 199
 200      Reload with:
 201
 202 </p><pre class="programlisting">
 203 cat <em class="replaceable"><code>filename</code></em>* | psql <em class="replaceable"><code>dbname</code></em>
 204 </pre><p>
 205
 206      If using GNU <span class="application">split</span>, it is possible to
 207      use it and <span class="application">gzip</span> together:
 208
 209 </p><pre class="programlisting">
 210 pg_dump <em class="replaceable"><code>dbname</code></em> | split -b 2G --filter='gzip &gt; $FILE.gz'
 211 </pre><p>
 212
 213      It can be restored using <code class="command">zcat</code>.
 214     </p><p><strong>Use <span class="application">pg_dump</span>'s custom dump format. </strong>
 215      If <span class="productname">PostgreSQL</span> was built on a system with the
 216      <span class="application">zlib</span> compression library installed, the custom dump
 217      format will compress data as it writes it to the output file. This will
 218      produce dump file sizes similar to using <code class="command">gzip</code>, but it
 219      has the added advantage that tables can be restored selectively. The
 220      following command dumps a database using the custom dump format:
 221
 222 </p><pre class="programlisting">
 223 pg_dump -Fc <em class="replaceable"><code>dbname</code></em> &gt; <em class="replaceable"><code>filename</code></em>
 224 </pre><p>
 225
 226      A custom-format dump is not a script for <span class="application">psql</span>, but
 227      instead must be restored with <span class="application">pg_restore</span>, for example:
 228
 229 </p><pre class="programlisting">
 230 pg_restore -d <em class="replaceable"><code>dbname</code></em> <em class="replaceable"><code>filename</code></em>
 231 </pre><p>
 232
 233      See the <a class="xref" href="app-pgdump.html" title="pg_dump"><span class="refentrytitle"><span class="application">pg_dump</span></span></a> and <a class="xref" href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle"><span class="application">pg_restore</span></span></a> reference pages for details.
 234     </p><p>
 235     For very large databases, you might need to combine <code class="command">split</code>
 236     with one of the other two approaches.
 237    </p><p><strong>Use <span class="application">pg_dump</span>'s parallel dump feature. </strong>
 238      To speed up the dump of a large database, you can use
 239      <span class="application">pg_dump</span>'s parallel mode. This will dump
 240      multiple tables at the same time. You can control the degree of
 241      parallelism with the <code class="command">-j</code> parameter. Parallel dumps
 242      are only supported for the "directory" archive format.
 243
 244 </p><pre class="programlisting">
 245 pg_dump -j <em class="replaceable"><code>num</code></em> -F d -f <em class="replaceable"><code>out.dir</code></em> <em class="replaceable"><code>dbname</code></em>
 246 </pre><p>
 247
 248      You can use <code class="command">pg_restore -j</code> to restore a dump in parallel.
 249      This will work for any archive of either the "custom" or the "directory"
 250      archive mode, whether or not it has been created with <code class="command">pg_dump -j</code>.
 251     </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="backup.html" title="Chapter 25. Backup and Restore">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="backup.html" title="Chapter 25. Backup and Restore">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="backup-file.html" title="25.2. File System Level Backup">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 25. Backup and Restore </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 25.2. File System Level Backup</td></tr></table></div></body></html>