diff options
Diffstat (limited to 'doc/src/sgml/html/app-pgdump.html')
| -rw-r--r-- | doc/src/sgml/html/app-pgdump.html | 863 |
1 files changed, 863 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..1fc3674 --- /dev/null +++ b/doc/src/sgml/html/app-pgdump.html @@ -0,0 +1,863 @@ +<?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 16.2 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">--large-objects</code><br /></span><span class="term"><code class="option">--blobs</code> (deprecated)</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 + large objects 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-large-objects</code><br /></span><span class="term"><code class="option">--no-blobs</code> (deprecated)</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 large object 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>, + <span class="application">lz4</span>, or + <span class="application">zstd</span> tools. + This format is compressed by default using <code class="literal">gzip</code> + 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>), + 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 large objects are not dumped when <code class="option">-n</code> is + specified. You can add large objects back to the dump with the + <code class="option">--large-objects</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>), + 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>level</code></em></code><br /></span><span class="term"><code class="option">-Z <em class="replaceable"><code>method</code></em></code>[:<em class="replaceable"><code>detail</code></em>]<br /></span><span class="term"><code class="option">--compress=<em class="replaceable"><code>level</code></em></code><br /></span><span class="term"><code class="option">--compress=<em class="replaceable"><code>method</code></em></code>[:<em class="replaceable"><code>detail</code></em>]</span></dt><dd><p> + Specify the compression method and/or the compression level to use. + The compression method can be set to <code class="literal">gzip</code>, + <code class="literal">lz4</code>, <code class="literal">zstd</code>, + or <code class="literal">none</code> for no compression. + A compression detail string can optionally be specified. If the + detail string is an integer, it specifies the compression level. + Otherwise, it should be a comma-separated list of items, each of the + form <code class="literal">keyword</code> or <code class="literal">keyword=value</code>. + Currently, the supported keywords are <code class="literal">level</code> and + <code class="literal">long</code>. + </p><p> + If no compression level is specified, the default compression + level will be used. If only a level is specified without mentioning + an algorithm, <code class="literal">gzip</code> compression will be used if + the level is greater than <code class="literal">0</code>, and no compression + will be used if the level is <code class="literal">0</code>. + </p><p> + For the custom and directory archive formats, this specifies compression of + individual table-data segments, and the default is to compress using + <code class="literal">gzip</code> 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>, + <span class="application">lz4</span>, or <span class="application">zstd</span>; + but the default is not to compress. + With zstd compression, <code class="literal">long</code> mode may improve the + compression ratio, at the cost of increased memory use. + </p><p> + 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-and-children=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p> + This is the same as + the <code class="option">-T</code>/<code class="option">--exclude-table</code> option, + except that it also excludes any partitions or inheritance child + tables of the table(s) matching the + <em class="replaceable"><code>pattern</code></em>. + </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">--exclude-table-data-and-children=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p> + This is the same as the <code class="option">--exclude-table-data</code> option, + except that it also excludes data of any partitions or inheritance + child tables of the table(s) matching the + <em class="replaceable"><code>pattern</code></em>. + </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>), + 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.94. Snapshot Synchronization Functions">Table 9.94</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>) pattern + match at least one extension/schema/table in the database to be dumped. + Note that if none of the extension/schema/table patterns 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">--table-and-children=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p> + This is the same as + the <code class="option">-t</code>/<code class="option">--table</code> option, + except that it also includes any partitions or inheritance child + tables of the table(s) matching the + <em class="replaceable"><code>pattern</code></em>. + </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 + <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-TWO-PHASE"><code class="literal">two_phase</code></a> + 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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>). 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 > 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 16.2 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 |