Blog

Databases are what is the most often the critical component of any enterprise. Therefore, in order to ensure high availability, replication mechanisms have been created. Today we will introduce the topic of logical replication in PostgreSQL 13.

The process of copying and transferring information between servers and keeping them synchronized in order to maintain consistency is what we call database replication. The main purpose of replication is to reduce the time it takes to access data and to become independent of one single server, which in case of a possible failure makes the whole system unavailable.

Database replication types supported by PostgreSQL

PostgreSQL supports most of the popular database replication methods. Among them, due to their capabilities and usefulness, two are worth mentioning: streaming replication and logical replication.

Streaming Replication allows WAL XLOG records to be continuously sent to backup servers and used to ensure complete synchronization. Streaming replication in PostgreSQL follows a master-slave model, where the master is called the primary instance that serves the master database.

The second method we will focus on in this article is Logical Replication. Its main task is to meet the requirements of even the most demanding customers, whose database structure is extremely complex. The operation of this method is based on replicating database objects, usually based on their primary key.

Advantages of logical replication using pglogical

Logical replication uses the pglogical library, which has been incorporated into the heart of PostgreSQL since version 9.4. The replication module, due to its design, is backward-compatible also in lower versions of PostgreSQL. Other advantages of the module include:

• the ability to modify the replication scope
• replications with granularity to a single table
• specific DML types (as well as their combinations) replications
• no DDL replication (protection of data structure in replicas)
• logical replication is not based on triggers’ functions
• replication of a single database within one cluster
• grid replication (multiple provider and subscriber servers)
• no automatic replication of newly created objects (increases security level)
• the ability to use replicas in read/write mode (various mechanisms for resolving conflicts)
• the ability to use logical replication to upgrade between major versions of EuroDB (rolling upgrade).

Instructions for creating database replication

In order to create database replication using the EuroDB replication module, you must first install it according to the instructions you received with the product. After the package has been installed correctly, you still need to configure it by following the steps below:

This manual was created for the EuroDB 13 database and its default configuration. It will work for any PostgreSQL-based database. The commands for different database solutions may differ slightly because the paths to the configuration files depend on the name of the specific database. Logical replication is created on the dvdrental database, which can be downloaded by clicking here.

1. Edit the file /var/lib/pgsql_13.3-1/data/postgresql.conf.

wal_level = 'logical'
shared_preload_libraries = 'pglogical'

The values below depend on how many provider and subscriber server we decide to use:

max_worker_processes = 10   # one per database needed on provider node
                            # one per node needed on subscriber node
max_replication_slots = 10  # one per node needed on provider node
max_wal_senders = 10        # one per node needed on provider node

2. Configure the file /var/lib/pgsql_13.3-1/data/pg_hba.conf in order to allow the connection between providers and subscribers. E.g.:

host all all 192.168.121.214/32 trust

3. Restart the database with pg_ctl.

sudo -u postgres /usr/eurodb-13/bin/`pg_ctl` -D /var/lib/pgsql_13.3-1/data `stop`

sudo -u postgres /usr/eurodb-13/bin/`pg_ctl` -D /var/lib/pgsql_13.3-1/data `start`

4. Enter the PostgreSQL shell (psql) and switch to the proper database.

sudo -u postgres `psql`

\c `dvdrental`

5. Install the pglogical module.

create extension `pglogical`;

In case of an error, make sure that you have the pglogical library properly installed. Invoke the following command and reset the database:

alter system set shared_preload_libraries = '`pglogical`';

6. Create a proper structure for provider and subscribers.

Apart from creating a node, a provider has to add tables for replication, e.g.:

select pglogical.create_node (node_name := '`provider1`', dsn := 'host=`192.168.121.214` port=`5432` dbname=`dvdrental`');
    
select pglogical.replication_set_add_all_tables('`default`', ARRAY['`public`']);

Apart from creating a node, a subscriber has to create a subscription as well, e.g.:

select pglogical.create_node (node_name := '`subscriber1`', dsn := 'host=`192.168.121.98` port=`5432` dbname=`dvdrental`');

select pglogical.create_subscription(subscription_name := '`subscription1`', provider_dsn := 'host=`192.168.121.214` port=`5432` dbname=`dvdrental`', replication_sets := '`{default}`');

You can use the following command to check the nodes created this way:

select * from pglogical.node_interface;

In case of subscribers, you can check their subscriptions and their status in a similar way:

select subscription_name, status FROM pglogical.show_subscription_status();

7. If all went well and a subscription status is labeled as replicating, the creation of the database replication mechanism succeeded. To test it, you can, as an example, UPDATE any record in the provider database and each subscriber should immediately update its database.

Summary

Creating database replication is a key element that ensures the stability of an information system.  Pglogical allow you to create logical and streaming replication of a database in a simplified way, so you can be sure of the operation of the database, and be ready for potential malfunctions as well.