widget

PostgreSQL logical replication has operational challenges, such as the absence of generated columns in the replication stream, replication conflicts causing apply worker halts, and lingering replication slots consuming write-ahead log (WAL) files. PostgreSQL 18 introduces enhancements to address these issues, including the ability to include STORED generated columns in the replication stream with a new publication parameter, detailed conflict counters in pg_stat_subscription_stats, automatic invalidation of idle replication slots, and the ability to toggle two-phase commit settings on live subscriptions. To set up a test environment, two PostgreSQL 18 instances (one publisher and one subscriber) are required, along with an EC2 instance for executing commands. The publisher and subscriber must have specific configurations for logical replication, including enabling logical replication and setting appropriate parameters. PostgreSQL 18 allows the replication of STORED generated columns by using the publish_generated_columns parameter. The subscriber must define these columns as regular columns. The new version also enhances conflict monitoring by providing detailed counters for different types of replication conflicts. Parallel streaming is now the default behavior for subscriptions, improving efficiency. The two-phase commit feature can now be modified on existing subscriptions without needing to recreate them. PostgreSQL 18 also introduces automatic cleanup of idle replication slots, which can be configured to invalidate unused slots after a specified duration. Considerations for implementation include ensuring that the subscriber's column types match those of the publisher for generated columns, the requirement for track_commit_timestamp for certain conflict counters, and the fact that sequences are not replicated. Cleanup of replication resources is recommended after testing to prevent unnecessary costs and WAL accumulation.