PostgreSQL+Pgpool實現HA讀寫分離

發布時間：2020-08-17 16:15:09 來源：ITPUB博客閱讀：764 作者：ywxj_001 欄目：關系型數據庫

環境說明和主機規劃：

操作系統主機名主機角色端口

CentOS 7.3 master 10.0.40.191 PG-Master 54321

CentOS 7.3 slave 10.0.40.192 PG-Slave 54321

CentOS 7.3 pool 10.0.40.193 pgpool 54321

基礎環境配置（所有主機操作）

配置HOSTS

echo -e "10.0.40.191 master\n10.0.40.192 slave\n10.0.40.193 pool" >> /etc/hosts #

執行一次即可

[root@pgpool-tdb01 ~]# echo -e "10.0.40.191 master\n10.0.40.192

slave\n10.0.40.193 pool" >> /etc/hosts

[root@pgpool-tdb01 ~]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

10.0.40.191 master

10.0.40.192 slave

10.0.40.193 pool

配置統一的時間（若已配置，請忽略）

也可以配置本地自己的ntp服務器。

yum install -y ntpdate && ntpdate ntp1.aliyun.com

echo -e "# sync time from ntp1.aliyun.com\n5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com >

/dev/null 2>&1

" >> /var/spool/cron/root # 寫入定時任務，執行一次即可

[root@pgpool-tdb01 ~]# yum install -y ntpdate && ntpdate ntp1.aliyun.com

Loaded plugins: fastestmirror, langpacks

......

Dependency Updated:

openssl.x86_64 1:1.0.2k-16.el7 openssl-libs.x86_64 1:1.0.2k-

16.el7

Complete!

17 Feb 17:15:16 ntpdate[32460]: step time server 120.25.115.20 offset 1.439184 sec

[root@pgpool-tdb01 ~]# echo -e "# sync time from ntp1.aliyun.com\n5 * * * *

/usr/sbin/ntpdate ntp1.aliyun.com > /dev/null 2>&1" >> /var/spool/cron/root

[root@pgpool-tdb01 ~]# cd /var/spool/cron

[root@pgpool-tdb01 cron]# ll

total 4

-rw-r--r--. 1 root root 94 Feb 17 17:29 root

[root@pgpool-tdb01 cron]# cat root

# sync time from ntp1.aliyun.com

5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com > /dev/null 2>&1

[root@pgpool-tdb01 cron]# crontab -l

# sync time from ntp1.aliyun.com

5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com > /dev/null 2>&1

創建postgres用戶:

useradd postgres && echo "your_password" | passwd --stdin postgres

useradd postgres && echo "XXXX" | passwd --stdin postgres

[root@pgpool-tdb02 ~]# useradd postgres && echo "XXXX" | passwd --stdin

postgres

Changing password for user postgres.

passwd: all authentication tokens updated successfully.

配置免密鑰登陸:

su - postgres

ssh-keygen -t rsa -f /home/postgres/.ssh/id_rsa -P ""

cd ~/.ssh/

ssh-copy-id postgres@master # 三臺主機執行

scp authorized_keys postgres@slave:~/.ssh # 只在master主機執行

scp authorized_keys postgres@pool:~/.ssh # 只在master主機執行

[root@pgpool-tdb01 ~]# su - postgres

[postgres@pgpool-tdb01 ~]$ ssh-keygen -t rsa -f /home/postgres/.ssh/id_rsa -P ""

Generating public/private rsa key pair.

Created directory '/home/postgres/.ssh'.

Your identification has been saved in /home/postgres/.ssh/id_rsa.

Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.

The key fingerprint is:

d7:39:7d:00:55:47:0f:4f:bf:ae:85:31:9c:f6:e8:9f postgres@pgpool-tdb01

The key's randomart image is:

+--[ RSA 2048]----+

| ..oo=|

| . ++|

| . +|

| ..o...|

| S . +*...|

| . ..B. |

| o + |

| . o .|

| o.E |

+-----------------+

[postgres@pgpool-tdb01 ~]$ cd ~/.ssh/

[postgres@pgpool-tdb01 .ssh]$ ssh-copy-id postgres@master

The authenticity of host 'master (10.0.40.191)' can't be established.

ECDSA key fingerprint is 1a:f7:cc:f3:f8:4e:af:40:cf:fe:26:f6:12:7e:5f:46.

Are you sure you want to continue connecting (yes/no)? y

Please type 'yes' or 'no': yes

/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any

that are already installed

/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now

it is to install the new keys

postgres@master's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh 'postgres@master'"

and check to make sure that only the key(s) you wanted were added.

[postgres@pgpool-tdb01 .ssh]$ scp authorized_keys postgres@slave:~/.ssh

The authenticity of host 'slave (10.0.40.192)' can't be established.

ECDSA key fingerprint is 37:ec:17:87:c6:8e:ee:6a:ba:47:a5:22:38:65:6b:21.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'slave,10.0.40.192' (ECDSA) to the list of known hosts.

postgres@slave's password:

authorized_keys 100%

1207 1.2KB/s 00:00

[postgres@pgpool-tdb01 .ssh]$ scp authorized_keys postgres@pool:~/.ssh

The authenticity of host 'pool (10.0.40.193)' can't be established.

ECDSA key fingerprint is 95:2e:fd:0c:2f:4e:54:4d:1f:5e:eb:b3:95:61:aa:5d.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'pool,10.0.40.193' (ECDSA) to the list of known hosts.

postgres@pool's password:

authorized_keys

安裝Postgresql數據庫（PG10.7）

yum安裝：

[root@pgpool-tdb01 ~]# yum install

https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-

x86_64/pgdg-centos10-10-2.noarch.rpm -y

[root@pgpool-tdb01 ~]# yum install postgresql10-contrib postgresql10-server

postgresql10 postgresql10-libs -y

創建統一的目錄結構：

[root@pgpool-tdb01 ~]# mkdir /data1/pg_{data,bin,logs} -p

[root@pgpool-tdb01 ~]# ll /data1/

total 0

drwxr-xr-x. 2 root root 6 Feb 17 19:00 pg_bin

drwxr-xr-x. 2 root root 6 Feb 17 19:00 pg_data

drwxr-xr-x. 2 root root 6 Feb 17 19:00 pg_logs

[root@pgpool-tdb01 ~]# chown -R postgres.postgres /data1/

修改系統變量：

vi /etc/profile #增加以下內容

export PGHOME=/usr/pgsql-10/

export PGDATA=/data1/pg_data

export PGPORT=54321

export PATH=$PATH:$PGHOME/bin

# 生效

source /etc/profile

PostgreSQL流復制結構（master和slave主機操

作）

master主機操作

初始化系統

[root@pgpool-tdb01 ~]# /usr/pgsql-10/bin/postgresql-10-setup initdb

Initializing database ... OK

刪除pg_data后重新初始化：

[postgres@wmsexpress-db01 ~]$ pg_ctl -D /data1/pg_data/ initdb

修改postgresql-10.service

vi /usr/lib/systemd/system/postgresql-10.service

# Include the default config:

#.include /usr/lib/systemd/system/postgresql-10.service

[Service]

Environment=PGDATA=/data1/pg_data

重啟PG服務：

systemctl daemon-reload

su - postgres -c '/usr/pgsql-10/bin/initdb -D /data1/pg_data'

systemctl restart postgresql-10

systemctl enable postgresql-10.service

systemctl status postgresql-10

/usr/pgsql-10/bin/pg_ctl -D /data1/pg_data -l logfile start

每次改postgresql.conf，需要執行下面的stop命令，再重啟pg服務。

/usr/pgsql-10/bin/pg_ctl -D /data1/pg_data -l logfile stop

修改系統配置（以下用postgres用戶操作）：

也可以用root賬號操作，注意目錄權限。

[root@pgpool-tdb01 pg_data]# cp /data1/pg_data/pg_hba.conf{,.bak}

[root@pgpool-tdb01 pg_data]# cat >/data1/pg_data/pg_hba.conf<<EOF

> local all all trust

> host all all 10.0.40.191/32 trust

> host all all 10.0.40.192/32 trust

> host all all 0.0.0.0/0 md5

> host all all ::1/128 trust

> host replication stream_replication 0.0.0.0/0 md5

> EOF

cp /data1/pg_data/postgresql.conf{,.bak}

cat >/data1/pg_data/postgresql.conf<<EOF

listen_addresses = '*'

port = 54321

max_connections = 3000

shared_buffers = 1GB

effective_cache_size = 2GB

work_mem = 64MB

maintenance_work_mem = 128MB

min_wal_size = 128MB

max_wal_size = 256MB

checkpoint_completion_target = 0.9

wal_buffers = 16MB

default_statistics_target = 100

wal_level = hot_standby

wal_log_hints = on

max_wal_senders = 10

hot_standby = on

logging_collector = on

log_directory = 'pg_log'

log_timezone = 'PRC'

timezone = 'PRC'

EOF

#操作完記得重啟 pg_ctl restart

[root@pgpool-tdb01 ~]# cat >/data1/pg_data/postgresql.conf<<EOF

> listen_addresses = '*'

> port = 54321

> max_connections = 200

> shared_buffers = 512MB

> effective_cache_size = 2GB

> work_mem = 64MB

> maintenance_work_mem = 256MB

> min_wal_size = 256MB

> max_wal_size = 512MB

> checkpoint_completion_target = 0.9

> wal_buffers = 16MB

> default_statistics_target = 100

> wal_level = hot_standby

> wal_log_hints = on

> max_wal_senders = 1

> hot_standby = on

> logging_collector = on

> log_directory = 'pg_log'

> EOF

[root@pgpool-tdb01 ~]# su - postgres

Last login: Sun Feb 17 20:02:31 CST 2019 on pts/0

[postgres@pgpool-tdb01 ~]$ pg_ctl restart

waiting for server to shut down.... done

server stopped

waiting for server to start....2019-02-17 12:25:10.486 GMT [3174] LOG: listening on

IPv4 address "0.0.0.0", port 54321

2019-02-17 12:25:10.486 GMT [3174] LOG: listening on IPv6 address "::", port

54321

2019-02-17 12:25:10.489 GMT [3174] LOG: listening on Unix socket

"/var/run/postgresql/.s.PGSQL.54321"

2019-02-17 12:25:10.492 GMT [3174] LOG: listening on Unix socket

"/tmp/.s.PGSQL.54321"

2019-02-17 12:25:10.520 GMT [3174] LOG: redirecting log output to logging

collector process

2019-02-17 12:25:10.520 GMT [3174] HINT: Future log output will appear in

directory "pg_log".

done

server started

在主庫中創建流復制用戶(stream_replication)和

PGPool用戶(srcheck)：

[postgres@pgpool-tdb01 ~]$ psql

psql (10.7)

Type "help" for help.

postgres=# psql

postgres-# \q

[postgres@pgpool-tdb01 ~]$ psql

psql (10.7)

Type "help" for help.

postgres=# CREATE USER stream_replication replication LOGIN CONNECTION

LIMIT 5 ENCRYPTED PASSWORD 'XXXX';

CREATE ROLE

postgres=# CREATE USER srcheck replication LOGIN CONNECTION LIMIT 5

ENCRYPTED PASSWORD 'XXXX';

CREATE ROLE

修改主庫pg_hba.conf文件（已操作見cat

>/data1/pg_data/pg_hba.conf<<EOF）：

host replication stream_replication 0.0.0.0/0 md5

slave主機操作

初始化系統

[root@pgpool-tdb02 ~]# /usr/pgsql-10/bin/postgresql-10-setup initdb

Initializing database ... OK

修改postgresql-10.service

[root@pgpool-tdb02 ~]# vi /usr/lib/systemd/system/postgresql-10.service

內容如下:

# Include the default config:

#.include /usr/lib/systemd/system/postgresql-10.service

[Service]

Environment=PGDATA=/data1/pg_data

重啟PG服務

systemctl daemon-reload

基礎備份復制到備庫服務器

rm -rf /data1/pg_data # 如果沒有重要數據可操作，主要為同步主庫路徑

[root@pgpool-tdb02 ~]# su - postgres -c 'pg_basebackup -D $PGDATA --

format=p -h master -p 54321 -U stream_replication -W'

PostgreSQL+Pgpool實現HA讀寫分離

關閉所有主機防火墻：(也可以在防火墻上設置端口限制)

如果你的系統上沒有安裝,使用命令安裝

安裝firewalld 防火墻yum install firewalld

開啟服務

關閉防火墻systemctl stop firewalld.service

開機自動啟動systemctl enable firewalld.service

關閉開機制動啟動systemctl disable firewalld.service

賦權：

[root@pgpool-tdb01 pg_data]# chown postgres.postgres pg_hba.conf.bak

[root@pgpool-tdb01 pg_data]# chown postgres.postgres postgresql.conf.bak

error：

pg_basebackup: could not connect to server: could not connect to server: No route

to host

Is the server running on host "master" (10.0.40.191) and accepting

TCP/IP connections on port 54321?

pg_basebackup: could not connect to server: FATAL: number of requested standby

connections exceeds max_wal_senders (currently 1)

pg_basebackup: could not get write-ahead log end position from server: ERROR:

could not open file "./pg_hba.conf.bak": Permission denied

以上報錯

需要關閉防火墻或在防火墻上設置端口限制。

[root@pgpool-tdb02 ~]# su - postgres -c 'pg_basebackup -D $PGDATA --

format=p -h master -p 54321 -U stream_replication -W'

Password:

[root@pgpool-tdb02 ~]# ll /data1/

total 4

drwxr-xr-x. 2 postgres postgres 6 Feb 17 19:03 pg_bin

drwx------. 21 postgres postgres 4096 Feb 17 21:18 pg_data

drwxr-xr-x. 2 postgres postgres 6 Feb 17 19:03 pg_logs

修改備庫配置信息：

cp $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf

vi $PGDATA/recovery.conf

[postgres@pgpool-tdb02 ~]$ vi $PGDATA/recovery.conf

增加以下內容

standby_mode='on'

primary_conninfo = 'host=master port=54321 user=stream_replication

password=1qaz.com'

restore_command = ''

recovery_target_timeline = 'latest'

重啟PG服務:

systemctl restart postgresql-10

systemctl enable postgresql-10.service

驗證

主節點執行

create table test (id int4, create_time timestamp(0) without time zone);

insert into test values (3, now());

select * from test;

備節點執行

select * from test;

其他查詢

進入測試數據庫test，主庫上執行如下命令返回f，備庫上返回t。

select pg_is_in_recovery();

執行如下命令查看快照，它返回主庫記錄點、備庫記錄點；主庫每增加一條寫入，記錄點的

值就會加1。

select txid_current_snapshot();

執行如下命令可以查看主備同步狀態。

select * from pg_stat_replication;

字段state顯示的同步狀態有：startup（連接中）、catchup（同步中）、streaming（同

步）；字段sync_state顯示的模式有：async（異步）、sync（同步）、potential（雖然現

在是異步模式，但是有可能升級到同步模式）。

主：

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

(1 row)

備：

postgres=# select pg_is_in_recovery();

pg_is_in_recovery

-------------------

(1 row)

主備切換

假設主庫崩潰了，備庫如何從只讀狀態切換為讀寫狀態呢？只要把備庫的

postgresql.conf中hot_standby修改為off，并且刪除recovery.conf，然后重啟

庫就可以提供服務了。

PGPool2（pool主機操作）

安裝PGPool2

yum install -y http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-

x86_64/pgpool-II-release-3.6-1.noarch.rpm

yum -y install pgpool-II-pg96 pgpool-II-pg96-debuginfo pgpool-II-pg96-devel

pgpool-II-pg96-extensions

systemctl enable pgpool.service #開啟自動啟動

下面的可以都安裝：