复制方式 优点 缺点
————————————————————————————————————————————————————————————————————————————————————
同步 数据一致性高 1.写入性能低
2.只支持一个从节点、多余的从节点为备份
3.若只有一个从节点时、从节点正好宕机、则阻塞主节点事物
异步方式（从节点都连接主节点） 写入性能高 1.数据一致性相对弱点（具体看网络部署）
2.从节点多时，主节点存在压力较高
异步级联复制 主节点压力小 可靠性差、若前面从节点宕机，则后面从节点数据复制失效
异步+同步复制 数据一致性+高可用都会有保障 1. 需要对同步的从节点设置备份节点
2. 故障切换复杂
搭建1主两从同步流复制环境
创建自定义网络类型，并且指定网段
1 10.1.20.114:docker network create --subnet=172.172.0.0/24 docker-br0 2 10.1.20.124:docker network create --subnet=172.172.1.0/24 docker-br0
创建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/init 2 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
创建路由
1 10.1.20.114:ip route add 172.172.1.0/24 via 10.1.20.124 dev eth0 2 10.1.20.124:ip route add 172.172.0.0/24 via 10.1.20.114 dev eth0
安装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_profile export PGHOME=/usr/local/postgresql/ export PGDATA=/pgdata/postgresql/data export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib/ PATH=$PATH:/usr/local/postgresql/bin source .bash_profile
###初始化数据库
initdb -D /pgdata/postgresql/data
###创建配置文件postgresql.conf
cat > /pgdata/postgresql/data/postgresql.conf << EOF listen_addresses = '*' port = 5432 max_connections = 100 superuser_reserved_connections = 10 wal_level = logical ##pg10版本中，待选的值为minimal、replica、logical。 minimal --不能通过基础备份和wal日志恢复数据库。 replica = 9.6版本以前的archive和hot_standby --该级别支持wal归档和复制。logical --在replica级别的基础上添加了支持逻辑解码所需的信息。 full_page_writes = on wal_log_hints = off archive_mode = on archive_command = 'cp %p /pgdata/postgresql/archive/%f < /dev/null' max_wal_senders = 50 hot_standby = on log_destination = 'csvlog' logging_collector = on log_directory = '/pgdata/postgresql/logfile' log_filename = 'postgresql-%Y-%m-%d_%H%M%S' log_rotation_age = 1d log_rotation_size = 10MB log_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/postgresql chmod a+x /etc/init.d/postgresql
# 添加开启自启动 chkconfig --add postgresql
#修改开机启动文件/etc/init.d/postgresql的配置信息 vi /etc/init.d/postgresql prefix=/usr/local/postgresql PGDATA="/pgdata/postgresql/data" PGLOG="/pgdata/postgresql/logfile/serverlog" #重新加载并启动 systemctl daemon-reload systemctl stop postgresql systemctl start postgresql
###启动后进程情况如下
1 postgres 9538 0 0 09:50 ? 00:00:00 /usr/local/postgresql/bin/postgres -D /pgdata/postgresql/data 2 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
###测试
[root@pg01 data]# source ~/.bash_profile [root@pg01 data]# psql -U postgres Password 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 DATABASE postgres=# \c pgpool You are now connected to database "pgpool" as user "postgres". pgpool=# pgpool=# CREATE TABLE pgpool (id serial,age bigint,insertTime timestamp default now()); CREATE TABLE pgpool=# insert into pgpool (age) values (1); INSERT 0 1 pgpool=# 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.conf
local all all md5
host 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 << EOF standby_mode = on primary_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 - root
systemctl stop postgresql
systemctl 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/pgpool
chown -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-openssl make && make install
###安装扩展包
cd src/sql/ $ cd pgpool-II-x.x.x/src/sql/pgpool-regclass $ make $ make install psql -f pgpool-regclass.sql -h 172.172.1.100 -p 5432 -U postgres template1 psql -f insert_lock.sql -h 172.172.1.100 -p 5432 -U postgres template1 psql -f pgpool-recovery.sql -h 172.172.1.100 -p 5432 -U postgres template1 chown -R postgres:postgres /usr/local/pgpool 连接主库，创建扩展函数(该步骤非必须，上述安装成功内部已经安装了扩展，下述只为方便查看) postgres=# create extension pgpool_regclass; CREATE EXTENSION postgres=# CREATE EXTENSION pgpool_recovery; CREATE EXTENSION postgres=# 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/etc cp pcp.conf.sample pcp.conf cp pgpool.conf.sample pgpool.conf cp pool_hba.conf.sample pool_hba.conf mkdir -p /pgdata/pgpool/data /pgdata/pgpool/logfile /pgdata/pgpool/script chown -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 = 9999 pcp_listen_addresses = '*' pcp_port = 9898 backend_hostname0 = '172.172.1.100' #主库IP backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/pgdata/postgresql/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = '172.172.0.100' #从库IP backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/pgdata/postgresql/data' backend_flag1 = 'ALLOW_TO_FAILOVER' enable_pool_hba = on pool_passwd = 'pool_passwd' pid_file_name = '/pgdata/pgpool/data/pgpool.pid' logdir = '/pgdata/pgpool/logfile' connection_cache = on replication_mode = off load_balance_mode = on master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_period = 10 sr_check_user = 'postgres' sr_check_password = 'postgres-vcredit' sr_check_database = 'postgres' delay_threshold = 10000000 follow_master_command = '' health_check_period = 3 health_check_timeout = 20 health_check_user = 'postgres' health_check_password = 'postgres-vcredit' health_check_database = 'postgres' health_check_max_retries = 0 health_check_retry_delay = 1 connect_timeout = 10000 failover_command = '/pgdata/pgpool/script/failover.sh %d %P %H %R' failback_command = '' fail_over_on_backend_error = on search_primary_node_timeout = 10 use_watchdog = on wd_hostname = '172.172.1.100' wd_port = 9000 wd_priority = 1 wd_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 = 10 wd_heartbeat_port = 9694 wd_heartbeat_keepalive = 2 wd_heartbeat_deadtime = 30 heartbeat_destination0 = '172.172.0.100' #从库IP heartbeat_destination_port0 = 9694 heartbeat_device0 = 'eth0' other_pgpool_hostname0 = '172.172.0.100' #从库IP other_pgpool_port0 = 9999 other_wd_port0 = 9000
###创建failover.sh文件
cat >> /pgdata/pgpool/script/failover.sh << EOF #!/bin/bash -x falling_node=$1 old_primary=$2 new_primary=$3 pgdata=/pgdata/pgpool/data pghome=/usr/local/postgresql log=/pgdata/pgpool/logfile/failover.log date >> $log echo "failed_node_id=$falling_node new_primary=$new_primary" >> $log if [ $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; EOF chmod 755 /pgdata/pgpool/script/failover.sh chown -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 pgpool show 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
