PG_CREATESUBSCRIBER(1)

📦 version 18.6, in opensuse-leap 16.0

🌸 pg_createsubscriber 🌸

PostgreSQL 18.6 Documentation · manual page, section 1 · okay bestie sit DOWN because this one is a whole journey 💖

📛 NAME 📛

pg_createsubscriber — glow-up time: turn a physical replica into a brand new logical replica ✨

🎀 SYNOPSIS 🎀

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

🦋 DESCRIPTION 🦋

So like, pg_createsubscriber builds you a new logical replica out of a physical standby server. Every single table in the database you point at gets pulled into the logical replication setup, no crumbs left behind. For each database it makes a pair — one publication object and one subscription object, matching set, iconic. And important: you have to run it at the target server. Like, the target one. Not the source. The target. 🙈

Once it runs successfully, the target server ends up in a state that's basically analogous to a fresh logical replication setup. So what's the difference then?? The main difference between a normal logical replication setup and pg_createsubscriber is how the data synchronization goes down. pg_createsubscriber does NOT copy the initial table data. Nope. It only does the synchronization phase, which makes sure every table gets brought up to a synchronized state. That's it, that's the job. 💅

pg_createsubscriber is built for large database systems fr fr, because in a regular logical replication setup, most of the time is spent doing that initial data copy. And here's the sneaky part — a side effect of all that time spent synchronizing data is usually a large amount of changes to be applied (all the changes that were produced during the initial data copy), which increases <em>even more</em> the time until the logical replica will actually be available. 😭 So for smaller databases, it's recommended to set up logical replication with initial data synchronization instead. For the details on that, go look at the CREATE SUBSCRIPTION copy_data option. 👑

✨ OPTIONS ✨

pg_createsubscriber accepts the following command-line arguments:

  • -a, --all — Makes one subscription per database on the target server. The exceptions are template databases and databases that don't allow connections (rude of them honestly). To discover the list of all databases, it connects to the source server using the database name specified in the --publisher-server connection string — or if that's not specified, the postgres database gets used, or if <em>that</em> one doesn't exist, template1 gets used. When this option is on, the names for subscriptions, publications, and replication slots are all automatically generated. Heads up bestie: this option cannot be used along with --database, --publication, --replication-slot, or --subscription. Pick a lane. 🫶
  • -d dbname, --database=dbname — The name of the database where the subscription gets created. You can pick multiple databases by writing multiple -d switches (stack 'em up 💅). This one cannot be used together with -a. If -d isn't provided, the database name gets obtained from the -P option. And if the database name isn't specified in <em>either</em> the -d option or the -P option, and -a isn't specified either? Error. Reported. Done. 😤
  • -D directory, --pgdata=directory — The target directory that contains a cluster directory from a physical replica. That's the one. 📁
  • -n, --dry-run — Do everything except actually modifying the target directory. A little window-shopping run, no cap. 🛍️
  • -p port, --subscriber-port=port — The port number the target server is listening on for connections. Defaults to running the target server on port 50432 so you don't get any unintended client connections sneaking in. Safety first, always. 🔒
  • -P connstr, --publisher-server=connstr — The connection string to the publisher. For details, see Section 32.1.1. 🔗
  • -s dir, --socketdir=dir — The directory to use for postmaster sockets on the target server. Default is the current directory.
  • -t seconds, --recovery-timeout=seconds — The max number of seconds to wait for recovery to end. Setting it to 0 disables it. Default is 0. ⏳
  • -T, --enable-two-phase — Enables two_phase commit for the subscription. When multiple databases are specified, this option applies <em>uniformly</em> to all subscriptions created on those databases — no picking favorites. Default is false. 🌈
  • -U username, --subscriber-username=username — The user name to connect as on the target server. Defaults to the current operating system user name. 🫶
  • -v, --verbose — Enables verbose mode. This makes pg_createsubscriber output progress messages and detailed info about each step to standard error. And if you repeat the option? You get <em>additional</em> debug-level messages on standard error. Stack that verbosity, queen. 📢
  • --clean=objtype — Drop all objects of the specified type from the 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. And listen — there is no opportunity to affect or stop the dropping of the selected objects. None. So consider taking a backup of them using pg_dump. This is your sign. 💀

  • --config-file=filename — Use the specified main server configuration file for the target data directory. pg_createsubscriber internally uses the pg_ctl command to start and stop the target server, and this option lets you specify the actual postgresql.conf configuration file if it's stored <em>outside</em> the data directory. 📂
  • --publication=name — The publication name to set up the logical replication. You can specify multiple publications by writing multiple --publication switches. The number of publication names must match the number of specified databases, otherwise an error is reported. And the order of the multiple publication name switches must match the order of the database switches — order matters, bestie, don't play. If this option isn't specified, a generated name is assigned to the publication name. This option cannot be used together with --all. 🎀
  • --replication-slot=name — The replication slot name to set up the logical replication. Multiple replication slots can be specified by writing multiple --replication-slot switches. The number of replication slot names must match the number of specified databases, otherwise an error is reported. The order of the multiple replication slot name switches must match the order of the database switches. If this option isn't specified, the subscription name gets assigned to the replication slot name. This option cannot be used together with --all. 🕳️
  • --subscription=name — The subscription name to set up the logical replication. Multiple subscriptions can be specified by writing multiple --subscription switches. The number of subscription names must match the number of specified databases, otherwise an error is reported. The order of the multiple subscription name switches must match the order of the database switches. If this option isn't specified, a generated name is assigned to the subscription name. This option cannot be used together with --all. 💖
  • -V, --version — Print the pg_createsubscriber version and exit. Short and sweet. ✨
  • -?, --help — Show help about pg_createsubscriber command line arguments, and exit. Your lil cheat sheet. 🫶

📝 NOTES 📝

🎀 Prerequisites 🎀

Okay bestie, before pg_createsubscriber will even look at your target server and turn it into a logical replica, there's a whole checklist it needs you to pass. Miss even one and it's gonna report an error at you. No mercy, no cap. 💀

  • The source server, the target server, and pg_createsubscriber itself must all be the same major version. Same era, same vibe, periodt.
  • The target data directory you hand over must have the same system identifier as the source data directory. Twins or nothing. 👯
  • The database user you give for the target data directory must have privileges for creating subscriptions and for using pg_replication_origin_advance().

And now the target server's own little requirements list, because she's high maintenance and we love her for it:

  • The target server must be used as a physical standby.
  • max_active_replication_origins and max_logical_replication_workers must be set to a value greater than or equal to the number of specified databases.
  • max_worker_processes must be set to a value greater than the number of specified databases. (Note: greater than, not equal. She's picky. 🙈)
  • The target server must accept local connections.
  • Planning to use the --enable-two-phase switch? Then you also need to set max_prepared_transactions appropriately. Don't forget, or it's giving chaos. ✨

And the source server? She has demands too:

  • 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.
  • max_replication_slots must be set to a value greater than or equal to the number of specified databases plus existing replication slots.
  • max_wal_senders must be set to a value greater than or equal to the number of specified databases and existing WAL sender processes.

⚠️ Warnings ⚠️

Read these, bestie, I'm literally begging. 😭

  • 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 that case, creating a new standby server is recommended. Yeah. Start over. Iconic but devastating.
  • pg_createsubscriber usually starts the target server with different connection settings during transformation. So yeah — connections to the target server should fail. That's expected, don't panic. 🫶
  • Since DDL commands are not replicated by logical replication, avoid executing DDL commands that change the database schema while pg_createsubscriber is running. And if the target server has already been converted to a logical replica, those DDL commands might not be replicated, which might cause an error. Hands off the schema, okay? 💅
  • If pg_createsubscriber fails while processing, objects (publications, replication slots) created on the source server are removed. But that removal might fail if the target server cannot connect to the source server — in which case a warning message will tell you which objects were left behind. And if the target server is running, it will be stopped.
  • If the replication is using primary_slot_name, it will be removed from the source server after the logical replication setup.
  • If the target server is a synchronous replica, transaction commits on the primary might wait for replication while pg_createsubscriber is running. Yes, it can hang out there. Be patient or be prepared. 💀
  • Unless the --enable-two-phase switch is specified, pg_createsubscriber sets up logical replication with two-phase commit disabled. What that means: 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. Your call, your journey. ✨
  • pg_createsubscriber changes the system identifier using pg_resetwal. This avoids situations where the target server might use WAL files from the source server. But heads up: if the target server has a standby, replication will break and a fresh standby should be created.
  • 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 so that required WAL files are not prematurely removed. Protect those WALs, they're precious. 🎀

🦋 How It Works 🦋

The basic idea? Grab a replication start point from the source server, and set up logical replication to start from that exact point. Here's the whole glow-up, step by step:

  1. Start the target server with the specified command-line options. If the target server is already running, pg_createsubscriber will terminate with an error. She does not do interruptions. 🙅
  2. Check if the target server can be converted. There are also a few checks on the source server. If any of the prerequisites are not met, pg_createsubscriber will terminate with an error.
  3. Create a publication and replication slot for each specified database on the source server. Each publication is created using FOR ALL TABLES. If the --publication option is not specified, the publication has the following name pattern: pg_createsubscriber_%u_%x (parameter: database oid, random int). If the --replication-slot option is not specified, the replication slot has the following name pattern: pg_createsubscriber_%u_%x (parameters: database oid, random int). These replication slots will be used by the subscriptions in a future step. The last replication slot LSN is used as a stopping point in the recovery_target_lsn parameter and by the subscriptions as a replication start point. It guarantees that no transaction will be lost. Periodt. 💖
  4. Write recovery parameters into the target data directory and restart the target server. It specifies an LSN (recovery_target_lsn) of the write-ahead log location up to which recovery will proceed. It also specifies promote as the action that the server should take once the recovery target is reached. Additional recovery parameters are added to avoid unexpected behavior during the recovery process — things like 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 the --recovery-timeout option is set, pg_createsubscriber terminates if recovery does not end until the given number of seconds. ⏳
  5. Create a subscription for each specified database on the target server. If the --subscription option is not specified, the subscription has the following name pattern: pg_createsubscriber_%u_%x (parameters: database oid, random int). It does not copy existing data from the source server. It does not create a replication slot. Instead, it uses the replication slot that was created in a previous step. The subscription is created but it is not enabled yet — because the replication progress must be set to the replication start point before starting the replication. Patience, bestie. 🧚
  6. Drop publications on the target server that were replicated because they were created before the replication start location. It has no use on the subscriber. Bye girl. 👋
  7. Set the replication progress to the replication start point for each subscription. When the target server starts the recovery process, it catches up to the replication start point. This is the exact LSN to be used as an initial replication location for each subscription. The replication origin name is obtained since the subscription was created. The replication origin name and the replication start point are used in pg_replication_origin_advance() to set up the initial replication location.
  8. Enable the subscription for each specified database on the target server. The subscription starts applying transactions from the replication start point. Slay. 🔥
  9. If the standby server was using primary_slot_name, it has no use from now on, so drop it.
  10. If the standby server contains failover replication slots, they cannot be synchronized anymore, so drop them.
  11. Update the system identifier on the target server. The pg_resetwal(1) is run to modify the system identifier. The target server is stopped as a pg_resetwal requirement. 👑

🌈 EXAMPLES 🌈

Wanna create a logical replica for the databases hr and finance from a physical replica at foo? Say less:

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

💜 SEE ALSO 💜

And that's the whole page, bestie! 🫶 This one came from PostgreSQL 18.6, dated 2026, and it's filed under PG_CREATESUBSCRIBER(1) — go forth and replicate logically, you absolute main character. 💅✨🎀

📦 where this page comes from