Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
app-pgdump.html863 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>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.3 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 — 3   extract a <span class="productname">PostgreSQL</span> database into a script file or other archive file4  </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>5   <span class="application">pg_dump</span> is a utility for backing up a6   <span class="productname">PostgreSQL</span> database. It makes consistent7   backups even if the database is being used concurrently.8   <span class="application">pg_dump</span> does not block other users9   accessing the database (readers or writers).10  </p><p>11   <span class="application">pg_dump</span> only dumps a single database.12   To back up an entire cluster, or to back up global objects that are13   common to all databases in a cluster (such as roles and tablespaces),14   use <a class="xref" href="app-pg-dumpall.html" title="pg_dumpall"><span class="refentrytitle"><span class="application">pg_dumpall</span></span></a>.15  </p><p>16   Dumps can be output in script or archive file formats. Script17   dumps are plain-text files containing the SQL commands required18   to reconstruct the database to the state it was in at the time it was19   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 files20   can be used to reconstruct the database even on other machines and21   other architectures; with some modifications, even on other SQL22   database products.23  </p><p>24   The alternative archive file formats must be used with25   <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.  They26   allow <span class="application">pg_restore</span> to be selective about27   what is restored, or even to reorder the items prior to being28   restored.29   The archive file formats are designed to be portable across30   architectures.31  </p><p>32   When used with one of the archive file formats and combined with33   <span class="application">pg_restore</span>,34   <span class="application">pg_dump</span> provides a flexible archival and35   transfer mechanism. <span class="application">pg_dump</span> can be used to36   backup an entire database, then <span class="application">pg_restore</span>37   can be used to examine the archive and/or select which parts of the38   database are to be restored. The most flexible output file formats are39   the <span class="quote">“<span class="quote">custom</span>”</span> format (<code class="option">-Fc</code>) and the40   <span class="quote">“<span class="quote">directory</span>”</span> format (<code class="option">-Fd</code>). They allow41   for selection and reordering of all archived items, support parallel42   restoration, and are compressed by default. The <span class="quote">“<span class="quote">directory</span>”</span>43   format is the only format that supports parallel dumps.44  </p><p>45   While running <span class="application">pg_dump</span>, one should examine the46   output for any warnings (printed on standard error), especially in47   light of the limitations listed below.48  </p></div><div class="refsect1" id="PG-DUMP-OPTIONS"><h2>Options</h2><p>49    The following command-line options control the content and50    format of the output.51 52    </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>dbname</code></em></span></dt><dd><p>53        Specifies the name of the database to be dumped.  If this is54        not specified, the environment variable55        <code class="envar">PGDATABASE</code> is used.  If that is not set, the56        user name specified for the connection is used.57       </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>58        Dump only the data, not the schema (data definitions).59        Table data, large objects, and sequence values are dumped.60       </p><p>61        This option is similar to, but for historical reasons not identical62        to, specifying <code class="option">--section=data</code>.63       </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>64        Include large objects in the dump.  This is the default behavior65        except when <code class="option">--schema</code>, <code class="option">--table</code>, or66        <code class="option">--schema-only</code> is specified.  The <code class="option">-b</code>67        switch is therefore only useful to add large objects to dumps68        where a specific schema or table has been requested.  Note that69        large objects are considered data and therefore will be included when70        <code class="option">--data-only</code> is used, but not71        when <code class="option">--schema-only</code> is.72       </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>73        Exclude large objects in the dump.74       </p><p>75        When both <code class="option">-b</code> and <code class="option">-B</code> are given, the behavior76        is to output large objects, when data is being dumped, see the77        <code class="option">-b</code> documentation.78       </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>79        Output commands to <code class="command">DROP</code> all the dumped80        database objects prior to outputting the commands for creating them.81        This option is useful when the restore is to overwrite an existing82        database.  If any of the objects do not exist in the destination83        database, ignorable error messages will be reported during84        restore, unless <code class="option">--if-exists</code> is also specified.85       </p><p>86        This option is ignored when emitting an archive (non-text) output87        file.  For the archive formats, you can specify the option when you88        call <code class="command">pg_restore</code>.89       </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>90        Begin the output with a command to create the91        database itself and reconnect to the created database.  (With a92        script of this form, it doesn't matter which database in the93        destination installation you connect to before running the script.)94        If <code class="option">--clean</code> is also specified, the script drops and95        recreates the target database before reconnecting to it.96       </p><p>97        With <code class="option">--create</code>, the output also includes the98        database's comment if any, and any configuration variable settings99        that are specific to this database, that is,100        any <code class="command">ALTER DATABASE ... SET ...</code>101        and <code class="command">ALTER ROLE ... IN DATABASE ... SET ...</code>102        commands that mention this database.103        Access privileges for the database itself are also dumped,104        unless <code class="option">--no-acl</code> is specified.105       </p><p>106        This option is ignored when emitting an archive (non-text) output107        file.  For the archive formats, you can specify the option when you108        call <code class="command">pg_restore</code>.109       </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>110        Dump only extensions matching <em class="replaceable"><code>pattern</code></em>.  When this option is not111        specified, all non-system extensions in the target database will be112        dumped.  Multiple extensions can be selected by writing multiple113        <code class="option">-e</code> switches.  The <em class="replaceable"><code>pattern</code></em> parameter is interpreted as a114        pattern according to the same rules used by115        <span class="application">psql</span>'s <code class="literal">\d</code> commands (see116        <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a>), so multiple extensions can also117        be selected by writing wildcard characters in the pattern.  When using118        wildcards, be careful to quote the pattern if needed to prevent the119        shell from expanding the wildcards.120       </p><p>121        Any configuration relation registered by122        <code class="function">pg_extension_config_dump</code> is included in the123        dump if its extension is specified by <code class="option">--extension</code>.124       </p><div class="note"><h3 class="title">Note</h3><p>125         When <code class="option">-e</code> is specified,126         <span class="application">pg_dump</span> makes no attempt to dump any other127         database objects that the selected extension(s) might depend upon.128         Therefore, there is no guarantee that the results of a129         specific-extension dump can be successfully restored by themselves130         into a clean database.131        </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>132        Create the dump in the specified character set encoding. By default,133        the dump is created in the database encoding.  (Another way to get the134        same result is to set the <code class="envar">PGCLIENTENCODING</code> environment135        variable to the desired dump encoding.)  The supported encodings are136        described in <a class="xref" href="multibyte.html#MULTIBYTE-CHARSET-SUPPORTED" title="24.3.1. Supported Character Sets">Section 24.3.1</a>.137       </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>138        Send output to the specified file. This parameter can be omitted for139        file based output formats, in which case the standard output is used.140        It must be given for the directory output format however, where it141        specifies the target directory instead of a file. In this case the142        directory is created by <code class="command">pg_dump</code> and must not exist143        before.144       </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>145        Selects the format of the output.146        <em class="replaceable"><code>format</code></em> can be one of the following:147 148       </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>149           Output a plain-text <acronym class="acronym">SQL</acronym> script file (the default).150          </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>151           Output a custom-format archive suitable for input into152           <span class="application">pg_restore</span>.153           Together with the directory output format, this is the most flexible154           output format in that it allows manual selection and reordering of155           archived items during restore. This format is also compressed by156           default.157          </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>158           Output a directory-format archive suitable for input into159           <span class="application">pg_restore</span>. This will create a directory160           with one file for each table and large object being dumped, plus a161           so-called Table of Contents file describing the dumped objects in a162           machine-readable format that <span class="application">pg_restore</span>163           can read. A directory format archive can be manipulated with164           standard Unix tools; for example, files in an uncompressed archive165           can be compressed with the <span class="application">gzip</span>,166           <span class="application">lz4</span>, or167           <span class="application">zstd</span> tools.168           This format is compressed by default using <code class="literal">gzip</code>169           and also supports parallel dumps.170          </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>171           Output a <code class="command">tar</code>-format archive suitable for input172           into <span class="application">pg_restore</span>. The tar format is173           compatible with the directory format: extracting a tar-format174           archive produces a valid directory-format archive.175           However, the tar format does not support compression. Also, when176           using tar format the relative order of table data items cannot be177           changed during restore.178          </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>179        Run the dump in parallel by dumping <em class="replaceable"><code>njobs</code></em>180        tables simultaneously. This option may reduce the time needed to perform the dump but it also181        increases the load on the database server. You can only use this option with the182        directory output format because this is the only output format where multiple processes183        can write their data at the same time.184       </p><p><span class="application">pg_dump</span> will open <em class="replaceable"><code>njobs</code></em>185        + 1 connections to the database, so make sure your <a class="xref" href="runtime-config-connection.html#GUC-MAX-CONNECTIONS">max_connections</a>186        setting is high enough to accommodate all connections.187       </p><p>188        Requesting exclusive locks on database objects while running a parallel dump could189        cause the dump to fail. The reason is that the <span class="application">pg_dump</span> leader process190        requests shared locks (<a class="link" href="explicit-locking.html#LOCKING-TABLES" title="13.3.1. Table-Level Locks">ACCESS SHARE</a>) on the191        objects that the worker processes are going to dump later in order to192        make sure that nobody deletes them and makes them go away while the dump is running.193        If another client then requests an exclusive lock on a table, that lock will not be194        granted but will be queued waiting for the shared lock of the leader process to be195        released. Consequently any other access to the table will not be granted either and196        will queue after the exclusive lock request. This includes the worker process trying197        to dump the table. Without any precautions this would be a classic deadlock situation.198        To detect this conflict, the <span class="application">pg_dump</span> worker process requests another199        shared lock using the <code class="literal">NOWAIT</code> option. If the worker process is not granted200        this shared lock, somebody else must have requested an exclusive lock in the meantime201        and there is no way to continue with the dump, so <span class="application">pg_dump</span> has no choice202        but to abort the dump.203       </p><p>204        To perform a parallel dump, the database server needs to support205        synchronized snapshots, a feature that was introduced in206        <span class="productname">PostgreSQL</span> 9.2 for primary servers and 10207        for standbys. With this feature, database clients can ensure they see208        the same data set even though they use different connections.209        <code class="command">pg_dump -j</code> uses multiple database connections; it210        connects to the database once with the leader process and once again211        for each worker job. Without the synchronized snapshot feature, the212        different worker jobs wouldn't be guaranteed to see the same data in213        each connection, which could lead to an inconsistent backup.214       </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>215        Dump only schemas matching <em class="replaceable"><code>pattern</code></em>; this selects both the216        schema itself, and all its contained objects.  When this option is217        not specified, all non-system schemas in the target database will be218        dumped.  Multiple schemas can be219        selected by writing multiple <code class="option">-n</code> switches.  The220        <em class="replaceable"><code>pattern</code></em> parameter is221        interpreted as a pattern according to the same rules used by222        <span class="application">psql</span>'s <code class="literal">\d</code> commands223        (see <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a>),224        so multiple schemas can also be selected by writing wildcard characters225        in the pattern.  When using wildcards, be careful to quote the pattern226        if needed to prevent the shell from expanding the wildcards;  see227        <a class="xref" href="app-pgdump.html#PG-DUMP-EXAMPLES" title="Examples">Examples</a> below.228       </p><div class="note"><h3 class="title">Note</h3><p>229         When <code class="option">-n</code> is specified, <span class="application">pg_dump</span>230         makes no attempt to dump any other database objects that the selected231         schema(s) might depend upon. Therefore, there is no guarantee232         that the results of a specific-schema dump can be successfully233         restored by themselves into a clean database.234        </p></div><div class="note"><h3 class="title">Note</h3><p>235         Non-schema objects such as large objects are not dumped when <code class="option">-n</code> is236         specified.  You can add large objects back to the dump with the237         <code class="option">--large-objects</code> switch.238        </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>239        Do not dump any schemas matching <em class="replaceable"><code>pattern</code></em>.  The pattern is240        interpreted according to the same rules as for <code class="option">-n</code>.241        <code class="option">-N</code> can be given more than once to exclude schemas242        matching any of several patterns.243       </p><p>244        When both <code class="option">-n</code> and <code class="option">-N</code> are given, the behavior245        is to dump just the schemas that match at least one <code class="option">-n</code>246        switch but no <code class="option">-N</code> switches.  If <code class="option">-N</code> appears247        without <code class="option">-n</code>, then schemas matching <code class="option">-N</code> are248        excluded from what is otherwise a normal dump.249       </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>250        Do not output commands to set251        ownership of objects to match the original database.252        By default, <span class="application">pg_dump</span> issues253        <code class="command">ALTER OWNER</code> or254        <code class="command">SET SESSION AUTHORIZATION</code>255        statements to set ownership of created database objects.256        These statements257        will fail when the script is run unless it is started by a superuser258        (or the same user that owns all of the objects in the script).259        To make a script that can be restored by any user, but will give260        that user ownership of all the objects, specify <code class="option">-O</code>.261       </p><p>262        This option is ignored when emitting an archive (non-text) output263        file.  For the archive formats, you can specify the option when you264        call <code class="command">pg_restore</code>.265       </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>266        This option is obsolete but still accepted for backwards267        compatibility.268       </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>269        Dump only the object definitions (schema), not data.270       </p><p>271        This option is the inverse of <code class="option">--data-only</code>.272        It is similar to, but for historical reasons not identical to,273        specifying274        <code class="option">--section=pre-data --section=post-data</code>.275       </p><p>276        (Do not confuse this with the <code class="option">--schema</code> option, which277        uses the word <span class="quote">“<span class="quote">schema</span>”</span> in a different meaning.)278       </p><p>279        To exclude table data for only a subset of tables in the database,280        see <code class="option">--exclude-table-data</code>.281       </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>282        Specify the superuser user name to use when disabling triggers.283        This is relevant only if <code class="option">--disable-triggers</code> is used.284        (Usually, it's better to leave this out, and instead start the285        resulting script as superuser.)286       </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>287        Dump only tables with names matching288        <em class="replaceable"><code>pattern</code></em>. Multiple tables289        can be selected by writing multiple <code class="option">-t</code> switches.  The290        <em class="replaceable"><code>pattern</code></em> parameter is291        interpreted as a pattern according to the same rules used by292        <span class="application">psql</span>'s <code class="literal">\d</code> commands293        (see <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a>),294        so multiple tables can also be selected by writing wildcard characters295        in the pattern.  When using wildcards, be careful to quote the pattern296        if needed to prevent the shell from expanding the wildcards;  see297        <a class="xref" href="app-pgdump.html#PG-DUMP-EXAMPLES" title="Examples">Examples</a> below.298       </p><p>299        As well as tables, this option can be used to dump the definition of matching300        views, materialized views, foreign tables, and sequences.  It will not dump the301        contents of views or materialized views, and the contents of foreign tables will302        only be dumped if the corresponding foreign server is specified with303        <code class="option">--include-foreign-data</code>.304       </p><p>305        The <code class="option">-n</code> and <code class="option">-N</code> switches have no effect when306        <code class="option">-t</code> is used, because tables selected by <code class="option">-t</code> will307        be dumped regardless of those switches, and non-table objects will not308        be dumped.309       </p><div class="note"><h3 class="title">Note</h3><p>310         When <code class="option">-t</code> is specified, <span class="application">pg_dump</span>311         makes no attempt to dump any other database objects that the selected312         table(s) might depend upon. Therefore, there is no guarantee313         that the results of a specific-table dump can be successfully314         restored by themselves into a clean database.315        </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>316        Do not dump any tables matching <em class="replaceable"><code>pattern</code></em>.  The pattern is317        interpreted according to the same rules as for <code class="option">-t</code>.318        <code class="option">-T</code> can be given more than once to exclude tables319        matching any of several patterns.320       </p><p>321        When both <code class="option">-t</code> and <code class="option">-T</code> are given, the behavior322        is to dump just the tables that match at least one <code class="option">-t</code>323        switch but no <code class="option">-T</code> switches.  If <code class="option">-T</code> appears324        without <code class="option">-t</code>, then tables matching <code class="option">-T</code> are325        excluded from what is otherwise a normal dump.326       </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>327        Specifies verbose mode.  This will cause328        <span class="application">pg_dump</span> to output detailed object329        comments and start/stop times to the dump file, and progress330        messages to standard error.331        Repeating the option causes additional debug-level messages332        to appear on standard error.333       </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>334       Print the <span class="application">pg_dump</span> version and exit.335       </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>336        Prevent dumping of access privileges (grant/revoke commands).337       </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>338        Specify the compression method and/or the compression level to use.339        The compression method can be set to <code class="literal">gzip</code>,340        <code class="literal">lz4</code>, <code class="literal">zstd</code>,341        or <code class="literal">none</code> for no compression.342        A compression detail string can optionally be specified.  If the343        detail string is an integer, it specifies the compression level.344        Otherwise, it should be a comma-separated list of items, each of the345        form <code class="literal">keyword</code> or <code class="literal">keyword=value</code>.346        Currently, the supported keywords are <code class="literal">level</code> and347        <code class="literal">long</code>.348       </p><p>349        If no compression level is specified, the default compression350        level will be used. If only a level is specified without mentioning351        an algorithm, <code class="literal">gzip</code> compression will be used if352        the level is greater than <code class="literal">0</code>, and no compression353        will be used if the level is <code class="literal">0</code>.354       </p><p>355        For the custom and directory archive formats, this specifies compression of356        individual table-data segments, and the default is to compress using357        <code class="literal">gzip</code> at a moderate level. For plain text output,358        setting a nonzero compression level causes the entire output file to be compressed,359        as though it had been fed through <span class="application">gzip</span>,360        <span class="application">lz4</span>, or <span class="application">zstd</span>;361        but the default is not to compress.362        With zstd compression, <code class="literal">long</code> mode may improve the363        compression ratio, at the cost of increased memory use.364       </p><p>365        The tar archive format currently does not support compression at all.366       </p></dd><dt><span class="term"><code class="option">--binary-upgrade</code></span></dt><dd><p>367        This option is for use by in-place upgrade utilities.  Its use368        for other purposes is not recommended or supported.  The369        behavior of the option may change in future releases without370        notice.371       </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>372        Dump data as <code class="command">INSERT</code> commands with explicit373        column names (<code class="literal">INSERT INTO374        <em class="replaceable"><code>table</code></em>375        (<em class="replaceable"><code>column</code></em>, ...) VALUES376        ...</code>).  This will make restoration very slow; it is mainly377        useful for making dumps that can be loaded into378        non-<span class="productname">PostgreSQL</span> databases.379        Any error during restoring will cause only rows that are part of the380        problematic <code class="command">INSERT</code> to be lost, rather than the381        entire table contents.382       </p></dd><dt><span class="term"><code class="option">--disable-dollar-quoting</code></span></dt><dd><p>383        This option disables the use of dollar quoting for function bodies,384        and forces them to be quoted using SQL standard string syntax.385       </p></dd><dt><span class="term"><code class="option">--disable-triggers</code></span></dt><dd><p>386        This option is relevant only when creating a data-only dump.387        It instructs <span class="application">pg_dump</span> to include commands388        to temporarily disable triggers on the target tables while389        the data is restored.  Use this if you have referential390        integrity checks or other triggers on the tables that you391        do not want to invoke during data restore.392       </p><p>393        Presently, the commands emitted for <code class="option">--disable-triggers</code>394        must be done as superuser.  So, you should also specify395        a superuser name with <code class="option">-S</code>, or preferably be careful to396        start the resulting script as a superuser.397       </p><p>398        This option is ignored when emitting an archive (non-text) output399        file.  For the archive formats, you can specify the option when you400        call <code class="command">pg_restore</code>.401       </p></dd><dt><span class="term"><code class="option">--enable-row-security</code></span></dt><dd><p>402        This option is relevant only when dumping the contents of a table403        which has row security.  By default, <span class="application">pg_dump</span> will set404        <a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> to off, to ensure405        that all data is dumped from the table.  If the user does not have406        sufficient privileges to bypass row security, then an error is thrown.407        This parameter instructs <span class="application">pg_dump</span> to set408        <a class="xref" href="runtime-config-client.html#GUC-ROW-SECURITY">row_security</a> to on instead, allowing the user409        to dump the parts of the contents of the table that they have access to.410       </p><p>411        Note that if you use this option currently, you probably also want412        the dump be in <code class="command">INSERT</code> format, as the413        <code class="command">COPY FROM</code> during restore does not support row security.414       </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>415        This is the same as416        the <code class="option">-T</code>/<code class="option">--exclude-table</code> option,417        except that it also excludes any partitions or inheritance child418        tables of the table(s) matching the419        <em class="replaceable"><code>pattern</code></em>.420       </p></dd><dt><span class="term"><code class="option">--exclude-table-data=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>421        Do not dump data for any tables matching <em class="replaceable"><code>pattern</code></em>. The pattern is422        interpreted according to the same rules as for <code class="option">-t</code>.423        <code class="option">--exclude-table-data</code> can be given more than once to424        exclude tables matching any of several patterns. This option is425        useful when you need the definition of a particular table even426        though you do not need the data in it.427       </p><p>428        To exclude data for all tables in the database, see <code class="option">--schema-only</code>.429       </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>430        This is the same as the <code class="option">--exclude-table-data</code> option,431        except that it also excludes data of any partitions or inheritance432        child tables of the table(s) matching the433        <em class="replaceable"><code>pattern</code></em>.434       </p></dd><dt><span class="term"><code class="option">--extra-float-digits=<em class="replaceable"><code>ndigits</code></em></code></span></dt><dd><p>435        Use the specified value of <code class="option">extra_float_digits</code> when dumping436        floating-point data, instead of the maximum available precision.437        Routine dumps made for backup purposes should not use this option.438       </p></dd><dt><span class="term"><code class="option">--if-exists</code></span></dt><dd><p>439        Use <code class="literal">DROP ... IF EXISTS</code> commands to drop objects440        in <code class="option">--clean</code> mode.  This suppresses <span class="quote">“<span class="quote">does not441        exist</span>”</span> errors that might otherwise be reported.  This442        option is not valid unless <code class="option">--clean</code> is also443        specified.444       </p></dd><dt><span class="term"><code class="option">--include-foreign-data=<em class="replaceable"><code>foreignserver</code></em></code></span></dt><dd><p>445        Dump the data for any foreign table with a foreign server446        matching <em class="replaceable"><code>foreignserver</code></em>447        pattern. Multiple foreign servers can be selected by writing multiple448        <code class="option">--include-foreign-data</code> switches.449        Also, the <em class="replaceable"><code>foreignserver</code></em> parameter is450        interpreted as a pattern according to the same rules used by451        <span class="application">psql</span>'s <code class="literal">\d</code> commands452        (see <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a>),453        so multiple foreign servers can also be selected by writing wildcard characters454        in the pattern.  When using wildcards, be careful to quote the pattern455        if needed to prevent the shell from expanding the wildcards; see456        <a class="xref" href="app-pgdump.html#PG-DUMP-EXAMPLES" title="Examples">Examples</a> below.457        The only exception is that an empty pattern is disallowed.458       </p><div class="note"><h3 class="title">Note</h3><p>459         When <code class="option">--include-foreign-data</code> is specified,460         <span class="application">pg_dump</span> does not check that the foreign461         table is writable.  Therefore, there is no guarantee that the462         results of a foreign table dump can be successfully restored.463        </p></div></dd><dt><span class="term"><code class="option">--inserts</code></span></dt><dd><p>464        Dump data as <code class="command">INSERT</code> commands (rather465        than <code class="command">COPY</code>).  This will make restoration very slow;466        it is mainly useful for making dumps that can be loaded into467        non-<span class="productname">PostgreSQL</span> databases.468        Any error during restoring will cause only rows that are part of the469        problematic <code class="command">INSERT</code> to be lost, rather than the470        entire table contents.  Note that the restore might fail altogether if471        you have rearranged column order.  The472        <code class="option">--column-inserts</code> option is safe against column order473        changes, though even slower.474       </p></dd><dt><span class="term"><code class="option">--load-via-partition-root</code></span></dt><dd><p>475        When dumping data for a table partition, make476        the <code class="command">COPY</code> or <code class="command">INSERT</code> statements477        target the root of the partitioning hierarchy that contains it, rather478        than the partition itself.  This causes the appropriate partition to479        be re-determined for each row when the data is loaded.  This may be480        useful when restoring data on a server where rows do not always fall481        into the same partitions as they did on the original server.  That482        could happen, for example, if the partitioning column is of type text483        and the two systems have different definitions of the collation used484        to sort the partitioning column.485       </p></dd><dt><span class="term"><code class="option">--lock-wait-timeout=<em class="replaceable"><code>timeout</code></em></code></span></dt><dd><p>486        Do not wait forever to acquire shared table locks at the beginning of487        the dump. Instead fail if unable to lock a table within the specified488        <em class="replaceable"><code>timeout</code></em>. The timeout may be489        specified in any of the formats accepted by <code class="command">SET490        statement_timeout</code>.  (Allowed formats vary depending on the server491        version you are dumping from, but an integer number of milliseconds492        is accepted by all versions.)493       </p></dd><dt><span class="term"><code class="option">--no-comments</code></span></dt><dd><p>494        Do not dump comments.495       </p></dd><dt><span class="term"><code class="option">--no-publications</code></span></dt><dd><p>496        Do not dump publications.497       </p></dd><dt><span class="term"><code class="option">--no-security-labels</code></span></dt><dd><p>498        Do not dump security labels.499       </p></dd><dt><span class="term"><code class="option">--no-subscriptions</code></span></dt><dd><p>500        Do not dump subscriptions.501       </p></dd><dt><span class="term"><code class="option">--no-sync</code></span></dt><dd><p>502        By default, <code class="command">pg_dump</code> will wait for all files503        to be written safely to disk.  This option causes504        <code class="command">pg_dump</code> to return without waiting, which is505        faster, but means that a subsequent operating system crash can leave506        the dump corrupt.  Generally, this option is useful for testing507        but should not be used when dumping data from production installation.508       </p></dd><dt><span class="term"><code class="option">--no-table-access-method</code></span></dt><dd><p>509        Do not output commands to select table access methods.510        With this option, all objects will be created with whichever511        table access method is the default during restore.512       </p><p>513        This option is ignored when emitting an archive (non-text) output514        file.  For the archive formats, you can specify the option when you515        call <code class="command">pg_restore</code>.516       </p></dd><dt><span class="term"><code class="option">--no-tablespaces</code></span></dt><dd><p>517        Do not output commands to select tablespaces.518        With this option, all objects will be created in whichever519        tablespace is the default during restore.520       </p><p>521        This option is ignored when emitting an archive (non-text) output522        file.  For the archive formats, you can specify the option when you523        call <code class="command">pg_restore</code>.524       </p></dd><dt><span class="term"><code class="option">--no-toast-compression</code></span></dt><dd><p>525        Do not output commands to set <acronym class="acronym">TOAST</acronym> compression526        methods.527        With this option, all columns will be restored with the default528        compression setting.529       </p></dd><dt><span class="term"><code class="option">--no-unlogged-table-data</code></span></dt><dd><p>530        Do not dump the contents of unlogged tables and sequences.  This531        option has no effect on whether or not the table and sequence532        definitions (schema) are dumped; it only suppresses dumping the table533        and sequence data. Data in unlogged tables and sequences534        is always excluded when dumping from a standby server.535       </p></dd><dt><span class="term"><code class="option">--on-conflict-do-nothing</code></span></dt><dd><p>536        Add <code class="literal">ON CONFLICT DO NOTHING</code> to537        <code class="command">INSERT</code> commands.538        This option is not valid unless <code class="option">--inserts</code>,539        <code class="option">--column-inserts</code> or540        <code class="option">--rows-per-insert</code> is also specified.541       </p></dd><dt><span class="term"><code class="option">--quote-all-identifiers</code></span></dt><dd><p>542        Force quoting of all identifiers.  This option is recommended when543        dumping a database from a server whose <span class="productname">PostgreSQL</span>544        major version is different from <span class="application">pg_dump</span>'s, or when545        the output is intended to be loaded into a server of a different546        major version.  By default, <span class="application">pg_dump</span> quotes only547        identifiers that are reserved words in its own major version.548        This sometimes results in compatibility issues when dealing with549        servers of other versions that may have slightly different sets550        of reserved words.  Using <code class="option">--quote-all-identifiers</code> prevents551        such issues, at the price of a harder-to-read dump script.552       </p></dd><dt><span class="term"><code class="option">--rows-per-insert=<em class="replaceable"><code>nrows</code></em></code></span></dt><dd><p>553        Dump data as <code class="command">INSERT</code> commands (rather than554        <code class="command">COPY</code>).  Controls the maximum number of rows per555        <code class="command">INSERT</code> command. The value specified must be a556        number greater than zero.  Any error during restoring will cause only557        rows that are part of the problematic <code class="command">INSERT</code> to be558        lost, rather than the entire table contents.559       </p></dd><dt><span class="term"><code class="option">--section=<em class="replaceable"><code>sectionname</code></em></code></span></dt><dd><p>560          Only dump the named section. The section name can be561          <code class="option">pre-data</code>, <code class="option">data</code>, or <code class="option">post-data</code>.562          This option can be specified more than once to select multiple563          sections. The default is to dump all sections.564         </p><p>565          The data section contains actual table data, large-object566          contents, and sequence values.567          Post-data items include definitions of indexes, triggers, rules,568          and constraints other than validated check constraints.569          Pre-data items include all other data definition items.570         </p></dd><dt><span class="term"><code class="option">--serializable-deferrable</code></span></dt><dd><p>571        Use a <code class="literal">serializable</code> transaction for the dump, to572        ensure that the snapshot used is consistent with later database573        states; but do this by waiting for a point in the transaction stream574        at which no anomalies can be present, so that there isn't a risk of575        the dump failing or causing other transactions to roll back with a576        <code class="literal">serialization_failure</code>.  See <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a>577        for more information about transaction isolation and concurrency578        control.579       </p><p>580        This option is not beneficial for a dump which is intended only for581        disaster recovery.  It could be useful for a dump used to load a582        copy of the database for reporting or other read-only load sharing583        while the original database continues to be updated.  Without it the584        dump may reflect a state which is not consistent with any serial585        execution of the transactions eventually committed.  For example, if586        batch processing techniques are used, a batch may show as closed in587        the dump without all of the items which are in the batch appearing.588       </p><p>589        This option will make no difference if there are no read-write590        transactions active when pg_dump is started.  If read-write591        transactions are active, the start of the dump may be delayed for an592        indeterminate length of time.  Once running, performance with or593        without the switch is the same.594       </p></dd><dt><span class="term"><code class="option">--snapshot=<em class="replaceable"><code>snapshotname</code></em></code></span></dt><dd><p>595          Use the specified synchronized snapshot when making a dump of the596          database (see597          <a class="xref" href="functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION-TABLE" title="Table 9.94. Snapshot Synchronization Functions">Table 9.94</a> for more598          details).599         </p><p>600          This option is useful when needing to synchronize the dump with601          a logical replication slot (see <a class="xref" href="logicaldecoding.html" title="Chapter 49. Logical Decoding">Chapter 49</a>)602          or with a concurrent session.603         </p><p>604          In the case of a parallel dump, the snapshot name defined by this605          option is used rather than taking a new snapshot.606         </p></dd><dt><span class="term"><code class="option">--strict-names</code></span></dt><dd><p>607        Require that each608        extension (<code class="option">-e</code>/<code class="option">--extension</code>),609        schema (<code class="option">-n</code>/<code class="option">--schema</code>) and610        table (<code class="option">-t</code>/<code class="option">--table</code>) pattern611        match at least one extension/schema/table in the database to be dumped.612        Note that if none of the extension/schema/table patterns find613        matches, <span class="application">pg_dump</span> will generate an error614        even without <code class="option">--strict-names</code>.615       </p><p>616        This option has no effect617        on <code class="option">-N</code>/<code class="option">--exclude-schema</code>,618        <code class="option">-T</code>/<code class="option">--exclude-table</code>,619        or <code class="option">--exclude-table-data</code>.  An exclude pattern failing620        to match any objects is not considered an error.621       </p></dd><dt><span class="term"><code class="option">--table-and-children=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>622        This is the same as623        the <code class="option">-t</code>/<code class="option">--table</code> option,624        except that it also includes any partitions or inheritance child625        tables of the table(s) matching the626        <em class="replaceable"><code>pattern</code></em>.627       </p></dd><dt><span class="term"><code class="option">--use-set-session-authorization</code></span></dt><dd><p>628        Output SQL-standard <code class="command">SET SESSION AUTHORIZATION</code> commands629        instead of <code class="command">ALTER OWNER</code> commands to determine object630        ownership.  This makes the dump more standards-compatible, but631        depending on the history of the objects in the dump, might not restore632        properly.  Also, a dump using <code class="command">SET SESSION AUTHORIZATION</code>633        will certainly require superuser privileges to restore correctly,634        whereas <code class="command">ALTER OWNER</code> requires lesser privileges.635       </p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p>636       Show help about <span class="application">pg_dump</span> command line637       arguments, and exit.638       </p></dd></dl></div><p>639   </p><p>640    The following command-line options control the database connection parameters.641 642    </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>643       Specifies the name of the database to connect to. This is644       equivalent to specifying <em class="replaceable"><code>dbname</code></em> as the first non-option645       argument on the command line.  The <em class="replaceable"><code>dbname</code></em>646       can be a <a class="link" href="libpq-connect.html#LIBPQ-CONNSTRING" title="34.1.1. Connection Strings">connection string</a>.647       If so, connection string parameters will override any conflicting648       command line options.649      </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>650        Specifies the host name of the machine on which the server is651        running.  If the value begins with a slash, it is used as the652        directory for the Unix domain socket. The default is taken653        from the <code class="envar">PGHOST</code> environment variable, if set,654        else a Unix domain socket connection is attempted.655       </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>656        Specifies the TCP port or local Unix domain socket file657        extension on which the server is listening for connections.658        Defaults to the <code class="envar">PGPORT</code> environment variable, if659        set, or a compiled-in default.660       </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>661        User name to connect as.662       </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>663        Never issue a password prompt.  If the server requires664        password authentication and a password is not available by665        other means such as a <code class="filename">.pgpass</code> file, the666        connection attempt will fail.  This option can be useful in667        batch jobs and scripts where no user is present to enter a668        password.669       </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>670        Force <span class="application">pg_dump</span> to prompt for a671        password before connecting to a database.672       </p><p>673        This option is never essential, since674        <span class="application">pg_dump</span> will automatically prompt675        for a password if the server demands password authentication.676        However, <span class="application">pg_dump</span> will waste a677        connection attempt finding out that the server wants a password.678        In some cases it is worth typing <code class="option">-W</code> to avoid the extra679        connection attempt.680       </p></dd><dt><span class="term"><code class="option">--role=<em class="replaceable"><code>rolename</code></em></code></span></dt><dd><p>681        Specifies a role name to be used to create the dump.682        This option causes <span class="application">pg_dump</span> to issue a683        <code class="command">SET ROLE</code> <em class="replaceable"><code>rolename</code></em>684        command after connecting to the database. It is useful when the685        authenticated user (specified by <code class="option">-U</code>) lacks privileges686        needed by <span class="application">pg_dump</span>, but can switch to a role with687        the required rights.  Some installations have a policy against688        logging in directly as a superuser, and use of this option allows689        dumps to be made without violating the policy.690       </p></dd></dl></div><p>691   </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>692      Default connection parameters.693     </p></dd><dt><span class="term"><code class="envar">PG_COLOR</code></span></dt><dd><p>694      Specifies whether to use color in diagnostic messages. Possible values695      are <code class="literal">always</code>, <code class="literal">auto</code> and696      <code class="literal">never</code>.697     </p></dd></dl></div><p>698   This utility, like most other <span class="productname">PostgreSQL</span> utilities,699   also uses the environment variables supported by <span class="application">libpq</span>700   (see <a class="xref" href="libpq-envars.html" title="34.15. Environment Variables">Section 34.15</a>).701  </p></div><div class="refsect1" id="APP-PGDUMP-DIAGNOSTICS"><h2>Diagnostics</h2><p>702   <span class="application">pg_dump</span> internally executes703   <code class="command">SELECT</code> statements. If you have problems running704   <span class="application">pg_dump</span>, make sure you are able to705   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 environment706   variables used by the <span class="application">libpq</span> front-end707   library will apply.708  </p><p>709   The database activity of <span class="application">pg_dump</span> is710   normally collected by the cumulative statistics system.  If this is711   undesirable, you can set parameter <code class="varname">track_counts</code>712   to false via <code class="envar">PGOPTIONS</code> or the <code class="literal">ALTER713   USER</code> command.714  </p></div><div class="refsect1" id="PG-DUMP-NOTES"><h2>Notes</h2><p>715   If your database cluster has any local additions to the <code class="literal">template1</code> database,716   be careful to restore the output of <span class="application">pg_dump</span> into a717   truly empty database; otherwise you are likely to get errors due to718   duplicate definitions of the added objects.  To make an empty database719   without any local additions, copy from <code class="literal">template0</code> not <code class="literal">template1</code>,720   for example:721</p><pre class="programlisting">722CREATE DATABASE foo WITH TEMPLATE template0;723</pre><p>724  </p><p>725   When a data-only dump is chosen and the option <code class="option">--disable-triggers</code>726   is used, <span class="application">pg_dump</span> emits commands727   to disable triggers on user tables before inserting the data,728   and then commands to re-enable them after the data has been729   inserted.  If the restore is stopped in the middle, the system730   catalogs might be left in the wrong state.731  </p><p>732   The dump file produced by <span class="application">pg_dump</span>733   does not contain the statistics used by the optimizer to make734   query planning decisions.  Therefore, it is wise to run735   <code class="command">ANALYZE</code> after restoring from a dump file736   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>737   and <a class="xref" href="routine-vacuuming.html#AUTOVACUUM" title="25.1.6. The Autovacuum Daemon">Section 25.1.6</a> for more information.738  </p><p>739   Because <span class="application">pg_dump</span> is used to transfer data740   to newer versions of <span class="productname">PostgreSQL</span>, the output of741   <span class="application">pg_dump</span> can be expected to load into742   <span class="productname">PostgreSQL</span> server versions newer than743   <span class="application">pg_dump</span>'s version.  <span class="application">pg_dump</span> can also744   dump from <span class="productname">PostgreSQL</span> servers older than its own version.745   (Currently, servers back to version 9.2 are supported.)746   However, <span class="application">pg_dump</span> cannot dump from747   <span class="productname">PostgreSQL</span> servers newer than its own major version;748   it will refuse to even try, rather than risk making an invalid dump.749   Also, it is not guaranteed that <span class="application">pg_dump</span>'s output can750   be loaded into a server of an older major version — not even if the751   dump was taken from a server of that version.  Loading a dump file752   into an older server may require manual editing of the dump file753   to remove syntax not understood by the older server.754   Use of the <code class="option">--quote-all-identifiers</code> option is recommended755   in cross-version cases, as it can prevent problems arising from varying756   reserved-word lists in different <span class="productname">PostgreSQL</span> versions.757  </p><p>758   When dumping logical replication subscriptions,759   <span class="application">pg_dump</span> will generate <code class="command">CREATE760   SUBSCRIPTION</code> commands that use the <code class="literal">connect = false</code>761   option, so that restoring the subscription does not make remote connections762   for creating a replication slot or for initial table copy.  That way, the763   dump can be restored without requiring network access to the remote764   servers.  It is then up to the user to reactivate the subscriptions in a765   suitable way.  If the involved hosts have changed, the connection766   information might have to be changed.  It might also be appropriate to767   truncate the target tables before initiating a new full table copy.  If users768   intend to copy initial data during refresh they must create the slot with769   <code class="literal">two_phase = false</code>.  After the initial sync, the770   <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-TWO-PHASE"><code class="literal">two_phase</code></a>771   option will be automatically enabled by the subscriber if the subscription772   had been originally created with <code class="literal">two_phase = true</code> option.773  </p></div><div class="refsect1" id="PG-DUMP-EXAMPLES"><h2>Examples</h2><p>774   To dump a database called <code class="literal">mydb</code> into an SQL-script file:775</p><pre class="screen">776<code class="prompt">$</code> <strong class="userinput"><code>pg_dump mydb &gt; db.sql</code></strong>777</pre><p>778  </p><p>779   To reload such a script into a (freshly created) database named780   <code class="literal">newdb</code>:781 782</p><pre class="screen">783<code class="prompt">$</code> <strong class="userinput"><code>psql -d newdb -f db.sql</code></strong>784</pre><p>785  </p><p>786   To dump a database into a custom-format archive file:787 788</p><pre class="screen">789<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -Fc mydb &gt; db.dump</code></strong>790</pre><p>791  </p><p>792   To dump a database into a directory-format archive:793 794</p><pre class="screen">795<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -Fd mydb -f dumpdir</code></strong>796</pre><p>797  </p><p>798   To dump a database into a directory-format archive in parallel with799   5 worker jobs:800 801</p><pre class="screen">802<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -Fd mydb -j 5 -f dumpdir</code></strong>803</pre><p>804  </p><p>805   To reload an archive file into a (freshly created) database named806   <code class="literal">newdb</code>:807 808</p><pre class="screen">809<code class="prompt">$</code> <strong class="userinput"><code>pg_restore -d newdb db.dump</code></strong>810</pre><p>811  </p><p>812   To reload an archive file into the same database it was dumped from,813   discarding the current contents of that database:814 815</p><pre class="screen">816<code class="prompt">$</code> <strong class="userinput"><code>pg_restore -d postgres --clean --create db.dump</code></strong>817</pre><p>818  </p><p>819   To dump a single table named <code class="literal">mytab</code>:820 821</p><pre class="screen">822<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -t mytab mydb &gt; db.sql</code></strong>823</pre><p>824  </p><p>825   To dump all tables whose names start with <code class="literal">emp</code> in the826   <code class="literal">detroit</code> schema, except for the table named827   <code class="literal">employee_log</code>:828 829</p><pre class="screen">830<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb &gt; db.sql</code></strong>831</pre><p>832  </p><p>833   To dump all schemas whose names start with <code class="literal">east</code> or834   <code class="literal">west</code> and end in <code class="literal">gsm</code>, excluding any schemas whose835   names contain the word <code class="literal">test</code>:836 837</p><pre class="screen">838<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb &gt; db.sql</code></strong>839</pre><p>840  </p><p>841   The same, using regular expression notation to consolidate the switches:842 843</p><pre class="screen">844<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -n '(east|west)*gsm' -N '*test*' mydb &gt; db.sql</code></strong>845</pre><p>846  </p><p>847   To dump all database objects except for tables whose names begin with848   <code class="literal">ts_</code>:849 850</p><pre class="screen">851<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -T 'ts_*' mydb &gt; db.sql</code></strong>852</pre><p>853  </p><p>854   To specify an upper-case or mixed-case name in <code class="option">-t</code> and related855   switches, you need to double-quote the name; else it will be folded to856   lower case (see <a class="xref" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns">Patterns</a>).  But857   double quotes are special to the shell, so in turn they must be quoted.858   Thus, to dump a single table with a mixed-case name, you need something859   like860 861</p><pre class="screen">862<code class="prompt">$</code> <strong class="userinput"><code>pg_dump -t "\"MixedCaseName\"" mydb &gt; mytab.sql</code></strong>863</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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> <span class="application">pg_dumpall</span></td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai