postgresql高可用集群的安装步骤

32次阅读

共计 9797 个字符，预计需要花费 25 分钟才能阅读完成。

这篇文章主要讲解了“postgresql 高可用集群的安装步骤”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“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 创建复制用户与数据库配置 (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). 停止数据库
[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 获取数据库数据

-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). 停止数据库
[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 获取数据库数据

-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 状态

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

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)