PostGIS集群离线部署

pg部署

# 外网机器yum下载postgresql的安装包（rpm）

# 解压
tar xvf postgresql10-rhel7-rpm.tgz

# 切换目录
cd postgresql10-rpm/

# 部署
yum localinstall *.rpm -y

# 初始化
/usr/pgsql-10/bin/postgresql-10-setup initdb
systemctl enable postgresql-10
systemctl start postgresql-10

# 开启数据库端口监听
cat >> /var/lib/pgsql/10/data/postgresql.conf <<-EOF
listen_addresses = '*'
port = 5432
EOF

# 开启远程访问
echo "host    all             all             0.0.0.0/0               md5" >> /var/lib/pgsql/10/data/pg_hba.conf

# 设置密码
sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'GeoStar@123';

# 系统服务生效
systemctl daemon-reload

# 重启
systemctl restart postgresql-10

PostGIS部署

# 外网机器yum下载postgis的安装包（rpm）

# 解压
tar xvf postgis30_rhel7.tgz

# 切换目录
cd postgis30

# 安装依赖rpm
yum localinstall *.rpm -y

# 创建POST的extension
sudo -u postgres psql
create extension postgis;
create extension postgis_topology;
create extension fuzzystrmatch;
create extension address_standardizer;
create extension address_standardizer_data_us;
create extension postgis_tiger_geocoder;

集群部署

# 集群部署
# @主节点操作
# 创建同步用户
sudo -u postgres psql
create role actorcloud login replication encrypted password 'public';
# 退出sql cli

# 配置节点可访问
cat >> /var/lib/pgsql/10/data/pg_hba.conf <<-EOF
host    replication    actorcloud    172.15.110.251/24    trust
host    replication    actorcloud    172.15.110.252/24    trust
EOF

# 修改配置
cat >> /var/lib/pgsql/10/data/postgresql.conf <<-EOF
wal_level = replica
max_wal_senders= 10
wal_keep_segments = 10240
EOF
sed -i 's/max_connections = 100/max_connections = 512/' /var/lib/pgsql/10/data/postgresql.conf

# 重启
systemctl restart postgresql-10


# 集群部署
# @从节点操作
# 同步初始数据
su postgres
rm -rf /var/lib/pgsql/10/data/*
pg_basebackup -h 172.15.110.251 -U actorcloud -D /var/lib/pgsql/10/data -X stream -P

# 创建recovery配置文件
cp /usr/pgsql-10/share/recovery.conf.sample /var/lib/pgsql/10/data/recovery.conf

# 写入配置文件，配置主节点地址
cat >> /var/lib/pgsql/10/data/recovery.conf <<-EOF
standby_mode = on
primary_conninfo = 'host=172.15.110.251 port=5432 user=actorcloud password=public' 
recovery_target_timeline = 'latest'
trigger_file = '/tmp/trigger_file0'
EOF

# 热备保护开启
echo "hot_standby = on" >> /var/lib/pgsql/10/data/postgresql.conf

# 退出postgres用户

# 重启数据库
systemctl restart postgresql-10



# 集群部署
# @主节点操作
# 查询从节点是否连接
sudo -u postgres psql
select client_addr,sync_state from pg_stat_replication;



# 集群部署
# @ all mathines

# 修改postgres用户密码
passwd postgres
mkdir -p /var/lib/pgsql/.ssh && chown postgres:postgres /var/lib/pgsql/.ssh
su postgres
ssh-keygen
ssh-copy-id <其他机器ip>



#  集群部署
#  @all mathines
# root user
# 外网提前下载pgpool II

# 解压
tar xvf pgpoolII-rhel7-rpm.tgz

# 切换目录
cd pgpoolII

# 安装
yum localinstall *.rpm -y

# 修改pool_hba.conf配置文件
cat >> /etc/pgpool-II/pool_hba.conf <<-EOF
host    replication    actorcloud    172.15.110.251/24    trust
host    replication    actorcloud    172.15.110.252/24    trust
host    all    all    0.0.0.0/0    md5
EOF

# 此处省略了生成密码md5的过程（pg_md5 GeoStar@123）
# 设置连接密码
echo "postgres:6d0f26e2540c65101f0c8187e352fcaf" >> /etc/pgpool-II/pcp.conf

# 开启监听
cat >> /etc/pgpool-II/pgpool.conf <<-EOF
#
# listen all address
listen_addresses = '*'
EOF

# 添加第一台主机的backend
cat >> /etc/pgpool-II/pgpool.conf <<-EOF

# cluster_node1
backend_hostname0 = '172.15.110.251'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/10/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
EOF

# 添加第二台主机的backend
cat >> /etc/pgpool-II/pgpool.conf <<-EOF

# cluster_node2
backend_hostname1 = '172.15.110.252'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/10/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
EOF

# pg_hba.conf生效
cat >> /etc/pgpool-II/pgpool.conf <<-EOF

# Use pool_hba.conf for client authentication
enable_pool_hba = on
EOF

# 开启负载均衡
cat >> /etc/pgpool-II/pgpool.conf <<-EOF

# load_balance_mode
load_balance_mode = on
EOF

# 主从流复制
cat >> /etc/pgpool-II/pgpool.conf <<-EOF

# Streaming replication check
master_slave_mode = on
sr_check_period = 6
sr_check_user = 'actorcloud'
sr_check_password = 'public'
sr_check_database = 'postgres'
delay_threshold = 10000000
EOF

# 配置主机故障触发执行的脚本
cat >> /etc/pgpool-II/pgpool.conf <<-EOF

# Executes this command at failover
failover_command = '/var/lib/pgsql/10/failover_stream.sh %d %H'
EOF

# 开启看门狗，IP为本机IP
cat >> /etc/pgpool-II/pgpool.conf <<-EOF

# Watchdog
use_watchdog = on
wd_hostname = '172.15.110.251'
EOF

# 开启虚拟IP，此处主从节点一致
cat >> /etc/pgpool-II/pgpool.conf <<-EOF

# delegate IP
delegate_IP = '172.15.110.253'
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
arping_cmd = 'arping -U $_IP_$ -w 1 -I eth0'
EOF

# 心跳检查的配置与看门狗配置，IP为其他节点的IP。
cat >> /etc/pgpool-II/pgpool.conf <<-EOF

# delegate IP
heartbeat_destination0 = '172.15.110.252'
heartbeat_device0 = 'eth0'
other_pgpool_hostname0 = '172.15.110.252'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
EOF

# 编写故障切换脚本
touch /var/lib/pgsql/10/failover_stream.sh
cat >> /var/lib/pgsql/10/failover_stream.sh <<-EOF
#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.
failed_node=$1
new_master=$2
trigger_file=$3
# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
exit 0;
fi
# Create the trigger file.
# use commond 
/usr/bin/ssh -T $new_master /usr/pgsql-10/bin/pg_ctl promote -D /var/lib/pgsql/10/data/
# use file
# /usr/bin/ssh -T $new_master  /bin/touch /tmp/trigger_file0
exit 0
EOF

# 各种权限处理 
chmod u+s /sbin/ifconfig && chmod u+s /usr/sbin
chown postgres:postgres /var/lib/pgsql/10/failover_stream.sh && chmod 777 /var/lib/pgsql/10/failover_stream.sh
chown -R postgres.postgres /etc/pgpool-II
# pgpool 日志
mkdir /var/log/pgpool
chown -R postgres.postgres /var/log/pgpool
# pgpool pid目录
mkdir /var/run/pgpool
chown -R postgres.postgres /var/run/pgpool

# 启动
systemctl start pgpool
# 开机自启
systemctl enable pgpool