Step By Step Streaming Replication – PostgreSQL 12


Step By Step Streaming Replication In PostgreSQL 12

In this demonstration,We’ll see how to setup a Streaming Replication in PostgreSQL 12 running on Oracle Linux 6.8

Here, I assume that you have two servers (Primary/Master & Standby/Slave) having PostgreSQL 12 installed. And Database cluster is initialized using initdb on both the servers. If not, you might want to see Installation of PostgreSQL here.

Note that you don’t have to start the database server, just follow till initdb from this article.

In this demonstration Primary Server is 192.168.0.201 and Standby is 192.168.0.202

Primary Server Configurations.

  • So, as we have initialized the database cluster using initdb on both the servers. We’ll make some necessary changes in postgresql.conf file on the primary server.
[postgres@bcp ~]$ vi $PGDATA/postgresql.conf

listen_addresses = '*'
port = 5432
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /mnt/postgresql/12/data/archived_logs/%f'
max_wal_senders = 10
wal_keep_segments = 50
  • As we have made necessary changes in the postgresql.conf file. Let’s start the database server on primary server using below command.
[postgres@prod data]$ pg_ctl -D /mnt/postgresql/12/data -l logfile start
  • Once the database server is started, invoke psql as super user i.e. postgres and create a role for replication.
postgres=# CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'strongpassword';
CREATE ROLE
  • Allow connection to created user from Standby Server by seting pg_hba.conf
[postgres@prod ~]$ vi $PGDATA/pg_hba.conf

host replication repl_user 192.168.0.202/32 md5
  • Reload the setting of the cluster.
[postgres@prod data]$ pg_ctl -D /mnt/postgresql/12/data -l logfile reload

OR

[postgres@prod ~]$ psql -c "select pg_reload_conf()"
pg_reload_conf
----------------
t
(1 row)

We’re done with the configuration of the primary server. Now we’ll use pg_basebackup to get the data directory i.e. /mnt/postgresql/12/data to Standby Server.

  • Standby Server Configurations.

[postgres@bcp ~]$ pg_basebackup -h 192.168.0.201 -U repl_user -p 5432 -D $PGDATA -Fp -Xs -P -R
Password:
pg_basebackup: error: directory "/mnt/postgresql/12/data" exists but is not empty

  • Navigate to data directory and empty it.
[postgres@bcp ~]$ cd /mnt/postgresql/12/data/
[postgres@bcp data]$ rm -rf *
  • Now run,
[postgres@bcp data]$ pg_basebackup -h 192.168.0.201 -U repl_user -p 5432 -D $PGDATA -Fp -Xs -P -R
Password:
24880/24880 kB (100%), 1/1 tablespace

Note that here we saw only single line of output because our Primary Server’s database is a fresh server and have not database exist. If you’ll setup replication for existing database, you’ll see list of tablespaces here which will be replicated to standby as initial load.

  • Let’s now start the database server at standby side using below command.
[postgres@bcp data]$ pg_ctl -D /mnt/postgresql/12/data/ -l logfile start
waiting for server to start.... done
server started
  • Check the logfile to gather some more information.
[postgres@bcp log]$ tail postgresql-Sun.log 
2019-12-08 22:31:56.508 IST [24676] LOG: received SIGHUP, reloading configuration files
cp: cannot create regular file `/mnt/postgresql/12/data/archived_logs/000000010000000000000001': No such file or directory
2019-12-08 22:35:18.165 IST [24683] LOG: archive command failed with exit code 1
2019-12-08 22:35:18.165 IST [24683] DETAIL: The failed archive command was: cp pg_wal/000000010000000000000001 /mnt/postgresql/12/data/archived_logs/000000010000000000000001
2019-12-08 22:40:15.014 IST [14207] LOG: database system was interrupted; last known up at 2019-12-08 22:35:18 IST
2019-12-08 22:40:15.022 IST [14207] LOG: entering standby mode
2019-12-08 22:40:15.025 IST [14207] LOG: redo starts at 0/2000028
2019-12-08 22:40:15.026 IST [14207] LOG: consistent recovery state reached at 0/2000100
2019-12-08 22:40:15.026 IST [14205] LOG: database system is ready to accept read only connections
2019-12-08 22:40:15.033 IST [14211] LOG: started streaming WAL from primary at 0/3000000 on timeline 1

As we can see, it says entering standby mode and database system is ready for read only connections.

  • Let’s now check the replication. Invoke psql and perform some operations.

Primary Server

[postgres@prod ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Standby Server

[postgres@bcp log]$ psql
psql (12.1)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Let’s create database named demunix at primary side.

postgres=# create database demunix;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 demunix   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

Let’s list the databases at standby side again.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 demunix   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

There we go, replication is working as expected. Let’s create one demo table named demunix_test_table in demunix database and see if it replicates to standby side.

postgres=# \c demunix;
You are now connected to database "demunix" as user "postgres".
demunix=# create table demunix_test_table (serial int);
CREATE TABLE
demunix=# insert into demunix_test_table values (1);
INSERT 0 1

At standby side.

demunix=# select count(*) from demunix_test_table ;
 count 
-------
     1
(1 row)

There we go, DMLs are also replicating.
Peace 🙂