postgresql高可用集群的安裝步驟

這篇文章主要講解了“postgresql高可用集群的安裝步驟”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“postgresql高可用集群的安裝步驟”吧！

一、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)

二、安裝配置

1.安裝、初始化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創(chuàng)建復制用戶與數(shù)據(jù)庫配置(cls01_node01)

1).create replication user

user/password: repuser/repuser

[root@pg_master ~]# su - postgres
Last login: Sun Apr 22 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$

設置超級用戶密碼
-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             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
--# IPv6 local connections:
host    all             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配置
1).停止數(shù)據(jù)庫
[root@cls01_node02 ~]# systemctl stop postgresql-10

2).clear old pgdata dir
[root@cls01_node02 ~]# su - postgres
Last login: Sun Apr 22 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

注意：從primary獲取數(shù)據(jù)庫數(shù)據(jù)

-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=stream --checkpoint=fast --progress --verbose --write-recovery-conf > makeslave$(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
[root@cls01_node02 ~]# systemctl start postgresql-10
[root@cls01_node02 ~]#

4.async slave 配置
1).停止數(shù)據(jù)庫
[root@cls01_node03 ~]# systemctl stop postgresql-10

2).清空pgdata目錄
[root@cls01_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

注意：從sync standby獲取數(shù)據(jù)庫數(shù)據(jù)

-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=stream --checkpoint=fast --progress --verbose --write-recovery-conf > makeslave$(date +%Y%m%d).log 2>&1
Password:
-bash-4.2$ ll
total 4
drwx------ 4 postgres postgres  51 Apr 22 17:17 10
-rw-r--r-- 1 postgres postgres 690 Apr 22 19:23 make_slave_2018_04_22.log
-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/6000028 on timeline 1
pg_basebackup: starting background WAL receiver
32173/32173 kB (100%), 1/1 tablespace                                        
pg_basebackup: write-ahead log end point: 0/7000060
pg_basebackup: waiting for background 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
[root@cls01_node03 ~]#
[root@cls01_node03 ~]# systemctl start postgresql-10
[root@cls01_node03 ~]#

5.檢查replication狀態(tài)

1).primary

[root@cls01_node01 ~]# su - postgres
Last login: Sun Apr 22 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
You are now 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

[root@cls01_node02 ~]# su - postgres
Last login: Sun Apr 22 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

[root@cls01_node03 ~]# su - postgres
Last login: Sun Apr 22 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
You are now connected to database "tdb01" as user "postgres".
tdb01=# select * from t1;
id | user_name
----+-----------
1 | mia
(1 row)

tdb01=# \q
-bash-4.2$

感謝各位的閱讀，以上就是“postgresql高可用集群的安裝步驟”的內容了，經過本文的學習后，相信大家對postgresql高可用集群的安裝步驟這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！