2020-01-03 KK日记，第一次进行postgresql 11.5+pgpool 安装
一、安装规划
- 安装单实例
- 配置主从
- 安装pgpool -ii
二、单实例安装
2.1 操作系统
os: centos 7.6
cpu: 4 core
memory:32g
disk: 60g
192.168.0.1
192.168.0.2
2.2 postgresql 软件准备
源码： postgresql 11.5
2.3 安装前检查
使用 yum install 安装以下包
make 2.5+
gcc
readline readline-devel
zlib
zlib-devel
perl 5.8.3
perl-devel
python
tcl 8.4
2.4 修改内核参数、增加postgres用户、修改/dev/shm 大小
2.4.1 修改内核参数
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 123695058124
kernel.shmall = 30198988
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 41943040
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
2.4.2 增加用户
useradd postgres
passwd postgres
2.4.3 修改/dev/shm
fuser -km /dev/shm
mount -t tmpfs -o size=10240m tmpfs /dev/shm
vi /etc/fstab
tmpfs /dev/shm tmpfs defaults,size=10240m 0 0
备注：不修改这个，后面在大并发查询时，可能会出现下面大错误
CAUSE BY:could not resize shared memory segment "/PostgreSQL.644318876" to 196864 bytes: 设备上没有空间
2.6 修改limit配置
/etc/security/limits.conf
postgres soft nproc 65536
postgres hard nproc 65536
postgres soft nofile 2048
postgres hard nofile 65536
postgres soft stack 10240
postgres hard stack 10240
2.7 源码安装
scp postgresql-11.5.tar.gz aaa@qq.com:/tmppostgres login the 192.168.0.1cd /tmptar zxvf postgresql-11.5.tar.gz./configure make su - make install mkdir /work/pgsql/data chown postgres /work/pgsql/data su - postgres #初始化数据库
/usr/local/pgsql/bin/initdb -D /work/pgsql/data |
|
#启动实例/usr/local/pgsql/bin/pg_ctl -D /work/pgsql/data -l logfile start |
|
#创建test db/usr/local/pgsql/bin/createdb qqt #连接test/usr/local/pgsql/bin/psql qqt
2.8 连接参数配置
控制那些ip可以访问pgsql
/work/pgsql/data/pg_hpa.conf
在不进行配置时，会出现下面的错误
#修改postgresql.conf 的参数
listen_addresses = '*'
port=5432
#修改pg_hba.conf
host all all 0.0.0.0/0 password
重启pg
pg_ctl restart -D /work/pgsql/data -m smart
尝试连接，成功。
2.8.1 dba与开发员连接pg的工具与方式
使用工具navicat or pgadmin
2.8.2 java 连接pg 的方式
下载jdbc
JDBC连接数据库，首先应该获取的是Java与PostgreSQL连接的jar包，驱动的下载地址：https://jdbc.postgresql.org/download.html
示例代码
|
|
命令行登陆
psql -U username -d database_name -h host -W
2.9 创建pg用户
CREATE USERname[ [ WITH ]option[ ... ] ] whereoptioncan be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMITconnlimit| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLErole_name[, ...] | IN GROUProle_name[, ...] | ROLErole_name[, ...] | ADMINrole_name[, ...] | USERrole_name[, ...] | SYSIDuid
例子：
create user hqq with password 'password';
alter user hqq with superuser;
2.10 授权
grant select on book to hqq;
2.11 参数查看
命令: show name or show all
在主机层面，通psql登陆的如果忘记name 具体名字可以通过【tab】键自动补全。
对应的表是pg_settings
关联系统表
pg_tables pg_roles pg_user pg_rules pg_indexes
3. 主从架构配置
3.1 从节点安装
从节点ip：192.168.0.2
过程参考主节点，但不要执行initdb 这个命令。
3.2 主节点上配置
在postgres.conf修改以下参数：
wal_level=replica
archive_mode=on
archive_command='/bin/date'
max_wal_senders=10
wal_keep_segments=512
hot_standby=on
3.3 创建复制用户
CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD ’password ’;
配置pg_hba.conf，添加以下数据
host replication repuser 192.168.0.2/32 md5
3.4 复制数据到从库
在从库上os执行如下命令
[[email protected]] pg_basebackup - D $PGDATA -Fp -Xs -v -P -h 192.168.0.1 -p 5432 -U repuser
3.5 在从库上配置recovery.conf
cp recovery.conf.sample $PGDATA
cd $PGDATA
mv recovery.conf.sample recovery.conf
vi recovery.conf
recovery_target_timeline = 'latest'
standby mode = on
primary_conninfo = 'host=192.168.0.1 port=5432 user=repuser'
3.6 在从库上配置复制用户的登陆信息
touch .pgpass
chmod 0600 .pgpass
vi .pgpass
192.168.0.1:5432:replication:repuser:password
192.168.0.2:5432:replication:repuser:password
3.7 验证主从复制
ps -ef | grep wal
看到以下信息，就是主库，并且复制已经开启
postgres 6101 6097 0 16:38 ? 00:00:00 postgres: walwriter
postgres 6108 6097 0 16:38 ? 00:00:00 postgres: walsender repuser 10.87.140.53(41750) streaming 0/80004A8
看到以下信息，就是从库，并且复制已经开启
postgres 32211 22668 0 16:38 ? 00:00:00 postgres: walreceiver streaming 0/80004A8
数据库视图：
select * from pg_stat_replication;
pg命令：
pg_controldata | grep cluster
Database cluster state: in production #主库
Database cluster state: in archive recovery #备库
3.8 主从切换
3.8.1 纯手工切换
- 关闭主库
pg_ctl stop -m fast - 执行备库切换，在备库端执行
修订pg_hba.conf 添加以下内容：host replication repuser 192.168.0.1/32 md5
执行切换：
pg_ctl promote -D $PGDATA - 执行主库切换
3.1 在$PGDATA 增加recovery.confvi recovery.conf
recovery_target_timeline = 'latest'
standby mode = on
primary_conninfo = 'host=192.168.0.2 port=5432 user=repuser'
3.2 增加 .pgpasstouch .pgpass
chmod 0600 .pgpass
vi .pgpass
192.168.0.1:5432:replication:repuser:password
192.168.0.2:5432:replication:repuser:password
-
启动老主库
pg_ctl start -D $PGDATA -l $PGDATA/pg_log/
ps： 注意决定不能在主库在启动状态下，把从库切换成主库。
4. pgpool-ii 安装
4.1 安装规划
- 采用源码方式安装
- 安装用户与pg 使用相同用户 postgres
- 安装路径默认安装
- 安装组件：pgpool 、pgpool_recovery、pg_regclass （pg 9.4后不需要）、insert_lock（不采用native native replication mode不需要）
- pgpool 安装在现有pg 主从节点上。
4.2 开始安装
----------使用postgres 在主备节点上进行安装-------------------
上传源码，并解压:
tar zxvf pgpool-II-4.0.2.tar.gz
编译并安装
./configure
make
root> make install
安装完成后，在 /usr/local/bin 路径下看到pgpool的东西
安装完毕
4.2.2 安装pgpool_recovery插件
$ cd pgpool-II-4.1.0/src/sql/pgpool-recovery
$ make
$ make install (用root安装)psql -f pgpool-recovery.sql template1vi postgres.conf
pgpool.pg_ctl = '/usr/local/pgsql/bin/pg_ctl'pg_ctl reload -D $PGDATA
4.3 配置
4.3.1 免密互访配置
*在pgdbsvr01 和pgdbsvr02 上做同样修改
vi /etc/hosts
192.168.0.1 pgdbsvr01
192.168.0.2 pgdbsvr02
ssh-******
ssh-copy-id aaa@qq.com # 在pgdbsvr02上执行 ssh-copy-id aaa@qq.com
4.3.2 授权
1）如果需要非root用户启动Pgpool时能启动虚拟IP，需要配置该权限
chmod u+s /bin/ping
chmod u+s /sbin/ifup
chmod u+s /sbin/ifdown
chmod u+s /sbin/ip
chmod u+s /sbin/ifconfig
chmod u+s /sbin/arping
4.3.3 配置pool_hba.conf
cd /usr/local/etc
cp pool_hba.conf
chown postgres:postgres pool_hba.conf
host replication repuser 192.168.0.1/32 md5
host replication repuser 192.168.0.2/32 md5
host all all 0.0.0.0/0 md5
4.3.4 配置pgpool.conf
cp /usr/local/etc/pgpool.conf.sample-stream pgpool.conf
参数配置如下：
|
序号 |
参数名字 |
解释 |
pgdbsvr01配置值 |
pgdbsvr02配置值 |
|---|---|---|---|---|
| 1 | listen_addresses | 配置pgpool的监听地址 | * | * |
| 2 | port | pgpool的监听端口 | 9999 | 9999 |
| 3 | backend_hostname0 |
配置后端 PostgreSQL 节点 0 的主机名或 IP |
pgdbsvr01 | pgdbsvr01 |
| 4 | backend_port0 |
配置 PostgreSQL 节点 0 的端口 。 |
5432 | 5432 |
| backend_data_directory0 |
配置 PostgreSQL 节点 0 的数据目录 。 |
/work/pgsql/data | /work/pgsql/data | |
| backend_flag0 |
设置后端数据库节点 的行为，默认为 ALLOW_TO_FAILOVER，表 示允许故障转移。 |
ALLOW_TO_FAILOVER | ALLOW_TO_FAILOVER | |
| backend_hostname1 |
配置后端 PostgreSQL 节点 1的主机名或 IP |
pgdbsvr02 | pgdbsvr02 | |
| backend_port1 | 配置 PostgreSQL 节点 1 的端口 。 | 5432 | 5432 | |
| backend_data_directory1 | 配置 PostgreSQL 节点 1的数据目录 。 | /work/pgsql/data | /work/pgsql/data | |
| backend_flag1 |
设置后端数据库节点1 的行为，默认为 ALLOW_TO_FAILOVER，表 示允许故障转移。 |
ALLOW_TO_FAILOVER | ALLOW_TO_FAILOVER | |
| enable_pool_ha |
表示 pgpool 启用 pool_hba.conf |
on | on | |
| pool_passwd |
设置 MD5 认证的密码文件， 默认为 pool_passwd |
pool_passwd | pool_passwd | |
| log_destination |
pgpool 支持两种类型日志输出， stdeη和 syslog， 这里设 置成 syslog, /var/log/message 系统日志里会显示 pgpool 日志。 |
syslog | syslog | |
|
pid_file name |
pgpool 进程的 PID 文件 |
/usr/local/etc | /usr/local/etc | |
|
load balance mode |
表示是否开启 pgpool 的负载均衡，如果开启此参数， 会被 pgpool 分发到流复制备库上 |
on | on | |
|
master slave mode |
是否启用主备模式，默认为 off，设置成 on。 |
on | on | |
| master_slave_sub_mode |
设置主备模式，可选项为 slony或 stream, slony表示使 用 slony 复制模式， stream 表示使用 PostgreSQL 内置的流复制模式，这里设置成 stream 。 |
stream | stream | |
| sr_check_period |
流复制延时检测的周期，默认为 10秒 |
10 | 10 | |
| sr_check_user |
流复制延时检测使用的数据库用户 |
repuser | repuser | |
| sr_check_password | password | password | ||
| sr_check_database |
流复制延时检测时连接的数据库 |
postgres | postgres | |
| delay_threshold |
当流复制备库延迟大于设置的 WAL 字节数时， pgpool 不会将 SELECT 语句分发到备库 |
10000000 | 10000000 | |
| health_check_period | 5 | 5 | ||
| health_check_timeout | 20 | 20 | ||
| health_check_user | repuser | repuser | ||
| health_check_password | ||||
| health_check_database | postgres | postgres | ||
| health_check_max_retries | 3 | 3 | ||
| health_check_retry_delay | 3 | 3 | ||
| failover_command |
failover_command 表示设置故障转移的脚本，当 pgpool 主备实例或主机者机时， 触发 此脚本进行故障转移，后面四个参数为 pgpool 系统变量， %d表示着机的节点 ID , %P 表 示老的主库节点 ID, %H 表示新主库的主机名， %R 表示新主库的数据目录，后面会贴出 failover stream.sh脚本中的内容。 |
/usr/local/etc/failover_stream.sh %d %P %H %R | /usr/local/etc/failover_stream.sh %d %P %H %R | |
| use_watchdog |
是否启用 watchdog，默认为 off。 |
on | on | |
| wd_hostname |
watchdog所在主机的 IP地址或主机名，和相应 pgpool位于同一主机。 |
pgdbsvr01 | pgdbsvr02 | |
| wd_port |
watchdog 的端口 号，默认为 9000。 |
9000 | 9000 | |
| wd_priority |
设置 watchdog 的优先级 ，当 pgpool 主节点服务通断后，优先级越高 的 watchdog 将被选择成 pgpool 主节点 ，实验环境为一主一备，只有两个 pgpool 节 点，此参数无影响 。 |
1 | 1 | |
|
delegate_IP |
192.168.0.3 | 192.168.0.3 | ||
|
if_cmd_path |
设置启动和关闭 VIP命令的路径。 |
/sbin | /sbin | |
| if_up_cmd |
设 置启动 VIP 的命令，使用 ip addr add 命令启动一个 VIP .[默认生成] |
ip addr add $_IP_$/24 dev eth0 label eth0:0 |
ip addr add $_IP_$/24 dev eth0 label eth0:0 |
|
| if_down_cmd | 设 置启动 VIP 的命令，使用 ip addr add 命令启动一个 VIP .[默认生成] |
ip addr del $_IP_$/24 dev eth0 |
ip addr del $_IP_$/24 dev eth0 |
|
|
heartbeat_destination0 |
设置远程 pgpool 节点主机名或 IP，本地的 watchdog 心跳发 往远程pgpool主机， heartbeat_ct巳stination后的编号从0开始 |
pgdbsvr02 | pgdbsvr01 | |
| heartbeat_destination_port0 |
设置远程 pgpool 节点的端 口号， 默认为 9694。 |
9694 | 9694 | |
| heartbeat_device0 |
本地 pgpool 发送 watchdog 心跳的网络设备别名 , 可以通过ip a名令获得 |
eth0 | eth0 | |
| wd_life_point | 3 | 3 | ||
| wd_lifecheck_query | select 1 | select 1 | ||
| wd_lifecheck_dbname | postgres | postgres | ||
| wd_lifecheck_user | repuser | repuser | ||
| wd_lifecheck_password | ||||
| other_pgpool_hostname0 | pgdbsvr02 | pgdbvr01 | ||
| other_pgpool_port0 | 9999 | 9999 | ||
| other_wd_port0 | 9000 | 9000 | ||
| max_pool |
Number of connection pool caches per connection [需要重启生效] |
4 | 4 | |
|
num_init_children |
Number of concurrent sessions allowed [需要重启生效] pgpool最大允许并发数 |
32 | 3000 |
参考：https://www.pgpool.net/docs/latest/en/html/example-cluster.html
4.3.5 切换脚本
pg_failover
|
|
4.3.6 切换演练
pgdbsvr01上执行：
pg_ctl stop -m fast
顺利完成切换，然后启动pgdbsvr01
cp $PGDATA/recovery.done $PGDATA/recovery.conf ; ls -l $PGDATA/recovery.conf
pg_ctl start -D $PGDATA -l $PGLOG
启动后，通过show pool_nodes 显示：
执行如下命令即可恢复到up：
pcp_attach_node -d -h 192.168.0.1 -p 9898 -U pgpool -w -n 0
pcp_watchdog_info -v -w -U pgpool
pg_rewind --target-pgdata $PGDATA --source-server='host=192.168.0.1 port=5432 user=postgres password=XXX dbname=qqt' -P
#rewind完后，记得要调recovery.done 里面的primary_info 的host ，因为rewind是会把主库的差异数据考过来的。。。所以会把recovery.done也一起考过来。
#备库启动，一定要确保recovery.conf已经存在， 否则建立不起复制，，一旦备库以“主库”模式启动过，就不能再以复制模式启动。
pgpool -f /usr/local/etc/pgpool.confpgpool -n -d > /tmp/pgpool.log 2>&1 &
注意事项
使用pgpool后，会自动负载均衡查询语句的，那么你想知道你连在那个库上，建议使用：SELECT inet_server_addr(), inet_server_port();
查询阻塞sql：
select datname,usename,wait_event_type,wait_event,pg_blocking_pids(pid) as blocked_by,query
from pg_stat_activity
where wait_event_type is not null and wait_event_type not in ('Activity','client');
上一篇： Linux 的目录结构详解和注意事项
下一篇： CentOS 目录结构详解