Ubuntu Manpage: pg_createsubscriber - convert a physical replica into a new logical replica

NAME

       pg_createsubscriber - convert a physical replica into a new logical replica

SYNOPSIS

       pg_createsubscriber [option...] {-d | --database}dbname {-D | --pgdata}datadir {-P |
                           --publisher-server}connstr

DESCRIPTION

       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.

       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.

       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.

OPTIONS

pg_createsubscriber accepts the following command-line arguments:

-d dbname
--database=dbname
The name of the database in which to create a subscription. Multiple databases can be
selected by writing multiple -d switches.

-D directory
--pgdata=directory
The target directory that contains a cluster directory from a physical replica.

-n
--dry-run
Do everything except actually modifying the target directory.

-p port
--subscriber-port=port
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.

-P connstr
--publisher-server=connstr
The connection string to the publisher. For details see Section 32.1.1.

-s dir
--socketdir=dir
The directory to use for postmaster sockets on target server. The default is current
directory.

-t seconds
--recovery-timeout=seconds
The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The
default is 0.

-U username
--subscriber-username=username
The user name to connect as on target server. Defaults to the current operating system
user name.

-v
--verbose
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.

--config-file=filename
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.

--publication=name
The publication name to set up the logical replication. Multiple publications can be
specified by writing multiple --publication 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.

--replication-slot=name
The replication slot name to set up the logical replication. Multiple replication
slots can be specified by writing multiple --replication-slot 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.

--subscription=name
The subscription name to set up the logical replication. Multiple subscriptions can be
specified by writing multiple --subscription 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.

-V
--version
Print the pg_createsubscriber version and exit.

-?
--help
Show help about pg_createsubscriber command line arguments, and exit.

NOTES

Prerequisites
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 pg_replication_origin_advance().

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.

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.

Warnings
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.

pg_createsubscriber usually starts the target server with different connection settings
during transformation. Hence, connections to the target server should fail.

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.

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.

If the replication is using primary_slot_name, it will be removed from the source server
after the logical replication setup.

If the target server is a synchronous replica, transaction commits on the primary might
wait for replication while running pg_createsubscriber.

pg_createsubscriber sets up logical replication with two-phase commit disabled. This means
that any prepared transactions will be replicated at the time of COMMIT PREPARED, without
advance preparation. Once setup is complete, you can manually drop and re-create the
subscription(s) with the two_phase option enabled.

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.

How It Works
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:

1. Start the target server with the specified command-line options. If the target server
is already running, pg_createsubscriber will terminate with an error.

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.

3. Create a publication and replication slot for each specified database on the source
server. Each publication is created using FOR ALL TABLES. If publication-name option
is not specified, it has the following name pattern: “pg_createsubscriber_%u_%x”
(parameter: database oid, random int). If replication-slot-name is not specified, the
replication slot has the following name pattern: “pg_createsubscriber_%u_%x”
(parameters: database oid, random int). 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.

4. 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 --recovery-timeout option is set, pg_createsubscriber terminates if
recovery does not end until the given number of seconds.

5. Create a subscription for each specified database on the target server. If
subscription-name is not specified, the subscription has the following name pattern:
“pg_createsubscriber_%u_%x” (parameters: database oid, random int). 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.

6. 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.

7. 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
pg_replication_origin_advance() to set up the initial replication location.

8. Enable the subscription for each specified database on the target server. The
subscription starts applying transactions from the replication start point.

9. If the standby server was using primary_slot_name, it has no use from now on so drop
it.

10. If the standby server contains failover replication slots, they cannot be
synchronized anymore, so drop them.

11. Update the system identifier on the target server. The pg_resetwal(1) is run to
modify the system identifier. The target server is stopped as a pg_resetwal
requirement.

EXAMPLES