Debian Codegraph @

doc/src/sgml/ref/pg_dump.sgml

raw
1 <!--
2 doc/src/sgml/ref/pg_dump.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="app-pgdump">
7 <indexterm zone="app-pgdump">
8 <primary>pg_dump</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle><application>pg_dump</application></refentrytitle>
13 <manvolnum>1</manvolnum>
14 <refmiscinfo>Application</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>pg_dump</refname>
19
20 <refpurpose>
21 export a <productname>PostgreSQL</productname> database as an SQL script or to other formats
22 </refpurpose>
23 </refnamediv>
24
25 <refsynopsisdiv>
26 <cmdsynopsis>
27 <command>pg_dump</command>
28 <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
29 <arg rep="repeat"><replaceable>option</replaceable></arg>
30 <arg choice="opt"><replaceable>dbname</replaceable></arg>
31 </cmdsynopsis>
32 </refsynopsisdiv>
33
34
35 <refsect1 id="pg-dump-description">
36 <title>Description</title>
37
38 <para>
39 <application>pg_dump</application> is a utility for exporting a
40 <productname>PostgreSQL</productname> database. It makes consistent
41 exports even if the database is being used concurrently.
42 <application>pg_dump</application> does not block other users
43 accessing the database (readers or writers).
44 Note, however, that except in simple cases,
45 <application>pg_dump</application> is generally not the right choice for
46 taking regular backups of production databases. See <xref
47 linkend="backup"/> for further discussion.
48 </para>
49
50 <para>
51 <application>pg_dump</application> only dumps a single database.
52 To export an entire cluster, or to export global objects that are
53 common to all databases in a cluster (such as roles and tablespaces),
54 use <xref linkend="app-pg-dumpall"/>.
55 </para>
56
57 <para>
58 Dumps can be output in script or archive file formats. Script
59 dumps are plain-text files containing the SQL commands required
60 to reconstruct the database to the state it was in at the time it was
61 saved. To restore from such a script, feed it to <xref
62 linkend="app-psql"/>. Script files
63 can be used to reconstruct the database even on other machines and
64 other architectures; with some modifications, even on other SQL
65 database products.
66 </para>
67
68 <para>
69 The alternative archive file formats must be used with
70 <xref linkend="app-pgrestore"/> to rebuild the database. They
71 allow <application>pg_restore</application> to be selective about
72 what is restored, or even to reorder the items prior to being
73 restored.
74 The archive file formats are designed to be portable across
75 architectures.
76 </para>
77
78 <para>
79 When used with one of the archive file formats and combined with
80 <application>pg_restore</application>,
81 <application>pg_dump</application> provides a flexible archival and
82 transfer mechanism. <application>pg_dump</application> can be used to
83 export an entire database, then <application>pg_restore</application>
84 can be used to examine the archive and/or select which parts of the
85 database are to be restored. The most flexible output file formats are
86 the <quote>custom</quote> format (<option>-Fc</option>) and the
87 <quote>directory</quote> format (<option>-Fd</option>). They allow
88 for selection and reordering of all archived items, support parallel
89 restoration, and are compressed by default. The <quote>directory</quote>
90 format is the only format that supports parallel dumps.
91 </para>
92
93 <para>
94 While running <application>pg_dump</application>, one should examine the
95 output for any warnings (printed on standard error), especially in
96 light of the limitations listed below.
97 </para>
98
99 <warning>
100 <para>
101 Restoring a dump causes the destination to execute arbitrary code of the
102 source superusers' choice. Partial dumps and partial restores do not limit
103 that. If the source superusers are not trusted, the dumped SQL statements
104 must be inspected before restoring. Non-plain-text dumps can be inspected
105 by using <application>pg_restore</application>'s <option>--file</option>
106 option. Note that the client running the dump and restore need not trust
107 the source or destination superusers.
108 </para>
109 </warning>
110
111 </refsect1>
112
113 <refsect1 id="pg-dump-options">
114 <title>Options</title>
115
116 <para>
117 The following command-line options control the content and
118 format of the output.
119
120 <variablelist>
121 <varlistentry>
122 <term><replaceable class="parameter">dbname</replaceable></term>
123 <listitem>
124 <para>
125 Specifies the name of the database to be dumped. If this is
126 not specified, the environment variable
127 <envar>PGDATABASE</envar> is used. If that is not set, the
128 user name specified for the connection is used.
129 </para>
130 </listitem>
131 </varlistentry>
132
133 <varlistentry>
134 <term><option>-a</option></term>
135 <term><option>--data-only</option></term>
136 <listitem>
137 <para>
138 Dump only the data, not the schema (data definitions) or statistics.
139 Table data, large objects, and sequence values are dumped.
140 </para>
141
142 <para>
143 This option is similar to, but for historical reasons not identical
144 to, specifying <option>--section=data</option>.
145 </para>
146 </listitem>
147 </varlistentry>
148
149 <varlistentry>
150 <term><option>-b</option></term>
151 <term><option>--large-objects</option></term>
152 <term><option>--blobs</option> (deprecated)</term>
153 <listitem>
154 <para>
155 Include large objects in the dump. This is the default behavior
156 except when <option>--schema</option>, <option>--table</option>,
157 <option>--schema-only</option>, <option>--statistics-only</option>, or
158 <option>--no-data</option> is specified. The <option>-b</option>
159 switch is therefore only useful to add large objects to dumps where a
160 specific schema or table has been requested. Note that large objects
161 are considered data and therefore will be included when
162 <option>--data-only</option> is used, but not when
163 <option>--schema-only</option> or <option>--statistics-only</option>
164 is.
165 </para>
166 </listitem>
167 </varlistentry>
168
169 <varlistentry>
170 <term><option>-B</option></term>
171 <term><option>--no-large-objects</option></term>
172 <term><option>--no-blobs</option> (deprecated)</term>
173 <listitem>
174 <para>
175 Exclude large objects in the dump.
176 </para>
177
178 <para>
179 When both <option>-b</option> and <option>-B</option> are given, the behavior
180 is to output large objects, when data is being dumped, see the
181 <option>-b</option> documentation.
182 </para>
183 </listitem>
184 </varlistentry>
185
186 <varlistentry>
187 <term><option>-c</option></term>
188 <term><option>--clean</option></term>
189 <listitem>
190 <para>
191 Output commands to <command>DROP</command> all the dumped
192 database objects prior to outputting the commands for creating them.
193 This option is useful when the restore is to overwrite an existing
194 database. If any of the objects do not exist in the destination
195 database, ignorable error messages will be reported during
196 restore, unless <option>--if-exists</option> is also specified.
197 </para>
198
199 <para>
200 This option is ignored when emitting an archive (non-text) output
201 file. For the archive formats, you can specify the option when you
202 call <command>pg_restore</command>.
203 </para>
204 </listitem>
205 </varlistentry>
206
207 <varlistentry>
208 <term><option>-C</option></term>
209 <term><option>--create</option></term>
210 <listitem>
211 <para>
212 Begin the output with a command to create the
213 database itself and reconnect to the created database. (With a
214 script of this form, it doesn't matter which database in the
215 destination installation you connect to before running the script.)
216 If <option>--clean</option> is also specified, the script drops and
217 recreates the target database before reconnecting to it.
218 </para>
219
220 <para>
221 With <option>--create</option>, the output also includes the
222 database's comment if any, and any configuration variable settings
223 that are specific to this database, that is,
224 any <command>ALTER DATABASE ... SET ...</command>
225 and <command>ALTER ROLE ... IN DATABASE ... SET ...</command>
226 commands that mention this database.
227 Access privileges for the database itself are also dumped,
228 unless <option>--no-acl</option> is specified.
229 </para>
230
231 <para>
232 This option is ignored when emitting an archive (non-text) output
233 file. For the archive formats, you can specify the option when you
234 call <command>pg_restore</command>.
235 </para>
236 </listitem>
237 </varlistentry>
238
239 <varlistentry>
240 <term><option>-e <replaceable class="parameter">pattern</replaceable></option></term>
241 <term><option>--extension=<replaceable class="parameter">pattern</replaceable></option></term>
242 <listitem>
243 <para>
244 Dump only extensions matching <replaceable
245 class="parameter">pattern</replaceable>. When this option is not
246 specified, all non-system extensions in the target database will be
247 dumped. Multiple extensions can be selected by writing multiple
248 <option>-e</option> switches. The <replaceable
249 class="parameter">pattern</replaceable> parameter is interpreted as a
250 pattern according to the same rules used by
251 <application>psql</application>'s <literal>\d</literal> commands (see
252 <xref linkend="app-psql-patterns"/>), so multiple extensions can also
253 be selected by writing wildcard characters in the pattern. When using
254 wildcards, be careful to quote the pattern if needed to prevent the
255 shell from expanding the wildcards.
256 </para>
257
258 <para>
259 Any configuration relation registered by
260 <function>pg_extension_config_dump</function> is included in the
261 dump if its extension is specified by <option>--extension</option>.
262 </para>
263
264 <note>
265 <para>
266 When <option>-e</option> is specified,
267 <application>pg_dump</application> makes no attempt to dump any other
268 database objects that the selected extension(s) might depend upon.
269 Therefore, there is no guarantee that the results of a
270 specific-extension dump can be successfully restored by themselves
271 into a clean database.
272 </para>
273 </note>
274 </listitem>
275 </varlistentry>
276
277 <varlistentry>
278 <term><option>-E <replaceable class="parameter">encoding</replaceable></option></term>
279 <term><option>--encoding=<replaceable class="parameter">encoding</replaceable></option></term>
280 <listitem>
281 <para>
282 Create the dump in the specified character set encoding. By default,
283 the dump is created in the database encoding. (Another way to get the
284 same result is to set the <envar>PGCLIENTENCODING</envar> environment
285 variable to the desired dump encoding.) The supported encodings are
286 described in <xref linkend="multibyte-charset-supported"/>.
287 </para>
288 </listitem>
289 </varlistentry>
290
291 <varlistentry>
292 <term><option>-f <replaceable class="parameter">file</replaceable></option></term>
293 <term><option>--file=<replaceable class="parameter">file</replaceable></option></term>
294 <listitem>
295 <para>
296 Send output to the specified file. This parameter can be omitted for
297 file based output formats, in which case the standard output is used.
298 It must be given for the directory output format however, where it
299 specifies the target directory instead of a file. In this case the
300 directory is created by <command>pg_dump</command> and must not exist
301 before.
302 </para>
303 </listitem>
304 </varlistentry>
305
306 <varlistentry>
307 <term><option>-F <replaceable class="parameter">format</replaceable></option></term>
308 <term><option>--format=<replaceable class="parameter">format</replaceable></option></term>
309 <listitem>
310 <para>
311 Selects the format of the output.
312 <replaceable>format</replaceable> can be one of the following:
313
314 <variablelist>
315 <varlistentry>
316 <term><literal>p</literal></term>
317 <term><literal>plain</literal></term>
318 <listitem>
319 <para>
320 Output a plain-text <acronym>SQL</acronym> script file (the default).
321 </para>
322 </listitem>
323 </varlistentry>
324
325 <varlistentry>
326 <term><literal>c</literal></term>
327 <term><literal>custom</literal></term>
328 <listitem>
329 <para>
330 Output a custom-format archive suitable for input into
331 <application>pg_restore</application>.
332 Together with the directory output format, this is the most flexible
333 output format in that it allows manual selection and reordering of
334 archived items during restore. This format is also compressed by
335 default.
336 </para>
337 </listitem>
338 </varlistentry>
339
340 <varlistentry>
341 <term><literal>d</literal></term>
342 <term><literal>directory</literal></term>
343 <listitem>
344 <para>
345 Output a directory-format archive suitable for input into
346 <application>pg_restore</application>. This will create a directory
347 with one file for each table and large object being dumped, plus a
348 so-called Table of Contents file describing the dumped objects in a
349 machine-readable format that <application>pg_restore</application>
350 can read. A directory format archive can be manipulated with
351 standard Unix tools; for example, files in an uncompressed archive
352 can be compressed with the <application>gzip</application>,
353 <application>lz4</application>, or
354 <application>zstd</application> tools.
355 This format is compressed by default using <literal>gzip</literal>
356 and also supports parallel dumps.
357 </para>
358 </listitem>
359 </varlistentry>
360
361 <varlistentry>
362 <term><literal>t</literal></term>
363 <term><literal>tar</literal></term>
364 <listitem>
365 <para>
366 Output a <command>tar</command>-format archive suitable for input
367 into <application>pg_restore</application>. The tar format is
368 compatible with the directory format: extracting a tar-format
369 archive produces a valid directory-format archive.
370 However, the tar format does not support compression. Also, when
371 using tar format the relative order of table data items cannot be
372 changed during restore.
373 </para>
374 </listitem>
375 </varlistentry>
376 </variablelist></para>
377 </listitem>
378 </varlistentry>
379
380 <varlistentry>
381 <term><option>-j <replaceable class="parameter">njobs</replaceable></option></term>
382 <term><option>--jobs=<replaceable class="parameter">njobs</replaceable></option></term>
383 <listitem>
384 <para>
385 Run the dump in parallel by dumping <replaceable class="parameter">njobs</replaceable>
386 tables simultaneously. This option may reduce the time needed to perform the dump but it also
387 increases the load on the database server. You can only use this option with the
388 directory output format because this is the only output format where multiple processes
389 can write their data at the same time.
390 </para>
391 <para><application>pg_dump</application> will open <replaceable class="parameter">njobs</replaceable>
392 + 1 connections to the database, so make sure your <xref linkend="guc-max-connections"/>
393 setting is high enough to accommodate all connections.
394 </para>
395 <para>
396 Requesting exclusive locks on database objects while running a parallel dump could
397 cause the dump to fail. The reason is that the <application>pg_dump</application> leader process
398 requests shared locks (<link linkend="locking-tables">ACCESS SHARE</link>) on the
399 objects that the worker processes are going to dump later in order to
400 make sure that nobody deletes them and makes them go away while the dump is running.
401 If another client then requests an exclusive lock on a table, that lock will not be
402 granted but will be queued waiting for the shared lock of the leader process to be
403 released. Consequently any other access to the table will not be granted either and
404 will queue after the exclusive lock request. This includes the worker process trying
405 to dump the table. Without any precautions this would be a classic deadlock situation.
406 To detect this conflict, the <application>pg_dump</application> worker process requests another
407 shared lock using the <literal>NOWAIT</literal> option. If the worker process is not granted
408 this shared lock, somebody else must have requested an exclusive lock in the meantime
409 and there is no way to continue with the dump, so <application>pg_dump</application> has no choice
410 but to abort the dump.
411 </para>
412 <para>
413 To perform a parallel dump, the database server needs to support
414 synchronized snapshots, a feature that was introduced in
415 <productname>PostgreSQL</productname> 9.2 for primary servers and 10
416 for standbys. With this feature, database clients can ensure they see
417 the same data set even though they use different connections.
418 <command>pg_dump -j</command> uses multiple database connections; it
419 connects to the database once with the leader process and once again
420 for each worker job. Without the synchronized snapshot feature, the
421 different worker jobs wouldn't be guaranteed to see the same data in
422 each connection, which could lead to an inconsistent backup.
423 </para>
424 </listitem>
425 </varlistentry>
426
427 <varlistentry>
428 <term><option>-n <replaceable class="parameter">pattern</replaceable></option></term>
429 <term><option>--schema=<replaceable class="parameter">pattern</replaceable></option></term>
430 <listitem>
431 <para>
432 Dump only schemas matching <replaceable
433 class="parameter">pattern</replaceable>; this selects both the
434 schema itself, and all its contained objects. When this option is
435 not specified, all non-system schemas in the target database will be
436 dumped. Multiple schemas can be
437 selected by writing multiple <option>-n</option> switches. The
438 <replaceable class="parameter">pattern</replaceable> parameter is
439 interpreted as a pattern according to the same rules used by
440 <application>psql</application>'s <literal>\d</literal> commands
441 (see <xref linkend="app-psql-patterns"/>),
442 so multiple schemas can also be selected by writing wildcard characters
443 in the pattern. When using wildcards, be careful to quote the pattern
444 if needed to prevent the shell from expanding the wildcards; see
445 <xref linkend="pg-dump-examples"/> below.
446 </para>
447
448 <note>
449 <para>
450 When <option>-n</option> is specified, <application>pg_dump</application>
451 makes no attempt to dump any other database objects that the selected
452 schema(s) might depend upon. Therefore, there is no guarantee
453 that the results of a specific-schema dump can be successfully
454 restored by themselves into a clean database.
455 </para>
456 </note>
457
458 <note>
459 <para>
460 Non-schema objects such as large objects are not dumped when <option>-n</option> is
461 specified. You can add large objects back to the dump with the
462 <option>--large-objects</option> switch.
463 </para>
464 </note>
465
466 </listitem>
467 </varlistentry>
468
469 <varlistentry>
470 <term><option>-N <replaceable class="parameter">pattern</replaceable></option></term>
471 <term><option>--exclude-schema=<replaceable class="parameter">pattern</replaceable></option></term>
472 <listitem>
473 <para>
474 Do not dump any schemas matching <replaceable
475 class="parameter">pattern</replaceable>. The pattern is
476 interpreted according to the same rules as for <option>-n</option>.
477 <option>-N</option> can be given more than once to exclude schemas
478 matching any of several patterns.
479 </para>
480
481 <para>
482 When both <option>-n</option> and <option>-N</option> are given, the behavior
483 is to dump just the schemas that match at least one <option>-n</option>
484 switch but no <option>-N</option> switches. If <option>-N</option> appears
485 without <option>-n</option>, then schemas matching <option>-N</option> are
486 excluded from what is otherwise a normal dump.
487 </para>
488 </listitem>
489 </varlistentry>
490
491 <varlistentry>
492 <term><option>-O</option></term>
493 <term><option>--no-owner</option></term>
494 <listitem>
495 <para>
496 Do not output commands to set
497 ownership of objects to match the original database.
498 By default, <application>pg_dump</application> issues
499 <command>ALTER OWNER</command> or
500 <command>SET SESSION AUTHORIZATION</command>
501 statements to set ownership of created database objects.
502 These statements
503 will fail when the script is run unless it is started by a superuser
504 (or the same user that owns all of the objects in the script).
505 To make a script that can be restored by any user, but will give
506 that user ownership of all the objects, specify <option>-O</option>.
507 </para>
508
509 <para>
510 This option is ignored when emitting an archive (non-text) output
511 file. For the archive formats, you can specify the option when you
512 call <command>pg_restore</command>.
513 </para>
514 </listitem>
515 </varlistentry>
516
517 <varlistentry>
518 <term><option>-R</option></term>
519 <term><option>--no-reconnect</option></term>
520 <listitem>
521 <para>
522 This option is obsolete but still accepted for backwards
523 compatibility.
524 </para>
525 </listitem>
526 </varlistentry>
527
528 <varlistentry>
529 <term><option>-s</option></term>
530 <term><option>--schema-only</option></term>
531 <listitem>
532 <para>
533 Dump only the object definitions (schema), not data or statistics.
534 </para>
535 <para>
536 This option cannot be used with <option>--data-only</option>
537 or <option>--statistics-only</option>.
538 It is similar to, but for historical reasons not identical to,
539 specifying
540 <option>--section=pre-data --section=post-data</option>.
541 </para>
542 <para>
543 (Do not confuse this with the <option>--schema</option> option, which
544 uses the word <quote>schema</quote> in a different meaning.)
545 </para>
546 <para>
547 To exclude table data for only a subset of tables in the database,
548 see <option>--exclude-table-data</option>.
549 </para>
550 </listitem>
551 </varlistentry>
552
553 <varlistentry>
554 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
555 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
556 <listitem>
557 <para>
558 Specify the superuser user name to use when disabling triggers.
559 This is relevant only if <option>--disable-triggers</option> is used.
560 (Usually, it's better to leave this out, and instead start the
561 resulting script as superuser.)
562 </para>
563 </listitem>
564 </varlistentry>
565
566 <varlistentry>
567 <term><option>-t <replaceable class="parameter">pattern</replaceable></option></term>
568 <term><option>--table=<replaceable class="parameter">pattern</replaceable></option></term>
569 <listitem>
570 <para>
571 Dump only tables with names matching
572 <replaceable class="parameter">pattern</replaceable>. Multiple tables
573 can be selected by writing multiple <option>-t</option> switches. The
574 <replaceable class="parameter">pattern</replaceable> parameter is
575 interpreted as a pattern according to the same rules used by
576 <application>psql</application>'s <literal>\d</literal> commands
577 (see <xref linkend="app-psql-patterns"/>),
578 so multiple tables can also be selected by writing wildcard characters
579 in the pattern. When using wildcards, be careful to quote the pattern
580 if needed to prevent the shell from expanding the wildcards; see
581 <xref linkend="pg-dump-examples"/> below.
582 </para>
583
584 <para>
585 As well as tables, this option can be used to dump the definition of matching
586 views, materialized views, foreign tables, and sequences. It will not dump the
587 contents of views or materialized views, and the contents of foreign tables will
588 only be dumped if the corresponding foreign server is specified with
589 <option>--include-foreign-data</option>.
590 </para>
591
592 <para>
593 The <option>-n</option> and <option>-N</option> switches have no effect when
594 <option>-t</option> is used, because tables selected by <option>-t</option> will
595 be dumped regardless of those switches, and non-table objects will not
596 be dumped.
597 </para>
598
599 <note>
600 <para>
601 When <option>-t</option> is specified, <application>pg_dump</application>
602 makes no attempt to dump any other database objects that the selected
603 table(s) might depend upon. Therefore, there is no guarantee
604 that the results of a specific-table dump can be successfully
605 restored by themselves into a clean database.
606 </para>
607 </note>
608
609 </listitem>
610 </varlistentry>
611
612 <varlistentry>
613 <term><option>-T <replaceable class="parameter">pattern</replaceable></option></term>
614 <term><option>--exclude-table=<replaceable class="parameter">pattern</replaceable></option></term>
615 <listitem>
616 <para>
617 Do not dump any tables matching <replaceable
618 class="parameter">pattern</replaceable>. The pattern is
619 interpreted according to the same rules as for <option>-t</option>.
620 <option>-T</option> can be given more than once to exclude tables
621 matching any of several patterns.
622 </para>
623
624 <para>
625 When both <option>-t</option> and <option>-T</option> are given, the behavior
626 is to dump just the tables that match at least one <option>-t</option>
627 switch but no <option>-T</option> switches. If <option>-T</option> appears
628 without <option>-t</option>, then tables matching <option>-T</option> are
629 excluded from what is otherwise a normal dump.
630 </para>
631 </listitem>
632 </varlistentry>
633
634 <varlistentry>
635 <term><option>-v</option></term>
636 <term><option>--verbose</option></term>
637 <listitem>
638 <para>
639 Specifies verbose mode. This will cause
640 <application>pg_dump</application> to output detailed object
641 comments and start/stop times to the dump file, and progress
642 messages to standard error.
643 Repeating the option causes additional debug-level messages
644 to appear on standard error.
645 </para>
646 </listitem>
647 </varlistentry>
648
649 <varlistentry>
650 <term><option>-V</option></term>
651 <term><option>--version</option></term>
652 <listitem>
653 <para>
654 Print the <application>pg_dump</application> version and exit.
655 </para>
656 </listitem>
657 </varlistentry>
658
659 <varlistentry>
660 <term><option>-x</option></term>
661 <term><option>--no-privileges</option></term>
662 <term><option>--no-acl</option></term>
663 <listitem>
664 <para>
665 Prevent dumping of access privileges (grant/revoke commands).
666 </para>
667 </listitem>
668 </varlistentry>
669
670 <varlistentry>
671 <term><option>-Z <replaceable class="parameter">level</replaceable></option></term>
672 <term><option>-Z <replaceable class="parameter">method</replaceable></option>[:<replaceable>detail</replaceable>]</term>
673 <term><option>--compress=<replaceable class="parameter">level</replaceable></option></term>
674 <term><option>--compress=<replaceable class="parameter">method</replaceable></option>[:<replaceable>detail</replaceable>]</term>
675 <listitem>
676 <para>
677 Specify the compression method and/or the compression level to use.
678 The compression method can be set to <literal>gzip</literal>,
679 <literal>lz4</literal>, <literal>zstd</literal>,
680 or <literal>none</literal> for no compression.
681 A compression detail string can optionally be specified. If the
682 detail string is an integer, it specifies the compression level.
683 Otherwise, it should be a comma-separated list of items, each of the
684 form <literal>keyword</literal> or <literal>keyword=value</literal>.
685 Currently, the supported keywords are <literal>level</literal> and
686 <literal>long</literal>.
687 </para>
688
689 <para>
690 If no compression level is specified, the default compression
691 level will be used. If only a level is specified without mentioning
692 an algorithm, <literal>gzip</literal> compression will be used if
693 the level is greater than <literal>0</literal>, and no compression
694 will be used if the level is <literal>0</literal>.
695 </para>
696
697 <para>
698 For the custom and directory archive formats, this specifies compression of
699 individual table-data segments, and the default is to compress using
700 <literal>gzip</literal> at a moderate level. For plain text output,
701 setting a nonzero compression level causes the entire output file to be compressed,
702 as though it had been fed through <application>gzip</application>,
703 <application>lz4</application>, or <application>zstd</application>;
704 but the default is not to compress.
705 With zstd compression, <literal>long</literal> mode may improve the
706 compression ratio, at the cost of increased memory use.
707 </para>
708 <para>
709 The tar archive format currently does not support compression at all.
710 </para>
711 </listitem>
712 </varlistentry>
713
714 <varlistentry>
715 <term><option>--binary-upgrade</option></term>
716 <listitem>
717 <para>
718 This option is for use by in-place upgrade utilities. Its use
719 for other purposes is not recommended or supported. The
720 behavior of the option may change in future releases without
721 notice.
722 </para>
723 </listitem>
724 </varlistentry>
725
726 <varlistentry>
727 <term><option>--column-inserts</option></term>
728 <term><option>--attribute-inserts</option></term>
729 <listitem>
730 <para>
731 Dump data as <command>INSERT</command> commands with explicit
732 column names (<literal>INSERT INTO
733 <replaceable>table</replaceable>
734 (<replaceable>column</replaceable>, ...) VALUES
735 ...</literal>). This will make restoration very slow; it is mainly
736 useful for making dumps that can be loaded into
737 non-<productname>PostgreSQL</productname> databases.
738 Any error during restoring will cause only rows that are part of the
739 problematic <command>INSERT</command> to be lost, rather than the
740 entire table contents.
741 </para>
742 </listitem>
743 </varlistentry>
744
745 <varlistentry>
746 <term><option>--disable-dollar-quoting</option></term>
747 <listitem>
748 <para>
749 This option disables the use of dollar quoting for function bodies,
750 and forces them to be quoted using SQL standard string syntax.
751 </para>
752 </listitem>
753 </varlistentry>
754
755 <varlistentry>
756 <term><option>--disable-triggers</option></term>
757 <listitem>
758 <para>
759 This option is relevant only when creating a dump that includes data
760 but does not include schema.
761 It instructs <application>pg_dump</application> to include commands
762 to temporarily disable triggers on the target tables while
763 the data is restored. Use this if you have referential
764 integrity checks or other triggers on the tables that you
765 do not want to invoke during data restore.
766 </para>
767
768 <para>
769 Presently, the commands emitted for <option>--disable-triggers</option>
770 must be done as superuser. So, you should also specify
771 a superuser name with <option>-S</option>, or preferably be careful to
772 start the resulting script as a superuser.
773 </para>
774
775 <para>
776 This option is ignored when emitting an archive (non-text) output
777 file. For the archive formats, you can specify the option when you
778 call <command>pg_restore</command>.
779 </para>
780 </listitem>
781 </varlistentry>
782
783 <varlistentry>
784 <term><option>--enable-row-security</option></term>
785 <listitem>
786 <para>
787 This option is relevant only when dumping the contents of a table
788 which has row security. By default, <application>pg_dump</application> will set
789 <xref linkend="guc-row-security"/> to off, to ensure
790 that all data is dumped from the table. If the user does not have
791 sufficient privileges to bypass row security, then an error is thrown.
792 This parameter instructs <application>pg_dump</application> to set
793 <xref linkend="guc-row-security"/> to on instead, allowing the user
794 to dump the parts of the contents of the table that they have access to.
795 </para>
796
797 <para>
798 Note that if you use this option currently, you probably also want
799 the dump be in <command>INSERT</command> format, as the
800 <command>COPY FROM</command> during restore does not support row security.
801 </para>
802 </listitem>
803 </varlistentry>
804
805 <varlistentry>
806 <term><option>--exclude-extension=<replaceable class="parameter">pattern</replaceable></option></term>
807 <listitem>
808 <para>
809 Do not dump any extensions matching <replaceable
810 class="parameter">pattern</replaceable>. The pattern is
811 interpreted according to the same rules as for <option>-e</option>.
812 <option>--exclude-extension</option> can be given more than once to exclude extensions
813 matching any of several patterns.
814 </para>
815
816 <para>
817 When both <option>-e</option> and <option>--exclude-extension</option> are given, the behavior
818 is to dump just the extensions that match at least one <option>-e</option>
819 switch but no <option>--exclude-extension</option> switches. If <option>--exclude-extension</option>
820 appears without <option>-e</option>, then extensions matching <option>--exclude-extension</option> are
821 excluded from what is otherwise a normal dump.
822 </para>
823 </listitem>
824 </varlistentry>
825
826 <varlistentry>
827 <term><option>--exclude-table-and-children=<replaceable class="parameter">pattern</replaceable></option></term>
828 <listitem>
829 <para>
830 This is the same as
831 the <option>-T</option>/<option>--exclude-table</option> option,
832 except that it also excludes any partitions or inheritance child
833 tables of the table(s) matching the
834 <replaceable class="parameter">pattern</replaceable>.
835 </para>
836 </listitem>
837 </varlistentry>
838
839 <varlistentry>
840 <term><option>--exclude-table-data=<replaceable class="parameter">pattern</replaceable></option></term>
841 <listitem>
842 <para>
843 Do not dump data for any tables matching <replaceable
844 class="parameter">pattern</replaceable>. The pattern is
845 interpreted according to the same rules as for <option>-t</option>.
846 <option>--exclude-table-data</option> can be given more than once to
847 exclude tables matching any of several patterns. This option is
848 useful when you need the definition of a particular table even
849 though you do not need the data in it.
850 </para>
851 <para>
852 To exclude data for all tables in the database, see <option>--schema-only</option>
853 or <option>--statistics-only</option>.
854 </para>
855 </listitem>
856 </varlistentry>
857
858 <varlistentry>
859 <term><option>--exclude-table-data-and-children=<replaceable class="parameter">pattern</replaceable></option></term>
860 <listitem>
861 <para>
862 This is the same as the <option>--exclude-table-data</option> option,
863 except that it also excludes data of any partitions or inheritance
864 child tables of the table(s) matching the
865 <replaceable class="parameter">pattern</replaceable>.
866 </para>
867 </listitem>
868 </varlistentry>
869
870 <varlistentry>
871 <term><option>--extra-float-digits=<replaceable class="parameter">ndigits</replaceable></option></term>
872 <listitem>
873 <para>
874 Use the specified value of <option>extra_float_digits</option> when dumping
875 floating-point data, instead of the maximum available precision.
876 Routine dumps made for backup purposes should not use this option.
877 </para>
878 </listitem>
879 </varlistentry>
880
881 <varlistentry>
882 <term><option>--filter=<replaceable class="parameter">filename</replaceable></option></term>
883 <listitem>
884 <para>
885 Specify a filename from which to read patterns for objects to include
886 or exclude from the dump. The patterns are interpreted according to the
887 same rules as the corresponding options:
888 <option>-t</option>/<option>--table</option>,
889 <option>--table-and-children</option>,
890 <option>-T</option>/<option>--exclude-table</option>, and
891 <option>--exclude-table-and-children</option> for tables,
892 <option>-n</option>/<option>--schema</option> and
893 <option>-N</option>/<option>--exclude-schema</option> for schemas,
894 <option>--include-foreign-data</option> for data on foreign servers,
895 <option>--exclude-table-data</option> and
896 <option>--exclude-table-data-and-children</option> for table data, and
897 <option>-e</option>/<option>--extension</option> and
898 <option>--exclude-extension</option> for extensions.
899 To read from <literal>STDIN</literal>, use <filename>-</filename> as the
900 filename. The <option>--filter</option> option can be specified in
901 conjunction with the above listed options for including or excluding
902 objects, and can also be specified more than once for multiple filter
903 files.
904 </para>
905
906 <para>
907 The file lists one object pattern per row, with the following format:
908 <synopsis>
909 { include | exclude } { extension | foreign_data | table | table_and_children | table_data | table_data_and_children | schema } <replaceable class="parameter">PATTERN</replaceable>
910 </synopsis>
911 </para>
912
913 <para>
914 The first keyword specifies whether the objects matched by the pattern
915 are to be included or excluded. The second keyword specifies the type
916 of object to be filtered using the pattern:
917 <itemizedlist>
918 <listitem>
919 <para>
920 <literal>extension</literal>: extensions. This works like the
921 <option>-e</option>/<option>--extension</option> or
922 <option>--exclude-extension</option> option.
923 </para>
924 </listitem>
925 <listitem>
926 <para>
927 <literal>foreign_data</literal>: data on foreign servers. This works like
928 the <option>--include-foreign-data</option> option. This keyword can
929 only be used with the <literal>include</literal> keyword.
930 </para>
931 </listitem>
932 <listitem>
933 <para>
934 <literal>table</literal>: tables. This works like the
935 <option>-t</option>/<option>--table</option> or
936 <option>-T</option>/<option>--exclude-table</option> option.
937 </para>
938 </listitem>
939 <listitem>
940 <para>
941 <literal>table_and_children</literal>: tables including any partitions
942 or inheritance child tables. This works like the
943 <option>--table-and-children</option> or
944 <option>--exclude-table-and-children</option> option.
945 </para>
946 </listitem>
947 <listitem>
948 <para>
949 <literal>table_data</literal>: table data of any tables matching
950 <replaceable>pattern</replaceable>. This works like the
951 <option>--exclude-table-data</option> option. This keyword can only
952 be used with the <literal>exclude</literal> keyword.
953 </para>
954 </listitem>
955 <listitem>
956 <para>
957 <literal>table_data_and_children</literal>: table data of any tables
958 matching <replaceable>pattern</replaceable> as well as any partitions
959 or inheritance children of the table(s). This works like the
960 <option>--exclude-table-data-and-children</option> option. This
961 keyword can only be used with the <literal>exclude</literal> keyword.
962 </para>
963 </listitem>
964 <listitem>
965 <para>
966 <literal>schema</literal>: schemas. This works like the
967 <option>-n</option>/<option>--schema</option> or
968 <option>-N</option>/<option>--exclude-schema</option> option.
969 </para>
970 </listitem>
971 </itemizedlist>
972 </para>
973
974 <para>
975 Lines starting with <literal>#</literal> are considered comments and
976 ignored. Comments can be placed after an object pattern row as well.
977 Blank lines are also ignored. See <xref linkend="app-psql-patterns"/>
978 for how to perform quoting in patterns.
979 </para>
980
981 <para>
982 Example files are listed below in the <xref linkend="pg-dump-examples"/>
983 section.
984 </para>
985
986 </listitem>
987 </varlistentry>
988
989 <varlistentry>
990 <term><option>--if-exists</option></term>
991 <listitem>
992 <para>
993 Use <literal>DROP ... IF EXISTS</literal> commands to drop objects
994 in <option>--clean</option> mode. This suppresses <quote>does not
995 exist</quote> errors that might otherwise be reported. This
996 option is not valid unless <option>--clean</option> is also
997 specified.
998 </para>
999 </listitem>
1000 </varlistentry>
1001
1002 <varlistentry>
1003 <term><option>--include-foreign-data=<replaceable class="parameter">foreignserver</replaceable></option></term>
1004 <listitem>
1005 <para>
1006 Dump the data for any foreign table with a foreign server
1007 matching <replaceable class="parameter">foreignserver</replaceable>
1008 pattern. Multiple foreign servers can be selected by writing multiple
1009 <option>--include-foreign-data</option> switches.
1010 Also, the <replaceable class="parameter">foreignserver</replaceable> parameter is
1011 interpreted as a pattern according to the same rules used by
1012 <application>psql</application>'s <literal>\d</literal> commands
1013 (see <xref linkend="app-psql-patterns"/>),
1014 so multiple foreign servers can also be selected by writing wildcard characters
1015 in the pattern. When using wildcards, be careful to quote the pattern
1016 if needed to prevent the shell from expanding the wildcards; see
1017 <xref linkend="pg-dump-examples"/> below.
1018 The only exception is that an empty pattern is disallowed.
1019 </para>
1020
1021 <note>
1022 <para>
1023 Using wildcards in <option>--include-foreign-data</option> may result
1024 in access to unexpected foreign servers. Also, to use this option securely,
1025 make sure that the named server must have a trusted owner.
1026 </para>
1027 </note>
1028
1029 <note>
1030 <para>
1031 When <option>--include-foreign-data</option> is specified,
1032 <application>pg_dump</application> does not check that the foreign
1033 table is writable. Therefore, there is no guarantee that the
1034 results of a foreign table dump can be successfully restored.
1035 </para>
1036 </note>
1037 </listitem>
1038 </varlistentry>
1039
1040 <varlistentry>
1041 <term><option>--inserts</option></term>
1042 <listitem>
1043 <para>
1044 Dump data as <command>INSERT</command> commands (rather
1045 than <command>COPY</command>). This will make restoration very slow;
1046 it is mainly useful for making dumps that can be loaded into
1047 non-<productname>PostgreSQL</productname> databases.
1048 Any error during restoring will cause only rows that are part of the
1049 problematic <command>INSERT</command> to be lost, rather than the
1050 entire table contents. Note that the restore might fail altogether if
1051 you have rearranged column order. The
1052 <option>--column-inserts</option> option is safe against column order
1053 changes, though even slower.
1054 </para>
1055 </listitem>
1056 </varlistentry>
1057
1058 <varlistentry>
1059 <term><option>--load-via-partition-root</option></term>
1060 <listitem>
1061 <para>
1062 When dumping data for a table partition, make
1063 the <command>COPY</command> or <command>INSERT</command> statements
1064 target the root of the partitioning hierarchy that contains it, rather
1065 than the partition itself. This causes the appropriate partition to
1066 be re-determined for each row when the data is loaded. This may be
1067 useful when restoring data on a server where rows do not always fall
1068 into the same partitions as they did on the original server. That
1069 could happen, for example, if the partitioning column is of type text
1070 and the two systems have different definitions of the collation used
1071 to sort the partitioning column.
1072 </para>
1073 </listitem>
1074 </varlistentry>
1075
1076 <varlistentry>
1077 <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
1078 <listitem>
1079 <para>
1080 Do not wait forever to acquire shared table locks at the beginning of
1081 the dump. Instead fail if unable to lock a table within the specified
1082 <replaceable class="parameter">timeout</replaceable>. The timeout may be
1083 specified in any of the formats accepted by <command>SET
1084 statement_timeout</command>. (Allowed formats vary depending on the server
1085 version you are dumping from, but an integer number of milliseconds
1086 is accepted by all versions.)
1087 </para>
1088 </listitem>
1089 </varlistentry>
1090
1091 <varlistentry>
1092 <term><option>--no-comments</option></term>
1093 <listitem>
1094 <para>
1095 Do not dump <command>COMMENT</command> commands.
1096 </para>
1097 </listitem>
1098 </varlistentry>
1099
1100 <varlistentry>
1101 <term><option>--no-data</option></term>
1102 <listitem>
1103 <para>
1104 Do not dump data.
1105 </para>
1106 </listitem>
1107 </varlistentry>
1108
1109 <varlistentry>
1110 <term><option>--no-policies</option></term>
1111 <listitem>
1112 <para>
1113 Do not dump row security policies.
1114 </para>
1115 </listitem>
1116 </varlistentry>
1117
1118 <varlistentry>
1119 <term><option>--no-publications</option></term>
1120 <listitem>
1121 <para>
1122 Do not dump publications.
1123 </para>
1124 </listitem>
1125 </varlistentry>
1126
1127 <varlistentry>
1128 <term><option>--no-schema</option></term>
1129 <listitem>
1130 <para>
1131 Do not dump schema (data definitions).
1132 </para>
1133 </listitem>
1134 </varlistentry>
1135
1136 <varlistentry>
1137 <term><option>--no-security-labels</option></term>
1138 <listitem>
1139 <para>
1140 Do not dump security labels.
1141 </para>
1142 </listitem>
1143 </varlistentry>
1144
1145 <varlistentry>
1146 <term><option>--no-statistics</option></term>
1147 <listitem>
1148 <para>
1149 Do not dump statistics. This is the default.
1150 </para>
1151 </listitem>
1152 </varlistentry>
1153
1154 <varlistentry>
1155 <term><option>--no-subscriptions</option></term>
1156 <listitem>
1157 <para>
1158 Do not dump subscriptions.
1159 </para>
1160 </listitem>
1161 </varlistentry>
1162
1163 <varlistentry>
1164 <term><option>--no-sync</option></term>
1165 <listitem>
1166 <para>
1167 By default, <command>pg_dump</command> will wait for all files
1168 to be written safely to disk. This option causes
1169 <command>pg_dump</command> to return without waiting, which is
1170 faster, but means that a subsequent operating system crash can leave
1171 the dump corrupt. Generally, this option is useful for testing
1172 but should not be used when dumping data from production installation.
1173 </para>
1174 </listitem>
1175 </varlistentry>
1176
1177 <varlistentry>
1178 <term><option>--no-table-access-method</option></term>
1179 <listitem>
1180 <para>
1181 Do not output commands to select table access methods.
1182 With this option, all objects will be created with whichever
1183 table access method is the default during restore.
1184 </para>
1185
1186 <para>
1187 This option is ignored when emitting an archive (non-text) output
1188 file. For the archive formats, you can specify the option when you
1189 call <command>pg_restore</command>.
1190 </para>
1191 </listitem>
1192 </varlistentry>
1193
1194 <varlistentry>
1195 <term><option>--no-tablespaces</option></term>
1196 <listitem>
1197 <para>
1198 Do not output commands to select tablespaces.
1199 With this option, all objects will be created in whichever
1200 tablespace is the default during restore.
1201 </para>
1202
1203 <para>
1204 This option is ignored when emitting an archive (non-text) output
1205 file. For the archive formats, you can specify the option when you
1206 call <command>pg_restore</command>.
1207 </para>
1208 </listitem>
1209 </varlistentry>
1210
1211 <varlistentry>
1212 <term><option>--no-toast-compression</option></term>
1213 <listitem>
1214 <para>
1215 Do not output commands to set <acronym>TOAST</acronym> compression
1216 methods.
1217 With this option, all columns will be restored with the default
1218 compression setting.
1219 </para>
1220 </listitem>
1221 </varlistentry>
1222
1223 <varlistentry>
1224 <term><option>--no-unlogged-table-data</option></term>
1225 <listitem>
1226 <para>
1227 Do not dump the contents of unlogged tables and sequences. This
1228 option has no effect on whether or not the table and sequence
1229 definitions (schema) are dumped; it only suppresses dumping the table
1230 and sequence data. Data in unlogged tables and sequences
1231 is always excluded when dumping from a standby server.
1232 </para>
1233 </listitem>
1234 </varlistentry>
1235
1236 <varlistentry>
1237 <term><option>--on-conflict-do-nothing</option></term>
1238 <listitem>
1239 <para>
1240 Add <literal>ON CONFLICT DO NOTHING</literal> to
1241 <command>INSERT</command> commands.
1242 This option is not valid unless <option>--inserts</option>,
1243 <option>--column-inserts</option> or
1244 <option>--rows-per-insert</option> is also specified.
1245 </para>
1246 </listitem>
1247 </varlistentry>
1248
1249 <varlistentry>
1250 <term><option>--quote-all-identifiers</option></term>
1251 <listitem>
1252 <para>
1253 Force quoting of all identifiers. This option is recommended when
1254 dumping a database from a server whose <productname>PostgreSQL</productname>
1255 major version is different from <application>pg_dump</application>'s, or when
1256 the output is intended to be loaded into a server of a different
1257 major version. By default, <application>pg_dump</application> quotes only
1258 identifiers that are reserved words in its own major version.
1259 This sometimes results in compatibility issues when dealing with
1260 servers of other versions that may have slightly different sets
1261 of reserved words. Using <option>--quote-all-identifiers</option> prevents
1262 such issues, at the price of a harder-to-read dump script.
1263 </para>
1264 </listitem>
1265 </varlistentry>
1266
1267 <varlistentry>
1268 <term><option>--restrict-key=<replaceable class="parameter">restrict_key</replaceable></option></term>
1269 <listitem>
1270 <para>
1271 Use the provided string as the <application>psql</application>
1272 <command>\restrict</command> key in the dump output. This can only be
1273 specified for plain-text dumps, i.e., when <option>--format</option> is
1274 set to <literal>plain</literal> or the <option>--format</option> option
1275 is omitted. If no restrict key is specified,
1276 <application>pg_dump</application> will generate a random one as
1277 needed. Keys may contain only alphanumeric characters.
1278 </para>
1279 <para>
1280 This option is primarily intended for testing purposes and other
1281 scenarios that require repeatable output (e.g., comparing dump files).
1282 It is not recommended for general use, as a malicious server with
1283 advance knowledge of the key may be able to inject arbitrary code that
1284 will be executed on the machine that runs
1285 <application>psql</application> with the dump output.
1286 </para>
1287 </listitem>
1288 </varlistentry>
1289
1290 <varlistentry>
1291 <term><option>--rows-per-insert=<replaceable class="parameter">nrows</replaceable></option></term>
1292 <listitem>
1293 <para>
1294 Dump data as <command>INSERT</command> commands (rather than
1295 <command>COPY</command>). Controls the maximum number of rows per
1296 <command>INSERT</command> command. The value specified must be a
1297 number greater than zero. Any error during restoring will cause only
1298 rows that are part of the problematic <command>INSERT</command> to be
1299 lost, rather than the entire table contents.
1300 </para>
1301 </listitem>
1302 </varlistentry>
1303
1304 <varlistentry>
1305 <term><option>--section=<replaceable class="parameter">sectionname</replaceable></option></term>
1306 <listitem>
1307 <para>
1308 Only dump the named section. The section name can be
1309 <option>pre-data</option>, <option>data</option>, or <option>post-data</option>.
1310 This option can be specified more than once to select multiple
1311 sections. The default is to dump all sections.
1312 </para>
1313 <para>
1314 The data section contains actual table data, large-object
1315 contents, sequence values, and statistics for tables,
1316 materialized views, and foreign tables.
1317 Post-data items include definitions of indexes, triggers, rules,
1318 statistics for indexes, and constraints other than validated check
1319 and not-null constraints.
1320 Pre-data items include all other data definition items.
1321 </para>
1322 </listitem>
1323 </varlistentry>
1324
1325 <varlistentry>
1326 <term><option>--sequence-data</option></term>
1327 <listitem>
1328 <para>
1329 Include sequence data in the dump. This is the default behavior except
1330 when <option>--no-data</option>, <option>--schema-only</option>, or
1331 <option>--statistics-only</option> is specified.
1332 </para>
1333 </listitem>
1334 </varlistentry>
1335
1336 <varlistentry>
1337 <term><option>--serializable-deferrable</option></term>
1338 <listitem>
1339 <para>
1340 Use a <literal>serializable</literal> transaction for the dump, to
1341 ensure that the snapshot used is consistent with later database
1342 states; but do this by waiting for a point in the transaction stream
1343 at which no anomalies can be present, so that there isn't a risk of
1344 the dump failing or causing other transactions to roll back with a
1345 <literal>serialization_failure</literal>. See <xref linkend="mvcc"/>
1346 for more information about transaction isolation and concurrency
1347 control.
1348 </para>
1349
1350 <para>
1351 This option is not beneficial for a dump which is intended only for
1352 disaster recovery. It could be useful for a dump used to load a
1353 copy of the database for reporting or other read-only load sharing
1354 while the original database continues to be updated. Without it the
1355 dump may reflect a state which is not consistent with any serial
1356 execution of the transactions eventually committed. For example, if
1357 batch processing techniques are used, a batch may show as closed in
1358 the dump without all of the items which are in the batch appearing.
1359 </para>
1360
1361 <para>
1362 This option will make no difference if there are no read-write
1363 transactions active when pg_dump is started. If read-write
1364 transactions are active, the start of the dump may be delayed for an
1365 indeterminate length of time. Once running, performance with or
1366 without the switch is the same.
1367 </para>
1368 </listitem>
1369 </varlistentry>
1370
1371 <varlistentry>
1372 <term><option>--snapshot=<replaceable class="parameter">snapshotname</replaceable></option></term>
1373 <listitem>
1374 <para>
1375 Use the specified synchronized snapshot when making a dump of the
1376 database (see
1377 <xref linkend="functions-snapshot-synchronization-table"/> for more
1378 details).
1379 </para>
1380 <para>
1381 This option is useful when needing to synchronize the dump with
1382 a logical replication slot (see <xref linkend="logicaldecoding"/>)
1383 or with a concurrent session.
1384 </para>
1385 <para>
1386 In the case of a parallel dump, the snapshot name defined by this
1387 option is used rather than taking a new snapshot.
1388 </para>
1389 </listitem>
1390 </varlistentry>
1391
1392 <varlistentry>
1393 <term><option>--statistics</option></term>
1394 <listitem>
1395 <para>
1396 Dump optimizer statistics.
1397 </para>
1398 </listitem>
1399 </varlistentry>
1400
1401 <varlistentry>
1402 <term><option>--statistics-only</option></term>
1403 <listitem>
1404 <para>
1405 Dump only the statistics, not the schema (data definitions) or data.
1406 Optimizer statistics for tables, materialized views, foreign tables,
1407 and indexes are dumped.
1408 </para>
1409 </listitem>
1410 </varlistentry>
1411
1412 <varlistentry>
1413 <term><option>--strict-names</option></term>
1414 <listitem>
1415 <para>
1416 Require that each
1417 extension (<option>-e</option>/<option>--extension</option>),
1418 schema (<option>-n</option>/<option>--schema</option>) and
1419 table (<option>-t</option>/<option>--table</option>) pattern
1420 match at least one extension/schema/table in the database to be dumped.
1421 This also applies to filters used with <option>--filter</option>.
1422 Note that if none of the extension/schema/table patterns find
1423 matches, <application>pg_dump</application> will generate an error
1424 even without <option>--strict-names</option>.
1425 </para>
1426 <para>
1427 This option has no effect
1428 on <option>--exclude-extension</option>,
1429 <option>-N</option>/<option>--exclude-schema</option>,
1430 <option>-T</option>/<option>--exclude-table</option>,
1431 or <option>--exclude-table-data</option>. An exclude pattern failing
1432 to match any objects is not considered an error.
1433 </para>
1434 </listitem>
1435 </varlistentry>
1436
1437 <varlistentry>
1438 <term><option>--sync-method=<replaceable class="parameter">method</replaceable></option></term>
1439 <listitem>
1440 <para>
1441 When set to <literal>fsync</literal>, which is the default,
1442 <command>pg_dump --format=directory</command> will recursively open and
1443 synchronize all files in the archive directory.
1444 </para>
1445 <para>
1446 On Linux, <literal>syncfs</literal> may be used instead to ask the
1447 operating system to synchronize the whole file system that contains the
1448 archive directory. See <xref linkend="guc-recovery-init-sync-method"/>
1449 for information about the caveats to be aware of when using
1450 <literal>syncfs</literal>.
1451 </para>
1452 <para>
1453 This option has no effect when <option>--no-sync</option> is used or
1454 <option>--format</option> is not set to <literal>directory</literal>.
1455 </para>
1456 </listitem>
1457 </varlistentry>
1458
1459 <varlistentry>
1460 <term><option>--table-and-children=<replaceable class="parameter">pattern</replaceable></option></term>
1461 <listitem>
1462 <para>
1463 This is the same as
1464 the <option>-t</option>/<option>--table</option> option,
1465 except that it also includes any partitions or inheritance child
1466 tables of the table(s) matching the
1467 <replaceable class="parameter">pattern</replaceable>.
1468 </para>
1469 </listitem>
1470 </varlistentry>
1471
1472 <varlistentry>
1473 <term><option>--use-set-session-authorization</option></term>
1474 <listitem>
1475 <para>
1476 Output SQL-standard <command>SET SESSION AUTHORIZATION</command> commands
1477 instead of <command>ALTER OWNER</command> commands to determine object
1478 ownership. This makes the dump more standards-compatible, but
1479 depending on the history of the objects in the dump, might not restore
1480 properly. Also, a dump using <command>SET SESSION AUTHORIZATION</command>
1481 will certainly require superuser privileges to restore correctly,
1482 whereas <command>ALTER OWNER</command> requires lesser privileges.
1483 </para>
1484 </listitem>
1485 </varlistentry>
1486
1487 <varlistentry>
1488 <term><option>-?</option></term>
1489 <term><option>--help</option></term>
1490 <listitem>
1491 <para>
1492 Show help about <application>pg_dump</application> command line
1493 arguments, and exit.
1494 </para>
1495 </listitem>
1496 </varlistentry>
1497
1498 </variablelist>
1499 </para>
1500
1501 <para>
1502 The following command-line options control the database connection parameters.
1503
1504 <variablelist>
1505 <varlistentry>
1506 <term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
1507 <term><option>--dbname=<replaceable class="parameter">dbname</replaceable></option></term>
1508 <listitem>
1509 <para>
1510 Specifies the name of the database to connect to. This is
1511 equivalent to specifying <replaceable
1512 class="parameter">dbname</replaceable> as the first non-option
1513 argument on the command line. The <replaceable>dbname</replaceable>
1514 can be a <link linkend="libpq-connstring">connection string</link>.
1515 If so, connection string parameters will override any conflicting
1516 command line options.
1517 </para>
1518 </listitem>
1519 </varlistentry>
1520
1521 <varlistentry>
1522 <term><option>-h <replaceable class="parameter">host</replaceable></option></term>
1523 <term><option>--host=<replaceable class="parameter">host</replaceable></option></term>
1524 <listitem>
1525 <para>
1526 Specifies the host name of the machine on which the server is
1527 running. If the value begins with a slash, it is used as the
1528 directory for the Unix domain socket. The default is taken
1529 from the <envar>PGHOST</envar> environment variable, if set,
1530 else a Unix domain socket connection is attempted.
1531 </para>
1532 </listitem>
1533 </varlistentry>
1534
1535 <varlistentry>
1536 <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
1537 <term><option>--port=<replaceable class="parameter">port</replaceable></option></term>
1538 <listitem>
1539 <para>
1540 Specifies the TCP port or local Unix domain socket file
1541 extension on which the server is listening for connections.
1542 Defaults to the <envar>PGPORT</envar> environment variable, if
1543 set, or a compiled-in default.
1544 </para>
1545 </listitem>
1546 </varlistentry>
1547
1548 <varlistentry>
1549 <term><option>-U <replaceable>username</replaceable></option></term>
1550 <term><option>--username=<replaceable class="parameter">username</replaceable></option></term>
1551 <listitem>
1552 <para>
1553 User name to connect as.
1554 </para>
1555 </listitem>
1556 </varlistentry>
1557
1558 <varlistentry>
1559 <term><option>-w</option></term>
1560 <term><option>--no-password</option></term>
1561 <listitem>
1562 <para>
1563 Never issue a password prompt. If the server requires
1564 password authentication and a password is not available by
1565 other means such as a <filename>.pgpass</filename> file, the
1566 connection attempt will fail. This option can be useful in
1567 batch jobs and scripts where no user is present to enter a
1568 password.
1569 </para>
1570 </listitem>
1571 </varlistentry>
1572
1573 <varlistentry>
1574 <term><option>-W</option></term>
1575 <term><option>--password</option></term>
1576 <listitem>
1577 <para>
1578 Force <application>pg_dump</application> to prompt for a
1579 password before connecting to a database.
1580 </para>
1581
1582 <para>
1583 This option is never essential, since
1584 <application>pg_dump</application> will automatically prompt
1585 for a password if the server demands password authentication.
1586 However, <application>pg_dump</application> will waste a
1587 connection attempt finding out that the server wants a password.
1588 In some cases it is worth typing <option>-W</option> to avoid the extra
1589 connection attempt.
1590 </para>
1591 </listitem>
1592 </varlistentry>
1593
1594 <varlistentry>
1595 <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
1596 <listitem>
1597 <para>
1598 Specifies a role name to be used to create the dump.
1599 This option causes <application>pg_dump</application> to issue a
1600 <command>SET ROLE</command> <replaceable class="parameter">rolename</replaceable>
1601 command after connecting to the database. It is useful when the
1602 authenticated user (specified by <option>-U</option>) lacks privileges
1603 needed by <application>pg_dump</application>, but can switch to a role with
1604 the required rights. Some installations have a policy against
1605 logging in directly as a superuser, and use of this option allows
1606 dumps to be made without violating the policy.
1607 </para>
1608 </listitem>
1609 </varlistentry>
1610 </variablelist>
1611 </para>
1612 </refsect1>
1613
1614 <refsect1>
1615 <title>Environment</title>
1616
1617 <variablelist>
1618 <varlistentry>
1619 <term><envar>PGDATABASE</envar></term>
1620 <term><envar>PGHOST</envar></term>
1621 <term><envar>PGOPTIONS</envar></term>
1622 <term><envar>PGPORT</envar></term>
1623
1624 <term><envar>PGUSER</envar></term>
1625 <listitem>
1626 <para>
1627 Default connection parameters.
1628 </para>
1629 </listitem>
1630
1631 </varlistentry>
1632
1633 <varlistentry>
1634 <term><envar>PG_COLOR</envar></term>
1635 <listitem>
1636 <para>
1637 Specifies whether to use color in diagnostic messages. Possible values
1638 are <literal>always</literal>, <literal>auto</literal> and
1639 <literal>never</literal>.
1640 </para>
1641 </listitem>
1642 </varlistentry>
1643 </variablelist>
1644
1645 <para>
1646 This utility, like most other <productname>PostgreSQL</productname> utilities,
1647 also uses the environment variables supported by <application>libpq</application>
1648 (see <xref linkend="libpq-envars"/>).
1649 </para>
1650
1651 </refsect1>
1652
1653 <refsect1 id="app-pgdump-diagnostics">
1654 <title>Diagnostics</title>
1655
1656 <para>
1657 <application>pg_dump</application> internally executes
1658 <command>SELECT</command> statements. If you have problems running
1659 <application>pg_dump</application>, make sure you are able to
1660 select information from the database using, for example, <xref
1661 linkend="app-psql"/>. Also, any default connection settings and environment
1662 variables used by the <application>libpq</application> front-end
1663 library will apply.
1664 </para>
1665
1666 <para>
1667 The database activity of <application>pg_dump</application> is
1668 normally collected by the cumulative statistics system. If this is
1669 undesirable, you can set parameter <varname>track_counts</varname>
1670 to false via <envar>PGOPTIONS</envar> or the <literal>ALTER
1671 USER</literal> command.
1672 </para>
1673
1674 </refsect1>
1675
1676
1677 <refsect1 id="pg-dump-notes">
1678 <title>Notes</title>
1679
1680 <para>
1681 If your database cluster has any local additions to the <literal>template1</literal> database,
1682 be careful to restore the output of <application>pg_dump</application> into a
1683 truly empty database; otherwise you are likely to get errors due to
1684 duplicate definitions of the added objects. To make an empty database
1685 without any local additions, copy from <literal>template0</literal> not <literal>template1</literal>,
1686 for example:
1687 <programlisting>
1688 CREATE DATABASE foo WITH TEMPLATE template0;
1689 </programlisting>
1690 </para>
1691
1692 <para>
1693 When a dump without schema is chosen and the option <option>--disable-triggers</option>
1694 is used, <application>pg_dump</application> emits commands
1695 to disable triggers on user tables before inserting the data,
1696 and then commands to re-enable them after the data has been
1697 inserted. If the restore is stopped in the middle, the system
1698 catalogs might be left in the wrong state.
1699 </para>
1700
1701 <para>
1702 When <option>--statistics</option> is specified,
1703 <command>pg_dump</command> will include most optimizer statistics in the
1704 resulting dump file. This does not include all statistics, such as
1705 those created explicitly with <xref linkend="sql-createstatistics"/>,
1706 custom statistics added by an extension, or statistics collected by the
1707 cumulative statistics system. Therefore, it may still be useful to
1708 run <command>ANALYZE</command> after restoring from a dump file to ensure
1709 optimal performance; see <xref linkend="vacuum-for-statistics"/> and <xref
1710 linkend="autovacuum"/> for more information.
1711 </para>
1712
1713 <para>
1714 Because <application>pg_dump</application> is used to transfer data
1715 to newer versions of <productname>PostgreSQL</productname>, the output of
1716 <application>pg_dump</application> can be expected to load into
1717 <productname>PostgreSQL</productname> server versions newer than
1718 <application>pg_dump</application>'s version. <application>pg_dump</application> can also
1719 dump from <productname>PostgreSQL</productname> servers older than its own version.
1720 (Currently, servers back to version 9.2 are supported.)
1721 However, <application>pg_dump</application> cannot dump from
1722 <productname>PostgreSQL</productname> servers newer than its own major version;
1723 it will refuse to even try, rather than risk making an invalid dump.
1724 Also, it is not guaranteed that <application>pg_dump</application>'s output can
1725 be loaded into a server of an older major version &mdash; not even if the
1726 dump was taken from a server of that version. Loading a dump file
1727 into an older server may require manual editing of the dump file
1728 to remove syntax not understood by the older server.
1729 Use of the <option>--quote-all-identifiers</option> option is recommended
1730 in cross-version cases, as it can prevent problems arising from varying
1731 reserved-word lists in different <productname>PostgreSQL</productname> versions.
1732 </para>
1733
1734 <para>
1735 When dumping logical replication subscriptions,
1736 <application>pg_dump</application> will generate <command>CREATE
1737 SUBSCRIPTION</command> commands that use the <literal>connect = false</literal>
1738 option, so that restoring the subscription does not make remote connections
1739 for creating a replication slot or for initial table copy. That way, the
1740 dump can be restored without requiring network access to the remote
1741 servers. It is then up to the user to reactivate the subscriptions in a
1742 suitable way. If the involved hosts have changed, the connection
1743 information might have to be changed. It might also be appropriate to
1744 truncate the target tables before initiating a new full table copy. If users
1745 intend to copy initial data during refresh they must create the slot with
1746 <literal>two_phase = false</literal>. After the initial sync, the
1747 <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
1748 option will be automatically enabled by the subscriber if the subscription
1749 had been originally created with <literal>two_phase = true</literal> option.
1750 </para>
1751
1752 <para>
1753 It is generally recommended to use the <option>-X</option>
1754 (<option>--no-psqlrc</option>) option when restoring a database from a
1755 plain-text <application>pg_dump</application> script to ensure a clean
1756 restore process and prevent potential conflicts with
1757 non-default <application>psql</application> configurations.
1758 </para>
1759 </refsect1>
1760
1761 <refsect1 id="pg-dump-examples" xreflabel="Examples">
1762 <title>Examples</title>
1763
1764 <para>
1765 To dump a database called <literal>mydb</literal> into an SQL-script file:
1766 <screen>
1767 <prompt>$</prompt> <userinput>pg_dump mydb &gt; db.sql</userinput>
1768 </screen>
1769 </para>
1770
1771 <para>
1772 To reload such a script into a (freshly created) database named
1773 <literal>newdb</literal>:
1774
1775 <screen>
1776 <prompt>$</prompt> <userinput>psql -X -d newdb -f db.sql</userinput>
1777 </screen>
1778 </para>
1779
1780 <para>
1781 To dump a database into a custom-format archive file:
1782
1783 <screen>
1784 <prompt>$</prompt> <userinput>pg_dump -Fc mydb &gt; db.dump</userinput>
1785 </screen>
1786 </para>
1787
1788 <para>
1789 To dump a database into a directory-format archive:
1790
1791 <screen>
1792 <prompt>$</prompt> <userinput>pg_dump -Fd mydb -f dumpdir</userinput>
1793 </screen>
1794 </para>
1795
1796 <para>
1797 To dump a database into a directory-format archive in parallel with
1798 5 worker jobs:
1799
1800 <screen>
1801 <prompt>$</prompt> <userinput>pg_dump -Fd mydb -j 5 -f dumpdir</userinput>
1802 </screen>
1803 </para>
1804
1805 <para>
1806 To reload an archive file into a (freshly created) database named
1807 <literal>newdb</literal>:
1808
1809 <screen>
1810 <prompt>$</prompt> <userinput>pg_restore -d newdb db.dump</userinput>
1811 </screen>
1812 </para>
1813
1814 <para>
1815 To reload an archive file into the same database it was dumped from,
1816 discarding the current contents of that database:
1817
1818 <screen>
1819 <prompt>$</prompt> <userinput>pg_restore -d postgres --clean --create db.dump</userinput>
1820 </screen>
1821 </para>
1822
1823 <para>
1824 To dump a single table named <literal>mytab</literal>:
1825
1826 <screen>
1827 <prompt>$</prompt> <userinput>pg_dump -t mytab mydb &gt; db.sql</userinput>
1828 </screen>
1829 </para>
1830
1831 <para>
1832 To dump all tables whose names start with <literal>emp</literal> in the
1833 <literal>detroit</literal> schema, except for the table named
1834 <literal>employee_log</literal>:
1835
1836 <screen>
1837 <prompt>$</prompt> <userinput>pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb &gt; db.sql</userinput>
1838 </screen>
1839 </para>
1840
1841 <para>
1842 To dump all schemas whose names start with <literal>east</literal> or
1843 <literal>west</literal> and end in <literal>gsm</literal>, excluding any schemas whose
1844 names contain the word <literal>test</literal>:
1845
1846 <screen>
1847 <prompt>$</prompt> <userinput>pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb &gt; db.sql</userinput>
1848 </screen>
1849 </para>
1850
1851 <para>
1852 The same, using regular expression notation to consolidate the switches:
1853
1854 <screen>
1855 <prompt>$</prompt> <userinput>pg_dump -n '(east|west)*gsm' -N '*test*' mydb &gt; db.sql</userinput>
1856 </screen>
1857 </para>
1858
1859 <para>
1860 To dump all database objects except for tables whose names begin with
1861 <literal>ts_</literal>:
1862
1863 <screen>
1864 <prompt>$</prompt> <userinput>pg_dump -T 'ts_*' mydb &gt; db.sql</userinput>
1865 </screen>
1866 </para>
1867
1868 <para>
1869 To specify an upper-case or mixed-case name in <option>-t</option> and related
1870 switches, you need to double-quote the name; else it will be folded to
1871 lower case (see <xref linkend="app-psql-patterns"/>). But
1872 double quotes are special to the shell, so in turn they must be quoted.
1873 Thus, to dump a single table with a mixed-case name, you need something
1874 like
1875
1876 <screen>
1877 <prompt>$</prompt> <userinput>pg_dump -t "\"MixedCaseName\"" mydb &gt; mytab.sql</userinput>
1878 </screen></para>
1879
1880 <para>
1881 To dump all tables whose names start with <literal>mytable</literal>, except
1882 for table <literal>mytable2</literal>, specify a filter file
1883 <filename>filter.txt</filename> like:
1884 <programlisting>
1885 include table mytable*
1886 exclude table mytable2
1887 </programlisting>
1888
1889 <screen>
1890 <prompt>$</prompt> <userinput>pg_dump --filter=filter.txt mydb &gt; db.sql</userinput>
1891 </screen></para>
1892
1893 </refsect1>
1894
1895 <refsect1>
1896 <title>See Also</title>
1897
1898 <simplelist type="inline">
1899 <member><xref linkend="app-pg-dumpall"/></member>
1900 <member><xref linkend="app-pgrestore"/></member>
1901 <member><xref linkend="app-psql"/></member>
1902 </simplelist>
1903 </refsect1>
1904
1905 </refentry>