{"kind": "tool", "major": "18", "item": {"slug": "pg-createsubscriber", "name": "pg_createsubscriber", "name_zh": "", "category": "Server applications", "summary": "pg_createsubscriber \u2014 convert a physical replica into a new logical replica", "aliases": ["pg_createsubscriber"], "content_hash": "c4b251615b714c289c0c54356896bce5978e2206a711c18be1da52a81af966ee", "versions": {"17": {"facts": [{"label": "Documented executable", "value": "pg_createsubscriber"}, {"label": "Executable version", "value": "17.11"}, {"label": "Reference inventory", "value": "Server applications"}, {"label": "Option definition groups", "value": "15"}], "tables": [{"key": "options", "rows": [{"summary": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-d dbname --database= dbname"}}, {"summary": "The target directory that contains a cluster directory from a physical replica.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-D directory --pgdata= directory"}}, {"summary": "Do everything except actually modifying the target directory.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-n --dry-run"}}, {"summary": "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.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-p port --subscriber-port= port"}}, {"summary": "The connection string to the publisher. For details see Section 32.1.1 .", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-P connstr --publisher-server= connstr"}}, {"summary": "The directory to use for postmaster sockets on target server. The default is current directory.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-s dir --socketdir= dir"}}, {"summary": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-t seconds --recovery-timeout= seconds"}}, {"summary": "The user name to connect as on target server. Defaults to the current operating system user name.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-U username --subscriber-username= username"}}, {"summary": "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.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-v --verbose"}}, {"summary": "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.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "--config-file= filename"}}, {"summary": "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.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "--publication= name"}}, {"summary": "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.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "--replication-slot= name"}}, {"summary": "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.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "--subscription= name"}}, {"summary": "Print the pg_createsubscriber version and exit.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-V --version"}}, {"summary": "Show help about pg_createsubscriber command line arguments, and exit.", "signature": {"url": "/docs/17/app-pgcreatesubscriber.html", "text": "-? --help"}}], "title": "Documented options", "columns": [{"key": "signature", "label": "Option and arguments"}, {"key": "summary", "label": "Description"}]}], "options": [{"names": ["-d dbname", "--database= dbname"], "summary": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches.", "signature": "-d dbname --database= dbname", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches."}, {"names": ["-D directory", "--pgdata= directory"], "summary": "The target directory that contains a cluster directory from a physical replica.", "signature": "-D directory --pgdata= directory", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "The target directory that contains a cluster directory from a physical replica."}, {"names": ["-n", "--dry-run"], "summary": "Do everything except actually modifying the target directory.", "signature": "-n --dry-run", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "Do everything except actually modifying the target directory."}, {"names": ["-p port", "--subscriber-port= port"], "summary": "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.", "signature": "-p port --subscriber-port= port", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["-P connstr", "--publisher-server= connstr"], "summary": "The connection string to the publisher. For details see Section 32.1.1 .", "signature": "-P connstr --publisher-server= connstr", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "The connection string to the publisher. For details see Section 32.1.1 ."}, {"names": ["-s dir", "--socketdir= dir"], "summary": "The directory to use for postmaster sockets on target server. The default is current directory.", "signature": "-s dir --socketdir= dir", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "The directory to use for postmaster sockets on target server. The default is current directory."}, {"names": ["-t seconds", "--recovery-timeout= seconds"], "summary": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.", "signature": "-t seconds --recovery-timeout= seconds", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0."}, {"names": ["-U username", "--subscriber-username= username"], "summary": "The user name to connect as on target server. Defaults to the current operating system user name.", "signature": "-U username --subscriber-username= username", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "The user name to connect as on target server. Defaults to the current operating system user name."}, {"names": ["-v", "--verbose"], "summary": "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.", "signature": "-v --verbose", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--config-file= filename"], "summary": "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.", "signature": "--config-file= filename", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--publication= name"], "summary": "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.", "signature": "--publication= name", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--replication-slot= name"], "summary": "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.", "signature": "--replication-slot= name", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--subscription= name"], "summary": "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.", "signature": "--subscription= name", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["-V", "--version"], "summary": "Print the pg_createsubscriber version and exit.", "signature": "-V --version", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "Print the pg_createsubscriber version and exit."}, {"names": ["-?", "--help"], "summary": "Show help about pg_createsubscriber command line arguments, and exit.", "signature": "-? --help", "source_url": "/docs/17/app-pgcreatesubscriber.html", "description": "Show help about pg_createsubscriber command line arguments, and exit."}], "related": [], "release": {"ref": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "label": "17.11", "major": "17", "channel": "stable", "manifest": {"pdf": {"A4": {"url": "/files/documentation/pdf/17/postgresql-17-A4.pdf", "bytes": 15521293, "pages": 3099, "sha256": "1991354df0dc89e70ec39328c28988ef8b19c6a93671dab3893650b63e9f4e36", "built_at": "2026-09-26"}, "US": {"url": "/files/documentation/pdf/17/postgresql-17-US.pdf", "bytes": 15398150, "pages": 3270, "sha256": "07696c8f38abf31babf22d2db337093936e7c472d2af36d050b000c49bbcf52c", "built_at": "2026-09-26"}}, "tree": "17", "index": "index.html", "major": "17", "pages": 1143, "release": "17.11", "source_url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "svg_assets": 3, "source_mode": "en SGML built with pinned official archive", "source_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, "revision": "58419c9b0dd42cb34c8d53695bb025a7e582edf55ccd4c5bcb1c2c7c71a37487", "evidence_kind": "English manual and source declarations", "source_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, "sources": [{"url": "/docs/17/app-pgcreatesubscriber.html", "file": "app-pgcreatesubscriber.html", "label": "17.11 English manual \u00b7 app-pgcreatesubscriber.html", "anchor": "", "sha256": "acc0ccb21b3ea99df91f6bcf9db0e12b9e2b582391e36ac1f3f06ee2c7411337"}, {"url": "/docs/17/reference-server.html", "file": "reference-server.html", "label": "Server applications inventory", "anchor": "", "sha256": "01f07993ff020684f4c290d17cdafa26fb17ea5c1d476bc3a5efc68bf8ddd9b9"}], "sections": [], "synopsis": ["pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr"], "signature": "pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr", "description": ["pg_createsubscriber \u2014 convert a physical replica into a new logical replica"], "environment": [], "manual_html": "
\n
\n
\n

pg_createsubscriber

\n

pg_createsubscriber \u2014 convert a physical replica into a new logical replica

\n
\n
\n

Synopsis

\n
\n

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

\n
\n
\n
\n

Description

\n

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.

\n

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.

\n

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 CREATE SUBSCRIPTION copy_data option.

\n
\n
\n

Options

\n

pg_createsubscriber accepts the following command-line arguments:

\n
\n
\n
-d dbname
--database=dbname
\n
\n

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

\n
\n
-D directory
--pgdata=directory
\n
\n

The target directory that contains a cluster directory from a physical replica.

\n
\n
-n
--dry-run
\n
\n

Do everything except actually modifying the target directory.

\n
\n
-p port
--subscriber-port=port
\n
\n

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.

\n
\n
-P connstr
--publisher-server=connstr
\n
\n

The connection string to the publisher. For details see Section\u00a032.1.1.

\n
\n
-s dir
--socketdir=dir
\n
\n

The directory to use for postmaster sockets on target server. The default is current directory.

\n
\n
-t seconds
--recovery-timeout=seconds
\n
\n

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

\n
\n
-U username
--subscriber-username=username
\n
\n

The user name to connect as on target server. Defaults to the current operating system user name.

\n
\n
-v
--verbose
\n
\n

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.

\n
\n
--config-file=filename
\n
\n

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.

\n
\n
--publication=name
\n
\n

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.

\n
\n
--replication-slot=name
\n
\n

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.

\n
\n
--subscription=name
\n
\n

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.

\n
\n
-V
--version
\n
\n

Print the pg_createsubscriber version and exit.

\n
\n
-?
--help
\n
\n

Show help about pg_createsubscriber command line arguments, and exit.

\n
\n
\n
\n
\n
\n

Notes

\n
\n

Prerequisites

\n

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

\n

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.

\n

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.

\n
\n
\n

Warnings

\n

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.

\n

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

\n

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.

\n

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.

\n

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

\n

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

\n

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.

\n

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.

\n
\n
\n

How It Works

\n

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:

\n
\n
    \n
  1. \n

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

    \n
    2. \n
  2. \n

    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.

    \n
    3. \n
  3. \n

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

    \n
    4. \n
  4. \n

    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.

    \n
    5. \n
  5. \n

    Create a subscription for each specified database on the target server. If the --subscription option is not specified, the subscription has the following name pattern: \u201cpg_createsubscriber_%u_%x\u201d (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.

    \n
    6. \n
  6. \n

    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.

    \n
    7. \n
  7. \n

    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.

    \n
    8. \n
  8. \n

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

    \n
    9. \n
  9. \n

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

    \n
    10. \n
  10. \n

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

    \n
    11. \n
  11. \n

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

    \n
    12. \n
\n
\n
\n
\n
\n

Examples

\n

To create a logical replica for databases hr and finance from a physical replica at foo:

\n
$ pg_createsubscriber -D /usr/local/pgsql/data -P \"host=foo\" -d hr -d finance\n
\n
\n
\n

See Also

pg_basebackup\n
\n
", "manual_path": "app-pgcreatesubscriber.html", "comparison_data": {"options": [{"names": ["-d dbname", "--database= dbname"], "signature": "-d dbname --database= dbname", "description": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches."}, {"names": ["-D directory", "--pgdata= directory"], "signature": "-D directory --pgdata= directory", "description": "The target directory that contains a cluster directory from a physical replica."}, {"names": ["-n", "--dry-run"], "signature": "-n --dry-run", "description": "Do everything except actually modifying the target directory."}, {"names": ["-p port", "--subscriber-port= port"], "signature": "-p port --subscriber-port= port", "description": "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."}, {"names": ["-P connstr", "--publisher-server= connstr"], "signature": "-P connstr --publisher-server= connstr", "description": "The connection string to the publisher. For details see Section 32.1.1 ."}, {"names": ["-s dir", "--socketdir= dir"], "signature": "-s dir --socketdir= dir", "description": "The directory to use for postmaster sockets on target server. The default is current directory."}, {"names": ["-t seconds", "--recovery-timeout= seconds"], "signature": "-t seconds --recovery-timeout= seconds", "description": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0."}, {"names": ["-U username", "--subscriber-username= username"], "signature": "-U username --subscriber-username= username", "description": "The user name to connect as on target server. Defaults to the current operating system user name."}, {"names": ["-v", "--verbose"], "signature": "-v --verbose", "description": "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."}, {"names": ["--config-file= filename"], "signature": "--config-file= filename", "description": "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."}, {"names": ["--publication= name"], "signature": "--publication= name", "description": "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."}, {"names": ["--replication-slot= name"], "signature": "--replication-slot= name", "description": "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."}, {"names": ["--subscription= name"], "signature": "--subscription= name", "description": "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."}, {"names": ["-V", "--version"], "signature": "-V --version", "description": "Print the pg_createsubscriber version and exit."}, {"names": ["-?", "--help"], "signature": "-? --help", "description": "Show help about pg_createsubscriber command line arguments, and exit."}], "synopsis": ["pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr"], "environment": []}, "comparison_hash": "104b1091aa60e86021ca6825e5d2414d196b482450979f525b6f54db62c8b4dd"}, "18": {"facts": [{"label": "Documented executable", "value": "pg_createsubscriber"}, {"label": "Executable version", "value": "18.6"}, {"label": "Reference inventory", "value": "Server applications"}, {"label": "Option definition groups", "value": "18"}], "tables": [{"key": "options", "rows": [{"summary": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-a --all"}}, {"summary": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-d dbname --database= dbname"}}, {"summary": "The target directory that contains a cluster directory from a physical replica.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-D directory --pgdata= directory"}}, {"summary": "Do everything except actually modifying the target directory.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-n --dry-run"}}, {"summary": "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.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-p port --subscriber-port= port"}}, {"summary": "The connection string to the publisher. For details see Section 32.1.1 .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-P connstr --publisher-server= connstr"}}, {"summary": "The directory to use for postmaster sockets on target server. The default is current directory.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-s dir --socketdir= dir"}}, {"summary": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-t seconds --recovery-timeout= seconds"}}, {"summary": "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 .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-T --enable-two-phase"}}, {"summary": "The user name to connect as on target server. Defaults to the current operating system user name.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-U username --subscriber-username= username"}}, {"summary": "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.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-v --verbose"}}, {"summary": "Drop all objects of the specified type from specified databases on the target server.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "--clean= objtype"}}, {"summary": "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.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "--config-file= filename"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "--publication= name"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "--replication-slot= name"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "--subscription= name"}}, {"summary": "Print the pg_createsubscriber version and exit.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-V --version"}}, {"summary": "Show help about pg_createsubscriber command line arguments, and exit.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-? --help"}}], "title": "Documented options", "columns": [{"key": "signature", "label": "Option and arguments"}, {"key": "summary", "label": "Description"}]}], "options": [{"names": ["-a", "--all"], "summary": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription .", "signature": "-a --all", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription ."}, {"names": ["-d dbname", "--database= dbname"], "summary": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.", "signature": "-d dbname --database= dbname", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported."}, {"names": ["-D directory", "--pgdata= directory"], "summary": "The target directory that contains a cluster directory from a physical replica.", "signature": "-D directory --pgdata= directory", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The target directory that contains a cluster directory from a physical replica."}, {"names": ["-n", "--dry-run"], "summary": "Do everything except actually modifying the target directory.", "signature": "-n --dry-run", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "Do everything except actually modifying the target directory."}, {"names": ["-p port", "--subscriber-port= port"], "summary": "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.", "signature": "-p port --subscriber-port= port", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["-P connstr", "--publisher-server= connstr"], "summary": "The connection string to the publisher. For details see Section 32.1.1 .", "signature": "-P connstr --publisher-server= connstr", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The connection string to the publisher. For details see Section 32.1.1 ."}, {"names": ["-s dir", "--socketdir= dir"], "summary": "The directory to use for postmaster sockets on target server. The default is current directory.", "signature": "-s dir --socketdir= dir", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The directory to use for postmaster sockets on target server. The default is current directory."}, {"names": ["-t seconds", "--recovery-timeout= seconds"], "summary": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.", "signature": "-t seconds --recovery-timeout= seconds", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0."}, {"names": ["-T", "--enable-two-phase"], "summary": "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 .", "signature": "-T --enable-two-phase", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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 ."}, {"names": ["-U username", "--subscriber-username= username"], "summary": "The user name to connect as on target server. Defaults to the current operating system user name.", "signature": "-U username --subscriber-username= username", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The user name to connect as on target server. Defaults to the current operating system user name."}, {"names": ["-v", "--verbose"], "summary": "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.", "signature": "-v --verbose", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--clean= objtype"], "summary": "Drop all objects of the specified type from specified databases on the target server.", "signature": "--clean= objtype", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "Drop all objects of the specified type from specified databases on the target server. 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. The objects selected to be dropped are individually logged, including during a --dry-run . There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump ."}, {"names": ["--config-file= filename"], "summary": "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.", "signature": "--config-file= filename", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--publication= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--publication= name", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all ."}, {"names": ["--replication-slot= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--replication-slot= name", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all ."}, {"names": ["--subscription= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--subscription= name", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all ."}, {"names": ["-V", "--version"], "summary": "Print the pg_createsubscriber version and exit.", "signature": "-V --version", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "Print the pg_createsubscriber version and exit."}, {"names": ["-?", "--help"], "summary": "Show help about pg_createsubscriber command line arguments, and exit.", "signature": "-? --help", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "Show help about pg_createsubscriber command line arguments, and exit."}], "related": [], "release": {"ref": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "label": "18.6", "major": "18", "channel": "stable", "manifest": {"pdf": {"A4": {"url": "/files/documentation/pdf/18/postgresql-18-A4.pdf", "bytes": 15865106, "pages": 3154, "sha256": "19512c405da53f9f7fcf0abba359223aa65f021be025bf3411381918f92e3190", "built_at": "2026-09-26"}, "US": {"url": "/files/documentation/pdf/18/postgresql-18-US.pdf", "bytes": 15748059, "pages": 3328, "sha256": "facbe6c229e598b872d3d98bef53308f46e06746006fa4590de9a7de9dd46319", "built_at": "2026-09-26"}}, "tree": "18", "index": "index.html", "major": "18", "pages": 1148, "release": "18.6", "source_url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "svg_assets": 3, "source_mode": "en SGML built with pinned official archive", "source_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, "revision": "ee8d1a3612338fd9adf250730cb640fcc5233b5491337cc00a316a44e3a0b9f8", "evidence_kind": "English manual and source declarations", "source_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, "sources": [{"url": "/docs/18/app-pgcreatesubscriber.html", "file": "app-pgcreatesubscriber.html", "label": "18.6 English manual \u00b7 app-pgcreatesubscriber.html", "anchor": "", "sha256": "fb0e5cd7f37044410e06deb436d7b70a1bceffbd849ce10f468351d9cf11d385"}, {"url": "/docs/18/reference-server.html", "file": "reference-server.html", "label": "Server applications inventory", "anchor": "", "sha256": "e9f8bbcc8e3641fadd1d20991e8aad6b532155b786ba5a1b7b364ec4f1a5bef3"}], "sections": [], "synopsis": ["pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr"], "signature": "pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr", "description": ["pg_createsubscriber \u2014 convert a physical replica into a new logical replica"], "environment": [], "manual_html": "
\n
\n
\n

pg_createsubscriber

\n

pg_createsubscriber \u2014 convert a physical replica into a new logical replica

\n
\n
\n

Synopsis

\n
\n

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

\n
\n
\n
\n

Description

\n

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.

\n

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.

\n

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 CREATE SUBSCRIPTION copy_data option.

\n
\n
\n

Options

\n

pg_createsubscriber accepts the following command-line arguments:

\n
\n
\n
-a
--all
\n
\n

Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database, --publication, --replication-slot, or --subscription.

\n
\n
-d dbname
--database=dbname
\n
\n

The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a. If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.

\n
\n
-D directory
--pgdata=directory
\n
\n

The target directory that contains a cluster directory from a physical replica.

\n
\n
-n
--dry-run
\n
\n

Do everything except actually modifying the target directory.

\n
\n
-p port
--subscriber-port=port
\n
\n

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.

\n
\n
-P connstr
--publisher-server=connstr
\n
\n

The connection string to the publisher. For details see Section\u00a032.1.1.

\n
\n
-s dir
--socketdir=dir
\n
\n

The directory to use for postmaster sockets on target server. The default is current directory.

\n
\n
-t seconds
--recovery-timeout=seconds
\n
\n

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

\n
\n
-T
--enable-two-phase
\n
\n

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.

\n
\n
-U username
--subscriber-username=username
\n
\n

The user name to connect as on target server. Defaults to the current operating system user name.

\n
\n
-v
--verbose
\n
\n

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.

\n
\n
--clean=objtype
\n
\n

Drop all objects of the specified type from specified databases on the target server.

\n
\n
    \n
  • \n

    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.

    \n
    • \n
\n
\n

The objects selected to be dropped are individually logged, including during a --dry-run. There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump.

\n
\n
--config-file=filename
\n
\n

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.

\n
\n
--publication=name
\n
\n

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. This option cannot be used together with --all.

\n
\n
--replication-slot=name
\n
\n

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. This option cannot be used together with --all.

\n
\n
--subscription=name
\n
\n

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. This option cannot be used together with --all.

\n
\n
-V
--version
\n
\n

Print the pg_createsubscriber version and exit.

\n
\n
-?
--help
\n
\n

Show help about pg_createsubscriber command line arguments, and exit.

\n
\n
\n
\n
\n
\n

Notes

\n
\n

Prerequisites

\n

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

\n

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 --enable-two-phase switch then you will also need to set the max_prepared_transactions appropriately.

\n

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.

\n
\n
\n

Warnings

\n

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.

\n

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

\n

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.

\n

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.

\n

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

\n

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

\n

Unless the --enable-two-phase 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 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.

\n

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.

\n

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.

\n
\n
\n

How It Works

\n

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:

\n
\n
    \n
  1. \n

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

    \n
    2. \n
  2. \n

    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.

    \n
    3. \n
  3. \n

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

    \n
    4. \n
  4. \n

    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.

    \n
    5. \n
  5. \n

    Create a subscription for each specified database on the target server. If the --subscription option is not specified, the subscription has the following name pattern: \u201cpg_createsubscriber_%u_%x\u201d (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.

    \n
    6. \n
  6. \n

    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.

    \n
    7. \n
  7. \n

    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.

    \n
    8. \n
  8. \n

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

    \n
    9. \n
  9. \n

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

    \n
    10. \n
  10. \n

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

    \n
    11. \n
  11. \n

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

    \n
    12. \n
\n
\n
\n
\n
\n

Examples

\n

To create a logical replica for databases hr and finance from a physical replica at foo:

\n
$ pg_createsubscriber -D /usr/local/pgsql/data -P \"host=foo\" -d hr -d finance\n
\n
\n
\n

See Also

pg_basebackup\n
\n
", "manual_path": "app-pgcreatesubscriber.html", "comparison_data": {"options": [{"names": ["-a", "--all"], "signature": "-a --all", "description": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription ."}, {"names": ["-d dbname", "--database= dbname"], "signature": "-d dbname --database= dbname", "description": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported."}, {"names": ["-D directory", "--pgdata= directory"], "signature": "-D directory --pgdata= directory", "description": "The target directory that contains a cluster directory from a physical replica."}, {"names": ["-n", "--dry-run"], "signature": "-n --dry-run", "description": "Do everything except actually modifying the target directory."}, {"names": ["-p port", "--subscriber-port= port"], "signature": "-p port --subscriber-port= port", "description": "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."}, {"names": ["-P connstr", "--publisher-server= connstr"], "signature": "-P connstr --publisher-server= connstr", "description": "The connection string to the publisher. For details see Section 32.1.1 ."}, {"names": ["-s dir", "--socketdir= dir"], "signature": "-s dir --socketdir= dir", "description": "The directory to use for postmaster sockets on target server. The default is current directory."}, {"names": ["-t seconds", "--recovery-timeout= seconds"], "signature": "-t seconds --recovery-timeout= seconds", "description": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0."}, {"names": ["-T", "--enable-two-phase"], "signature": "-T --enable-two-phase", "description": "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 ."}, {"names": ["-U username", "--subscriber-username= username"], "signature": "-U username --subscriber-username= username", "description": "The user name to connect as on target server. Defaults to the current operating system user name."}, {"names": ["-v", "--verbose"], "signature": "-v --verbose", "description": "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."}, {"names": ["--clean= objtype"], "signature": "--clean= objtype", "description": "Drop all objects of the specified type from specified databases on the target server. 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. The objects selected to be dropped are individually logged, including during a --dry-run . There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump ."}, {"names": ["--config-file= filename"], "signature": "--config-file= filename", "description": "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."}, {"names": ["--publication= name"], "signature": "--publication= name", "description": "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. This option cannot be used together with --all ."}, {"names": ["--replication-slot= name"], "signature": "--replication-slot= name", "description": "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. This option cannot be used together with --all ."}, {"names": ["--subscription= name"], "signature": "--subscription= name", "description": "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. This option cannot be used together with --all ."}, {"names": ["-V", "--version"], "signature": "-V --version", "description": "Print the pg_createsubscriber version and exit."}, {"names": ["-?", "--help"], "signature": "-? --help", "description": "Show help about pg_createsubscriber command line arguments, and exit."}], "synopsis": ["pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr"], "environment": []}, "comparison_hash": "ab2f9ba9dc71a377cdb3a682c66b1a84f3cb3ae29cb8c1b178c5acc418bc4441"}, "19": {"facts": [{"label": "Documented executable", "value": "pg_createsubscriber"}, {"label": "Executable version", "value": "19beta4"}, {"label": "Reference inventory", "value": "Server applications"}, {"label": "Option definition groups", "value": "19"}], "tables": [{"key": "options", "rows": [{"summary": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription .", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-a --all"}}, {"summary": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-d dbname --database= dbname"}}, {"summary": "The target directory that contains a cluster directory from a physical replica.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-D datadir --pgdata= datadir"}}, {"summary": "Specify the name of the log directory. A new directory is created with this name if it does not exist. A subdirectory with a timestamp indicating the time at which pg_createsubscriber was run will be created. The following two log files will be created in the subdirectory.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-l directory --logdir= directory"}}, {"summary": "Do everything except actually modifying the target directory.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-n --dry-run"}}, {"summary": "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.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-p port --subscriber-port= port"}}, {"summary": "The connection string to the publisher. For details see Section 32.1.1 .", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-P connstr --publisher-server= connstr"}}, {"summary": "The directory to use for postmaster sockets on target server. The default is current directory.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-s dir --socketdir= dir"}}, {"summary": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-t seconds --recovery-timeout= seconds"}}, {"summary": "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 .", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-T --enable-two-phase"}}, {"summary": "The user name to connect as on target server. Defaults to the current operating system user name.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-U username --subscriber-username= username"}}, {"summary": "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.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-v --verbose"}}, {"summary": "Drop all objects of the specified type from specified databases on the target server.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "--clean= objtype"}}, {"summary": "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.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "--config-file= filename"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "--publication= name"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "--replication-slot= name"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "--subscription= name"}}, {"summary": "Print the pg_createsubscriber version and exit.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-V --version"}}, {"summary": "Show help about pg_createsubscriber command line arguments, and exit.", "signature": {"url": "/docs/19/app-pgcreatesubscriber.html", "text": "-? --help"}}], "title": "Documented options", "columns": [{"key": "signature", "label": "Option and arguments"}, {"key": "summary", "label": "Description"}]}], "options": [{"names": ["-a", "--all"], "summary": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription .", "signature": "-a --all", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription ."}, {"names": ["-d dbname", "--database= dbname"], "summary": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.", "signature": "-d dbname --database= dbname", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported."}, {"names": ["-D datadir", "--pgdata= datadir"], "summary": "The target directory that contains a cluster directory from a physical replica.", "signature": "-D datadir --pgdata= datadir", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "The target directory that contains a cluster directory from a physical replica."}, {"names": ["-l directory", "--logdir= directory"], "summary": "Specify the name of the log directory. A new directory is created with this name if it does not exist. A subdirectory with a timestamp indicating the time at which pg_createsubscriber was run will be created. The following two log files will be created in the subdirectory.", "signature": "-l directory --logdir= directory", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "Specify the name of the log directory. A new directory is created with this name if it does not exist. A subdirectory with a timestamp indicating the time at which pg_createsubscriber was run will be created. The following two log files will be created in the subdirectory. pg_createsubscriber_server.log which captures logs related to stopping and starting the standby server, pg_createsubscriber_internal.log which captures internal diagnostic output (validations, checks, etc.) By default, the umask is set to 077 so that the log files are only readable by the user running the command. However, if the target data directory is configured to allow group-read access, pg_createsubscriber will adjust the log file permissions to match. This ensures that the log file security remains consistent with the database cluster itself."}, {"names": ["-n", "--dry-run"], "summary": "Do everything except actually modifying the target directory.", "signature": "-n --dry-run", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "Do everything except actually modifying the target directory."}, {"names": ["-p port", "--subscriber-port= port"], "summary": "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.", "signature": "-p port --subscriber-port= port", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["-P connstr", "--publisher-server= connstr"], "summary": "The connection string to the publisher. For details see Section 32.1.1 .", "signature": "-P connstr --publisher-server= connstr", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "The connection string to the publisher. For details see Section 32.1.1 ."}, {"names": ["-s dir", "--socketdir= dir"], "summary": "The directory to use for postmaster sockets on target server. The default is current directory.", "signature": "-s dir --socketdir= dir", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "The directory to use for postmaster sockets on target server. The default is current directory."}, {"names": ["-t seconds", "--recovery-timeout= seconds"], "summary": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.", "signature": "-t seconds --recovery-timeout= seconds", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0."}, {"names": ["-T", "--enable-two-phase"], "summary": "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 .", "signature": "-T --enable-two-phase", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "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 ."}, {"names": ["-U username", "--subscriber-username= username"], "summary": "The user name to connect as on target server. Defaults to the current operating system user name.", "signature": "-U username --subscriber-username= username", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "The user name to connect as on target server. Defaults to the current operating system user name."}, {"names": ["-v", "--verbose"], "summary": "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.", "signature": "-v --verbose", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--clean= objtype"], "summary": "Drop all objects of the specified type from specified databases on the target server.", "signature": "--clean= objtype", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "Drop all objects of the specified type from specified databases on the target server. 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. The objects selected to be dropped are individually logged, including during a --dry-run . There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump ."}, {"names": ["--config-file= filename"], "summary": "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.", "signature": "--config-file= filename", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--publication= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--publication= name", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all . If a specified publication already exists on the publisher, it is reused. It is useful to partially replicate the database if the specified publication includes a list of tables. If the publication does not exist, it is automatically created with FOR ALL TABLES . Use --dry-run option to preview which publications will be reused and which will be created."}, {"names": ["--replication-slot= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--replication-slot= name", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all ."}, {"names": ["--subscription= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--subscription= name", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all ."}, {"names": ["-V", "--version"], "summary": "Print the pg_createsubscriber version and exit.", "signature": "-V --version", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "Print the pg_createsubscriber version and exit."}, {"names": ["-?", "--help"], "summary": "Show help about pg_createsubscriber command line arguments, and exit.", "signature": "-? --help", "source_url": "/docs/19/app-pgcreatesubscriber.html", "description": "Show help about pg_createsubscriber command line arguments, and exit."}], "related": [], "release": {"ref": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "label": "19beta4", "major": "19", "channel": "preview", "manifest": {"pdf": {"A4": {"url": "/files/documentation/pdf/19/postgresql-19-A4.pdf", "bytes": 16064841, "pages": 3052, "sha256": "4dd099e4125c591128fc5f3ebd02178dc24781f9e5ae629f96d67c4c8547427b", "built_at": "2026-09-26"}, "US": {"url": "/files/documentation/pdf/19/postgresql-19-US.pdf", "bytes": 15974616, "pages": 3225, "sha256": "61971fa857f0956d47341a0388fa6af9ae10acf691d4b2fc009007d384b0342b", "built_at": "2026-09-26"}}, "tree": "19", "index": "index.html", "major": "19", "pages": 1155, "release": "19beta4", "source_url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "svg_assets": 5, "source_mode": "en SGML built with pinned official archive", "source_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, "revision": "1bbbbf4133d426f0e4304010688d2984c30fb67df0cc3a61b3e37eb3f6f37833", "evidence_kind": "English manual and source declarations", "source_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, "sources": [{"url": "/docs/19/app-pgcreatesubscriber.html", "file": "app-pgcreatesubscriber.html", "label": "19beta4 English manual \u00b7 app-pgcreatesubscriber.html", "anchor": "", "sha256": "b80fa11097ac7787e12ec63969d98dbf47c367fddcc5edc87271f213dd4a07df"}, {"url": "/docs/19/reference-server.html", "file": "reference-server.html", "label": "Server applications inventory", "anchor": "", "sha256": "425b070323b448b5516d2cb4e9e4ac77efb5d19dd4e8005d0ae1c61db9ec78e6"}], "sections": [], "synopsis": ["pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr"], "signature": "pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr", "description": ["pg_createsubscriber \u2014 convert a physical replica into a new logical replica"], "environment": [], "manual_html": "
\n
\n
\n

pg_createsubscriber

\n

pg_createsubscriber \u2014 convert a physical replica into a new logical replica

\n
\n
\n

Synopsis

\n
\n

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

\n
\n
\n
\n

Description

\n

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.

\n

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.

\n

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 CREATE SUBSCRIPTION copy_data option.

\n
\n
\n

Options

\n

pg_createsubscriber accepts the following command-line arguments:

\n
\n
\n
-a
--all
\n
\n

Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database, --publication, --replication-slot, or --subscription.

\n
\n
-d dbname
--database=dbname
\n
\n

The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a. If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.

\n
\n
-D datadir
--pgdata=datadir
\n
\n

The target directory that contains a cluster directory from a physical replica.

\n
\n
-l directory
--logdir=directory
\n
\n

Specify the name of the log directory. A new directory is created with this name if it does not exist. A subdirectory with a timestamp indicating the time at which pg_createsubscriber was run will be created. The following two log files will be created in the subdirectory.

\n
\n
    \n
  • \n

    pg_createsubscriber_server.log which captures logs related to stopping and starting the standby server,

    \n
    • \n
  • \n

    pg_createsubscriber_internal.log which captures internal diagnostic output (validations, checks, etc.)

    \n
    • \n
\n
\n

By default, the umask is set to 077 so that the log files are only readable by the user running the command. However, if the target data directory is configured to allow group-read access, pg_createsubscriber will adjust the log file permissions to match. This ensures that the log file security remains consistent with the database cluster itself.

\n
\n
-n
--dry-run
\n
\n

Do everything except actually modifying the target directory.

\n
\n
-p port
--subscriber-port=port
\n
\n

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.

\n
\n
-P connstr
--publisher-server=connstr
\n
\n

The connection string to the publisher. For details see Section\u00a032.1.1.

\n
\n
-s dir
--socketdir=dir
\n
\n

The directory to use for postmaster sockets on target server. The default is current directory.

\n
\n
-t seconds
--recovery-timeout=seconds
\n
\n

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

\n
\n
-T
--enable-two-phase
\n
\n

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.

\n
\n
-U username
--subscriber-username=username
\n
\n

The user name to connect as on target server. Defaults to the current operating system user name.

\n
\n
-v
--verbose
\n
\n

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.

\n
\n
--clean=objtype
\n
\n

Drop all objects of the specified type from specified databases on the target server.

\n
\n
    \n
  • \n

    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.

    \n
    • \n
\n
\n

The objects selected to be dropped are individually logged, including during a --dry-run. There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump.

\n
\n
--config-file=filename
\n
\n

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.

\n
\n
--publication=name
\n
\n

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. This option cannot be used together with --all.

\n

If a specified publication already exists on the publisher, it is reused. It is useful to partially replicate the database if the specified publication includes a list of tables. If the publication does not exist, it is automatically created with FOR ALL TABLES. Use --dry-run option to preview which publications will be reused and which will be created.

\n
\n
--replication-slot=name
\n
\n

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. This option cannot be used together with --all.

\n
\n
--subscription=name
\n
\n

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. This option cannot be used together with --all.

\n
\n
-V
--version
\n
\n

Print the pg_createsubscriber version and exit.

\n
\n
-?
--help
\n
\n

Show help about pg_createsubscriber command line arguments, and exit.

\n
\n
\n
\n
\n
\n

Notes

\n
\n

Prerequisites

\n

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

\n

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 --enable-two-phase switch then you will also need to set the max_prepared_transactions appropriately.

\n

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 replica or 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.

\n
\n
\n

Warnings

\n

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.

\n

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

\n

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.

\n

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.

\n

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

\n

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

\n

Unless the --enable-two-phase 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 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.

\n

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.

\n

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.

\n
\n
\n

How It Works

\n

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:

\n
\n
    \n
  1. \n

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

    \n
    2. \n
  2. \n

    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.

    \n
    3. \n
  3. \n

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

    \n
    4. \n
  4. \n

    Write recovery parameters into the separate configuration file pg_createsubscriber.conf that is included from postgresql.auto.conf using include_if_exists in the target data directory, then 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. Upon completion, the included configuration file is renamed to pg_createsubscriber.conf.disabled so as it is no longer loaded on subsequent restarts.

    \n
    5. \n
  5. \n

    Create a subscription for each specified database on the target server. If the --subscription option is not specified, the subscription has the following name pattern: \u201cpg_createsubscriber_%u_%x\u201d (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.

    \n
    6. \n
  6. \n

    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.

    \n
    7. \n
  7. \n

    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.

    \n
    8. \n
  8. \n

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

    \n
    9. \n
  9. \n

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

    \n
    10. \n
  10. \n

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

    \n
    11. \n
  11. \n

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

    \n
    12. \n
\n
\n
\n
\n
\n

Examples

\n

To create a logical replica for databases hr and finance from a physical replica at foo:

\n
$ pg_createsubscriber -D /usr/local/pgsql/data -P \"host=foo\" -d hr -d finance\n
\n
\n
\n

See Also

pg_basebackup\n
\n
", "manual_path": "app-pgcreatesubscriber.html", "comparison_data": {"options": [{"names": ["-a", "--all"], "signature": "-a --all", "description": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription ."}, {"names": ["-d dbname", "--database= dbname"], "signature": "-d dbname --database= dbname", "description": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported."}, {"names": ["-D datadir", "--pgdata= datadir"], "signature": "-D datadir --pgdata= datadir", "description": "The target directory that contains a cluster directory from a physical replica."}, {"names": ["-l directory", "--logdir= directory"], "signature": "-l directory --logdir= directory", "description": "Specify the name of the log directory. A new directory is created with this name if it does not exist. A subdirectory with a timestamp indicating the time at which pg_createsubscriber was run will be created. The following two log files will be created in the subdirectory. pg_createsubscriber_server.log which captures logs related to stopping and starting the standby server, pg_createsubscriber_internal.log which captures internal diagnostic output (validations, checks, etc.) By default, the umask is set to 077 so that the log files are only readable by the user running the command. However, if the target data directory is configured to allow group-read access, pg_createsubscriber will adjust the log file permissions to match. This ensures that the log file security remains consistent with the database cluster itself."}, {"names": ["-n", "--dry-run"], "signature": "-n --dry-run", "description": "Do everything except actually modifying the target directory."}, {"names": ["-p port", "--subscriber-port= port"], "signature": "-p port --subscriber-port= port", "description": "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."}, {"names": ["-P connstr", "--publisher-server= connstr"], "signature": "-P connstr --publisher-server= connstr", "description": "The connection string to the publisher. For details see Section 32.1.1 ."}, {"names": ["-s dir", "--socketdir= dir"], "signature": "-s dir --socketdir= dir", "description": "The directory to use for postmaster sockets on target server. The default is current directory."}, {"names": ["-t seconds", "--recovery-timeout= seconds"], "signature": "-t seconds --recovery-timeout= seconds", "description": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0."}, {"names": ["-T", "--enable-two-phase"], "signature": "-T --enable-two-phase", "description": "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 ."}, {"names": ["-U username", "--subscriber-username= username"], "signature": "-U username --subscriber-username= username", "description": "The user name to connect as on target server. Defaults to the current operating system user name."}, {"names": ["-v", "--verbose"], "signature": "-v --verbose", "description": "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."}, {"names": ["--clean= objtype"], "signature": "--clean= objtype", "description": "Drop all objects of the specified type from specified databases on the target server. 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. The objects selected to be dropped are individually logged, including during a --dry-run . There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump ."}, {"names": ["--config-file= filename"], "signature": "--config-file= filename", "description": "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."}, {"names": ["--publication= name"], "signature": "--publication= name", "description": "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. This option cannot be used together with --all . If a specified publication already exists on the publisher, it is reused. It is useful to partially replicate the database if the specified publication includes a list of tables. If the publication does not exist, it is automatically created with FOR ALL TABLES . Use --dry-run option to preview which publications will be reused and which will be created."}, {"names": ["--replication-slot= name"], "signature": "--replication-slot= name", "description": "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. This option cannot be used together with --all ."}, {"names": ["--subscription= name"], "signature": "--subscription= name", "description": "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. This option cannot be used together with --all ."}, {"names": ["-V", "--version"], "signature": "-V --version", "description": "Print the pg_createsubscriber version and exit."}, {"names": ["-?", "--help"], "signature": "-? --help", "description": "Show help about pg_createsubscriber command line arguments, and exit."}], "synopsis": ["pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr"], "environment": []}, "comparison_hash": "b4807e2c2ddbf51e9e6cdd5c6c5a1cd58c95530174f72f8e053b38ce95c721d8"}, "20": {"facts": [{"label": "Documented executable", "value": "pg_createsubscriber"}, {"label": "Executable version", "value": "20devel"}, {"label": "Reference inventory", "value": "Server applications"}, {"label": "Option definition groups", "value": "19"}], "tables": [{"key": "options", "rows": [{"summary": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription .", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-a --all"}}, {"summary": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-d dbname --database= dbname"}}, {"summary": "The target directory that contains a cluster directory from a physical replica.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-D datadir --pgdata= datadir"}}, {"summary": "Specify the name of the log directory. A new directory is created with this name if it does not exist. A subdirectory with a timestamp indicating the time at which pg_createsubscriber was run will be created. The following two log files will be created in the subdirectory.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-l directory --logdir= directory"}}, {"summary": "Do everything except actually modifying the target directory.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-n --dry-run"}}, {"summary": "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.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-p port --subscriber-port= port"}}, {"summary": "The connection string to the publisher. For details see Section 32.1.1 .", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-P connstr --publisher-server= connstr"}}, {"summary": "The directory to use for postmaster sockets on target server. The default is current directory.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-s dir --socketdir= dir"}}, {"summary": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-t seconds --recovery-timeout= seconds"}}, {"summary": "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 .", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-T --enable-two-phase"}}, {"summary": "The user name to connect as on target server. Defaults to the current operating system user name.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-U username --subscriber-username= username"}}, {"summary": "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.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-v --verbose"}}, {"summary": "Drop all objects of the specified type from specified databases on the target server.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "--clean= objtype"}}, {"summary": "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.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "--config-file= filename"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "--publication= name"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "--replication-slot= name"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "--subscription= name"}}, {"summary": "Print the pg_createsubscriber version and exit.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-V --version"}}, {"summary": "Show help about pg_createsubscriber command line arguments, and exit.", "signature": {"url": "/docs/devel/app-pgcreatesubscriber.html", "text": "-? --help"}}], "title": "Documented options", "columns": [{"key": "signature", "label": "Option and arguments"}, {"key": "summary", "label": "Description"}]}], "options": [{"names": ["-a", "--all"], "summary": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription .", "signature": "-a --all", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription ."}, {"names": ["-d dbname", "--database= dbname"], "summary": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.", "signature": "-d dbname --database= dbname", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported."}, {"names": ["-D datadir", "--pgdata= datadir"], "summary": "The target directory that contains a cluster directory from a physical replica.", "signature": "-D datadir --pgdata= datadir", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "The target directory that contains a cluster directory from a physical replica."}, {"names": ["-l directory", "--logdir= directory"], "summary": "Specify the name of the log directory. A new directory is created with this name if it does not exist. A subdirectory with a timestamp indicating the time at which pg_createsubscriber was run will be created. The following two log files will be created in the subdirectory.", "signature": "-l directory --logdir= directory", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "Specify the name of the log directory. A new directory is created with this name if it does not exist. A subdirectory with a timestamp indicating the time at which pg_createsubscriber was run will be created. The following two log files will be created in the subdirectory. pg_createsubscriber_server.log which captures logs related to stopping and starting the standby server, pg_createsubscriber_internal.log which captures internal diagnostic output (validations, checks, etc.) By default, the umask is set to 077 so that the log files are only readable by the user running the command. However, if the target data directory is configured to allow group-read access, pg_createsubscriber will adjust the log file permissions to match. This ensures that the log file security remains consistent with the database cluster itself."}, {"names": ["-n", "--dry-run"], "summary": "Do everything except actually modifying the target directory.", "signature": "-n --dry-run", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "Do everything except actually modifying the target directory."}, {"names": ["-p port", "--subscriber-port= port"], "summary": "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.", "signature": "-p port --subscriber-port= port", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["-P connstr", "--publisher-server= connstr"], "summary": "The connection string to the publisher. For details see Section 32.1.1 .", "signature": "-P connstr --publisher-server= connstr", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "The connection string to the publisher. For details see Section 32.1.1 ."}, {"names": ["-s dir", "--socketdir= dir"], "summary": "The directory to use for postmaster sockets on target server. The default is current directory.", "signature": "-s dir --socketdir= dir", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "The directory to use for postmaster sockets on target server. The default is current directory."}, {"names": ["-t seconds", "--recovery-timeout= seconds"], "summary": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.", "signature": "-t seconds --recovery-timeout= seconds", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0."}, {"names": ["-T", "--enable-two-phase"], "summary": "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 .", "signature": "-T --enable-two-phase", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "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 ."}, {"names": ["-U username", "--subscriber-username= username"], "summary": "The user name to connect as on target server. Defaults to the current operating system user name.", "signature": "-U username --subscriber-username= username", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "The user name to connect as on target server. Defaults to the current operating system user name."}, {"names": ["-v", "--verbose"], "summary": "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.", "signature": "-v --verbose", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--clean= objtype"], "summary": "Drop all objects of the specified type from specified databases on the target server.", "signature": "--clean= objtype", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "Drop all objects of the specified type from specified databases on the target server. 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. The objects selected to be dropped are individually logged, including during a --dry-run . There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump ."}, {"names": ["--config-file= filename"], "summary": "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.", "signature": "--config-file= filename", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--publication= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--publication= name", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all . If a specified publication already exists on the publisher, it is reused. It is useful to partially replicate the database if the specified publication includes a list of tables. If the publication does not exist, it is automatically created with FOR ALL TABLES . Use --dry-run option to preview which publications will be reused and which will be created."}, {"names": ["--replication-slot= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--replication-slot= name", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all ."}, {"names": ["--subscription= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--subscription= name", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all ."}, {"names": ["-V", "--version"], "summary": "Print the pg_createsubscriber version and exit.", "signature": "-V --version", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "Print the pg_createsubscriber version and exit."}, {"names": ["-?", "--help"], "summary": "Show help about pg_createsubscriber command line arguments, and exit.", "signature": "-? --help", "source_url": "/docs/devel/app-pgcreatesubscriber.html", "description": "Show help about pg_createsubscriber command line arguments, and exit."}], "related": [], "release": {"ref": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "label": "20devel", "major": "20", "channel": "devel", "manifest": {"pdf": {"A4": {"url": "/files/documentation/pdf/20/postgresql-20-A4.pdf", "bytes": 16030631, "pages": 3052, "sha256": "bd5d82c0ce38fc18f92a0447818a91a193a261776bca1c37564bf9a683e177d0", "built_at": "2026-09-28"}, "US": {"url": "/files/documentation/pdf/20/postgresql-20-US.pdf", "bytes": 15936613, "pages": 3223, "sha256": "d97d9e0db479a02f4234b175f50fcad70c3661619afc8d6df9b9437882e3c299", "built_at": "2026-09-28"}}, "tree": "0", "index": "index.html", "major": "20", "pages": 1156, "release": "20devel", "source_url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "svg_assets": 6, "source_mode": "en SGML built with pinned official archive", "source_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41", "source_snapshot_utc": "26-Sep-2026 20:22"}, "revision": "2eba5e0fd4c3bffb2803247b6cd537878e9d6ee5a6dfbe3c50ece8b421b80918", "evidence_kind": "English manual and source declarations", "source_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, "sources": [{"url": "/docs/devel/app-pgcreatesubscriber.html", "file": "app-pgcreatesubscriber.html", "label": "20devel English manual \u00b7 app-pgcreatesubscriber.html", "anchor": "", "sha256": "55e90fc7b3d88304ec0ec7d8793a2f0993d82a397b09d5436ec2637e3aa47ba3"}, {"url": "/docs/devel/reference-server.html", "file": "reference-server.html", "label": "Server applications inventory", "anchor": "", "sha256": "5532c1ebfb8c6f310301be63a28daf0ae212e2a78025bea61a1bdca99e02c43d"}], "sections": [], "synopsis": ["pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr"], "signature": "pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr", "description": ["pg_createsubscriber \u2014 convert a physical replica into a new logical replica"], "environment": [], "manual_html": "
\n
\n
\n

pg_createsubscriber

\n

pg_createsubscriber \u2014 convert a physical replica into a new logical replica

\n
\n
\n

Synopsis

\n
\n

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

\n
\n
\n
\n

Description

\n

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.

\n

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.

\n

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 CREATE SUBSCRIPTION copy_data option.

\n
\n
\n

Options

\n

pg_createsubscriber accepts the following command-line arguments:

\n
\n
\n
-a
--all
\n
\n

Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database, --publication, --replication-slot, or --subscription.

\n
\n
-d dbname
--database=dbname
\n
\n

The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a. If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.

\n
\n
-D datadir
--pgdata=datadir
\n
\n

The target directory that contains a cluster directory from a physical replica.

\n
\n
-l directory
--logdir=directory
\n
\n

Specify the name of the log directory. A new directory is created with this name if it does not exist. A subdirectory with a timestamp indicating the time at which pg_createsubscriber was run will be created. The following two log files will be created in the subdirectory.

\n
\n
    \n
  • \n

    pg_createsubscriber_server.log which captures logs related to stopping and starting the standby server,

    \n
    • \n
  • \n

    pg_createsubscriber_internal.log which captures internal diagnostic output (validations, checks, etc.)

    \n
    • \n
\n
\n

By default, the umask is set to 077 so that the log files are only readable by the user running the command. However, if the target data directory is configured to allow group-read access, pg_createsubscriber will adjust the log file permissions to match. This ensures that the log file security remains consistent with the database cluster itself.

\n
\n
-n
--dry-run
\n
\n

Do everything except actually modifying the target directory.

\n
\n
-p port
--subscriber-port=port
\n
\n

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.

\n
\n
-P connstr
--publisher-server=connstr
\n
\n

The connection string to the publisher. For details see Section\u00a032.1.1.

\n
\n
-s dir
--socketdir=dir
\n
\n

The directory to use for postmaster sockets on target server. The default is current directory.

\n
\n
-t seconds
--recovery-timeout=seconds
\n
\n

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

\n
\n
-T
--enable-two-phase
\n
\n

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.

\n
\n
-U username
--subscriber-username=username
\n
\n

The user name to connect as on target server. Defaults to the current operating system user name.

\n
\n
-v
--verbose
\n
\n

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.

\n
\n
--clean=objtype
\n
\n

Drop all objects of the specified type from specified databases on the target server.

\n
\n
    \n
  • \n

    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.

    \n
    • \n
\n
\n

The objects selected to be dropped are individually logged, including during a --dry-run. There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump.

\n
\n
--config-file=filename
\n
\n

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.

\n
\n
--publication=name
\n
\n

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. This option cannot be used together with --all.

\n

If a specified publication already exists on the publisher, it is reused. It is useful to partially replicate the database if the specified publication includes a list of tables. If the publication does not exist, it is automatically created with FOR ALL TABLES. Use --dry-run option to preview which publications will be reused and which will be created.

\n
\n
--replication-slot=name
\n
\n

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. This option cannot be used together with --all.

\n
\n
--subscription=name
\n
\n

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. This option cannot be used together with --all.

\n
\n
-V
--version
\n
\n

Print the pg_createsubscriber version and exit.

\n
\n
-?
--help
\n
\n

Show help about pg_createsubscriber command line arguments, and exit.

\n
\n
\n
\n
\n
\n

Notes

\n
\n

Prerequisites

\n

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

\n

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 --enable-two-phase switch then you will also need to set the max_prepared_transactions appropriately.

\n

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 replica or 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.

\n
\n
\n

Warnings

\n

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.

\n

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

\n

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.

\n

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.

\n

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

\n

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

\n

Unless the --enable-two-phase 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 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.

\n

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.

\n

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.

\n
\n
\n

How It Works

\n

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:

\n
\n
    \n
  1. \n

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

    \n
    2. \n
  2. \n

    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.

    \n
    3. \n
  3. \n

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

    \n
    4. \n
  4. \n

    Write recovery parameters into the separate configuration file pg_createsubscriber.conf that is included from postgresql.auto.conf using include_if_exists in the target data directory, then 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. Upon completion, the included configuration file is renamed to pg_createsubscriber.conf.disabled so as it is no longer loaded on subsequent restarts.

    \n
    5. \n
  5. \n

    Create a subscription for each specified database on the target server. If the --subscription option is not specified, the subscription has the following name pattern: \u201cpg_createsubscriber_%u_%x\u201d (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.

    \n
    6. \n
  6. \n

    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.

    \n
    7. \n
  7. \n

    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.

    \n
    8. \n
  8. \n

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

    \n
    9. \n
  9. \n

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

    \n
    10. \n
  10. \n

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

    \n
    11. \n
  11. \n

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

    \n
    12. \n
\n
\n
\n
\n
\n

Examples

\n

To create a logical replica for databases hr and finance from a physical replica at foo:

\n
$ pg_createsubscriber -D /usr/local/pgsql/data -P \"host=foo\" -d hr -d finance\n
\n
\n
\n

See Also

pg_basebackup\n
\n
", "manual_path": "app-pgcreatesubscriber.html", "comparison_data": {"options": [{"names": ["-a", "--all"], "signature": "-a --all", "description": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription ."}, {"names": ["-d dbname", "--database= dbname"], "signature": "-d dbname --database= dbname", "description": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported."}, {"names": ["-D datadir", "--pgdata= datadir"], "signature": "-D datadir --pgdata= datadir", "description": "The target directory that contains a cluster directory from a physical replica."}, {"names": ["-l directory", "--logdir= directory"], "signature": "-l directory --logdir= directory", "description": "Specify the name of the log directory. A new directory is created with this name if it does not exist. A subdirectory with a timestamp indicating the time at which pg_createsubscriber was run will be created. The following two log files will be created in the subdirectory. pg_createsubscriber_server.log which captures logs related to stopping and starting the standby server, pg_createsubscriber_internal.log which captures internal diagnostic output (validations, checks, etc.) By default, the umask is set to 077 so that the log files are only readable by the user running the command. However, if the target data directory is configured to allow group-read access, pg_createsubscriber will adjust the log file permissions to match. This ensures that the log file security remains consistent with the database cluster itself."}, {"names": ["-n", "--dry-run"], "signature": "-n --dry-run", "description": "Do everything except actually modifying the target directory."}, {"names": ["-p port", "--subscriber-port= port"], "signature": "-p port --subscriber-port= port", "description": "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."}, {"names": ["-P connstr", "--publisher-server= connstr"], "signature": "-P connstr --publisher-server= connstr", "description": "The connection string to the publisher. For details see Section 32.1.1 ."}, {"names": ["-s dir", "--socketdir= dir"], "signature": "-s dir --socketdir= dir", "description": "The directory to use for postmaster sockets on target server. The default is current directory."}, {"names": ["-t seconds", "--recovery-timeout= seconds"], "signature": "-t seconds --recovery-timeout= seconds", "description": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0."}, {"names": ["-T", "--enable-two-phase"], "signature": "-T --enable-two-phase", "description": "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 ."}, {"names": ["-U username", "--subscriber-username= username"], "signature": "-U username --subscriber-username= username", "description": "The user name to connect as on target server. Defaults to the current operating system user name."}, {"names": ["-v", "--verbose"], "signature": "-v --verbose", "description": "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."}, {"names": ["--clean= objtype"], "signature": "--clean= objtype", "description": "Drop all objects of the specified type from specified databases on the target server. 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. The objects selected to be dropped are individually logged, including during a --dry-run . There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump ."}, {"names": ["--config-file= filename"], "signature": "--config-file= filename", "description": "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."}, {"names": ["--publication= name"], "signature": "--publication= name", "description": "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. This option cannot be used together with --all . If a specified publication already exists on the publisher, it is reused. It is useful to partially replicate the database if the specified publication includes a list of tables. If the publication does not exist, it is automatically created with FOR ALL TABLES . Use --dry-run option to preview which publications will be reused and which will be created."}, {"names": ["--replication-slot= name"], "signature": "--replication-slot= name", "description": "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. This option cannot be used together with --all ."}, {"names": ["--subscription= name"], "signature": "--subscription= name", "description": "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. This option cannot be used together with --all ."}, {"names": ["-V", "--version"], "signature": "-V --version", "description": "Print the pg_createsubscriber version and exit."}, {"names": ["-?", "--help"], "signature": "-? --help", "description": "Show help about pg_createsubscriber command line arguments, and exit."}], "synopsis": ["pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr"], "environment": []}, "comparison_hash": "b4807e2c2ddbf51e9e6cdd5c6c5a1cd58c95530174f72f8e053b38ce95c721d8"}}}, "snapshot": {"facts": [{"label": "Documented executable", "value": "pg_createsubscriber"}, {"label": "Executable version", "value": "18.6"}, {"label": "Reference inventory", "value": "Server applications"}, {"label": "Option definition groups", "value": "18"}], "tables": [{"key": "options", "rows": [{"summary": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-a --all"}}, {"summary": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-d dbname --database= dbname"}}, {"summary": "The target directory that contains a cluster directory from a physical replica.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-D directory --pgdata= directory"}}, {"summary": "Do everything except actually modifying the target directory.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-n --dry-run"}}, {"summary": "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.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-p port --subscriber-port= port"}}, {"summary": "The connection string to the publisher. For details see Section 32.1.1 .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-P connstr --publisher-server= connstr"}}, {"summary": "The directory to use for postmaster sockets on target server. The default is current directory.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-s dir --socketdir= dir"}}, {"summary": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-t seconds --recovery-timeout= seconds"}}, {"summary": "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 .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-T --enable-two-phase"}}, {"summary": "The user name to connect as on target server. Defaults to the current operating system user name.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-U username --subscriber-username= username"}}, {"summary": "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.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-v --verbose"}}, {"summary": "Drop all objects of the specified type from specified databases on the target server.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "--clean= objtype"}}, {"summary": "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.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "--config-file= filename"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "--publication= name"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "--replication-slot= name"}}, {"summary": "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. This option cannot be used together with --all .", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "--subscription= name"}}, {"summary": "Print the pg_createsubscriber version and exit.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-V --version"}}, {"summary": "Show help about pg_createsubscriber command line arguments, and exit.", "signature": {"url": "/docs/18/app-pgcreatesubscriber.html", "text": "-? --help"}}], "title": "Documented options", "columns": [{"key": "signature", "label": "Option and arguments"}, {"key": "summary", "label": "Description"}]}], "options": [{"names": ["-a", "--all"], "summary": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription .", "signature": "-a --all", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription ."}, {"names": ["-d dbname", "--database= dbname"], "summary": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.", "signature": "-d dbname --database= dbname", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported."}, {"names": ["-D directory", "--pgdata= directory"], "summary": "The target directory that contains a cluster directory from a physical replica.", "signature": "-D directory --pgdata= directory", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The target directory that contains a cluster directory from a physical replica."}, {"names": ["-n", "--dry-run"], "summary": "Do everything except actually modifying the target directory.", "signature": "-n --dry-run", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "Do everything except actually modifying the target directory."}, {"names": ["-p port", "--subscriber-port= port"], "summary": "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.", "signature": "-p port --subscriber-port= port", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["-P connstr", "--publisher-server= connstr"], "summary": "The connection string to the publisher. For details see Section 32.1.1 .", "signature": "-P connstr --publisher-server= connstr", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The connection string to the publisher. For details see Section 32.1.1 ."}, {"names": ["-s dir", "--socketdir= dir"], "summary": "The directory to use for postmaster sockets on target server. The default is current directory.", "signature": "-s dir --socketdir= dir", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The directory to use for postmaster sockets on target server. The default is current directory."}, {"names": ["-t seconds", "--recovery-timeout= seconds"], "summary": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.", "signature": "-t seconds --recovery-timeout= seconds", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0."}, {"names": ["-T", "--enable-two-phase"], "summary": "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 .", "signature": "-T --enable-two-phase", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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 ."}, {"names": ["-U username", "--subscriber-username= username"], "summary": "The user name to connect as on target server. Defaults to the current operating system user name.", "signature": "-U username --subscriber-username= username", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "The user name to connect as on target server. Defaults to the current operating system user name."}, {"names": ["-v", "--verbose"], "summary": "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.", "signature": "-v --verbose", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--clean= objtype"], "summary": "Drop all objects of the specified type from specified databases on the target server.", "signature": "--clean= objtype", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "Drop all objects of the specified type from specified databases on the target server. 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. The objects selected to be dropped are individually logged, including during a --dry-run . There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump ."}, {"names": ["--config-file= filename"], "summary": "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.", "signature": "--config-file= filename", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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."}, {"names": ["--publication= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--publication= name", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all ."}, {"names": ["--replication-slot= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--replication-slot= name", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all ."}, {"names": ["--subscription= name"], "summary": "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. This option cannot be used together with --all .", "signature": "--subscription= name", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "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. This option cannot be used together with --all ."}, {"names": ["-V", "--version"], "summary": "Print the pg_createsubscriber version and exit.", "signature": "-V --version", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "Print the pg_createsubscriber version and exit."}, {"names": ["-?", "--help"], "summary": "Show help about pg_createsubscriber command line arguments, and exit.", "signature": "-? --help", "source_url": "/docs/18/app-pgcreatesubscriber.html", "description": "Show help about pg_createsubscriber command line arguments, and exit."}], "related": [], "release": {"ref": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "label": "18.6", "major": "18", "channel": "stable", "manifest": {"pdf": {"A4": {"url": "/files/documentation/pdf/18/postgresql-18-A4.pdf", "bytes": 15865106, "pages": 3154, "sha256": "19512c405da53f9f7fcf0abba359223aa65f021be025bf3411381918f92e3190", "built_at": "2026-09-26"}, "US": {"url": "/files/documentation/pdf/18/postgresql-18-US.pdf", "bytes": 15748059, "pages": 3328, "sha256": "facbe6c229e598b872d3d98bef53308f46e06746006fa4590de9a7de9dd46319", "built_at": "2026-09-26"}}, "tree": "18", "index": "index.html", "major": "18", "pages": 1148, "release": "18.6", "source_url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "svg_assets": 3, "source_mode": "en SGML built with pinned official archive", "source_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, "revision": "ee8d1a3612338fd9adf250730cb640fcc5233b5491337cc00a316a44e3a0b9f8", "evidence_kind": "English manual and source declarations", "source_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, "sources": [{"url": "/docs/18/app-pgcreatesubscriber.html", "file": "app-pgcreatesubscriber.html", "label": "18.6 English manual \u00b7 app-pgcreatesubscriber.html", "anchor": "", "sha256": "fb0e5cd7f37044410e06deb436d7b70a1bceffbd849ce10f468351d9cf11d385"}, {"url": "/docs/18/reference-server.html", "file": "reference-server.html", "label": "Server applications inventory", "anchor": "", "sha256": "e9f8bbcc8e3641fadd1d20991e8aad6b532155b786ba5a1b7b364ec4f1a5bef3"}], "sections": [], "synopsis": ["pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr"], "signature": "pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr", "description": ["pg_createsubscriber \u2014 convert a physical replica into a new logical replica"], "environment": [], "manual_html": "
\n
\n
\n

pg_createsubscriber

\n

pg_createsubscriber \u2014 convert a physical replica into a new logical replica

\n
\n
\n

Synopsis

\n
\n

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

\n
\n
\n
\n

Description

\n

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.

\n

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.

\n

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 CREATE SUBSCRIPTION copy_data option.

\n
\n
\n

Options

\n

pg_createsubscriber accepts the following command-line arguments:

\n
\n
\n
-a
--all
\n
\n

Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database, --publication, --replication-slot, or --subscription.

\n
\n
-d dbname
--database=dbname
\n
\n

The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a. If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported.

\n
\n
-D directory
--pgdata=directory
\n
\n

The target directory that contains a cluster directory from a physical replica.

\n
\n
-n
--dry-run
\n
\n

Do everything except actually modifying the target directory.

\n
\n
-p port
--subscriber-port=port
\n
\n

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.

\n
\n
-P connstr
--publisher-server=connstr
\n
\n

The connection string to the publisher. For details see Section\u00a032.1.1.

\n
\n
-s dir
--socketdir=dir
\n
\n

The directory to use for postmaster sockets on target server. The default is current directory.

\n
\n
-t seconds
--recovery-timeout=seconds
\n
\n

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

\n
\n
-T
--enable-two-phase
\n
\n

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.

\n
\n
-U username
--subscriber-username=username
\n
\n

The user name to connect as on target server. Defaults to the current operating system user name.

\n
\n
-v
--verbose
\n
\n

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.

\n
\n
--clean=objtype
\n
\n

Drop all objects of the specified type from specified databases on the target server.

\n
\n
    \n
  • \n

    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.

    \n
    • \n
\n
\n

The objects selected to be dropped are individually logged, including during a --dry-run. There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump.

\n
\n
--config-file=filename
\n
\n

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.

\n
\n
--publication=name
\n
\n

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. This option cannot be used together with --all.

\n
\n
--replication-slot=name
\n
\n

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. This option cannot be used together with --all.

\n
\n
--subscription=name
\n
\n

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. This option cannot be used together with --all.

\n
\n
-V
--version
\n
\n

Print the pg_createsubscriber version and exit.

\n
\n
-?
--help
\n
\n

Show help about pg_createsubscriber command line arguments, and exit.

\n
\n
\n
\n
\n
\n

Notes

\n
\n

Prerequisites

\n

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

\n

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 --enable-two-phase switch then you will also need to set the max_prepared_transactions appropriately.

\n

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.

\n
\n
\n

Warnings

\n

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.

\n

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

\n

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.

\n

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.

\n

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

\n

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

\n

Unless the --enable-two-phase 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 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.

\n

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.

\n

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.

\n
\n
\n

How It Works

\n

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:

\n
\n
    \n
  1. \n

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

    \n
    2. \n
  2. \n

    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.

    \n
    3. \n
  3. \n

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

    \n
    4. \n
  4. \n

    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.

    \n
    5. \n
  5. \n

    Create a subscription for each specified database on the target server. If the --subscription option is not specified, the subscription has the following name pattern: \u201cpg_createsubscriber_%u_%x\u201d (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.

    \n
    6. \n
  6. \n

    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.

    \n
    7. \n
  7. \n

    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.

    \n
    8. \n
  8. \n

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

    \n
    9. \n
  9. \n

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

    \n
    10. \n
  10. \n

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

    \n
    11. \n
  11. \n

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

    \n
    12. \n
\n
\n
\n
\n
\n

Examples

\n

To create a logical replica for databases hr and finance from a physical replica at foo:

\n
$ pg_createsubscriber -D /usr/local/pgsql/data -P \"host=foo\" -d hr -d finance\n
\n
\n
\n

See Also

pg_basebackup\n
\n
", "manual_path": "app-pgcreatesubscriber.html", "comparison_data": {"options": [{"names": ["-a", "--all"], "signature": "-a --all", "description": "Create one subscription per database on the target server. Exceptions are template databases and databases that don't allow connections. To discover the list of all databases, connect to the source server using the database name specified in the --publisher-server 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 --database , --publication , --replication-slot , or --subscription ."}, {"names": ["-d dbname", "--database= dbname"], "signature": "-d dbname --database= dbname", "description": "The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches. This option cannot be used together with -a . If -d option is not provided, the database name will be obtained from -P option. If the database name is not specified in either the -d option, or the -P option, and -a option is not specified, an error will be reported."}, {"names": ["-D directory", "--pgdata= directory"], "signature": "-D directory --pgdata= directory", "description": "The target directory that contains a cluster directory from a physical replica."}, {"names": ["-n", "--dry-run"], "signature": "-n --dry-run", "description": "Do everything except actually modifying the target directory."}, {"names": ["-p port", "--subscriber-port= port"], "signature": "-p port --subscriber-port= port", "description": "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."}, {"names": ["-P connstr", "--publisher-server= connstr"], "signature": "-P connstr --publisher-server= connstr", "description": "The connection string to the publisher. For details see Section 32.1.1 ."}, {"names": ["-s dir", "--socketdir= dir"], "signature": "-s dir --socketdir= dir", "description": "The directory to use for postmaster sockets on target server. The default is current directory."}, {"names": ["-t seconds", "--recovery-timeout= seconds"], "signature": "-t seconds --recovery-timeout= seconds", "description": "The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0."}, {"names": ["-T", "--enable-two-phase"], "signature": "-T --enable-two-phase", "description": "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 ."}, {"names": ["-U username", "--subscriber-username= username"], "signature": "-U username --subscriber-username= username", "description": "The user name to connect as on target server. Defaults to the current operating system user name."}, {"names": ["-v", "--verbose"], "signature": "-v --verbose", "description": "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."}, {"names": ["--clean= objtype"], "signature": "--clean= objtype", "description": "Drop all objects of the specified type from specified databases on the target server. 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. The objects selected to be dropped are individually logged, including during a --dry-run . There is no opportunity to affect or stop the dropping of the selected objects, so consider taking a backup of them using pg_dump ."}, {"names": ["--config-file= filename"], "signature": "--config-file= filename", "description": "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."}, {"names": ["--publication= name"], "signature": "--publication= name", "description": "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. This option cannot be used together with --all ."}, {"names": ["--replication-slot= name"], "signature": "--replication-slot= name", "description": "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. This option cannot be used together with --all ."}, {"names": ["--subscription= name"], "signature": "--subscription= name", "description": "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. This option cannot be used together with --all ."}, {"names": ["-V", "--version"], "signature": "-V --version", "description": "Print the pg_createsubscriber version and exit."}, {"names": ["-?", "--help"], "signature": "-? --help", "description": "Show help about pg_createsubscriber command line arguments, and exit."}], "synopsis": ["pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr"], "environment": []}, "comparison_hash": "ab2f9ba9dc71a377cdb3a682c66b1a84f3cb3ae29cb8c1b178c5acc418bc4441"}, "comparison": {"left": "16", "right": "17", "status": "added", "diff": "--- PostgreSQL 16\n+++ PostgreSQL 17\n@@ -1 +1,124 @@\n-Not recorded in this version\n+{\n+ \"environment\": [],\n+ \"options\": [\n+ {\n+ \"description\": \"The name of the database in which to create a subscription. Multiple databases can be selected by writing multiple -d switches.\",\n+ \"names\": [\n+ \"-d dbname\",\n+ \"--database= dbname\"\n+ ],\n+ \"signature\": \"-d dbname --database= dbname\"\n+ },\n+ {\n+ \"description\": \"The target directory that contains a cluster directory from a physical replica.\",\n+ \"names\": [\n+ \"-D directory\",\n+ \"--pgdata= directory\"\n+ ],\n+ \"signature\": \"-D directory --pgdata= directory\"\n+ },\n+ {\n+ \"description\": \"Do everything except actually modifying the target directory.\",\n+ \"names\": [\n+ \"-n\",\n+ \"--dry-run\"\n+ ],\n+ \"signature\": \"-n --dry-run\"\n+ },\n+ {\n+ \"description\": \"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.\",\n+ \"names\": [\n+ \"-p port\",\n+ \"--subscriber-port= port\"\n+ ],\n+ \"signature\": \"-p port --subscriber-port= port\"\n+ },\n+ {\n+ \"description\": \"The connection string to the publisher. For details see Section 32.1.1 .\",\n+ \"names\": [\n+ \"-P connstr\",\n+ \"--publisher-server= connstr\"\n+ ],\n+ \"signature\": \"-P connstr --publisher-server= connstr\"\n+ },\n+ {\n+ \"description\": \"The directory to use for postmaster sockets on target server. The default is current directory.\",\n+ \"names\": [\n+ \"-s dir\",\n+ \"--socketdir= dir\"\n+ ],\n+ \"signature\": \"-s dir --socketdir= dir\"\n+ },\n+ {\n+ \"description\": \"The maximum number of seconds to wait for recovery to end. Setting to 0 disables. The default is 0.\",\n+ \"names\": [\n+ \"-t seconds\",\n+ \"--recovery-timeout= seconds\"\n+ ],\n+ \"signature\": \"-t seconds --recovery-timeout= seconds\"\n+ },\n+ {\n+ \"description\": \"The user name to connect as on target server. Defaults to the current operating system user name.\",\n+ \"names\": [\n+ \"-U username\",\n+ \"--subscriber-username= username\"\n+ ],\n+ \"signature\": \"-U username --subscriber-username= username\"\n+ },\n+ {\n+ \"description\": \"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.\",\n+ \"names\": [\n+ \"-v\",\n+ \"--verbose\"\n+ ],\n+ \"signature\": \"-v --verbose\"\n+ },\n+ {\n+ \"description\": \"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.\",\n+ \"names\": [\n+ \"--config-file= filename\"\n+ ],\n+ \"signature\": \"--config-file= filename\"\n+ },\n+ {\n+ \"description\": \"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.\",\n+ \"names\": [\n+ \"--publication= name\"\n+ ],\n+ \"signature\": \"--publication= name\"\n+ },\n+ {\n+ \"description\": \"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.\",\n+ \"names\": [\n+ \"--replication-slot= name\"\n+ ],\n+ \"signature\": \"--replication-slot= name\"\n+ },\n+ {\n+ \"description\": \"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.\",\n+ \"names\": [\n+ \"--subscription= name\"\n+ ],\n+ \"signature\": \"--subscription= name\"\n+ },\n+ {\n+ \"description\": \"Print the pg_createsubscriber version and exit.\",\n+ \"names\": [\n+ \"-V\",\n+ \"--version\"\n+ ],\n+ \"signature\": \"-V --version\"\n+ },\n+ {\n+ \"description\": \"Show help about pg_createsubscriber command line arguments, and exit.\",\n+ \"names\": [\n+ \"-?\",\n+ \"--help\"\n+ ],\n+ \"signature\": \"-? --help\"\n+ }\n+ ],\n+ \"synopsis\": [\n+ \"pg_createsubscriber [ option ...] { -d | --database } dbname { -D | --pgdata } datadir { -P | --publisher-server } connstr\"\n+ ]\n+}"}}