doc/src/sgml/ref/pg_createsubscriber.sgml

   1 <!--
   2 doc/src/sgml/ref/pg_createsubscriber.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="app-pgcreatesubscriber">
   7  <indexterm zone="app-pgcreatesubscriber">
   8   <primary>pg_createsubscriber</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle><application>pg_createsubscriber</application></refentrytitle>
  13   <manvolnum>1</manvolnum>
  14   <refmiscinfo>Application</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>pg_createsubscriber</refname>
  19   <refpurpose>convert a physical replica into a new logical replica</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23   <cmdsynopsis>
  24    <command>pg_createsubscriber</command>
  25    <arg rep="repeat"><replaceable>option</replaceable></arg>
  26    <group choice="plain">
  27     <group choice="req">
  28      <arg choice="plain"><option>-d</option></arg>
  29      <arg choice="plain"><option>--database</option></arg>
  30     </group>
  31     <replaceable>dbname</replaceable>
  32     <group choice="req">
  33      <arg choice="plain"><option>-D</option> </arg>
  34      <arg choice="plain"><option>--pgdata</option></arg>
  35     </group>
  36     <replaceable>datadir</replaceable>
  37     <group choice="req">
  38      <arg choice="plain"><option>-P</option></arg>
  39      <arg choice="plain"><option>--publisher-server</option></arg>
  40     </group>
  41     <replaceable>connstr</replaceable>
  42    </group>
  43   </cmdsynopsis>
  44  </refsynopsisdiv>
  45
  46  <refsect1>
  47   <title>Description</title>
  48
  49   <para>
  50    <application>pg_createsubscriber</application> creates a new logical
  51    replica from a physical standby server.  All tables in the specified
  52    database are included in the <link linkend="logical-replication">logical
  53    replication</link> setup.  A pair of
  54    publication and subscription objects are created for each database.  It
  55    must be run at the target server.
  56   </para>
  57
  58   <para>
  59    After a successful run, the state of the target server is analogous to a
  60    fresh logical replication setup.  The main difference between the logical
  61    replication setup and <application>pg_createsubscriber</application> is how
  62    the data synchronization is done. <application>pg_createsubscriber</application>
  63    does not copy the initial table data. It does only the synchronization phase,
  64    which ensures each table is brought up to a synchronized state.
  65   </para>
  66
  67   <para>
  68    <application>pg_createsubscriber</application> targets large database
  69    systems because in logical replication setup, most of the time is spent
  70    doing the initial data copy.  Furthermore, a side effect of this long time
  71    spent synchronizing data is usually a large amount of changes to be applied
  72    (that were produced during the initial data copy), which increases even
  73    more the time when the logical replica will be available. For smaller
  74    databases, it is recommended to set up logical replication with initial data
  75    synchronization.  For details, see the <command>CREATE SUBSCRIPTION</command>
  76    <link linkend="sql-createsubscription-params-with-copy-data">
  77    <literal>copy_data</literal></link> option.
  78
  79   </para>
  80  </refsect1>
  81
  82  <refsect1>
  83   <title>Options</title>
  84
  85   <para>
  86    <application>pg_createsubscriber</application> accepts the following
  87    command-line arguments:
  88
  89    <variablelist>
  90     <varlistentry>
  91      <term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
  92      <term><option>--database=<replaceable class="parameter">dbname</replaceable></option></term>
  93      <listitem>
  94       <para>
  95        The name of the database in which to create a subscription.  Multiple
  96        databases can be selected by writing multiple <option>-d</option>
  97        switches.
  98       </para>
  99      </listitem>
 100     </varlistentry>
 101
 102     <varlistentry>
 103      <term><option>-D <replaceable class="parameter">directory</replaceable></option></term>
 104      <term><option>--pgdata=<replaceable class="parameter">directory</replaceable></option></term>
 105      <listitem>
 106       <para>
 107        The target directory that contains a cluster directory from a physical
 108        replica.
 109       </para>
 110      </listitem>
 111     </varlistentry>
 112
 113     <varlistentry>
 114      <term><option>-n</option></term>
 115      <term><option>--dry-run</option></term>
 116      <listitem>
 117       <para>
 118        Do everything except actually modifying the target directory.
 119       </para>
 120      </listitem>
 121     </varlistentry>
 122
 123     <varlistentry>
 124      <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
 125      <term><option>--subscriber-port=<replaceable class="parameter">port</replaceable></option></term>
 126      <listitem>
 127       <para>
 128        The port number on which the target server is listening for
 129        connections.  Defaults to running the target server on port 50432 to
 130        avoid unintended client connections.
 131       </para>
 132      </listitem>
 133     </varlistentry>
 134
 135     <varlistentry>
 136      <term><option>-P <replaceable class="parameter">connstr</replaceable></option></term>
 137      <term><option>--publisher-server=<replaceable class="parameter">connstr</replaceable></option></term>
 138      <listitem>
 139       <para>
 140        The connection string to the publisher.  For details see <xref
 141        linkend="libpq-connstring"/>.
 142       </para>
 143      </listitem>
 144     </varlistentry>
 145
 146     <varlistentry>
 147      <term><option>-s <replaceable class="parameter">dir</replaceable></option></term>
 148      <term><option>--socketdir=<replaceable class="parameter">dir</replaceable></option></term>
 149      <listitem>
 150       <para>
 151        The directory to use for postmaster sockets on target server.  The
 152        default is current directory.
 153       </para>
 154      </listitem>
 155     </varlistentry>
 156
 157     <varlistentry>
 158      <term><option>-t <replaceable class="parameter">seconds</replaceable></option></term>
 159      <term><option>--recovery-timeout=<replaceable class="parameter">seconds</replaceable></option></term>
 160      <listitem>
 161       <para>
 162        The maximum number of seconds to wait for recovery to end.  Setting to
 163        0 disables.  The default is 0.
 164       </para>
 165      </listitem>
 166     </varlistentry>
 167
 168     <varlistentry>
 169      <term><option>-U <replaceable class="parameter">username</replaceable></option></term>
 170      <term><option>--subscriber-username=<replaceable class="parameter">username</replaceable></option></term>
 171      <listitem>
 172       <para>
 173        The user name to connect as on target server.  Defaults to the current
 174        operating system user name.
 175       </para>
 176      </listitem>
 177     </varlistentry>
 178
 179     <varlistentry>
 180      <term><option>-v</option></term>
 181      <term><option>--verbose</option></term>
 182      <listitem>
 183       <para>
 184        Enables verbose mode.  This will cause
 185        <application>pg_createsubscriber</application> to output progress
 186        messages and detailed information about each step to standard error.
 187        Repeating the option causes additional debug-level messages to appear
 188        on standard error.
 189       </para>
 190      </listitem>
 191     </varlistentry>
 192
 193     <varlistentry>
 194      <term><option>--config-file=<replaceable class="parameter">filename</replaceable></option></term>
 195      <listitem>
 196       <para>
 197        Use the specified main server configuration file for the target data
 198        directory.  <application>pg_createsubscriber</application> internally uses
 199        the <application>pg_ctl</application> command to start and
 200        stop the target server.  It allows you to specify the actual
 201        <filename>postgresql.conf</filename> configuration file if it is stored
 202        outside the data directory.
 203       </para>
 204      </listitem>
 205     </varlistentry>
 206
 207     <varlistentry>
 208      <term><option>--publication=<replaceable class="parameter">name</replaceable></option></term>
 209      <listitem>
 210       <para>
 211        The publication name to set up the logical replication.  Multiple
 212        publications can be specified by writing multiple
 213        <option>--publication</option> switches.  The number of publication
 214        names must match the number of specified databases, otherwise an error
 215        is reported.  The order of the multiple publication name switches must
 216        match the order of database switches.  If this option is not specified,
 217        a generated name is assigned to the publication name.
 218       </para>
 219      </listitem>
 220     </varlistentry>
 221
 222     <varlistentry>
 223      <term><option>--replication-slot=<replaceable class="parameter">name</replaceable></option></term>
 224      <listitem>
 225       <para>
 226        The replication slot name to set up the logical replication.  Multiple
 227        replication slots can be specified by writing multiple
 228        <option>--replication-slot</option> switches.  The number of
 229        replication slot names must match the number of specified databases,
 230        otherwise an error is reported.  The order of the multiple replication
 231        slot name switches must match the order of database switches.  If this
 232        option is not specified, the subscription name is assigned to the
 233        replication slot name.
 234       </para>
 235      </listitem>
 236     </varlistentry>
 237
 238     <varlistentry>
 239      <term><option>--subscription=<replaceable class="parameter">name</replaceable></option></term>
 240      <listitem>
 241       <para>
 242        The subscription name to set up the logical replication.  Multiple
 243        subscriptions can be specified by writing multiple
 244        <option>--subscription</option> switches.  The number of subscription
 245        names must match the number of specified databases, otherwise an error
 246        is reported.  The order of the multiple subscription name switches must
 247        match the order of database switches.  If this option is not specified,
 248        a generated name is assigned to the subscription name.
 249       </para>
 250      </listitem>
 251     </varlistentry>
 252
 253     <varlistentry>
 254      <term><option>-V</option></term>
 255      <term><option>--version</option></term>
 256      <listitem>
 257       <para>
 258        Print the <application>pg_createsubscriber</application> version and exit.
 259       </para>
 260      </listitem>
 261     </varlistentry>
 262
 263     <varlistentry>
 264      <term><option>-?</option></term>
 265      <term><option>--help</option></term>
 266      <listitem>
 267       <para>
 268        Show help about <application>pg_createsubscriber</application> command
 269        line arguments, and exit.
 270       </para>
 271      </listitem>
 272     </varlistentry>
 273     </variablelist>
 274    </para>
 275  </refsect1>
 276
 277  <refsect1>
 278   <title>Notes</title>
 279
 280   <refsect2>
 281    <title>Prerequisites</title>
 282
 283    <para>
 284     There are some prerequisites for
 285     <application>pg_createsubscriber</application> to convert the target server
 286     into a logical replica.  If these are not met, an error will be reported.
 287     The source and target servers must have the same major version as the
 288     <application>pg_createsubscriber</application>.  The given target data
 289     directory must have the same system identifier as the source data
 290     directory.  The given database user for the target data directory must have
 291     privileges for creating <link
 292     linkend="sql-createsubscription">subscriptions</link> and using <link
 293     linkend="pg-replication-origin-advance"><function>pg_replication_origin_advance()</function></link>.
 294    </para>
 295
 296    <para>
 297     The target server must be used as a physical standby.  The target server
 298     must have <xref linkend="guc-max-replication-slots"/> and <xref
 299     linkend="guc-max-logical-replication-workers"/> configured to a value
 300     greater than or equal to the number of specified databases.  The target
 301     server must have <xref linkend="guc-max-worker-processes"/> configured to a
 302     value greater than the number of specified databases.  The target server
 303     must accept local connections.
 304    </para>
 305
 306    <para>
 307     The source server must accept connections from the target server.  The
 308     source server must not be in recovery. The source server must have <xref
 309     linkend="guc-wal-level"/> as <literal>logical</literal>.  The source server
 310     must have <xref linkend="guc-max-replication-slots"/> configured to a value
 311     greater than or equal to the number of specified databases plus existing
 312     replication slots.  The source server must have <xref
 313     linkend="guc-max-wal-senders"/> configured to a value greater than or equal
 314     to the number of specified databases and existing WAL sender processes.
 315    </para>
 316   </refsect2>
 317
 318   <refsect2>
 319    <title>Warnings</title>
 320
 321    <para>
 322     If <application>pg_createsubscriber</application> fails after the target
 323     server was promoted, then the data directory is likely not in a state that
 324     can be recovered.  In such case, creating a new standby server is
 325     recommended.
 326    </para>
 327
 328    <para>
 329     <application>pg_createsubscriber</application> usually starts the target
 330     server with different connection settings during transformation.  Hence,
 331     connections to the target server should fail.
 332    </para>
 333
 334    <para>
 335     Since DDL commands are not replicated by logical replication, avoid
 336     executing DDL commands that change the database schema while running
 337     <application>pg_createsubscriber</application>.  If the target server has
 338     already been converted to logical replica, the DDL commands might not be
 339     replicated, which might cause an error.
 340    </para>
 341
 342    <para>
 343     If <application>pg_createsubscriber</application> fails while processing,
 344     objects (publications, replication slots) created on the source server are
 345     removed.  The removal might fail if the target server cannot connect to
 346     the source server.  In such a case, a warning message will inform the
 347     objects left.  If the target server is running, it will be stopped.
 348    </para>
 349
 350    <para>
 351     If the replication is using <xref linkend="guc-primary-slot-name"/>, it
 352     will be removed from the source server after the logical replication
 353     setup.
 354    </para>
 355
 356    <para>
 357     If the target server is a synchronous replica, transaction commits on the
 358     primary might wait for replication while running
 359     <application>pg_createsubscriber</application>.
 360    </para>
 361
 362    <para>
 363     <application>pg_createsubscriber</application> sets up logical
 364     replication with two-phase commit disabled.  This means that any
 365     prepared transactions will be replicated at the time
 366     of <command>COMMIT PREPARED</command>, without advance preparation.
 367     Once setup is complete, you can manually drop and re-create the
 368     subscription(s) with
 369     the <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
 370     option enabled.
 371    </para>
 372
 373    <para>
 374     <application>pg_createsubscriber</application> changes the system
 375     identifier using <application>pg_resetwal</application>.  It would avoid
 376     situations in which the target server might use WAL files from the source
 377     server.  If the target server has a standby, replication will break and a
 378     fresh standby should be created.
 379    </para>
 380   </refsect2>
 381
 382   <refsect2>
 383    <title>How It Works</title>
 384
 385    <para>
 386     The basic idea is to have a replication start point from the source server
 387     and set up a logical replication to start from this point:
 388    </para>
 389
 390    <procedure>
 391     <step>
 392      <para>
 393       Start the target server with the specified command-line options.  If the
 394       target server is already running,
 395       <application>pg_createsubscriber</application> will terminate with an
 396       error.
 397      </para>
 398     </step>
 399
 400     <step>
 401      <para>
 402       Check if the target server can be converted.  There are also a few
 403       checks on the source server.  If any of the prerequisites are not met,
 404       <application>pg_createsubscriber</application> will terminate with an
 405       error.
 406      </para>
 407     </step>
 408
 409     <step>
 410      <para>
 411       Create a publication and replication slot for each specified database on
 412       the source server.  Each publication is created using <link
 413       linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
 414       TABLES</literal></link>.  If the <option>--publication</option> option
 415       is not specified, the publication has the following name pattern:
 416       <quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
 417       database <parameter>oid</parameter>, random <parameter>int</parameter>).
 418       If the <option>--replication-slot</option> option is not specified, the
 419       replication slot has the following name pattern:
 420       <quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameters:
 421       database <parameter>oid</parameter>, random <parameter>int</parameter>).
 422       These replication slots will be used by the subscriptions in a future
 423       step.  The last replication slot LSN is used as a stopping point in the
 424       <xref linkend="guc-recovery-target-lsn"/> parameter and by the
 425       subscriptions as a replication start point.  It guarantees that no
 426       transaction will be lost.
 427      </para>
 428     </step>
 429
 430     <step>
 431      <para>
 432       Write recovery parameters into the target data directory and restart the
 433       target server.  It specifies an LSN (<xref
 434       linkend="guc-recovery-target-lsn"/>) of the write-ahead log location up
 435       to which recovery will proceed.  It also specifies
 436       <literal>promote</literal> as the action that the server should take
 437       once the recovery target is reached.  Additional <link
 438       linkend="runtime-config-wal-recovery-target">recovery parameters</link>
 439       are added to avoid unexpected behavior during the recovery process such
 440       as end of the recovery as soon as a consistent state is reached (WAL
 441       should be applied until the replication start location) and multiple
 442       recovery targets that can cause a failure.  This step finishes once the
 443       server ends standby mode and is accepting read-write transactions.  If
 444       <option>--recovery-timeout</option> option is set,
 445       <application>pg_createsubscriber</application> terminates if recovery
 446       does not end until the given number of seconds.
 447      </para>
 448     </step>
 449
 450     <step>
 451      <para>
 452       Create a subscription for each specified database on the target server.
 453       If the <option>--subscription</option> option is not specified, the
 454       subscription has the following name pattern:
 455       <quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameters:
 456       database <parameter>oid</parameter>, random <parameter>int</parameter>).
 457       It does not copy existing data from the source server.  It does not
 458       create a replication slot.  Instead, it uses the replication slot that
 459       was created in a previous step.  The subscription is created but it is
 460       not enabled yet.  The reason is the replication progress must be set to
 461       the replication start point before starting the replication.
 462      </para>
 463     </step>
 464
 465     <step>
 466      <para>
 467       Drop publications on the target server that were replicated because they
 468       were created before the replication start location.  It has no use on
 469       the subscriber.
 470      </para>
 471     </step>
 472
 473     <step>
 474      <para>
 475       Set the replication progress to the replication start point for each
 476       subscription.  When the target server starts the recovery process, it
 477       catches up to the replication start point.  This is the exact LSN to be
 478       used as a initial replication location for each subscription.  The
 479       replication origin name is obtained since the subscription was created.
 480       The replication origin name and the replication start point are used in
 481       <link
 482       linkend="pg-replication-origin-advance"><function>pg_replication_origin_advance()</function></link>
 483       to set up the initial replication location.
 484      </para>
 485     </step>
 486
 487     <step>
 488      <para>
 489       Enable the subscription for each specified database on the target server.
 490       The subscription starts applying transactions from the replication start
 491       point.
 492      </para>
 493     </step>
 494
 495     <step>
 496      <para>
 497       If the standby server was using <xref linkend="guc-primary-slot-name"/>,
 498       it has no use from now on so drop it.
 499      </para>
 500     </step>
 501
 502     <step>
 503      <para>
 504       If the standby server contains <link
 505       linkend="logicaldecoding-replication-slots-synchronization">failover
 506       replication slots</link>, they cannot be synchronized anymore, so drop
 507       them.
 508      </para>
 509     </step>
 510
 511     <step>
 512      <para>
 513       Update the system identifier on the target server. The
 514       <xref linkend="app-pgresetwal"/> is run to modify the system identifier.
 515       The target server is stopped as a <command>pg_resetwal</command> requirement.
 516      </para>
 517     </step>
 518    </procedure>
 519   </refsect2>
 520  </refsect1>
 521
 522  <refsect1>
 523   <title>Examples</title>
 524
 525   <para>
 526    To create a logical replica for databases <literal>hr</literal> and
 527    <literal>finance</literal> from a physical replica at
 528    <literal>foo</literal>:
 529 <screen>
 530 <prompt>$</prompt> <userinput>pg_createsubscriber -D /usr/local/pgsql/data -P "host=foo" -d hr -d finance</userinput>
 531 </screen>
 532   </para>
 533  </refsect1>
 534
 535  <refsect1>
 536   <title>See Also</title>
 537
 538   <simplelist type="inline">
 539    <member><xref linkend="app-pgbasebackup"/></member>
 540   </simplelist>
 541  </refsect1>
 542 </refentry>