Win $20,000. Help build the future of education. Answer the call. Learn more

Configure PostGreSQL Active-Active replication with Bucardo

Introduction

Users expect databases to be available at all times. Losing access to or the losing the database itself can be very disruptive with serious business and brand implications. This tutorial provides detailed instructions on how to use Burcado to set up an active-active replication between two instances of PostgreSQL installed on two different instances of IBM Cloud Hyper Protect Virtual Servers, possibly in two different regions. PostgreSQL, one of the most popular relational database management systems, is free and open source. It provides the ability to manage data without concern for the size of the database.

Figure 1 is a representation of the sync requirements between two instances of PostgreSQL.

PostgreSQL Active-Active Sync

The Hyper Protect Virtual Servers are built on the IBM Z LinuxONE Secure Service Container technology. The Hyper Protect Virtual Servers offering is available On-Prem and on IBM Cloud as Virtual Server Instances. Hyper Protect Virtual Server grants complete control over Linux-based workloads that contain sensitive business data and offers a confidential computing environment to address top security concerns when building applications on the cloud.

Figure 2 is a schematic of the Secure Hyper Protect Virtual Servers platform and the technologies that make it happen.

Hyper Protect Virtual Servers

Prerequisites

  • An IBM Cloud account. Sign in or create one on the IBM Cloud main site.
  • 2 instances of Hyper Protect Virtual Server in two different regions. Make a note of the following:
    • Hyper Protect Virtual Servers-1 Public & Private IP addresses
    • Hyper Protect Virtual Servers-2 Public & Private IP addresses

Estimated Time

60-90 minutes

Steps

Open an SSH session before you begin.

Note: I recommended you execute the steps outlined below on both of the participating Hyper Protect Virtual Servers instances.

1. Update iptables

Prepare to edit iptables by running this command:

  iptables-save > /etc/systemd/iptables.rules

Add the following line to /etc/systemd/iptables.rules just above the COMMIT line

  -A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT

Update iptables by running this command: iptables-restore < /etc/systemd/iptables.rules

List iptables by running this command: iptables -L

2. Install PostgreSQL

  apt install -y postgresql-10 postgresql-contrib libpq-dev postgresql-server-dev-10 postgresql-plperl-10

3. Install Bucardo

  apt install -y libdbix-safe-perl libtest-simple-perl libboolean-perl libextutils-makemaker-cpanfile-perl libextutils-modulemaker-perl libcgi*-perl libdbd-pg-perl libencode-locale-perl libpod-parser-perl libsys-syslog-perl vim sudo iputils-ping net-tools netcat

  apt install -y bucardo

Create the required directories for Bucardo:

  mkdir -p /var/run/bucardo
  mkdir -p /var/log/bucardo

4. Modify the PostgreSQL listen_address

Add the following line to the /etc/postgresql/10/main/postgresql.conf file:

  listen_addresses = '*'     # what IP address(es) to listen on; use '*' for all

Add the following line to the /etc/postgresql/10/main/pg_hba.conf file:

  host    all             bucardo         0.0.0.0/0               trust

5. Bring up PostgreSQL

  /etc/init.d/postgresql start

6. Create the Bucardo user, database, and table in PostgreSQL

  sudo -i -u postgres                                               # change user to *postgres*
  psql                                                              # enter the psql shell
  create user bucardo with superuser password 'bucardobucardo';
  create database bucardo with owner = bucardo;
  \c bucardo                                                        # connect to database "bucardo" as user "postgres"
  create table tmp_t0(c0 bigint,c1 varchar(100));
  alter table tmp_t0 add primary key(c0);
  insert into tmp_t0
  select id,md5(id::varchar) from generate_series(1,10) as id;

7. Generate the authentication file for Bucardo

Add the following line to the /root/.pgpass file:

  *:5432:*:bucardo:bucardobucardo
  [host]:[port]:[database]:[username]:[password]

Run the following:

  chmod 600 /root/.pgpass

8. Initialize Bucardo

  bucardo install

  - Select 1 to modify host address, please use the internal ip address.
  - Select 3 to modify db user, please use ‘bucardo’ which we created in previous step.
  - Select P to process it.

Wait until you see a message similar to this: ‘Installation is now complete.’

9. Configure source/target for active-active sync

On Hyper Protect Virtual Servers instance 1

  bucardo add database vs1db dbname=bucardo host=<hpvs-1-private-ip-address> port=5432 user=bucardo      # src
  bucardo add database vs2db dbname=bucardo host=<hpvs-2-public-ip-address> port=5432 user=bucardo       # target
  bucardo add dbgroup grp1 vs1db:source vs2db:target                                                     # db-group

On Hyper Protect Virtual Servers instance 2

  bucardo add database vs1db dbname=bucardo host=<hpvs-2-private-ip-address> port=5432 user=bucardo      # src
  bucardo add database vs2db dbname=bucardo host=<hpvs-1-public-ip-address> port=5432 user=bucardo       # target
  bucardo add dbgroup grp1 vs2db:source vs1db:target                                                     # db-group

10. Establish Active-Active sync

  bucardo add sync dbsync herd=herd_test dbs=grp1 conflict_strategy=latest                 # establish sync
  bucardo start                                                                            # start bucardo
  bucardo status                                                                           # view status
  bucardo list all                                                                         # list all databases

Configure Source and Target

11. Verify active-active mode replication

Insert a record on database 1 on Hyper Protect Virtual Servers instance 1:

bucardo=# insert into tmp_t0 (c0, c1) values (11,’11111111111111111111111111111111');

Check records on database 2 on Hyper Protect Virtual Servers instance 2:

bucardo=# select * from tmp_t0;

Summary

In this tutorial you have learned how to set up an active-active replication between two instances of PostgreSQL installed on two different instances of Hyper Protect Virtual Servers. Bucardo can be used to setup a cross-replication like the one depicted in the picture below:

PostgreSQL Cross Replication

Bucardo can also be used to address backup, Disaster Recovery (DR) requirements, and also to address dynamic capacity issues as depicted in the picture below:

PostgreSQL DR Use Case