Friday, November 01, 2019

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
#------------------------------------------------------------------------------
# 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


Repnode2
Now login to node2 (10.0.0.202) and do the below
Su – postgres
Cat id_rsa.pub >> authorized_keys

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 .


 













Step by Step YugabyteDB 2.11 (Open Source) Distributed DB - Multi-node Cluster Setup on RHEL

Scope - ·        Infrastructure planning and requirement for installation of the multi-node cluster database ·        Prerequisites Software...