1.PostgreSQL部署

1.1 安装前准备

关闭防火墙和selinux

systemctl stop firewalld

systemctl disable firewalld

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

 

1.2 创建用户和目录

groupadd pg14

useradd -g pg14 pg14

echo 123| passwd --stdin pg14

 

软件安装路径 /home/pg14/soft

数据目录     /home/pg14/data

su - pg14

mkdir -p /home/pg14/soft

mkdir  -p  /home/pg14/data

 

1.3解压安装包

 

 tar -zxf postgresql-14.6.tar.gz

 

1.4安装依赖包

yum install -y gcc

yum install -y make

yum install -y readline*

yum install -y zlib-devel

yum install -y libxml2-devel

yum install -y libxslt-devel

yum install -y openssl-devel

1.5编译

 ./configure --prefix=/home/pg14/soft --with-pgport=5666 --with-openssl

 

1.6安装

 

make world -j16

make install-world -j16

1.7 配置环境变量

 vi .bashrc  添加

export PGHOME=/home/pg14/soft

export PGDATA=/home/pg14/data

export LD_LIBRARY_PATH=${PGHOME}/lib:${LD_LIBRARY_PATH}

export PATH=${PGHOME}/bin:${PATH}

source .bashrc

 

1.8初始化集簇

  启用checksum

  超级用户是postgres

 initdb -D /home/pg14/data -k -U postgres -W

 

1.9启动数据库

pg_ctl start -D /home/pg14/data -l /tmp/logfile

 

1.2repmgr安装

1.2.1 主机互信配置

./sshusersetup.sh

1.2.2上传repmgr安装包、解压、安装

 

yum  check-update

yum groupinstall "Development Tools" -y

yum install yum-utils openjade docbook-dtds docbook-style-dsssl docbook-style-xsl -y

yum-builddep postgresql96

 

 

tar -zxf repmgr-5.3.3.tar.gz

./configure

make && make install

 

1.2.3创建复制账号

 

postgres=# create user repmgr with superuser password '1qaz@WSX';

CREATE ROLE

postgres=# create database repmgrdb with owner=repmgr;

CREATE DATABASE

postgres=#

 

1.2.4 参数配置

 

postgresql.auto.conf

max_wal_senders=10

max_replication_slots=10

wal_level='logical'

hot_standby=on

archive_mode=on

wal_keep_size = '128'

archive_command='cp %p /home/pg14/arch/%f'

 

pg_hba_conf IPV4 一栏和replication 一栏分别添加下面两行

[pg14@node1 data]$ egrep repmgr $PGDATA/pg_hba.conf

host    repmgrdb        repmgr          192.168.18.0/24         trust

host    replication     repmgr          192.168.18.0/24         trust

 

 

1.2.5创建repmgr扩展

psql -U repmgr -d repmgrdb

repmgrdb=# create extension repmgr;

CREATE EXTENSION

 

1.2.6 repmgr配置文件

 

mkdir -p /home/pg14/conf

cd /home/pg14/conf

 

node1:

vi repmgr.conf

[pg14@node1 conf]$ vi repmgr.conf

#[base]

node_id=1

node_name=node1

conninfo ='host=node1 dbname=repmgrdb user=repmgr connect_timeout=2 password=1qaz@WSX'

data_directory='/home/pg14/data'

#[optional]

#[log]

log_level=info

log_facility=stderr

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

log_status_interval=300

 

node2

#[base]

node_id=2

node_name=node2

conninfo ='host=node2 dbname=repmgrdb user=repmgr connect_timeout=2 password=1qaz@WSX'

data_directory='/home/pg14/data'

#[optional]

#[log]

log_level=info

log_facility=stderr

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

log_status_interval=300

 

1.3 主从数据初始化

1.3.1 主节点注册

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

1.3.2 备库克隆

repmgr -h node1 -U repmgr -d repmgrdb -f /home/pg14/conf/repmgr.conf standby clone

1.3.3从节点注册

repmgr -f ~/conf/repmgr.conf standby register

1.4 故障自动切换配置

1.4.1 配置操作系统服务

vi /usr/lib/systemd/system/postgresql.service

 

[Unit]

Description=PostgreSQL database server

After=network.target

 

[Service]

Type=forking

User=pg14

Group=pg14

Environment=PGPORT=5666

Environment=PGDATA=/home/pg14/data

Environment=PGLOG=/tmp/logfile

OOMScoreAdjust=-1000

ExecStart=/home/pg14/soft/bin/pg_ctl start -D ${PGDATA} -l ${PGLOG}

ExecStop=/home/pg14/soft/bin/pg_ctl stop -D ${PGDATA} -l ${PGLOG}

ExecRestart=/home/pg14/soft/bin/pg_ctl restart -D ${PGDATA} -l ${PGLOG}

ExecReload=/home/pg14/soft/bin/pg_ctl reload -D ${PGDATA} -l ${PGLOG}

TimeoutSec=300

[Install]

WantedBy=multi-user.target

 

1.4.2 数据库账号赋权

vi /etc/sudoers

# Refuse to run if unable to disable echo on the tty.

#

#Defaults   !visiblepw

Defaults    !requiretty

 

## Allow root to run any commands anywhere

root    ALL=(ALL)       ALL

pg14    ALL=(ALL)       NOPASSWD:/bin/systemctl stop postgresql,\

/bin/systemctl start postgresql,\

/bin/systemctl restart postgresql,\

/bin/systemctl reload postgresql

 

1.4.3 PostgreSQL参数添加

vi /home/pg14/conf/repmgr.conf

#[service]

service_start_command='sudo systemctl start postgresql'

service_stop_command='sudo systemctl stop postgresql'

service_restart_command='sudo systemctl restart postgresql'

service_reload_command='sudo systemctl relaod postgresql'

 

1.4.4 repmgr配置文件添加

repmgr 配置文件添加

#[auto-failover]

 

failover=automatic

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

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

 

#[monitor]

monitoring_history=yes

monitor_interval_secs=1

reconnect_attempts=6

reconnect_interval=1

primary_notification_timeout=3

repmgrd_standby_startup_timeout=1

sibling_nodes_disconnect_timeout=1

2.5.5 repmgr 守护进程启动

 

repmgrd -f /home/pg14/conf/repmgr.conf -v -d  -p ~/conf/repmgrd.pid

 

 

2高可用测试

 

手动切换测试

[pg14@node2 data]$ repmgr -f /home/pg14/conf/repmgr.conf standby switchover

NOTICE: executing switchover on node "node2" (ID: 2)

NOTICE: attempting to pause repmgrd on 2 nodes

NOTICE: local node "node2" (ID: 2) will be promoted to primary; current primary "node1" (ID: 1) will be demoted to standby

NOTICE: stopping current primary node "node1" (ID: 1)

NOTICE: issuing CHECKPOINT on node "node1" (ID: 1)

DETAIL: executing server command "sudo systemctl stop postgresql"

INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")

NOTICE: current primary has been cleanly shut down at location 0/15000028

NOTICE: promoting standby to primary

DETAIL: promoting server "node2" (ID: 2) using pg_promote()

NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete

NOTICE: STANDBY PROMOTE successful

DETAIL: server "node2" (ID: 2) was successfully promoted to primary

NOTICE: node "node2" (ID: 2) promoted to primary, node "node1" (ID: 1) demoted to standby

NOTICE: switchover was successful

DETAIL: node "node2" is now primary and node "node1" is attached as standby

NOTICE: STANDBY SWITCHOVER has completed successfully