| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/pg_dump.sgml,v 1.92.2.2 2007/03/26 17:23:44 tgl Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="APP-PGDUMP"> |
| <refmeta> |
| <refentrytitle>pg_dump</refentrytitle> |
| <manvolnum>1</manvolnum> |
| <refmiscinfo>Application</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>pg_dump</refname> |
| |
| <refpurpose> |
| extract a <productname>PostgreSQL</productname> database into a script file or other archive file |
| </refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="app-pgdump"> |
| <primary>pg_dump</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <cmdsynopsis> |
| <command>pg_dump</command> |
| <arg rep="repeat"><replaceable>option</replaceable></arg> |
| <arg><replaceable>dbname</replaceable></arg> |
| </cmdsynopsis> |
| </refsynopsisdiv> |
| |
| |
| <refsect1 id="pg-dump-description"> |
| <title> |
| Description |
| </title> |
| |
| <para> |
| <application>pg_dump</application> is a utility for backing up a |
| <productname>PostgreSQL</productname> database. It makes consistent |
| backups even if the database is being used concurrently. |
| <application>pg_dump</application> does not block other users |
| accessing the database (readers or writers). |
| </para> |
| |
| <para> |
| Dumps can be output in script or archive file formats. Script |
| dumps are plain-text files containing the SQL commands required |
| to reconstruct the database to the state it was in at the time it was |
| saved. To restore from such a script, feed it to <xref |
| linkend="app-psql">. Script files |
| can be used to reconstruct the database even on other machines and |
| other architectures; with some modifications even on other SQL |
| database products. |
| </para> |
| |
| <para> |
| The alternative archive file formats must be used with |
| <xref linkend="app-pgrestore"> to rebuild the database. They |
| allow <application>pg_restore</application> to be selective about |
| what is restored, or even to reorder the items prior to being |
| restored. |
| The archive file formats are designed to be portable across |
| architectures. |
| </para> |
| |
| <para> |
| When used with one of the archive file formats and combined with |
| <application>pg_restore</application>, |
| <application>pg_dump</application> provides a flexible archival and |
| transfer mechanism. <application>pg_dump</application> can be used to |
| backup an entire database, then <application>pg_restore</application> |
| can be used to examine the archive and/or select which parts of the |
| database are to be restored. The most flexible output file format is |
| the <quote>custom</quote> format (<option>-Fc</option>). It allows |
| for selection and reordering of all archived items, and is compressed |
| by default. The <application>tar</application> format |
| (<option>-Ft</option>) is not compressed and it is not possible to |
| reorder data when loading, but it is otherwise quite flexible; |
| moreover, it can be manipulated with standard Unix tools such as |
| <command>tar</command>. |
| </para> |
| |
| <para> |
| While running <application>pg_dump</application>, one should examine the |
| output for any warnings (printed on standard error), especially in |
| light of the limitations listed below. |
| </para> |
| |
| </refsect1> |
| |
| <refsect1 id="pg-dump-options"> |
| <title>Options</title> |
| |
| <para> |
| The following command-line options control the content and |
| format of the output. |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="parameter">dbname</replaceable></term> |
| <listitem> |
| <para> |
| Specifies the name of the database to be dumped. If this is |
| not specified, the environment variable |
| <envar>PGDATABASE</envar> is used. If that is not set, the |
| user name specified for the connection is used. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-a</></term> |
| <term><option>--data-only</></term> |
| <listitem> |
| <para> |
| Dump only the data, not the schema (data definitions). |
| </para> |
| |
| <para> |
| This option is only meaningful for the plain-text format. For |
| the archive formats, you may specify the option when you |
| call <command>pg_restore</command>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-b</></term> |
| <term><option>--blobs</></term> |
| <listitem> |
| <para> |
| Include large objects in the dump. This is the default behavior |
| except when <option>--schema</>, <option>--table</>, or |
| <option>--schema-only</> is specified, so the <option>-b</> |
| switch is only useful to add large objects to selective dumps. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-c</option></term> |
| <term><option>--clean</option></term> |
| <listitem> |
| <para> |
| Output commands to clean (drop) |
| database objects prior to (the commands for) creating them. |
| </para> |
| |
| <para> |
| This option is only meaningful for the plain-text format. For |
| the archive formats, you may specify the option when you |
| call <command>pg_restore</command>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-C</></term> |
| <term><option>--create</></term> |
| <listitem> |
| <para> |
| Begin the output with a command to create the |
| database itself and reconnect to the created database. (With a |
| script of this form, it doesn't matter which database you connect |
| to before running the script.) |
| </para> |
| |
| <para> |
| This option is only meaningful for the plain-text format. For |
| the archive formats, you may specify the option when you |
| call <command>pg_restore</command>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-d</option></term> |
| <term><option>--inserts</option></term> |
| <listitem> |
| <para> |
| Dump data as <command>INSERT</command> commands (rather |
| than <command>COPY</command>). This will make restoration very slow; |
| it is mainly useful for making dumps that can be loaded into |
| non-<productname>PostgreSQL</productname> databases. |
| Also, since this option generates a separate command for each row, |
| an error in reloading a row causes only that row to be lost rather |
| than the entire table contents. |
| Note that |
| the restore may fail altogether if you have rearranged column order. |
| The <option>-D</option> option is safe against column order changes, |
| though even slower. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-D</option></term> |
| <term><option>--column-inserts</option></term> |
| <term><option>--attribute-inserts</option></term> |
| <listitem> |
| <para> |
| Dump data as <command>INSERT</command> commands with explicit |
| column names (<literal>INSERT INTO |
| <replaceable>table</replaceable> |
| (<replaceable>column</replaceable>, ...) VALUES |
| ...</literal>). This will make restoration very slow; it is mainly |
| useful for making dumps that can be loaded into |
| non-<productname>PostgreSQL</productname> databases. |
| Also, since this option generates a separate command for each row, |
| an error in reloading a row causes only that row to be lost rather |
| than the entire table contents. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-E <replaceable class="parameter">encoding</replaceable></option></term> |
| <term><option>--encoding=<replaceable class="parameter">encoding</replaceable></option></term> |
| <listitem> |
| <para> |
| Create the dump in the specified character set encoding. By default, |
| the dump is created in the database encoding. (Another way to get the |
| same result is to set the <envar>PGCLIENTENCODING</envar> environment |
| variable to the desired dump encoding.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-f <replaceable class="parameter">file</replaceable></option></term> |
| <term><option>--file=<replaceable class="parameter">file</replaceable></option></term> |
| <listitem> |
| <para> |
| Send output to the specified file. If this is omitted, the |
| standard output is used. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-F <replaceable class="parameter">format</replaceable></option></term> |
| <term><option>--format=<replaceable class="parameter">format</replaceable></option></term> |
| <listitem> |
| <para> |
| Selects the format of the output. |
| <replaceable>format</replaceable> can be one of the following: |
| |
| <variablelist> |
| <varlistentry> |
| <term><literal>p</></term> |
| <term><literal>plain</></term> |
| <listitem> |
| <para> |
| Output a plain-text <acronym>SQL</acronym> script file (the default). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>c</></term> |
| <term><literal>custom</></term> |
| <listitem> |
| <para> |
| Output a custom archive suitable for input into |
| <application>pg_restore</application>. This is the most flexible |
| format in that it allows reordering of loading data as well |
| as object definitions. This format is also compressed by default. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>t</></term> |
| <term><literal>tar</></term> |
| <listitem> |
| <para> |
| Output a <command>tar</command> archive suitable for input into |
| <application>pg_restore</application>. Using this archive format |
| allows reordering and/or exclusion of database objects |
| at the time the database is restored. It is also possible to limit |
| which data is reloaded at restore time. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| </para> |
| |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-i</></term> |
| <term><option>--ignore-version</></term> |
| <listitem> |
| <para> |
| Ignore version mismatch between |
| <application>pg_dump</application> and the database server. |
| </para> |
| |
| <para> |
| <application>pg_dump</application> can dump from servers running |
| previous releases of <productname>PostgreSQL</>, but very old |
| versions are not supported anymore (currently, those prior to 7.0). |
| Dumping from a server newer than <application>pg_dump</application> |
| is likely not to work at all. |
| Use this option if you need to override the version check (and |
| if <application>pg_dump</application> then fails, don't say |
| you weren't warned). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-n <replaceable class="parameter">schema</replaceable></option></term> |
| <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term> |
| <listitem> |
| <para> |
| Dump only schemas matching <replaceable |
| class="parameter">schema</replaceable>; this selects both the |
| schema itself, and all its contained objects. When this option is |
| not specified, all non-system schemas in the target database will be |
| dumped. Multiple schemas can be |
| selected by writing multiple <option>-n</> switches. Also, the |
| <replaceable class="parameter">schema</replaceable> parameter is |
| interpreted as a pattern according to the same rules used by |
| <application>psql</>'s <literal>\d</> commands (see <xref |
| linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">), |
| so multiple schemas can also be selected by writing wildcard characters |
| in the pattern. When using wildcards, be careful to quote the pattern |
| if needed to prevent the shell from expanding the wildcards. |
| </para> |
| |
| <note> |
| <para> |
| When <option>-n</> is specified, <application>pg_dump</application> |
| makes no attempt to dump any other database objects that the selected |
| schema(s) may depend upon. Therefore, there is no guarantee |
| that the results of a specific-schema dump can be successfully |
| restored by themselves into a clean database. |
| </para> |
| </note> |
| |
| <note> |
| <para> |
| Non-schema objects such as blobs are not dumped when <option>-n</> is |
| specified. You can add blobs back to the dump with the |
| <option>--blobs</> switch. |
| </para> |
| </note> |
| |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-N <replaceable class="parameter">schema</replaceable></option></term> |
| <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term> |
| <listitem> |
| <para> |
| Do not dump any schemas matching the <replaceable |
| class="parameter">schema</replaceable> pattern. The pattern is |
| interpreted according to the same rules as for <option>-n</>. |
| <option>-N</> can be given more than once to exclude schemas |
| matching any of several patterns. |
| </para> |
| |
| <para> |
| When both <option>-n</> and <option>-N</> are given, the behavior |
| is to dump just the schemas that match at least one <option>-n</> |
| switch but no <option>-N</> switches. If <option>-N</> appears |
| without <option>-n</>, then schemas matching <option>-N</> are |
| excluded from what is otherwise a normal dump. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-o</></term> |
| <term><option>--oids</></term> |
| <listitem> |
| <para> |
| Dump object identifiers (<acronym>OID</acronym>s) as part of the |
| data for every table. Use this option if your application references |
| the <acronym>OID</> |
| columns in some way (e.g., in a foreign key constraint). |
| Otherwise, this option should not be used. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-O</></term> |
| <term><option>--no-owner</option></term> |
| <listitem> |
| <para> |
| Do not output commands to set |
| ownership of objects to match the original database. |
| By default, <application>pg_dump</application> issues |
| <command>ALTER OWNER</> or |
| <command>SET SESSION AUTHORIZATION</command> |
| statements to set ownership of created database objects. |
| These statements |
| will fail when the script is run unless it is started by a superuser |
| (or the same user that owns all of the objects in the script). |
| To make a script that can be restored by any user, but will give |
| that user ownership of all the objects, specify <option>-O</>. |
| </para> |
| |
| <para> |
| This option is only meaningful for the plain-text format. For |
| the archive formats, you may specify the option when you |
| call <command>pg_restore</command>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-R</option></term> |
| <term><option>--no-reconnect</option></term> |
| <listitem> |
| <para> |
| This option is obsolete but still accepted for backwards |
| compatibility. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-s</option></term> |
| <term><option>--schema-only</option></term> |
| <listitem> |
| <para> |
| Dump only the object definitions (schema), not data. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-S <replaceable class="parameter">username</replaceable></option></term> |
| <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term> |
| <listitem> |
| <para> |
| Specify the superuser user name to use when disabling triggers. |
| This is only relevant if <option>--disable-triggers</> is used. |
| (Usually, it's better to leave this out, and instead start the |
| resulting script as superuser.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-t <replaceable class="parameter">table</replaceable></option></term> |
| <term><option>--table=<replaceable class="parameter">table</replaceable></option></term> |
| <listitem> |
| <para> |
| Dump only tables (or views or sequences) matching the table pattern. |
| Specify the table in the format schema.table. |
| |
| Multiple tables can be selected by writing multiple -t switches. |
| Also, the table parameter is interpreted as a pattern according |
| to the same rules used by the <literal>\d</> commands in psql, so multiple tables |
| can also be selected by writing wildcard characters in the pattern. |
| When using wildcards, be careful to quote the pattern if needed |
| to prevent the shell from expanding the wildcards. The -n and -N |
| switches have no effect when -t is used, because tables |
| selected by -t will be dumped regardless of those switches, |
| and non-table objects will not be dumped. |
| </para> |
| |
| <note> |
| <para> |
| When <option>-t</> is specified, <application>pg_dump</application> |
| makes no attempt to dump any other database objects that the selected |
| table(s) may depend upon. Therefore, there is no guarantee |
| that the results of a specific-table dump can be successfully |
| restored by themselves into a clean database. |
| </para> |
| </note> |
| |
| <note> |
| <para> |
| -t cannot be used to specify a child table partition. |
| To dump a partitioned table, you must specify the parent table name. |
| </para> |
| </note> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-T <replaceable class="parameter">table</replaceable></option></term> |
| <term><option>--exclude-table=<replaceable class="parameter">table</replaceable></option></term> |
| <listitem> |
| <para> |
| Do not dump any tables matching the table pattern. |
| The pattern is interpreted according to the same rules as for -t. |
| -T can be given more than once to exclude tables matching any of |
| several patterns. When both -t and -T are given, the behavior is |
| to dump just the tables that match at least one -t switch but |
| no -T switches. If -T appears without -t, then tables matching -T |
| are excluded from what is otherwise a normal dump. |
| </para> |
| |
| <para> |
| When both <option>-t</> and <option>-T</> are given, the behavior |
| is to dump just the tables that match at least one <option>-t</> |
| switch but no <option>-T</> switches. If <option>-T</> appears |
| without <option>-t</>, then tables matching <option>-T</> are |
| excluded from what is otherwise a normal dump. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-v</></term> |
| <term><option>--verbose</></term> |
| <listitem> |
| <para> |
| Specifies verbose mode. This will cause |
| <application>pg_dump</application> to output detailed object |
| comments and start/stop times to the dump file, and progress |
| messages to standard error. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-x</></term> |
| <term><option>--no-privileges</></term> |
| <term><option>--no-acl</></term> |
| <listitem> |
| <para> |
| Prevent dumping of access privileges (grant/revoke commands). |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--disable-dollar-quoting</></term> |
| <listitem> |
| <para> |
| This option disables the use of dollar quoting for function bodies, |
| and forces them to be quoted using SQL standard string syntax. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--disable-triggers</></term> |
| <listitem> |
| <para> |
| This option is only relevant when creating a data-only dump. |
| It instructs <application>pg_dump</application> to include commands |
| to temporarily disable triggers on the target tables while |
| the data is reloaded. Use this if you have referential |
| integrity checks or other triggers on the tables that you |
| do not want to invoke during data reload. |
| </para> |
| |
| <para> |
| Presently, the commands emitted for <option>--disable-triggers</> |
| must be done as superuser. So, you should also specify |
| a superuser name with <option>-S</>, or preferably be careful to |
| start the resulting script as a superuser. |
| </para> |
| |
| <para> |
| This option is only meaningful for the plain-text format. For |
| the archive formats, you may specify the option when you |
| call <command>pg_restore</command>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>--use-set-session-authorization</></term> |
| <listitem> |
| <para> |
| Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands |
| instead of <command>ALTER OWNER</> commands to determine object |
| ownership. This makes the dump more standards compatible, but |
| depending on the history of the objects in the dump, may not restore |
| properly. Also, a dump using <command>SET SESSION AUTHORIZATION</> |
| will certainly require superuser privileges to restore correctly, |
| whereas <command>ALTER OWNER</> requires lesser privileges. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-Z <replaceable class="parameter">0..9</replaceable></option></term> |
| <term><option>--compress=<replaceable class="parameter">0..9</replaceable></option></term> |
| <listitem> |
| <para> |
| Specify the compression level to use. Zero means no compression. |
| For the custom archive format, this specifies compression of |
| individual table-data segments, and the default is to compress |
| at a moderate level. |
| For plain text output, setting a nonzero compression level causes |
| the entire output file to be compressed, as though it had been |
| fed through <application>gzip</>; but the default is not to compress. |
| The tar archive format currently does not support compression at all. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </para> |
| |
| <para> |
| The following command-line options control the database connection parameters. |
| |
| <variablelist> |
| <varlistentry> |
| <term><option>-h <replaceable class="parameter">host</replaceable></option></term> |
| <term><option>--host=<replaceable class="parameter">host</replaceable></option></term> |
| <listitem> |
| <para> |
| Specifies the host name of the machine on which the server is |
| running. If the value begins with a slash, it is used as the |
| directory for the Unix domain socket. The default is taken |
| from the <envar>PGHOST</envar> environment variable, if set, |
| else a Unix domain socket connection is attempted. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-p <replaceable class="parameter">port</replaceable></option></term> |
| <term><option>--port=<replaceable class="parameter">port</replaceable></option></term> |
| <listitem> |
| <para> |
| Specifies the TCP port or local Unix domain socket file |
| extension on which the server is listening for connections. |
| Defaults to the <envar>PGPORT</envar> environment variable, if |
| set, or a compiled-in default. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-U <replaceable>username</replaceable></option></term> |
| <listitem> |
| <para> |
| Connect as the given user |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><option>-W</option></term> |
| <listitem> |
| <para> |
| Force a password prompt. This should happen automatically if |
| the server requires password authentication. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Environment</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><envar>PGDATABASE</envar></term> |
| <term><envar>PGHOST</envar></term> |
| <term><envar>PGPORT</envar></term> |
| <term><envar>PGUSER</envar></term> |
| |
| <listitem> |
| <para> |
| Default connection parameters. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| |
| <para> |
| This utility, like most other <productname>PostgreSQL</> utilities, |
| also uses the environment variables supported by <application>libpq</> |
| (see <xref linkend="libpq-envars">). |
| </para> |
| |
| </refsect1> |
| |
| <refsect1 id="app-pgdump-diagnostics"> |
| <title>Diagnostics</title> |
| |
| <para> |
| <application>pg_dump</application> internally executes |
| <command>SELECT</command> statements. If you have problems running |
| <application>pg_dump</application>, make sure you are able to |
| select information from the database using, for example, <xref |
| linkend="app-psql">. Also, any default connection settings and environment |
| variables used by the <application>libpq</application> front-end |
| library will apply. |
| </para> |
| </refsect1> |
| |
| |
| <refsect1 id="pg-dump-notes"> |
| <title>Notes</title> |
| |
| <para> |
| If your database cluster has any local additions to the <literal>template1</> database, |
| be careful to restore the output of <application>pg_dump</application> into a |
| truly empty database; otherwise you are likely to get errors due to |
| duplicate definitions of the added objects. To make an empty database |
| without any local additions, copy from <literal>template0</> not <literal>template1</>, |
| for example: |
| <programlisting> |
| CREATE DATABASE foo WITH TEMPLATE template0; |
| </programlisting> |
| </para> |
| |
| <para> |
| <application>pg_dump</application> has a few limitations: |
| |
| <itemizedlist> |
| <listitem> |
| <para> |
| When a data-only dump is chosen and the option |
| <option>--disable-triggers</> is used, |
| <application>pg_dump</application> emits commands to disable |
| triggers on user tables before inserting the data and commands |
| to re-enable them after the data has been inserted. If the |
| restore is stopped in the middle, the system catalogs may be |
| left in the wrong state. |
| </para> |
| </listitem> |
| |
| </itemizedlist> |
| </para> |
| |
| <para> |
| Members of tar archives are limited to a size less than 8 GB. |
| (This is an inherent limitation of the tar file format.) Therefore |
| this format cannot be used if the textual representation of any one table |
| exceeds that size. The total size of a tar archive and any of the |
| other output formats is not limited, except possibly by the |
| operating system. |
| </para> |
| |
| <para> |
| The dump file produced by <application>pg_dump</application> does |
| not contain the statistics used by the optimizer to make query |
| planning decisions. Therefore, it is wise to run |
| <command>ANALYZE</command> after restoring from a dump file to |
| ensure good performance. |
| </para> |
| |
| <para> |
| Because <application>pg_dump</application> is used to transfer data |
| to newer versions of <productname>PostgreSQL</>, the output of |
| <application>pg_dump</application> can be loaded into |
| newer <productname>PostgreSQL</> databases. It also can read older |
| <productname>PostgreSQL</> databases. However, it usually cannot |
| read newer <productname>PostgreSQL</> databases or produce dump output |
| that can be loaded into older database versions. To do this, manual |
| editing of the dump file might be required. |
| </para> |
| |
| </refsect1> |
| |
| <refsect1 id="pg-dump-examples"> |
| <title>Examples</title> |
| |
| <para> |
| To dump a database called <literal>mydb</> into a SQL-script file: |
| <screen> |
| <prompt>$</prompt> <userinput>pg_dump mydb > db.sql</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| To reload such a script into a (freshly created) database named |
| <literal>newdb</>: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>psql -d newdb -f db.sql</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| To dump a database into a custom-format archive file: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>pg_dump -Fc mydb > db.dump</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| To reload an archive file into a (freshly created) database named |
| <literal>newdb</>: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>pg_restore -d newdb db.dump</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| To dump a single table named <literal>mytab</>: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>pg_dump -t mytab mydb > db.sql</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| To dump all tables whose names start with <literal>emp</> in the |
| <literal>detroit</> schema, except for the table named |
| <literal>employee_log</literal>: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| To dump all schemas whose names start with <literal>east</> or |
| <literal>west</> and end in <literal>gsm</>, excluding any schemas whose |
| names contain the word <literal>test</>: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| The same, using regular expression notation to consolidate the switches: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| To dump all database objects except for tables whose names begin with |
| <literal>ts_</literal>: |
| |
| <screen> |
| <prompt>$</prompt> <userinput>pg_dump -T 'ts_*' mydb > db.sql</userinput> |
| </screen> |
| </para> |
| |
| <para> |
| To specify an upper-case or mixed-case name in <option>-t</> and related |
| switches, you need to double-quote the name; else it will be folded to |
| lower case (see <xref |
| linkend="APP-PSQL-patterns" endterm="APP-PSQL-patterns-title">). But |
| double quotes are special to the shell, so in turn they must be quoted. |
| Thus, to dump a single table with a mixed-case name, you need something |
| like |
| |
| <screen> |
| <prompt>$</prompt> <userinput>pg_dump -t '"MixedCaseName"' mydb > mytab.sql</userinput> |
| </screen> |
| </para> |
| |
| </refsect1> |
| |
| <refsect1> |
| <title>History</title> |
| |
| <para> |
| The <application>pg_dump</application> utility first appeared in |
| <application>Postgres95</application> release 0.02. The |
| non-plain-text output formats were introduced in |
| <productname>PostgreSQL</productname> release 7.1. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="app-pg-dumpall"></member> |
| <member><xref linkend="app-pgrestore"></member> |
| <member><xref linkend="app-psql"></member> |
| </simplelist> |
| </refsect1> |
| |
| </refentry> |
