Home Explore Blog CI



postgresql

24th chunk of `doc/src/sgml/ref/pg_dump.sgml`
4b6deabffb06af77039966300275fe5439a3da950ab937e20000000100000d6f
 and <xref
   linkend="autovacuum"/> for more information.
  </para>

  <para>
   Because <application>pg_dump</application> is used to transfer data
   to newer versions of <productname>PostgreSQL</productname>, the output of
   <application>pg_dump</application> can be expected to load into
   <productname>PostgreSQL</productname> server versions newer than
   <application>pg_dump</application>'s version.  <application>pg_dump</application> can also
   dump from <productname>PostgreSQL</productname> servers older than its own version.
   (Currently, servers back to version 9.2 are supported.)
   However, <application>pg_dump</application> cannot dump from
   <productname>PostgreSQL</productname> servers newer than its own major version;
   it will refuse to even try, rather than risk making an invalid dump.
   Also, it is not guaranteed that <application>pg_dump</application>'s output can
   be loaded into a server of an older major version &mdash; not even if the
   dump was taken from a server of that version.  Loading a dump file
   into an older server may require manual editing of the dump file
   to remove syntax not understood by the older server.
   Use of the <option>--quote-all-identifiers</option> option is recommended
   in cross-version cases, as it can prevent problems arising from varying
   reserved-word lists in different <productname>PostgreSQL</productname> versions.
  </para>

  <para>
   When dumping logical replication subscriptions,
   <application>pg_dump</application> will generate <command>CREATE
   SUBSCRIPTION</command> commands that use the <literal>connect = false</literal>
   option, so that restoring the subscription does not make remote connections
   for creating a replication slot or for initial table copy.  That way, the
   dump can be restored without requiring network access to the remote
   servers.  It is then up to the user to reactivate the subscriptions in a
   suitable way.  If the involved hosts have changed, the connection
   information might have to be changed.  It might also be appropriate to
   truncate the target tables before initiating a new full table copy.  If users
   intend to copy initial data during refresh they must create the slot with
   <literal>two_phase = false</literal>.  After the initial sync, the
   <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
   option will be automatically enabled by the subscriber if the subscription
   had been originally created with <literal>two_phase = true</literal> option.
  </para>

  <para>
   It is generally recommended to use the <option>-X</option>
   (<option>--no-psqlrc</option>) option when restoring a database from a
   plain-text <application>pg_dump</application> script to ensure a clean
   restore process and prevent potential conflicts with
   non-default <application>psql</application> configurations.
  </para>
 </refsect1>

 <refsect1 id="pg-dump-examples" xreflabel="Examples">
  <title>Examples</title>

  <para>
   To dump a database called <literal>mydb</literal> into an SQL-script file:
<screen>
<prompt>$</prompt> <userinput>pg_dump mydb &gt; db.sql</userinput>
</screen>
  </para>

  <para>
   To reload such a script into a (freshly created) database named
   <literal>newdb</literal>:

<screen>
<prompt>$</prompt> <userinput>psql -X -d newdb -f db.sql</userinput>
</screen>
  </para>

  <para>
   To dump a database

Title: pg_dump Compatibility, Logical Replication Subscriptions, and Usage Examples
Summary
This section discusses pg_dump's version compatibility, advising it can dump from older PostgreSQL servers (down to 9.2) but not newer ones. Loading dumps into older servers may require manual editing. The --quote-all-identifiers option is suggested for cross-version compatibility. It then covers dumping logical replication subscriptions, creating CREATE SUBSCRIPTION commands with connect = false for restoration without remote connections, requiring manual reactivation. It also recommends the -X/--no-psqlrc option during restoration to prevent conflicts with psql configurations. Finally, it provides examples of dumping a database into an SQL script and reloading it into a new database.