PostgreSQL 11 Streaming Replication/Hot Standby

Multi-Node Cluster Setup

Primary Host : 10.0.0.201

HOSTNAME  : repnode1

Primary Node : Sharjah_primdb

Primary DB Port        : 6001

 

Secondary Host : 10.0.0.202

HOSTNAME  : repnode2

Secondary Node : Sharjah_secondby

Secondary DB Port : 6001

PRIMARY SIDE #

Create Directory under pgdata path :   /var/lib/pgsql/11

Cd /var/lib/pgsql/11

-bash-4.2$ hostname -i -a

repnode1

-bash-4.2$ hostname -i

10.0.0.201

-bash-4.2$ cd $PGDATA

-bash-4.2$ pwd

/var/lib/pgsql/11

-bash-4.2$

-bash-4.2$ mkdir -p sharjah_primdb

-bash-4.2$ ls -d *shar*

sharjah_primdb

-bash-4.2$

Create Sharjah_primdb database using initdb from $PGPATH

Pgpath =>

-bash-4.2$ echo $PGPATH

/usr/pgsql-11/bin

-bash-4.2$

/usr/pgsql-11/bin/initdb –D Sharjah –D Sharjah_primdb

New database directory structure is created as below

/var/lib/pgsql/11/sharjah_primdb

-bash-4.2$ ls -tlr

total 132

drwx------. 2 postgres postgres  4096 Oct 27 11:18 pg_twophase

drwx------. 2 postgres postgres  4096 Oct 27 11:18 pg_tblspc

drwx------. 2 postgres postgres  4096 Oct 27 11:18 pg_snapshots

drwx------. 2 postgres postgres  4096 Oct 27 11:18 pg_serial

drwx------. 4 postgres postgres  4096 Oct 27 11:18 pg_multixact

drwx------. 2 postgres postgres  4096 Oct 27 11:18 pg_dynshmem

drwx------. 2 postgres postgres  4096 Oct 27 11:18 pg_commit_ts

-rw-------. 1 postgres postgres     3 Oct 27 11:18 PG_VERSION

-rw-------. 1 postgres postgres    88 Oct 27 11:18 postgresql.auto.conf

-rw-------. 1 postgres postgres  1636 Oct 27 11:18 pg_ident.conf

drwx------. 2 postgres postgres  4096 Oct 27 11:18 pg_xact

drwx------. 2 postgres postgres  4096 Oct 27 11:18 pg_subtrans

drwx------. 2 postgres postgres  4096 Oct 27 11:28 log

-rw-------. 1 postgres postgres 23922 Oct 27 14:09 postgresql.conf

drwx------. 2 postgres postgres  4096 Oct 29 14:45 pg_replslot

-rw-------. 1 postgres postgres  4529 Oct 29 16:55 pg_hba.conf

drwx------. 3 postgres postgres  4096 Oct 29 16:56 pg_wal

drwx------. 2 postgres postgres  4096 Oct 29 16:56 pg_notify

-rw-------. 1 postgres postgres    49 Oct 29 16:56 postmaster.opts

-rw-------. 1 postgres postgres   106 Oct 29 16:56 postmaster.pid

drwx------. 2 postgres postgres  4096 Oct 29 16:56 pg_stat

drwx------. 2 postgres postgres  4096 Oct 29 16:57 global

drwx------. 9 postgres postgres  4096 Oct 29 17:00 base

drwx------. 4 postgres postgres  4096 Oct 29 17:05 pg_logical

drwx------. 2 postgres postgres  4096 Oct 30 00:00 pg_log

-rw-------. 1 postgres postgres    47 Oct 30 00:00 current_logfiles

drwx------. 2 postgres postgres  4096 Oct 30 09:41 pg_stat_tmp

-bash-4.2$

Open the postgresql.conf file, update the below parameters and then start the db services

#------------------------------------------------------------------------------

# CONNECTIONS AND AUTHENTICATION

#------------------------------------------------------------------------------

port = 6001

listen_addresses = '*'

wal_level = replica

Start the database services in   repnode1

/usr/pgsql-11/bin/pg_ctl -D sharjah_primdb -l logfile start

-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D sharjah_primdb/ start

waiting for server to start....2019-10-27 11:28:04.320 +04 [15761] LOG:  listening on IPv4 address "0.0.0.0", port 6001

2019-10-27 11:28:04.320 +04 [15761] LOG:  listening on IPv6 address "::", port 6001

2019-10-27 11:28:04.322 +04 [15761] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.6001"

2019-10-27 11:28:04.324 +04 [15761] LOG:  listening on Unix socket "/tmp/.s.PGSQL.6001"

2019-10-27 11:28:04.349 +04 [15761] LOG:  redirecting log output to logging collector process

2019-10-27 11:28:04.349 +04 [15761] HINT:  Future log output will appear in directory "log".

 done

server started

-bash-4.2$

[root@Replica_node1 ~]# sudo su - postgres

Last login: Wed Oct 30 09:37:52 +04 2019 on pts/1

-bash-4.2$ ps -ef | grep postgres

postgres 15771     1  0 Oct29 ?        00:00:01 /usr/pgsql-11/bin/postgres -D sharjah_primdb

postgres 15772 15771  0 Oct29 ?        00:00:00 postgres: logger

postgres 15774 15771  0 Oct29 ?        00:00:00 postgres: checkpointer

postgres 15775 15771  0 Oct29 ?        00:00:00 postgres: background writer

postgres 15776 15771  0 Oct29 ?        00:00:00 postgres: walwriter

postgres 15777 15771  0 Oct29 ?        00:00:00 postgres: autovacuum launcher

postgres 15778 15771  0 Oct29 ?        00:00:00 postgres: archiver

postgres 15779 15771  0 Oct29 ?        00:00:01 postgres: stats collector

postgres 15780 15771  0 Oct29 ?        00:00:00 postgres: logical replication launcher

postgres 15783 15771  0 Oct29 ?        00:00:00 postgres: walsender postgres 10.0.0.202(43006) streaming 0/20000E08

root     17761 17717  0 09:37 pts/1    00:00:00 sudo su – postgres

Now Update postgresql.conf file with the below parameters for Archive,Replication and logging then restart the db services .

-bash-4.2$ pwd

/var/lib/pgsql/11/sharjah_primdb

-bash-4.2$ ls -ltr postgresql.conf

-rw-------. 1 postgres postgres 23922 Oct 27 14:09 postgresql.conf

-bash-4.2$

-bash-4.2$ vi postgresql.conf

# - Archiving –

archive_mode = on

archive_command = 'scp %p postgres@10.0.0.202:/var/lib/pgsql/11/str_archive/%f'

#------------------------------------------------------------------------------

# REPLICATION

#------------------------------------------------------------------------------

max_wal_senders = 2             # max number of walsender processes

wal_keep_segments = 50          # in logfile segments; 0 disables

#------------------------------------------------------------------------------

# REPORTING AND LOGGING

#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr'

# This is used when logging to stderr:

logging_collector = on

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_truncate_on_rotation = on

log_rotation_age = 1d

log_rotation_size = 10MB

 

Sharjah_primdb is started..!

· Let us check the parameters 

postgres=# show archive_mode;

 archive_mode

--------------

 on

(1 row)

postgres=# show archive_command;

                        archive_command

----------------------------------------------------------------

 scp %p postgres@10.0.0.202:/var/lib/pgsql/11/str_archive/%f

(1 row)

postgres=# show max_wal_senders;

 max_wal_senders

-----------------

 2

(1 row)

postgres=# show wal_keep_segments;

 wal_keep_segments

-------------------

 50

(1 row)

postgres=#

Now check the archive destination in standby node to find the archive files are generated on the str_archive on repnode2 .

-bash-4.2$ hostname -a

repnode2

-bash-4.2$

/var/lib/pgsql/11/str_archive

-bash-4.2$ ls -l

total 507908

-rw-------. 1 postgres postgres 16777216 Oct 28 14:59 000000010000000000000001

Now let us configure ssh key configuration for password less authentication of postgres user on both nodes (repnode1, repnode2).

Update theses parameters under /etc/ssh/ssh_config file in root user and then restart the sshd services

[root@Replica_node1 ~]# locate ssh_config

/etc/ssh/ssh_config

/usr/share/man/man5/ssh_config.5.gz

 [root@Replica_node1 ~]# vi /etc/ssh/ssh_config

Host *

StrictHostKeyChecking no

[root@Replica_node2 ~]# vi /etc/ssh/ssh_config

[root@Replica_node2 ~]#

Host *

StrictHostKeyChecking no

[root@Replica_node1 ~]# service sshd restart

[root@Replica_node2 ~]# service sshd restart

[root@Replica_node1 ~]# service sshd status

Redirecting to /bin/systemctl status sshd.service

● sshd.service - OpenSSH server daemon

   Loaded: loaded (/usr/lib/systemd/system/sshd.service; enabled; vendor preset: enabled)

   Active: active (running) since Mon 2019-10-28 10:05:20 +04; 2 days ago

     Docs: man:sshd(8)

           man:sshd_config(5)

  Process: 5246 ExecReload=/bin/kill -HUP $MAINPID (code=exited, status=0/SUCCESS)

 Main PID: 5257 (sshd)

   CGroup: /system.slice/sshd.service

           └─5257 /usr/sbin/sshd -D

Oct 29 13:54:10 Replica_node1 sshd[13487]: Accepted password for root from 56.100.13.111 port 60930 ssh2

Oct 29 14:26:02 Replica_node1 sshd[13666]: Accepted keyboard-interactive/pam for root from 56.100.13.111 port 52172 ssh2

Oct 29 14:26:03 Replica_node1 sshd[13672]: Accepted password for root from 56.100.13.111 port 52187 ssh2

Oct 29 14:30:22 Replica_node1 sshd[13811]: Accepted publickey for postgres from 10.0.0.202 port 36776 ssh2: RSA SHA256:N+PR+aXCIRlNl0j7HWcnuwmER+ksiIK3F4kWYNDScHs

Oct 29 15:53:30 Replica_node1 sshd[14373]: Accepted keyboard-interactive/pam for root from 56.100.13.111 port 61092 ssh2

Oct 29 15:53:30 Replica_node1 sshd[14379]: Accepted password for root from 56.100.13.111 port 61095 ssh2

Oct 30 09:34:59 Replica_node1 sshd[17700]: Accepted keyboard-interactive/pam for root from 56.100.13.111 port 55217 ssh2

Oct 30 09:34:59 Replica_node1 sshd[17705]: Accepted password for root from 56.100.13.111 port 55224 ssh2

Oct 30 10:01:36 Replica_node1 sshd[17901]: Accepted keyboard-interactive/pam for root from 56.100.13.111 port 58057 ssh2

Oct 30 10:01:36 Replica_node1 sshd[17906]: Accepted password for root from 56.100.13.111 port 58066 ssh2

[root@Replica_node1 ~]#

SSH-KEY setup for postgres user #

-bash-4.2$ hostname -i -a

repnode1

-bash-4.2$ pwd

/var/lib/pgsql

-bash-4.2$

su - postgres

mkdir ~/.ssh

chmod 700 ~/.ssh

/usr/bin/ssh-keygen -t rsa # Accept the default settings.

-bash-4.2$ hostname -i -a

repnode2

su - postgres

mkdir ~/.ssh

chmod 700 ~/.ssh

/usr/bin/ssh-keygen -t rsa # Accept the default settings.

Repnode1

cat id_rsa.pub >> authorized_keys

scp authorized_keys  postgres@10.0.0.202:/var/lib/pgsql/.ssh

Repnode2

Now login to node2 (10.0.0.202) and do the below

Su – postgres

Cat id_rsa.pub >> authorized_keys

Scp authorized_keys postgres@10.0.0.201:/var/lib/pgsql/.ssh

The "authorized_keys" file on both servers now contains the public keys generated on all nodes.

Now let us check the postgres from node 1 to node 2 without password connection#

-bash-4.2$ hostname

Replica_node1

-bash-4.2$ ssh postgres@10.0.0.202

Last login: Wed Oct 30 10:49:15 2019 from 10.0.0.201

-bash-4.2$ hostname

Replica_node2

Now let us check the postgres from node 2 to node 1 without password connection#

-bash-4.2$ hostname -a

repnode2

-bash-4.2$ ssh postgres@10.0.0.201

Last login: Wed Oct 30 10:56:06 2019 from 10.0.0.202

-bash-4.2$ hostname -a

repnode1

-bash-4.2$

 

BACKUP FROM REPLICA_NODE1(PRIMARY SIDE) for replication #

Now let us take base backup of sharjah_primdb database base backup:

-bash-4.2$ pg_basebackup -p 6001 --format=t -D standby/

-bash-4.2$

Verify the backup:

Transfer backup from Primary to Secondary

scp base.tar postgres@10.0.0.202:/var/lib/pgsql/11/sharjah_standby

Standby side#

-- untar the base backup which is copied from primary in standby side.

tar -xvf base.tar

var/lib/pgsql/11/sharjah_standby

-bash-4.2$ ls -l

total 409832

-rw-------. 1 postgres postgres       226 Oct 29 14:45 backup_label

drwx------. 6 postgres postgres      4096 Oct 28 15:07 base

-rw-------. 1 postgres postgres 419534336 Oct 29 14:52 base.tar

-rw-------. 1 postgres postgres        47 Oct 29 00:00 current_logfiles

drwx------. 2 postgres postgres      4096 Oct 29 14:58 global

drwx------. 2 postgres postgres      4096 Oct 27 11:28 log

drwx------. 2 postgres postgres      4096 Oct 27 11:18 pg_commit_ts

drwx------. 2 postgres postgres      4096 Oct 27 11:18 pg_dynshmem

-rw-------. 1 postgres postgres      4513 Oct 27 11:18 pg_hba.conf

-rw-------. 1 postgres postgres      1636 Oct 27 11:18 pg_ident.conf

drwx------. 2 postgres postgres      4096 Oct 29 00:00 pg_log

drwx------. 4 postgres postgres      4096 Oct 29 14:45 pg_logical

drwx------. 4 postgres postgres      4096 Oct 27 11:18 pg_multixact

drwx------. 2 postgres postgres      4096 Oct 28 14:59 pg_notify

drwx------. 2 postgres postgres      4096 Oct 29 14:45 pg_replslot

drwx------. 2 postgres postgres      4096 Oct 27 11:18 pg_serial

drwx------. 2 postgres postgres      4096 Oct 27 11:18 pg_snapshots

drwx------. 2 postgres postgres      4096 Oct 28 14:59 pg_stat

drwx------. 2 postgres postgres      4096 Oct 29 14:44 pg_stat_tmp

drwx------. 2 postgres postgres      4096 Oct 27 11:18 pg_subtrans

drwx------. 2 postgres postgres      4096 Oct 27 11:18 pg_tblspc

drwx------. 2 postgres postgres      4096 Oct 27 11:18 pg_twophase

-rw-------. 1 postgres postgres         3 Oct 27 11:18 PG_VERSION

drwx------. 3 postgres postgres      4096 Oct 29 14:58 pg_wal

drwx------. 2 postgres postgres      4096 Oct 27 11:18 pg_xact

-rw-------. 1 postgres postgres        88 Oct 27 11:18 postgresql.auto.conf

-rw-------. 1 postgres postgres     23922 Oct 27 14:09 postgresql.conf

-rw-------. 1 postgres postgres         0 Oct 29 14:45 tablespace_map

-bash-4.2$

hb_pga.conf file configuration :-

/var/lib/pgsql/11/sharjah_primdb

-bash-4.2$ ls -l pg_hba.conf

-rw-------. 1 postgres postgres 4529 Oct 29 16:55 pg_hba.conf

-bash-4.2$

Now update node 2 ips in pg_hba.conf file

host    replication     postgres            10.0.0.202/32         trust

#host    replication     postgres             ::1/128                 trust

 

STANDBY SIDE #

-bash-4.2$ whoami

postgres

-bash-4.2$

Open the postgresql.conf file and update the paremeter:

File location: /var/lib/pgsql/11/sharjah_standby

File name: postgresql.conf

hot_standby = on

Create standby database directory structure:-

bash-4.2$ pwd

/var/lib/pgsql/11

-bash-4.2$

mkdir –p Sharjah_standby

chown –R postgres:postgres Sharjah_standby

 

Mkdir –p str_archive

Chown –R postgres:postgres str_archive

 

 

Create recovery configuration file#

/var/lib/pgsql/11/sharjah_standby

-base-4.2$ touch recovery.conf

-bash-4.2$ cat recovery.conf

standby_mode=on

primary_conninfo='host=10.0.0.201 port=6001'

restore_command='cp /var/lib/pgsql/11/str_archive/%f %p'

trigger_file='/tmp/failover_6001'

-bash-4.2$

-bash-4.2$ chmod -R 700 sharjah_standby/

drwx------. 21 postgres postgres 4096 Oct 29 15:38 sharjah_standby

-bash-4.2$

Start the Sharjah_standby database in 10.0.0.202 host

bash-4.2$ echo $PGPATH

/usr/pgsql-11/bin

-bash-4.2$ pwd

/var/lib/pgsql/11

-bash-4.2$ /usr/pgsql-11/bin/pg_ctl -D sharjah_standby/ start

waiting for server to start....2019-10-29 15:57:00.019 +04 [12651] LOG:  listening on IPv4 address "0.0.0.0", port 6001

2019-10-29 15:57:00.020 +04 [12651] LOG:  listening on IPv6 address "::", port 6001

2019-10-29 15:57:00.021 +04 [12651] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.6001"

2019-10-29 15:57:00.023 +04 [12651] LOG:  listening on Unix socket "/tmp/.s.PGSQL.6001"

2019-10-29 15:57:00.326 +04 [12651] LOG:  redirecting log output to logging collector process

2019-10-29 15:57:00.326 +04 [12651] HINT:  Future log output will appear in directory "pg_log".

... done

server started

-bash-4.2$

--check the standby server postgres process

postgres 11736 11735  0 14:27 pts/0    00:00:01 -bash

postgres 12651     1  0 15:56 pts/0    00:00:00 /usr/pgsql-11/bin/postgres -D sharjah_standby

postgres 12652 12651  0 15:56 ?        00:00:00 postgres: logger

postgres 12653 12651  0 15:56 ?        00:00:00 postgres: startup   waiting for 00000001000000000000001E

postgres 12655 12651  0 15:57 ?        00:00:00 postgres: checkpointer

postgres 12656 12651  0 15:57 ?        00:00:00 postgres: background writer

postgres 12658 12651  0 15:57 ?        00:00:00 postgres: stats collector

postgres 12688 11736  0 15:58 pts/0    00:00:00 ps -ef

postgres 12689 11736  0 15:58 pts/0    00:00:00 grep --color=auto postgres

-bash-4.2$

-bash-4.2$ netstat -nultlp  | grep 6001

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 0.0.0.0:6001            0.0.0.0:*               LISTEN      12651/postgres

tcp6       0      0 :::6001                 :::*                    LISTEN      12651/postgres

-bash-4.2$

sharjah_standby database is started successfully .