PostgreSQL 11与12跨版本搭建副本可行吗？publicator主配subcriptor从

Short Answer #

Absolutely, you can set up logical replication with PostgreSQL 11 as the publisher and PostgreSQL 12 as the subscriber—this is a fully supported configuration, and it’s a common setup when transitioning between versions or maintaining a mixed-version environment.

Key Compatibility Notes #

PostgreSQL’s logical replication system is designed to support subscribers running a version equal to or newer than the publisher. This works because newer PostgreSQL versions can understand the WAL (Write-Ahead Log) format and data types used by older versions, but the reverse (older subscriber, newer publisher) is not supported. Since 12 is newer than 11, this combination is safe and valid.

Step-by-Step Setup Overview #

Here’s a simplified breakdown of the setup process:

1. Configure the PostgreSQL 11 Publisher

   • First, update the postgresql.conf file to enable logical replication:

     wal_level = logical          # Required for logical replication
     max_replication_slots = 10   # Adjust based on your number of subscribers
     max_wal_senders = 10         # Ensure enough slots for replication processes
     

   • Restart the PostgreSQL 11 service to apply these changes.
   • Create a publication for the tables you want to replicate:

     -- Replicate specific tables
     CREATE PUBLICATION app_data_publication FOR TABLE users, orders;
     
     -- Or replicate all tables in the database
     -- CREATE PUBLICATION app_data_publication FOR ALL TABLES;
     

   • Create a dedicated replication user with necessary permissions:

     CREATE ROLE replication_sub WITH REPLICATION LOGIN PASSWORD 'secure_password_123';
     GRANT SELECT ON users, orders TO replication_sub;
     

2. Set Up the PostgreSQL 12 Subscriber

   • Ensure target tables exist on the subscriber (you can use pg_dump from the publisher to initialize data, or let the subscription handle initial data copy).
   • Create the subscription to connect to the publisher:

     CREATE SUBSCRIPTION app_data_subscription
     CONNECTION 'host=publisher_host port=5432 dbname=publisher_db user=replication_sub password=secure_password_123'
     PUBLICATION app_data_publication
     WITH (copy_data = true);  -- Automatically copies existing data (default behavior)
     

Critical Reminders #

• DDL Synchronization: PostgreSQL 11 and 12 do not automatically replicate DDL changes (like ALTER TABLE or CREATE TABLE). If you modify the schema on the publisher, you’ll need to manually apply the same changes to the subscriber to keep schemas aligned.
• WAL Retention: The publisher needs to retain WAL segments until the subscriber has consumed them. Monitor replication slots (using SELECT * FROM pg_replication_slots;) to avoid WAL being cleaned up prematurely, which can break replication.
• Testing: Always validate the setup in a non-production environment first. Test data inserts, updates, and deletes to confirm they sync correctly between the publisher and subscriber.
• Upgrade Path: This setup is ideal if you plan to eventually upgrade the publisher to PostgreSQL 12—you can do so without disrupting replication once the subscriber is already on 12.

内容的提问来源于stack exchange，提问作者Salvatore Tumminaro