PostgreSQL Streaming Replication Setup

Preamble

PostgreSQL replication: What we want to achieve

It makes sense to consider our goals before beginning the PostgreSQL configuration process. The objective of this tutorial is to build a primary server that uses asynchronous replication to replicate data to a secondary server.

CentOS 8.3 will be used for the entire setup. The procedure should be the same on RHEL (Redhat Enterprise Linux). Just stick to the same steps.

We are using two virtual machines with the following IP addresses to demonstrate how the setup works:

• Primary: Node1 at 10.0.3.200
• Secondary: Node 2 at 10.0.3.201

Let’s set up these systems gradually.

Setting up PostgreSQL

After installing CentOS or RHEL, you can begin configuring PostgreSQL’s installation. Visit the PostgreSQL website and follow the instructions there to accomplish that. This script demonstrates how everything operates. You can simply copy / paste the script:

 sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo dnf -qy module disable postgresql sudo dnf install -y postgresql13-server # can be skipped on the 2nd node sudo /usr/pgsql-13/bin/postgresql-13-setup initdb sudo systemctl enable postgresql-13 # can be skipped on the 2nd node sudo systemctl start postgresql-13 

Check what should appear on node1 right now:

 [root@node1 ~]# ps axf | grep post 5542 pts/1 S+ 0:00 \_ grep --color=auto post 5215 ? Ss 0:00 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/ 5217 ? Ss 0:00 \_ postgres: logger 5219 ? Ss 0:00 \_ postgres: checkpointer 5220 ? Ss 0:00 \_ postgres: background writer 5221 ? Ss 0:00 \_ postgres: walwriter 5222 ? Ss 0:00 \_ postgres: autovacuum launcher 5223 ? Ss 0:00 \_ postgres: stats collector 5224 ? Ss 0:00 \_ postgres: logical replication launcher 

After completing this process, you should have:

• downloaded binaries
• A fully functional node1.
  • setup of systemd scripts
  • initialized database instances
• An ready second node (node2)
  • installed binaries
  • setup of systemd scripts

Let’s proceed to the subsequent step, which is to turn off the firewall on the primary.

 [root@node1 ~]# systemctl disable firewalld Removed /etc/systemd/system/multi-user.target.wants/firewalld.service. Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service. [root@node1 ~]# systemctl stop firewalld 

Why is that required? The replica will use port 5432 to connect to the master. The replica cannot access port 5432 if the firewall is still running. For the sake of simplicity for the reader, the firewall will be completely turned off in our example. You might want to do this in a more precise manner in a more secure setup.

establishing the primary’s replication settings

On the main server, we need to do the following four things:

• Connect to the internet (bind addresses)postgresql.conf
• Create a replication user (best practice but not mandatory)
• enabling remote accesspg_hba.conf
• Restart the primary server

These actions can be carried out gradually.

The first thing is to change postgresql.conf. The file can be found in /var/lib/pgsql/13/data/postgresql.conf. However, if you have no clue where to find postgresql.conf you can ask PostgreSQL itself to point you to the configuration file. Here is how it works:

 [root@node1 ~]# su - postgres [postgres@node1 ~]$ psql postgres psql (13.2) Type "help" for help. postgres=# SHOW config_file; config_file ---------------------------------------- /var/lib/pgsql/13/data/postgresql.conf (1 row) 

The following parameter has to be changed in postgresql.conf:

listen_addresses = '*'

What does listen_addresses mean? By default, PostgreSQL only listens on localhost. For security purposes, remote access is not by default permitted. PostgreSQL must be trained to listen for remote requests as a result. In other words: listen_addresses defines the bind addresses of our database service. Without it, remote access is not possible (even if you change pg_hba.conf later on).

Following that, the user can be created in the database:

 postgres=# CREATE USER repuser REPLICATION; CREATE ROLE 

Of course, a password can also be set. What is important here is that the user has the REPLICATION flag set. Keep in mind that you shouldn’t use the superuser to stream the transaction log from the primary to the replica.

The next thing we can do is to change pg_hba.conf, which controls who is allowed to connect to PostgreSQL from which IP. Please add the following line to the configuration file:

host replication repuser 10.0.3.201/32 trust

We want to allow the repuser coming from 10.0.3.201 to log in and stream the transaction log from the primary. Keep in mind that 10.0.3.200 is the primary in our setup and 10.0.3.201 is the replica.

Finally, we can restart the primary because we have changed listen_addresses in postgresql.conf. If you only changed pg_hba.conf a reload is enough:

 [postgres@node1 ~]$ exit logout [root@node1 ~]# systemctl restart postgresql-13 

Your system is now ready, and we can focus our attention on the replica.

Creating a base backup

Making the replica is the next step. To make this work, we must take a number of steps. Before proceeding, the replica must be stopped and the data directory must be empty. First, let’s confirm that the service has been stopped:

 [root@node2 ~] ~] # systemctl stop postgresql-13 

The data directory must then be empty, so check that:

 [root@node2 ~] ~] # cd /var/lib/pgsql/13/data/ [root@node2 data] # ls PG_VERSION global pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_xact postmaster.opts base log pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase postgresql.auto.conf current_logfiles pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans pg_wal postgresql.conf [root@node2 data] data] # rm -rf * 

Note that this step is not necessary if you skipped the initdb step during installation. But you need to do this if you want to make a copy of an existing server.

 [root@node2 data] data] # su postgres bash-4.4$ pwd /var/lib/pgsql/13/data bash -4.4$ pg_basebackup -h 10.0.3.200 -U repuser --checkpoint=fast \ -D /var/lib/pgsql/13/data/ -R --slot=some_name -C 

pg_basebackupwill establish a connection with the primary and simply transfer all the data files. The connection has to be made as repuser. To ensure that the copy process starts instantly, it makes sense to tell PostgreSQL to quickly checkpoint. The-D flag defines the destination directory where we want to store the data on the replica. The-R flag automatically configures our replica for replication. On the secondary server, no further configuration is required. Finally, a replication slot was created. What purpose does PostgreSQL’s replication slot serve? In essence, the primary server has the ability to recycle the WAL if the primary no longer requires it. What if, however, the replica has not yet eaten it? The replica will then fail unless a replication slot is available, ensuring that the primary can only recycle the WAL after the replica has used it all up. 

Let’s figure out what pg_basebackup has done:

 bash-4.4$ ls -l total 196 -rw-------. 1 postgres postgres 3 Feb 12 09:12 PG_VERSION -rw-------. 1 postgres postgres 224 Feb 12 09:12 backup_label -rw-------. 1 postgres postgres 135413 Feb 12 09:12 backup_manifest drwx ------. 5 postgres postgres 41 Feb 12 09:12 base -rw-------. 1 postgres postgres 30 Feb 12 09:12 current_logfiles drwx ------. 2 postgres postgres 4096 Feb 12 09:12 global drwx ------. 2 postgres postgres 32 Feb 12 09:12 log drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_commit_ts drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_dynshmem -rw -------. 1 postgres postgres 4598 Feb 12 09:12 pg_hba.conf -rw -------. 1 postgres postgres 1636 Feb 12 09:12 pg_ident.conf drwx ------. 4 postgres postgres 68 Feb 12 09:12 pg_logical drwx ------. 4 postgres postgres 36 Feb 12 09:12 pg_multixact drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_notify drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_replslot drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_serial drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_snapshots drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_stat drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_stat_tmp drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_subtrans drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_tblspc drwx ------. 2 postgres postgres 6 Feb 12 09:12 pg_twophase drwx ------. 3 postgres postgres 60 Feb 12 09:12 pg_wal drwx ------. 2 postgres postgres 18 Feb 12 09:12 pg_xact -rw -------. 1 postgres postgres 335 Feb 12 09:12 postgresql.auto.conf -rw -------. 1 postgres postgres 28014 Feb 12 09:12 postgresql.conf -rw -------. 1 postgres postgres 0 Feb 12 09:12 standby.signal 

pg_basebackupHowever, there is more because the standby.signal file, which notifies the replica that it is a replica, has been created.

Finally, the tooling has adjusted the postgresql.auto.conf file which happens to contain all the configuration needed to make the replica connect to its replica on the primary server (node 1):

 bash-4.4$ cat postgresql.auto.conf Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = "user=Repuser" passfile = "/var/lib/pgsql/.pgpass" channel_binding=prefer host=10.0.3.200 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=Postgres target_session_attrs=any primary_slot_name = 'some_name' 

Voilà, we’re done, and the replica can now be started.

Firing up the replica

We are ready to start the replica using systemctl:

 bash-4.4$ exit exit [root@node2 data]# systemctl start postgresql-13 [root@node2 data]# ps axf | grep post 36394 pts/1 S+ 0:00 \_ grep --color=auto post 36384 ? Ss 0:00 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/ 36386 ? Ss 0:00 \_ postgres: logger 36387 ? Ss 0:00 \_ postgres: startup recovering 000000010000000000000007 36388 ? Ss 0:00 \_ postgres: checkpointer 36389 ? Ss 0:00 \_ postgres: background writer 36390 ? Ss 0:00 \_ postgres: stats collector 36391 ? Ss 0:00 \_ postgres: walreceiver streaming 0/7000148 

It’s a good idea to check that the processes are indeed running. It’s especially important to check for the existence of the walreceiver process. walreceiver is in charge of fetching the WAL from the primary. If it is not there, your setup has failed.
Make sure the service is turned on as well.

Checking your PostgreSQL replication setup

It makes sense to look at monitoring after the setup is complete. Generally speaking, it makes sense to use a program like pgwatch2 to expertly monitor your database.

Let’s check the primary first:

 [root@node1 ~] ~] # su - postgres [postgres@node1 ~] ~] $ psql postgres psql (13.2) Type "help" for help. postgres=# \x Expanded display is on. postgres=# SELECT * FROM pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 6102 usesysid | 16385 usename | repuser application_name | walreceiver client_addr | 10.0.3.201 client_hostname | client_port | 34002 backend_start | 2021-02-12 09:27:59.53724-05 backend_xmin | state | streaming sent_lsn | 0/7000148 write_lsn | 0/7000148 flush_lsn | 0/7000148 replay_lsn | 0/7000148 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2021-02-12 09:29:49.783076-05 

The existence of a row in pg_stat_replication tells us that WAL is flowing from the primary to a secondary.

However, we can also make a check on the replica:

 [root@node2 data] data] # su - postgres [postgres@node2 ~] ~] $ psql postgres psql (13.2) Type "help" for help. postgres=# \x Expanded display is on. postgres=# SELECT * FROM pg_stat_wal_receiver; -[ RECORD 1 ]---------+-------------------------------------------- pid | 36391 status: "streaming receive_start_lsn | 0/7000000" receive_start_tli | 1 written_lsn | 0/7000148 flushed_lsn | 0/7000148 received_tli | 1 last_msg_send_time | 2021-02-12 09:29:59.683418-05 last_msg_receipt_time | 2021-02-12 09:29:59.674194-05 latest_end_lsn | 0/7000148 latest_end_time | 2021-02-12 09:27:59.556631-05 slot_name | some_name sender_host | 10.0.3.200 sender_port | 5432 conninfo | user=repuser passfile=/var/lib/pgsql/.pgpass channel_binding=prefer dbname=replication host=10.0.3.200 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any 

A row in pg_stat_wal_receiver ensures that the WAL receiver does indeed exist, and that data is flowing.

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.