pg_createsubscriber

Convert a physical replica to a logical replica for a specific database

$ pg_createsubscriber [[-d|--database]] [dbname] [[-D|--pgdata]] [path/to/data] [[-P|--publisher-server]] [connstr]

$ pg_createsubscriber [[-n|--dry-run]] [[-d|--database]] [dbname] [[-D|--pgdata]] [path/to/data] [[-P|--publisher-server]] [connstr]

$ pg_createsubscriber [[-T|--enable-two-phase]] [[-d|--database]] [dbname] [[-D|--pgdata]] [path/to/data] [[-P|--publisher-server]] [connstr]

$ pg_createsubscriber [[-v|--verbose]] [[-d|--database]] [dbname] [[-D|--pgdata]] [path/to/data] [[-P|--publisher-server]] [connstr]

$ pg_createsubscriber [[-?|--help]]

psql [connection_options] -c "CREATE SUBSCRIPTION name CONNECTION 'dsn' PUBLICATION publication_name [, publication_name...] [WITH (option = value [, ...])];"

Alternatively, for interactive use:
psql [connection_options]
... then at the psql prompt:
CREATE SUBSCRIPTION name CONNECTION 'dsn' PUBLICATION publication_name [, publication_name...] [WITH (option = value [, ...])];

name
    A unique name for the new subscription. This name is used to identify the subscription for management, e.g., with `ALTER SUBSCRIPTION`.

CONNECTION 'dsn'
    The connection string (Data Source Name) to the publisher database. This string specifies host, port, database name, user, and password for connecting to the publisher.

PUBLICATION publication_name
    The name(s) of the publication(s) on the publisher database to subscribe to. Multiple publication names can be listed, separated by commas.

WITH (copy_data = boolean)
    If `true` (default), existing data in the publisher's tables will be copied to the subscriber once the subscription starts. If `false`, only new changes are replicated.

WITH (create_slot = boolean)
    If `true` (default), the subscription will automatically create a replication slot on the publisher with a name derived from the subscription name. Set to `false` if a slot already exists or is managed externally.

WITH (enabled = boolean)
    If `true` (default), the subscription starts in an enabled state and begins replicating immediately after creation. If `false`, it must be enabled later using `ALTER SUBSCRIPTION ... ENABLE`.

WITH (slot_name = string)
    The explicit name of the replication slot to use on the publisher. If not specified and `create_slot` is `true`, a name is automatically generated.

WITH (connect = boolean)
    If `false`, the subscription will not attempt to connect to the publisher during its creation. This can be useful for initial setup where the publisher might not be immediately available.

WITH (binary = boolean)
    If `true`, data is transferred in binary format where possible, potentially offering better performance. If `false` (default), data is transferred in text format.

WITH (streaming = boolean)
    If `true`, large transactions are streamed to the subscriber while in progress, reducing memory footprint on the publisher for long-running transactions. (Available since PostgreSQL 14)

The `pg_createsubscriber` operation refers to the process of setting up a logical replication subscriber in PostgreSQL. This is primarily achieved using the SQL command `CREATE SUBSCRIPTION`, which is executed within a PostgreSQL client, typically `psql`.

Logical replication allows data changes from a publisher database to be replicated to one or more subscriber databases. The `CREATE SUBSCRIPTION` command defines a connection to a publisher and specifies which publications (sets of tables and/or changes) to receive. It manages the initial data copy, continuous data synchronization, and can be configured with various options to control its behavior, such as whether to copy existing data, create a replication slot, or start the subscription in an enabled state.

While there isn't a standalone "pg_createsubscriber" Linux executable in the core PostgreSQL distribution, the term might conceptually refer to the overall task of creating a subscriber, often involving this SQL command executed through a shell-based tool like `psql`.

The term "pg_createsubscriber" is not a direct standalone executable provided by core PostgreSQL. The functionality described is achieved through the `CREATE SUBSCRIPTION` SQL command, typically executed via `psql`.

Proper network connectivity and firewall rules must be in place between the subscriber and publisher. The user account specified in the connection DSN must have sufficient privileges on the publisher, including `REPLICATION` role, `SELECT` on published tables, and potentially `USAGE` on schemas.

Care must be taken when setting `copy_data = false`, as it assumes the subscriber tables are already in sync with the publisher, otherwise data divergence will occur. Setting up logical replication requires careful planning and understanding of its implications.

Before creating a subscription, ensure the publisher database has `wal_level` set to `logical` in its `postgresql.conf` and has a `PUBLICATION` defined. The user connecting from the subscriber must have `REPLICATION` privilege and `SELECT` privilege on the tables within the published schemas.

Replication slots are crucial for logical replication as they prevent the publisher from removing WAL segments until they have been consumed by all subscribers. By default, `CREATE SUBSCRIPTION` creates a new slot. If the slot is dropped or becomes invalid on the publisher, the subscriber will stop receiving changes, and manual intervention will be required.

Native logical replication, including the `CREATE SUBSCRIPTION` command, was introduced in PostgreSQL 10, providing an integrated solution for selective data replication. Prior to this, solutions like the `pglogical` extension were commonly used to achieve similar functionality. Subsequent PostgreSQL versions have introduced additional features and options, such as streaming large transactions in PostgreSQL 14, to enhance the flexibility and performance of logical replication.

CREATE PUBLICATION (SQL command), ALTER SUBSCRIPTION (SQL command), DROP SUBSCRIPTION (SQL command), psql(1), pg_basebackup(1)