| 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 failover if required, enable the subscription, and refresh the |
| 133 |
subscription. See |
| 134 |
<xref linkend="logical-replication-subscription-examples-deferred-slot"/> |
| 135 |
for examples. |
| 136 |
</para> |
| 137 |
</listitem> |
| 138 |
</varlistentry> |
| 139 |
|
| 140 |
<varlistentry id="sql-createsubscription-params-with-create-slot"> |
| 141 |
<term><literal>create_slot</literal> (<type>boolean</type>)</term> |
| 142 |
<listitem> |
| 143 |
<para> |
| 144 |
Specifies whether the command should create the replication slot on |
| 145 |
the publisher. The default is <literal>true</literal>. |
| 146 |
</para> |
| 147 |
<para> |
| 148 |
If set to <literal>false</literal>, you are responsible for |
| 149 |
creating the publisher's slot in some other way. See |
| 150 |
<xref linkend="logical-replication-subscription-examples-deferred-slot"/> |
| 151 |
for examples. |
| 152 |
</para> |
| 153 |
</listitem> |
| 154 |
</varlistentry> |
| 155 |
|
| 156 |
<varlistentry id="sql-createsubscription-params-with-enabled"> |
| 157 |
<term><literal>enabled</literal> (<type>boolean</type>)</term> |
| 158 |
<listitem> |
| 159 |
<para> |
| 160 |
Specifies whether the subscription should be actively replicating |
| 161 |
or whether it should just be set up but not started yet. The default |
| 162 |
is <literal>true</literal>. |
| 163 |
</para> |
| 164 |
</listitem> |
| 165 |
</varlistentry> |
| 166 |
|
| 167 |
<varlistentry id="sql-createsubscription-params-with-slot-name"> |
| 168 |
<term><literal>slot_name</literal> (<type>string</type>)</term> |
| 169 |
<listitem> |
| 170 |
<para> |
| 171 |
Name of the publisher's replication slot to use. The default is |
| 172 |
to use the name of the subscription for the slot name. |
| 173 |
</para> |
| 174 |
|
| 175 |
<para> |
| 176 |
Setting <literal>slot_name</literal> to <literal>NONE</literal> |
| 177 |
means there will be no replication slot associated with the |
| 178 |
subscription. Such subscriptions must also have both |
| 179 |
<literal>enabled</literal> and <literal>create_slot</literal> set to |
| 180 |
<literal>false</literal>. Use this when you will be creating the |
| 181 |
replication slot later manually. See |
| 182 |
<xref linkend="logical-replication-subscription-examples-deferred-slot"/> |
| 183 |
for examples. |
| 184 |
</para> |
| 185 |
|
| 186 |
<para> |
| 187 |
When setting <literal>slot_name</literal> to a valid name and |
| 188 |
<literal>create_slot</literal> to <literal>false</literal>, the |
| 189 |
<literal>failover</literal> property value of the named slot may |
| 190 |
differ from the counterpart <literal>failover</literal> parameter |
| 191 |
specified in the subscription. Always ensure the slot property |
| 192 |
<literal>failover</literal> matches the counterpart parameter of the |
| 193 |
subscription and vice versa. Otherwise, the slot on the publisher may |
| 194 |
behave differently from what these subscription options say: for |
| 195 |
example, the slot on the publisher could either be synced to the |
| 196 |
standbys even when the subscription's <literal>failover</literal> |
| 197 |
option is disabled or could be disabled for sync even when the |
| 198 |
subscription's <literal>failover</literal> option is enabled. |
| 199 |
</para> |
| 200 |
</listitem> |
| 201 |
</varlistentry> |
| 202 |
</variablelist> |
| 203 |
</para> |
| 204 |
|
| 205 |
<para> |
| 206 |
The following parameters control the subscription's replication |
| 207 |
behavior after it has been created: |
| 208 |
|
| 209 |
<variablelist> |
| 210 |
|
| 211 |
<varlistentry id="sql-createsubscription-params-with-binary"> |
| 212 |
<term><literal>binary</literal> (<type>boolean</type>)</term> |
| 213 |
<listitem> |
| 214 |
<para> |
| 215 |
Specifies whether the subscription will request the publisher to send |
| 216 |
the data in binary format (as opposed to text). The default is |
| 217 |
<literal>false</literal>. Any initial table synchronization copy |
| 218 |
(see <literal>copy_data</literal>) also uses the same format. Binary |
| 219 |
format can be faster than the text format, but it is less portable |
| 220 |
across machine architectures and <productname>PostgreSQL</productname> |
| 221 |
versions. Binary format is very data type specific; for example, it |
| 222 |
will not allow copying from a <type>smallint</type> column to an |
| 223 |
<type>integer</type> column, even though that would work fine in text |
| 224 |
format. Even when this option is enabled, only data types having binary |
| 225 |
send and receive functions will be transferred in binary. Note that |
| 226 |
the initial synchronization requires all data types to have binary |
| 227 |
send and receive functions, otherwise the synchronization will fail |
| 228 |
(see <xref linkend="sql-createtype"/> for more about send/receive |
| 229 |
functions). |
| 230 |
</para> |
| 231 |
|
| 232 |
<para> |
| 233 |
When doing cross-version replication, it could be that the |
| 234 |
publisher has a binary send function for some data type, but the |
| 235 |
subscriber lacks a binary receive function for that type. In |
| 236 |
such a case, data transfer will fail, and |
| 237 |
the <literal>binary</literal> option cannot be used. |
| 238 |
</para> |
| 239 |
|
| 240 |
<para> |
| 241 |
If the publisher is a <productname>PostgreSQL</productname> version |
| 242 |
before 16, then any initial table synchronization will use text format |
| 243 |
even if <literal>binary = true</literal>. |
| 244 |
</para> |
| 245 |
</listitem> |
| 246 |
</varlistentry> |
| 247 |
|
| 248 |
<varlistentry id="sql-createsubscription-params-with-copy-data"> |
| 249 |
<term><literal>copy_data</literal> (<type>boolean</type>)</term> |
| 250 |
<listitem> |
| 251 |
<para> |
| 252 |
Specifies whether to copy pre-existing data in the publications |
| 253 |
that are being subscribed to when the replication starts. |
| 254 |
The default is <literal>true</literal>. |
| 255 |
</para> |
| 256 |
<para> |
| 257 |
If the publications contain <literal>WHERE</literal> clauses, it |
| 258 |
will affect what data is copied. Refer to the |
| 259 |
<xref linkend="sql-createsubscription-notes" /> for details. |
| 260 |
</para> |
| 261 |
<para> |
| 262 |
See <xref linkend="sql-createsubscription-notes"/> for details of how |
| 263 |
<literal>copy_data = true</literal> can interact with the |
| 264 |
<literal>origin</literal> parameter. |
| 265 |
</para> |
| 266 |
</listitem> |
| 267 |
</varlistentry> |
| 268 |
|
| 269 |
<varlistentry id="sql-createsubscription-params-with-streaming"> |
| 270 |
<term><literal>streaming</literal> (<type>enum</type>)</term> |
| 271 |
<listitem> |
| 272 |
<para> |
| 273 |
Specifies whether to enable streaming of in-progress transactions |
| 274 |
for this subscription. The default value is <literal>parallel</literal>, |
| 275 |
meaning incoming changes are directly applied via one of the parallel |
| 276 |
apply workers, if available. If no parallel apply worker is free to |
| 277 |
handle streaming transactions then the changes are written to |
| 278 |
temporary files and applied after the transaction is committed. Note |
| 279 |
that if an error happens in a parallel apply worker, the finish LSN |
| 280 |
of the remote transaction might not be reported in the server log. |
| 281 |
</para> |
| 282 |
|
| 283 |
<caution> |
| 284 |
<para> |
| 285 |
There is a risk of deadlock when the schemas of the publisher and |
| 286 |
subscriber differ, although such cases are rare. The apply worker |
| 287 |
is equipped to retry these transactions automatically. |
| 288 |
</para> |
| 289 |
</caution> |
| 290 |
|
| 291 |
<para> |
| 292 |
If set to <literal>on</literal>, the incoming changes are written to |
| 293 |
temporary files and then applied only after the transaction is |
| 294 |
committed on the publisher and received by the subscriber. |
| 295 |
</para> |
| 296 |
|
| 297 |
<para> |
| 298 |
If set to <literal>off</literal>, all transactions are fully decoded |
| 299 |
on the publisher and only then sent to the subscriber as a whole. |
| 300 |
</para> |
| 301 |
</listitem> |
| 302 |
</varlistentry> |
| 303 |
|
| 304 |
<varlistentry id="sql-createsubscription-params-with-synchronous-commit"> |
| 305 |
<term><literal>synchronous_commit</literal> (<type>enum</type>)</term> |
| 306 |
<listitem> |
| 307 |
<para> |
| 308 |
The value of this parameter overrides the |
| 309 |
<xref linkend="guc-synchronous-commit"/> setting within this |
| 310 |
subscription's apply worker processes. The default value |
| 311 |
is <literal>off</literal>. |
| 312 |
</para> |
| 313 |
|
| 314 |
<para> |
| 315 |
It is safe to use <literal>off</literal> for logical replication: |
| 316 |
If the subscriber loses transactions because of missing |
| 317 |
synchronization, the data will be sent again from the publisher. |
| 318 |
</para> |
| 319 |
|
| 320 |
<para> |
| 321 |
A different setting might be appropriate when doing synchronous |
| 322 |
logical replication. The logical replication workers report the |
| 323 |
positions of writes and flushes to the publisher, and when using |
| 324 |
synchronous replication, the publisher will wait for the actual |
| 325 |
flush. This means that setting |
| 326 |
<literal>synchronous_commit</literal> for the subscriber to |
| 327 |
<literal>off</literal> when the subscription is used for |
| 328 |
synchronous replication might increase the latency for |
| 329 |
<command>COMMIT</command> on the publisher. In this scenario, it |
| 330 |
can be advantageous to set <literal>synchronous_commit</literal> |
| 331 |
to <literal>local</literal> or higher. |
| 332 |
</para> |
| 333 |
</listitem> |
| 334 |
</varlistentry> |
| 335 |
|
| 336 |
<varlistentry id="sql-createsubscription-params-with-two-phase"> |
| 337 |
<term><literal>two_phase</literal> (<type>boolean</type>)</term> |
| 338 |
<listitem> |
| 339 |
<para> |
| 340 |
Specifies whether two-phase commit is enabled for this subscription. |
| 341 |
The default is <literal>false</literal>. |
| 342 |
</para> |
| 343 |
|
| 344 |
<para> |
| 345 |
When two-phase commit is enabled, prepared transactions are sent |
| 346 |
to the subscriber at the time of <command>PREPARE |
| 347 |
TRANSACTION</command>, and are processed as two-phase |
| 348 |
transactions on the subscriber too. Otherwise, prepared |
| 349 |
transactions are sent to the subscriber only when committed, and |
| 350 |
are then processed immediately by the subscriber. |
| 351 |
</para> |
| 352 |
|
| 353 |
<para> |
| 354 |
The implementation of two-phase commit requires that replication |
| 355 |
has successfully finished the initial table synchronization |
| 356 |
phase. So even when <literal>two_phase</literal> is enabled for a |
| 357 |
subscription, the internal two-phase state remains |
| 358 |
temporarily <quote>pending</quote> until the initialization phase |
| 359 |
completes. See column <structfield>subtwophasestate</structfield> |
| 360 |
of <link linkend="catalog-pg-subscription"><structname>pg_subscription</structname></link> |
| 361 |
to know the actual two-phase state. |
| 362 |
</para> |
| 363 |
|
| 364 |
</listitem> |
| 365 |
</varlistentry> |
| 366 |
|
| 367 |
<varlistentry id="sql-createsubscription-params-with-disable-on-error"> |
| 368 |
<term><literal>disable_on_error</literal> (<type>boolean</type>)</term> |
| 369 |
<listitem> |
| 370 |
<para> |
| 371 |
Specifies whether the subscription should be automatically disabled |
| 372 |
if any errors are detected by subscription workers during data |
| 373 |
replication from the publisher. The default is |
| 374 |
<literal>false</literal>. |
| 375 |
</para> |
| 376 |
</listitem> |
| 377 |
</varlistentry> |
| 378 |
|
| 379 |
<varlistentry id="sql-createsubscription-params-with-password-required"> |
| 380 |
<term><literal>password_required</literal> (<type>boolean</type>)</term> |
| 381 |
<listitem> |
| 382 |
<para> |
| 383 |
If set to <literal>true</literal>, connections to the publisher made |
| 384 |
as a result of this subscription must use password authentication |
| 385 |
and the password must be specified as a part of the connection |
| 386 |
string. This setting is ignored when the subscription is owned by a |
| 387 |
superuser. The default is <literal>true</literal>. Only superusers |
| 388 |
can set this value to <literal>false</literal>. |
| 389 |
</para> |
| 390 |
</listitem> |
| 391 |
</varlistentry> |
| 392 |
|
| 393 |
<varlistentry id="sql-createsubscription-params-with-run-as-owner"> |
| 394 |
<term><literal>run_as_owner</literal> (<type>boolean</type>)</term> |
| 395 |
<listitem> |
| 396 |
<para> |
| 397 |
If true, all replication actions are performed as the subscription |
| 398 |
owner. If false, replication workers will perform actions on each |
| 399 |
table as the owner of that table. The latter configuration is |
| 400 |
generally much more secure; for details, see |
| 401 |
<xref linkend="logical-replication-security" />. |
| 402 |
The default is <literal>false</literal>. |
| 403 |
</para> |
| 404 |
</listitem> |
| 405 |
</varlistentry> |
| 406 |
|
| 407 |
<varlistentry id="sql-createsubscription-params-with-origin"> |
| 408 |
<term><literal>origin</literal> (<type>string</type>)</term> |
| 409 |
<listitem> |
| 410 |
<para> |
| 411 |
Specifies whether the subscription will request the publisher to only |
| 412 |
send changes that don't have an origin or send changes regardless of |
| 413 |
origin. Setting <literal>origin</literal> to <literal>none</literal> |
| 414 |
means that the subscription will request the publisher to only send |
| 415 |
changes that don't have an origin. Setting <literal>origin</literal> |
| 416 |
to <literal>any</literal> means that the publisher sends changes |
| 417 |
regardless of their origin. The default is <literal>any</literal>. |
| 418 |
</para> |
| 419 |
<para> |
| 420 |
See <xref linkend="sql-createsubscription-notes"/> for details of how |
| 421 |
<literal>copy_data = true</literal> can interact with the |
| 422 |
<literal>origin</literal> parameter. |
| 423 |
</para> |
| 424 |
</listitem> |
| 425 |
</varlistentry> |
| 426 |
|
| 427 |
<varlistentry id="sql-createsubscription-params-with-failover"> |
| 428 |
<term><literal>failover</literal> (<type>boolean</type>)</term> |
| 429 |
<listitem> |
| 430 |
<para> |
| 431 |
Specifies whether the replication slots associated with the subscription |
| 432 |
are enabled to be synced to the standbys so that logical |
| 433 |
replication can be resumed from the new primary after failover. |
| 434 |
The default is <literal>false</literal>. |
| 435 |
</para> |
| 436 |
</listitem> |
| 437 |
</varlistentry> |
| 438 |
</variablelist></para> |
| 439 |
|
| 440 |
</listitem> |
| 441 |
</varlistentry> |
| 442 |
</variablelist> |
| 443 |
|
| 444 |
<para> |
| 445 |
When specifying a parameter of type <type>boolean</type>, the |
| 446 |
<literal>=</literal> <replaceable class="parameter">value</replaceable> |
| 447 |
part can be omitted, which is equivalent to |
| 448 |
specifying <literal>TRUE</literal>. |
| 449 |
</para> |
| 450 |
</refsect1> |
| 451 |
|
| 452 |
<refsect1 id="sql-createsubscription-notes" xreflabel="Notes"> |
| 453 |
<title>Notes</title> |
| 454 |
|
| 455 |
<para> |
| 456 |
See <xref linkend="logical-replication-security"/> for details on |
| 457 |
how to configure access control between the subscription and the |
| 458 |
publication instance. |
| 459 |
</para> |
| 460 |
|
| 461 |
<para> |
| 462 |
When creating a replication slot (the default behavior), <command>CREATE |
| 463 |
SUBSCRIPTION</command> cannot be executed inside a transaction block. |
| 464 |
</para> |
| 465 |
|
| 466 |
<para> |
| 467 |
Creating a subscription that connects to the same database cluster (for |
| 468 |
example, to replicate between databases in the same cluster or to replicate |
| 469 |
within the same database) will only succeed if the replication slot is not |
| 470 |
created as part of the same command. Otherwise, the <command>CREATE |
| 471 |
SUBSCRIPTION</command> call will hang. To make this work, create the |
| 472 |
replication slot separately (using the |
| 473 |
function <function>pg_create_logical_replication_slot</function> with the |
| 474 |
plugin name <literal>pgoutput</literal>) and create the subscription using |
| 475 |
the parameter <literal>create_slot = false</literal>. See |
| 476 |
<xref linkend="logical-replication-subscription-examples-deferred-slot"/> |
| 477 |
for examples. This is an implementation restriction that might be lifted in a |
| 478 |
future release. |
| 479 |
</para> |
| 480 |
|
| 481 |
<para> |
| 482 |
If any table in the publication has a <literal>WHERE</literal> clause, rows |
| 483 |
for which the <replaceable class="parameter">expression</replaceable> |
| 484 |
evaluates to <literal>false</literal> or <literal>NULL</literal> will not be |
| 485 |
published. If the subscription has |
| 486 |
several publications in which the same table has been published with |
| 487 |
different <literal>WHERE</literal> clauses, a row will be published if any |
| 488 |
of the expressions (referring to that publish operation) are satisfied. In |
| 489 |
the case of different <literal>WHERE</literal> clauses, if one of the |
| 490 |
publications has no <literal>WHERE</literal> clause (referring to that |
| 491 |
publish operation) or the publication is declared as |
| 492 |
<link linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL TABLES</literal></link> |
| 493 |
or <link linkend="sql-createpublication-params-for-tables-in-schema"><literal>FOR TABLES IN SCHEMA</literal></link>, |
| 494 |
rows are always published regardless of the definition of the other |
| 495 |
expressions. If the subscriber is a <productname>PostgreSQL</productname> |
| 496 |
version before 15, then any row filtering is ignored during the initial data |
| 497 |
synchronization phase. For this case, the user might want to consider |
| 498 |
deleting any initially copied data that would be incompatible with |
| 499 |
subsequent filtering. Because initial data synchronization does not take |
| 500 |
into account the publication |
| 501 |
<link linkend="sql-createpublication-params-with-publish"><literal>publish</literal></link> |
| 502 |
parameter when copying existing table data, some rows may be copied that |
| 503 |
would not be replicated using DML. See |
| 504 |
<xref linkend="logical-replication-subscription-examples"/> for examples. |
| 505 |
</para> |
| 506 |
|
| 507 |
<para> |
| 508 |
Subscriptions having several publications in which the same table has been |
| 509 |
published with different column lists are not supported. |
| 510 |
</para> |
| 511 |
|
| 512 |
<para> |
| 513 |
We allow non-existent publications to be specified so that users can add |
| 514 |
those later. This means |
| 515 |
<link linkend="catalog-pg-subscription"><structname>pg_subscription</structname></link> |
| 516 |
can have non-existent publications. |
| 517 |
</para> |
| 518 |
|
| 519 |
<para> |
| 520 |
When using a subscription parameter combination of |
| 521 |
<literal>copy_data = true</literal> and <literal>origin = NONE</literal>, |
| 522 |
the initial sync table data is copied directly from the publisher, meaning |
| 523 |
that knowledge of the true origin of that data is not possible. If the |
| 524 |
publisher also has subscriptions then the copied table data might have |
| 525 |
originated from further upstream. This scenario is detected and a WARNING is |
| 526 |
logged to the user, but the warning is only an indication of a potential |
| 527 |
problem; it is the user's responsibility to make the necessary checks to |
| 528 |
ensure the copied data origins are really as wanted or not. |
| 529 |
</para> |
| 530 |
|
| 531 |
<para> |
| 532 |
To find which tables might potentially include non-local origins (due to |
| 533 |
other subscriptions created on the publisher) try this SQL query: |
| 534 |
<programlisting> |
| 535 |
# substitute <pub-names> below with your publication name(s) to be queried |
| 536 |
SELECT DISTINCT PT.schemaname, PT.tablename |
| 537 |
FROM pg_publication_tables PT |
| 538 |
JOIN pg_class C ON (C.relname = PT.tablename) |
| 539 |
JOIN pg_namespace N ON (N.nspname = PT.schemaname), |
| 540 |
pg_subscription_rel PS |
| 541 |
WHERE C.relnamespace = N.oid AND |
| 542 |
(PS.srrelid = C.oid OR |
| 543 |
C.oid IN (SELECT relid FROM pg_partition_ancestors(PS.srrelid) UNION |
| 544 |
SELECT relid FROM pg_partition_tree(PS.srrelid))) AND |
| 545 |
PT.pubname IN (<pub-names>); |
| 546 |
</programlisting></para> |
| 547 |
|
| 548 |
</refsect1> |
| 549 |
|
| 550 |
<refsect1> |
| 551 |
<title>Examples</title> |
| 552 |
|
| 553 |
<para> |
| 554 |
Create a subscription to a remote server that replicates tables in |
| 555 |
the publications <literal>mypublication</literal> and |
| 556 |
<literal>insert_only</literal> and starts replicating immediately on |
| 557 |
commit: |
| 558 |
<programlisting> |
| 559 |
CREATE SUBSCRIPTION mysub |
| 560 |
CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb' |
| 561 |
PUBLICATION mypublication, insert_only; |
| 562 |
</programlisting> |
| 563 |
</para> |
| 564 |
|
| 565 |
<para> |
| 566 |
Create a subscription to a remote server that replicates tables in |
| 567 |
the <literal>insert_only</literal> publication and does not start replicating |
| 568 |
until enabled at a later time. |
| 569 |
<programlisting> |
| 570 |
CREATE SUBSCRIPTION mysub |
| 571 |
CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb' |
| 572 |
PUBLICATION insert_only |
| 573 |
WITH (enabled = false); |
| 574 |
</programlisting></para> |
| 575 |
</refsect1> |
| 576 |
|
| 577 |
<refsect1> |
| 578 |
<title>Compatibility</title> |
| 579 |
|
| 580 |
<para> |
| 581 |
<command>CREATE SUBSCRIPTION</command> is a <productname>PostgreSQL</productname> |
| 582 |
extension. |
| 583 |
</para> |
| 584 |
</refsect1> |
| 585 |
|
| 586 |
<refsect1> |
| 587 |
<title>See Also</title> |
| 588 |
|
| 589 |
<simplelist type="inline"> |
| 590 |
<member><xref linkend="sql-altersubscription"/></member> |
| 591 |
<member><xref linkend="sql-dropsubscription"/></member> |
| 592 |
<member><xref linkend="sql-createpublication"/></member> |
| 593 |
<member><xref linkend="sql-alterpublication"/></member> |
| 594 |
</simplelist> |
| 595 |
</refsect1> |
| 596 |
</refentry> |