begriffs open source - ai-pg/blob - full-docs/html/sql-createsubscription.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE SUBSCRIPTION</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createstatistics.html" title="CREATE STATISTICS" /><link rel="next" href="sql-createtable.html" title="CREATE TABLE" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE SUBSCRIPTION</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createstatistics.html" title="CREATE STATISTICS">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createtable.html" title="CREATE TABLE">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATESUBSCRIPTION"><div class="titlepage"></div><a id="id-1.9.3.84.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE SUBSCRIPTION</span></h2><p>CREATE SUBSCRIPTION — define a new subscription</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
   3 CREATE SUBSCRIPTION <em class="replaceable"><code>subscription_name</code></em>
   4     CONNECTION '<em class="replaceable"><code>conninfo</code></em>'
   5     PUBLICATION <em class="replaceable"><code>publication_name</code></em> [, ...]
   6     [ WITH ( <em class="replaceable"><code>subscription_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ]
   7 </pre></div><div class="refsect1" id="id-1.9.3.84.5"><h2>Description</h2><p>
   8    <code class="command">CREATE SUBSCRIPTION</code> adds a new logical-replication
   9    subscription.  The user that creates a subscription becomes the owner
  10    of the subscription. The subscription name must be distinct from the name of
  11    any existing subscription in the current database.
  12   </p><p>
  13    A subscription represents a replication connection to the publisher.
  14    Hence, in addition to adding definitions in the local catalogs, this
  15    command normally creates a replication slot on the publisher.
  16   </p><p>
  17    A logical replication worker will be started to replicate data for the new
  18    subscription at the commit of the transaction where this command is run,
  19    unless the subscription is initially disabled.
  20   </p><p>
  21    To be able to create a subscription, you must have the privileges of
  22    the <code class="literal">pg_create_subscription</code> role, as well as
  23    <code class="literal">CREATE</code> privileges on the current database.
  24   </p><p>
  25    Additional information about subscriptions and logical replication as a
  26    whole is available at <a class="xref" href="logical-replication-subscription.html" title="29.2. Subscription">Section 29.2</a> and
  27    <a class="xref" href="logical-replication.html" title="Chapter 29. Logical Replication">Chapter 29</a>.
  28   </p></div><div class="refsect1" id="id-1.9.3.84.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt id="SQL-CREATESUBSCRIPTION-PARAMS-NAME"><span class="term"><em class="replaceable"><code>subscription_name</code></em></span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-NAME" class="id_link">#</a></dt><dd><p>
  29       The name of the new subscription.
  30      </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-CONNECTION"><span class="term"><code class="literal">CONNECTION '<em class="replaceable"><code>conninfo</code></em>'</code></span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-CONNECTION" class="id_link">#</a></dt><dd><p>
  31       The <span class="application">libpq</span> connection string defining how
  32       to connect to the publisher database.  For details see
  33       <a class="xref" href="libpq-connect.html#LIBPQ-CONNSTRING" title="32.1.1. Connection Strings">Section 32.1.1</a>.
  34      </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-PUBLICATION"><span class="term"><code class="literal">PUBLICATION <em class="replaceable"><code>publication_name</code></em> [, ...]</code></span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-PUBLICATION" class="id_link">#</a></dt><dd><p>
  35       Names of the publications on the publisher to subscribe to.
  36      </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH"><span class="term"><code class="literal">WITH ( <em class="replaceable"><code>subscription_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )</code></span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH" class="id_link">#</a></dt><dd><p>
  37       This clause specifies optional parameters for a subscription.
  38      </p><p>
  39       The following parameters control what happens during subscription creation:
  40
  41       </p><div class="variablelist"><dl class="variablelist"><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-CONNECT"><span class="term"><code class="literal">connect</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-CONNECT" class="id_link">#</a></dt><dd><p>
  42           Specifies whether the <code class="command">CREATE SUBSCRIPTION</code>
  43           command should connect to the publisher at all.  The default
  44           is <code class="literal">true</code>.  Setting this to
  45           <code class="literal">false</code> will force the values of
  46           <code class="literal">create_slot</code>, <code class="literal">enabled</code> and
  47           <code class="literal">copy_data</code> to <code class="literal">false</code>.
  48           (You cannot combine setting <code class="literal">connect</code>
  49           to <code class="literal">false</code> with
  50           setting <code class="literal">create_slot</code>, <code class="literal">enabled</code>,
  51           or <code class="literal">copy_data</code> to <code class="literal">true</code>.)
  52          </p><p>
  53           Since no connection is made when this option is
  54           <code class="literal">false</code>, no tables are subscribed. To initiate
  55           replication, you must manually create the replication slot, enable
  56           the failover if required, enable the subscription, and refresh the
  57           subscription. See
  58           <a class="xref" href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES-DEFERRED-SLOT" title="29.2.3. Examples: Deferred Replication Slot Creation">Section 29.2.3</a>
  59           for examples.
  60          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-CREATE-SLOT"><span class="term"><code class="literal">create_slot</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-CREATE-SLOT" class="id_link">#</a></dt><dd><p>
  61           Specifies whether the command should create the replication slot on
  62           the publisher.  The default is <code class="literal">true</code>.
  63          </p><p>
  64           If set to <code class="literal">false</code>, you are responsible for
  65           creating the publisher's slot in some other way. See
  66           <a class="xref" href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES-DEFERRED-SLOT" title="29.2.3. Examples: Deferred Replication Slot Creation">Section 29.2.3</a>
  67           for examples.
  68          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-ENABLED"><span class="term"><code class="literal">enabled</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-ENABLED" class="id_link">#</a></dt><dd><p>
  69           Specifies whether the subscription should be actively replicating
  70           or whether it should just be set up but not started yet.  The default
  71           is <code class="literal">true</code>.
  72          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME"><span class="term"><code class="literal">slot_name</code> (<code class="type">string</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME" class="id_link">#</a></dt><dd><p>
  73           Name of the publisher's replication slot to use.  The default is
  74           to use the name of the subscription for the slot name.
  75          </p><p>
  76           Setting <code class="literal">slot_name</code> to <code class="literal">NONE</code>
  77           means there will be no replication slot associated with the
  78           subscription. Such subscriptions must also have both
  79           <code class="literal">enabled</code> and <code class="literal">create_slot</code> set to
  80           <code class="literal">false</code>.  Use this when you will be creating the
  81           replication slot later manually. See
  82           <a class="xref" href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES-DEFERRED-SLOT" title="29.2.3. Examples: Deferred Replication Slot Creation">Section 29.2.3</a>
  83           for examples.
  84          </p><p>
  85           When setting <code class="literal">slot_name</code> to a valid name and
  86           <code class="literal">create_slot</code> to <code class="literal">false</code>, the
  87           <code class="literal">failover</code> property value of the named slot may
  88           differ from the counterpart <code class="literal">failover</code> parameter
  89           specified in the subscription. Always ensure the slot property
  90           <code class="literal">failover</code> matches the counterpart parameter of the
  91           subscription and vice versa. Otherwise, the slot on the publisher may
  92           behave differently from what these subscription options say: for
  93           example, the slot on the publisher could either be synced to the
  94           standbys even when the subscription's <code class="literal">failover</code>
  95           option is disabled or could be disabled for sync even when the
  96           subscription's <code class="literal">failover</code> option is enabled.
  97          </p></dd></dl></div><p>
  98      </p><p>
  99       The following parameters control the subscription's replication
 100       behavior after it has been created:
 101
 102       </p><div class="variablelist"><dl class="variablelist"><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-BINARY"><span class="term"><code class="literal">binary</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-BINARY" class="id_link">#</a></dt><dd><p>
 103           Specifies whether the subscription will request the publisher to send
 104           the data in binary format (as opposed to text). The default is
 105           <code class="literal">false</code>. Any initial table synchronization copy
 106           (see <code class="literal">copy_data</code>) also uses the same format. Binary
 107           format can be faster than the text format, but it is less portable
 108           across machine architectures and <span class="productname">PostgreSQL</span>
 109           versions. Binary format is very data type specific; for example, it
 110           will not allow copying from a <code class="type">smallint</code> column to an
 111           <code class="type">integer</code> column, even though that would work fine in text
 112           format. Even when this option is enabled, only data types having binary
 113           send and receive functions will be transferred in binary. Note that
 114           the initial synchronization requires all data types to have binary
 115           send and receive functions, otherwise the synchronization will fail
 116           (see <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a> for more about send/receive
 117           functions).
 118          </p><p>
 119           When doing cross-version replication, it could be that the
 120           publisher has a binary send function for some data type, but the
 121           subscriber lacks a binary receive function for that type.  In
 122           such a case, data transfer will fail, and
 123           the <code class="literal">binary</code> option cannot be used.
 124          </p><p>
 125           If the publisher is a <span class="productname">PostgreSQL</span> version
 126           before 16, then any initial table synchronization will use text format
 127           even if <code class="literal">binary = true</code>.
 128          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-COPY-DATA"><span class="term"><code class="literal">copy_data</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-COPY-DATA" class="id_link">#</a></dt><dd><p>
 129           Specifies whether to copy pre-existing data in the publications
 130           that are being subscribed to when the replication starts.
 131           The default is <code class="literal">true</code>.
 132          </p><p>
 133           If the publications contain <code class="literal">WHERE</code> clauses, it
 134           will affect what data is copied. Refer to the
 135           <a class="xref" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-NOTES" title="Notes">Notes</a> for details.
 136          </p><p>
 137           See <a class="xref" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-NOTES" title="Notes">Notes</a> for details of how
 138           <code class="literal">copy_data = true</code> can interact with the
 139           <code class="literal">origin</code> parameter.
 140          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-STREAMING"><span class="term"><code class="literal">streaming</code> (<code class="type">enum</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-STREAMING" class="id_link">#</a></dt><dd><p>
 141           Specifies whether to enable streaming of in-progress transactions
 142           for this subscription.  The default value is <code class="literal">parallel</code>,
 143           meaning incoming changes are directly applied via one of the parallel
 144           apply workers, if available. If no parallel apply worker is free to
 145           handle streaming transactions then the changes are written to
 146           temporary files and applied after the transaction is committed. Note
 147           that if an error happens in a parallel apply worker, the finish LSN
 148           of the remote transaction might not be reported in the server log.
 149          </p><div class="caution"><h3 class="title">Caution</h3><p>
 150            There is a risk of deadlock when the schemas of the publisher and
 151            subscriber differ, although such cases are rare. The apply worker
 152            is equipped to retry these transactions automatically.
 153           </p></div><p>
 154           If set to <code class="literal">on</code>, the incoming changes are written to
 155           temporary files and then applied only after the transaction is
 156           committed on the publisher and received by the subscriber.
 157          </p><p>
 158           If set to <code class="literal">off</code>, all transactions are fully decoded
 159           on the publisher and only then sent to the subscriber as a whole.
 160          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-SYNCHRONOUS-COMMIT"><span class="term"><code class="literal">synchronous_commit</code> (<code class="type">enum</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SYNCHRONOUS-COMMIT" class="id_link">#</a></dt><dd><p>
 161           The value of this parameter overrides the
 162           <a class="xref" href="runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT">synchronous_commit</a> setting within this
 163           subscription's apply worker processes.  The default value
 164           is <code class="literal">off</code>.
 165          </p><p>
 166           It is safe to use <code class="literal">off</code> for logical replication:
 167           If the subscriber loses transactions because of missing
 168           synchronization, the data will be sent again from the publisher.
 169          </p><p>
 170           A different setting might be appropriate when doing synchronous
 171           logical replication.  The logical replication workers report the
 172           positions of writes and flushes to the publisher, and when using
 173           synchronous replication, the publisher will wait for the actual
 174           flush.  This means that setting
 175           <code class="literal">synchronous_commit</code> for the subscriber to
 176           <code class="literal">off</code> when the subscription is used for
 177           synchronous replication might increase the latency for
 178           <code class="command">COMMIT</code> on the publisher.  In this scenario, it
 179           can be advantageous to set <code class="literal">synchronous_commit</code>
 180           to <code class="literal">local</code> or higher.
 181          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE"><span class="term"><code class="literal">two_phase</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE" class="id_link">#</a></dt><dd><p>
 182           Specifies whether two-phase commit is enabled for this subscription.
 183           The default is <code class="literal">false</code>.
 184          </p><p>
 185           When two-phase commit is enabled, prepared transactions are sent
 186           to the subscriber at the time of <code class="command">PREPARE
 187           TRANSACTION</code>, and are processed as two-phase
 188           transactions on the subscriber too.  Otherwise, prepared
 189           transactions are sent to the subscriber only when committed, and
 190           are then processed immediately by the subscriber.
 191          </p><p>
 192           The implementation of two-phase commit requires that replication
 193           has successfully finished the initial table synchronization
 194           phase. So even when <code class="literal">two_phase</code> is enabled for a
 195           subscription, the internal two-phase state remains
 196           temporarily <span class="quote">“<span class="quote">pending</span>”</span> until the initialization phase
 197           completes. See column <code class="structfield">subtwophasestate</code>
 198           of <a class="link" href="catalog-pg-subscription.html" title="52.54. pg_subscription"><code class="structname">pg_subscription</code></a>
 199           to know the actual two-phase state.
 200          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-DISABLE-ON-ERROR"><span class="term"><code class="literal">disable_on_error</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-DISABLE-ON-ERROR" class="id_link">#</a></dt><dd><p>
 201           Specifies whether the subscription should be automatically disabled
 202           if any errors are detected by subscription workers during data
 203           replication from the publisher. The default is
 204           <code class="literal">false</code>.
 205          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-PASSWORD-REQUIRED"><span class="term"><code class="literal">password_required</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-PASSWORD-REQUIRED" class="id_link">#</a></dt><dd><p>
 206           If set to <code class="literal">true</code>, connections to the publisher made
 207           as a result of this subscription must use password authentication
 208           and the password must be specified as a part of the connection
 209           string. This setting is ignored when the subscription is owned by a
 210           superuser.  The default is <code class="literal">true</code>. Only superusers
 211           can set this value to <code class="literal">false</code>.
 212          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-RUN-AS-OWNER"><span class="term"><code class="literal">run_as_owner</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-RUN-AS-OWNER" class="id_link">#</a></dt><dd><p>
 213           If true, all replication actions are performed as the subscription
 214           owner. If false, replication workers will perform actions on each
 215           table as the owner of that table. The latter configuration is
 216           generally much more secure; for details, see
 217           <a class="xref" href="logical-replication-security.html" title="29.11. Security">Section 29.11</a>.
 218           The default is <code class="literal">false</code>.
 219          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-ORIGIN"><span class="term"><code class="literal">origin</code> (<code class="type">string</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-ORIGIN" class="id_link">#</a></dt><dd><p>
 220           Specifies whether the subscription will request the publisher to only
 221           send changes that don't have an origin or send changes regardless of
 222           origin. Setting <code class="literal">origin</code> to <code class="literal">none</code>
 223           means that the subscription will request the publisher to only send
 224           changes that don't have an origin. Setting <code class="literal">origin</code>
 225           to <code class="literal">any</code> means that the publisher sends changes
 226           regardless of their origin. The default is <code class="literal">any</code>.
 227          </p><p>
 228           See <a class="xref" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-NOTES" title="Notes">Notes</a> for details of how
 229           <code class="literal">copy_data = true</code> can interact with the
 230           <code class="literal">origin</code> parameter.
 231          </p></dd><dt id="SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER"><span class="term"><code class="literal">failover</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER" class="id_link">#</a></dt><dd><p>
 232           Specifies whether the replication slots associated with the subscription
 233           are enabled to be synced to the standbys so that logical
 234           replication can be resumed from the new primary after failover.
 235           The default is <code class="literal">false</code>.
 236          </p></dd></dl></div></dd></dl></div><p>
 237    When specifying a parameter of type <code class="type">boolean</code>, the
 238    <code class="literal">=</code> <em class="replaceable"><code>value</code></em>
 239    part can be omitted, which is equivalent to
 240    specifying <code class="literal">TRUE</code>.
 241   </p></div><div class="refsect1" id="SQL-CREATESUBSCRIPTION-NOTES"><h2>Notes</h2><p>
 242    See <a class="xref" href="logical-replication-security.html" title="29.11. Security">Section 29.11</a> for details on
 243    how to configure access control between the subscription and the
 244    publication instance.
 245   </p><p>
 246    When creating a replication slot (the default behavior), <code class="command">CREATE
 247    SUBSCRIPTION</code> cannot be executed inside a transaction block.
 248   </p><p>
 249    Creating a subscription that connects to the same database cluster (for
 250    example, to replicate between databases in the same cluster or to replicate
 251    within the same database) will only succeed if the replication slot is not
 252    created as part of the same command.  Otherwise, the <code class="command">CREATE
 253    SUBSCRIPTION</code> call will hang.  To make this work, create the
 254    replication slot separately (using the
 255    function <code class="function">pg_create_logical_replication_slot</code> with the
 256    plugin name <code class="literal">pgoutput</code>) and create the subscription using
 257    the parameter <code class="literal">create_slot = false</code>.  See
 258    <a class="xref" href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES-DEFERRED-SLOT" title="29.2.3. Examples: Deferred Replication Slot Creation">Section 29.2.3</a>
 259    for examples. This is an implementation restriction that might be lifted in a
 260    future release.
 261   </p><p>
 262    If any table in the publication has a <code class="literal">WHERE</code> clause, rows
 263    for which the <em class="replaceable"><code>expression</code></em>
 264    evaluates to <code class="literal">false</code> or <code class="literal">NULL</code> will not be
 265    published. If the subscription has
 266    several publications in which the same table has been published with
 267    different <code class="literal">WHERE</code> clauses, a row will be published if any
 268    of the expressions (referring to that publish operation) are satisfied. In
 269    the case of different <code class="literal">WHERE</code> clauses, if one of the
 270    publications has no <code class="literal">WHERE</code> clause (referring to that
 271    publish operation) or the publication is declared as
 272    <a class="link" href="sql-createpublication.html#SQL-CREATEPUBLICATION-PARAMS-FOR-ALL-TABLES"><code class="literal">FOR ALL TABLES</code></a>
 273    or <a class="link" href="sql-createpublication.html#SQL-CREATEPUBLICATION-PARAMS-FOR-TABLES-IN-SCHEMA"><code class="literal">FOR TABLES IN SCHEMA</code></a>,
 274    rows are always published regardless of the definition of the other
 275    expressions. If the subscriber is a <span class="productname">PostgreSQL</span>
 276    version before 15, then any row filtering is ignored during the initial data
 277    synchronization phase. For this case, the user might want to consider
 278    deleting any initially copied data that would be incompatible with
 279    subsequent filtering. Because initial data synchronization does not take
 280    into account the publication
 281    <a class="link" href="sql-createpublication.html#SQL-CREATEPUBLICATION-PARAMS-WITH-PUBLISH"><code class="literal">publish</code></a>
 282    parameter when copying existing table data, some rows may be copied that
 283    would not be replicated using DML. See
 284    <a class="xref" href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES" title="29.2.2. Examples: Set Up Logical Replication">Section 29.2.2</a> for examples.
 285   </p><p>
 286    Subscriptions having several publications in which the same table has been
 287    published with different column lists are not supported.
 288   </p><p>
 289    We allow non-existent publications to be specified so that users can add
 290    those later. This means
 291    <a class="link" href="catalog-pg-subscription.html" title="52.54. pg_subscription"><code class="structname">pg_subscription</code></a>
 292    can have non-existent publications.
 293   </p><p>
 294    When using a subscription parameter combination of
 295    <code class="literal">copy_data = true</code> and <code class="literal">origin = NONE</code>,
 296    the initial sync table data is copied directly from the publisher, meaning
 297    that knowledge of the true origin of that data is not possible. If the
 298    publisher also has subscriptions then the copied table data might have
 299    originated from further upstream. This scenario is detected and a WARNING is
 300    logged to the user, but the warning is only an indication of a potential
 301    problem; it is the user's responsibility to make the necessary checks to
 302    ensure the copied data origins are really as wanted or not.
 303   </p><p>
 304    To find which tables might potentially include non-local origins (due to
 305    other subscriptions created on the publisher) try this SQL query:
 306 </p><pre class="programlisting">
 307 # substitute &lt;pub-names&gt; below with your publication name(s) to be queried
 308 SELECT DISTINCT PT.schemaname, PT.tablename
 309 FROM pg_publication_tables PT
 310      JOIN pg_class C ON (C.relname = PT.tablename)
 311      JOIN pg_namespace N ON (N.nspname = PT.schemaname),
 312      pg_subscription_rel PS
 313 WHERE C.relnamespace = N.oid AND
 314       (PS.srrelid = C.oid OR
 315       C.oid IN (SELECT relid FROM pg_partition_ancestors(PS.srrelid) UNION
 316                 SELECT relid FROM pg_partition_tree(PS.srrelid))) AND
 317       PT.pubname IN (&lt;pub-names&gt;);
 318 </pre></div><div class="refsect1" id="id-1.9.3.84.8"><h2>Examples</h2><p>
 319    Create a subscription to a remote server that replicates tables in
 320    the publications <code class="literal">mypublication</code> and
 321    <code class="literal">insert_only</code> and starts replicating immediately on
 322    commit:
 323 </p><pre class="programlisting">
 324 CREATE SUBSCRIPTION mysub
 325          CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
 326         PUBLICATION mypublication, insert_only;
 327 </pre><p>
 328   </p><p>
 329    Create a subscription to a remote server that replicates tables in
 330    the <code class="literal">insert_only</code> publication and does not start replicating
 331    until enabled at a later time.
 332 </p><pre class="programlisting">
 333 CREATE SUBSCRIPTION mysub
 334          CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
 335         PUBLICATION insert_only
 336                WITH (enabled = false);
 337 </pre></div><div class="refsect1" id="id-1.9.3.84.9"><h2>Compatibility</h2><p>
 338    <code class="command">CREATE SUBSCRIPTION</code> is a <span class="productname">PostgreSQL</span>
 339    extension.
 340   </p></div><div class="refsect1" id="id-1.9.3.84.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-altersubscription.html" title="ALTER SUBSCRIPTION"><span class="refentrytitle">ALTER SUBSCRIPTION</span></a>, <a class="xref" href="sql-dropsubscription.html" title="DROP SUBSCRIPTION"><span class="refentrytitle">DROP SUBSCRIPTION</span></a>, <a class="xref" href="sql-createpublication.html" title="CREATE PUBLICATION"><span class="refentrytitle">CREATE PUBLICATION</span></a>, <a class="xref" href="sql-alterpublication.html" title="ALTER PUBLICATION"><span class="refentrytitle">ALTER PUBLICATION</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createstatistics.html" title="CREATE STATISTICS">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createtable.html" title="CREATE TABLE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE STATISTICS </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE TABLE</td></tr></table></div></body></html>