Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Watch 1
Star 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branch: master
Branches Tags
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/backup-dump.html

238 lines
18KB
Raw Permalink Blame History 

  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 V1.79.1" /><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><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">25.1. <acronym xmlns="http://www.w3.org/1999/xhtml" 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 12.4 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></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</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">pg_dump</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</h3></div></div></div><p>

  64.     Text files created by <span class="application">pg_dump</span> are intended to

  65.     be read in by the <span class="application">psql</span> program. The

  66.     general command form to restore a dump is

  67. </p><pre class="synopsis">

  68. psql <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>).  <span class="application">psql</span>

  75.     supports options similar to <span class="application">pg_dump</span> for specifying

  76.     the database server to connect to and the user name to use. See

  77.     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.

  78.     Non-text file dumps are restored using the <a class="xref" href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle">pg_restore</span></a> utility.

  79.    </p><p>

  80.     Before restoring an SQL dump, all the users who own objects or were

  81.     granted permissions on objects in the dumped database must already

  82.     exist. If they do not, the restore will fail to recreate the

  83.     objects with the original ownership and/or permissions.

  84.     (Sometimes this is what you want, but usually it is not.)

  85.    </p><p>

  86.     By default, the <span class="application">psql</span> script will continue to

  87.     execute after an SQL error is encountered. You might wish to run

  88.     <span class="application">psql</span> with

  89.     the <code class="literal">ON_ERROR_STOP</code> variable set to alter that

  90.     behavior and have <span class="application">psql</span> exit with an

  91.     exit status of 3 if an SQL error occurs:

  92. </p><pre class="programlisting">

  93. psql --set ON_ERROR_STOP=on <em class="replaceable"><code>dbname</code></em> &lt; <em class="replaceable"><code>dumpfile</code></em>

  94. </pre><p>

  95.     Either way, you will only have a partially restored database.

  96.     Alternatively, you can specify that the whole dump should be

  97.     restored as a single transaction, so the restore is either fully

  98.     completed or fully rolled back. This mode can be specified by

  99.     passing the <code class="option">-1</code> or <code class="option">--single-transaction</code>

  100.     command-line options to <span class="application">psql</span>. When using this

  101.     mode, be aware that even a minor error can rollback a

  102.     restore that has already run for many hours. However, that might

  103.     still be preferable to manually cleaning up a complex database

  104.     after a partially restored dump.

  105.    </p><p>

  106.     The ability of <span class="application">pg_dump</span> and <span class="application">psql</span> to

  107.     write to or read from pipes makes it possible to dump a database

  108.     directly from one server to another, for example:

  109. </p><pre class="programlisting">

  110. pg_dump -h <em class="replaceable"><code>host1</code></em> <em class="replaceable"><code>dbname</code></em> | psql -h <em class="replaceable"><code>host2</code></em> <em class="replaceable"><code>dbname</code></em>

  111. </pre><p>

  112.    </p><div class="important"><h3 class="title">Important</h3><p>

  113.      The dumps produced by <span class="application">pg_dump</span> are relative to

  114.      <code class="literal">template0</code>. This means that any languages, procedures,

  115.      etc. added via <code class="literal">template1</code> will also be dumped by

  116.      <span class="application">pg_dump</span>. As a result, when restoring, if you are

  117.      using a customized <code class="literal">template1</code>, you must create the

  118.      empty database from <code class="literal">template0</code>, as in the example

  119.      above.

  120.     </p></div><p>

  121.     After restoring a backup, it is wise to run <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> on each

  122.     database so the query optimizer has useful statistics;

  123.     see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="24.1.3. Updating Planner Statistics">Section 24.1.3</a>

  124.     and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="24.1.6. The Autovacuum Daemon">Section 24.1.6</a> for more information.

  125.     For more advice on how to load large amounts of data

  126.     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>.

  127.    </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></h3></div></div></div><p>

  128.     <span class="application">pg_dump</span> dumps only a single database at a time,

  129.     and it does not dump information about roles or tablespaces

  130.     (because those are cluster-wide rather than per-database).

  131.     To support convenient dumping of the entire contents of a database

  132.     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.

  133.     <span class="application">pg_dumpall</span> backs up each database in a given

  134.     cluster, and also preserves cluster-wide data such as role and

  135.     tablespace definitions. The basic usage of this command is:

  136. </p><pre class="synopsis">

  137. pg_dumpall &gt; <em class="replaceable"><code>dumpfile</code></em>

  138. </pre><p>

  139.     The resulting dump can be restored with <span class="application">psql</span>:

  140. </p><pre class="synopsis">

  141. psql -f <em class="replaceable"><code>dumpfile</code></em> postgres

  142. </pre><p>

  143.     (Actually, you can specify any existing database name to start from,

  144.     but if you are loading into an empty cluster then <code class="literal">postgres</code>

  145.     should usually be used.)  It is always necessary to have

  146.     database superuser access when restoring a <span class="application">pg_dumpall</span>

  147.     dump, as that is required to restore the role and tablespace information.

  148.     If you use tablespaces, make sure that the tablespace paths in the

  149.     dump are appropriate for the new installation.

  150.    </p><p>

  151.     <span class="application">pg_dumpall</span> works by emitting commands to re-create

  152.     roles, tablespaces, and empty databases, then invoking

  153.     <span class="application">pg_dump</span> for each database.  This means that while

  154.     each database will be internally consistent, the snapshots of

  155.     different databases are not synchronized.

  156.    </p><p>

  157.     Cluster-wide data can be dumped alone using the

  158.     <span class="application">pg_dumpall</span> <code class="option">--globals-only</code> option.

  159.     This is necessary to fully backup the cluster if running the

  160.     <span class="application">pg_dump</span> command on individual databases.

  161.    </p></div><div class="sect2" id="BACKUP-DUMP-LARGE"><div class="titlepage"><div><div><h3 class="title">25.1.3. Handling Large Databases</h3></div></div></div><p>

  162.     Some operating systems have maximum file size limits that cause

  163.     problems when creating large <span class="application">pg_dump</span> output files.

  164.     Fortunately, <span class="application">pg_dump</span> can write to the standard

  165.     output, so you can use standard Unix tools to work around this

  166.     potential problem.  There are several possible methods:

  167.    </p><p><strong>Use compressed dumps. </strong>

  168.      You can use your favorite compression program, for example

  169.      <span class="application">gzip</span>:

  170. 

  171. </p><pre class="programlisting">

  172. pg_dump <em class="replaceable"><code>dbname</code></em> | gzip &gt; <em class="replaceable"><code>filename</code></em>.gz

  173. </pre><p>

  174. 

  175.      Reload with:

  176. 

  177. </p><pre class="programlisting">

  178. gunzip -c <em class="replaceable"><code>filename</code></em>.gz | psql <em class="replaceable"><code>dbname</code></em>

  179. </pre><p>

  180. 

  181.      or:

  182. 

  183. </p><pre class="programlisting">

  184. cat <em class="replaceable"><code>filename</code></em>.gz | gunzip | psql <em class="replaceable"><code>dbname</code></em>

  185. </pre><p>

  186.     </p><p><strong>Use <code class="command">split</code>. </strong>

  187.      The <code class="command">split</code> command

  188.      allows you to split the output into smaller files that are

  189.      acceptable in size to the underlying file system. For example, to

  190.      make chunks of 1 megabyte:

  191. 

  192. </p><pre class="programlisting">

  193. pg_dump <em class="replaceable"><code>dbname</code></em> | split -b 1m - <em class="replaceable"><code>filename</code></em>

  194. </pre><p>

  195. 

  196.      Reload with:

  197. 

  198. </p><pre class="programlisting">

  199. cat <em class="replaceable"><code>filename</code></em>* | psql <em class="replaceable"><code>dbname</code></em>

  200. </pre><p>

  201.     </p><p><strong>Use <span class="application">pg_dump</span>'s custom dump format. </strong>

  202.      If <span class="productname">PostgreSQL</span> was built on a system with the

  203.      <span class="application">zlib</span> compression library installed, the custom dump

  204.      format will compress data as it writes it to the output file. This will

  205.      produce dump file sizes similar to using <code class="command">gzip</code>, but it

  206.      has the added advantage that tables can be restored selectively. The

  207.      following command dumps a database using the custom dump format:

  208. 

  209. </p><pre class="programlisting">

  210. pg_dump -Fc <em class="replaceable"><code>dbname</code></em> &gt; <em class="replaceable"><code>filename</code></em>

  211. </pre><p>

  212. 

  213.      A custom-format dump is not a script for <span class="application">psql</span>, but

  214.      instead must be restored with <span class="application">pg_restore</span>, for example:

  215. 

  216. </p><pre class="programlisting">

  217. pg_restore -d <em class="replaceable"><code>dbname</code></em> <em class="replaceable"><code>filename</code></em>

  218. </pre><p>

  219. 

  220.      See the <a class="xref" href="app-pgdump.html" title="pg_dump"><span class="refentrytitle">pg_dump</span></a> and <a class="xref" href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle">pg_restore</span></a> reference pages for details.

  221.     </p><p>

  222.     For very large databases, you might need to combine <code class="command">split</code>

  223.     with one of the other two approaches.

  224.    </p><p><strong>Use <span class="application">pg_dump</span>'s parallel dump feature. </strong>

  225.      To speed up the dump of a large database, you can use

  226.      <span class="application">pg_dump</span>'s parallel mode. This will dump

  227.      multiple tables at the same time. You can control the degree of

  228.      parallelism with the <code class="command">-j</code> parameter. Parallel dumps

  229.      are only supported for the "directory" archive format.

  230. 

  231. </p><pre class="programlisting">

  232. 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>

  233. </pre><p>

  234. 

  235.      You can use <code class="command">pg_restore -j</code> to restore a dump in parallel.

  236.      This will work for any archive of either the "custom" or the "directory"

  237.      archive mode, whether or not it has been created with <code class="command">pg_dump -j</code>.

  238.     </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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="backup.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="backup-file.html">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">Home</a></td><td width="40%" align="right" valign="top"> 25.2. File System Level Backup</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1