postgresql 10 使用PGPOOL-II做集群

最新推荐文章于 2024-07-10 17:29:20 发布

chunhongbi5635

最新推荐文章于 2024-07-10 17:29:20 发布

阅读量1k

点赞数

文章标签：数据库运维操作系统

原文链接：https://my.oschina.net/heiky/blog/1588143

版权

DBA不可不知的操作系统内核参数

修改/etc/sysctl.conf

# vi /etc/sysctl.conf

# add by digoal.zhou
fs.aio-max-nr = 1048576
fs.file-max = 76724600
kernel.core_pattern= /data01/corefiles/core_%e_%u_%t_%s.%p         
# /data01/corefiles事先建好，权限777，如果是软链接，对应的目录修改为777
kernel.sem = 4096 2147483647 2147483646 512000    
# 信号量, ipcs -l 或 -u 查看，每16个进程一组，每组信号量需要17个信号量。
kernel.shmall = 107374182      
# 所有共享内存段相加大小限制(建议内存的80%)
kernel.shmmax = 274877906944   
# 最大单个共享内存段大小(建议为内存一半), >9.2的版本已大幅降低共享内存的使用
kernel.shmmni = 819200         
# 一共能生成多少共享内存段，每个PG数据库集群至少2个共享内存段
net.core.netdev_max_backlog = 10000
net.core.rmem_default = 262144       
# The default setting of the socket receive buffer in bytes.
net.core.rmem_max = 4194304          
# The maximum receive socket buffer size in bytes
net.core.wmem_default = 262144       
# The default setting (in bytes) of the socket send buffer.
net.core.wmem_max = 4194304          
# The maximum send socket buffer size in bytes.
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_keepalive_intvl = 20
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_mem = 8388608 12582912 16777216
net.ipv4.tcp_fin_timeout = 5
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syncookies = 1    
# 开启SYN Cookies。当出现SYN等待队列溢出时，启用cookie来处理，可防范少量的SYN攻击
net.ipv4.tcp_timestamps = 1    
# 减少time_wait
net.ipv4.tcp_tw_recycle = 0    
# 如果=1则开启TCP连接中TIME-WAIT套接字的快速回收，但是NAT环境可能导致连接失败，建议服务端关闭它
net.ipv4.tcp_tw_reuse = 1      
# 开启重用。允许将TIME-WAIT套接字重新用于新的TCP连接
net.ipv4.tcp_max_tw_buckets = 262144
net.ipv4.tcp_rmem = 8192 87380 16777216
net.ipv4.tcp_wmem = 8192 65536 16777216
net.nf_conntrack_max = 1200000
net.netfilter.nf_conntrack_max = 1200000
vm.dirty_background_bytes = 409600000       
#  系统脏页到达这个值，系统后台刷脏页调度进程 pdflush（或其他） 自动将(dirty_expire_centisecs/100）秒前的脏页刷到磁盘
vm.dirty_expire_centisecs = 3000             
#  比这个值老的脏页，将被刷到磁盘。3000表示30秒。
vm.dirty_ratio = 95                          
#  如果系统进程刷脏页太慢，使得系统脏页超过内存 95 % 时，则用户进程如果有写磁盘的操作（如fsync, fdatasync等调用），则需要主动把系统脏页刷出。
#  有效防止用户进程刷脏页，在单机多实例，并且使用CGROUP限制单实例IOPS的情况下非常有效。  
vm.dirty_writeback_centisecs = 100            
#  pdflush（或其他）后台刷脏页进程的唤醒间隔， 100表示1秒。
vm.mmap_min_addr = 65536
vm.overcommit_memory = 0     
#  在分配内存时，允许少量over malloc, 如果设置为 1, 则认为总是有足够的内存，内存较少的测试环境可以使用 1 .  
vm.overcommit_ratio = 90     
#  当overcommit_memory = 2 时，用于参与计算允许指派的内存大小。
vm.swappiness = 0            
#  关闭交换分区
vm.zone_reclaim_mode = 0     
# 禁用 numa, 或者在vmlinux中禁止. 
net.ipv4.ip_local_port_range = 40000 65535    
# 本地自动分配的TCP, UDP端口号范围
fs.nr_open=20480000
# 单个进程允许打开的文件句柄上限

# 以下参数请注意
# vm.extra_free_kbytes = 4096000
# vm.min_free_kbytes = 2097152
# 如果是小内存机器，以上两个值不建议设置
# vm.nr_hugepages = 66536    
#  建议shared buffer设置超过64GB时 使用大页，页大小 /proc/meminfo Hugepagesize
# vm.lowmem_reserve_ratio = 1 1 1
# 对于内存大于64G时，建议设置，否则建议默认值 256 256 32

修改/etc/security/limits.conf

# nofile超过1048576的话，一定要先将sysctl的fs.nr_open设置为更大的值，并生效后才能继续设置nofile.

* soft    nofile  1024000
* hard    nofile  1024000
* soft    nproc   unlimited
* hard    nproc   unlimited
* soft    core    unlimited
* hard    core    unlimited
* soft    memlock unlimited
* hard    memlock unlimited

以上是postgresql数据库的Linux系统参数调优，其解释已经非常明了，如果不清楚可以网上搜索“PostgreSQL on Linux 最佳部署手册”，在这感谢该作者的贡献！！

postgresql 10的安装

yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-1.noarch.rpm

yum install postgresql10 postgresql10-server postgresql10-devel -y

初始化数据库

在这里我没有用postgres用户，而是使用自己定义的用户dbuser进行数据库管理，本人觉得关于postgresql数据库的管理用户随便定义就好了，不用非要用特定的用户。另外，dbuser在不同服务器需要设置ssh免密登录，这涉及到数据库间的复制的用户权限。

#初始化数据库
$/usr/pgsql-10/bin/initdb -D /opt/db/pgdb10/

#数据库启动与停止
$/usr/pgsql-10/bin/pg_ctl -D /opt/db/pgdb10/ -l $/sas/pgdb10/logfile start
$/usr/pgsql-10/bin/pg_ctl -D /opt/db/pgdb10/ -m fast stop

#创建数据库
$createdb -E utf-8 -U dbuser mydb
$psql -d mydb 
#修改dbuser的密码，后面使用md5的验证方式，不修改密码会验证不通过
mydb=#alter user dbuser with password 'db123';
#创建复制流用户
mydb=#create role repl login replication encrypted password 'db123';

修改配置文件

postgresql.conf

listen_addresses = '*' #指定监听的地址 defaults to 'localhost'; use '*' for all
port = 5432  #指定数据库的端口 
unix_socket_directories = '/opt/db/pgdb10/' #
password_encryption = md5 #指定用户密码验证方式

pg_hba.conf

#指定数据库被访问IP地址范围，以及用户密码的验证方式
host    all             all             172.16.36.0/24          md5
#指定复制数据库的IP地址范围和用户，以及加密方式
host	replication	repl		172.16.36.0/24		md5

pgpool安裝

yum install -y http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-release-3.7-1.noarch.rpm
yum install pgpool-II-pg10-debuginfo pgpool-II-pg10-devel  pgpool-II-pg10-extensions  pgpool-II-pg10

注意：pgpool的配置以及相关执行命令均使用root用户执行。

postgresql数据库准备

首先，将主数据库设置为流复制模式。在主备机上创建数据归档目录：

#创建归档目录，owner是dbuser别弄错了
mkdir /opt/db/archivedir

修改==主机==的postgresql.conf

listen_addresses = '*'
wal_level = replica #pg10以上版本值为replica，pg10以下版本为hot_standby
max_wal_senders = 2

#打开归档模式
archive_mode = on
#归档数据文件到指定目录
archive_command = 'cp "%p" "/opt/db/archivedir/%f"'

准备数据库节点

进入/etc/pgpool-II/目录，首先备份pgpool.conf文件，由于我们是用流复制做数据同步，所以复制一份pgpool.conf.sample-stream文件替换pgpool.conf,操作如下：

#cd /etc/pgpool-II/
#cp pgpool.conf pgpool.conf.bak
#cp pgpool.conf.sample-stream pgpool.conf

在pgpool.conf上的配置如下：

listen_addresses = '*' #修改pgpool监听IP地址
...

backend_hostname0 = '172.16.36.137'
                                   # 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 = '/opt/db/pgdb10'
                                   # Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
                                   # Controls various backend behavior
                                   # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
				   # or ALWAYS_MASTER
backend_hostname1 = '172.16.36.138'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/opt/db/pgdb10'
backend_flag1 = 'ALLOW_TO_FAILOVER'

...
#指定日志输出路径
logdir = '/var/log/pgpool' 

...
sr_check_user = 'repl'
sr_check_password = 'db123'

分别为 backend_hostname，backend_port，backend_weight 设置节点的主机名，端口号和负载均衡系数。在每个参数串的后面，必须通过添加从0开始（例如 0，1，2，…）的整数来指出节点编号。

backend_weight 参数都为 1 ，这意味着 SELECT 查询被平均分配到db服务器上。

Failover configuration

failover_command = '/etc/pgpool-II/failover.sh %d %P %H %R'

创建/etc/pgpool-II/failover.sh

# vi /etc/pgpool-II/failover.sh
# chmod 755 /etc/pgpool-II/failover.sh

failover.sh文件内容

#! /bin/sh -x
# Execute command by failover.
# special values:  %d = node id
#                  %h = host name
#                  %p = port number
#                  %D = database cluster path
#                  %m = new master node id
#                  %M = old master node id
#                  %H = new master node host name
#                  %P = old primary node id
#                  %R = new master database cluster path
#                  %r = new master port number
#                  %% = '%' character

falling_node=$1          # %d
old_primary=$2           # %P
new_primary=$3           # %H
pgdata=$4                # %R

pghome=/usr/pgsql-10
log=/var/log/pgpool/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 dbuser -c "ssh -T dbuser@$new_primary $pghome/bin/pg_ctl promote -D $pgdata"
    else
        ssh -T dbuser@$new_primary $pghome/bin/pg_ctl promote -D $pgdata
    fi
    exit 0;
fi;
exit 0;

pgpool-II在线恢复设置

pgpool.conf文件设置

recovery_user = 'dbuser'
                                   # Online recovery user
recovery_password = 'db123'
                                   # Online recovery password

recovery_1st_stage_command = 'recovery_1st_stage'

创建相关脚本文件

$ > /opt/db/pgdb10/recovery_1st_stage
$ > /opt/db/pgdb10/pgpool_remote_start
$ chmod 755 /opt/db/pgdb10/recovery_1st_stage
$ chmod 755 /opt/db/pgdb10/pgpool_remote_start

注意：以上两个文件的owner是dbuser，否则在数据库复制过程中会出错。

recovery_1st_stage文件内容

#!/bin/bash -x
# Recovery script for streaming replication.

pgdata=$1
remote_host=$2
remote_pgdata=$3
port=$4

pghome=/usr/pgsql-10
archivedir=/opt/db/archivedir
hostname=$(hostname)

ssh -T dbuser@$remote_host "
rm -rf $remote_pgdata
$pghome/bin/pg_basebackup -h $hostname -U repl -D $remote_pgdata  -c fast
rm -rf $archivedir/*

cd $remote_pgdata
cp postgresql.conf postgresql.conf.bak
sed -e 's/#*hot_standby = off/hot_standby = on/' postgresql.conf.bak > postgresql.conf
rm -f postgresql.conf.bak
cat > recovery.conf << EOT
standby_mode = 'on'
primary_conninfo = 'host="$hostname" port=$port user=repl'
restore_command = 'scp $hostname:$archivedir/%f %p'
EOT
"

pgpool_remote_start文件内容

#! /bin/sh -x

pghome=/usr/pgsql-10
remote_host=$1
remote_pgdata=$2

# Start recovery target PostgreSQL server
ssh -T $remote_host $pghome/bin/pg_ctl -w -D $remote_pgdata start > /dev/null 2>&1 < /dev/null &

准备相关脚本后，我们需要在主数据库上的template1安装pgpool_recovery，操作如下：

# su - dbuser
$ psql template1 -h 172.16.36.137
=# CREATE EXTENSION pgpool_recovery;

客户端权限设置

在pgpool.conf文件中修改过enable_pool_hba,默认为off。

enable_pool_hba = on

修改pool_hba.conf文件

#其余相关设置请注释，如有其他设置请自行添加
host    all             all     172.16.36.0/24          md5
host    replication     repl    172.16.36.0/24          md5

执行pg_md5 --md5auth --username=<user name> <password> 去注册用户：

$pg_md5 --md5auth --username=dbuser db123
$pg_md5 --md5auth --username=repl db123

完成后，在/etc/pgpool-II目录下会产生pool_passwd的文件。

pcp相关设置

我们使用pg_md5命令为dbuser的密码创建md5加密串，然后以“<username: encrypted password>”格式加入到pcp.conf文件最后面。

# pg_md5 -p
Password: (input password)
(paste the md5 encrypted password to pcp.conf)

# vi /etc/pgpool-II/pcp.conf
(add password entry)
user name:md5 encrypted password

Pgpool-II启/停操作

Starting Pgpool-II

在配置完成后，最好重启一下主PostgreSQL数据库。

$ systemctl start pgpool.service

Stopping Pgpool-II

$ systemctl stop pgpool.service

设置PostgreSQL standby服务器

pcp_recovery_node -h 172.16.36.137 -p 9898 -U dbuser -n 1

# psql -h 172.16.36.13 -p 9999 -U dbuser mydb

postgres=> show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | dbsrv1  | 5432 | up     | 0.500000  | primary | 0          | true              | 0
 1       | dbsrv2  | 5432 | up     | 0.500000  | standby | 0          | false             | 0

至此，PostgreSQL 10 的使用Pgpool-II做集群的设置已经完整结束。下一期讲述使用PGPool-II自带的watchdog做PGpool-II的HA。

转载于:https://my.oschina.net/heiky/blog/1588143

chunhongbi5635

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
postgresql 10 使用PGPOOL-II做集群

DBA不可不知的操作系统内核参数修改/etc/sysctl.conf# vi /etc/sysctl.conf# add by digoal.zhoufs.aio-max-nr = 1048576fs.file-max = 76724600kernel.core_pattern=...
复制链接

扫一扫