Pgpool-II部署

There are six different running modes in Pgpool-II: streaming
replication mode, logical replication mode, main replica mode (slony
mode), ‎本地复制‎ mode, raw mode and ‎快照等素‎ mode. In any mode, Pgpool-II
provides connection pooling, and automatic fail over. Online recovery
can be used only with streaming replication mode and native
replication mode.

Those modes are exclusive each other and cannot be changed after
starting the server. You should make a decision which to use in the
early stage of designing the system. If you are not sure, it is
recommended to use the streaming replication mode.

The streaming replication mode can be used with PostgreSQL servers
operating streaming replication. In this mode, PostgreSQL is
responsible for ‎同步‎databases. This mode is widely used and most
recommended way to use Pgpool-II. Load balancing is possible in the
mode.

In the native replication mode, Pgpool-II is responsible for
synchronizing databases. The advantage for the mode is the
synchronization is done in synchronous way: writing to the database
does not return until all of PostgreSQL servers finish the write
operation. However, you could get a similar effect using PostgreSQL
9.6 or later with synchronous_commit = remote_apply being set in streaming replication. If you could use the setting, we strongly
recommend to use it instead of native replication mode because you can
avoid some restrictions in the native replication mode. Since
PostgreSQL does not provide cross node snapshot control, it is
possible that session X can see data on node A committed by session Y
before session Y commits data on node B. If session X tries to update
data on node B based on the data seen on node A, then data consistency
between node A and B might be lost. To avoid the problem, user need to
issue an explicit lock on the data. This is another reason why we
recommend to use streaming replication mode with synchronous_commit =
remote_apply.

Load balancing is possible in the mode.

The logical replication mode can be used with PostgreSQL servers
operating logical replication. In this mode, PostgreSQL is responsible
for synchronizing tables. Load balancing is possible in the mode.
Since logical replication does not replicate all tables, it’s user’s
responsibility to replicate the table which could be load balanced.
Pgpool-II load balances all tables. This means that if a table is not
replicated, Pgpool-II may lookup outdated tables in the subscriber
side.

The main replica mode mode (slony mode) can be used with PostgreSQL
servers operating Slony. In this mode, Slony/PostgreSQL is responsible
for synchronizing databases. Since Slony-I is being obsoleted by
streaming replication, we do not recommend to use this mode unless you
have specific reason to use Slony. Load balancing is possible in the
mode.

The snapshot isolation mode is similar to the native replication mode
and it adds the visibility consistency among nodes. Please note that
there are some limitations in this mode and currently (in Pgpool-II
4.2) this mode is regarded as “experimental” implementation. Be warned that careful testings are required before you implement this in a
production system.

It is necessary to set the transaction isolation level to REPEATABLE
READ. That means you need to set it in postgresql.conf like this:

default_transaction_isolation = ‘repeatable read’
Consistent visibility in SERIAL data type and sequences are not guaranteed.

In the raw mode, Pgpool-II does not care about the database
synchronization. It’s user’s responsibility to make the whole system
does a meaningful thing. Load balancing is not possible in the mode.

1.pgpool安装
防火墙配置

firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp --add-port=9000/tcp
firewall-cmd --permanent --zone=public --add-port=9694/udp
firewall-cmd --reload
firewall-cmd --zone=public --remove-service=popstgresql --permanent
firewall-cmd --zone=public --add-port=5432/tcp --permanent
firewall-cmd --reload

yum install -y openssl-devel


./configure --prefix=/usr/local/pgpool4.2.5 --with-openssl
make && make install
ln -sf /usr/local/pgpool4.2.5 /usr/local/pgpool
chown -R postgres:postgres /usr/local/pgpool
chown -R postgres:postgres /usr/local/pgpool4.2.5

mkdir -p /var/log/pgpool_logs/
chown -R postgres:postgres /var/log/pgpool_logs/
mkdir -p /var/run/pgpool/
chown -R postgres:postgres /var/run/pgpool

2.主库安装pgpool_recovery

cd pgpool-II-4.2.5/src/sql/pgpool-recovery
make && make install
su - postgres
psql template1
CREATE EXTENSION pgpool_recovery;
select * from pg_extension;

3.配置
编辑环境变量

vim /etc/profile
export PATH=/usr/local/pgsql/bin:/usr/local/pgpool/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:LD_LIBRARY_PATH


cp pgpool.conf.sample pgpool.conf
cp pcp.conf.sample pcp.conf
cp pool_hba.conf.sample pool_hba.conf

mkdir -p /var/run/pgpool/
chown -R postgres:postgres /var/run/pgpool/
mkdir -p /var/log/pgpool/
chown -R postgres:postgres /var/log/pgpool/


vim pgpool.conf
listen_addresses = '*'
port = 9999
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/var/log/pgpool'
backend_hostname0 = '192.168.124.61'
                                   # Host name or IP address to connect to for backend 0
backend_port0 = 5432
                                   # Port number for backend 0
backend_weight0 = 1
                                   # Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/postgres'
                                   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
                                   # Controls various backend behavior
                                   # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
                                   # or ALWAYS_PRIMARY
backend_application_name0 = 'server0'
                                   # walsender's application_name, used for "show pool_nodes" command
backend_hostname1 = '192.168.124.62'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/postgres'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'

backend_hostname2 = '192.168.124.63'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/postgres'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'server2'

enable_pool_hba = on




pg_md5 -m -p -u postgres pool_passwd -f /usr/local/pgpool/etc/pgpool.conf

如下是数据库用户密码：

more pool_passwd
postgres:md53175bce1d3201d16594cebf9d7eb3f9d

vim pool_hba.conf
host    all         all         0/0                   trust


pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5

vim pcp.conf
postgres:e8a48653851e28c69d0506508fb27fc5

chmod 0600  /usr/local/pgpool/etc/pool_passwd
pgpool -f /usr/local/pgpool/etc/pgpool.conf -a /usr/local/pgpool/etc/pool_hba.conf -k /usr/local/pgpool/etc/pool_passwd -F /usr/local/pgpool/etc/pcp.conf

启动失败可以使用-n选项，会显示日志，通过报错信息处理问题

pgpool -n

pgpool stop

"show"在PostgreSQL中是一个真正的SQL命令，但pgpool-II扩展了此命令。连接到pgpool-II后可以使用“show”命令查看pgpool-II的信息，这些命令的说明如下。
·pool_status：获得pgpool-II的配置信息。
·pool_nodes：获得后端各节点的状态信息，如后端数据库是否在线。
·pool_processes：显示pgpool-II的进程信息。
·pool_pools：显示pgpool-II连接池中的各个连接信息。
·pool_version：显示pgpool-II的版本。

用如下脚本测试负载均衡情况：

vim pgtest.sh
while true
do
psql -h 192.168.124.61 -U postgres -p 9999 << !
show pool_nodes
\q
!
sleep 0.1
done

5.压力测试

pgbench -i -p 9999 -h 192.168.124.61
pgbench -p 9999 -T 10  -h 192.168.124.61

6.临时关库

In this case you can use backend_flag to avoid failover. By setting
below in pgpool.conf will avoid failover of backend0.

backend_flag0 = DISALLOW_TO_FAILOVER
This will take effect by reloading or restarting Pgpool-II. If this flag is set, failover will not happen if the backend is not
available. While the backend is not available, clients wil get error
message:

psql: error: could not connect to server: FATAL: failed to create a
backend connection DETAIL: executing failover on backend
After restarting the backend, clients can connect as usual. To allow failover on the backend again, you can set:

backend_flag0 = ALLOW_TO_FAILOVER
and reload or restart Pgpool-II.

7.使用pg_rewind需要在主库进行如下配置

listen_addresses = '*'
archive_mode = on
archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on

8.配置互信

[all servers]# cd ~/.ssh
[all servers]# ssh-keygen -t rsa -f id_rsa_pgpool
[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@192.168.124.61
[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@192.168.124.62
[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@192.168.124.63


[all servers]# su - postgres
[all servers]$ cd ~/.ssh
[all servers]$ ssh-keygen -t rsa -f id_rsa_pgpool
[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server3

9.配置密码
这个后面在测试

vim ~/.pgpass
192.168.124.61:5432:replication:repl:repl
192.168.124.62:5432:replication:repl:repl
192.168.124.63:5432:replication:repl:repl
192.168.124.61:5432:postgres:postgres:postgres
192.168.124.62:5432:postgres:postgres:postgres
192.168.124.63:5432:postgres:postgres:postgres

chmod 600 ~/.pgpass

目前先使用这个办法

pg_md5 --md5auth -u postgres  helloosdba

pg_md5 pgpool

10.新从库落后太多配置

pg_rewind -D /postgres --source-server='host=192.168.124.64 user=postgres password=postgres'

11.老主库恢复为新从库

pcp_recovery_node -h localhost -Upgpool -n0