Home Explore Blog CI



postgresql

6th chunk of `doc/src/sgml/ref/pg_dump.sgml`
6567d25750a4c832e62e5c45c678a939f7875328278cdbc80000000100000fb6
   increases the load on the database server. You can only use this option with the
        directory output format because this is the only output format where multiple processes
        can write their data at the same time.
       </para>
       <para><application>pg_dump</application> will open <replaceable class="parameter">njobs</replaceable>
        + 1 connections to the database, so make sure your <xref linkend="guc-max-connections"/>
        setting is high enough to accommodate all connections.
       </para>
       <para>
        Requesting exclusive locks on database objects while running a parallel dump could
        cause the dump to fail. The reason is that the <application>pg_dump</application> leader process
        requests shared locks (<link linkend="locking-tables">ACCESS SHARE</link>) on the
        objects that the worker processes are going to dump later in order to
        make sure that nobody deletes them and makes them go away while the dump is running.
        If another client then requests an exclusive lock on a table, that lock will not be
        granted but will be queued waiting for the shared lock of the leader process to be
        released. Consequently any other access to the table will not be granted either and
        will queue after the exclusive lock request. This includes the worker process trying
        to dump the table. Without any precautions this would be a classic deadlock situation.
        To detect this conflict, the <application>pg_dump</application> worker process requests another
        shared lock using the <literal>NOWAIT</literal> option. If the worker process is not granted
        this shared lock, somebody else must have requested an exclusive lock in the meantime
        and there is no way to continue with the dump, so <application>pg_dump</application> has no choice
        but to abort the dump.
       </para>
       <para>
        To perform a parallel dump, the database server needs to support
        synchronized snapshots, a feature that was introduced in
        <productname>PostgreSQL</productname> 9.2 for primary servers and 10
        for standbys. With this feature, database clients can ensure they see
        the same data set even though they use different connections.
        <command>pg_dump -j</command> uses multiple database connections; it
        connects to the database once with the leader process and once again
        for each worker job. Without the synchronized snapshot feature, the
        different worker jobs wouldn't be guaranteed to see the same data in
        each connection, which could lead to an inconsistent backup.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><option>-n <replaceable class="parameter">pattern</replaceable></option></term>
      <term><option>--schema=<replaceable class="parameter">pattern</replaceable></option></term>
      <listitem>
       <para>
        Dump only schemas matching <replaceable
        class="parameter">pattern</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</option> switches.  The
        <replaceable class="parameter">pattern</replaceable> parameter is
        interpreted as a pattern according to the same rules used by
        <application>psql</application>'s <literal>\d</literal> commands
        (see <xref linkend="app-psql-patterns"/>),
        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;  see
        <xref linkend="pg-dump-examples"/> below.
       </para>

       <note>
        <para>
         When <option>-n</option> is specified, <application>pg_dump</application>

Title: Details of Parallel Dumps and Schema Selection with pg_dump
Summary
This section details the implications of using parallel dumps in pg_dump, explaining that it opens multiple connections and requires synchronized snapshots (introduced in PostgreSQL 9.2/10). It describes how the leader process acquires shared locks to prevent objects from being deleted during the dump and how exclusive locks from other clients can lead to deadlocks, causing the dump to abort. Then it explains the usage of the -n/--schema option, which allows dumping only schemas matching a specified pattern. Multiple schemas can be selected with multiple -n switches or by using wildcards in the pattern.