Introduction to Global High Availability in PostgreSQL

In today's interconnected economy, business continuity and low-latency data access are paramount. Traditional master-slave replication setups, while reliable for backups and read-scalability, introduce a critical single point of failure for write operations and suffer from significant cross-region latency when users are globally distributed. To overcome these limitations, enterprises are increasingly turning to Active-Active (Multi-Master) architectures.

An Active-Active configuration allows write operations to occur concurrently on multiple geographically separated database nodes. Data modifications made on one node are asynchronously replicated to the other, ensuring that both endpoints remain synchronized. This blog post provides an enterprise-grade, step-by-step guide to configuring a global Active-Active PostgreSQL cluster across two Virtual Private Servers (VPS) located in different regions using Bucardo, an asynchronous, trigger-based replication system.

Why Bucardo for Multi-Master Replication?

While several proprietary and cloud-native solutions exist for PostgreSQL clustering, Bucardo remains a premier choice for open-source architectures due to its flexibility and robustness. Key advantages include:

Asynchronous Replication: Minimizes application performance degradation by not requiring a two-phase commit across high-latency WAN connections.
Table-Level Granularity: Allows administrators to replicate specific tables or entire schemas based on business requirements.
Conflict Resolution: Features built-in, customizable conflict handling mechanisms (e.g., source wins, latest timestamp wins) to manage concurrent data modifications safely.
Master-Master and Master-Slave Flexibility: Supports complex topologies beyond simple bi-directional replication.

Architecture and Prerequisites

For this deployment, we will assume a scenario involving two VPS instances situated in distinct geographical locations (e.g., Node A in US-East and Node B in EU-West). Both nodes must be capable of secure inter-communication.

System Requirements

Two VPS instances running Ubuntu 22.04 LTS or later.
PostgreSQL 14 or higher installed on both nodes.
A dedicated, secure network channel (ideally via a private WireGuard VPN or strictly firewalled public IPs).
Bucardo 5.x installed on at least one primary controller node (Node A).

Security Note: Never expose your PostgreSQL default port (5432) to the open internet. Always restrict traffic using internal firewall rules (UFW) or a secure VPN tunnel between the two locations.

Step 1: Network and PostgreSQL Initial Configuration

Before installing Bucardo, both PostgreSQL instances must be configured to listen for external connections and allow authentication from the peer node.

Modify postgresql.conf

On both Node A and Node B, edit the main configuration file (usually located at /etc/postgresql/15/main/postgresql.conf) to enable network listening:

listen_addresses = '*'

Configure Client Authentication (pg_hba.conf)

Next, edit the pg_hba.conf file on both nodes to allow the replication user and Bucardo to connect. Append the following lines, substituting the actual IP addresses of your nodes:

# Allow peer node connection
host    all             all             [PEER_NODE_IP]/32       md5

Restart the PostgreSQL service on both servers to apply the changes:

sudo systemctl restart postgresql

Step 2: Database and Schema Preparation

For Bucardo to operate successfully, both databases must share an identical schema structure prior to replication initialization. Furthermore, Bucardo requires every replicated table to have a Primary Key or a unique index.

Create the Database and Test Tables

Execute the following SQL commands on both nodes to create a sample production database and a table for testing replication:

CREATE DATABASE enterprise_db;
\c enterprise_db;

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Installing and Initializing Bucardo

Bucardo requires a central control database to store its replication metadata, sync definitions, and conflict logs. We will install and run Bucardo on Node A.

Install Dependencies and Bucardo Package

Update your package manager and install the necessary Perl modules and Bucardo binaries on Node A:

sudo apt-get update
sudo apt-get install bucardo perl-modules libdbd-pg-perl

Initialize the Bucardo Control Database

Run the installation script to create the internal bucardo database and schema. Ensure you run this as a superuser or the postgres system user:

sudo -u postgres bucardo install

Follow the on-screen prompts. The utility will automatically create the required tracking tables and functions.

Step 4: Defining the Active-Active Replication Topology

With Bucardo installed on Node A, we must now inform it about the databases it needs to manage and define the replication relationship.

1. Add Databases to Bucardo

bucardo add db db_node_a dbname=enterprise_db host=127.0.0.1 user=postgres
bucardo add db db_node_b dbname=enterprise_db host=[NODE_B_IP] user=postgrespass=[NODE_B_PASSWORD]

2. Add Replicated Tables to a Herd

In Bucardo terminology, a "herd" is a collection of tables that are replicated together as a single logical unit. Add our customers table to a new herd named global_herd:

bucardo add table public.customers herd=global_herd

3. Create the Active-Active Sync

Now, define the synchronization task. To achieve an Active-Active (bi-directional) setup, we configure the sync with a type of swap. This tells Bucardo that data can flow dynamically in both directions between the nodes:

bucardo add sync global_sync herd=global_herd dbs=db_node_a:source,db_node_b:source

Notice that both databases are explicitly marked as source. This instructs Bucardo to monitor changes on both endpoints simultaneously.

Step 5: Testing Global Replication and Conflict Resolution

With the setup complete, start the Bucardo daemon to initiate active monitoring and data synchronization:

bucardo start

Verify the status of your replication cluster by running:

bucardo status

Testing Bi-directional Replication

To confirm that your multi-master setup is working correctly across locations, perform the following validation steps:

Insert a row into Node A: INSERT INTO customers (name, email) VALUES ('John Doe', 'john@example.com');
Query Node B to confirm the record appears instantly: SELECT * FROM customers;
Insert a different row into Node B: INSERT INTO customers (name, email) VALUES ('Jane Smith', 'jane@example.com');
Query Node A to confirm the record has replicated back across the network.

Understanding Conflict Management

Because Bucardo uses asynchronous replication, a race condition can occur if two distinct users modify the exact same row on Node A and Node B at the exact same millisecond. By default, Bucardo utilizes a "source wins" conflict handler, or looks at standard timestamp columns if defined. For production systems, it is highly recommended to use UUIDs (Universally Unique Identifiers) for primary keys instead of standard incremental SERIAL integers to prevent primary key collision conflicts entirely.

Conclusion and Best Practices

Configuring an Active-Active PostgreSQL cluster using Bucardo across disparate VPS locations provides a resilient framework for international enterprise applications. However, moving this architecture to production requires strict adherence to operational best practices:

Monitoring and Alerting: Implement active monitoring on the Bucardo log file (bucardo.log) to catch replication lag or connection failures immediately.
Connection Pooling: Utilize tools like PgBouncer on each node to manage connection spikes effectively without overwhelming Bucardo\'s processes.
Schema Migration Strategy: Any future ALTER TABLE statements must be executed manually on both nodes, followed by a reload of the Bucardo configuration using bucardo reload config.

By leveraging Bucardo\'s powerful trigger-based replication, businesses can confidently distribute database workloads globally, guaranteeing low latency for localized clients and structural redundancy against regional cloud outages.

Building a Global Active-Active PostgreSQL Cluster: Multi-Region Replication with Bucardo