doc/src/sgml/ref/create_subscription.sgml

   1 <!--
   2 doc/src/sgml/ref/create_subscription.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-createsubscription">
   7  <indexterm zone="sql-createsubscription">
   8   <primary>CREATE SUBSCRIPTION</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>CREATE SUBSCRIPTION</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>CREATE SUBSCRIPTION</refname>
  19   <refpurpose>define a new subscription</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceable>
  25     CONNECTION '<replaceable class="parameter">conninfo</replaceable>'
  26     PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...]
  27     [ WITH ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
  28 </synopsis>
  29  </refsynopsisdiv>
  30
  31  <refsect1>
  32   <title>Description</title>
  33
  34   <para>
  35    <command>CREATE SUBSCRIPTION</command> adds a new logical-replication
  36    subscription.  The user that creates a subscription becomes the owner
  37    of the subscription. The subscription name must be distinct from the name of
  38    any existing subscription in the current database.
  39   </para>
  40
  41   <para>
  42    A subscription represents a replication connection to the publisher.
  43    Hence, in addition to adding definitions in the local catalogs, this
  44    command normally creates a replication slot on the publisher.
  45   </para>
  46
  47   <para>
  48    A logical replication worker will be started to replicate data for the new
  49    subscription at the commit of the transaction where this command is run,
  50    unless the subscription is initially disabled.
  51   </para>
  52
  53   <para>
  54    To be able to create a subscription, you must have the privileges of
  55    the <literal>pg_create_subscription</literal> role, as well as
  56    <literal>CREATE</literal> privileges on the current database.
  57   </para>
  58
  59   <para>
  60    Additional information about subscriptions and logical replication as a
  61    whole is available at <xref linkend="logical-replication-subscription"/> and
  62    <xref linkend="logical-replication"/>.
  63   </para>
  64
  65  </refsect1>
  66
  67  <refsect1>
  68   <title>Parameters</title>
  69
  70   <variablelist>
  71    <varlistentry id="sql-createsubscription-params-name">
  72     <term><replaceable class="parameter">subscription_name</replaceable></term>
  73     <listitem>
  74      <para>
  75       The name of the new subscription.
  76      </para>
  77     </listitem>
  78    </varlistentry>
  79
  80    <varlistentry id="sql-createsubscription-params-connection">
  81     <term><literal>CONNECTION '<replaceable class="parameter">conninfo</replaceable>'</literal></term>
  82     <listitem>
  83      <para>
  84       The <application>libpq</application> connection string defining how
  85       to connect to the publisher database.  For details see
  86       <xref linkend="libpq-connstring"/>.
  87      </para>
  88     </listitem>
  89    </varlistentry>
  90
  91    <varlistentry id="sql-createsubscription-params-publication">
  92     <term><literal>PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...]</literal></term>
  93     <listitem>
  94      <para>
  95       Names of the publications on the publisher to subscribe to.
  96      </para>
  97     </listitem>
  98    </varlistentry>
  99
 100    <varlistentry id="sql-createsubscription-params-with">
 101     <term><literal>WITH ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
 102     <listitem>
 103      <para>
 104       This clause specifies optional parameters for a subscription.
 105      </para>
 106
 107      <para>
 108       The following parameters control what happens during subscription creation:
 109
 110       <variablelist>
 111
 112        <varlistentry id="sql-createsubscription-params-with-connect">
 113         <term><literal>connect</literal> (<type>boolean</type>)</term>
 114         <listitem>
 115          <para>
 116           Specifies whether the <command>CREATE SUBSCRIPTION</command>
 117           command should connect to the publisher at all.  The default
 118           is <literal>true</literal>.  Setting this to
 119           <literal>false</literal> will force the values of
 120           <literal>create_slot</literal>, <literal>enabled</literal> and
 121           <literal>copy_data</literal> to <literal>false</literal>.
 122           (You cannot combine setting <literal>connect</literal>
 123           to <literal>false</literal> with
 124           setting <literal>create_slot</literal>, <literal>enabled</literal>,
 125           or <literal>copy_data</literal> to <literal>true</literal>.)
 126          </para>
 127
 128          <para>
 129           Since no connection is made when this option is
 130           <literal>false</literal>, no tables are subscribed. To initiate
 131           replication, you must manually create the replication slot, enable
 132           the subscription, and refresh the subscription. See
 133           <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
 134           for examples.
 135          </para>
 136         </listitem>
 137        </varlistentry>
 138
 139        <varlistentry id="sql-createsubscription-params-with-create-slot">
 140         <term><literal>create_slot</literal> (<type>boolean</type>)</term>
 141         <listitem>
 142          <para>
 143           Specifies whether the command should create the replication slot on
 144           the publisher.  The default is <literal>true</literal>.
 145          </para>
 146          <para>
 147           If set to <literal>false</literal>, you are responsible for
 148           creating the publisher's slot in some other way. See
 149           <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
 150           for examples.
 151          </para>
 152         </listitem>
 153        </varlistentry>
 154
 155        <varlistentry id="sql-createsubscription-params-with-enabled">
 156         <term><literal>enabled</literal> (<type>boolean</type>)</term>
 157         <listitem>
 158          <para>
 159           Specifies whether the subscription should be actively replicating
 160           or whether it should just be set up but not started yet.  The default
 161           is <literal>true</literal>.
 162          </para>
 163         </listitem>
 164        </varlistentry>
 165
 166        <varlistentry id="sql-createsubscription-params-with-slot-name">
 167         <term><literal>slot_name</literal> (<type>string</type>)</term>
 168         <listitem>
 169          <para>
 170           Name of the publisher's replication slot to use.  The default is
 171           to use the name of the subscription for the slot name.
 172          </para>
 173
 174          <para>
 175           Setting <literal>slot_name</literal> to <literal>NONE</literal>
 176           means there will be no replication slot associated with the
 177           subscription. Such subscriptions must also have both
 178           <literal>enabled</literal> and <literal>create_slot</literal> set to
 179           <literal>false</literal>.  Use this when you will be creating the
 180           replication slot later manually. See
 181           <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
 182           for examples.
 183          </para>
 184         </listitem>
 185        </varlistentry>
 186       </variablelist>
 187      </para>
 188
 189      <para>
 190       The following parameters control the subscription's replication
 191       behavior after it has been created:
 192
 193       <variablelist>
 194
 195        <varlistentry id="sql-createsubscription-params-with-binary">
 196         <term><literal>binary</literal> (<type>boolean</type>)</term>
 197         <listitem>
 198          <para>
 199           Specifies whether the subscription will request the publisher to send
 200           the data in binary format (as opposed to text). The default is
 201           <literal>false</literal>. Any initial table synchronization copy
 202           (see <literal>copy_data</literal>) also uses the same format. Binary
 203           format can be faster than the text format, but it is less portable
 204           across machine architectures and <productname>PostgreSQL</productname>
 205           versions. Binary format is very data type specific; for example, it
 206           will not allow copying from a <type>smallint</type> column to an
 207           <type>integer</type> column, even though that would work fine in text
 208           format. Even when this option is enabled, only data types having binary
 209           send and receive functions will be transferred in binary. Note that
 210           the initial synchronization requires all data types to have binary
 211           send and receive functions, otherwise the synchronization will fail
 212           (see <xref linkend="sql-createtype"/> for more about send/receive
 213           functions).
 214          </para>
 215
 216          <para>
 217           When doing cross-version replication, it could be that the
 218           publisher has a binary send function for some data type, but the
 219           subscriber lacks a binary receive function for that type.  In
 220           such a case, data transfer will fail, and
 221           the <literal>binary</literal> option cannot be used.
 222          </para>
 223
 224          <para>
 225           If the publisher is a <productname>PostgreSQL</productname> version
 226           before 16, then any initial table synchronization will use text format
 227           even if <literal>binary = true</literal>.
 228          </para>
 229         </listitem>
 230        </varlistentry>
 231
 232        <varlistentry id="sql-createsubscription-params-with-copy-data">
 233         <term><literal>copy_data</literal> (<type>boolean</type>)</term>
 234         <listitem>
 235          <para>
 236           Specifies whether to copy pre-existing data in the publications
 237           that are being subscribed to when the replication starts.
 238           The default is <literal>true</literal>.
 239          </para>
 240          <para>
 241           If the publications contain <literal>WHERE</literal> clauses, it
 242           will affect what data is copied. Refer to the
 243           <xref linkend="sql-createsubscription-notes" /> for details.
 244          </para>
 245          <para>
 246           See <xref linkend="sql-createsubscription-notes"/> for details of how
 247           <literal>copy_data = true</literal> can interact with the
 248           <literal>origin</literal> parameter.
 249          </para>
 250         </listitem>
 251        </varlistentry>
 252
 253        <varlistentry id="sql-createsubscription-params-with-streaming">
 254         <term><literal>streaming</literal> (<type>enum</type>)</term>
 255         <listitem>
 256          <para>
 257           Specifies whether to enable streaming of in-progress transactions
 258           for this subscription.  The default value is <literal>off</literal>,
 259           meaning all transactions are fully decoded on the publisher and only
 260           then sent to the subscriber as a whole.
 261          </para>
 262
 263          <para>
 264           If set to <literal>on</literal>, the incoming changes are written to
 265           temporary files and then applied only after the transaction is
 266           committed on the publisher and received by the subscriber.
 267          </para>
 268
 269          <para>
 270           If set to <literal>parallel</literal>, incoming changes are directly
 271           applied via one of the parallel apply workers, if available. If no
 272           parallel apply worker is free to handle streaming transactions then
 273           the changes are written to temporary files and applied after the
 274           transaction is committed. Note that if an error happens in a
 275           parallel apply worker, the finish LSN of the remote transaction
 276           might not be reported in the server log.
 277          </para>
 278         </listitem>
 279        </varlistentry>
 280
 281        <varlistentry id="sql-createsubscription-params-with-synchronous-commit">
 282         <term><literal>synchronous_commit</literal> (<type>enum</type>)</term>
 283         <listitem>
 284          <para>
 285           The value of this parameter overrides the
 286           <xref linkend="guc-synchronous-commit"/> setting within this
 287           subscription's apply worker processes.  The default value
 288           is <literal>off</literal>.
 289          </para>
 290
 291          <para>
 292           It is safe to use <literal>off</literal> for logical replication:
 293           If the subscriber loses transactions because of missing
 294           synchronization, the data will be sent again from the publisher.
 295          </para>
 296
 297          <para>
 298           A different setting might be appropriate when doing synchronous
 299           logical replication.  The logical replication workers report the
 300           positions of writes and flushes to the publisher, and when using
 301           synchronous replication, the publisher will wait for the actual
 302           flush.  This means that setting
 303           <literal>synchronous_commit</literal> for the subscriber to
 304           <literal>off</literal> when the subscription is used for
 305           synchronous replication might increase the latency for
 306           <command>COMMIT</command> on the publisher.  In this scenario, it
 307           can be advantageous to set <literal>synchronous_commit</literal>
 308           to <literal>local</literal> or higher.
 309          </para>
 310         </listitem>
 311        </varlistentry>
 312
 313        <varlistentry id="sql-createsubscription-params-with-two-phase">
 314         <term><literal>two_phase</literal> (<type>boolean</type>)</term>
 315         <listitem>
 316          <para>
 317           Specifies whether two-phase commit is enabled for this subscription.
 318           The default is <literal>false</literal>.
 319          </para>
 320
 321          <para>
 322           When two-phase commit is enabled, prepared transactions are sent
 323           to the subscriber at the time of <command>PREPARE
 324           TRANSACTION</command>, and are processed as two-phase
 325           transactions on the subscriber too.  Otherwise, prepared
 326           transactions are sent to the subscriber only when committed, and
 327           are then processed immediately by the subscriber.
 328          </para>
 329
 330          <para>
 331           The implementation of two-phase commit requires that replication
 332           has successfully finished the initial table synchronization
 333           phase. So even when <literal>two_phase</literal> is enabled for a
 334           subscription, the internal two-phase state remains
 335           temporarily <quote>pending</quote> until the initialization phase
 336           completes. See column <structfield>subtwophasestate</structfield>
 337           of <link linkend="catalog-pg-subscription"><structname>pg_subscription</structname></link>
 338           to know the actual two-phase state.
 339          </para>
 340
 341         </listitem>
 342        </varlistentry>
 343
 344        <varlistentry id="sql-createsubscription-params-with-disable-on-error">
 345         <term><literal>disable_on_error</literal> (<type>boolean</type>)</term>
 346         <listitem>
 347          <para>
 348           Specifies whether the subscription should be automatically disabled
 349           if any errors are detected by subscription workers during data
 350           replication from the publisher. The default is
 351           <literal>false</literal>.
 352          </para>
 353         </listitem>
 354        </varlistentry>
 355
 356        <varlistentry id="sql-createsubscription-params-with-password-required">
 357         <term><literal>password_required</literal> (<type>boolean</type>)</term>
 358         <listitem>
 359          <para>
 360           Specifies whether connections to the publisher made as a result
 361           of this subscription must use password authentication. This setting
 362           is ignored when the subscription is owned by a superuser.
 363           The default is <literal>true</literal>. Only superusers can set
 364           this value to <literal>false</literal>.
 365          </para>
 366         </listitem>
 367        </varlistentry>
 368
 369        <varlistentry id="sql-createsubscription-params-with-run-as-owner">
 370         <term><literal>run_as_owner</literal> (<type>boolean</type>)</term>
 371         <listitem>
 372          <para>
 373           If true, all replication actions are performed as the subscription
 374           owner. If false, replication workers will perform actions on each
 375           table as the owner of that table. The latter configuration is
 376           generally much more secure; for details, see
 377           <xref linkend="logical-replication-security" />.
 378           The default is <literal>false</literal>.
 379          </para>
 380         </listitem>
 381        </varlistentry>
 382
 383        <varlistentry id="sql-createsubscription-params-with-origin">
 384         <term><literal>origin</literal> (<type>string</type>)</term>
 385         <listitem>
 386          <para>
 387           Specifies whether the subscription will request the publisher to only
 388           send changes that don't have an origin or send changes regardless of
 389           origin. Setting <literal>origin</literal> to <literal>none</literal>
 390           means that the subscription will request the publisher to only send
 391           changes that don't have an origin. Setting <literal>origin</literal>
 392           to <literal>any</literal> means that the publisher sends changes
 393           regardless of their origin. The default is <literal>any</literal>.
 394          </para>
 395          <para>
 396           See <xref linkend="sql-createsubscription-notes"/> for details of how
 397           <literal>copy_data = true</literal> can interact with the
 398           <literal>origin</literal> parameter.
 399          </para>
 400         </listitem>
 401        </varlistentry>
 402       </variablelist></para>
 403
 404     </listitem>
 405    </varlistentry>
 406   </variablelist>
 407
 408   <para>
 409    When specifying a parameter of type <type>boolean</type>, the
 410    <literal>=</literal> <replaceable class="parameter">value</replaceable>
 411    part can be omitted, which is equivalent to
 412    specifying <literal>TRUE</literal>.
 413   </para>
 414  </refsect1>
 415
 416  <refsect1 id="sql-createsubscription-notes" xreflabel="Notes">
 417   <title>Notes</title>
 418
 419   <para>
 420    See <xref linkend="logical-replication-security"/> for details on
 421    how to configure access control between the subscription and the
 422    publication instance.
 423   </para>
 424
 425   <para>
 426    When creating a replication slot (the default behavior), <command>CREATE
 427    SUBSCRIPTION</command> cannot be executed inside a transaction block.
 428   </para>
 429
 430   <para>
 431    Creating a subscription that connects to the same database cluster (for
 432    example, to replicate between databases in the same cluster or to replicate
 433    within the same database) will only succeed if the replication slot is not
 434    created as part of the same command.  Otherwise, the <command>CREATE
 435    SUBSCRIPTION</command> call will hang.  To make this work, create the
 436    replication slot separately (using the
 437    function <function>pg_create_logical_replication_slot</function> with the
 438    plugin name <literal>pgoutput</literal>) and create the subscription using
 439    the parameter <literal>create_slot = false</literal>.  See
 440    <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
 441    for examples. This is an implementation restriction that might be lifted in a
 442    future release.
 443   </para>
 444
 445   <para>
 446    If any table in the publication has a <literal>WHERE</literal> clause, rows
 447    for which the <replaceable class="parameter">expression</replaceable>
 448    evaluates to false or null will not be published. If the subscription has
 449    several publications in which the same table has been published with
 450    different <literal>WHERE</literal> clauses, a row will be published if any
 451    of the expressions (referring to that publish operation) are satisfied. In
 452    the case of different <literal>WHERE</literal> clauses, if one of the
 453    publications has no <literal>WHERE</literal> clause (referring to that
 454    publish operation) or the publication is declared as
 455    <link linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL TABLES</literal></link>
 456    or <link linkend="sql-createpublication-params-for-tables-in-schema"><literal>FOR TABLES IN SCHEMA</literal></link>,
 457    rows are always published regardless of the definition of the other
 458    expressions. If the subscriber is a <productname>PostgreSQL</productname>
 459    version before 15, then any row filtering is ignored during the initial data
 460    synchronization phase. For this case, the user might want to consider
 461    deleting any initially copied data that would be incompatible with
 462    subsequent filtering. Because initial data synchronization does not take
 463    into account the publication
 464    <link linkend="sql-createpublication-params-with-publish"><literal>publish</literal></link>
 465    parameter when copying existing table data, some rows may be copied that
 466    would not be replicated using DML. See
 467    <xref linkend="logical-replication-subscription-examples"/> for examples.
 468   </para>
 469
 470   <para>
 471    Subscriptions having several publications in which the same table has been
 472    published with different column lists are not supported.
 473   </para>
 474
 475   <para>
 476    We allow non-existent publications to be specified so that users can add
 477    those later. This means
 478    <link linkend="catalog-pg-subscription"><structname>pg_subscription</structname></link>
 479    can have non-existent publications.
 480   </para>
 481
 482   <para>
 483    When using a subscription parameter combination of
 484    <literal>copy_data = true</literal> and <literal>origin = NONE</literal>,
 485    the initial sync table data is copied directly from the publisher, meaning
 486    that knowledge of the true origin of that data is not possible. If the
 487    publisher also has subscriptions then the copied table data might have
 488    originated from further upstream. This scenario is detected and a WARNING is
 489    logged to the user, but the warning is only an indication of a potential
 490    problem; it is the user's responsibility to make the necessary checks to
 491    ensure the copied data origins are really as wanted or not.
 492   </para>
 493
 494   <para>
 495    To find which tables might potentially include non-local origins (due to
 496    other subscriptions created on the publisher) try this SQL query:
 497 <programlisting>
 498 # substitute &lt;pub-names&gt; below with your publication name(s) to be queried
 499 SELECT DISTINCT PT.schemaname, PT.tablename
 500 FROM pg_publication_tables PT,
 501      pg_subscription_rel PS
 502      JOIN pg_class C ON (C.oid = PS.srrelid)
 503      JOIN pg_namespace N ON (N.oid = C.relnamespace)
 504 WHERE N.nspname = PT.schemaname AND
 505       C.relname = PT.tablename AND
 506       PT.pubname IN (&lt;pub-names&gt;);
 507 </programlisting></para>
 508
 509  </refsect1>
 510
 511  <refsect1>
 512   <title>Examples</title>
 513
 514   <para>
 515    Create a subscription to a remote server that replicates tables in
 516    the publications <literal>mypublication</literal> and
 517    <literal>insert_only</literal> and starts replicating immediately on
 518    commit:
 519 <programlisting>
 520 CREATE SUBSCRIPTION mysub
 521          CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
 522         PUBLICATION mypublication, insert_only;
 523 </programlisting>
 524   </para>
 525
 526   <para>
 527    Create a subscription to a remote server that replicates tables in
 528    the <literal>insert_only</literal> publication and does not start replicating
 529    until enabled at a later time.
 530 <programlisting>
 531 CREATE SUBSCRIPTION mysub
 532          CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
 533         PUBLICATION insert_only
 534                WITH (enabled = false);
 535 </programlisting></para>
 536  </refsect1>
 537
 538  <refsect1>
 539   <title>Compatibility</title>
 540
 541   <para>
 542    <command>CREATE SUBSCRIPTION</command> is a <productname>PostgreSQL</productname>
 543    extension.
 544   </para>
 545  </refsect1>
 546
 547  <refsect1>
 548   <title>See Also</title>
 549
 550   <simplelist type="inline">
 551    <member><xref linkend="sql-altersubscription"/></member>
 552    <member><xref linkend="sql-dropsubscription"/></member>
 553    <member><xref linkend="sql-createpublication"/></member>
 554    <member><xref linkend="sql-alterpublication"/></member>
 555   </simplelist>
 556  </refsect1>
 557 </refentry>