Convert HTML docs to more streamlined TXT

[ai-pg] / full-docs / man1 / pg_createsubscriber.1

'\" t
.\"     Title: pg_createsubscriber
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\"      Date: 2025
.\"    Manual: PostgreSQL 18.0 Documentation
.\"    Source: PostgreSQL 18.0
.\"  Language: English
.\"
.TH "PG_CREATESUBSCRIBER" "1" "2025" "PostgreSQL 18.0" "PostgreSQL 18.0 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, it is recommended to set up logical replication with initial data synchronization\&. For details, see the
\fBCREATE SUBSCRIPTION\fR
copy_data
option\&.
.SH "OPTIONS"
.PP
pg_createsubscriber
accepts the following command\-line arguments:
.PP
\fB\-a\fR
.br
\fB\-\-all\fR
.RS 4
Create one subscription per database on the target server\&. Exceptions are template databases and databases that don\*(Aqt allow connections\&. To discover the list of all databases, connect to the source server using the database name specified in the
\fB\-\-publisher\-server\fR
connection string, or if not specified, the
postgres
database will be used, or if that does not exist,
template1
will be used\&. Automatically generated names for subscriptions, publications, and replication slots are used when this option is specified\&. This option cannot be used along with
\fB\-\-database\fR,
\fB\-\-publication\fR,
\fB\-\-replication\-slot\fR, or
\fB\-\-subscription\fR\&.
.RE
.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\&. This option cannot be used together with
\fB\-a\fR\&. If
\fB\-d\fR
option is not provided, the database name will be obtained from
\fB\-P\fR
option\&. If the database name is not specified in either the
\fB\-d\fR
option, or the
\fB\-P\fR
option, and
\fB\-a\fR
option is not specified, an error will be reported\&.
.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\-T\fR
.br
\fB\-\-enable\-two\-phase\fR
.RS 4
Enables
two_phase
commit for the subscription\&. When multiple databases are specified, this option applies uniformly to all subscriptions created on those databases\&. The default is
false\&.
.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\-\-clean=\fR\fB\fIobjtype\fR\fR
.RS 4
Drop all objects of the specified type from specified databases on the target server\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
publications: The
FOR ALL TABLES
publications established for this subscriber are always dropped; specifying this object type causes all other publications replicated from the source server to be dropped as well\&.
.RE
.sp
The objects selected to be dropped are individually logged, including during a
\fB\-\-dry\-run\fR\&. There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using
pg_dump\&.
.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\&. This option cannot be used together with
\fB\-\-all\fR\&.
.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\&. This option cannot be used together with
\fB\-\-all\fR\&.
.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\&. This option cannot be used together with
\fB\-\-all\fR\&.
.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_active_replication_origins
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\&. If you are planning to use the
\fB\-\-enable\-two\-phase\fR
switch then you will also need to set the
max_prepared_transactions
appropriately\&.
.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
Unless the
\fB\-\-enable\-two\-phase\fR
switch is specified,
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\&.
.PP
Replication failures can occur if required WAL files are missing\&. To prevent this, the source server must set
max_slot_wal_keep_size
to
\-1
to ensure that required WAL files are not prematurely removed\&.
.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 the
\fB\-\-publication\fR
option is not specified, the publication has the following name pattern:
\(lqpg_createsubscriber_%u_%x\(rq
(parameter: database
\fIoid\fR, random
\fIint\fR)\&. If the
\fB\-\-replication\-slot\fR
option 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 the
\fB\-\-subscription\fR
option 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)