New features
Several recent enhancements have improved the flexibility and usability of pg_createsubscriber. Let's dive into some of the key improvements that simplify your replication workflows:
- The power of --all: Converting all databases at once
- Enabling two-phase transactions (--enable-two-phase option)
- Introduction of the --clean option.
- Validation of max_slot_wal_keep_size
The power of --all: Converting all databases at once
Previously, pg_createsubscriber required you to specify a single database to convert. On servers with multiple databases, this meant manually identifying all relevant databases and listing them in a single command, which was cumbersome and error prone.
The introduction of the --all option streamlines this process. When you use --all, pg_createsubscriber will:
- Identify all user-created databases on the specified data directory.
- For each database, it automatically creates a corresponding publication on the publisher and a subscription on the converting standby.
- It then proceeds with the initial synchronization for all tables within all these databases.
By streamlining this setup, the --all option ensures a consistent and scalable logical replication environment across multiple databases with minimal effort.
Before
Users had to run pg_createsubscriber separately for each database, specifying -d, --publication, --subscription each time.
After
User runs one command with --all, and subscriptions are created for all matching databases.
The following diagram shows the effect of using the --all option:
Restrictions on --all option:
To ensure a clear and consistent subscription setup, the --all option is mutually exclusive with the following options:
- --database
- --publication
- --replication-slot
- --subscription
Example
To create subscriptions for all eligible databases on the publisher:
./pg_createsubscriber -D standby/ -P "host=localhost port=5432 dbname=postgres" --all --verbose
This streamlines replication setup, especially useful during migrations or major upgrades.
Enabling two-phase transactions (--enable-two-phase option)
The --enable-two-phase option enables support for replicating prepared transactions. When specified, it instructs pg_createsubscriber to generate subscriptions with two_phase = true in the resulting CREATE SUBSCRIPTION command.
When --enable-two-phase is specified prepared transactions are sent to the subscriber at the time of PREPARE TRANSACTION. If omitted, prepared transactions are only replicated after being fully committed to the publisher.
Before
Prepared transactions were not replicated until committed. Complex consistency management is required during failover.
After
--enable-two-phase ensures prepared transactions are sent to the subscriber at the time of PREPARE TRANSACTION and are processed as two-phase transactions on the subscriber too.
The following diagram shows the effect of using the --enable-two-phase option:
Example
To create a subscription with two-phase commit enabled:
./pg_createsubscriber -D standby/ -P "host=localhost port=5432 dbname=postgres" --enable-two-phase --verbose
Introduction of the --clean option
The --clean option in pg_createsubscriber enhances logical replication setup by removing any pre-existing replication artifacts on the subscriber—specifically, existing publications that might interfere with the new configuration.
While publications are typically only defined on the publisher, there are cases—such as when a base backup or streaming replication is used before logical replication is configured—where publication metadata may be unintentionally present on the subscriber. This occurs because catalog entries (like publications) will be copied at the storage level.
The --clean option helps ensure a clean state on the subscriber, especially in cases where the node was previously a physical replica of the publisher and inherited publication definitions. The --clean option in pg_createsubscriber streamlines this by allowing users to explicitly remove such legacy or unused publications as part of the replication setup, ensuring a clean and conflict-free environment.
The --clean=publications option instructs pg_createsubscriber to:
- Connect to the target subscriber database (or databases, if using --all).
- Identify all existing publications on the subscriber, including unintended ones.
- Drop those publications before proceeding with new replication setup.
- Log all dropped publications for transparency.
Before
Users had to manually connect to each subscriber DB and drop publication via SQL.
After
The --clean=publications option drops all publications automatically before setup.
The following diagram shows the effect of using the --clean=publications option:
Example
To remove all publications before proceeding with logical replication setup:
./pg_createsubscriber -D standby/ -P "host=localhost port=5432 dbname=postgres" --clean=publications --verbose
This ensures that outdated publications are removed, allowing for a fresh replication setup without legacy configurations.
Validation of max_slot_wal_keep_size
A key enhancement in pg_createsubscriber is the validation of the max_slot_wal_keep_size configuration parameter. This ensures that the publisher's max_slot_wal_keep_size is set to -1, preventing logical replication failures due to missing WAL files. This validation is performed only when pg_createsubscriber is invoked with the --dry-run option.
If the user chooses to proceed despite the warning, the actual replication setup may still succeed — but there's a risk it could fail later if necessary WAL files are removed too soon.
Before
Misconfigured max_slot_wal_keep_size (e.g., 1GB) could silently result in missing WAL and replication failure.
After
pg_createsubscriber emits a warning if max_slot_wal_keep_size is not -1, reducing risk up front.
Example
If a user attempts to create a subscriber while the publisher has a restrictive max_slot_wal_keep_size, pg_createsubscriber will display the following warning:
WARNING: publisher requires WAL size must not be restricted
HINT: Set the configuration parameter "max_slot_wal_keep_size" to -1
to ensure that required WAL files are not prematurely removed.
This warning helps users identify potential replication risks before proceeding, ensuring a more reliable logical replication setup.
Conclusion
These enhancements improve the functionality, usability, and reliability of pg_createsubscriber for migrating physical standbys to logical replicas. Whether you're dealing with multiple databases, require two-phase commit capabilities, need better cleanup options, or want to avoid common replication slot issues, these new features make pg_createsubscriber a more capable tool for PostgreSQL administration tasks.
For the future
Development on pg_createsubscriber is ongoing, with several proposals under consideration to further enhance its functionality. An upcoming enhancement to pg_createsubscriber introduces the --existing-publication option, allowing users to leverage pre-configured publications instead of the default FOR ALL TABLES approach. This enhancement provides greater flexibility and fine-grained control over replication configuration. The feature is not yet committed, but prototypes are being evaluated and refined.
Expect more improvements in future PostgreSQL releases as pg_createsubscriber continues to evolve into a comprehensive solution for logical replication setup and management.
References
- Source code
- Documentation
