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