Bidirectional replication in PostgreSQL enables two or more databases to replicate data to each other, ensuring changes on one server are mirrored on the other. This setup is ideal for high-availability systems or distributed applications. This guide walks you through setting up bidirectional replication for all tables using PostgreSQL’s native logical replication, including handling restarts and making the replication user optional.
What is Bidirectional Replication?
Bidirectional replication in PostgreSQL allows two or more database servers to replicate data to each other, ensuring that changes made on one server are propagated to the other, and vice versa. Each server acts as both a publisher and a subscriber, maintaining synchronized data across all nodes. This setup is useful for scenarios requiring high availability, load balancing, or distributed systems where updates can occur on multiple nodes.
How It Differs from Standard Replication
* Standard Replication: Typically unidirectional, where a primary server sends data to one or more read-only replicas. Updates occur only on the primary, and replicas are used for read queries or failover.
* Bidirectional Replication: Allows writes on all nodes, with changes synchronized across them. Each node can accept read and write operations, making it more flexible but also more complex to manage.
Prerequisites
* PostgreSQL 10 or later is installed on two servers (Server A and Server B).
* Superuser access to both PostgreSQL instances (e.g., postgres user).
* Network connectivity between the servers.
*A sample database with tables to replicate.
Step 1: Configure PostgreSQL Settings
Both servers require specific settings to enable logical replication and ensure continuity after restarts.
Edit postgresql.conf on both servers: Open the configuration file (typically in /etc/postgresql/<version>/main/postgresql.conf) and set:
wal_level = logical
max_wal_senders = 8
max_replication_slots = 8
wal_keep_size = 1GB # Retain WAL logs for replication continuity
listen_addresses = '*' # Allow external connections
Edit pg_hba.conf on both servers: Add entries to allow replication from the other server. If using the postgres user, ensure it’s allowed:
host all all <Server_A_IP>/32 trust
host replication all <Server_B_IP>/32 trust
If you create a replication user in Step 2, replace postgres with that user (e.g., replicator).
Restart PostgreSQL on both servers:
sudo systemctl restart postgresql
Step 2: (Optional) Set Up a Replication User
You can create a dedicated replication user for security, but it’s optional. Alternatively, use an existing user like postgres with REPLICATION privileges.
* Option A: Create a replication user (recommended for security):
1. Connect to PostgreSQL on Server A and Server B:
psql -U postgres
2. Create a replication user:
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';
3. Update pg_hba.conf (Step 1.2) to use replicator instead of postgres.
* Option B: Use an existing user (e.g., postgres): Ensure the user has REPLICATION privileges. For postgres, this is already granted. If using another user, grant privileges:
ALTER ROLE existing_user WITH REPLICATION;
Use this user’s credentials in the subscription connection strings (Steps 5 and 6).
Step 3: Create a Sample Database and Tables
Use a sample database with multiple tables to demonstrate replication on both servers.
CREATE DATABACREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 4: Prevent Primary Key Conflicts
Bidirectional replication can cause conflicts with SERIAL primary keys if both servers generate the same ID. To avoid this, assign distinct IDSE mydb;
\c mydb
ranges.
* On Server A:
ALTER SEQUENCE users_id_seq RESTART WITH 1 INCREMENT BY 2; -- Odd IDs
* On Server B:
ALTER SEQUENCE users_id_seq RESTART WITH 2 INCREMENT BY 2; -- Even IDs
This ensures Server A generates odd IDs (1, 3, 5, …) and Server B generates even IDs (2, 4, 6, …).
Step 5: Set Up Replication for All Tables from Server A to Server B
On Server A, create a publication for all tables:
CREATE PUBLICATION pub_a FOR ALL TABLES;
On Server B, create a subscription to Server A’s publication. Use the postgres user or the replicator user if created:
CREATE SUBSCRIPTION sub_b
CONNECTION 'host= <Server_B_IP> port=5432 dbname=mydb user=postgres'
PUBLICATION pub_a;
Step 6: Set Up Replication for All Tables from Server B to Server A
On Server B, create a publication for all tables:
CREATE PUBLICATION pub_b FOR ALL TABLES;
On Server A, create a subscription to Server B’s publication:
CREATE SUBSCRIPTION sub_a
CONNECTION 'host=<Server_B_IP> port=5432 dbname=mydb user=postgres'
PUBLICATION pub_b;
Adjust the user, dbname, and host as needed.
Step 7: Test the Replication
* Insert data on Server A:
INSERT INTO users (name) VALUES ('Alice');* Verify on Server B:
SELECT * FROM users;
* Insert data on Server B:
INSERT INTO users (name) VALUES ('Bob');* Verify on Server A:
SELECT *FROM users;
You should see all records on both servers with distinct IDs.
Step 8: Handle Restarts and Ensure Continuity
Restarting PostgreSQL temporarily disconnects replication, but the setup persists, and replication resumes automatically.
* WAL Retention: The wal_keep_size = 1GB setting retains enough WAL logs to prevent data loss during restarts.
* Automatic Reconnection: Subscriptions retry connections automatically.
Verify Post-Restart: After restarting (sudo systemctl restart postgresql), check replication status:
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;
Clean Up Stale Slots (if needed):
SELECT pg_drop_replication_slot('slot_name');Step 9: Monitor and Manage Replication
* Check subscription status:
SELECT * FROM pg_stat_subscription;
* View replication slots:
SELECT * FROM pg_replication_slots;
Handle conflicts: Use application-level logic or updated_at timestamps to resolve conflicts.
Limitations
* Conflict Resolution: Logical replication doesn’t handle conflicts automatically.
* Performance: Replication is asynchronous. Synchronous replication requires additional setup.
* Schema Changes: Schema changes must be manually applied to both servers.
* Security: If using postgres, ensure strong passwords and consider SSL. A dedicated replication user is safer.
* New Tables: The FOR ALL TABLES publication includes new tables automatically, but their sequences need the script from Step 4 to avoid ID conflicts.
* Existing Data: If tables already contain data, adjust the RESTART WITH value in the scripts to avoid ID overlaps.
Bidirectional replication for all tables in PostgreSQL ensures data consistency across servers. By automating sequence adjustments, configuring publications and subscriptions, and ensuring WAL retention, you can maintain a robust system that handles restarts. Using an existing user like postgres simplifies setup, but a dedicated replication user enhances security. For more details, consult the PostgreSQL documentation on logical replication.