How To Install PostgreSQL 12 Streaming Replication Master/Slave(Synchronous / Asynchronous) on the CentOS 7

PostgreSQL Streaming Replicatiıon master slave Installation big dba

2 February 2021 0 Comment

Using High Availability is a requirement for companies all over the world. Stream Replication as the replication method, PostgreSQL Stream Replication is asynchronous by default, so it is possible to do some operations on the primary node that have not yet been copied to the standby server. This means there is some potential possibility of data loss. If the standby server is strong enough to keep up with the load, this delay in the commit process should be very small. If this small risk of data loss is not acceptable at the company, you can also use simultaneous replication instead of the default.

In synchronous replication, each record of a write operation awaits confirmation that the recording was written to both the primary and standby server’s write-from-log disk. This method minimizes the possibility of data loss. For data loss to occur, both primary and standby must fail at the same time.

The disadvantage of this method is the same for all synchronous methods because the response time for each write operation increases with this method. This is because you wait for all confirmations that the transaction is complete. Fortunately, read-only transactions will not be affected..

Master/Primary: 192.168.1.26

Slave/Standby: 192.168.1.27

sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

postgres1$ rpm -qi pgdg-redhat-repo

Name : pgdg-redhat-repo
Version : 42.0
Release : 14
Architecture: noarch
Install Date: Tue 02 Feb 2021 04:43:24 PM +03
Group : Unspecified
Size : 11464
License : PostgreSQL
Signature : DSA/SHA1, Thu 24 Sep 2020 06:41:43 PM +03, Key ID 1f16d2e1442df0f8
Source RPM : pgdg-redhat-repo-42.0-14.src.rpm
Build Date : Thu 24 Sep 2020 06:41:37 PM +03
Build Host : koji-centos7-x86-64-pgbuild
Relocations : (not relocatable)
Vendor : PostgreSQL Global Development Group
URL : https://yum.postgresql.org
Summary : PostgreSQL PGDG RPMs- Yum Repository Configuration for Red Hat / CentOS
Description :
This package contains yum configuration for Red Hat Enterprise Linux, CentOS,
and also the GPG key for PGDG RPMs.

[root@postgre2 ~]# rpm -qi pgdg-redhat-repo

Name : pgdg-redhat-repo
Version : 42.0
Release : 14
Architecture: noarch
Install Date: Tue 02 Feb 2021 08:43:41 AM EST
Group : Unspecified
Size : 11464
License : PostgreSQL
Signature : DSA/SHA1, Thu 24 Sep 2020 11:41:43 AM EDT, Key ID 1f16d2e1442df0f8
Source RPM : pgdg-redhat-repo-42.0-14.src.rpm
Build Date : Thu 24 Sep 2020 11:41:37 AM EDT
Build Host : koji-centos7-x86-64-pgbuild
Relocations : (not relocatable)
Vendor : PostgreSQL Global Development Group
URL : https://yum.postgresql.org
Summary : PostgreSQL PGDG RPMs- Yum Repository Configuration for Red Hat / CentOS
Description :
This package contains yum configuration for Red Hat Enterprise Linux, CentOS,
and also the GPG key for PGDG RPMs.

sudo yum -y install epel-release yum-utils
sudo yum-config-manager –enable pgdg12
sudo yum install postgresql12-server postgresql12

İnitialize and start Database Service

sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

The database main configuration ifile is written to: /var/lib/pgsql/12/data/postgresql.conf

Start Dabatase Service

sudo systemctl enable –now postgresql-12

systemctl status postgresql-12

[root@postgre1 ~]# passwd postgres

[root@postgre2 ~]# passwd postgres

sudo firewall-cmd –add-service=postgresql –permanent
sudo firewall-cmd –reload

psql -c “alter user postgres with password ‘12345’”

vi /etc/selinux/config -> Disabled

vi /var/lib/pgsql/12/data/postgresql.conf
listen_addresses = ‘*’ # what IP address(es) to listen on;

vi /var/lib/pgsql/12/data/pg_hba.conf

# Accept from anywhere
host all all 0.0.0.0/0 md5

# Accept from trusted subnet
host all all 192.168.1.1/24 md5

SELECT pg_reload_conf(); -> Change now

systemctl restart postgresql-12.service

[root@postgre1 data]# systemctl status postgresql-12
● postgresql-12.service – PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2021-02-02 18:06:24 +03; 15s ago
Docs: https://www.postgresql.org/docs/12/static/
Process: 9609 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 9615 (postmaster)
CGroup: /system.slice/postgresql-12.service
├─9615 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
├─9617 postgres: logger
├─9619 postgres: checkpointer
├─9620 postgres: background writer
├─9621 postgres: walwriter
├─9622 postgres: autovacuum launcher
├─9623 postgres: stats collector
└─9624 postgres: logical replication launcher

Feb 02 18:06:24 postgre1.localdomain systemd[1]: Stopped PostgreSQL 12 database server.
Feb 02 18:06:24 postgre1.localdomain systemd[1]: Starting PostgreSQL 12 database server…
Feb 02 18:06:24 postgre1.localdomain postmaster[9615]: 2021-02-02 18:06:24.339 +03 [9615] LOG: starting PostgreSQL 12.5 on x86_64-p…64-bit
Feb 02 18:06:24 postgre1.localdomain postmaster[9615]: 2021-02-02 18:06:24.339 +03 [9615] LOG: listening on IPv4 address “0.0.0.0”,…t 5432
Feb 02 18:06:24 postgre1.localdomain postmaster[9615]: 2021-02-02 18:06:24.339 +03 [9615] LOG: listening on IPv6 address “::”, port 5432
Feb 02 18:06:24 postgre1.localdomain postmaster[9615]: 2021-02-02 18:06:24.340 +03 [9615] LOG: listening on Unix socket “/var/run/p….5432”
Feb 02 18:06:24 postgre1.localdomain postmaster[9615]: 2021-02-02 18:06:24.342 +03 [9615] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
Feb 02 18:06:24 postgre1.localdomain postmaster[9615]: 2021-02-02 18:06:24.347 +03 [9615] LOG: redirecting log output to logging co…rocess
Feb 02 18:06:24 postgre1.localdomain postmaster[9615]: 2021-02-02 18:06:24.347 +03 [9615] HINT: Future log output will appear in di…”log”.
Feb 02 18:06:24 postgre1.localdomain systemd[1]: Started PostgreSQL 12 database server.
Hint: Some lines were ellipsized, use -l to show in full.

Pgadmin Control

su – postgres

postgres1$ createuser –replication -P -e replicator

Enter password for new role:
Enter it again:
SELECT pg_catalog.set_config(‘search_path’, ”, false);
CREATE ROLE replicator PASSWORD ‘md52497fb6e40899329c3cd1145f384aa82’ NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN REPLICATION;

postgres2$ createuser –replication -P -e replicator

vi /var/lib/pgsql/12/data/pg_hba.conf

host replication replicator 192.168.1.27/24 md5

systemctl restart postgresql-12.service

[root@postgre1 data]# firewall-cmd –add-service=postgresql –permanent

Warning: ALREADY_ENABLED: postgresql
success

[root@postgre1 data]# firewall-cmd –reload
success
[root@postgre1 data]#

Standby

systemctl stop postgresql-12.service

cp -R /var/lib/pgsql/12/data /var/lib/pgsql/12/data_orjinal

rm -rf /var/lib/pgsql/12/data/*

postgres2$ pwd
/var/lib/pgsql/12

postgres2$ ll
total 8

postgres2$ pg_basebackup -h 192.168.1.26 -D /var/lib/pgsql/12/data -U replicator -P -v -R -X stream -C -S pgstandby
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot “pgstandby”
25314/25314 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000100
pg_basebackup: waiting for background process to finish streaming …
pg_basebackup: syncing data to disk …
pg_basebackup: base backup completed

postgres2$ pwd
/var/lib/pgsql/12/data
postgres2$ ll
total 60
-rw——-. 1 postgres postgres 224 Feb 2 10:56 backup_label
drwx——. 5 postgres postgres 41 Feb 2 10:56 base
-rw——-. 1 postgres postgres 30 Feb 2 10:56 current_logfiles
drwx——. 2 postgres postgres 4096 Feb 2 10:56 global
drwx——. 2 postgres postgres 32 Feb 2 10:56 log
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_commit_ts
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_dynshmem
-rw——-. 1 postgres postgres 4690 Feb 2 10:56 pg_hba.conf
-rw——-. 1 postgres postgres 1636 Feb 2 10:56 pg_ident.conf
drwx——. 4 postgres postgres 68 Feb 2 10:56 pg_logical
drwx——. 4 postgres postgres 36 Feb 2 10:56 pg_multixact
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_notify
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_replslot
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_serial
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_snapshots
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_stat
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_stat_tmp
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_subtrans
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_tblspc
drwx——. 2 postgres postgres 6 Feb 2 10:56 pg_twophase
-rw——-. 1 postgres postgres 3 Feb 2 10:56 PG_VERSION
drwx——. 3 postgres postgres 60 Feb 2 10:56 pg_wal
drwx——. 2 postgres postgres 18 Feb 2 10:56 pg_xact
-rw——-. 1 postgres postgres 295 Feb 2 10:56 postgresql.auto.conf
-rw——-. 1 postgres postgres 26627 Feb 2 10:56 postgresql.conf
-rw——-. 1 postgres postgres 0 Feb 2 10:56 standby.signal

[root@postgre1 data]# su – postgres
Last login: Tue Feb 2 18:27:22 +03 2021 on pts/0

postgres1$ psql -c “SELECT * FROM pg_replication_slots;”
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
———–+——–+———–+——–+———-+———–+——–+————+——+————–+————-+———————
pgstandby | | physical | | | f | f | | | | 0/2000000 |
(1 row)

[root@postgre2 data]# cat /var/lib/pgsql/12/data/postgresql.auto.conf

Do not edit this file manually! It will be overwritten by the ALTER SYSTEM command. primary_conninfo = ‘user=replicator password=12345 host=192.168.1.26 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any’ primary_slot_name = ‘pgstandby’

[root@postgre2 data]# systemctl start postgresql-12

[root@postgre2 data]# systemctl status postgresql-12

● postgresql-12.service – PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2021-02-02 11:07:28 EST; 3s ago
Docs: https://www.postgresql.org/docs/12/static/
Process: 9893 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 9899 (postmaster)
CGroup: /system.slice/postgresql-12.service
├─9899 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
├─9901 postgres: logger
├─9902 postgres: startup recovering 000000010000000000000003
├─9903 postgres: checkpointer
├─9904 postgres: background writer
├─9905 postgres: stats collector
├─9906 postgres: walreceiver streaming 0/3000148
└─9907 postgres: postgres postgres 192.168.1.21(58361) idle

Feb 02 11:07:27 postgre2.localdomain systemd[1]: Starting PostgreSQL 12 database server…
Feb 02 11:07:28 postgre2.localdomain postmaster[9899]: 2021-02-02 19:07:28.004 +03 [9899] LOG: starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
Feb 02 11:07:28 postgre2.localdomain postmaster[9899]: 2021-02-02 19:07:28.005 +03 [9899] LOG: listening on IPv4 address “0.0.0.0”, port 5432
Feb 02 11:07:28 postgre2.localdomain postmaster[9899]: 2021-02-02 19:07:28.005 +03 [9899] LOG: listening on IPv6 address “::”, port 5432
Feb 02 11:07:28 postgre2.localdomain postmaster[9899]: 2021-02-02 19:07:28.006 +03 [9899] LOG: listening on Unix socket “/var/run/postgresql/.s.PGSQL.5432”
Feb 02 11:07:28 postgre2.localdomain postmaster[9899]: 2021-02-02 19:07:28.008 +03 [9899] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
Feb 02 11:07:28 postgre2.localdomain postmaster[9899]: 2021-02-02 19:07:28.013 +03 [9899] LOG: redirecting log output to logging collector process
Feb 02 11:07:28 postgre2.localdomain postmaster[9899]: 2021-02-02 19:07:28.013 +03 [9899] HINT: Future log output will appear in directory “log”.
Feb 02 11:07:28 postgre2.localdomain systemd[1]: Started PostgreSQL 12 database server.

Standby streaming control

postgres2$ psql -c “\x” -c “SELECT * FROM pg_stat_wal_receiver;”

Expanded display is on.
-[ RECORD 1 ]———+———————————————————————————————————————————————————————————————————————-
pid | 9906
status | streaming
receive_start_lsn | 0/3000000
receive_start_tli | 1
received_lsn | 0/3000148
received_tli | 1
last_msg_send_time | 2021-02-02 19:08:55.439372+03
last_msg_receipt_time | 2021-02-02 19:08:58.342552+03
latest_end_lsn | 0/3000148
latest_end_time | 2021-02-02 19:07:25.261201+03
slot_name | pgstandby
sender_host | 192.168.1.26
sender_port | 5432
conninfo | user=replicator password= dbname=replication host=192.168.1.26 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any