postgresql9.6主从高可用源码环境编译配置详解

系统版本：centos7

8核

32G内存

主从服务器IP：

192.168.125.33 postgreSQL master

192.168.125.34 postgreSQL slave

1、创建数据库管理账户

# groupadd pggroup

# useradd -g pggroup pguser 

# passwd pguser

2、安装依赖包

yum install readline-devel zlib-devel -y

如果不安装以上两个包，编译postgresql就会报缺少相应包的错误，导致无法通过。

yum install postgresql-contrib

http://mirror.centos.org/centos/7/os/x86_64/Packages/uuid-devel-1.6.2-26.el7.x86_64.rpm

版本和系统的uuid版本一样，uuid-1.6.2-26.el7.x86_64

rpm -ivh uuid-devel-1.6.2-26.el7.x86_64.rpm

如果不安装以上的一个包，编译postgresql就会报以下的错误：

configure: error: library 'ossp-uuid' or 'uuid' is required for OSSP-UUID"

如果源里边有这个包，可以使用yum进行安装。

3、编译

主从服务器上传包postgresql-9.6.3.tar.gz，并进行编译

# tar -zxf postgresql-9.6.3.tar.gz

# cd postgresql-9.6.3

# ./configure --prefix=/usr/local/postgresql --with-ossp-uuid

# make && make install

4、编译uuid模块和pg_stat_statements模块

如果环境中不需要使用UUID类型的函数，也不需要pg_stat_statements函数模块对分析sql对资源占用的情况的话，可以跳过UUID和pg_stat_statements的处理，当然在之后的配置文件也要相应做修改。

# cd contrib/uuid-ossp

# make && make install 

上边如果不编译uuid-ossp，就会出现下面的错误：

postgres=# create extension "uuid-ossp";

ERROR:  could not open extension control file "/usr/local/postgresql/share/extension/uuid-ossp.control": No such file or directory

# cd contrib/pg_stat_statements

# make && make install

5、配置环境变量

# vi /etc/profile

export PGHOME=/usr/local/postgresql

export PGDATA=/data/pg_data

export PATH=$PATH:$PGHOME/bin

# source /etc/profile

6、给目录赋予权限

# make /data/pg_data

# chown -R pguser:pggroup /data/pg_data

# chown -R pguser:pggroup /usr/local/postgresql

7、主服务器初始化数据库

首先切换到普通用户

# su pguser

$ initdb -D /data/pg_data

启动服务

$ pg_ctl -D /data/pg_data start

8、创建数据同步用户

$ psql -h 127.0.0.1 -d postgres

postgres=# create role repuser login replication encrypted password '密码自己定义';

修改管理账户登录密码

postgres=# ALTER USER pguser WITH PASSWORD '密码自己定义'

postgres=# \q

9、修改配置文件

编辑pg_hba.conf

bash-4.2$ vi /data/pg_data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

local   all             all                                     md5

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

host    all             all             192.168.125.0/24            md5

host    all             all             192.168.99.0/24            md5

# replication privilege.

host    replication     repuser         192.168.125.0/24             md5

说明：

TYPE定义了多种连接PostgreSQL的方式，分别是：“local”使用本地unix套接字，“host”使用TCP/IP连接（包括SSL和非SSL），“host”结合“IPv4地址”使用IPv4方式，结合“IPv6地址”则使用IPv6方式，“hostssl”只能使用SSL TCP/IP连接，“hostnossl”不能使用SSL TCP/IP连接。

DATABASE指定哪个数据库，多个数据库，库名间以逗号分隔。“all”只有在没有其他的符合条目时才代表“所有”，如果有其他的符合条目则代表“除了该条之外的”，因为“all”的优先级最低。

USER指定哪个数据库用户（PostgreSQL正规的叫法是角色，role）。多个用户以逗号分隔。

ADDRESS项local方式不必填写，该项可以是IPv4地址或IPv6地址，可以定义某台主机或某个网段。

配置 192.168.125.X  和 192.168.99.X 两个网段的服务器可以访问数据库

METHOD指定如何处理客户端的认证。常用的有ident，md5，password，trust，reject。

ident是Linux下PostgreSQL默认的local认证方式，凡是能正确登录服务器的操作系统用户（注：不是数据库用户）就能使用本用户映射的数据库用户不需密码登录数据库。

md5是常用的密码认证方式，如果你不使用ident，最好使用md5。密码是以md5形式传送给数据库，较安全，且不需建立同名的操作系统用户。

password是以明文密码传送给数据库，建议不要在生产环境中使用。

trust是只要知道数据库用户名就不需要密码或ident就能登录，建议不要在生产环境中使用。

reject是拒绝认证。

最后一行配置的是从库服务器的信息，也就是同步主库数据数据库的信息。

编辑postgresql.conf

bash-4.2$ vi /data/pg_data/postgresql.conf

添加下面配置，配置文件有下面配置的要删除（包括前边有警号'#'的）

listen_addresses = '*'

wal_level = hot_standby

max_wal_senders= 6

wal_keep_segments = 10240

max_connections = 512

archive_mode = on

archive_command = 'cp %p /data/pg_data/pg_archive/%f'

# 配置pg_stat_statements

shared_preload_libraries = 'pg_stat_statements'  

track_io_timing = on

track_activity_query_size = 2048

pg_stat_statements.max = 10000

pg_stat_statements.track = all

pg_stat_statements.track_utility = off

pg_stat_statements.save = on

创建归档文件存放目录

bash-4.2$ mkdir /data/pg_data/pg_archive

#如果需要的话需要调整做性能的优化

shared_buffers = 3276MB

work_mem = 655MB

effective_cache_size = 2GB

maintence_work_mem = 256MB

max_connections = 4000

# vi /etc/sysctl.conf

kernel.sem = 50100 128256000 50100 2560

# sysctl -p

重启服务

$ pg_ctl -D /data/pg_data restart

10、创建uuid和pg_stat_statements

登录相应的数据库

postgres=# create extension "uuid-ossp";

CREATE EXTENSION

验证

postgres=# select uuid_generate_v4();

           uuid_generate_v4           

--------------------------------------

 28cbfa1e-d659-4aa2-a0fd-95fc7ec0aa8b

(1 row)

postgres=# create extension pg_stat_statements;

验证

select * from pg_stat_statements order by total_time desc limit 5;

11、创建数据库、访问用户并给数据库赋权

主服务器

postgres=# create user testuser with password '密码自定义';

CREATE ROLE

postgres=# create database test owner yourpassword;

CREATE DATABASE

postgres=# grant all privileges on database test to testuser;

GRANT

登录数据库

psql -U testuser -d test -W

12、同步数据

从服务器，在普通用户下

bash-4.2$ pg_basebackup -h 192.168.125.33 -U repuser -D /data/pg_data -X stream -P

Password:

36413/36413 kB (100%), 1/1 tablespace

13、从服务器修改配置文件recovery.conf、postgresql.conf

bash-4.2$ cp /usr/local/postgresql/share/pgsql/recovery.conf.sample /data/pg_data/recovery.conf

bash-4.2$ vi /data/pg_data/recovery.conf

添加下面配置，配置文件有下面配置的要删除（包括前边有警号'#'的）

standby_mode = on

primary_conninfo = 'host=192.168.125.33 port=5432 user=repuser password=yourpassword keepalives_idle=60'

recovery_target_timeline = 'latest'

bash-4.2$ vi /data/pg_data/postgresql.conf

添加下面配置

hot_standby = on

max_standby_streaming_delay = 30s

wal_receiver_status_interval = 10s

hot_standby_feedback = on

postgresql.conf配置文件说明：

# 使PostgreSQL可以接受来自任意IP的连接请求

listen_addresses = '*'

# postgres在9.0之后引入了主从的流复制机制，所谓流复制，就是从库通过tcp流从主库中同步相应的数据。

# 设置主为wal的主机

wal_level = hot_standby

# 设置可以最多有几个流复制连接，有几个从，就设置几个

max_wal_senders= 6

# postgresql的pg_xlog是记录数据库事务信息用的，叫wal日志(write ahead log)，就是在写数据到磁盘里成为固定数据之前，先写入到日志里，然后一定条件下触发调用fsync()将此数据刷到磁盘。

# 在主数据库上设置wal_keep_segments为一个足够大的值，以防止主库生成WAL日志太快，日志还没有来得及传送到standby，就会循环覆盖了。

wal_keep_segments = 10240

# 最大服务器连接数

max_connections = 4000

# 启用archive_mode

# 当启用archive_mode时，通过设置archive_command将已完成的WAL段发送到归档存储。除了off，disable，还有两种模式：on,always。在正常操作期间，两种模式之间没有区别，但是当设置为always的情况下,WAL archiver在存档恢复或待机模式下也被启用。在always模式下，从归档还原或流式复制流的所有文件都将被归档（再次）。archive_mode和archive_command是单独的变量，因此可以在不更改存档模式的情况下更改archive_command。此参数只能在服务器启动时设置。当wal_level设置为minimal时，无法启用archive_mode。

archive_mode = on

# 把归档文件保存到 /data/pg_data/pg_archive 目录下

archive_command = 'cp %p /data/pg_data/pg_archive/%f'

# 设置数据库服务器既用于数据归档，也用于数据查询。

hot_standby = on

# 数据流备份的最大延迟时间

max_standby_streaming_delay = 30s

# 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间。

wal_receiver_status_interval = 10s

# 如果有错误的数据复制，是否向主进行反馈

hot_standby_feedback = on

# 在启动时导入pg_stat_statements 动态库

shared_preload_libraries = 'pg_stat_statements' 

# 如果要跟踪IO消耗的时间，还需要打开如下参数

track_io_timing = on

# 设置单条SQL的最长长度，超过被截断显示

track_activity_query_size = 2048

# 监控的语句最多为10000句

pg_stat_statements.max = 10000

# pg_stat_statements.track控制哪些语句会被该模块计数。指定top可以跟踪顶层语句（那些直接由客户端发出的语句），指定all还可以跟踪嵌套的语句（例如在函数中调用的语句），指定none可以禁用语句统计信息收集。默认值是top。 只有超级用户能够改变这个设置。

pg_stat_statements.track = all

# 配置pg_stat_statements.track_utility控制该模块不跟踪工具命令。工具命令是除了SELECT、INSERT、 UPDATE和DELETE之外所有的其他命令。默认值是on。 只有超级用户能够改变这个设置。

pg_stat_statements.track_utility = off

# 指定在服务器关闭之后还保存语句统计信息。如果被设置为off，那么关闭后不保存统计信息并且在服务器启动时也不会重新载入统计信息。默认值为on。这个参数只能在postgresql.conf文件中或者在服务器命令行上设置。

pg_stat_statements.save = on

# 设置数据库服务器将使用的共享内存缓冲区量

shared_buffers = 3276MB

#  work_mem在pgsql 8.0之前叫做sort_mem。postgresql在执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和 work_mem查不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加work_mem有助于提高排序的速度。通常设置为实际RAM的2% -4%，根据需要排序结果集的大小而定。

work_mem = 655MB

# postgresql能够使用的最大缓存

effective_cache_size = 2GB

# 这里定义的内存只是在CREATE INDEX, VACUUM等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，因此应该尽快让这些指令快速执行完毕。

maintence_work_mem = 256MB

# vi /etc/sysctl.conf

kernel.sem = 50100 128256000 50100 2560

# sysctl -p

14、从服务器启动服务

首先修改目录权限

# chmod 700 /data/pg_data

# su pguser

bash-4.2$ pg_ctl -D /data/pg_data start

15、验证

主服务器：

bash-4.2$ psql -h 127.0.0.1 -d postgres

查看同步数据库

postgres=# select client_addr,sync_state from pg_stat_replication;

 client_addr  | sync_state

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

 192.168.125.34 | async

 postgres=# create database test1;

CREATE DATABASE

查看数据库

postgres=# \l

从服务器：

# su pguser

bash-4.2$ psql -h 127.0.0.1 -d postgres

查看 test1库是否已经同步过来

postgres=# \l

16、主从高可用组件安装配置

16.1、主从服务器安装keepalived

# yum install -y keepalived

16.2、主服务器配置

# cd /etc/keepalived

# vi keepalived.conf

! Configuration File for keepalived

global_defs {

   smtp_server 127.0.0.1

   smtp_connect_timeout 30

   router_id pg

}

vrrp_script chk_pg {

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

    interval 2

    weight -5

    fall 2

    rise 1

}

vrrp_instance VI_1 {

    state BACKUP

    interface eth0

    virtual_router_id 61

    priority 100

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    track_script {

        chk_pgsql

    }

    virtual_ipaddress {

        192.168.125.200

    }

}

# mkdir log 

# mkdir scripts 

# vi scripts/pgsql_check.sh 

#!/bin/bash

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

# 判断vip浮到哪里

B=`ip a | grep 192.168.125.200 | 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浮到了从，提升从的地位让他可读写

        if [ $B -eq 1 -a $C -eq 1 -a $D -eq 0 ];then

                su - pguser -c "pg_ctl promote -D /data/pg_data"

                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

16.3、从服务器配置

# cd /etc/keepalived

# vi keepalived.conf

! Configuration File for keepalived

global_defs {

   smtp_server 127.0.0.1

   smtp_connect_timeout 30

   router_id pg

}

vrrp_script chk_pg {

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

    interval 2

    weight -5

    fall 2

    rise 1

}

vrrp_instance VI_1 {

    state BACKUP

    interface eth0

    virtual_router_id 61

    priority 96

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    track_script {

        chk_pgsql

    }

    virtual_ipaddress {

        192.168.125.200

    }

}

# mkdir log 

# mkdir scripts 

# vi scripts/pgsql_check.sh 

#!/bin/bash

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

# 判断vip浮到哪里

B=`ip a | grep 192.168.125.200 | 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浮到了从，提升从的地位让他可读写

        if [ $B -eq 1 -a $C -eq 1 -a $D -eq 0 ];then

                su - pguser -c "pg_ctl promote -D /data/pg_data"

                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

16.4、主从服务器启动服务

# systemctl start keepalived.service

17、访问数据库就可以通过访问虚拟IP192.168.125.200直接访问

18、主从切换

当主库数据库服务器宕机或者数据库服务异常停止后，从库会自动切换为主库，且虚拟IP会在从库上生成，实现从库自动切换为主库，但是停止的原来主库要变为现在的从库需要手动切换。

原来的主作为现在的备

首先做备份

# cp pg_data pg_data.bak -Rp

删除文件

$ cd pg_data

$ rm -rf *

同步数据

$ pg_basebackup -h 192.168.125.34 -U repuser -D /data/pg_data -X stream -P

$ mv recovery.done recovery.conf

$ vi recovery.conf

primary_conninfo = 'host=192.168.125.33 port=5432 user=repuser password=yourpassword keepalives_idle=60'

>>

primary_conninfo = 'host=192.168.125.34 port=5432 user=repuser password=yourpassword keepalives_idle=60'

启动主机keepalived

# systemctl start keepalived.service