PostgreSQL High Availability cluster installation

I. Hosts and topology structure of PG cluster

1.host infos

CLUSTER01_NODE01 192.168.0.108
CLUSTER01_NODE02 192.168.0.109
CLUSTER02_NODE03 192.168.0.110

2.topology structure

                    sync                              async

Primary (CLS01_NODE01)-------"Standby01 (CLS01_NODE02)-------" standby02 (CLS01_NODE03)

Second, installation configuration

1. Install and initialize PG (CLS01_NODE01,CLS01_NODE02, CLS01_NODE03)
1). Install and Init

---install PG packages
Yum Install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
Yum Install Postgresql10-server
Yum Install-y Postgresql10-contrib

---init and auto boot
/usr/pgsql-10/bin/postgresql-10-setup Initdb
Systemctl Enable postgresql-10
Systemctl Start postgresql-10

PGDATA:
/var/lib/pgsql/10/data/

2). mkdir PG Archives

Mkdir/pg_archive
Chown postgres.postgres/pg_archive/
chmod 700/pg_archive/

2.primary Create replication user and database configuration (CLS01_NODE01)

1). Create Replication User

User/password:repuser/repuser

[Email protected]_master ~]# su-postgres
Last Login:sun April 17:25:06 CST 2018 on pts/0
-bash-4.2$ createuser-u postgres repuser-p-C 5--replication
Enter Password for new role:
Enter It again:
-bash-4.2$

To set the root user password
-bash-4.2$ psql-h 127.0.0.1
Psql (10.3)
Type ' help ' for help.
postgres=#
postgres=# alter user postgres with password ' 123456 ';

2). configuration file

A. postgresql.conf

#------------------------------------------------------------------------------
--# CONNECTIONS and authentication
#------------------------------------------------------------------------------
--#-Connection Settings-
listen_addresses = ' * '
max_connections = 2000

--#-TCP keepalives-
Tcp_keepalives_idle = 60
Tcp_keepalives_interval = 10
Tcp_keepalives_count = 6

#------------------------------------------------------------------------------
--# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
--#-Memory-
Shared_buffers = 256MB
Maintenance_work_mem = 64MB

--#-Kernel Resource Usage-
shared_preload_libraries = ' pg_stat_statements '

#------------------------------------------------------------------------------
--# WRITE AHEAD LOG
#------------------------------------------------------------------------------
--#-Settings-
Wal_level = Logical
Wal_log_hints = On

--#-Checkpoints-
Max_wal_size = 10GB
Checkpoint_completion_target = 0.9

--#-Archiving-
Archive_mode = On
Archive_command = ' Test! -F/PG_ARCHIVE/%F && CP%p/pg_archive/%f '

#------------------------------------------------------------------------------
--# REPLICATION
#------------------------------------------------------------------------------
--#-Sending Server (s)-
Wal_keep_segments = 5000

--#-Master Server-
Synchronous_standby_names = ' * '

--#-Standby Servers-
Hot_standby_feedback = On

#------------------------------------------------------------------------------
--# ERROR REPORTING and LOGGING
#------------------------------------------------------------------------------
--#-When to Log-
log_min_duration_statement = 1000

--#-What to Log-
Log_checkpoints = On
Log_connections = On
Log_disconnections =
Log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
Log_lock_waits = On
Log_temp_files = 0

#------------------------------------------------------------------------------
Autovacuum PARAMETERS
#------------------------------------------------------------------------------
log_autovacuum_min_duration = 0

B. pg_hba.conf

--# TYPE DATABASE USER ADDRESS METHOD
--# IPV4 Local connections:
Host All 127.0.0.1/32 MD5
Host All 0.0.0.0/0 MD5
--# IPV6 Local connections:
Host all:: 1/128 MD5
--# Allow replication connections
Host replication Repuser 192.168.0.108/32 MD5
Host replication Repuser 192.168.0.109/32 MD5
Host replication Repuser 192.168.0.110/32 MD5

3). Restart Database

--# systemctl Restart Postgresql-10

3.sync slave configuration
1). Stop the Database
[[Email protected]_node02 ~]# systemctl Stop postgresql-10

2). Clear Old Pgdata dir
[Email protected]_node02 ~]# su-postgres
Last Login:sun April 18:39:24 CST 2018 on pts/0
-bash-4.2$ CD 10/data/
-bash-4.2$ RM-RF *
-bash-4.2$ LL
Total 0
-bash-4.2$
3). Make Sync Standby

Note: Get database data from primary

-bash-4.2$ CD
-bash-4.2$ pg_basebackup-h 192.168.0.108-u repuser-p 5432-d/var/lib/pgsql/10/data--wal-method=s Tream--checkpoint=fast--progress--verbose--write-recovery-conf > Make slave $ (date +%y %m %d). Log 2>&1
Password:
-bash-4.2$ more Make_slave_2018_04_22.log
pg_basebackup:initiating base Backup, Waiting for checkpoint to complete
Pg_basebackup:checkpoint completed
Pg_basebackup:write-ahead log start point: 0/4000060 on timeline 1
pg_basebackup:starting background WAL receiver
24448/24448 KB (100%), 1/1 tablespace
PG _basebackup:write-ahead Log End point:0/4000130
pg_basebackup:waiting for background process to finish streaming: .
Pg_basebackup:base Backup completed
-bash-4.2$

4). Add Sync Flag (application_name) and trigger file (Trigger_file)
-bash-4.2$ cd/var/lib/pgsql/10/data/
-bash-4.2$ VI recovery.conf
Standby_mode = ' on '
Primary_conninfo = ' Application_name=sync_slave user=repuser password=repuser host=192.168.0.108 port=5432 sslmode= Prefer sslcompression=1 krbsrvname=postgres Target_session_attrs=any '
Trigger_file = '/tmp/trigger_failover '
-bash-4.2$

5). Start Sync Slave
-bash-4.2$ exit
Logout
[Email protected]_node02 ~]# systemctl start postgresql-10
[[Email Protected]_node02 ~]#

4.async slave configuration
1). Stop the Database
[[Email protected]_node03 ~]# systemctl Stop postgresql-10

2). Empty the Pgdata directory
[Email protected]_node03 ~]# su-postgres
-bash-4.2$ cd/var/lib/pgsql/10/data/
-bash-4.2$ RM-RF *
-bash-4.2$ LL
Total 0
-bash-4.2$

3). Make slave

Note: Get database data from sync standby

-bash-4.2$ CD
-bash-4.2$ pg_basebackup-h 192.168.0.109-u repuser-p 5432-d/var/lib/pgsql/10/data--wal-method=s Tream--checkpoint=fast--progress--verbose--write-recovery-conf > Make slave $ (date +%y %m %d). Log 2>&1
Password:
-bash-4.2$ ll
Total 4
drwx------4 postgres postgres April 17:17
-rw-r--r --1 Postgres postgres 690 Apr 19:23 make_slave_2018_04_22.log
-bash-4.2$ more Make_slave_2018_04_22.log
Pg_bas Ebackup:initiating base backup, waiting for checkpoint to complete
Pg_basebackup:checkpoint completed
Pg_ Basebackup:write-ahead log start point:0/6000028 on timeline 1
pg_basebackup:starting background WAL receiver
321 73/32173 KB (100%), 1/1 tablespace
pg_basebackup:write-ahead log End point:0/7000060
Pg_basebackup:waiting for B Ackground process to finish streaming ...
Pg_basebackup:base Backup completed
-bash-4.2$

4). Add Wal switch flag (recovery_target_timeline= ' latest ')

-bash-4.2$ cd/var/lib/pgsql/10/data/
-bash-4.2$ VI recovery.conf
Standby_mode = ' on '
Primary_conninfo = ' User=repuser password=repuser host=192.168.0.109 port=5432 sslmode=prefer sslcompression=1 Krbsrvname=postgres Target_session_attrs=any '
Recovery_target_timeline= ' latest '
-bash-4.2$

5). Start Async Standby

-bash-4.2$ exit
Logout
[[Email Protected]_node03 ~]#
[Email protected]_node03 ~]# systemctl start postgresql-10
[[Email Protected]_node03 ~]#

5. Check the replication status

1). Primary

[Email protected]_node01 ~]# su-postgres
Last Login:sun April 19:16:19 CST 2018 on pts/0

-bash-4.2$
-bash-4.2$ psql-h 127.0.0.1
Password:
Psql (10.3)
Type ' help ' for help.

postgres=# \x
Expanded display is on.
postgres=# select * from Pg_stat_replication;
-[RECORD 1]----+------------------------------
PID | 9341
Usesysid | 16384
Usename | repuser
Application_name | Sync_slave
Client_addr | 192.168.0.109
Client_hostname |
Client_port | 34152
Backend_start | 2018-04-22 19:15:51.242261+08
Backend_xmin | 558
State | streaming
SENT_LSN | 0/7000140
Write_lsn | 0/7000140
Flush_lsn | 0/7000140
Replay_lsn | 0/7000140
Write_lag |
Flush_lag |
Replay_lag |
Sync_priority | 1
sync_state | sync

postgres=# CREATE DATABASE tdb01;
CREATE DATABASE
postgres=# \c TDB01
Connected to Database "TDB01" as User "Postgres".
tdb01=# CREATE TABLE t1 (ID serial,user_name varchar (20));
CREATE TABLE
tdb01=# INSERT INTO T1 (user_name) VALUES (' Mia ');
INSERT 0 1
tdb01=#
tdb01=#
tdb01=# select * from T1;
ID | User_name
----+-----------
1 | Mia
(1 row)

tdb01=# \q
-bash-4.2$

2). Sync Standby

[Email protected]_node02 ~]# su-postgres
Last Login:sun April 18:41:55 CST 2018 on pts/0
-bash-4.2$
-bash-4.2$ psql-h 127.0.0.1
Password:
Psql (10.3)
Type ' help ' for help.

postgres=# \x
Expanded display is on.
postgres=# select * from Pg_stat_replication;
-[RECORD 1]----+------------------------------
PID | 9086
Usesysid | 16384
Usename | repuser
Application_name | Walreceiver
Client_addr | 192.168.0.110
Client_hostname |
Client_port | 51408
Backend_start | 2018-04-22 19:29:17.659393+08
Backend_xmin | 563
State | streaming
SENT_LSN | 0/7039290
Write_lsn | 0/7039290
Flush_lsn | 0/7039290
Replay_lsn | 0/7039290
Write_lag |
Flush_lag |
Replay_lag |
Sync_priority | 0
Sync_state | async

tdb01=# \x
Expanded display is off.
tdb01=#
tdb01=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access Privileges
-----------+----------+----------+-------------+-------------+-----------------------
Postgres | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |
TDB01 | 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)

tdb01=# select * from T1;
ID | User_name
----+-----------
1 | Mia
(1 row)

tdb01=#

3). Async Standby

[Email protected]_node03 ~]# su-postgres
Last Login:sun April 19:18:55 CST 2018 on pts/0
-bash-4.2$
-bash-4.2$ psql-h 127.0.0.1
Password:
Psql (10.3)
Type ' help ' for help.

postgres=# \x
Expanded display is on.
postgres=# select * from Pg_stat_replication;
(0 rows)

postgres=# \x
Expanded display is off.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access Privileges
-----------+----------+----------+-------------+-------------+-----------------------
Postgres | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |
TDB01 | 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)

postgres=# \c TDB01
Connected to Database "TDB01" as User "Postgres".
tdb01=# select * from T1;
ID | User_name
----+-----------
1 | Mia
(1 row)

tdb01=# \q
-bash-4.2$

PostgreSQL High Availability cluster installation