搭建双节点pg_pool+主从postgresql架构 -云博客

搭建双节点pg_pool+主从postgresql架构

# 数据库 2024-05-05 06:30 0 24 来源：云博客

复制方式优点缺点
————————————————————————————————————————————————————————————————————————————————————
同步数据一致性高 1.写入性能低
2.只支持一个从节点、多余的从节点为备份
3.若只有一个从节点时、从节点正好宕机、则阻塞主节点事物

异步方式（从节点都连接主节点）写入性能高 1.数据一致性相对弱点（具体看网络部署）
2.从节点多时，主节点存在压力较高

异步级联复制主节点压力小可靠性差、若前面从节点宕机，则后面从节点数据复制失效

异步+同步复制数据一致性+高可用都会有保障 1. 需要对同步的从节点设置备份节点
2. 故障切换复杂

搭建1主两从同步流复制环境

创建自定义网络类型，并且指定网段

1 10.1.20.114:docker network create --subnet=172.172.0.0/24 docker-br02 10.1.20.124:docker network create --subnet=172.172.1.0/24 docker-br0

View Code

创建docker系统

1 docker run -d --privileged --name pg01 --hostname pg01 --net docker-br0 --ip 172.172.0.100 -v /data/pgdata:/pgdata -p 5000:5432 -p 8000:9999 centos:latest /usr/sbin/init2 docker run -d --privileged --name pg02 --hostname pg02 --net docker-br0 --ip 172.172.1.100 -v /data/pgdata:/pgdata -p 5000:5432 -p 8000:9999 centos:latest /usr/sbin/init

View Code

创建路由

1 10.1.20.114:ip route add 172.172.1.0/24 via 10.1.20.124 dev eth02 10.1.20.124:ip route add 172.172.0.0/24 via 10.1.20.114 dev eth0

View Code

安装postgresql(10.1.20.114)

###安装依赖包
yum install zlib-devel gcc make

###创建用户和组
groupadd postgres
useradd -g postgres postgres
passwd postgres

###创建postgresql安装目录
mkdir -p /usr/local/postgresql
chown -R postgres:postgres /usr/local/postgresql

###下载安装文件
cd /usr/local/src
wget https://ftp.postgresql.org/pub/source/v10.5/postgresql-10.5.tar.gz

###解压
tar -xzvf postgresql-10.5.tar.gz

###安装软件
cd postgresql-10.5
./configure --prefix=/usr/local/postgresql --without-readline
make && make install

###安装contrib目录下的一些工具，是第三方组织的一些工具代码，建议安装
cd contrib
make && make install

###创建postgresql的数据目录
mkdir -p /pgdata/postgresql/data
mkdir -p /pgdata/postgresql/logfile
mkdir -p /pgdata/postgresql/archive
chown -R postgres:postgres /pgdata

su - postgres
###配置环境变量

vi .bash_profileexport PGHOME=/usr/local/postgresql/export PGDATA=/pgdata/postgresql/dataexport LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib/PATH=$PATH:/usr/local/postgresql/binsource .bash_profile

View Code

###初始化数据库
initdb -D /pgdata/postgresql/data

###创建配置文件postgresql.conf

cat > /pgdata/postgresql/data/postgresql.conf << EOFlisten_addresses = ‘*‘port = 5432max_connections = 100superuser_reserved_connections = 10wal_level = logical ##pg10版本中，待选的值为minimal、replica、logical。 minimal --不能通过基础备份和wal日志恢复数据库。 replica = 9.6版本以前的archive和hot_standby --该级别支持wal归档和复制。logical --在replica级别的基础上添加了支持逻辑解码所需的信息。full_page_writes = onwal_log_hints = offarchive_mode = onarchive_command = ‘cp %p /pgdata/postgresql/archive/%f < /dev/null‘max_wal_senders = 50hot_standby = onlog_destination = ‘csvlog‘logging_collector = onlog_directory = ‘/pgdata/postgresql/logfile‘log_filename = ‘postgresql-%Y-%m-%d_%H%M%S‘log_rotation_age = 1dlog_rotation_size = 10MBlog_statement = ‘mod‘log_timezone = ‘PRC‘timezone = ‘PRC‘EOF

###启动服务
pg_ctl -D /pgdata/postgresql/data -l logfile start

###设置为开机自启动

su - root# 添加启动服务（确认文件postgresql内的目录正确）cp /usr/local/src/postgresql-10.5/contrib/start-scripts/linux /etc/init.d/postgresql chown -R postgres:postgres /etc/init.d/postgresqlchmod a+x /etc/init.d/postgresql
# 添加开启自启动chkconfig --add postgresql
#修改开机启动文件/etc/init.d/postgresql的配置信息vi /etc/init.d/postgresql prefix=/usr/local/postgresqlPGDATA="/pgdata/postgresql/data"PGLOG="/pgdata/postgresql/logfile/serverlog"#重新加载并启动systemctl daemon-reloadsystemctl stop postgresqlsystemctl start postgresql

###启动后进程情况如下

1 postgres 9538 0 0 09:50 ? 00:00:00 /usr/local/postgresql/bin/postgres -D /pgdata/postgresql/data2 postgres 9540 9538 0 09:50 ? 00:00:00 postgres: checkpointer process 3 postgres 9541 9538 0 09:50 ? 00:00:00 postgres: writer process 4 postgres 9542 9538 0 09:50 ? 00:00:00 postgres: wal writer process 5 postgres 9543 9538 0 09:50 ? 00:00:00 postgres: autovacuum launcher process 6 postgres 9544 9538 0 09:50 ? 00:00:00 postgres: stats collector process 7 postgres 9545 9538 0 09:50 ? 00:00:00 postgres: bgworker: logical replication launcher

View Code

###测试

[root@pg01 data]# source ~/.bash_profile[root@pg01 data]# psql -U postgresPassword for user postgres: psql (10.5)Type "help" for help.postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+-----------+---------+-------+----------------------- postgres | postgres | SQL_ASCII | C | C | template0 | postgres | SQL_ASCII | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | SQL_ASCII | C | C | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | SQL_ASCII | C | C | (5 rows)postgres=# CREATE DATABASE pgpool ;CREATE DATABASEpostgres=# \c pgpoolYou are now connected to database "pgpool" as user "postgres".pgpool=#pgpool=# CREATE TABLE pgpool (id serial,age bigint,insertTime timestamp default now());CREATE TABLEpgpool=# insert into pgpool (age) values (1);INSERT 0 1pgpool=# select * from pgpool; id | age | inserttime----+-----+---------------------------- 1 | 1 | 2018-07-02 15:07:03.329849(1 row)1234567891011121314

安装postgresql(10.1.20.124)

安装步骤和主库10.1.20.114一致

###登录主库并创建同步账号

ALTER USER postgres WITH PASSWORD ‘postgres-vcredit‘;CREATE USER replica WITH PASSWORD ‘replica-vcredit‘ REPLICATION;

###修改配置信息（10.1.20.114上执行）

vi pg_hba.conflocal all all md5host all all 0.0.0.0/0 md5 host replication replica 0.0.0.0/0 md5 local all all ident map=map_root 注：trust为无密码信任登录，只需输入ip和port即可登录；mds需要用户验证登录；ident为映射系统账户到pgsql访问账户。

###同步主库数据（在10.1.20.124上执行）

pg_basebackup -F p --progress -D /pgdata/postgresql/data -h 10.1.20.114 -p 5432 -U replica --password#pg_basebackup -h 10.10.56.16 -p 5532 -U repl -w -Fp -Xs -Pv -R -D /pgdata/postgresql/data参数说明-h 启动的主库数据库地址 -p 主库数据库端口-U 流复制用户 -w 不使用密码验证-Fp 备份输出正常的数据库目录 -Xs 使用流复制的方式进行复制-Pv 输出复制过程的详细信息 -R 为备库创建recovery.conf文件-D 指定创建的备库的数据库目录

###创建recovery.cnf 文件（10.1.20.124）

cat >> recovery.conf << EOFstandby_mode = onprimary_conninfo = ‘application_name=slave1 host=10.1020.114 port=5432 user=replica password=replica-vcredit‘recovery_target_timeline = ‘latest‘#trigger_file = ‘/pgdata/postgresql/data/trigger_activesdb‘EOF

################################# 小技巧 ##########################################

我们默认情况下使用root，但是必须切换到postgres管理员用户才能执行initdb或psql等命令，而如果我们将root映射成postgres管理员，即可不需要切换用户。

#在pg_hba.conf添加本地账户为ident
vim pg_hba.conf
local all all ident map=map_root
#在pg_ident.conf中添加映射，将本地root账户映射为pgsql管理员账户postgres
vim pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
map_root root postgres
#测试在系统账户root下，直接以postgres账户登录数据库；而默认下是不允许这样登录的

###################################################################################

###重启服务

su - rootsystemctl stop postgresqlsystemctl start postgresql

至此主从搭建完毕

安装pgpool(10.1.20.114)

###安装依赖包

yum install gcc-c++ flex readline* readline-devel zlib* openssl openssl-devel -y yum -y install openssh openssh-clients openssh-server openssh-askpass yum -y install initscripts

###创建安装目录

mkdir -p /usr/local/pgpoolchown -R postgres:postgres /usr/local/pgpool

###下载安装包

cd /usr/local/src
wget http://www.pgpool.net/mediawiki/images/pgpool-II-3.7.5.tar.gz

###解压

tar -xzvf pgpool-II-3.7.5.tar.gz

###编译安装

cd pgpool-II-3.7.5./configure --prefix=/usr/local/pgpool -with-pgsql=path -with-pgsql=/usr/local/postgresql --with-opensslmake && make install

###安装扩展包

cd src/sql/$ cd pgpool-II-x.x.x/src/sql/pgpool-regclass$ make$ make installpsql -f pgpool-regclass.sql -h 172.172.1.100 -p 5432 -U postgres template1psql -f insert_lock.sql -h 172.172.1.100 -p 5432 -U postgres template1psql -f pgpool-recovery.sql -h 172.172.1.100 -p 5432 -U postgres template1chown -R postgres:postgres /usr/local/pgpool连接主库，创建扩展函数(该步骤非必须，上述安装成功内部已经安装了扩展，下述只为方便查看)postgres=# create extension pgpool_regclass;CREATE EXTENSIONpostgres=# CREATE EXTENSION pgpool_recovery;CREATE EXTENSIONpostgres=# select * from pg_extension; extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition-----------------+----------+--------------+----------------+------------+-----------+-------------- plpgsql | 10 | 11 | f | 1.0 | | pgpool_regclass | 10 | 2200 | t | 1.0 | | pgpool_recovery | 10 | 2200 | t | 1.1 | |(3 rows)postgres=#

###服务器配置pgpool-Ⅱ

cd /usr/local/pgpool/etccp pcp.conf.sample pcp.confcp pgpool.conf.sample pgpool.confcp pool_hba.conf.sample pool_hba.confmkdir -p /pgdata/pgpool/data /pgdata/pgpool/logfile /pgdata/pgpool/scriptchown -R postgres:postgres /pgdata
#执行完自动生成pool_passwd文件

pg_md5 -p -m -u postgres pool_passwd 
pg_md5 -p -m -u pg_check pool_passwd 
chown -R postgres:postgres /usr/local/pgpool/

###修改配置文件pgpool.conf

listen_addresses = ‘*‘port = 9999pcp_listen_addresses = ‘*‘pcp_port = 9898backend_hostname0 = ‘172.172.1.100‘ #主库IPbackend_port0 = 5432backend_weight0 = 1backend_data_directory0 = ‘/pgdata/postgresql/data‘backend_flag0 = ‘ALLOW_TO_FAILOVER‘backend_hostname1 = ‘172.172.0.100‘ #从库IPbackend_port1 = 5432backend_weight1 = 1backend_data_directory1 = ‘/pgdata/postgresql/data‘backend_flag1 = ‘ALLOW_TO_FAILOVER‘enable_pool_hba = onpool_passwd = ‘pool_passwd‘pid_file_name = ‘/pgdata/pgpool/data/pgpool.pid‘logdir = ‘/pgdata/pgpool/logfile‘connection_cache = onreplication_mode = offload_balance_mode = onmaster_slave_mode = onmaster_slave_sub_mode = ‘stream‘sr_check_period = 10sr_check_user = ‘postgres‘sr_check_password = ‘postgres-vcredit‘sr_check_database = ‘postgres‘delay_threshold = 10000000follow_master_command = ‘‘health_check_period = 3health_check_timeout = 20health_check_user = ‘postgres‘health_check_password = ‘postgres-vcredit‘health_check_database = ‘postgres‘health_check_max_retries = 0health_check_retry_delay = 1connect_timeout = 10000failover_command = ‘/pgdata/pgpool/script/failover.sh %d %P %H %R‘failback_command = ‘‘fail_over_on_backend_error = onsearch_primary_node_timeout = 10use_watchdog = onwd_hostname = ‘172.172.1.100‘wd_port = 9000wd_priority = 1wd_authkey = ‘‘wd_ipc_socket_dir = ‘/tmp‘#delegate_IP = ‘172.172.0.220‘ #VIP IP#if_cmd_path = ‘/usr/sbin‘#if_up_cmd = ‘ip addr add $_IP_$/24 dev eth0 label eth0:0‘ #if_down_cmd = ‘ip addr del $_IP_$/24 dev eth0‘ #arping_path = ‘/usr/sbin‘#arping_cmd = ‘arping -U $_IP_$ -w 1 -I eth0‘wd_monitoring_interfaces_list = ‘‘wd_lifecheck_method = ‘heartbeat‘wd_interval = 10wd_heartbeat_port = 9694wd_heartbeat_keepalive = 2wd_heartbeat_deadtime = 30heartbeat_destination0 = ‘172.172.0.100‘ #从库IPheartbeat_destination_port0 = 9694heartbeat_device0 = ‘eth0‘other_pgpool_hostname0 = ‘172.172.0.100‘ #从库IPother_pgpool_port0 = 9999other_wd_port0 = 9000

###创建failover.sh文件

cat >> /pgdata/pgpool/script/failover.sh << EOF#!/bin/bash -xfalling_node=$1old_primary=$2new_primary=$3pgdata=/pgdata/pgpool/datapghome=/usr/local/postgresqllog=/pgdata/pgpool/logfile/failover.logdate >> $logecho "failed_node_id=$falling_node new_primary=$new_primary" >> $logif [ $falling_node = $old_primary ]; then if [ $UID -eq 0 ] then su postgres -c "ssh -T postgres@$new_primary $pghome/bin/pg_ctl promote -D $pgdata" else ssh -T postgres@$new_primary $pghome/bin/pg_ctl promote -D $pgdata fi exit 0;fi;exit 0;EOFchmod 755 /pgdata/pgpool/script/failover.shchown -R postgres:postgres /pgdata/pgpool/script/failover.sh

###启动

pgpool -n -d -D -a /usr/local/pgpool/etc/pool_hba.conf -f /usr/local/pgpool/etc/pgpool.conf -F /usr/local/pgpool/etc/pcp.conf > /pgdata/pgpool/logfile/pgpool.log 2>&1 &

###停止

pgpool -m fast stop

###pgpool 状态查看

psql -h 10.200.22.114 -p 9999 -U postgres pgpoolshow pool_nodes;show pool_processes; -- 查看 pgpool进程 信息show pool_status; -- 查看pgpool 配置信息show pool_pools; -- 查看 pgpool 连接池

###pcp 配置管理 pgpool

pcp_watchdog_info -h 10.1.20.114 -p 9898 -U postgres -v --查看 pgpool集群状态pcp_node_count -h 10.1.20.114 -p 9898 -U postgres -v -- 查看节点数量pcp_pool_status -h 10.1.20.114 -p 9898 -U postgres -v -- 查看pgpool集群配置pcp_proc_count -h 10.1.20.114 -p 9898 -U postgres -v -- 查看pgpool processor进程信息

参考资料：

https://blog.csdn.net/yaoqiancuo3276/article/details/80983201
https://blog.csdn.net/kk185800961/article/details/78458999
https://blog.csdn.net/yanggd1987/article/details/51150190
https://my.oschina.net/Suregogo/blog/551626
https://my.oschina.net/Suregogo/blog/552765
https://www.jianshu.com/p/2d07339774c0
https://blog.csdn.net/hkyw000/article/details/51693683
https://www.cnblogs.com/yjf512/p/4499547.html