summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/app-pgdump.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/app-pgdump.html
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/app-pgdump.html')
-rw-r--r--doc/src/sgml/html/app-pgdump.html824
1 files changed, 824 insertions, 0 deletions
diff --git a/doc/src/sgml/html/app-pgdump.html b/doc/src/sgml/html/app-pgdump.html
new file mode 100644
index 0000000..a27bd9a
--- /dev/null
+++ b/doc/src/sgml/html/app-pgdump.html
@@ -0,0 +1,824 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!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>pg_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="app-pgconfig.html" title="pg_config" /><link rel="next" href="app-pg-dumpall.html" title="pg_dumpall" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center"><span class="application">pg_dump</span></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="app-pgconfig.html" title="pg_config">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="reference-client.html" title="PostgreSQL Client Applications">Up</a></td><th width="60%" align="center">PostgreSQL Client Applications</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="app-pg-dumpall.html" title="pg_dumpall">Next</a></td></tr></table><hr /></div><div class="refentry" id="APP-PGDUMP"><div class="titlepage"></div><a id="id-1.9.4.13.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle"><span class="application">pg_dump</span></span></h2><p>pg_dump —
+ extract a <span class="productname">PostgreSQL</span> database into a script file or other archive file
+ </p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><div class="cmdsynopsis"><p id="id-1.9.4.13.4.1"><code class="command">pg_dump</code> [<em class="replaceable"><code>connection-option</code></em>...] [<em class="replaceable"><code>option</code></em>...] [<em class="replaceable"><code>dbname</code></em>]</p></div></div><div class="refsect1" id="PG-DUMP-DESCRIPTION"><h2>Description</h2><p>
+ <span class="application">pg_dump</span> is a utility for backing up a
+ <span class="productname">PostgreSQL</span> database. It makes consistent
+ backups even if the database is being used concurrently.
+ <span class="application">pg_dump</span> does not block other users
+ accessing the database (readers or writers).
+ </p><p>
+ <span class="application">pg_dump</span> only dumps a single database.
+ To back up an entire cluster, or to back up global objects that are
+ common to all databases in a cluster (such as roles and tablespaces),
+ use <a class="xref" href="app-pg-dumpall.html" title="pg_dumpall"><span class="refentrytitle"><span class="application">pg_dumpall</span></span></a>.
+ </p><p>
+ Dumps can be output in script or archive file formats. Script
+ dumps are plain-text files containing the SQL commands required
+ to reconstruct the database to the state it was in at the time it was
+ saved. To restore from such a script, feed it to <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a>. Script files
+ can be used to reconstruct the database even on other machines and
+ other architectures; with some modifications, even on other SQL
+ database products.
+ </p><p>
+ The alternative archive file formats must be used with
+ <a class="xref" href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle"><span class="application">pg_restore</span></span></a> to rebuild the database. They
+ allow <span class="application">pg_restore</span> to be selective about
+ what is restored, or even to reorder the items prior to being
+ restored.
+ The archive file formats are designed to be portable across
+ architectures.
+ </p><p>
+ When used with one of the archive file formats and combined with
+ <span class="application">pg_restore</span>,
+ <span class="application">pg_dump</span> provides a flexible archival and
+ transfer mechanism. <span class="application">pg_dump</span> can be used to
+ backup an entire database, then <span class="application">pg_restore</span>
+ can be used to examine the archive and/or select which parts of the
+ database are to be restored. The most flexible output file formats are
+ the <span class="quote">“<span class="quote">custom</span>”</span> format (<code class="option">-Fc</code>) and the
+ <span class="quote">“<span class="quote">directory</span>”</span> format (<code class="option">-Fd</code>). They allow
+ for selection and reordering of all archived items, support parallel
+ restoration, and are compressed by default. The <span class="quote">“<span class="quote">directory</span>”</span>
+ format is the only format that supports parallel dumps.
+ </p><p>
+ While running <span class="application">pg_dump</span>, one should examine the
+ output for any warnings (printed on standard error), especially in
+ light of the limitations listed below.
+ </p></div><div class="refsect1" id="PG-DUMP-OPTIONS"><h2>Options</h2><p>
+ The following command-line options control the content and
+ format of the output.
+
+ </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>dbname</code></em></span></dt><dd><p>
+ Specifies the name of the database to be dumped. If this is
+ not specified, the environment variable
+ <code class="envar">PGDATABASE</code> is used. If that is not set, the
+ user name specified for the connection is used.
+ </p></dd><dt><span class="term"><code class="option">-a</code><br /></span><span class="term"><code class="option">--data-only</code></span></dt><dd><p>
+ Dump only the data, not the schema (data definitions).
+ Table data, large objects, and sequence values are dumped.
+ </p><p>
+ This option is similar to, but for historical reasons not identical
+ to, specifying <code class="option">--section=data</code>.
+ </p></dd><dt><span class="term"><code class="option">-b</code><br /></span><span class="term"><code class="option">--blobs</code></span></dt><dd><p>
+ Include large objects in the dump. This is the default behavior
+ except when <code class="option">--schema</code>, <code class="option">--table</code>, or
+ <code class="option">--schema-only</code> is specified. The <code class="option">-b</code>
+ switch is therefore only useful to add large objects to dumps
+ where a specific schema or table has been requested. Note that
+ blobs are considered data and therefore will be included when
+ <code class="option">--data-only</code> is used, but not
+ when <code class="option">--schema-only</code> is.
+ </p></dd><dt><span class="term"><code class="option">-B</code><br /></span><span class="term"><code class="option">--no-blobs</code></span></dt><dd><p>
+ Exclude large objects in the dump.
+ </p><p>
+ When both <code class="option">-b</code> and <code class="option">-B</code> are given, the behavior
+ is to output large objects, when data is being dumped, see the
+ <code class="option">-b</code> documentation.
+ </p></dd><dt><span class="term"><code class="option">-c</code><br /></span><span class="term"><code class="option">--clean</code></span></dt><dd><p>
+ Output commands to <code class="command">DROP</code> all the dumped
+ database objects prior to outputting the commands for creating them.
+ This option is useful when the restore is to overwrite an existing
+ database. If any of the objects do not exist in the destination
+ database, ignorable error messages will be reported during
+ restore, unless <code class="option">--if-exists</code> is also specified.
+ </p><p>
+ This option is ignored when emitting an archive (non-text) output
+ file. For the archive formats, you can specify the option when you
+ call <code class="command">pg_restore</code>.
+ </p></dd><dt><span class="term"><code class="option">-C</code><br /></span><span class="term"><code class="option">--create</code></span></dt><dd><p>
+ Begin the output with a command to create the
+ database itself and reconnect to the created database. (With a
+ script of this form, it doesn't matter which database in the
+ destination installation you connect to before running the script.)
+ If <code class="option">--clean</code> is also specified, the script drops and
+ recreates the target database before reconnecting to it.
+ </p><p>
+ With <code class="option">--create</code>, the output also includes the
+ database's comment if any, and any configuration variable settings
+ that are specific to this database, that is,
+ any <code class="command">ALTER DATABASE ... SET ...</code>
+ and <code class="command">ALTER ROLE ... IN DATABASE ... SET ...</code>
+ commands that mention this database.
+ Access privileges for the database itself are also dumped,
+ unless <code class="option">--no-acl</code> is specified.
+ </p><p>
+ This option is ignored when emitting an archive (non-text) output
+ file. For the archive formats, you can specify the option when you
+ call <code class="command">pg_restore</code>.
+ </p></dd><dt><span class="term"><code class="option">-e <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--extension=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
+ Dump only extensions matching <em class="replaceable"><code>pattern</code></em>. When this option is not
+ specified, all non-system extensions in the target database will be
+ dumped. Multiple extensions can be selected by writing multiple
+ <code class="option">-e</code> switches. The <em class="replaceable"><code>pattern</code></em> parameter is interpreted as a
+ pattern according to the same rules used by
+ <span class="application">psql</span>'s <code class="literal">\d</code> commands (see
+ <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a>), so multiple extensions can also
+ be selected by writing wildcard characters in the pattern. When using
+ wildcards, be careful to quote the pattern if needed to prevent the
+ shell from expanding the wildcards.
+ </p><p>
+ Any configuration relation registered by
+ <code class="function">pg_extension_config_dump</code> is included in the
+ dump if its extension is specified by <code class="option">--extension</code>.
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ When <code class="option">-e</code> is specified,
+ <span class="application">pg_dump</span> makes no attempt to dump any other
+ database objects that the selected extension(s) might depend upon.
+ Therefore, there is no guarantee that the results of a
+ specific-extension dump can be successfully restored by themselves
+ into a clean database.
+ </p></div></dd><dt><span class="term"><code class="option">-E <em class="replaceable"><code>encoding</code></em></code><br /></span><span class="term"><code class="option">--encoding=<em class="replaceable"><code>encoding</code></em></code></span></dt><dd><p>
+ Create the dump in the specified character set encoding. By default,
+ the dump is created in the database encoding. (Another way to get the
+ same result is to set the <code class="envar">PGCLIENTENCODING</code> environment
+ variable to the desired dump encoding.) The supported encodings are
+ described in <a class="xref" href="multibyte.html#MULTIBYTE-CHARSET-SUPPORTED" title="24.3.1. Supported Character Sets">Section 24.3.1</a>.
+ </p></dd><dt><span class="term"><code class="option">-f <em class="replaceable"><code>file</code></em></code><br /></span><span class="term"><code class="option">--file=<em class="replaceable"><code>file</code></em></code></span></dt><dd><p>
+ Send output to the specified file. This parameter can be omitted for
+ file based output formats, in which case the standard output is used.
+ It must be given for the directory output format however, where it
+ specifies the target directory instead of a file. In this case the
+ directory is created by <code class="command">pg_dump</code> and must not exist
+ before.
+ </p></dd><dt><span class="term"><code class="option">-F <em class="replaceable"><code>format</code></em></code><br /></span><span class="term"><code class="option">--format=<em class="replaceable"><code>format</code></em></code></span></dt><dd><p>
+ Selects the format of the output.
+ <em class="replaceable"><code>format</code></em> can be one of the following:
+
+ </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">p</code><br /></span><span class="term"><code class="literal">plain</code></span></dt><dd><p>
+ Output a plain-text <acronym class="acronym">SQL</acronym> script file (the default).
+ </p></dd><dt><span class="term"><code class="literal">c</code><br /></span><span class="term"><code class="literal">custom</code></span></dt><dd><p>
+ Output a custom-format archive suitable for input into
+ <span class="application">pg_restore</span>.
+ Together with the directory output format, this is the most flexible
+ output format in that it allows manual selection and reordering of
+ archived items during restore. This format is also compressed by
+ default.
+ </p></dd><dt><span class="term"><code class="literal">d</code><br /></span><span class="term"><code class="literal">directory</code></span></dt><dd><p>
+ Output a directory-format archive suitable for input into
+ <span class="application">pg_restore</span>. This will create a directory
+ with one file for each table and blob being dumped, plus a
+ so-called Table of Contents file describing the dumped objects in a
+ machine-readable format that <span class="application">pg_restore</span>
+ can read. A directory format archive can be manipulated with
+ standard Unix tools; for example, files in an uncompressed archive
+ can be compressed with the <span class="application">gzip</span> tool.
+ This format is compressed by default and also supports parallel
+ dumps.
+ </p></dd><dt><span class="term"><code class="literal">t</code><br /></span><span class="term"><code class="literal">tar</code></span></dt><dd><p>
+ Output a <code class="command">tar</code>-format archive suitable for input
+ into <span class="application">pg_restore</span>. The tar format is
+ compatible with the directory format: extracting a tar-format
+ archive produces a valid directory-format archive.
+ However, the tar format does not support compression. Also, when
+ using tar format the relative order of table data items cannot be
+ changed during restore.
+ </p></dd></dl></div></dd><dt><span class="term"><code class="option">-j <em class="replaceable"><code>njobs</code></em></code><br /></span><span class="term"><code class="option">--jobs=<em class="replaceable"><code>njobs</code></em></code></span></dt><dd><p>
+ Run the dump in parallel by dumping <em class="replaceable"><code>njobs</code></em>
+ tables simultaneously. This option may reduce the time needed to perform the dump but it also
+ increases the load on the database server. You can only use this option with the
+ directory output format because this is the only output format where multiple processes
+ can write their data at the same time.
+ </p><p><span class="application">pg_dump</span> will open <em class="replaceable"><code>njobs</code></em>
+ + 1 connections to the database, so make sure your <a class="xref" href="runtime-config-connection.html#GUC-MAX-CONNECTIONS">max_connections</a>
+ setting is high enough to accommodate all connections.
+ </p><p>
+ Requesting exclusive locks on database objects while running a parallel dump could
+ cause the dump to fail. The reason is that the <span class="application">pg_dump</span> leader process
+ requests shared locks (<a class="link" href="explicit-locking.html#LOCKING-TABLES" title="13.3.1. Table-Level Locks">ACCESS SHARE</a>) on the
+ objects that the worker processes are going to dump later in order to
+ make sure that nobody deletes them and makes them go away while the dump is running.
+ If another client then requests an exclusive lock on a table, that lock will not be
+ granted but will be queued waiting for the shared lock of the leader process to be
+ released. Consequently any other access to the table will not be granted either and
+ will queue after the exclusive lock request. This includes the worker process trying
+ to dump the table. Without any precautions this would be a classic deadlock situation.
+ To detect this conflict, the <span class="application">pg_dump</span> worker process requests another
+ shared lock using the <code class="literal">NOWAIT</code> option. If the worker process is not granted
+ this shared lock, somebody else must have requested an exclusive lock in the meantime
+ and there is no way to continue with the dump, so <span class="application">pg_dump</span> has no choice
+ but to abort the dump.
+ </p><p>
+ To perform a parallel dump, the database server needs to support
+ synchronized snapshots, a feature that was introduced in
+ <span class="productname">PostgreSQL</span> 9.2 for primary servers and 10
+ for standbys. With this feature, database clients can ensure they see
+ the same data set even though they use different connections.
+ <code class="command">pg_dump -j</code> uses multiple database connections; it
+ connects to the database once with the leader process and once again
+ for each worker job. Without the synchronized snapshot feature, the
+ different worker jobs wouldn't be guaranteed to see the same data in
+ each connection, which could lead to an inconsistent backup.
+ </p></dd><dt><span class="term"><code class="option">-n <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--schema=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
+ Dump only schemas matching <em class="replaceable"><code>pattern</code></em>; this selects both the
+ schema itself, and all its contained objects. When this option is
+ not specified, all non-system schemas in the target database will be
+ dumped. Multiple schemas can be
+ selected by writing multiple <code class="option">-n</code> switches. The
+ <em class="replaceable"><code>pattern</code></em> parameter is
+ interpreted as a pattern according to the same rules used by
+ <span class="application">psql</span>'s <code class="literal">\d</code> commands
+ (see <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a> below),
+ so multiple schemas can also be selected by writing wildcard characters
+ in the pattern. When using wildcards, be careful to quote the pattern
+ if needed to prevent the shell from expanding the wildcards; see
+ <a class="xref" href="app-pgdump.html#PG-DUMP-EXAMPLES" title="Examples">Examples</a> below.
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ When <code class="option">-n</code> is specified, <span class="application">pg_dump</span>
+ makes no attempt to dump any other database objects that the selected
+ schema(s) might depend upon. Therefore, there is no guarantee
+ that the results of a specific-schema dump can be successfully
+ restored by themselves into a clean database.
+ </p></div><div class="note"><h3 class="title">Note</h3><p>
+ Non-schema objects such as blobs are not dumped when <code class="option">-n</code> is
+ specified. You can add blobs back to the dump with the
+ <code class="option">--blobs</code> switch.
+ </p></div></dd><dt><span class="term"><code class="option">-N <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--exclude-schema=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
+ Do not dump any schemas matching <em class="replaceable"><code>pattern</code></em>. The pattern is
+ interpreted according to the same rules as for <code class="option">-n</code>.
+ <code class="option">-N</code> can be given more than once to exclude schemas
+ matching any of several patterns.
+ </p><p>
+ When both <code class="option">-n</code> and <code class="option">-N</code> are given, the behavior
+ is to dump just the schemas that match at least one <code class="option">-n</code>
+ switch but no <code class="option">-N</code> switches. If <code class="option">-N</code> appears
+ without <code class="option">-n</code>, then schemas matching <code class="option">-N</code> are
+ excluded from what is otherwise a normal dump.
+ </p></dd><dt><span class="term"><code class="option">-O</code><br /></span><span class="term"><code class="option">--no-owner</code></span></dt><dd><p>
+ Do not output commands to set
+ ownership of objects to match the original database.
+ By default, <span class="application">pg_dump</span> issues
+ <code class="command">ALTER OWNER</code> or
+ <code class="command">SET SESSION AUTHORIZATION</code>
+ statements to set ownership of created database objects.
+ These statements
+ will fail when the script is run unless it is started by a superuser
+ (or the same user that owns all of the objects in the script).
+ To make a script that can be restored by any user, but will give
+ that user ownership of all the objects, specify <code class="option">-O</code>.
+ </p><p>
+ This option is ignored when emitting an archive (non-text) output
+ file. For the archive formats, you can specify the option when you
+ call <code class="command">pg_restore</code>.
+ </p></dd><dt><span class="term"><code class="option">-R</code><br /></span><span class="term"><code class="option">--no-reconnect</code></span></dt><dd><p>
+ This option is obsolete but still accepted for backwards
+ compatibility.
+ </p></dd><dt><span class="term"><code class="option">-s</code><br /></span><span class="term"><code class="option">--schema-only</code></span></dt><dd><p>
+ Dump only the object definitions (schema), not data.
+ </p><p>
+ This option is the inverse of <code class="option">--data-only</code>.
+ It is similar to, but for historical reasons not identical to,
+ specifying
+ <code class="option">--section=pre-data --section=post-data</code>.
+ </p><p>
+ (Do not confuse this with the <code class="option">--schema</code> option, which
+ uses the word <span class="quote">“<span class="quote">schema</span>”</span> in a different meaning.)
+ </p><p>
+ To exclude table data for only a subset of tables in the database,
+ see <code class="option">--exclude-table-data</code>.
+ </p></dd><dt><span class="term"><code class="option">-S <em class="replaceable"><code>username</code></em></code><br /></span><span class="term"><code class="option">--superuser=<em class="replaceable"><code>username</code></em></code></span></dt><dd><p>
+ Specify the superuser user name to use when disabling triggers.
+ This is relevant only if <code class="option">--disable-triggers</code> is used.
+ (Usually, it's better to leave this out, and instead start the
+ resulting script as superuser.)
+ </p></dd><dt><span class="term"><code class="option">-t <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--table=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
+ Dump only tables with names matching
+ <em class="replaceable"><code>pattern</code></em>. Multiple tables
+ can be selected by writing multiple <code class="option">-t</code> switches. The
+ <em class="replaceable"><code>pattern</code></em> parameter is
+ interpreted as a pattern according to the same rules used by
+ <span class="application">psql</span>'s <code class="literal">\d</code> commands
+ (see <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a> below),
+ so multiple tables can also be selected by writing wildcard characters
+ in the pattern. When using wildcards, be careful to quote the pattern
+ if needed to prevent the shell from expanding the wildcards; see
+ <a class="xref" href="app-pgdump.html#PG-DUMP-EXAMPLES" title="Examples">Examples</a> below.
+ </p><p>
+ As well as tables, this option can be used to dump the definition of matching
+ views, materialized views, foreign tables, and sequences. It will not dump the
+ contents of views or materialized views, and the contents of foreign tables will
+ only be dumped if the corresponding foreign server is specified with
+ <code class="option">--include-foreign-data</code>.
+ </p><p>
+ The <code class="option">-n</code> and <code class="option">-N</code> switches have no effect when
+ <code class="option">-t</code> is used, because tables selected by <code class="option">-t</code> will
+ be dumped regardless of those switches, and non-table objects will not
+ be dumped.
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ When <code class="option">-t</code> is specified, <span class="application">pg_dump</span>
+ makes no attempt to dump any other database objects that the selected
+ table(s) might depend upon. Therefore, there is no guarantee
+ that the results of a specific-table dump can be successfully
+ restored by themselves into a clean database.
+ </p></div></dd><dt><span class="term"><code class="option">-T <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--exclude-table=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
+ Do not dump any tables matching <em class="replaceable"><code>pattern</code></em>. The pattern is
+ interpreted according to the same rules as for <code class="option">-t</code>.
+ <code class="option">-T</code> can be given more than once to exclude tables
+ matching any of several patterns.
+ </p><p>
+ When both <code class="option">-t</code> and <code class="option">-T</code> are given, the behavior
+ is to dump just the tables that match at least one <code class="option">-t</code>
+ switch but no <code class="option">-T</code> switches. If <code class="option">-T</code> appears
+ without <code class="option">-t</code>, then tables matching <code class="option">-T</code> are
+ excluded from what is otherwise a normal dump.
+ </p></dd><dt><span class="term"><code class="option">-v</code><br /></span><span class="term"><code class="option">--verbose</code></span></dt><dd><p>
+ Specifies verbose mode. This will cause
+ <span class="application">pg_dump</span> to output detailed object
+ comments and start/stop times to the dump file, and progress
+ messages to standard error.
+ Repeating the option causes additional debug-level messages
+ to appear on standard error.
+ </p></dd><dt><span class="term"><code class="option">-V</code><br /></span><span class="term"><code class="option">--version</code></span></dt><dd><p>
+ Print the <span class="application">pg_dump</span> version and exit.
+ </p></dd><dt><span class="term"><code class="option">-x</code><br /></span><span class="term"><code class="option">--no-privileges</code><br /></span><span class="term"><code class="option">--no-acl</code></span></dt><dd><p>
+ Prevent dumping of access privileges (grant/revoke commands).
+ </p></dd><dt><span class="term"><code class="option">-Z <em class="replaceable"><code>0..9</code></em></code><br /></span><span class="term"><code class="option">--compress=<em class="replaceable"><code>0..9</code></em></code></span></dt><dd><p>
+ Specify the compression level to use. Zero means no compression.
+ For the custom and directory archive formats, this specifies compression of
+ individual table-data segments, and the default is to compress
+ at a moderate level.
+ For plain text output, setting a nonzero compression level causes
+ the entire output file to be compressed, as though it had been
+ fed through <span class="application">gzip</span>; but the default is not to compress.
+ The tar archive format currently does not support compression at all.
+ </p></dd><dt><span class="term"><code class="option">--binary-upgrade</code></span></dt><dd><p>
+ This option is for use by in-place upgrade utilities. Its use
+ for other purposes is not recommended or supported. The
+ behavior of the option may change in future releases without
+ notice.
+ </p></dd><dt><span class="term"><code class="option">--column-inserts</code><br /></span><span class="term"><code class="option">--attribute-inserts</code></span></dt><dd><p>
+ Dump data as <code class="command">INSERT</code> commands with explicit
+ column names (<code class="literal">INSERT INTO
+ <em class="replaceable"><code>table</code></em>
+ (<em class="replaceable"><code>column</code></em>, ...) VALUES
+ ...</code>). This will make restoration very slow; it is mainly
+ useful for making dumps that can be loaded into
+ non-<span class="productname">PostgreSQL</span> databases.
+ Any error during restoring will cause only rows that are part of the
+ problematic <code class="command">INSERT</code> to be lost, rather than the
+ entire table contents.
+ </p></dd><dt><span class="term"><code class="option">--disable-dollar-quoting</code></span></dt><dd><p>
+ This option disables the use of dollar quoting for function bodies,
+ and forces them to be quoted using SQL standard string syntax.
+ </p></dd><dt><span class="term"><code class="option">--disable-triggers</code></span></dt><dd><p>
+ This option is relevant only when creating a data-only dump.
+ It instructs <span class="application">pg_dump</span> to include commands
+ to temporarily disable triggers on the target tables while
+ the data is restored. Use this if you have referential
+ integrity checks or other triggers on the tables that you
+ do not want to invoke during data restore.
+ </p><p>
+ Presently, the commands emitted for <code class="option">--disable-triggers</code>
+ must be done as superuser. So, you should also specify
+ a superuser name with <code class="option">-S</code>, or preferably be careful to
+ start the resulting script as a superuser.
+ </p><p>
+ This option is ignored when emitting an archive (non-text) output
+ file. For the archive formats, you can specify the option when you
+ call <code class="command">pg_restore</code>.
+ </p></dd><dt><span class="term"><code class="option">--enable-row-security</code></span></dt><dd><p>
+ This option is relevant only when dumping the contents of a table
+ which has row security. By default, <span class="application">pg_dump</span> will set
+ <a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> to off, to ensure
+ that all data is dumped from the table. If the user does not have
+ sufficient privileges to bypass row security, then an error is thrown.
+ This parameter instructs <span class="application">pg_dump</span> to set
+ <a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> to on instead, allowing the user
+ to dump the parts of the contents of the table that they have access to.
+ </p><p>
+ Note that if you use this option currently, you probably also want
+ the dump be in <code class="command">INSERT</code> format, as the
+ <code class="command">COPY FROM</code> during restore does not support row security.
+ </p></dd><dt><span class="term"><code class="option">--exclude-table-data=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
+ Do not dump data for any tables matching <em class="replaceable"><code>pattern</code></em>. The pattern is
+ interpreted according to the same rules as for <code class="option">-t</code>.
+ <code class="option">--exclude-table-data</code> can be given more than once to
+ exclude tables matching any of several patterns. This option is
+ useful when you need the definition of a particular table even
+ though you do not need the data in it.
+ </p><p>
+ To exclude data for all tables in the database, see <code class="option">--schema-only</code>.
+ </p></dd><dt><span class="term"><code class="option">--extra-float-digits=<em class="replaceable"><code>ndigits</code></em></code></span></dt><dd><p>
+ Use the specified value of <code class="option">extra_float_digits</code> when dumping
+ floating-point data, instead of the maximum available precision.
+ Routine dumps made for backup purposes should not use this option.
+ </p></dd><dt><span class="term"><code class="option">--if-exists</code></span></dt><dd><p>
+ Use <code class="literal">DROP ... IF EXISTS</code> commands to drop objects
+ in <code class="option">--clean</code> mode. This suppresses <span class="quote">“<span class="quote">does not
+ exist</span>”</span> errors that might otherwise be reported. This
+ option is not valid unless <code class="option">--clean</code> is also
+ specified.
+ </p></dd><dt><span class="term"><code class="option">--include-foreign-data=<em class="replaceable"><code>foreignserver</code></em></code></span></dt><dd><p>
+ Dump the data for any foreign table with a foreign server
+ matching <em class="replaceable"><code>foreignserver</code></em>
+ pattern. Multiple foreign servers can be selected by writing multiple
+ <code class="option">--include-foreign-data</code> switches.
+ Also, the <em class="replaceable"><code>foreignserver</code></em> parameter is
+ interpreted as a pattern according to the same rules used by
+ <span class="application">psql</span>'s <code class="literal">\d</code> commands
+ (see <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a> below),
+ so multiple foreign servers can also be selected by writing wildcard characters
+ in the pattern. When using wildcards, be careful to quote the pattern
+ if needed to prevent the shell from expanding the wildcards; see
+ <a class="xref" href="app-pgdump.html#PG-DUMP-EXAMPLES" title="Examples">Examples</a> below.
+ The only exception is that an empty pattern is disallowed.
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ When <code class="option">--include-foreign-data</code> is specified,
+ <span class="application">pg_dump</span> does not check that the foreign
+ table is writable. Therefore, there is no guarantee that the
+ results of a foreign table dump can be successfully restored.
+ </p></div></dd><dt><span class="term"><code class="option">--inserts</code></span></dt><dd><p>
+ Dump data as <code class="command">INSERT</code> commands (rather
+ than <code class="command">COPY</code>). This will make restoration very slow;
+ it is mainly useful for making dumps that can be loaded into
+ non-<span class="productname">PostgreSQL</span> databases.
+ Any error during restoring will cause only rows that are part of the
+ problematic <code class="command">INSERT</code> to be lost, rather than the
+ entire table contents. Note that the restore might fail altogether if
+ you have rearranged column order. The
+ <code class="option">--column-inserts</code> option is safe against column order
+ changes, though even slower.
+ </p></dd><dt><span class="term"><code class="option">--load-via-partition-root</code></span></dt><dd><p>
+ When dumping data for a table partition, make
+ the <code class="command">COPY</code> or <code class="command">INSERT</code> statements
+ target the root of the partitioning hierarchy that contains it, rather
+ than the partition itself. This causes the appropriate partition to
+ be re-determined for each row when the data is loaded. This may be
+ useful when restoring data on a server where rows do not always fall
+ into the same partitions as they did on the original server. That
+ could happen, for example, if the partitioning column is of type text
+ and the two systems have different definitions of the collation used
+ to sort the partitioning column.
+ </p></dd><dt><span class="term"><code class="option">--lock-wait-timeout=<em class="replaceable"><code>timeout</code></em></code></span></dt><dd><p>
+ Do not wait forever to acquire shared table locks at the beginning of
+ the dump. Instead fail if unable to lock a table within the specified
+ <em class="replaceable"><code>timeout</code></em>. The timeout may be
+ specified in any of the formats accepted by <code class="command">SET
+ statement_timeout</code>. (Allowed formats vary depending on the server
+ version you are dumping from, but an integer number of milliseconds
+ is accepted by all versions.)
+ </p></dd><dt><span class="term"><code class="option">--no-comments</code></span></dt><dd><p>
+ Do not dump comments.
+ </p></dd><dt><span class="term"><code class="option">--no-publications</code></span></dt><dd><p>
+ Do not dump publications.
+ </p></dd><dt><span class="term"><code class="option">--no-security-labels</code></span></dt><dd><p>
+ Do not dump security labels.
+ </p></dd><dt><span class="term"><code class="option">--no-subscriptions</code></span></dt><dd><p>
+ Do not dump subscriptions.
+ </p></dd><dt><span class="term"><code class="option">--no-sync</code></span></dt><dd><p>
+ By default, <code class="command">pg_dump</code> will wait for all files
+ to be written safely to disk. This option causes
+ <code class="command">pg_dump</code> to return without waiting, which is
+ faster, but means that a subsequent operating system crash can leave
+ the dump corrupt. Generally, this option is useful for testing
+ but should not be used when dumping data from production installation.
+ </p></dd><dt><span class="term"><code class="option">--no-table-access-method</code></span></dt><dd><p>
+ Do not output commands to select table access methods.
+ With this option, all objects will be created with whichever
+ table access method is the default during restore.
+ </p><p>
+ This option is ignored when emitting an archive (non-text) output
+ file. For the archive formats, you can specify the option when you
+ call <code class="command">pg_restore</code>.
+ </p></dd><dt><span class="term"><code class="option">--no-tablespaces</code></span></dt><dd><p>
+ Do not output commands to select tablespaces.
+ With this option, all objects will be created in whichever
+ tablespace is the default during restore.
+ </p><p>
+ This option is ignored when emitting an archive (non-text) output
+ file. For the archive formats, you can specify the option when you
+ call <code class="command">pg_restore</code>.
+ </p></dd><dt><span class="term"><code class="option">--no-toast-compression</code></span></dt><dd><p>
+ Do not output commands to set <acronym class="acronym">TOAST</acronym> compression
+ methods.
+ With this option, all columns will be restored with the default
+ compression setting.
+ </p></dd><dt><span class="term"><code class="option">--no-unlogged-table-data</code></span></dt><dd><p>
+ Do not dump the contents of unlogged tables and sequences. This
+ option has no effect on whether or not the table and sequence
+ definitions (schema) are dumped; it only suppresses dumping the table
+ and sequence data. Data in unlogged tables and sequences
+ is always excluded when dumping from a standby server.
+ </p></dd><dt><span class="term"><code class="option">--on-conflict-do-nothing</code></span></dt><dd><p>
+ Add <code class="literal">ON CONFLICT DO NOTHING</code> to
+ <code class="command">INSERT</code> commands.
+ This option is not valid unless <code class="option">--inserts</code>,
+ <code class="option">--column-inserts</code> or
+ <code class="option">--rows-per-insert</code> is also specified.
+ </p></dd><dt><span class="term"><code class="option">--quote-all-identifiers</code></span></dt><dd><p>
+ Force quoting of all identifiers. This option is recommended when
+ dumping a database from a server whose <span class="productname">PostgreSQL</span>
+ major version is different from <span class="application">pg_dump</span>'s, or when
+ the output is intended to be loaded into a server of a different
+ major version. By default, <span class="application">pg_dump</span> quotes only
+ identifiers that are reserved words in its own major version.
+ This sometimes results in compatibility issues when dealing with
+ servers of other versions that may have slightly different sets
+ of reserved words. Using <code class="option">--quote-all-identifiers</code> prevents
+ such issues, at the price of a harder-to-read dump script.
+ </p></dd><dt><span class="term"><code class="option">--rows-per-insert=<em class="replaceable"><code>nrows</code></em></code></span></dt><dd><p>
+ Dump data as <code class="command">INSERT</code> commands (rather than
+ <code class="command">COPY</code>). Controls the maximum number of rows per
+ <code class="command">INSERT</code> command. The value specified must be a
+ number greater than zero. Any error during restoring will cause only
+ rows that are part of the problematic <code class="command">INSERT</code> to be
+ lost, rather than the entire table contents.
+ </p></dd><dt><span class="term"><code class="option">--section=<em class="replaceable"><code>sectionname</code></em></code></span></dt><dd><p>
+ Only dump the named section. The section name can be
+ <code class="option">pre-data</code>, <code class="option">data</code>, or <code class="option">post-data</code>.
+ This option can be specified more than once to select multiple
+ sections. The default is to dump all sections.
+ </p><p>
+ The data section contains actual table data, large-object
+ contents, and sequence values.
+ Post-data items include definitions of indexes, triggers, rules,
+ and constraints other than validated check constraints.
+ Pre-data items include all other data definition items.
+ </p></dd><dt><span class="term"><code class="option">--serializable-deferrable</code></span></dt><dd><p>
+ Use a <code class="literal">serializable</code> transaction for the dump, to
+ ensure that the snapshot used is consistent with later database
+ states; but do this by waiting for a point in the transaction stream
+ at which no anomalies can be present, so that there isn't a risk of
+ the dump failing or causing other transactions to roll back with a
+ <code class="literal">serialization_failure</code>. See <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>
+ for more information about transaction isolation and concurrency
+ control.
+ </p><p>
+ This option is not beneficial for a dump which is intended only for
+ disaster recovery. It could be useful for a dump used to load a
+ copy of the database for reporting or other read-only load sharing
+ while the original database continues to be updated. Without it the
+ dump may reflect a state which is not consistent with any serial
+ execution of the transactions eventually committed. For example, if
+ batch processing techniques are used, a batch may show as closed in
+ the dump without all of the items which are in the batch appearing.
+ </p><p>
+ This option will make no difference if there are no read-write
+ transactions active when pg_dump is started. If read-write
+ transactions are active, the start of the dump may be delayed for an
+ indeterminate length of time. Once running, performance with or
+ without the switch is the same.
+ </p></dd><dt><span class="term"><code class="option">--snapshot=<em class="replaceable"><code>snapshotname</code></em></code></span></dt><dd><p>
+ Use the specified synchronized snapshot when making a dump of the
+ database (see
+ <a class="xref" href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION-TABLE" title="Table 9.92. Snapshot Synchronization Functions">Table 9.92</a> for more
+ details).
+ </p><p>
+ This option is useful when needing to synchronize the dump with
+ a logical replication slot (see <a class="xref" href="logicaldecoding.html" title="Chapter 49. Logical Decoding">Chapter 49</a>)
+ or with a concurrent session.
+ </p><p>
+ In the case of a parallel dump, the snapshot name defined by this
+ option is used rather than taking a new snapshot.
+ </p></dd><dt><span class="term"><code class="option">--strict-names</code></span></dt><dd><p>
+ Require that each
+ extension (<code class="option">-e</code>/<code class="option">--extension</code>),
+ schema (<code class="option">-n</code>/<code class="option">--schema</code>) and
+ table (<code class="option">-t</code>/<code class="option">--table</code>) qualifier
+ match at least one extension/schema/table in the database to be dumped.
+ Note that if none of the extension/schema/table qualifiers find
+ matches, <span class="application">pg_dump</span> will generate an error
+ even without <code class="option">--strict-names</code>.
+ </p><p>
+ This option has no effect
+ on <code class="option">-N</code>/<code class="option">--exclude-schema</code>,
+ <code class="option">-T</code>/<code class="option">--exclude-table</code>,
+ or <code class="option">--exclude-table-data</code>. An exclude pattern failing
+ to match any objects is not considered an error.
+ </p></dd><dt><span class="term"><code class="option">--use-set-session-authorization</code></span></dt><dd><p>
+ Output SQL-standard <code class="command">SET SESSION AUTHORIZATION</code> commands
+ instead of <code class="command">ALTER OWNER</code> commands to determine object
+ ownership. This makes the dump more standards-compatible, but
+ depending on the history of the objects in the dump, might not restore
+ properly. Also, a dump using <code class="command">SET SESSION AUTHORIZATION</code>
+ will certainly require superuser privileges to restore correctly,
+ whereas <code class="command">ALTER OWNER</code> requires lesser privileges.
+ </p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p>
+ Show help about <span class="application">pg_dump</span> command line
+ arguments, and exit.
+ </p></dd></dl></div><p>
+ </p><p>
+ The following command-line options control the database connection parameters.
+
+ </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-d <em class="replaceable"><code>dbname</code></em></code><br /></span><span class="term"><code class="option">--dbname=<em class="replaceable"><code>dbname</code></em></code></span></dt><dd><p>
+ Specifies the name of the database to connect to. This is
+ equivalent to specifying <em class="replaceable"><code>dbname</code></em> as the first non-option
+ argument on the command line. The <em class="replaceable"><code>dbname</code></em>
+ can be a <a class="link" href="libpq-connect.html#LIBPQ-CONNSTRING" title="34.1.1. Connection Strings">connection string</a>.
+ If so, connection string parameters will override any conflicting
+ command line options.
+ </p></dd><dt><span class="term"><code class="option">-h <em class="replaceable"><code>host</code></em></code><br /></span><span class="term"><code class="option">--host=<em class="replaceable"><code>host</code></em></code></span></dt><dd><p>
+ Specifies the host name of the machine on which the server is
+ running. If the value begins with a slash, it is used as the
+ directory for the Unix domain socket. The default is taken
+ from the <code class="envar">PGHOST</code> environment variable, if set,
+ else a Unix domain socket connection is attempted.
+ </p></dd><dt><span class="term"><code class="option">-p <em class="replaceable"><code>port</code></em></code><br /></span><span class="term"><code class="option">--port=<em class="replaceable"><code>port</code></em></code></span></dt><dd><p>
+ Specifies the TCP port or local Unix domain socket file
+ extension on which the server is listening for connections.
+ Defaults to the <code class="envar">PGPORT</code> environment variable, if
+ set, or a compiled-in default.
+ </p></dd><dt><span class="term"><code class="option">-U <em class="replaceable"><code>username</code></em></code><br /></span><span class="term"><code class="option">--username=<em class="replaceable"><code>username</code></em></code></span></dt><dd><p>
+ User name to connect as.
+ </p></dd><dt><span class="term"><code class="option">-w</code><br /></span><span class="term"><code class="option">--no-password</code></span></dt><dd><p>
+ Never issue a password prompt. If the server requires
+ password authentication and a password is not available by
+ other means such as a <code class="filename">.pgpass</code> file, the
+ connection attempt will fail. This option can be useful in
+ batch jobs and scripts where no user is present to enter a
+ password.
+ </p></dd><dt><span class="term"><code class="option">-W</code><br /></span><span class="term"><code class="option">--password</code></span></dt><dd><p>
+ Force <span class="application">pg_dump</span> to prompt for a
+ password before connecting to a database.
+ </p><p>
+ This option is never essential, since
+ <span class="application">pg_dump</span> will automatically prompt
+ for a password if the server demands password authentication.
+ However, <span class="application">pg_dump</span> will waste a
+ connection attempt finding out that the server wants a password.
+ In some cases it is worth typing <code class="option">-W</code> to avoid the extra
+ connection attempt.
+ </p></dd><dt><span class="term"><code class="option">--role=<em class="replaceable"><code>rolename</code></em></code></span></dt><dd><p>
+ Specifies a role name to be used to create the dump.
+ This option causes <span class="application">pg_dump</span> to issue a
+ <code class="command">SET ROLE</code> <em class="replaceable"><code>rolename</code></em>
+ command after connecting to the database. It is useful when the
+ authenticated user (specified by <code class="option">-U</code>) lacks privileges
+ needed by <span class="application">pg_dump</span>, but can switch to a role with
+ the required rights. Some installations have a policy against
+ logging in directly as a superuser, and use of this option allows
+ dumps to be made without violating the policy.
+ </p></dd></dl></div><p>
+ </p></div><div class="refsect1" id="id-1.9.4.13.7"><h2>Environment</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="envar">PGDATABASE</code><br /></span><span class="term"><code class="envar">PGHOST</code><br /></span><span class="term"><code class="envar">PGOPTIONS</code><br /></span><span class="term"><code class="envar">PGPORT</code><br /></span><span class="term"><code class="envar">PGUSER</code></span></dt><dd><p>
+ Default connection parameters.
+ </p></dd><dt><span class="term"><code class="envar">PG_COLOR</code></span></dt><dd><p>
+ Specifies whether to use color in diagnostic messages. Possible values
+ are <code class="literal">always</code>, <code class="literal">auto</code> and
+ <code class="literal">never</code>.
+ </p></dd></dl></div><p>
+ This utility, like most other <span class="productname">PostgreSQL</span> utilities,
+ also uses the environment variables supported by <span class="application">libpq</span>
+ (see <a class="xref" href="libpq-envars.html" title="34.15. Environment Variables">Section 34.15</a>).
+ </p></div><div class="refsect1" id="APP-PGDUMP-DIAGNOSTICS"><h2>Diagnostics</h2><p>
+ <span class="application">pg_dump</span> internally executes
+ <code class="command">SELECT</code> statements. If you have problems running
+ <span class="application">pg_dump</span>, make sure you are able to
+ select information from the database using, for example, <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a>. Also, any default connection settings and environment
+ variables used by the <span class="application">libpq</span> front-end
+ library will apply.
+ </p><p>
+ The database activity of <span class="application">pg_dump</span> is
+ normally collected by the cumulative statistics system. If this is
+ undesirable, you can set parameter <code class="varname">track_counts</code>
+ to false via <code class="envar">PGOPTIONS</code> or the <code class="literal">ALTER
+ USER</code> command.
+ </p></div><div class="refsect1" id="PG-DUMP-NOTES"><h2>Notes</h2><p>
+ If your database cluster has any local additions to the <code class="literal">template1</code> database,
+ be careful to restore the output of <span class="application">pg_dump</span> into a
+ truly empty database; otherwise you are likely to get errors due to
+ duplicate definitions of the added objects. To make an empty database
+ without any local additions, copy from <code class="literal">template0</code> not <code class="literal">template1</code>,
+ for example:
+</p><pre class="programlisting">
+CREATE DATABASE foo WITH TEMPLATE template0;
+</pre><p>
+ </p><p>
+ When a data-only dump is chosen and the option <code class="option">--disable-triggers</code>
+ is used, <span class="application">pg_dump</span> emits commands
+ to disable triggers on user tables before inserting the data,
+ and then commands to re-enable them after the data has been
+ inserted. If the restore is stopped in the middle, the system
+ catalogs might be left in the wrong state.
+ </p><p>
+ The dump file produced by <span class="application">pg_dump</span>
+ does not contain the statistics used by the optimizer to make
+ query planning decisions. Therefore, it is wise to run
+ <code class="command">ANALYZE</code> after restoring from a dump file
+ to ensure optimal performance; see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-STATISTICS" title="25.1.3. Updating Planner Statistics">Section 25.1.3</a>
+ and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.1.6</a> for more information.
+ </p><p>
+ Because <span class="application">pg_dump</span> is used to transfer data
+ to newer versions of <span class="productname">PostgreSQL</span>, the output of
+ <span class="application">pg_dump</span> can be expected to load into
+ <span class="productname">PostgreSQL</span> server versions newer than
+ <span class="application">pg_dump</span>'s version. <span class="application">pg_dump</span> can also
+ dump from <span class="productname">PostgreSQL</span> servers older than its own version.
+ (Currently, servers back to version 9.2 are supported.)
+ However, <span class="application">pg_dump</span> cannot dump from
+ <span class="productname">PostgreSQL</span> servers newer than its own major version;
+ it will refuse to even try, rather than risk making an invalid dump.
+ Also, it is not guaranteed that <span class="application">pg_dump</span>'s output can
+ be loaded into a server of an older major version — not even if the
+ dump was taken from a server of that version. Loading a dump file
+ into an older server may require manual editing of the dump file
+ to remove syntax not understood by the older server.
+ Use of the <code class="option">--quote-all-identifiers</code> option is recommended
+ in cross-version cases, as it can prevent problems arising from varying
+ reserved-word lists in different <span class="productname">PostgreSQL</span> versions.
+ </p><p>
+ When dumping logical replication subscriptions,
+ <span class="application">pg_dump</span> will generate <code class="command">CREATE
+ SUBSCRIPTION</code> commands that use the <code class="literal">connect = false</code>
+ option, so that restoring the subscription does not make remote connections
+ for creating a replication slot or for initial table copy. That way, the
+ dump can be restored without requiring network access to the remote
+ servers. It is then up to the user to reactivate the subscriptions in a
+ suitable way. If the involved hosts have changed, the connection
+ information might have to be changed. It might also be appropriate to
+ truncate the target tables before initiating a new full table copy. If users
+ intend to copy initial data during refresh they must create the slot with
+ <code class="literal">two_phase = false</code>. After the initial sync, the
+ <code class="literal">two_phase</code> option will be automatically enabled by the
+ subscriber if the subscription had been originally created with
+ <code class="literal">two_phase = true</code> option.
+ </p></div><div class="refsect1" id="PG-DUMP-EXAMPLES"><h2>Examples</h2><p>
+ To dump a database called <code class="literal">mydb</code> into an SQL-script file:
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_dump mydb &gt; db.sql</code></strong>
+</pre><p>
+ </p><p>
+ To reload such a script into a (freshly created) database named
+ <code class="literal">newdb</code>:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>psql -d newdb -f db.sql</code></strong>
+</pre><p>
+ </p><p>
+ To dump a database into a custom-format archive file:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -Fc mydb &gt; db.dump</code></strong>
+</pre><p>
+ </p><p>
+ To dump a database into a directory-format archive:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -Fd mydb -f dumpdir</code></strong>
+</pre><p>
+ </p><p>
+ To dump a database into a directory-format archive in parallel with
+ 5 worker jobs:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -Fd mydb -j 5 -f dumpdir</code></strong>
+</pre><p>
+ </p><p>
+ To reload an archive file into a (freshly created) database named
+ <code class="literal">newdb</code>:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_restore -d newdb db.dump</code></strong>
+</pre><p>
+ </p><p>
+ To reload an archive file into the same database it was dumped from,
+ discarding the current contents of that database:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_restore -d postgres --clean --create db.dump</code></strong>
+</pre><p>
+ </p><p>
+ To dump a single table named <code class="literal">mytab</code>:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -t mytab mydb &gt; db.sql</code></strong>
+</pre><p>
+ </p><p>
+ To dump all tables whose names start with <code class="literal">emp</code> in the
+ <code class="literal">detroit</code> schema, except for the table named
+ <code class="literal">employee_log</code>:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb &gt; db.sql</code></strong>
+</pre><p>
+ </p><p>
+ To dump all schemas whose names start with <code class="literal">east</code> or
+ <code class="literal">west</code> and end in <code class="literal">gsm</code>, excluding any schemas whose
+ names contain the word <code class="literal">test</code>:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb &gt; db.sql</code></strong>
+</pre><p>
+ </p><p>
+ The same, using regular expression notation to consolidate the switches:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -n '(east|west)*gsm' -N '*test*' mydb &gt; db.sql</code></strong>
+</pre><p>
+ </p><p>
+ To dump all database objects except for tables whose names begin with
+ <code class="literal">ts_</code>:
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -T 'ts_*' mydb &gt; db.sql</code></strong>
+</pre><p>
+ </p><p>
+ To specify an upper-case or mixed-case name in <code class="option">-t</code> and related
+ switches, you need to double-quote the name; else it will be folded to
+ lower case (see <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a> below). But
+ double quotes are special to the shell, so in turn they must be quoted.
+ Thus, to dump a single table with a mixed-case name, you need something
+ like
+
+</p><pre class="screen">
+<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -t "\"MixedCaseName\"" mydb &gt; mytab.sql</code></strong>
+</pre></div><div class="refsect1" id="id-1.9.4.13.11"><h2>See Also</h2><span class="simplelist"><a class="xref" href="app-pg-dumpall.html" title="pg_dumpall"><span class="refentrytitle"><span class="application">pg_dumpall</span></span></a>, <a class="xref" href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle"><span class="application">pg_restore</span></span></a>, <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="app-pgconfig.html" title="pg_config">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="reference-client.html" title="PostgreSQL Client Applications">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="app-pg-dumpall.html" title="pg_dumpall">Next</a></td></tr><tr><td width="40%" align="left" valign="top"><span class="application">pg_config</span> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> <span class="application">pg_dumpall</span></td></tr></table></div></body></html> \ No newline at end of file