Introduction

One vital aspect of database administration is making copies of data and ensuring the replications remain in sync. With PostgreSQL there are two forms of native replication: physical replication, also known as binary replication, which sends changes at a disk block level and logical replication, which offers row-by-row changes streamed from a primary server to a secondary server. One of the major benefits of logical replication is that you may target certain tables instead of the entire database. This tutorial provides a guide for implementing logical replication between a primary and secondary server.

Assumptions

This tutorial assumes that you are operating on a Linux OS, and the examples shown are specific to Ubuntu 20.04 LTS. It is also assumed that PostgreSQL is already installed on your server and data in the database already exists. The user performing commands should have sudo access to the postgres user.

Variables

The primary server will have an example IP address of 198.168.0.1, and the secondary server will have an IP address of 198.168.0.2. The user, database, table, publication, subscription names, and target directories/files are examples and will need modification specific to you and your system.

____________________________________________________________________________

Primary Server Procedure

Step 1: Set Up the Replication Role

Let us begin by creating a role with replication privileges, as this is mandatory to carry out any replication procedures. First, we login as the postgres user and initiate the psql prompt to run the commands:

sudo -iu postgres psql

Now, we create the role replicator with:

CREATE ROLE replicator WITH REPLICATION LOGIN;

For security reasons, we don’t want the password stored anywhere in plain text and accessible to anyone with nefarious intentions, so we use the \password meta-command to conceal our password. First we set the superuser postgres’s password, as this will be needed for the data dumps:

\password

Enter the password when prompted. Then for our replication user:

\password replicator

Now, we exit the psql prompt and return to the system user:

exit

Step 2: Edit the Configuration File

We must edit the PostgreSQL configuration file (postgresql.conf) in order for replication of any data to occur. On Ubuntu 20.04 LTS, the configuration file is located in the /etc/postgresql/12/main directory. The 12 in the path name reflects the use of PostgreSQL 12 in this scenario; replace it with the version you are working with or the correct path to the configuration file on the OS you are using. We will open an editor with:

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

The comment hash (#) must be removed from the beginning of each line we want to enable so the system will recognize it as an active setting. Find the line with listen_addresses and alter the text within quotes to be the IP address of the primary server, or a wildcard (*) to indicate that PostgreSQL will listen to all addresses it has access to. It will look something like this:

listen_addresses = '198.168.0.1'

or

listen_addresses = '*'

To keep the connection secure, we will set the encryption on passwords to md5. We do so by editing the password_encryption line to look like this:

password_encryption = md5

The Write-Ahead Log (WAL) is the log of changes made to a database cluster to be used as part of the database recovery process or, as in this case, to replay changes in the database for replication. The wal_level setting determines how much information is written into the WAL file. We want enough information in the WAL file to perform logical replication, so we set wal_level to logical, like so:

wal_level = logical

NOTE: During the initial sync, the WAL files will build up on the primary. Additionally, any pause during logical replication will cause the WAL files to build up on the primary.

Now we will set the max_replication_slots to a minimum of the number of subscriptions that will connect, plus some reserve to account for table synchronization. This setting is not so easily changed on a running database because it will need a restart for changes to take effect, therefore it is often a good idea to set it a bit higher (the default of 10) to account for a growing system and future needs. Since we will only be connecting one subscription, we will set the value to 3 to account for synchronization:

max_replication_slots = 3

The max_wal_senders setting should be equal to max_replication_slots, plus the number of physical replicas that will be connected simultaneously. Since we do not have any physical replicas connected, we also set this to 3:

max_wal_senders = 3

Now that we have made the changes to this configuration file, we will save and exit the postgresql.conf file.

Step 3: Edit the Host-Based Authentication File

Next, we need to allow access to the database via the PostgreSQL Host-Based Authentication configuration file (pg_hba.conf), which should be located in the same directory as postgresql.conf. Open the editor with:

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

The pg_hba.conf file has 5 fields that need to be filled out in order to authenticate a client to use PostgreSQL. They are the host type (TYPE), database name (DATABASE), user name (USER), IP address (ADDRESS), and encryption method (METHOD). In order to match connection attempts using TCP/IP, we set our TYPE to host. We enter all as the record for DATABASE to have access to all databases. We enter all under USER because the role we created and the superuser (postgres) will need access from the secondary. Under ADDRESS, we enter the IP address of the secondary server that will be accessing the database. Under METHOD, we enter the encryption method we are using, which is md5. Add the following line to pg_hba.conf:

# TYPE DATABASE USER ADDRESS METHOD

host all all 198.168.0.2/32 md5

We have finished entering authentication information, so we will save and exit pg_hba.conf.

Step 4: Restart/Reload PostgreSQL

For the changes made to postgresql.conf and pg_hba.conf to take effect, we must restart PostgreSQL. The systemd service unit name will vary depending on the OS and software package source you are using. In Debian (Ubuntu) and most Linux systems, we can do so with the systemctl command:

NOTE: If you changed the listen_addresses or wal_level settings in postgresql.conf while following this tutorial, a restart will be required. Otherwise, you can simply reload the database for any other setting changes by replacing restart with reload in the following commands.

sudo systemctl restart postgresql

It is likely to vary between systems and versions of PostgreSQL. For example, it is possible that you will need to use a command like this:

sudo systemctl restart postgresql-12

Or perhaps something like this:

sudo systemctl restart postgresql@12-main

In these examples, you would replace the 12 with the version you are working with.

Alternative Method: To reload PostgreSQL from within, you can use the following command:

SELECT pg_reload_conf();

Step 5: Granting User Privileges

Now that we have a user role with replication privileges, we want to grant that user access to the data that we want to replicate. One of the beauties of logical replication is the ability to fully customize and target the data you are replicating without replicating everything. A full list of options for granting privileges can be found here. Let’s first switch to our postgres user and psql prompt:

sudo -iu postgres psql

Next, we need to connect the postgres user to the tutorial database with the \connect meta-command:

\c tutorial

We will be granting connection privileges on the tutorial database and tables in the public schema to our user. We can do so with:

GRANT CONNECT ON DATABASE tutorial TO replicator;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;

Step 6: Creating the Publication

In order for tables to be available for replication, we must publish them. The publication we create will serve as a master copy of the data for any subscription that is connected to it. Once we have created the publication, we will also alter the publication to include the table we will be replicating. This can be done like so:

CREATE PUBLICATION best_pub;
ALTER PUBLICATION best_pub ADD TABLE first_table;

More tables can be added in the future with the same method. This concludes the configuration setup we need on the primary server.

Secondary Server Procedure

Now, let’s move to the secondary server for configuration and creating a subscription to the publication from the primary server.

Step 7: Setting Up .pgpass

To increase fluidity for our replication process and prevent possible security breaches, we will store PostgreSQL user passwords in the .pgpass file. We want this file to be located in the postgres user’s home directory, which is /var/lib/postgresql in Debian-based systems and /var/lib/psql in Red Hat systems. Let’s create the file with:

sudo touch /var/lib/postgresql/.pgpass

For postgres to use the .pgpass file, we must ensure postgres owns the file. For security purposes, we will restrict access to the file so only the postgres user can write on and read the file. We can change the ownership and change the mode of the .pgpass file with the following command:

sudo chown postgres:postgres /var/lib/postgresql/.pgpass; sudo chmod 0600 /var/lib/postgresql/.pgpass

Now that its data is properly protected, Let’s open .pgpass with:

sudo nano /var/lib/postgresql/.pgpass

You will see that the lines in this file follow the format:

hostname:port:database:username:password

For simplicity, you can use wildcards (*) in the hostname, port, and database fields. The wildcards indicate that the username and password will be applicable to any hostname, port, or database to which the user has access. For this tutorial and our desired security levels, we will be more specific and explicitly name the hostname, port, and database for which the username and password will be applicable. Based on the user and password (iwonttell) used when creating the replication role, we will add this line:

198.168.0.1:5432:tutorial:replicator:iwonttell

Now let’s save and exit the file.

Step 8: Copy Roles and Database Schema

Now that we have .pgpass configured where we are not entering passwords where they are visible, we want to pull the roles that have been previously set on the primary server. We can use the pg_dumpall with the roles-only (-r) option to accomplish this. The following command will create the roles.dmp file in the postgres home directory:

sudo pg_dumpall -U postgres -r -h 198.168.0.1 -f /var/lib/postgresql/roles.dmp

Depending on your system, this may ask you for a password. It will be asking for the postgres user’s password.

Next, let’s take a dump of the tutorial schema using the options schema (-s) and custom format (-Fc), which will allow us to use pg_restore to recreate the schema on the secondary:

sudo pg_dump -U postgres -Fc -h 198.168.0.1 -f /var/lib/postgresql/schema.dmp -s tutorial

Note: When using RDS and other cloud providers, passwords will need to be reset on the secondary, as passwords do not transfer as part of the dump. Additionally, tablespaces are not taken into account during logical replication and sequences do not replicate in this process.

Step 9: Recreate Roles and Database Schema

Now that we have our roles and schema copied from the primary, we will recreate them on the secondary, which will duplicate the replicator role and the framework of our table. Let’s first sign in as the postgres user:

sudo -iu postgres

Next, we will call psql to unpack the roles.dmp file, and we can omit the file path since this prompt source is the PostgreSQL home directory:

psql -f roles.dmp

Now, we will restore the contents of the schema.dmp file by connecting through the postgres database and using the create option (-C):

pg_restore -d postgres -C schema.dmp

Step 10: Creating the Subscription

Finally, we arrive at the subscription to the publication. We have already created the publication on the primary server, which makes the data we want to replicate available. Now, we need to subscribe to that publication in order to have access to the data. First, let’s go to the psql prompt and connect to the tutorial database we just recreated. Since you should be at the postgres user prompt, go to the psql prompt with:

psql

Now, connect to the tutorial database with:

\c tutorial

The CREATE SUBSCRIPTION parameter creates and names the subscription. The CONNECTION parameter defines details for our connection with the primary server. These details include host IP address, port number (5432 is the postgres default), password, user name, and database name. We will omit the password in our example, as we have set it for automatic application via the .pgpass file. And, the PUBLICATION parameter states the name of the publication we are subscribing to:

CREATE SUBSCRIPTION best_sub CONNECTION 'host=198.168.0.1 port=5432 user=replicator dbname=tutorial' PUBLICATION best_pub;

Follow-Up Procedures

Step 11: Confirm Successful Replication

Let’s confirm that the replication was successful. Run the following query at the psql prompt:

SELECT * FROM first_table;

If the data on the secondary matches the data from the primary, the replication has been successful. Let’s make sure the data is streaming properly. Return to primary and insert some data into first_table:

INSERT INTO first_table VALUES ('some data', 101);

Make sure the data was inserted properly (substitute data to match what you inserted):

SELECT * FROM first_table WHERE first_column='some data';

Now, let’s return to secondary to make sure the changes have been made:

SELECT * FROM first_table WHERE first_column='some data';

If the data matches the data from the primary, you have successfully set up streaming logical replication!

Step 12: Monitor Replication

It is important to monitor the replication that you have set up because sometimes the primary or the secondary has a heavy load and may have trouble keeping up with data transfer. It is also important to diagnose and address any network related issues that might occur. From the psql prompt, for visual clarity let’s first create an expanded view format with the meta-command:

\x

And now let’s look at the replication statistics from the primary. It should look something like this:

SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 21111
usesysid | 24576
usename | replicator
application_name | best_sub
client_addr | 198.168.0.2
client_hostname |
client_port | 50330
backend_start | 2022-05-18 17:18:10.05812+00
backend_xmin |
state | streaming
sent_lsn | 0/16776F8

write_lsn | 0/16776F8

flush_lsn | 0/16776F8

replay_lsn | 0/16776F8

write_lag | 00:00:00.000049
flush_lag | 00:00:00.00053
replay_lag | 00:00:00.000562

sync_priority | 0

sync_state | async

reply_time | 2022-05-19 14:58:58.667474+00

Let’s briefly discuss what some of these mean. First of all, the record itself indicates that there is an active subscription and the state line that reads streaming indicates that it is actively streaming data. The lines with the labels ending with _lsn indicate the location on the secondary of the WAL file for each stage of replication; we will return to the sent_lsn momentarily. Also important are the lines with labels ending with _lag. These indicate the lag times for their respective tasks. All of these lag times are very low, but if they have high levels, you can pinpoint where delays occur in the process. The absence of values on these lines indicates that everything is in sync.

Now let’s take a look at the subscription statistics on the secondary. First, we make the layout easier to read with:

\x

Now we run the query:

SELECT * FROM pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------

subid | 16399

subname | best_sub

pid | 21069

relid |

received_lsn | 0/16776F8

last_msg_send_time | 2022-05-19 14:58:48.656812+00

last_msg_receipt_time | 2022-05-19 14:58:48.656994+00

latest_end_lsn | 0/16776F8

latest_end_time | 2022-05-19 14:58:48.656812+00

We can see some important data points between the two queries. Again, the record itself indicates an active subscription. You’ll notice that the location of the latest_end_lsn on the secondary and the sent_lsn on the primary are identical, indicating that the two servers are in sync.

There are a host of other things we can look at for replication monitoring, but that is out of the scope of this tutorial. Perhaps there will be a future tutorial that covers these in more detail…

Summary

And there you have it! This tutorial has covered configuration, publication, and subscription settings necessary for logical replication. This should offer a thorough understanding of how to set up logical replication using .pgpass and md5 for increased security. From now on, any changes made on the primary server via INSERT, UPDATE, and UPDATE will be reflected on the secondary server.