PostgreSQL主从部署

自行下载 rpm 包本地安装或者 wget 创建 PostgreSQL 的源文件。本次记录为内网服务器，rpm 本地安装，版本为 PostgreSQL-9.6，经测试，9.6 主从流复制可切换，9.4 测试未果。

位于 /share/rpms/psql-need/ 目录下。

Master server

系统版本	机器配置	主机名	IP
CentOS Linux release 7.4.1708	48C/256G/33TB	Bus-App-21	190.176.35.170
CentOS Linux release 7.4.1708	48C/256G/33TB	Bus-App-22	190.176.35.171

以下命令在 rpm 包目录下执行。

# yum install ./postgresql96\*

# mkdir -p /data/pgsql-9.6/archive

# chown postgres.postgres /data/pgsql-9.6

# chmod 0700 /data/pgsql-9.6

# vim /usr/lib/systemd/system/postgresql-9.6.service

Environment=pgsql-9.6=/data/pgsql-9.6/

# /usr/pgsql-9.6/bin/initdb -D /data/pgsql-9.6

# systemctl enable postgresql-9.6

# systemctl start postgresq-9.6

# passwd postgres

# su postgres

$ psql –U postgres

=$ alter user postgres with password‘Ur passwd’;

=$\q

# vim /data/pgsql-9.6/postgresql.conf

listen_addresses = '*'

max_connections = 500

superuser_reserved_connections = 5

shared_buffers = 128MB

dynamic_shared_memory_type = posix

wal_level = hot_standby

synchronous_commit = local

archive_mode = on

archive_command = 'cp %p /data/pgsql-9.6/archive/%f'

max_wal_senders = 5

# vim /data/pgsql-9.6/pg_hba.conf

host    replication     postgres        127.0.0.1/32              md5

host    replication     postgres        190.176.35.0/24                 md5

# systemctl restart postgresql-9.6

以下命令在 rpm 目录下执行。

# yum install postgresql96*

# vim /usr/lib/systemd/system/postgresql-9.6.service

Environment=pgsql-9.6=/data/pgsql-9.6/

# mkdir -p /data/pgsql-9.6/archive

# chown postgres.postgres /data/pgsql-9.6

#chmod 0700 /data/pgsql-9.6

# su postgres

$ pg_basebackup -h 190.176.35.170 -U postgres -D /data/pgsql-9.6 -P --xlog

$ vim /data/pgsql-9.6/postgresql.conf

hot_standby = on

max_connections = 510 #于主库数值

max_wal_senders = 5

$ cp /usr/pgsql-9.6/share/recovery.conf.sample /data/pgsql-9.6/

$ mv /data/pgsql-9.6/recovery.conf.sample /data/pgsql-9.6/recovery.conf

$ vim /data/pgsql-9.6/recovery.conf

recovery_target_timeline = 'latest'

standby_mode = on

primary_conninfo = 'host= 主库’s IP port=5432 user=postgres password= 密码'

trigger_file = '/data/pgsql-9.6/trigger.pg

$ exit

# systemctl enable postgresql-9.6

# systemctl start postgresql-9.6

在 Master 所在服务器查看状态：

$ psql -c "select application_name,state,sync_priority,sync_state from pg_stat_replication"

application_name |   state   | sync_priority | sync_state

------------------+-----------+---------------+------------

 Bus-App-22       | streaming |             1 | sync

(1 row)

测试复制功能

自行安装软件并测试使用

配置文件配置 exm 如下

# cat /etc/keepalived/keepalived.conf

global_defs {

   smtp_server 127.0.0.1

   smtp_connect_timeout 30

   router_id pg

}

vrrp_script chk_pgsql {

   script "/etc/keepalived/scripts/pgsql_check.sh"

   interval 1

   weight 2

}

vrrp_instance VI_1 {

    state MASTER #备机为 BACKUP

    interface enp2s0f0

    virtual_router_id 61

    priority 100 #备机为 80

    nopreempt

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    track_script {chk_pgsql}

    virtual_ipaddress {190.176.35.210}

}

测试 vip 的浮动

脚本路径可自己在 keeplived.conf 中定义，注意脚本权限。

# cat /etc/keepalived/scripts/pgsql_check.sh

#!/bin/bash

#判断 pg 是否活着

A=`ps -C postmaster --no-header | wc -l`

#判断 vip 浮到哪里

B=`ip a | grep 190.176.35.210 | wc -l`

#判断是否是从库处于等待的状态

C=`ps -ef | grep postgres | grep 'startup process' | wc -l`

#判断从库链接主库是否正常

D=`ps -ef | grep postgres | grep 'receiver' | wc -l`

#判断主库连接从库是否正常

E=`ps -ef | grep postgres | grep 'sender' | wc -l`

#如果 pg 死了，将消息写入日记并且关闭 keepalived
if [$A -eq 0];then
    echo "\`date"+%Y-%m-%d--%H:%M:%S"\` postgresql stop so vip stop" >> /etc/keepalived/log/check_pg.log
    systemctl stop keepalived
else
        #判断出主挂了，vip 浮到了从，创建 trigger，pg 备机进入 recovery 模式，赋予读写权限
        if [$B -eq 1 -a $C -eq 1 -a $D -eq 0 \];then
                su - postgres -c "touch /data/pgsql-9.6/[trigger.pg](http://trigger.pg/)"
                echo "`date"+%Y-%m-%d--%H:%M:%S"` standby promote" >> /etc/keepalived/log/check_pg.log
        fi
        #判断出自己是主并且和从失去联系
        if [$B -eq 1 -a $C -eq 0 -a $D -eq 0 -a $E -eq 0];then
                                sleep 10
                echo "`date"+%Y-%m-%d--%H:%M:%S"` can't find standby " >> /etc/keepalived/log/check_pg.log
        fi
fi

以下命令为手动指定 keepalived 配置文件，并启动的命令

# /usr/local/keepalived/sbin/keepalived -D -f /usr/local/keepalived/etc/keepalived/keepalived.conf

以下红色标记的命令为流复制主从切换，与恢复 MASTER 的操作

模拟关闭主库，演练 MASTER 服务宕机

# systemcrl stop postgresql-9.6

SLAVE 上操作

在从库创建 trigger（此步骤部署于高可用脚本中，触发自动推读写数据库切换）

$ touch /data/pgsql-9.6/trigger.pg

旧备库 recovery.conf 变为 recovery.done，旧备库成为新主库

$ mv /data/pgsql-9.6/recovery.done /data/pgsql-9.6/recovery.conf

如第一次触发 HA，则将 recovery.done 拷贝到 MASTER，并修改以下内容

primary_conninfo = 'host= 新主库’s IP port=5432 user=postgres password= 密码'

如第一次出发 HA，则修改 MASTER 配置文件，MASTER 现在为新备库，所以 postgersql.conf 中的 max_connections 应该大于新主库

$ vim /data/pgsql-9.6/postgresql.conf

hot_standby = on #检查是否开启

max_connections = (每次恢复都必须修改新备机的这条配置，并比新主机的大)

启动新备库与新备库的 keepalived

# systemctl restart postgresql-9.6

以上操作完后应该主备流复制 MASTER 为备 receiver，SLAVE 为主 sender。使用命令# ps -ef | grep postgres 可查看

以下一条操作 VIP 从 SLAVE 跳至 MASTER

# systemctl restart keepalived

# ip a #查看 vip 应跳回 MASTER

关闭新主库 postgresql 服务

# systemctl stop postgresql-9.6

$ mv /data/pgsql-9.6/recovery.done /data/pgsql-9.6/recovery.conf

$ vim /data/pgsql-9.6/postgresql.conf

max_connections = (每次恢复都必须修改新备机的这条配置，并比新主机的大)

# systemctl start postgresql-9.6

# systemctl start keepalived

以上操作完后应该主备流复制 MASTER 为备 sender，SLAVE 为主 receiver。使用命令# ps -ef | grep postgres 可查看

在 MASTER 上查看流复制状态

$ psql

postgres=# select client_addr,state,sync_state from pg_stat_replication;

  client_addr   |   state   | sync_state

----------------+-----------+------------（主库’s IP）| streaming | async

(1 row)

MASTER 为发送端，不存在 startup process 并存在 sender 进程判定为主库

$ ps -ef | grep postgres

postgres   378     1  0 11:32 pts/0    00:00:00 /usr/pgsql-9.6/bin/postgres -D /data/pgsql-9.6

postgres   379   378  0 11:32 ?        00:00:00 postgres: logger process                   

postgres   381   378  0 11:32 ?        00:00:00 postgres: checkpointer process             

postgres   382   378  0 11:32 ?        00:00:00 postgres: writer process                   

postgres   383   378  0 11:32 ?        00:00:00 postgres: wal writer process               

postgres   384   378  0 11:32 ?        00:00:00 postgres: autovacuum launcher process      

postgres   385   378  0 11:32 ?        00:00:00 postgres: archiver process                 

postgres   387   378  0 11:32 ?        00:00:00 postgres: stats collector process          

postgres   464   378  0 11:47 ?        00:00:00 postgres: wal sender process postgres 192.168.40.185(50096) streaming 0/90038A0

root      1293  1258  0 14:13 pts/1    00:00:00 su postgres

postgres  1294  1293  0 14:13 pts/1    00:00:00 bash

postgres  1417  1294  0 14:38 pts/1    00:00:00 ps -ef

postgres  1418  1294  0 14:38 pts/1    00:00:00 grep postgres

SLAVE 为接收端，存在 startup process 与 wal receiver 进程并存则判定为备库

$ ps -ef | grep postgres

root      1293  1258  0 14:13 pts/1    00:00:00 su postgres

postgres  1294  1293  0 14:13 pts/1    00:00:00 bash

postgres  1448     1  0 14:43 pts/1    00:00:00 /usr/pgsql-9.6/bin/postgres -D /data/pgsql-9.6

postgres  1449  1448  0 14:43 ?        00:00:00 postgres: logger process                   

postgres  1450  1448  0 14:43 ?        00:00:00 postgres: startup process   recovering 00000003000000000000000A

postgres  1451  1448  0 14:43 ?        00:00:00 postgres: checkpointer process             

postgres  1452  1448  0 14:43 ?        00:00:00 postgres: writer process                   

postgres  1453  1448  0 14:43 ?        00:00:00 postgres: stats collector process          

postgres  1454  1448  0 14:43 ?        00:00:00 postgres: wal receiver process   streaming 0/A001060

postgres  1457  1294  0 14:44 pts/1    00:00:00 ps -ef

postgres  1458  1294  0 14:44 pts/1    00:00:00 grep postgres

测试新主库读写

测试数据同步功能

修改默认 data 目录时，要注意新目录权限必须为 0700，属主属组必须为 postgres。
务必设置 postgres 用户的数据库密码：postgres=# alter user postgres with password‘Ur passwd’;，否则配置主从流复制，备机 pg_basebackup 会出现认证错误。
注意 pg_hba.conf 文件的权限问题。
psql: FATAL: the database system is starting up. 修改配置文件 hot_standby = on。
Keepalived 脚本中 ps -C postmaster，如果是使用 postgres 用户启动则是 ps -C postgres，其他用户为 postmaster。

PostgreSQL主从部署

环境描述

机器规划

Master Server

Slave Server

测试

Keepalived-1.2

Keepalived 脚本

异步主从流复制主从切换与恢复 MASTER

恢复操作

MASTER 上操作

SLAVE 上操作

注意事项