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.confFind 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.confAdd 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 md5Save 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 postgresqlWith your configuration set to allow logical replication, you can now move on to creating a database, user role, and table.
