codekingpro/portable-devtools
115k
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 > 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 > 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 > 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 > 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 > 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 > 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 > 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 > 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>