2 doc/src/sgml/ref/pg_dump.sgml
3 PostgreSQL documentation
6 <refentry id="APP-PGDUMP">
8 <refentrytitle>pg_dump</refentrytitle>
9 <manvolnum>1</manvolnum>
10 <refmiscinfo>Application</refmiscinfo>
14 <refname>pg_dump</refname>
17 extract a <productname>PostgreSQL</productname> database into a script file or other archive file
21 <indexterm zone="app-pgdump">
22 <primary>pg_dump</primary>
27 <command>pg_dump</command>
28 <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
29 <arg rep="repeat"><replaceable>option</replaceable></arg>
30 <arg><replaceable>dbname</replaceable></arg>
35 <refsect1 id="pg-dump-description">
41 <application>pg_dump</application> is a utility for backing up a
42 <productname>PostgreSQL</productname> database. It makes consistent
43 backups even if the database is being used concurrently.
44 <application>pg_dump</application> does not block other users
45 accessing the database (readers or writers).
49 Dumps can be output in script or archive file formats. Script
50 dumps are plain-text files containing the SQL commands required
51 to reconstruct the database to the state it was in at the time it was
52 saved. To restore from such a script, feed it to <xref
53 linkend="app-psql">. Script files
54 can be used to reconstruct the database even on other machines and
55 other architectures; with some modifications, even on other SQL
60 The alternative archive file formats must be used with
61 <xref linkend="app-pgrestore"> to rebuild the database. They
62 allow <application>pg_restore</application> to be selective about
63 what is restored, or even to reorder the items prior to being
65 The archive file formats are designed to be portable across
70 When used with one of the archive file formats and combined with
71 <application>pg_restore</application>,
72 <application>pg_dump</application> provides a flexible archival and
73 transfer mechanism. <application>pg_dump</application> can be used to
74 backup an entire database, then <application>pg_restore</application>
75 can be used to examine the archive and/or select which parts of the
76 database are to be restored. The most flexible output file format is
77 the <quote>custom</quote> format (<option>-Fc</option>). It allows
78 for selection and reordering of all archived items, and is compressed
83 While running <application>pg_dump</application>, one should examine the
84 output for any warnings (printed on standard error), especially in
85 light of the limitations listed below.
90 <refsect1 id="pg-dump-options">
91 <title>Options</title>
94 The following command-line options control the content and
99 <term><replaceable class="parameter">dbname</replaceable></term>
102 Specifies the name of the database to be dumped. If this is
103 not specified, the environment variable
104 <envar>PGDATABASE</envar> is used. If that is not set, the
105 user name specified for the connection is used.
111 <term><option>-a</></term>
112 <term><option>--data-only</></term>
115 Dump only the data, not the schema (data definitions).
119 This option is only meaningful for the plain-text format. For
120 the archive formats, you can specify the option when you
121 call <command>pg_restore</command>.
127 <term><option>-b</></term>
128 <term><option>--blobs</></term>
131 Include large objects in the dump. This is the default behavior
132 except when <option>--schema</>, <option>--table</>, or
133 <option>--schema-only</> is specified, so the <option>-b</>
134 switch is only useful to add large objects to selective dumps.
140 <term><option>-c</option></term>
141 <term><option>--clean</option></term>
144 Output commands to clean (drop)
145 database objects prior to outputting the commands for creating them.
146 (Restore might generate some harmless errors.)
150 This option is only meaningful for the plain-text format. For
151 the archive formats, you can specify the option when you
152 call <command>pg_restore</command>.
158 <term><option>-C</></term>
159 <term><option>--create</></term>
162 Begin the output with a command to create the
163 database itself and reconnect to the created database. (With a
164 script of this form, it doesn't matter which database you connect
165 to before running the script.)
169 This option is only meaningful for the plain-text format. For
170 the archive formats, you can specify the option when you
171 call <command>pg_restore</command>.
177 <term><option>-E <replaceable class="parameter">encoding</replaceable></option></term>
178 <term><option>--encoding=<replaceable class="parameter">encoding</replaceable></option></term>
181 Create the dump in the specified character set encoding. By default,
182 the dump is created in the database encoding. (Another way to get the
183 same result is to set the <envar>PGCLIENTENCODING</envar> environment
184 variable to the desired dump encoding.)
190 <term><option>-f <replaceable class="parameter">file</replaceable></option></term>
191 <term><option>--file=<replaceable class="parameter">file</replaceable></option></term>
194 Send output to the specified file. This parameter can be omitted for
195 file based output formats, in which case the standard output is used.
196 It must be given for the directory output format however, where it
197 specifies the target directory instead of a file. In this case the
198 directory is created by <command>pg_dump</command> and must not exist
205 <term><option>-F <replaceable class="parameter">format</replaceable></option></term>
206 <term><option>--format=<replaceable class="parameter">format</replaceable></option></term>
209 Selects the format of the output.
210 <replaceable>format</replaceable> can be one of the following:
214 <term><literal>p</></term>
215 <term><literal>plain</></term>
218 Output a plain-text <acronym>SQL</acronym> script file (the default).
224 <term><literal>c</></term>
225 <term><literal>custom</></term>
228 Output a custom-format archive suitable for input into
229 <application>pg_restore</application>.
230 Together with the directory output format, this is the most flexible
231 output format in that it allows manual selection and reordering of
232 archived items during restore. This format is also compressed by
239 <term><literal>d</></term>
240 <term><literal>directory</></term>
243 Output a directory-format archive suitable for input into
244 <application>pg_restore</application>. This will create a directory
245 with one file for each table and blob being dumped, plus a
246 so-called Table of Contents file describing the dumped objects in a
247 machine-readable format that <application>pg_restore</application>
248 can read. A directory format archive can be manipulated with
249 standard Unix tools; for example, files in an uncompressed archive
250 can be compressed with the <application>gzip</application> tool.
251 This format is compressed by default.
257 <term><literal>t</></term>
258 <term><literal>tar</></term>
261 Output a <command>tar</command>-format archive suitable for input
262 into <application>pg_restore</application>. The tar-format is
263 compatible with the directory-format; extracting a tar-format
264 archive produces a valid directory-format archive.
265 However, the tar-format does not support compression and has a
266 limit of 8 GB on the size of individual tables. Also, the relative
267 order of table data items cannot be changed during restore.
271 </variablelist></para>
276 <term><option>-i</></term>
277 <term><option>--ignore-version</></term>
280 A deprecated option that is now ignored.
286 <term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
287 <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
290 Dump only schemas matching <replaceable
291 class="parameter">schema</replaceable>; this selects both the
292 schema itself, and all its contained objects. When this option is
293 not specified, all non-system schemas in the target database will be
294 dumped. Multiple schemas can be
295 selected by writing multiple <option>-n</> switches. Also, the
296 <replaceable class="parameter">schema</replaceable> parameter is
297 interpreted as a pattern according to the same rules used by
298 <application>psql</>'s <literal>\d</> commands (see <xref
299 linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">),
300 so multiple schemas can also be selected by writing wildcard characters
301 in the pattern. When using wildcards, be careful to quote the pattern
302 if needed to prevent the shell from expanding the wildcards; see
303 <xref linkend="pg-dump-examples" endterm="pg-dump-examples-title">.
308 When <option>-n</> is specified, <application>pg_dump</application>
309 makes no attempt to dump any other database objects that the selected
310 schema(s) might depend upon. Therefore, there is no guarantee
311 that the results of a specific-schema dump can be successfully
312 restored by themselves into a clean database.
318 Non-schema objects such as blobs are not dumped when <option>-n</> is
319 specified. You can add blobs back to the dump with the
320 <option>--blobs</> switch.
328 <term><option>-N <replaceable class="parameter">schema</replaceable></option></term>
329 <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term>
332 Do not dump any schemas matching the <replaceable
333 class="parameter">schema</replaceable> pattern. The pattern is
334 interpreted according to the same rules as for <option>-n</>.
335 <option>-N</> can be given more than once to exclude schemas
336 matching any of several patterns.
340 When both <option>-n</> and <option>-N</> are given, the behavior
341 is to dump just the schemas that match at least one <option>-n</>
342 switch but no <option>-N</> switches. If <option>-N</> appears
343 without <option>-n</>, then schemas matching <option>-N</> are
344 excluded from what is otherwise a normal dump.
350 <term><option>-o</></term>
351 <term><option>--oids</></term>
354 Dump object identifiers (<acronym>OID</acronym>s) as part of the
355 data for every table. Use this option if your application references
357 columns in some way (e.g., in a foreign key constraint).
358 Otherwise, this option should not be used.
364 <term><option>-O</></term>
365 <term><option>--no-owner</option></term>
368 Do not output commands to set
369 ownership of objects to match the original database.
370 By default, <application>pg_dump</application> issues
371 <command>ALTER OWNER</> or
372 <command>SET SESSION AUTHORIZATION</command>
373 statements to set ownership of created database objects.
375 will fail when the script is run unless it is started by a superuser
376 (or the same user that owns all of the objects in the script).
377 To make a script that can be restored by any user, but will give
378 that user ownership of all the objects, specify <option>-O</>.
382 This option is only meaningful for the plain-text format. For
383 the archive formats, you can specify the option when you
384 call <command>pg_restore</command>.
390 <term><option>-R</option></term>
391 <term><option>--no-reconnect</option></term>
394 This option is obsolete but still accepted for backwards
401 <term><option>-s</option></term>
402 <term><option>--schema-only</option></term>
405 Dump only the object definitions (schema), not data.
411 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
412 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
415 Specify the superuser user name to use when disabling triggers.
416 This is only relevant if <option>--disable-triggers</> is used.
417 (Usually, it's better to leave this out, and instead start the
418 resulting script as superuser.)
424 <term><option>-t <replaceable class="parameter">table</replaceable></option></term>
425 <term><option>--table=<replaceable class="parameter">table</replaceable></option></term>
428 Dump only tables (or views or sequences or foreign tables) matching
429 <replaceable class="parameter">table</replaceable>. Multiple tables
430 can be selected by writing multiple <option>-t</> switches. Also, the
431 <replaceable class="parameter">table</replaceable> parameter is
432 interpreted as a pattern according to the same rules used by
433 <application>psql</>'s <literal>\d</> commands (see <xref
434 linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">),
435 so multiple tables can also be selected by writing wildcard characters
436 in the pattern. When using wildcards, be careful to quote the pattern
437 if needed to prevent the shell from expanding the wildcards; see
438 <xref linkend="pg-dump-examples" endterm="pg-dump-examples-title">.
442 The <option>-n</> and <option>-N</> switches have no effect when
443 <option>-t</> is used, because tables selected by <option>-t</> will
444 be dumped regardless of those switches, and non-table objects will not
450 When <option>-t</> is specified, <application>pg_dump</application>
451 makes no attempt to dump any other database objects that the selected
452 table(s) might depend upon. Therefore, there is no guarantee
453 that the results of a specific-table dump can be successfully
454 restored by themselves into a clean database.
460 The behavior of the <option>-t</> switch is not entirely upward
461 compatible with pre-8.2 <productname>PostgreSQL</productname>
462 versions. Formerly, writing <literal>-t tab</> would dump all
463 tables named <literal>tab</>, but now it just dumps whichever one
464 is visible in your default search path. To get the old behavior
465 you can write <literal>-t '*.tab'</>. Also, you must write something
466 like <literal>-t sch.tab</> to select a table in a particular schema,
467 rather than the old locution of <literal>-n sch -t tab</>.
474 <term><option>-T <replaceable class="parameter">table</replaceable></option></term>
475 <term><option>--exclude-table=<replaceable class="parameter">table</replaceable></option></term>
478 Do not dump any tables matching the <replaceable
479 class="parameter">table</replaceable> pattern. The pattern is
480 interpreted according to the same rules as for <option>-t</>.
481 <option>-T</> can be given more than once to exclude tables
482 matching any of several patterns.
486 When both <option>-t</> and <option>-T</> are given, the behavior
487 is to dump just the tables that match at least one <option>-t</>
488 switch but no <option>-T</> switches. If <option>-T</> appears
489 without <option>-t</>, then tables matching <option>-T</> are
490 excluded from what is otherwise a normal dump.
496 <term><option>-v</></term>
497 <term><option>--verbose</></term>
500 Specifies verbose mode. This will cause
501 <application>pg_dump</application> to output detailed object
502 comments and start/stop times to the dump file, and progress
503 messages to standard error.
509 <term><option>-V</></term>
510 <term><option>--version</></term>
513 Print the <application>pg_dump</application> version and exit.
519 <term><option>-x</></term>
520 <term><option>--no-privileges</></term>
521 <term><option>--no-acl</></term>
524 Prevent dumping of access privileges (grant/revoke commands).
530 <term><option>-Z <replaceable class="parameter">0..9</replaceable></option></term>
531 <term><option>--compress=<replaceable class="parameter">0..9</replaceable></option></term>
534 Specify the compression level to use. Zero means no compression.
535 For the custom archive format, this specifies compression of
536 individual table-data segments, and the default is to compress
538 For plain text output, setting a nonzero compression level causes
539 the entire output file to be compressed, as though it had been
540 fed through <application>gzip</>; but the default is not to compress.
541 The tar archive format currently does not support compression at all.
547 <term><option>--binary-upgrade</option></term>
550 This option is for use by in-place upgrade utilities. Its use
551 for other purposes is not recommended or supported. The
552 behavior of the option may change in future releases without
559 <term><option>--column-inserts</option></term>
560 <term><option>--attribute-inserts</option></term>
563 Dump data as <command>INSERT</command> commands with explicit
564 column names (<literal>INSERT INTO
565 <replaceable>table</replaceable>
566 (<replaceable>column</replaceable>, ...) VALUES
567 ...</literal>). This will make restoration very slow; it is mainly
568 useful for making dumps that can be loaded into
569 non-<productname>PostgreSQL</productname> databases.
570 However, since this option generates a separate command for each row,
571 an error in reloading a row causes only that row to be lost rather
572 than the entire table contents.
578 <term><option>--disable-dollar-quoting</></term>
581 This option disables the use of dollar quoting for function bodies,
582 and forces them to be quoted using SQL standard string syntax.
588 <term><option>--disable-triggers</></term>
591 This option is only relevant when creating a data-only dump.
592 It instructs <application>pg_dump</application> to include commands
593 to temporarily disable triggers on the target tables while
594 the data is reloaded. Use this if you have referential
595 integrity checks or other triggers on the tables that you
596 do not want to invoke during data reload.
600 Presently, the commands emitted for <option>--disable-triggers</>
601 must be done as superuser. So, you should also specify
602 a superuser name with <option>-S</>, or preferably be careful to
603 start the resulting script as a superuser.
607 This option is only meaningful for the plain-text format. For
608 the archive formats, you can specify the option when you
609 call <command>pg_restore</command>.
615 <term><option>--inserts</option></term>
618 Dump data as <command>INSERT</command> commands (rather
619 than <command>COPY</command>). This will make restoration very slow;
620 it is mainly useful for making dumps that can be loaded into
621 non-<productname>PostgreSQL</productname> databases.
622 However, since this option generates a separate command for each row,
623 an error in reloading a row causes only that row to be lost rather
624 than the entire table contents.
626 the restore might fail altogether if you have rearranged column order.
627 The <option>--column-inserts</option> option is safe against column
628 order changes, though even slower.
634 <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
637 Do not wait forever to acquire shared table locks at the beginning of
638 the dump. Instead fail if unable to lock a table within the specified
639 <replaceable class="parameter">timeout</>. The timeout may be
640 specified in any of the formats accepted by <command>SET
641 statement_timeout</>. (Allowed values vary depending on the server
642 version you are dumping from, but an integer number of milliseconds
643 is accepted by all versions since 7.3. This option is ignored when
644 dumping from a pre-7.3 server.)
650 <term><option>--no-security-labels</option></term>
653 Do not dump security labels.
659 <term><option>--no-tablespaces</option></term>
662 Do not output commands to select tablespaces.
663 With this option, all objects will be created in whichever
664 tablespace is the default during restore.
668 This option is only meaningful for the plain-text format. For
669 the archive formats, you can specify the option when you
670 call <command>pg_restore</command>.
676 <term><option>--no-unlogged-table-data</option></term>
679 Do not dump the contents of unlogged tables. This option has no
680 effect on whether or not the table definitions (schema) are dumped;
681 it only suppresses dumping the table data.
687 <term><option>--quote-all-identifiers</></term>
690 Force quoting of all identifiers. This may be useful when dumping a
691 database for migration to a future version that may have introduced
698 <term><option>--serializable-deferrable</option></term>
701 Use a <literal>serializable</literal> transaction for the dump, to
702 ensure that the snapshot used is consistent with later database
703 states; but do this by waiting for a point in the transaction stream
704 at which no anomalies can be present, so that there isn't a risk of
705 the dump failing or causing other transactions to roll back with a
706 <literal>serialization_failure</literal>. See <xref linkend="mvcc">
707 for more information about transaction isolation and concurrency
712 This option is not beneficial for a dump which is intended only for
713 disaster recovery. It could be useful for a dump used to load a
714 copy of the database for reporting or other read-only load sharing
715 while the original database continues to be updated. Without it the
716 dump may reflect a state which is not consistent with any serial
717 execution of the transactions eventually committed. For example, if
718 batch processing techniques are used, a batch may show as closed in
719 the dump without all of the items which are in the batch appearing.
723 This option will make no difference if there are no read-write
724 transactions active when pg_dump is started. If read-write
725 transactions are active, the start of the dump may be delayed for an
726 indeterminate length of time. Once running, performance with or
727 without the switch is the same.
733 <term><option>--use-set-session-authorization</></term>
736 Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
737 instead of <command>ALTER OWNER</> commands to determine object
738 ownership. This makes the dump more standards-compatible, but
739 depending on the history of the objects in the dump, might not restore
740 properly. Also, a dump using <command>SET SESSION AUTHORIZATION</>
741 will certainly require superuser privileges to restore correctly,
742 whereas <command>ALTER OWNER</> requires lesser privileges.
748 <term><option>-?</></term>
749 <term><option>--help</></term>
752 Show help about <application>pg_dump</application> command line
762 The following command-line options control the database connection parameters.
766 <term><option>-h <replaceable class="parameter">host</replaceable></option></term>
767 <term><option>--host=<replaceable class="parameter">host</replaceable></option></term>
770 Specifies the host name of the machine on which the server is
771 running. If the value begins with a slash, it is used as the
772 directory for the Unix domain socket. The default is taken
773 from the <envar>PGHOST</envar> environment variable, if set,
774 else a Unix domain socket connection is attempted.
780 <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
781 <term><option>--port=<replaceable class="parameter">port</replaceable></option></term>
784 Specifies the TCP port or local Unix domain socket file
785 extension on which the server is listening for connections.
786 Defaults to the <envar>PGPORT</envar> environment variable, if
787 set, or a compiled-in default.
793 <term><option>-U <replaceable>username</replaceable></option></term>
794 <term><option>--username=<replaceable class="parameter">username</replaceable></option></term>
797 User name to connect as.
803 <term><option>-w</></term>
804 <term><option>--no-password</></term>
807 Never issue a password prompt. If the server requires
808 password authentication and a password is not available by
809 other means such as a <filename>.pgpass</filename> file, the
810 connection attempt will fail. This option can be useful in
811 batch jobs and scripts where no user is present to enter a
818 <term><option>-W</option></term>
819 <term><option>--password</option></term>
822 Force <application>pg_dump</application> to prompt for a
823 password before connecting to a database.
827 This option is never essential, since
828 <application>pg_dump</application> will automatically prompt
829 for a password if the server demands password authentication.
830 However, <application>pg_dump</application> will waste a
831 connection attempt finding out that the server wants a password.
832 In some cases it is worth typing <option>-W</> to avoid the extra
839 <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
842 Specifies a role name to be used to create the dump.
843 This option causes <application>pg_dump</> to issue a
844 <command>SET ROLE</> <replaceable class="parameter">rolename</>
845 command after connecting to the database. It is useful when the
846 authenticated user (specified by <option>-U</>) lacks privileges
847 needed by <application>pg_dump</>, but can switch to a role with
848 the required rights. Some installations have a policy against
849 logging in directly as a superuser, and use of this option allows
850 dumps to be made without violating the policy.
859 <title>Environment</title>
863 <term><envar>PGDATABASE</envar></term>
864 <term><envar>PGHOST</envar></term>
865 <term><envar>PGOPTIONS</envar></term>
866 <term><envar>PGPORT</envar></term>
868 <term><envar>PGUSER</envar></term>
871 Default connection parameters.
879 This utility, like most other <productname>PostgreSQL</> utilities,
880 also uses the environment variables supported by <application>libpq</>
881 (see <xref linkend="libpq-envars">).
886 <refsect1 id="app-pgdump-diagnostics">
887 <title>Diagnostics</title>
890 <application>pg_dump</application> internally executes
891 <command>SELECT</command> statements. If you have problems running
892 <application>pg_dump</application>, make sure you are able to
893 select information from the database using, for example, <xref
894 linkend="app-psql">. Also, any default connection settings and environment
895 variables used by the <application>libpq</application> front-end
900 The database activity of <application>pg_dump</application> is
901 normally collected by the statistics collector. If this is
902 undesirable, you can set parameter <varname>track_counts</>
903 to false via <envar>PGOPTIONS</envar> or the <literal>ALTER
904 USER</literal> command.
910 <refsect1 id="pg-dump-notes">
914 If your database cluster has any local additions to the <literal>template1</> database,
915 be careful to restore the output of <application>pg_dump</application> into a
916 truly empty database; otherwise you are likely to get errors due to
917 duplicate definitions of the added objects. To make an empty database
918 without any local additions, copy from <literal>template0</> not <literal>template1</>,
921 CREATE DATABASE foo WITH TEMPLATE template0;
926 When a data-only dump is chosen and the option <option>--disable-triggers</>
927 is used, <application>pg_dump</application> emits commands
928 to disable triggers on user tables before inserting the data,
929 and then commands to re-enable them after the data has been
930 inserted. If the restore is stopped in the middle, the system
931 catalogs might be left in the wrong state.
935 Members of tar archives are limited to a size less than 8 GB.
936 (This is an inherent limitation of the tar file format.) Therefore
937 this format cannot be used if the textual representation of any one table
938 exceeds that size. The total size of a tar archive and any of the
939 other output formats is not limited, except possibly by the
944 The dump file produced by <application>pg_dump</application>
945 does not contain the statistics used by the optimizer to make
946 query planning decisions. Therefore, it is wise to run
947 <command>ANALYZE</command> after restoring from a dump file
948 to ensure optimal performance; see <xref linkend="vacuum-for-statistics">
949 and <xref linkend="autovacuum"> for more information.
950 The dump file also does not
951 contain any <command>ALTER DATABASE ... SET</> commands;
952 these settings are dumped by <xref linkend="app-pg-dumpall">,
953 along with database users and other installation-wide settings.
957 Because <application>pg_dump</application> is used to transfer data
958 to newer versions of <productname>PostgreSQL</>, the output of
959 <application>pg_dump</application> can be expected to load into
960 <productname>PostgreSQL</> server versions newer than
961 <application>pg_dump</>'s version. <application>pg_dump</> can also
962 dump from <productname>PostgreSQL</> servers older than its own version.
963 (Currently, servers back to version 7.0 are supported.)
964 However, <application>pg_dump</> cannot dump from
965 <productname>PostgreSQL</> servers newer than its own major version;
966 it will refuse to even try, rather than risk making an invalid dump.
967 Also, it is not guaranteed that <application>pg_dump</>'s output can
968 be loaded into a server of an older major version — not even if the
969 dump was taken from a server of that version. Loading a dump file
970 into an older server may require manual editing of the dump file
971 to remove syntax not understood by the older server.
975 <refsect1 id="pg-dump-examples">
976 <title id="pg-dump-examples-title">Examples</title>
979 To dump a database called <literal>mydb</> into a SQL-script file:
981 <prompt>$</prompt> <userinput>pg_dump mydb > db.sql</userinput>
986 To reload such a script into a (freshly created) database named
990 <prompt>$</prompt> <userinput>psql -d newdb -f db.sql</userinput>
995 To dump a database into a custom-format archive file:
998 <prompt>$</prompt> <userinput>pg_dump -Fc mydb > db.dump</userinput>
1003 To dump a database into a directory-format archive:
1006 <prompt>$</prompt> <userinput>pg_dump -Fd mydb -f dumpdir</userinput>
1011 To reload an archive file into a (freshly created) database named
1015 <prompt>$</prompt> <userinput>pg_restore -d newdb db.dump</userinput>
1020 To dump a single table named <literal>mytab</>:
1023 <prompt>$</prompt> <userinput>pg_dump -t mytab mydb > db.sql</userinput>
1028 To dump all tables whose names start with <literal>emp</> in the
1029 <literal>detroit</> schema, except for the table named
1030 <literal>employee_log</literal>:
1033 <prompt>$</prompt> <userinput>pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql</userinput>
1038 To dump all schemas whose names start with <literal>east</> or
1039 <literal>west</> and end in <literal>gsm</>, excluding any schemas whose
1040 names contain the word <literal>test</>:
1043 <prompt>$</prompt> <userinput>pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql</userinput>
1048 The same, using regular expression notation to consolidate the switches:
1051 <prompt>$</prompt> <userinput>pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql</userinput>
1056 To dump all database objects except for tables whose names begin with
1057 <literal>ts_</literal>:
1060 <prompt>$</prompt> <userinput>pg_dump -T 'ts_*' mydb > db.sql</userinput>
1065 To specify an upper-case or mixed-case name in <option>-t</> and related
1066 switches, you need to double-quote the name; else it will be folded to
1067 lower case (see <xref
1068 linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">). But
1069 double quotes are special to the shell, so in turn they must be quoted.
1070 Thus, to dump a single table with a mixed-case name, you need something
1074 <prompt>$</prompt> <userinput>pg_dump -t '"MixedCaseName"' mydb > mytab.sql</userinput>
1080 <title>See Also</title>
1082 <simplelist type="inline">
1083 <member><xref linkend="app-pg-dumpall"></member>
1084 <member><xref linkend="app-pgrestore"></member>
1085 <member><xref linkend="app-psql"></member>