Pglogical is a third-party extension for PostgreSQL that enhances logical replication capabilities. It builds on PostgreSQL's native logical replication (introduced in version 10) but provides advanced features tailored for complex replication scenarios, particularly bidirectional (multi-master) replication. In bidirectional replication, multiple database servers act as both publishers and subscribers, allowing writes on all nodes with changes synchronized across them. While PostgreSQL's native logical replication (using CREATE PUBLICATION and CREATE SUBSCRIPTION) supports unidirectional replication and can be configured for bidirectional setups, pglogical offers significant advantages for managing bidirectional replication effectively.
Why Choose pglogical for Bidirectional Replication?
If you've previously set up bidirectional replication using native logical replication, you likely encountered challenges like manual conflict resolution, DDL synchronization, or sequence management. pglogical addresses these pain points by offering a robust, automated framework for multi-master replication. It's particularly valuable when:
* Both nodes accept writes to the same tables, increasing the risk of conflicts.
* You need to replicate schema changes or sequences without manual intervention.
* You want a scalable solution for distributed systems with multiple writable nodes.
* You prefer automated conflict resolution to minimize application-level complexity.
While pglogical requires installing an extension and additional setup steps, its benefits outweigh the complexity for bidirectional replication, ensuring reliability, scalability, and reduced maintenance.
Prerequisites
* Two PostgreSQL servers (version 15 or later recommended for logical replication improvements).
* Basic PostgreSQL administration knowledge.
* Network connectivity between servers with firewall rules allowing PostgreSQL port (default: 5432).
* Superuser access on both servers.
For this guide, we'll use:
* Server A: IP 192.168.1.10, hostname node1.
* Server B: IP 192.168.1.11, hostname node2.
* Database: mydb.
* Table: users.
Step-by-Step Setup
We'll use pglogical to enable bidirectional replication, as native logical replication in PostgreSQL doesn't fully support bidirectional setups without additional logic. pglogical simplifies conflict handling and supports multi-master replication.
Step 1: Install PostgreSQL and pglogical
On both servers, ensure PostgreSQL is installed, along with the development packages required for building extensions.
# On Ubuntu/Debian
sudo apt update
sudo apt install postgresql-15 postgresql-server-dev-15 build-essential git
# On RHEL/CentOS/Fedora
sudo dnf install postgresql15 postgresql15-devel git make gcc
# Clone and install pglogical
git clone https://github.com/2ndQuadrant/pglogical.git
cd pglogical
make USE_PGXS=1
sudo make USE_PGXS=1 install
For custom PostgreSQL installations, specify the path to pg_config:
make USE_PGXS=1 PG_CONFIG=/path/to/pg_config
sudo make USE_PGXS=1 PG_CONFIG=/path/to/pg_config install
Then we have to set the pglogical in our shared_preload_libraries parameter:
ALTER SYSTEM SET shared_preload_libraries TO ‘pglogical’;
Restart the PostgreSQL server:
# For systemd-based systems
sudo systemctl restart postgresql
# For non-systemd systems
sudo pg_ctl -D /path/to/data/directory restart
Then, verify the installation by creating an extension in PostgreSQL:
CREATE EXTENSION pglogical;
If no errors appear, pglogical is ready.
Step 2: Configure postgresql.conf
Edit your PostgreSQL configuration file on both servers to enable logical replication and pglogical. The location depends on your installation:
* Ubuntu/Debian: /etc/postgresql/15/main/postgresql.conf
* RHEL/CentOS: /var/lib/pgsql/15/data/postgresql.conf
* Custom installation: Check your data directory
Add or modify these settings:
# Enable logical replication
wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
shared_preload_libraries = 'pglogical'
listen_addresses = '*'
Restart PostgreSQL on both servers:
sudo systemctl restart postgresql
# OR
sudo pg_ctl -D /path/to/data/directory restart
Step 3: Configure pg_hba.conf
Allow replication connections between nodes. Edit /etc/postgresql/15/main/pg_hba.conf on both servers:
# Server A (node1)
host all postgres 192.168.1.11/32 md5
host replication postgres 192.168.1.11/32 md5
# Server B (node2)
host all postgres 192.168.1.10/32 md5
host replication postgres 192.168.1.10/32 md5
Reload PostgreSQL:
sudo systemctl reload postgresql
Step 4: Create Database and Table
Create the database and table on both servers. We'll use a users table with a UUID primary key to avoid key collisions.
On node1 and node2:
CREATE DATABASE mydb;
\c mydb
-- Create necessary extensions
CREATE EXTENSION pglogical;
CREATE EXTENSION pgcrypto; -- Required for gen_random_uuid()
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
Step 5: Set Up pglogical Nodes
Initialize pglogical nodes on both servers.
On node1:
SELECT pglogical.create_node(
node_name := 'node1',
dsn := 'host=192.168.1.10 port=5432 dbname=mydb user=postgres password=your_password'
);
On node2:
SELECT pglogical.create_node(
node_name := 'node2',
dsn := 'host=192.168.1.11 port=5432 dbname=mydb user=postgres password=your_password'
);
Replace your_password with your PostgreSQL user password.
Step 6: Create Replication Sets
Define a replication set to specify which tables to replicate. For simplicity, we'll replicate the users table.
On node1 and node2:
SELECT pglogical.create_replication_set(
set_name := 'my_replication_set'
);
SELECT pglogical.replication_set_add_table(
set_name := 'my_replication_set',
relation := 'users',
synchronize_data := true
);
Step 7: Subscribe Nodes to Each Other
Set up subscriptions so each node replicates data from the other.
On node1 (subscribe to node2):
SELECT pglogical.create_subscription(
subscription_name := 'sub_node1_to_node2',
provider_dsn := 'host=192.168.1.11 port=5432 dbname=mydb user=postgres password=your_password',
replication_sets := ARRAY['my_replication_set']
);
On node2 (subscribe to node1):
SELECT pglogical.create_subscription(
subscription_name := 'sub_node2_to_node1',
provider_dsn := 'host=192.168.1.10 port=5432 dbname=mydb user=postgres password=your_password',
replication_sets := ARRAY['my_replication_set']
);
Step 8: Configure Conflict Resolution
pglogical handles conflicts using a "last-update-wins" strategy by default, based on the updated_at timestamp. To enhance this, ensure your table has a timestamp column (like updated_at in our users table) and update it on every write.
Create a trigger to update updated_at automatically:
On node1 and node2:
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_users_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
For custom conflict resolution (e.g., merging data), you can define conflict handlers in pglogical, but this requires advanced configuration beyond this guide's scope.
Step 9: Test the Replication
Insert data on node1:
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');Check node2:
SELECT * FROM users;
You should see:
id | name | email | updated_at
--------------------------------------+-------+---------------------+----------------------------
<some-uuid> | Alice | alice@example.com | <timestamp>
Insert data on node2:
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');Check node1:
SELECT * FROM users;
You should see both rows:
id | name | email | updated_at
--------------------------------------+-------+---------------------+----------------------------
<some-uuid> | Alice | alice@example.com | <timestamp>
<some-uuid> | Bob | bob@example.com | <timestamp>
Step 10: Monitor Replication
Check replication status on node1:
SELECT * FROM pglogical.show_subscription_status();
On node2:
SELECT * FROM pglogical.show_subscription_status();
Look for active in the status column. If there are issues, check PostgreSQL logs (/var/log/postgresql/).
Handling Conflicts
To simulate a conflict, update the same row on both nodes before replication syncs.
On node1:
UPDATE users SET name = 'Alice Smith' WHERE email = 'alice@example.com';
On node2 (quickly, before sync):
UPDATE users SET name = 'Alice Jones' WHERE email = 'alice@example.com';
After replication, check the row. With default pglogical settings, the row with the later updated_at wins (e.g., Alice Jones if node2's update was later). To verify:
SELECT * FROM users WHERE email = 'alice@example.com';
You now have a bidirectional replication setup between two PostgreSQL servers using pglogical. Data inserted or updated on one node replicates to the other, with conflicts resolved based on timestamps. Test thoroughly in a development environment before deploying to production, and monitor replication health regularly.