How To Set Up Logical Replication with PostgreSQL 10 on Ubuntu 18.04

by cybersal

PostgreSQL is an open-source object-relational database system that is highly extensible and compliant with ACID (Atomicity, Consistency, Isolation, Durability) and the SQL standard. Version 10.0 of PostgreSQL introduced support for logical replication, in addition to physical replication.

In a logical replication scheme, high-level write operations are streamed from a master database server into one or more replica database servers.

Prerequisites

  • Two Ubuntu 18.04 servers, which we’ll name db-master and db-replica, each set up with a regular user account and sudo privileges. To set these up, follow this initial server setup tutorial.
  • Private networking enabled on your servers.
  • PostgreSQL 10 installed on both servers, following Step 1 of How To Install and Use PostgreSQL on Ubuntu 18.04.

Configuring PostgreSQL for Logical Replication

On db-master, open /etc/postgresql/10/main/postgresql.conf, the main server configuration file:

sudo nano /etc/postgresql/10/main/postgresql.conf

Find the following line and uncomment it by removing the #, and add your db_master_private_ip_address to enable connections on the private network:

...
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
...
...
listen_addresses = 'localhost, db_master_private_ip_address'
...

Note: In this step and the steps that follow, make sure to use the private IP addresses of your servers, and not their public IPs. Exposing a database server to the public internet is a considerable security risk.

This makes db-master listen for incoming connections on the private network in addition to the loopback interface.

Next, find the following line and Uncomment it, and change it to set the PostgreSQL Write Ahead Log (WAL) level to logical. This increases the volume of entries in the log, adding the necessary information for extracting discrepancies or changes to particular data sets:

...
#wal_level = replica                    # minimal, replica, or logical
...
...
wal_level = logical
...

Save the file and close it.

Next, let’s edit /etc/postgresql/10/main/pg_hba.conf, the file that controls allowed hosts, authentication, and access to databases:

sudo nano /etc/postgresql/10/main/pg_hba.conf

Add a line at the end of the file, to allow incoming network connections from db-replica. Use db-replica’s private IP address, and specify that connections are allowed from all users and databases:

..
# TYPE      DATABASE        USER            ADDRESS                               METHOD
...
host         all            all             db_replica_private_ip_address/32      md5

Save the file and close it.

Now set your firewall rules to allow traffic from db-replica to port 5432 on db-master

Rrestart the PostgreSQL server for the changes to take effect

sudo systemctl restart postgresql

With your configuration set to allow logical replication, you can now move on to creating a database, user role, and table.

Related Articles

Leave a Comment

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More

Privacy & Cookies Policy