0

postgresql高可用测试系列之patroni etcd haproxy keepalived 离线部署(二) -利来国际app

张玉龙 2021-10-30
260


说明:本系列文章仅用于共享我的学习成果,未经过生产系统考验,对于知识点和一些组件的使用会不定时更新,仅供参考,如有错误的地方,欢迎留言共同学习。

本高可用系列测试不说理论知识,如有需要自行百度,因生产环境大多数是内网环境,无法连接互联网,为模拟生产环境安装,postgresql高可用测试均采用离线部署

所需软件包均以打包上传百度网盘,如有需要自行下载: 提取码:n9w2 文件名:postgresql_ha.tar.gz

第一章: 介绍测试环境
第二章: postgresql replication 部署
第三章: etcd 部署和管理
第四章: patroni 部署和管理
第五章: haproxy keepalived 部署和管理
第六章: 高可用模拟故障测试用例
第七章: prometheus grafana 监控部署
第八章: 高可用管理

1. 所有节点关闭防火墙、networkmanager和selinux,如果需要启用防火墙,建议待配置完成后再启用,放开第一章提到的端口

systemctl stop firewalld.service systemctl disable firewalld.service systemctl stop networkmanager.service systemctl disable networkmanager.service sed -i "s/selinux=enforcing/selinux=disabled/g" /etc/selinux/config setenforce 0

2. 所有节点修改主机名

hostnamectl set-hostname pgtest1

3. 所有节点修改主机时间,确保节点间时间和时区同步,有条件的同步时间服务器

timedatectl timedatectl list-timezones timedatectl set-timezone asia/shanghai date -s "20211001 00:00:00" ntpdate time.windows.com && hwclock -w

4. 所有节点安装 postgresql 所需要的软件包

# 挂载操作系统镜像包 mkdir /media/cdrom mount /dev/cdrom /media/cdrom # 配置yum mkdir /etc/yum.repos.d/bak mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak cat >> /etc/yum.repos.d/enmo.repo << eof [server] name=server baseurl=file:///media/cdrom enabled=yes gpgcheck=0 eof # 执行yum安装 yum -y install wget flex libselinux-devel readline-devel zlib zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python python-devel tcl-devel systemd-devel pcre-devel gcc gcc-c make tree psmisc yum -y groupinstall "development tools"

5. 所有节点配置/etc/hosts解析文件

cat >> /etc/hosts << eof 192.168.58.10 pgtest1 192.168.58.11 pgtest2 192.168.58.12 pgtest3 eof

6. 所有节点创建用户和组

groupadd -g 5432 postgres useradd -u 5432 -g postgres postgres echo postgres | passwd --stdin postgres

7. 所有节点创建目录

mkdir -p /enmo/soft # 存放软件包 mkdir -p /enmo/app/pg13/13.3 # pg_home mkdir /enmo/pgdata # pg_data mkdir /enmo/pgwal # 存放wal文件 mkdir /enmo/pgarch # 存放wal归档文件 mkdir /enmo/app/pglog # 存放postgresql的软件日志文件 chown -r postgres:postgres /enmo chmod 0700 /enmo/pgdata /enmo/pgwal /enmo/pgarch # 创建目录软连接,方便日后数据库软件升级 ln -s /enmo/app/pg13 /enmo/app/pgsql

8. 所有节点配置系统内核参数

cat >> /etc/sysctl.conf << eof #for postgres db 13.3 kernel.shmall = 966327 # expr `free |grep mem|awk '{print $2 *1024}'` / `getconf page_size` kernel.shmmax = 3958075392 # free |grep mem|awk '{print $2 *1024}' kernel.shmmni = 4096 kernel.sem = 50100 64128000 50100 1280 fs.file-max = 76724200 net.ipv4.ip_local_port_range = 9000 65000 net.core.rmem_default = 1048576 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_max_syn_backlog = 4096 net.core.netdev_max_backlog = 10000 vm.overcommit_memory = 0 fs.aio-max-nr = 40960000 net.ipv4.tcp_timestamps = 0 vm.dirty_ratio=20 vm.dirty_background_ratio=3 vm.dirty_writeback_centisecs=100 vm.dirty_expire_centisecs=500 vm.swappiness=10 vm.min_free_kbytes=524288 eof

9. 所有节点配置资源限制

cat >> /etc/security/limits.conf << eof #for postgres db 13.3 * soft nofile 1048576 * hard nofile 1048576 * soft nproc unlimited * hard nproc unlimited * soft core unlimited * hard core unlimited * soft memlock unlimited * hard memlock unlimited eof cat >> /etc/pam.d/login << eof #for postgres db 13.3 session required pam_limits.so eof

10. 所有节点配置环境变量

cat >> /etc/profile << eof #for postgres db 13.3 export lang=en_us.utf8 export pghome=/enmo/app/pgsql/13.3/ export pguser=postgres export pgport=5432 export pgdata=/enmo/pgdata export path=\$pghome/bin:\$path:\$home/bin export ld_library_path=\$pghome/lib:\$ld_library_path eof source /etc/profile # 使环境变量生效

11. 所有节点解压postgresql安装包,进行源码编译安装

postgresql的官方网站下载:

# 解压安装包 cd /enmo/soft [[email protected] soft]# tar -xvf postgresql_ha.tar.gz [[email protected] soft]# tar -xzvf postgresql-13.3.tar.gz # 编译安装 cd /enmo/soft/postgresql-13.3 ./configure --prefix=/enmo/app/pgsql/13.3 # --with-pgport=6000 make -j 8 && make install # 安装工具集 cd /enmo/soft/postgresql-13.3/contrib make -j 8 && make install # 查询版本,确认安装成功 postgres --version # postgres (postgresql) 13.3

12. 主节点初始化数据库

su - postgres $ initdb --pgdata=/enmo/pgdata --waldir=/enmo/pgwal --encoding=utf8 --allow-group-access --data-checksums --username=postgres --pwprompt --wal-segsize=32 # pg11起,initdb设置wal段的大小 --wal-segsize=32

13. 主节点配置数据库参数

[[email protected] ~]$ vi $pgdata/postgresql.auto.conf log_destination='stderr' logging_collector=on log_directory = '/enmo/app/pglog' log_filename='postgresql-%y-%m-%d.log' log_duration=on log_error_verbosity = default log_line_prefix = '%m' log_statement = 'all' log_file_mode=0600 log_truncate_on_rotation=on log_rotation_age=1d log_rotation_size=0 log_checkpoints=on log_lock_waits=on log_min_duration_statement=500ms log_min_messages=warning idle_in_transaction_session_timeout=300000 autovacuum = 'on' autovacuum_max_workers = 3 full_page_writes = 'on' log_autovacuum_min_duration = -1 seq_page_cost = 1 # https://pgtune.leopard.in.ua/#/ superuser_reserved_connections = 10 shared_buffers = 1gb effective_cache_size = 3gb maintenance_work_mem = 256mb checkpoint_completion_target = 0.9 wal_buffers = 16mb default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 1048kb min_wal_size = 2gb max_wal_size = 8gb max_parallel_workers_per_gather = 1 max_parallel_workers = 2 max_parallel_maintenance_workers = 1 # 以下参数在 patroni 中设置,后面配置 patroni 时,可以将以下参数删除 max_connections = 3000 superuser_reserved_connections = 100 max_locks_per_transaction = 64 max_worker_processes = 8 max_prepared_transactions = 0 wal_level = 'logical' wal_log_hints = 'on' track_commit_timestamp = 'off' max_wal_senders = 10 max_replication_slots = 10 wal_keep_size = '4096mb' # wal_keep_size = wal_keep_segments * wal_segment_size = 128 * 32mb hot_standby = 'on' listen_addresses = '*' port = 5432 cluster_name = 'pg_cluster' archive_mode = 'on' archive_command = 'cp %p /enmo/pgarch/%f'

14. 主节点配置postgresql的访问策略文件

vi $pgdata/pg_hba.conf #修改为如下: host all all 192.168.58.0/24 md5

15. 主节点创建postgresql服务,启动数据库

[[email protected] ~]# vi /usr/lib/systemd/system/postgres-13.service [unit] description=postgresql 13 database server after=syslog.target network.target [service] type=forking timeoutsec=120 user=postgres environment="pghome=/enmo/app/pgsql/13.3" environment="pgdata=/enmo/pgdata" environment="pgport=5432" environment="ld_library_path=/enmo/app/pgsql/13.3/lib:/usr/local/lib:/usr/local/lib64:/usr/lib64" execstart=/bin/bash -c '${pghome}/bin/pg_ctl start -w -d ${pgdata} -l /enmo/app/pglog/startup.log' execstop=/bin/bash -c '${pghome}/bin/pg_ctl stop -m fast -w -d ${pgdata}' execreload=/bin/bash -c '${pghome}/bin/pg_ctl reload -d ${pgdata}' [install] wantedby=multi-user.target [[email protected] ~]# systemctl daemon-reload [[email protected] ~]# systemctl enable postgres-13.service [[email protected] ~]# systemctl start postgres-13.service # 确认启动成功 [[email protected] ~]# netstat -nltp|grep 5432

16. 主节点安装 pg_stat_statements

# 编译安装,在11小节处已安装工具集,此处可不用操作 [[email protected] ~]# cd /enmo/soft/postgresql-13.3/contrib/pg_stat_statements [[email protected] pg_stat_statements]# make && make install # 配置参数 [[email protected] ~]$ vi $pgdata/postgresql.auto.conf # pg_stat_statements shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 1000 pg_stat_statements.track = top pg_stat_statements.track_utility = true pg_stat_statements.save = true # 重启postgres-13服务 [[email protected] ~]# systemctl restart postgres-13.service # 进入 postgresql 数据库安装扩展插件 [[email protected] ~]# psql postgres=# create extension pg_stat_statements; # 确认安装成功 postgres=# select * from pg_stat_statements;

17. 部署 replication 同步两个备库

# 主库上创建用于流复制的用户 postgres=# create user replica with replication encrypted password 'replica'; # 配置主库允许接受流复制的连接 [[email protected] ~]# vi $pgdata/pg_hba.conf host replication replica 192.168.58.0/24 md5 [[email protected] ~]# systemctl reload postgres-13.service # 建议所有节点配置密码文件 [[email protected] ~]# su - postgres [[email protected] ~]$ cat >> ~/.pgpass << eof # hostname:port:database:username:password 192.168.58.10:5432:replication:replica:replica 192.168.58.11:5432:replication:replica:replica 192.168.58.12:5432:replication:replica:replica eof [[email protected] ~]$ chmod 0600 .pgpass # 所有备库节点执行pg_basebackup命令初始化数据库 [[email protected] ~]# su - postgres [[email protected] ~]$ pg_basebackup -h 192.168.58.10 -p 5432 -u replica -d $pgdata -fp -p -x stream -r -v -l replica_20211016 # 所有备库节点创建postgresql服务postgres-13.service,同主库一样,启动所有备库 [[email protected] ~]# systemctl daemon-reload [[email protected] ~]# systemctl enable postgres-13.service [[email protected] ~]# systemctl start postgres-13.service # 确认启动成功 [[email protected] ~]# netstat -nltp|grep 5432

18. 测试主备同步

# 在主库上查询主备同步状态 postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication; pid | state | client_addr | sync_priority | sync_state ------- ----------- --------------- --------------- ------------ 22100 | streaming | 192.168.58.11 | 0 | async 22101 | streaming | 192.168.58.12 | 0 | async # 主库创建测试表,插入数据 postgres=# create table test_1 (id int4,create_time timestamp(0) without time zone); postgres=# insert into test_1 values (1,now()); # 备库查询测试表,确认是否同步成功 postgres=# select * from test_1;

至此,postgresql replication 部署完成。

19. 补充:postgresql 主备机的判断

# 1.通过pg_controldata输出: [[email protected] ~]# pg_controldata database cluster state: in production # 主库 database cluster state: in archive recovery # 备库 # 2.通过数据字典表pg_stat_replication,主机表中能查到记录,备机表中无记录 postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication; pid | state | client_addr | sync_priority | sync_state ------- ----------- --------------- --------------- ------------ 22100 | streaming | 192.168.58.11 | 0 | async 22101 | streaming | 192.168.58.12 | 0 | async # 3.通过wal进程查看,显示 walsender 的是主机,显示 walreceiver 的是备机 [[email protected] ~]# ps -ef |grep wal postgres 11047 11042 0 00:19 ? 00:00:01 postgres: pg_cluster: walwriter postgres 12686 11042 0 17:17 ? 00:00:00 postgres: pg_cluster: walsender replica 192.168.58.12(31644) streaming 0/18000250 postgres 13166 11042 0 17:54 ? 00:00:00 postgres: pg_cluster: walsender replica 192.168.58.11(44964) streaming 0/18000250 [[email protected] ~]# ps -ef |grep wal postgres 11494 11488 0 17:54 ? 00:00:00 postgres: pg_cluster: walreceiver streaming 0/18000250 # 4. 通过自带函数判断,select pg_is_in_recovery(); postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f # 主库 t # 备库
最后修改时间:2021-11-04 02:45:54
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论