Debian Codegraph @

doc/src/sgml/ref/pg_createsubscriber.sgml

raw
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>-a</option></term>
92 <term><option>--all</option></term>
93 <listitem>
94 <para>
95 Create one subscription per database on the target server. Exceptions
96 are template databases and databases that don't allow connections.
97 To discover the list of all databases, connect to the source server
98 using the database name specified in the <option>--publisher-server</option>
99 connection string, or if not specified, the <literal>postgres</literal>
100 database will be used, or if that does not exist, <literal>template1</literal>
101 will be used.
102 Automatically generated names for subscriptions, publications, and
103 replication slots are used when this option is specified.
104 This option cannot be used along with <option>--database</option>,
105 <option>--publication</option>, <option>--replication-slot</option>, or
106 <option>--subscription</option>.
107 </para>
108 </listitem>
109 </varlistentry>
110
111 <varlistentry>
112 <term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
113 <term><option>--database=<replaceable class="parameter">dbname</replaceable></option></term>
114 <listitem>
115 <para>
116 The name of the database in which to create a subscription. Multiple
117 databases can be selected by writing multiple <option>-d</option>
118 switches. This option cannot be used together with <option>-a</option>.
119 If <option>-d</option> option is not provided, the database name will be
120 obtained from <option>-P</option> option. If the database name is not
121 specified in either the <option>-d</option> option, or the
122 <option>-P</option> option, and <option>-a</option> option is not
123 specified, an error will be reported.
124 </para>
125 </listitem>
126 </varlistentry>
127
128 <varlistentry>
129 <term><option>-D <replaceable class="parameter">directory</replaceable></option></term>
130 <term><option>--pgdata=<replaceable class="parameter">directory</replaceable></option></term>
131 <listitem>
132 <para>
133 The target directory that contains a cluster directory from a physical
134 replica.
135 </para>
136 </listitem>
137 </varlistentry>
138
139 <varlistentry>
140 <term><option>-n</option></term>
141 <term><option>--dry-run</option></term>
142 <listitem>
143 <para>
144 Do everything except actually modifying the target directory.
145 </para>
146 </listitem>
147 </varlistentry>
148
149 <varlistentry>
150 <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
151 <term><option>--subscriber-port=<replaceable class="parameter">port</replaceable></option></term>
152 <listitem>
153 <para>
154 The port number on which the target server is listening for
155 connections. Defaults to running the target server on port 50432 to
156 avoid unintended client connections.
157 </para>
158 </listitem>
159 </varlistentry>
160
161 <varlistentry>
162 <term><option>-P <replaceable class="parameter">connstr</replaceable></option></term>
163 <term><option>--publisher-server=<replaceable class="parameter">connstr</replaceable></option></term>
164 <listitem>
165 <para>
166 The connection string to the publisher. For details see <xref
167 linkend="libpq-connstring"/>.
168 </para>
169 </listitem>
170 </varlistentry>
171
172 <varlistentry>
173 <term><option>-s <replaceable class="parameter">dir</replaceable></option></term>
174 <term><option>--socketdir=<replaceable class="parameter">dir</replaceable></option></term>
175 <listitem>
176 <para>
177 The directory to use for postmaster sockets on target server. The
178 default is current directory.
179 </para>
180 </listitem>
181 </varlistentry>
182
183 <varlistentry>
184 <term><option>-t <replaceable class="parameter">seconds</replaceable></option></term>
185 <term><option>--recovery-timeout=<replaceable class="parameter">seconds</replaceable></option></term>
186 <listitem>
187 <para>
188 The maximum number of seconds to wait for recovery to end. Setting to
189 0 disables. The default is 0.
190 </para>
191 </listitem>
192 </varlistentry>
193
194 <varlistentry>
195 <term><option>-T</option></term>
196 <term><option>--enable-two-phase</option></term>
197 <listitem>
198 <para>
199 Enables <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
200 commit for the subscription. When multiple databases are specified, this
201 option applies uniformly to all subscriptions created on those databases.
202 The default is <literal>false</literal>.
203 </para>
204 </listitem>
205 </varlistentry>
206
207 <varlistentry>
208 <term><option>-U <replaceable class="parameter">username</replaceable></option></term>
209 <term><option>--subscriber-username=<replaceable class="parameter">username</replaceable></option></term>
210 <listitem>
211 <para>
212 The user name to connect as on target server. Defaults to the current
213 operating system user name.
214 </para>
215 </listitem>
216 </varlistentry>
217
218 <varlistentry>
219 <term><option>-v</option></term>
220 <term><option>--verbose</option></term>
221 <listitem>
222 <para>
223 Enables verbose mode. This will cause
224 <application>pg_createsubscriber</application> to output progress
225 messages and detailed information about each step to standard error.
226 Repeating the option causes additional debug-level messages to appear
227 on standard error.
228 </para>
229 </listitem>
230 </varlistentry>
231
232 <varlistentry>
233 <term><option>--clean=<replaceable class="parameter">objtype</replaceable></option></term>
234 <listitem>
235 <para>
236 Drop all objects of the specified type from specified databases on the
237 target server.
238 </para>
239 <para>
240 <itemizedlist>
241 <listitem>
242 <para>
243 <literal>publications</literal>:
244 The <literal>FOR ALL TABLES</literal> publications established for this
245 subscriber are always dropped; specifying this object type causes all
246 other publications replicated from the source server to be dropped as
247 well.
248 </para>
249 </listitem>
250 </itemizedlist>
251 </para>
252 <para>
253 The objects selected to be dropped are individually logged, including during
254 a <option>--dry-run</option>. There is no opportunity to affect or stop the
255 dropping of the selected objects, so consider taking a backup of them
256 using <application>pg_dump</application>.
257 </para>
258 </listitem>
259 </varlistentry>
260
261 <varlistentry>
262 <term><option>--config-file=<replaceable class="parameter">filename</replaceable></option></term>
263 <listitem>
264 <para>
265 Use the specified main server configuration file for the target data
266 directory. <application>pg_createsubscriber</application> internally uses
267 the <application>pg_ctl</application> command to start and
268 stop the target server. It allows you to specify the actual
269 <filename>postgresql.conf</filename> configuration file if it is stored
270 outside the data directory.
271 </para>
272 </listitem>
273 </varlistentry>
274
275 <varlistentry>
276 <term><option>--publication=<replaceable class="parameter">name</replaceable></option></term>
277 <listitem>
278 <para>
279 The publication name to set up the logical replication. Multiple
280 publications can be specified by writing multiple
281 <option>--publication</option> switches. The number of publication
282 names must match the number of specified databases, otherwise an error
283 is reported. The order of the multiple publication name switches must
284 match the order of database switches. If this option is not specified,
285 a generated name is assigned to the publication name. This option cannot
286 be used together with <option>--all</option>.
287 </para>
288 </listitem>
289 </varlistentry>
290
291 <varlistentry>
292 <term><option>--replication-slot=<replaceable class="parameter">name</replaceable></option></term>
293 <listitem>
294 <para>
295 The replication slot name to set up the logical replication. Multiple
296 replication slots can be specified by writing multiple
297 <option>--replication-slot</option> switches. The number of
298 replication slot names must match the number of specified databases,
299 otherwise an error is reported. The order of the multiple replication
300 slot name switches must match the order of database switches. If this
301 option is not specified, the subscription name is assigned to the
302 replication slot name. This option cannot be used together with
303 <option>--all</option>.
304 </para>
305 </listitem>
306 </varlistentry>
307
308 <varlistentry>
309 <term><option>--subscription=<replaceable class="parameter">name</replaceable></option></term>
310 <listitem>
311 <para>
312 The subscription name to set up the logical replication. Multiple
313 subscriptions can be specified by writing multiple
314 <option>--subscription</option> switches. The number of subscription
315 names must match the number of specified databases, otherwise an error
316 is reported. The order of the multiple subscription name switches must
317 match the order of database switches. If this option is not specified,
318 a generated name is assigned to the subscription name. This option cannot
319 be used together with <option>--all</option>.
320 </para>
321 </listitem>
322 </varlistentry>
323
324 <varlistentry>
325 <term><option>-V</option></term>
326 <term><option>--version</option></term>
327 <listitem>
328 <para>
329 Print the <application>pg_createsubscriber</application> version and exit.
330 </para>
331 </listitem>
332 </varlistentry>
333
334 <varlistentry>
335 <term><option>-?</option></term>
336 <term><option>--help</option></term>
337 <listitem>
338 <para>
339 Show help about <application>pg_createsubscriber</application> command
340 line arguments, and exit.
341 </para>
342 </listitem>
343 </varlistentry>
344 </variablelist>
345 </para>
346 </refsect1>
347
348 <refsect1>
349 <title>Notes</title>
350
351 <refsect2>
352 <title>Prerequisites</title>
353
354 <para>
355 There are some prerequisites for
356 <application>pg_createsubscriber</application> to convert the target server
357 into a logical replica. If these are not met, an error will be reported.
358 The source and target servers must have the same major version as the
359 <application>pg_createsubscriber</application>. The given target data
360 directory must have the same system identifier as the source data
361 directory. The given database user for the target data directory must have
362 privileges for creating <link
363 linkend="sql-createsubscription">subscriptions</link> and using <link
364 linkend="pg-replication-origin-advance"><function>pg_replication_origin_advance()</function></link>.
365 </para>
366
367 <para>
368 The target server must be used as a physical standby. The target server
369 must have <xref linkend="guc-max-active-replication-origins"/> and <xref
370 linkend="guc-max-logical-replication-workers"/> configured to a value
371 greater than or equal to the number of specified databases. The target
372 server must have <xref linkend="guc-max-worker-processes"/> configured to a
373 value greater than the number of specified databases. The target server
374 must accept local connections. If you are planning to use the
375 <option>--enable-two-phase</option> switch then you will also need to set
376 the <xref linkend="guc-max-prepared-transactions"/> appropriately.
377 </para>
378
379 <para>
380 The source server must accept connections from the target server. The
381 source server must not be in recovery. The source server must have <xref
382 linkend="guc-wal-level"/> as <literal>logical</literal>. The source server
383 must have <xref linkend="guc-max-replication-slots"/> configured to a value
384 greater than or equal to the number of specified databases plus existing
385 replication slots. The source server must have <xref
386 linkend="guc-max-wal-senders"/> configured to a value greater than or equal
387 to the number of specified databases and existing WAL sender processes.
388 </para>
389 </refsect2>
390
391 <refsect2>
392 <title>Warnings</title>
393
394 <para>
395 If <application>pg_createsubscriber</application> fails after the target
396 server was promoted, then the data directory is likely not in a state that
397 can be recovered. In such case, creating a new standby server is
398 recommended.
399 </para>
400
401 <para>
402 <application>pg_createsubscriber</application> usually starts the target
403 server with different connection settings during transformation. Hence,
404 connections to the target server should fail.
405 </para>
406
407 <para>
408 Since DDL commands are not replicated by logical replication, avoid
409 executing DDL commands that change the database schema while running
410 <application>pg_createsubscriber</application>. If the target server has
411 already been converted to logical replica, the DDL commands might not be
412 replicated, which might cause an error.
413 </para>
414
415 <para>
416 If <application>pg_createsubscriber</application> fails while processing,
417 objects (publications, replication slots) created on the source server are
418 removed. The removal might fail if the target server cannot connect to
419 the source server. In such a case, a warning message will inform the
420 objects left. If the target server is running, it will be stopped.
421 </para>
422
423 <para>
424 If the replication is using <xref linkend="guc-primary-slot-name"/>, it
425 will be removed from the source server after the logical replication
426 setup.
427 </para>
428
429 <para>
430 If the target server is a synchronous replica, transaction commits on the
431 primary might wait for replication while running
432 <application>pg_createsubscriber</application>.
433 </para>
434
435 <para>
436 Unless the <option>--enable-two-phase</option> switch is specified,
437 <application>pg_createsubscriber</application> sets up logical
438 replication with two-phase commit disabled. This means that any
439 prepared transactions will be replicated at the time
440 of <command>COMMIT PREPARED</command>, without advance preparation.
441 Once setup is complete, you can manually drop and re-create the
442 subscription(s) with
443 the <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
444 option enabled.
445 </para>
446
447 <para>
448 <application>pg_createsubscriber</application> changes the system
449 identifier using <application>pg_resetwal</application>. It would avoid
450 situations in which the target server might use WAL files from the source
451 server. If the target server has a standby, replication will break and a
452 fresh standby should be created.
453 </para>
454
455 <para>
456 Replication failures can occur if required WAL files are missing. To prevent
457 this, the source server must set
458 <xref linkend="guc-max-slot-wal-keep-size"/> to <literal>-1</literal> to
459 ensure that required WAL files are not prematurely removed.
460 </para>
461 </refsect2>
462
463 <refsect2>
464 <title>How It Works</title>
465
466 <para>
467 The basic idea is to have a replication start point from the source server
468 and set up a logical replication to start from this point:
469 </para>
470
471 <procedure>
472 <step>
473 <para>
474 Start the target server with the specified command-line options. If the
475 target server is already running,
476 <application>pg_createsubscriber</application> will terminate with an
477 error.
478 </para>
479 </step>
480
481 <step>
482 <para>
483 Check if the target server can be converted. There are also a few
484 checks on the source server. If any of the prerequisites are not met,
485 <application>pg_createsubscriber</application> will terminate with an
486 error.
487 </para>
488 </step>
489
490 <step>
491 <para>
492 Create a publication and replication slot for each specified database on
493 the source server. Each publication is created using <link
494 linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL
495 TABLES</literal></link>. If the <option>--publication</option> option
496 is not specified, the publication has the following name pattern:
497 <quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameter:
498 database <parameter>oid</parameter>, random <parameter>int</parameter>).
499 If the <option>--replication-slot</option> option is not specified, the
500 replication slot has the following name pattern:
501 <quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameters:
502 database <parameter>oid</parameter>, random <parameter>int</parameter>).
503 These replication slots will be used by the subscriptions in a future
504 step. The last replication slot LSN is used as a stopping point in the
505 <xref linkend="guc-recovery-target-lsn"/> parameter and by the
506 subscriptions as a replication start point. It guarantees that no
507 transaction will be lost.
508 </para>
509 </step>
510
511 <step>
512 <para>
513 Write recovery parameters into the target data directory and restart the
514 target server. It specifies an LSN (<xref
515 linkend="guc-recovery-target-lsn"/>) of the write-ahead log location up
516 to which recovery will proceed. It also specifies
517 <literal>promote</literal> as the action that the server should take
518 once the recovery target is reached. Additional <link
519 linkend="runtime-config-wal-recovery-target">recovery parameters</link>
520 are added to avoid unexpected behavior during the recovery process such
521 as end of the recovery as soon as a consistent state is reached (WAL
522 should be applied until the replication start location) and multiple
523 recovery targets that can cause a failure. This step finishes once the
524 server ends standby mode and is accepting read-write transactions. If
525 <option>--recovery-timeout</option> option is set,
526 <application>pg_createsubscriber</application> terminates if recovery
527 does not end until the given number of seconds.
528 </para>
529 </step>
530
531 <step>
532 <para>
533 Create a subscription for each specified database on the target server.
534 If the <option>--subscription</option> option is not specified, the
535 subscription has the following name pattern:
536 <quote><literal>pg_createsubscriber_%u_%x</literal></quote> (parameters:
537 database <parameter>oid</parameter>, random <parameter>int</parameter>).
538 It does not copy existing data from the source server. It does not
539 create a replication slot. Instead, it uses the replication slot that
540 was created in a previous step. The subscription is created but it is
541 not enabled yet. The reason is the replication progress must be set to
542 the replication start point before starting the replication.
543 </para>
544 </step>
545
546 <step>
547 <para>
548 Drop publications on the target server that were replicated because they
549 were created before the replication start location. It has no use on
550 the subscriber.
551 </para>
552 </step>
553
554 <step>
555 <para>
556 Set the replication progress to the replication start point for each
557 subscription. When the target server starts the recovery process, it
558 catches up to the replication start point. This is the exact LSN to be
559 used as a initial replication location for each subscription. The
560 replication origin name is obtained since the subscription was created.
561 The replication origin name and the replication start point are used in
562 <link
563 linkend="pg-replication-origin-advance"><function>pg_replication_origin_advance()</function></link>
564 to set up the initial replication location.
565 </para>
566 </step>
567
568 <step>
569 <para>
570 Enable the subscription for each specified database on the target server.
571 The subscription starts applying transactions from the replication start
572 point.
573 </para>
574 </step>
575
576 <step>
577 <para>
578 If the standby server was using <xref linkend="guc-primary-slot-name"/>,
579 it has no use from now on so drop it.
580 </para>
581 </step>
582
583 <step>
584 <para>
585 If the standby server contains <link
586 linkend="logicaldecoding-replication-slots-synchronization">failover
587 replication slots</link>, they cannot be synchronized anymore, so drop
588 them.
589 </para>
590 </step>
591
592 <step>
593 <para>
594 Update the system identifier on the target server. The
595 <xref linkend="app-pgresetwal"/> is run to modify the system identifier.
596 The target server is stopped as a <command>pg_resetwal</command> requirement.
597 </para>
598 </step>
599 </procedure>
600 </refsect2>
601 </refsect1>
602
603 <refsect1>
604 <title>Examples</title>
605
606 <para>
607 To create a logical replica for databases <literal>hr</literal> and
608 <literal>finance</literal> from a physical replica at
609 <literal>foo</literal>:
610 <screen>
611 <prompt>$</prompt> <userinput>pg_createsubscriber -D /usr/local/pgsql/data -P "host=foo" -d hr -d finance</userinput>
612 </screen>
613 </para>
614 </refsect1>
615
616 <refsect1>
617 <title>See Also</title>
618
619 <simplelist type="inline">
620 <member><xref linkend="app-pgbasebackup"/></member>
621 </simplelist>
622 </refsect1>
623 </refentry>