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

复制方式           优点                                                                 缺点
————————————————————————————————————————————————————————————————————————————————————
同步 数据一致性高                                            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 postgresuseradd -g postgres postgrespasswd postgres

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

###下载安装文件cd /usr/local/srcwget 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-readlinemake && make install

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

###创建postgresql的数据目录mkdir -p /pgdata/postgresql/datamkdir -p /pgdata/postgresql/logfilemkdir -p /pgdata/postgresql/archivechown -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 

###测试
[[email protected] data]# source ~/.bash_profile
[[email protected] 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/srcwget 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 [email protected]$new_primary $pghome/bin/pg_ctl promote -D $pgdata"
    else
        ssh -T [email protected]$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

原文地址:https://www.cnblogs.com/EikiXu/p/9773694.html

时间: 10-11

搭建双节点pg_pool+主从postgresql架构的相关文章

双节点(nginx+keepalived)为两台apache服务器提供负载均衡

说明:本实验为双节点nginx为两台apache服务器提供负载均衡,本文不是做lvs,所以realserver不是配置在keepalived.conf而是在nginx的配置文件中upstream.此架构需考虑的问题:1)Master没挂,则Master占有vip且nginx运行在Master上2)Master挂了,则backup抢占vip且在backup上运行nginx服务3)如果master服务器上的nginx服务挂了,则vip资源转移到backup服务器上4)检测后端服务器的健康状态Mast

MySQL主从多种架构部署及常见错误问题解析

本文的主要内容有mysql复制原理,mysql一主多从.双主架构的示例解读,以及mysql在主从复制架构实践中的常见错误问题和解决方法. 一 mysql复制原理 1 原理解读 mysql的复制(replication)是异步复制,即从一个mysql实列或端口(Master)复制到另一个mysql实列的或端口(slave):复制操作由3个进程完成,其中2个(SQL进程和I/O进程)在Slave上,另一个在Master上:要实现复制,必须打开Master端的二进制日志(log-bin),log-bi

MySQL双主互备模式架构

MySql双主互备架构模式 操作系统 CentOS 6.3 MySql版本 mysql-5.0.56 keepalived keepalived-1.1.19.tar.gz Master DB1配置文件 修改MySql 配置文件  /etc/my.cnf 增加以下段落 [mysqld] server-id=1    节点标示 log-bin=mysql-bin   开启mysql的binlog日志功能 relay-log = mysql-relay-bin  用来定义日志文件命名格式 repli

mysql高可用研究之主从+MHA架构

最近在研究mysql的高可用架构,自己想总结下常用的高可用方案都有哪些.有哪些优缺点以及应用的场景?搞得是头昏脑涨,天昏地暗,看了诸多资料,每次都觉得公说公有理婆说婆有理.其实嘛,大家都没有说错,只不过适合自己的才是最正确的选择.今天就从比较常用的主从+MHA说起. 学习一种新的架构还是软件,最好还是先从了解它的原理开始,这样才能在做实验时测试出它的优势和弱势. ###################################################################

搭建基于spring MVC框架 + RESTful架构风格技术总结

实战篇: 在SpringMVC框架中搭建RESTful架构风格来完成客户端与服务器端的低耦合度.可扩展性.高并发与大数据流量的访问. 用RESTful架构的创建步骤: 1.创建一个全新的Web工程 2.导包,导入所需要的所有第三方jar包.(springMVC+Hibernate的基本包是必须的) 3.作配置,针对不同的项目需求和不同的搭建设计,开发人员可以按照自己的编码风格来设计符合项目开发具体 应该用多少篇配置文件.但是这几篇配置文件是必不可少的: 3-1.web.xml配置文件:最基本的配

DB主从一致性架构优化4种方法

一.需求缘起 大部分互联网的业务都是"读多写少"的场景,数据库层面,读性能往往成为瓶颈.如下图:业界通常采用"一主多从,读写分离,冗余多个读库"的数据库架构来提升数据库的读性能. 这种架构的一个潜在缺点是,业务方有可能读取到并不是最新的旧数据: (1)系统先对DB-master进行了一个写操作,写主库 (2)很短的时间内并发进行了一个读操作,读从库,此时主从同步没有完成,故读取到了一个旧数据 (3)主从同步完成 有没有办法解决或者缓解这类"由于主从延时导致

网狐棋牌源码搭建教程之棋牌平台服务器架构(一)

一,棋牌类服务器的特点 1,棋牌类不分区不分服 一般来说,棋牌游戏都是不分区不分服的.所以棋牌类服务器要满足随着用户量的增加而扩展的需要. 2,房间模式 即在同一局游戏中就是在同一个房间中,同一个房间中的人可以接收到其他人的消息. 3,每个房间的操作必须是顺序性 这个特性类似与一般游戏的回合制,每个玩家的操作都是有顺序性的. 二,需要解决的技术点 1,数据共享 因为棋牌类游戏不分区不分服(棋牌源码搭建 www.yasewl.com),我们在设计服务器的时候,是按世界服的思想去设计,即服务器是一个

Mac下的hadoop环境搭建(单节点)

一.安装Java 1.下载以及安装 官网下载jdk,我下载的是1.8.0_45 网址:http://www.oracle.com/technetwork/java/javase/downloads/index-jsp-138363.html 如下所示: 然后安装,默认安装路径是:/Library/Java/JavaVirtualMachines/jdk1.8.0_45.jdk/Contents/Home 2.测试是否安装成功 在终端输入 : java -version 如果安装成功,则会显示相应

Centos6.5下OpenLdap搭建(环境配置+双机主从配置+LDAPS+enable SSHA)

为什么要做这个事 公司打算做统一认证,由于LDAP支持radius,可以把网络.安全设备集中在一个LDAP中认证: OpenLDAP OpenLDAP 是 LDAP 协议的一个开源实现.LDAP 服务器本质上是一个为只读访问而优化的非关系型数据库.它主要用做地址簿查询(如 email 客户端)或对各种服务访问做后台认证以及用户数据权限管控.(例如,访问 Samba 时,LDAP 可以起到域控制器的作用:或者 Linux 系统认证 时代替 /etc/passwd 的作用.) 环境 Centos 6

BIND:DNS主从服务器架构和安全控制详解

前提: 本篇文章以虚拟机中创建的CentOS 7系统主机配置为例进行叙述 主DNS服务器地址 172.16.49.2    CentOS 7 从DNS服务器地址 172.16.49.3    CentOS 使用二级域名 xuding.win 一.主-从DNS服务器配置 主DNS服务器:维护所负责解析的域数据库的服务器:读写操作均可进行: 从DNS服务器:从主DNS服务器那里或其它的从DNS服务器那里"复制"一份解析库:但只能进行读操作: 注意:从服务器是区域级别的概念,相对于某个区域配