postgresql9.6主從高可用源碼環(huán)境編譯配置詳解

系統(tǒng)版本：centos7

8核

32G內存

主從服務器IP：

192.168.125.33 postgreSQL master

192.168.125.34 postgreSQL slave

1、創(chuàng)建數據庫管理賬戶

# 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

版本和系統(tǒng)的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模塊

如果環(huán)境中不需要使用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、配置環(huán)境變量

# 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、創(chuàng)建數據同步用戶

$ 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的方式，分別是：“l(fā)ocal”使用本地unix套接字，“host”使用TCP/IP連接（包括SSL和非SSL），“host”結合“IPv4地址”使用IPv4方式，結合“IPv6地址”則使用IPv6方式，“hostssl”只能使用SSL TCP/IP連接，“hostnossl”不能使用SSL TCP/IP連接。

DATABASE指定哪個數據庫，多個數據庫，庫名間以逗號分隔?！癮ll”只有在沒有其他的符合條目時才代表“所有”，如果有其他的符合條目則代表“除了該條之外的”，因為“all”的優(yōu)先級最低。

USER指定哪個數據庫用戶（PostgreSQL正規(guī)的叫法是角色，role）。多個用戶以逗號分隔。

ADDRESS項local方式不必填寫，該項可以是IPv4地址或IPv6地址，可以定義某臺主機或某個網段。

配置 192.168.125.X  和 192.168.99.X 兩個網段的服務器可以訪問數據庫

METHOD指定如何處理客戶端的認證。常用的有ident，md5，password，trust，reject。

ident是Linux下PostgreSQL默認的local認證方式，凡是能正確登錄服務器的操作系統(tǒng)用戶（注：不是數據庫用戶）就能使用本用戶映射的數據庫用戶不需密碼登錄數據庫。

md5是常用的密碼認證方式，如果你不使用ident，最好使用md5。密碼是以md5形式傳送給數據庫，較安全，且不需建立同名的操作系統(tǒng)用戶。

password是以明文密碼傳送給數據庫，建議不要在生產環(huán)境中使用。

trust是只要知道數據庫用戶名就不需要密碼或ident就能登錄，建議不要在生產環(huán)境中使用。

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

創(chuàng)建歸檔文件存放目錄

bash-4.2$ mkdir /data/pg_data/pg_archive

#如果需要的話需要調整做性能的優(yōu)化

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、創(chuàng)建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、創(chuàng)建數據庫、訪問用戶并給數據庫賦權

主服務器

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)，就是在寫數據到磁盤里成為固定數據之前，先寫入到日志里，然后一定條件下觸發(fā)調用fsync()將此數據刷到磁盤。

# 在主數據庫上設置wal_keep_segments為一個足夠大的值，以防止主庫生成WAL日志太快，日志還沒有來得及傳送到standby，就會循環(huán)覆蓋了。

wal_keep_segments = 10240

# 最大服務器連接數

max_connections = 4000

# 啟用archive_mode

# 當啟用archive_mode時，通過設置archive_command將已完成的WAL段發(fā)送到歸檔存儲。除了off，disable，還有兩種模式：on,always。在正常操作期間，兩種模式之間沒有區(qū)別，但是當設置為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

# 多久向主報告一次從的狀態(tài)，當然從每次數據復制都會向主報告狀態(tài)，這里只是設置最長的間隔時間。

wal_receiver_status_interval = 10s

# 如果有錯誤的數據復制，是否向主進行反饋

hot_standby_feedback = on

# 在啟動時導入pg_stat_statements 動態(tài)庫

shared_preload_libraries = 'pg_stat_statements' 

# 如果要跟蹤IO消耗的時間，還需要打開如下參數

track_io_timing = on

# 設置單條SQL的最長長度，超過被截斷顯示

track_activity_query_size = 2048

# 監(jiān)控的語句最多為10000句

pg_stat_statements.max = 10000

# pg_stat_statements.track控制哪些語句會被該模塊計數。指定top可以跟蹤頂層語句（那些直接由客戶端發(fā)出的語句），指定all還可以跟蹤嵌套的語句（例如在函數中調用的語句），指定none可以禁用語句統(tǒng)計信息收集。默認值是top。 只有超級用戶能夠改變這個設置。

pg_stat_statements.track = all

# 配置pg_stat_statements.track_utility控制該模塊不跟蹤工具命令。工具命令是除了SELECT、INSERT、 UPDATE和DELETE之外所有的其他命令。默認值是on。 只有超級用戶能夠改變這個設置。

pg_stat_statements.track_utility = off

# 指定在服務器關閉之后還保存語句統(tǒng)計信息。如果被設置為off，那么關閉后不保存統(tǒng)計信息并且在服務器啟動時也不會重新載入統(tǒng)計信息。默認值為on。這個參數只能在postgresql.conf文件中或者在服務器命令行上設置。

pg_stat_statements.save = on

# 設置數據庫服務器將使用的共享內存緩沖區(qū)量

shared_buffers = 3276MB

#  work_mem在pgsql 8.0之前叫做sort_mem。postgresql在執(zhí)行排序操作時，會根據work_mem的大小決定是否將一個大的結果集拆分為幾個小的和 work_mem查不多大小的臨時文件。顯然拆分的結果是降低了排序的速度。因此增加work_mem有助于提高排序的速度。通常設置為實際RAM的2% -4%，根據需要排序結果集的大小而定。

work_mem = 655MB

# postgresql能夠使用的最大緩存

effective_cache_size = 2GB

# 這里定義的內存只是在CREATE INDEX, VACUUM等時用到，因此用到的頻率不高，但是往往這些指令消耗比較多的資源，因此應該盡快讓這些指令快速執(zhí)行完畢。

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`

# 判斷是否是從庫處于等待的狀態(tài)

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

# 判斷出自己是主并且和從失去聯(lián)系

        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`

# 判斷是否是從庫處于等待的狀態(tài)

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

# 判斷出自己是主并且和從失去聯(lián)系

        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會在從庫上生成，實現從庫自動切換為主庫，但是停止的原來主庫要變?yōu)楝F在的從庫需要手動切換。

原來的主作為現在的備

首先做備份

# 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