pg+repmgr安装部署

原创苏昭然 2023-11-19

459

一、操作系统环境准备

说明

操作系统版本：CentOS Linux release 7.6.1810 (Core)

PG版本：postgresql-14.2

REMPGR版本：repmgr-5.3.1

节点	IP
1	192.168.10.20
2	192.168.10.21
3	192.168.10.22

1、创建目录并上传安装包

mkdir -p /opt/software

2、配置本地yum源

mkdir -p /mnt/iso

cd /mnt/iso

mount -o loop /opt/CentOS-7-x86_64-DVD-2003.iso /mnt/iso/

vi /etc/yum.repos.d/local-yum.repo

[base-local]

name=local-yum

#步骤2中挂载镜像创建的目录

baseurl=file:///mnt/iso

#启动yum源： 1-启用 0-不启用

enabled=1

#安全检测： 1-开启 0-不开启

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

:wq

yum clean all

yum makecache

yum list

3、安装依赖包

yum install -y perl-ExtUtils-Embed readline readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake gcc* bison gettext gettext-devel perl perl-devel tcl-devel flex libcurl-devel

4、创建用户

groupadd -g 66666 postgres

useradd -u 66666 suzhaoran-g postgres

echo "pg123" | passwd --stdin suzhaoran

5、配置ssh互信

su - suzhaoran

ssh-keygen -t rsa

ssh-copy-id -i ~/.ssh/id_rsa.pub suzhaoran@192.168.10.20

ssh-copy-id -i ~/.ssh/id_rsa.pub suzhaoran@192.168.10.21

ssh-copy-id -i ~/.ssh/id_rsa.pub suzhaoran@192.168.10.22

6、关闭防火墙

systemctl stop firewalld

systemctl disable firewalld

7、关闭selinux

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

setenforce 0

8、设置内核参数

cp /etc/sysctl.conf /etc/sysctl.confbak

cat >> /etc/sysctl.conf <<EOF

#add by suzhaoran

kernel.shmmax = 68719476736

kernel.shmall = 4294967296

kernel.msgmax = 524288

kernel.msgmnb = 5242880

kernel.msgmni=2048

kernel.sem = 50100 64128000 50100 1280

fs.file-max = 7672460

net.ipv4.ip_local_port_range = 9000 65000

net.core.rmem_default = 1048576

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

vm.swappiness=10

vm.overcommit_memory=2

vm.overcommit_ratio=85

vm.dirty_background_ratio=1

vm.dirty_ratio=2

EOF

sysctl -p

9、Limits设置

cat >> /etc/security/limits.conf << "EOF"

#add by suzhaoran

* soft nofile 131072

* hard nofile 131072

* soft nproc 131072

* hard nproc 131072

* soft core unlimited

* hard core unlimited

* soft memlock 50000000

* hard memlock 50000000

EOF

10、修改磁盘预读

echo 8192 > /sys/class/block/sda/queue/read_ahead_kb

11、大页设置

su - suzhaoran

head -1 $PGDATA/postmaster.pid

31277

pmap 31277| awk '/rw-s/ && /zero/ {print $2}'

187480K

grep ^Hugepagesize /proc/meminfo

Hugepagesize: 2048 kB

#187480/2048大约是91，因此在这个示例中你至少需要3170个大页面，我们可以设置100

exit

sysctl -w vm.nr_hugepages=100

二、PG安装部署

1、pg编译（带ssl，节点2，节点3仅进行编译不进行初始化）

su – suzhaoran

mkdir -p /home/suzhaoran/{data,soft}

cd /opt/software

tar -zxvf postgresql-14.2.tar.gz

cd postgresql-14.2

./configure --prefix=/home/suzhaoran/soft --with-openssl

make world -j8 && make install-world

2、配置环境变量

cat >> ~/.bash_profile << "EOF"

export LANG=en_US.UTF-8

export PGDATA=/home/suzhaoran/data

export PGHOME=/home/suzhaoran/soft

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH

export PATH=$PGHOME/bin:$PATH:.

export PGUSER=suzhaoran

export PGPORT=5666

EOF

3、初始化（仅节点1）

/home/suzhaoran/soft/bin/initdb -D /home/suzhaoran/data -E UTF8 --locale=en_US.utf8 -U suzhaoran

4、配置postgresql.conf

cat >> /home/suzhaoran/data/postgresql.conf <<"EOF"

listen_addresses = '*'

port=5666

ssl = on

ssl_cert_file = 'server.crt'

ssl_key_file = 'server.key'

shared_preload_libraries='passwordcheck'

log_destination='csvlog'

logging_collector=on

log_directory='/home/suzhaoran/pglog'

log_rotation_age='1d'

log_rotation_size=0

log_truncate_on_rotation=on

log_line_prefix='%m %u %d %p'

full_page_writes=on

password_encryption='scram-sha-256'

max_connections=1000

huge_pages = try

EOF

5、配置ssl单向认证并启动

cd $PGDATA

openssl req -new -x509 -nodes -text -subj '/CN=postgres' -out server.crt -keyout server.key

chmod 600 server.key

echo "*:*:*:*:1qaz@WSX" > ~/.pgpass

chmod 600 ~/.pgpass

pg_ctl start

psql postgres

\password suzhaoran #修改密码为 1qaz@WSX

create extension sslinfo;

\dx

exit

pg_ctl restart

6、配置pg_hba.conf

cd $PGDATA

cat >> /home/suzhaoran/data/pg_hba.conf <<"EOF"

host postgres suzhaoran 0.0.0.0/0 reject

host all all 0.0.0.0/0 scram-sha-256

host all suzhaoran 0.0.0.0/0 scram-sha-256

hostssl all suzhaoran 0.0.0.0/0 scram-sha-256

hostnossl all suzhaoran 0.0.0.0/0 scram-sha-256

EOF

cat /home/suzhaoran/data/pg_hba.conf

pg_ctl reload

psql -h 127.0.0.1 postgres

select now();

三、repmgr安装

1、各节点编译安装repmgr

su - suzhaoran

cd /opt/software

tar -zxvf repmgr-5.3.1.tar.gz

cd repmgr-5.3.1

./configure

make -j4 && make install

which repmgr

2、主节点创建用户

psql postgres

create user repmgr superuser replication connection limit 10 password '1qaz@WSX' ;

create database repmgr owner repmgr;

\c repmgr

create extension repmgr;

psql -Urepmgr -h192.168.10.20 repmgr

3、主节点pg_hba.conf配置

cat >> /home/suzhaoran/data/pg_hba.conf << "EOF"

host repmgr repmgr 0.0.0.0/0 trust

host replication repmgr 0.0.0.0/0 trust

EOF

4、主节点postgresql.conf配置

vi /home/suzhaoran/data/postgresql.conf

shared_preload_libraries='passwordcheck,repmgr'

max_wal_senders = 10

max_replication_slots = 10

wal_level = replica

archive_mode=on

archive_command='/bin/true'

hot_standby=on

wal_keep_size=1GB

pg_ctl -D /home/suzhaoran/data -l logfile restart

5、各节点配置

主节点：

mkdir /home/suzhaoran/conf/

cat > /home/suzhaoran/conf/repmgr.conf << "EOF"

node_id=1

node_name='192.168.10.20'

conninfo='host=192.168.10.20 port=5666 user=repmgr dbname=repmgr connect_timeout=2'

data_directory='/home/suzhaoran/data'

log_file='/home/suzhaoran/conf/repmgr.log'

pg_bindir='/home/suzhaoran/soft/bin'

EOF

cat /home/suzhaoran/conf/repmgr.conf

备1：

mkdir /home/suzhaoran/conf/

cat > /home/suzhaoran/conf/repmgr.conf << "EOF"

node_id=2

node_name='192.168.10.21'

conninfo='host=192.168.10.21 port=5666 user=repmgr dbname=repmgr connect_timeout=2'

data_directory='/home/suzhaoran/data'

log_file='/home/suzhaoran/conf/repmgr.log'

pg_bindir='/home/suzhaoran/soft/bin'

EOF

cat /home/suzhaoran/conf/repmgr.conf

备2：

mkdir /home/suzhaoran/conf/

cat > /home/suzhaoran/conf/repmgr.conf << "EOF"

node_id=3

node_name='192.168.10.22'

conninfo='host=192.168.10.22 port=5666 user=repmgr dbname=repmgr connect_timeout=2'

data_directory='/home/suzhaoran/data'

log_file='/home/suzhaoran/conf/repmgr.log'

pg_bindir='/home/suzhaoran/soft/bin'

EOF

cat /home/suzhaoran/conf/repmgr.conf

6、注册主节点，主节点状态检查

repmgr -f /home/suzhaoran/conf/repmgr.conf primary register

repmgr -f /home/suzhaoran/conf/repmgr.conf cluster show

psql -Urepmgr -d repmgr

select * from nodes;

7、备节点注册

备1：

echo "*:*:*:*:1qaz@WSX" > ~/.pgpass

chmod 600 ~/.pgpass

repmgr -h 192.168.10.20 -U repmgr -d repmgr -f /home/suzhaoran/conf/repmgr.conf standby clone --force

pg_ctl -D /home/suzhaoran/data start

repmgr -f /home/suzhaoran/conf/repmgr.conf standby register

repmgr -f /home/suzhaoran/conf/repmgr.conf cluster show

备2：

echo "*:*:*:*:1qaz@WSX" > ~/.pgpass

chmod 600 ~/.pgpass

repmgr -h 192.168.10.20 -U repmgr -d repmgr -f /home/suzhaoran/conf/repmgr.conf standby clone --force

pg_ctl -D /home/suzhaoran/data start

repmgr -f /home/suzhaoran/conf/repmgr.conf standby register

repmgr -f /home/suzhaoran/conf/repmgr.conf cluster show

8、switchover切换测试

备1：

repmgr -f /home/suzhaoran/conf/repmgr.conf cluster show

repmgr -f /home/suzhaoran/conf/repmgr.conf standby switchover --dry-run

repmgr -f /home/suzhaoran/conf/repmgr.conf standby switchover --siblings-follow

repmgr -f /home/suzhaoran/conf/repmgr.conf cluster show

原主：

repmgr -f /home/suzhaoran/conf/repmgr.conf cluster show

repmgr -f /home/suzhaoran/conf/repmgr.conf standby switchover --dry-run

repmgr -f /home/suzhaoran/conf/repmgr.conf standby switchover --siblings-follow

repmgr -f /home/suzhaoran/conf/repmgr.conf cluster show

9、各节点配置自动切换设置

cat >> /home/suzhaoran/conf/repmgr.conf << "EOF"

monitoring_history=yes

monitor_interval_secs=5

failover=automatic

reconnect_attempts=6

reconnect_interval=5

promote_command='/home/suzhaoran/soft/bin/repmgr standby promote -f /home/suzhaoran/conf/repmgr.conf --log-to-file'

follow_command='/home/suzhaoran/soft/bin/repmgr standby follow -f /home/suzhaoran/conf/repmgr.conf --log-to-file --upstream-node-id=%n'-file --upstream-node-id=%n'

EOF