How to Set Up a PostgreSQL Master-Slave Replication on Ubuntu 20.04

Step 1: Configuring Postgres for Logical Replication

To perform the logical replication between your servers, there are quite some settings for you to configure. Firstly, you need to configure Postgres to listen on the private network interface rather than the public network since it’s prone to security breaches. Secondly, you need to configure the necessary settings to enable the replication to standby.
Go to your master server and open the main server configuration file by entering the below command.

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

Identify the following line.

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

Uncomment the above line to remove #, and add your master ip address.
Note: Always make sure that you only use the private IP addresses of your servers and never expose any of your public IPs as it may lead to a considerable security risk. But in this example, we go with the public ip address.

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

In addition to the loopback interface, you have ensured your master server listens for incoming connections on the public network.
Identify the following line.

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

In order to increase the volume of log entries, add the required information for extracting discrepancies or to make changes to particular data sets, uncomment the above lines so as to set the PostgreSQL Write Ahead Log (WAL) level to logical.

...
wal_level = logical
...

Generally, these log entries will be consumed by the standby server which in turn enables them with the replication of the high-level write operations from the master. Upon completion, save the file before you exit.
Now, you need to edit a file that controls the allowed hosts, authentication, and access to databases.

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

Add a line to allow incoming network connections from standby server after the last line of the above file. You’ll be using the standby’s public IP address and need to specify that the connections are allowed from all users and databases as well.

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

Now that you’ve carried out the required configuration, the incoming network connections will be allowed from the standby server, authenticated by a password hash (md5). Upon completion, save the file.
Next, you need to set your firewall rules so as to allow traffic from the standby server to port 5432 on the master server.

sudo ufw allow from standby_public_ip_address to any port 5432

Finally, to update the changes, restart your PostgreSQL server.

sudo systemctl restart postgresql

Now that you have configured the necessary settings to allow logical replication, let’s move forward with creating a database, user role, and table.
Note: Similarly carry out the above configuration on the standby server and make sure to allow the master server’s ip address in hba conf file and to change the listen value as well.

Step 2: Setting up a Database, User Role & Table

Post enabling logical replication between your servers, it’s time to test the functionality of your replication settings. To do so, you must create a sample database, table, and user role. Further, you need to create a dedicated user and assign them privileges over both the database and the table.
Open the psql prompt as the postgres user on master server.

sudo psql -h localhost -U postgres

Next, create the database and table.

create database demo;

Make sure to navigate the created db shell and to connect to the database you just created on the host.

\c demo

Note:

1. The final ; is crucial in executing the above commands. It is because until you terminate the SQL commands with a semicolon, PostgreSQL will not execute those SQL commands during the interactive sessions.
2. Since the meta-commands; starting with a backslash, like \q and \c directly control the psql client itself, they are exempt from the above rule. For detailed information, refer to the PostgreSQL documentation.

Now, create a new table called “testing”.

demo=# CREATE TABLE testing
demo-# (
demo(# id SERIAL,
demo(# name TEXT,
demo(# price DECIMAL,
demo(# CONSTRAINT testing_pkey PRIMARY KEY (id)
demo(# );

Let’s access the shell on the standby server.

sudo psql -h localhost -U postgres

Next create the database and table.

create database demo;

Make sure to navigate the created db shell.

\c demo

Create a new table called “testing”.

demo=# CREATE TABLE testing
demo-# (
demo(# id SERIAL,
demo(# name TEXT,
demo(# price DECIMAL,
demo(# CONSTRAINT testing_pkey PRIMARY KEY (id)
demo(# );

Note:

• Even though it’s not necessary that your standby’s table need not be identical with its master counterpart, it must contain every single column present on the table in your master server.
• Likewise, none of your additional columns should have NOT NULL or other constraints. If they do, then the replication will fail.

On your master server, create a new user role along with a REPLICATION option using your login password. The REPLICATION attribute must be assigned to any role used for replication. For example, we’re using the user name as “john” but you can replace this with your own username and don’t forget to replace “demo123” with your own secure password.

demo=# CREATE ROLE john WITH REPLICATION LOGIN PASSWORD 'demo123';

Note: Make a note of your password, as you’ll be needing it for the setting up the replication of your standby server.

demo=# GRANT ALL PRIVILEGES ON DATABASE demo TO john;
demo=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO john;

Step 3: Creating a Publication

It is through the method of Publications, PostgreSQL ensures that the tables are available for replication. On master, you need to create a publication, which forms the master copy of the data that will be sent to your subscribers, i.e. standby server.
On your master server, create a publication called demo_publication.

demo=# CREATE PUBLICATION demo_publication;

Next, add the testing table you have created in the previous step.

demo=# ALTER PUBLICATION demo_publication ADD TABLE testing;

Note: Instead of creating an individual publication for each table, you can straightaway create a publication for all the tables using the following command, for example:

demo=# CREATE PUBLICATION demo_publication FOR ALL TABLES;

Step 4: Creating a Subscription

PostgreSQL uses subscriptions so as to connect to existing publications. As a matter of fact, a publication can have as many as subscriptions across different replica servers, and the replica servers as well have the facility to have their own publications with subscribers. create a subscription to the publication you created in the previous step, demo_publication so as to access the data from the table you created on your master server.
On standby, you need to create a subscription using the command CREATE SUBSCRIPTION and CONNECTION to define the connection string to the publisher. In general, this connection string consists of the master server’s connection details and login credentials, username and password you defined earlier, along with the name of the demo database.
Note: Here, the demo_publication is the name which we have created for publication in the above step and don’t forget to replace “demo123” with your own password.

demo=# CREATE SUBSCRIPTION demo_subscription CONNECTION 'host=master_ip_address port=5432 password=demo123 user=john dbname=demo' PUBLICATION demo_publication;

As a confirmation of a successful creation of the subscription, you’ll get to see the below output.
Output

NOTICE: created replication slot "demo_subscription" on publisher
CREATE SUBSCRIPTION

Note: Post creating a subscription, all the pre-existing data from the master to the replica will be automatically synced. Here, we’ve no data to sync since the testing table is empty.

Having cleared all the routes with regard to subscription, let’s add some demo data to the testing table for checking the setup.

Step 5: Checking Master-Slave Communication

Let’s verify the communication between the master and standby server using the following steps.
On your standby server using the below command and make sure to update the necessary details.

sudo psql -h master_ip_address -U postgres -d demo

Output

psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
demo=#

Likewise, you can check it out from your master server also. Now, let’s check on the master server:

psql -h standby_server_public_ip -U postgres -d demo

Step 6: Testing the Query

To carry out your testing process and verify that the replication works as expected, add some data to the testing table.

On your master server, include the following data into your testing table:

demo=# INSERT INTO testing (name, price) VALUES ('abc', 4.50), ('def', 6.20), ('ghi', 3.80);

Output

INSERT 0 3

Let’s test our master server,

demo=# SELECT * FROM testing;

Output

id | name | price
----+------------+-------
1 | abc | 4.50
2 | def | 6.20
3 | ghi | 3.80
(3 rows)

To confirm whether the master-slave replication works as expected, navigate to the standby server and login to your postgres shell.

sudo psql -h localhost -U postgres

Navigate to your database.

\c demo

demo=# SELECT * FROM testing;

Output

id | name | price
----+------------+-------
1 | abc | 4.50
2 | def | 6.20
3 | ghi | 3.80
(3 rows)

Step 7: Troubleshooting

In case, if the replication does not work, ensure the following things.
Check for any errors in PostgreSQL log on standby server.

sudo tail /var/log/postgresql/postgresql-12-main.log

• Check whether the communication has been established between master and slave.
• Check the firewalls rules in both the servers.
• Check whether you have defined the ip address in hba conf file.

Conclusion:

This article would have walked you through the process involved in the setting up and configuration of master-slave replication on PostgreSQL 12 on the Ubuntu 20.0 system. As mentioned earlier, the master-slave replication on PostgreSQL is done using the synchronous (or logical) replication. Through performing logical replication followed by setting up the database, creation of publication and subscription, testing and troubleshooting, you would have familiarized with PostgreSQL’s horizontal scaling, high availability, geographic distribution and much more. Check out the PostgreSQL’s official documentation to get a better view of its architecture and features.