'\" t .\" Title: pg_createsubscriber .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot .\" Date: 2024 .\" Manual: PostgreSQL 17.2 Documentation .\" Source: PostgreSQL 17.2 .\" Language: English .\" .TH "PG_CREATESUBSCRIBER" "1" "2024" "PostgreSQL 17.2" "PostgreSQL 17.2 Documentation" .\" ----------------------------------------------------------------- .\" * Define some portability stuff .\" ----------------------------------------------------------------- .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .\" http://bugs.debian.org/507673 .\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" pg_createsubscriber \- convert a physical replica into a new logical replica .SH "SYNOPSIS" .HP \w'\fBpg_createsubscriber\fR\ 'u \fBpg_createsubscriber\fR [\fIoption\fR...] {\fB\-d\fR | \fB\-\-database\fR}\fIdbname\fR {\fB\-D\fR | \fB\-\-pgdata\fR}\fIdatadir\fR {\fB\-P\fR | \fB\-\-publisher\-server\fR}\fIconnstr\fR .SH "DESCRIPTION" .PP pg_createsubscriber creates a new logical replica from a physical standby server\&. All tables in the specified database are included in the logical replication setup\&. A pair of publication and subscription objects are created for each database\&. It must be run at the target server\&. .PP After a successful run, the state of the target server is analogous to a fresh logical replication setup\&. The main difference between the logical replication setup and pg_createsubscriber is how the data synchronization is done\&. pg_createsubscriber does not copy the initial table data\&. It does only the synchronization phase, which ensures each table is brought up to a synchronized state\&. .PP pg_createsubscriber targets large database systems because in logical replication setup, most of the time is spent doing the initial data copy\&. Furthermore, a side effect of this long time spent synchronizing data is usually a large amount of changes to be applied (that were produced during the initial data copy), which increases even more the time when the logical replica will be available\&. For smaller databases, initial data synchronization is recommended\&. .SH "OPTIONS" .PP pg_createsubscriber accepts the following command\-line arguments: .PP \fB\-d \fR\fB\fIdbname\fR\fR .br \fB\-\-database=\fR\fB\fIdbname\fR\fR .RS 4 The name of the database in which to create a subscription\&. Multiple databases can be selected by writing multiple \fB\-d\fR switches\&. .RE .PP \fB\-D \fR\fB\fIdirectory\fR\fR .br \fB\-\-pgdata=\fR\fB\fIdirectory\fR\fR .RS 4 The target directory that contains a cluster directory from a physical replica\&. .RE .PP \fB\-n\fR .br \fB\-\-dry\-run\fR .RS 4 Do everything except actually modifying the target directory\&. .RE .PP \fB\-p \fR\fB\fIport\fR\fR .br \fB\-\-subscriber\-port=\fR\fB\fIport\fR\fR .RS 4 The port number on which the target server is listening for connections\&. Defaults to running the target server on port 50432 to avoid unintended client connections\&. .RE .PP \fB\-P \fR\fB\fIconnstr\fR\fR .br \fB\-\-publisher\-server=\fR\fB\fIconnstr\fR\fR .RS 4 The connection string to the publisher\&. For details see Section\ \&32.1.1\&. .RE .PP \fB\-s \fR\fB\fIdir\fR\fR .br \fB\-\-socketdir=\fR\fB\fIdir\fR\fR .RS 4 The directory to use for postmaster sockets on target server\&. The default is current directory\&. .RE .PP \fB\-t \fR\fB\fIseconds\fR\fR .br \fB\-\-recovery\-timeout=\fR\fB\fIseconds\fR\fR .RS 4 The maximum number of seconds to wait for recovery to end\&. Setting to 0 disables\&. The default is 0\&. .RE .PP \fB\-U \fR\fB\fIusername\fR\fR .br \fB\-\-subscriber\-username=\fR\fB\fIusername\fR\fR .RS 4 The user name to connect as on target server\&. Defaults to the current operating system user name\&. .RE .PP \fB\-v\fR .br \fB\-\-verbose\fR .RS 4 Enables verbose mode\&. This will cause pg_createsubscriber to output progress messages and detailed information about each step to standard error\&. Repeating the option causes additional debug\-level messages to appear on standard error\&. .RE .PP \fB\-\-config\-file=\fR\fB\fIfilename\fR\fR .RS 4 Use the specified main server configuration file for the target data directory\&. pg_createsubscriber internally uses the pg_ctl command to start and stop the target server\&. It allows you to specify the actual postgresql\&.conf configuration file if it is stored outside the data directory\&. .RE .PP \fB\-\-publication=\fR\fB\fIname\fR\fR .RS 4 The publication name to set up the logical replication\&. Multiple publications can be specified by writing multiple \fB\-\-publication\fR switches\&. The number of publication names must match the number of specified databases, otherwise an error is reported\&. The order of the multiple publication name switches must match the order of database switches\&. If this option is not specified, a generated name is assigned to the publication name\&. .RE .PP \fB\-\-replication\-slot=\fR\fB\fIname\fR\fR .RS 4 The replication slot name to set up the logical replication\&. Multiple replication slots can be specified by writing multiple \fB\-\-replication\-slot\fR switches\&. The number of replication slot names must match the number of specified databases, otherwise an error is reported\&. The order of the multiple replication slot name switches must match the order of database switches\&. If this option is not specified, the subscription name is assigned to the replication slot name\&. .RE .PP \fB\-\-subscription=\fR\fB\fIname\fR\fR .RS 4 The subscription name to set up the logical replication\&. Multiple subscriptions can be specified by writing multiple \fB\-\-subscription\fR switches\&. The number of subscription names must match the number of specified databases, otherwise an error is reported\&. The order of the multiple subscription name switches must match the order of database switches\&. If this option is not specified, a generated name is assigned to the subscription name\&. .RE .PP \fB\-V\fR .br \fB\-\-version\fR .RS 4 Print the pg_createsubscriber version and exit\&. .RE .PP \fB\-?\fR .br \fB\-\-help\fR .RS 4 Show help about pg_createsubscriber command line arguments, and exit\&. .RE .SH "NOTES" .SS "Prerequisites" .PP There are some prerequisites for pg_createsubscriber to convert the target server into a logical replica\&. If these are not met, an error will be reported\&. The source and target servers must have the same major version as the pg_createsubscriber\&. The given target data directory must have the same system identifier as the source data directory\&. The given database user for the target data directory must have privileges for creating subscriptions and using \fBpg_replication_origin_advance()\fR\&. .PP The target server must be used as a physical standby\&. The target server must have max_replication_slots and max_logical_replication_workers configured to a value greater than or equal to the number of specified databases\&. The target server must have max_worker_processes configured to a value greater than the number of specified databases\&. The target server must accept local connections\&. .PP The source server must accept connections from the target server\&. The source server must not be in recovery\&. The source server must have wal_level as logical\&. The source server must have max_replication_slots configured to a value greater than or equal to the number of specified databases plus existing replication slots\&. The source server must have max_wal_senders configured to a value greater than or equal to the number of specified databases and existing WAL sender processes\&. .SS "Warnings" .PP If pg_createsubscriber fails after the target server was promoted, then the data directory is likely not in a state that can be recovered\&. In such case, creating a new standby server is recommended\&. .PP pg_createsubscriber usually starts the target server with different connection settings during transformation\&. Hence, connections to the target server should fail\&. .PP Since DDL commands are not replicated by logical replication, avoid executing DDL commands that change the database schema while running pg_createsubscriber\&. If the target server has already been converted to logical replica, the DDL commands might not be replicated, which might cause an error\&. .PP If pg_createsubscriber fails while processing, objects (publications, replication slots) created on the source server are removed\&. The removal might fail if the target server cannot connect to the source server\&. In such a case, a warning message will inform the objects left\&. If the target server is running, it will be stopped\&. .PP If the replication is using primary_slot_name, it will be removed from the source server after the logical replication setup\&. .PP If the target server is a synchronous replica, transaction commits on the primary might wait for replication while running pg_createsubscriber\&. .PP pg_createsubscriber sets up logical replication with two\-phase commit disabled\&. This means that any prepared transactions will be replicated at the time of \fBCOMMIT PREPARED\fR, without advance preparation\&. Once setup is complete, you can manually drop and re\-create the subscription(s) with the two_phase option enabled\&. .PP pg_createsubscriber changes the system identifier using pg_resetwal\&. It would avoid situations in which the target server might use WAL files from the source server\&. If the target server has a standby, replication will break and a fresh standby should be created\&. .SS "How It Works" .PP The basic idea is to have a replication start point from the source server and set up a logical replication to start from this point: .sp .RS 4 .ie n \{\ \h'-04' 1.\h'+01'\c .\} .el \{\ .sp -1 .IP " 1." 4.2 .\} Start the target server with the specified command\-line options\&. If the target server is already running, pg_createsubscriber will terminate with an error\&. .RE .sp .RS 4 .ie n \{\ \h'-04' 2.\h'+01'\c .\} .el \{\ .sp -1 .IP " 2." 4.2 .\} Check if the target server can be converted\&. There are also a few checks on the source server\&. If any of the prerequisites are not met, pg_createsubscriber will terminate with an error\&. .RE .sp .RS 4 .ie n \{\ \h'-04' 3.\h'+01'\c .\} .el \{\ .sp -1 .IP " 3." 4.2 .\} Create a publication and replication slot for each specified database on the source server\&. Each publication is created using FOR ALL TABLES\&. If \fBpublication\-name\fR option is not specified, it has the following name pattern: \(lqpg_createsubscriber_%u_%x\(rq (parameter: database \fIoid\fR, random \fIint\fR)\&. If \fBreplication\-slot\-name\fR is not specified, the replication slot has the following name pattern: \(lqpg_createsubscriber_%u_%x\(rq (parameters: database \fIoid\fR, random \fIint\fR)\&. These replication slots will be used by the subscriptions in a future step\&. The last replication slot LSN is used as a stopping point in the recovery_target_lsn parameter and by the subscriptions as a replication start point\&. It guarantees that no transaction will be lost\&. .RE .sp .RS 4 .ie n \{\ \h'-04' 4.\h'+01'\c .\} .el \{\ .sp -1 .IP " 4." 4.2 .\} Write recovery parameters into the target data directory and restart the target server\&. It specifies an LSN (recovery_target_lsn) of the write\-ahead log location up to which recovery will proceed\&. It also specifies promote as the action that the server should take once the recovery target is reached\&. Additional recovery parameters are added to avoid unexpected behavior during the recovery process such as end of the recovery as soon as a consistent state is reached (WAL should be applied until the replication start location) and multiple recovery targets that can cause a failure\&. This step finishes once the server ends standby mode and is accepting read\-write transactions\&. If \fB\-\-recovery\-timeout\fR option is set, pg_createsubscriber terminates if recovery does not end until the given number of seconds\&. .RE .sp .RS 4 .ie n \{\ \h'-04' 5.\h'+01'\c .\} .el \{\ .sp -1 .IP " 5." 4.2 .\} Create a subscription for each specified database on the target server\&. If \fBsubscription\-name\fR is not specified, the subscription has the following name pattern: \(lqpg_createsubscriber_%u_%x\(rq (parameters: database \fIoid\fR, random \fIint\fR)\&. It does not copy existing data from the source server\&. It does not create a replication slot\&. Instead, it uses the replication slot that was created in a previous step\&. The subscription is created but it is not enabled yet\&. The reason is the replication progress must be set to the replication start point before starting the replication\&. .RE .sp .RS 4 .ie n \{\ \h'-04' 6.\h'+01'\c .\} .el \{\ .sp -1 .IP " 6." 4.2 .\} Drop publications on the target server that were replicated because they were created before the replication start location\&. It has no use on the subscriber\&. .RE .sp .RS 4 .ie n \{\ \h'-04' 7.\h'+01'\c .\} .el \{\ .sp -1 .IP " 7." 4.2 .\} Set the replication progress to the replication start point for each subscription\&. When the target server starts the recovery process, it catches up to the replication start point\&. This is the exact LSN to be used as a initial replication location for each subscription\&. The replication origin name is obtained since the subscription was created\&. The replication origin name and the replication start point are used in \fBpg_replication_origin_advance()\fR to set up the initial replication location\&. .RE .sp .RS 4 .ie n \{\ \h'-04' 8.\h'+01'\c .\} .el \{\ .sp -1 .IP " 8." 4.2 .\} Enable the subscription for each specified database on the target server\&. The subscription starts applying transactions from the replication start point\&. .RE .sp .RS 4 .ie n \{\ \h'-04' 9.\h'+01'\c .\} .el \{\ .sp -1 .IP " 9." 4.2 .\} If the standby server was using primary_slot_name, it has no use from now on so drop it\&. .RE .sp .RS 4 .ie n \{\ \h'-04' 10.\h'+01'\c .\} .el \{\ .sp -1 .IP " 10." 4.2 .\} If the standby server contains failover replication slots, they cannot be synchronized anymore, so drop them\&. .RE .sp .RS 4 .ie n \{\ \h'-04' 11.\h'+01'\c .\} .el \{\ .sp -1 .IP " 11." 4.2 .\} Update the system identifier on the target server\&. The \fBpg_resetwal\fR(1) is run to modify the system identifier\&. The target server is stopped as a \fBpg_resetwal\fR requirement\&. .RE .SH "EXAMPLES" .PP To create a logical replica for databases hr and finance from a physical replica at foo: .sp .if n \{\ .RS 4 .\} .nf $ \fBpg_createsubscriber \-D /usr/local/pgsql/data \-P "host=foo" \-d hr \-d finance\fR .fi .if n \{\ .RE .\} .sp .SH "SEE ALSO" \fBpg_basebackup\fR(1)