天天看點
傳回

使用Pgpool搭建Postgresql叢集背景步驟

作業系統：CentOS7

Postgresql版本：10.10

Pgpool-II版本：4.0.6

端口

  9999：對外提供服務

  9000：watch_dog

  9898：pcp

  9694：watch_dog心跳檢查

備注：下文的安裝步驟中，【】包圍的部分需根據自己的環境來配置

背景

  項目上有實作資料庫高可用的需求，Postgresql本身支援資料同步和手動failover。即，一個雙機熱備的Postgresql叢集，主節點當機時，整個叢集隻能對外提供讀服務，而無法提供寫服務，隻有手動執行promote指令晉升備節點為主節點，才能恢複資料庫服務的正常。以下是Postgresql官方文檔的引文:

PostgreSQL does not provide the system software required to identify a

failure on the primary and notify the standby database server.

  Pgpool通過其WatchDog的虛拟IP和健康檢查功能實作了自動故障切換。本文使用master-slave模式搭建叢集，不使用replication_mode的原因是該模式下的online recovery存在限制，Pgpool官網引文如下：

There is a restriction in the online recovery in replication_mode. If

Pgpool-II itself is installed on multiple hosts, online recovery does

not work correctly, because Pgpool-II has to stop all the clients

during the 2nd stage of online recovery. If there are several

Pgpool-II hosts, only one of them will have received the online

recovery command and will block the connections from clients.

  為實作分區容錯，本文使用3個節點搭建叢集，每個節點安裝Pgpool-II和Postgresql資料庫，資料庫為1主2備。

  注意：Pgpool-II和Postgresql叢集對于主節點使用的術語不同，Pgpool-II的主節點稱為Master，Postgresql的主節點稱為Primary；備節點使用的術語相同，都為standby。

步驟

1 安裝并配置PostgreSQL

  在所有節點安裝PostgreSQL，但僅需要在節點1配置并啟動PostgreSQL，然後使用pcp_recovery_node功能初始化并啟動節點2、3上的Postgresql服務，注意不要讓PostgreSQL開機自啟。參考PG資料庫安裝。

2 配置/etc/hosts

  增加DNS配置，供recovery_1st_stage使用

【節點1的IP】 【節點1的主機名】
【節點2的IP】 【節點2的主機名】
【節點3的IP】 【節點3的主機名】

3 配置ssh免密通信

  Pgpool的failover配置中使用的failover.sh、follow_master.sh，以及online recovery配置中使用的recovery_1st_stage、pgpool_remote_start腳本中，都涉及通過ssh進行遠端操作。

  需要配置root使用者到postgres使用者（包括本機的postgres使用者）的單向，以及postgres使用者之間的雙向免密通信。

3.1 設定postgres使用者的密碼

su
passwd postgres

3.2 在目前節點執行以下語句，生成密鑰

  注意不要設定passphrase

su 【目前使用者】
ssh-keygen -t rsa

3.3 将公鑰發送到其他節點

ssh-copy-id -i ~/.ssh/id_rsa.pub 【使用者名】@【IP】

3.4 測試ssh

ssh 【使用者名】@【IP】

4 修改ssh配置（可選，預設10個一般夠）

vi /etc/ssh/sshd_config

修改

MaxSessions 100

重新開機sshd

systemctl restart sshd

5 配置系統日志

  建立日志檔案，應建立在/var/log路徑下，否則syslog無法寫入檔案（除非關閉或配置SELinux）。

mkdir /var/log/pgpool
touch /var/log/pgpool/pgpool_run.log

修改syslog配置

vi /etc/rsyslog.conf

修改

*.info;mail.none;authpriv.none;cron.none;LOCAL1.none  /var/log/messages
LOCAL1.*                                        /var/log/pgpool/pgpool_run.log

重新開機

systemctl restart rsyslog

6 配置pgpass

  為了允許資料庫的postgres使用者無需為流複制和線上恢複指定密碼，對于每個節點，在作業系統的postgres使用者的主目錄中建立.pgpass檔案，權限改為600。

su postgres
vi ~/.pgpass

編輯

# hostname:port:database:username:password
# In a standby server, a database field of replication matches streaming replication connections made to the master server.
【節點1的IP】:5432:postgres:postgres:123456
【節點2的IP】:5432:postgres:postgres:123456
【節點3的IP】:5432:postgres:postgres:123456

授權

chmod 600 ~/.pgpass

7 安裝Pgpool

  root使用者執行，在所有節點安裝和配置Pgpool；為了使用線上恢複功能，需要pgpool_recovery、pgpool_remote_start、pgpool_switch_xlog等函數，是以需要安裝pgpool_recovery。

  注意：安裝pgpool_recovery需要先安裝postgresql-devel。

mkdir /usr/local/pgpool
cd /usr/local/pgpool
tar -zxvf pgpool-II-4.0.6.tar.gz
cd /usr/local/pgpool/pgpool-II-4.0.6
./configure --with-pgsql=/usr/local/pgsql
make && make install
cd /usr/local/pgpool/pgpool-II-4.0.6/src/sql/pgpool-recovery
chown -R postgres /usr/local/pgsql
make && make install
cp /usr/local/etc/pgpool.conf.sample-stream /usr/local/etc/pgpool.conf

8 在節點1的Postgresql上安裝pgpool_recovery擴充

  使用PG建立資料庫時，實際上是通過複制一個現有的資料庫來工作的。預設情況下，它複制名為template1的标準系統資料庫。

psql template1 -c "CREATE EXTENSION pgpool_recovery"
psql postgres -c "CREATE EXTENSION pgpool_recovery"

9 編寫failover腳本

在所有節點上建立配置檔案，并賦予執行權限

vi /usr/local/pgpool/failover_stream.sh

編輯腳本（注意和pgpool.conf中failover_command參數的聯系），如果primary當機，晉升standby；如果standby當機，僅記錄日志。

#!/bin/bash
# This script is run by failover_command.

set -o xtrace
logger -i -p local1.info failover_command begin

# Special values:
#   %d = node id
#   %h = host name
#   %p = port number
#   %D = database cluster path
#   %m = new master node id
#   %H = hostname of the new master node
#   %M = old master node id
#   %P = old primary node id
#   %r = new master port number
#   %R = new master database cluster path
#   %% = '%' character

FAILED_NODE_ID="$1"
FAILED_NODE_HOST="$2"
FAILED_NODE_PORT="$3"
FAILED_NODE_PGDATA="$4"
NEW_MASTER_NODE_ID="$5"
NEW_MASTER_NODE_HOST="$6"
OLD_MASTER_NODE_ID="$7"
OLD_PRIMARY_NODE_ID="$8"
NEW_MASTER_NODE_PORT="$9"
NEW_MASTER_NODE_PGDATA="${10}"

PGHOME=/usr/local/pgsql

logger -i -p local1.info failover.sh: start: failed_node_id=${FAILED_NODE_ID} old_primary_node_id=${OLD_PRIMARY_NODE_ID} \
    failed_host=${FAILED_NODE_HOST} new_master_host=${NEW_MASTER_NODE_HOST}

## Test passwrodless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null [email protected]${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa ls /tmp > /dev/null

if [ $? -ne 0 ]; then
    logger -i -p local1.error failover.sh: passwrodless SSH to [email protected]${NEW_MASTER_NODE_HOST} failed. Please setup passwrodless SSH.
    exit 1
fi

# If standby node is down, skip failover.
if [ ${FAILED_NODE_ID} -ne ${OLD_PRIMARY_NODE_ID} ]; then
    logger -i -p local1.info failover.sh: Standby node is down. Skipping failover.
    exit 0
fi

# Promote standby node.
logger -i -p local1.info failover.sh: Primary node is down, promote standby node [email protected]${NEW_MASTER_NODE_HOST}.

ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \
[email protected]${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa ${PGHOME}/bin/pg_ctl -D ${NEW_MASTER_NODE_PGDATA} -w promote

if [ $? -ne 0 ]; then
    logger -i -p local1.error failover.sh: new_master_host=${NEW_MASTER_NODE_HOST} promote failed
    exit 1
fi

logger -i -p local1.info failover.sh: end: new_master_node_id=$NEW_MASTER_NODE_ID started as the primary node
exit 0

授權

chmod +x /usr/local/pgpool/failover_stream.sh

10 編寫follow_master腳本

  使仍在運作的standby從新的primary上同步資料，三個及以上節點數量時，需要設定此腳本

vi /usr/local/pgpool/follow_master.sh

編輯腳本

#!/bin/bash
# This script is run after failover_command to synchronize the Standby with the new Primary.

set -o xtrace
logger -i -p local1.info follow_master begin

# special values:  %d = node id
#                  %h = host name
#                  %p = port number
#                  %D = database cluster path
#                  %m = new master node id
#                  %M = old master node id
#                  %H = new master node host name
#                  %P = old primary node id
#                  %R = new master database cluster path
#                  %r = new master port number
#                  %% = '%' character
FAILED_NODE_ID="$1"
FAILED_NODE_HOST="$2"
FAILED_NODE_PORT="$3"
FAILED_NODE_PGDATA="$4"
NEW_MASTER_NODE_ID="$5"
NEW_MASTER_NODE_HOST="$6"
OLD_MASTER_NODE_ID="$7"
OLD_PRIMARY_NODE_ID="$8"
NEW_MASTER_NODE_PORT="$9"
NEW_MASTER_NODE_PGDATA="${10}"

PGHOME=/usr/local/pgsql
ARCHIVEDIR=/home/pg/data/archivedir
REPL_USER=postgres
PCP_USER=postgres
PGPOOL_PATH=/usr/local/bin
PCP_PORT=9898


# Recovery the slave from the new primary
logger -i -p local1.info follow_master.sh: start: synchronize the Standby node [email protected]${FAILED_NODE_HOST} with the new Primary node [email protected]${NEW_MASTER_NODE_HOST}

## Test passwrodless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null [email protected]${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa ls /tmp > /dev/null

if [ $? -ne 0 ]; then
    logger -i -p local1.error follow_master.sh: passwrodless SSH to postgr[email protected]${NEW_MASTER_NODE_HOST} failed. Please setup passwrodless SSH.
    exit 1
fi

## Get PostgreSQL major version
PGVERSION=`${PGHOME}/bin/initdb -V | awk '{print $3}' | sed 's/\..*//' | sed 's/\([0-9]*\)[a-zA-Z].*/\1/'`

if [ ${PGVERSION} -ge 12 ]; then
    RECOVERYCONF=${FAILED_NODE_PGDATA}/myrecovery.conf
else
    RECOVERYCONF=${FAILED_NODE_PGDATA}/recovery.conf
fi

# Check the status of standby
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \
    [email protected]${FAILED_NODE_HOST} -i ~/.ssh/id_rsa ${PGHOME}/bin/pg_ctl -w -D ${FAILED_NODE_PGDATA} status

## If Standby is running, run pg_basebackup.
if [ $? -eq 0 ]; then

    # Execute pg_basebackup
    ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null [email protected]${FAILED_NODE_HOST} -i ~/.ssh/id_rsa "

        set -o errexit
        ${PGHOME}/bin/pg_ctl -w -m f -D ${FAILED_NODE_PGDATA} stop

        rm -rf ${FAILED_NODE_PGDATA}
        rm -rf ${ARCHIVEDIR}/*

        ${PGHOME}/bin/pg_basebackup -h ${NEW_MASTER_NODE_HOST} -U ${REPL_USER} -p ${NEW_MASTER_NODE_PORT} -D ${FAILED_NODE_PGDATA} -X stream

        if [ ${PGVERSION} -ge 12 ]; then
            sed -i -e \"\\\$ainclude_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'\" \
                   -e \"/^include_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'/d\" ${FAILED_NODE_PGDATA}/postgresql.conf
        fi
      
        cat > ${RECOVERYCONF} << EOT
primary_conninfo = 'host=${NEW_MASTER_NODE_HOST} port=${NEW_MASTER_NODE_PORT} user=${REPL_USER} passfile=''/home/postgres/.pgpass'''
recovery_target_timeline = 'latest'
restore_command = 'scp ${NEW_MASTER_NODE_HOST}:${ARCHIVEDIR}/%f %p'
EOT

        if [ ${PGVERSION} -ge 12 ]; then
            touch ${FAILED_NODE_PGDATA}/standby.signal
        else
            echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}
        fi
    "

    if [ $? -ne 0 ]; then
        logger -i -p local1.error follow_master.sh: end: pg_basebackup failed
        exit 1
    fi

    # start Standby node on ${FAILED_NODE_HOST}
    ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \
            [email protected]${FAILED_NODE_HOST} -i ~/.ssh/id_rsa $PGHOME/bin/pg_ctl -l /dev/null -w -D ${FAILED_NODE_PGDATA} start

    # If start Standby successfully, attach this node
    if [ $? -eq 0 ]; then

        # Run pcp_attact_node to attach Standby node to Pgpool-II.
        ${PGPOOL_PATH}/pcp_attach_node -w -h localhost -U $PCP_USER -p ${PCP_PORT} -n ${FAILED_NODE_ID}

        if [ $? -ne 0 ]; then
            logger -i -p local1.error follow_master.sh: end: pcp_attach_node failed
            exit 1
        fi

    # If start Standby failed, drop replication slot "${FAILED_NODE_HOST}"
    else
        logger -i -p local1.error follow_master.sh: end: follow master command failed
        exit 1
    fi

else
    logger -i -p local1.info follow_master.sh: failed_nod_id=${FAILED_NODE_ID} is not running. skipping follow master command
    exit 0
fi

logger -i -p local1.info follow_master.sh: end: follow master command complete
exit 0

授權

chmod +x /usr/local/pgpool/follow_master.sh

11 編寫recovery_1st_stage腳本

  pcp_recovery_node會用到recovery_1st_stage和pgpool_remote_start。

  recovery_1st_stage會使用pg_basebackup從現存的主節點同步資料，初始化資料庫，更新recovery配置檔案。

vi /home/pg/data/recovery_1st_stage.sh

編輯

#!/bin/bash
# This script is executed by "recovery_1st_stage" to recovery a Standby node.

set -o xtrace
logger -i -p local1.info recovery_1st_stage begin

PRIMARY_NODE_PGDATA="$1"
DEST_NODE_HOST="$2"
DEST_NODE_PGDATA="$3"
PRIMARY_NODE_PORT="$4"
DEST_NODE_PORT=5432

# 這裡是hostname變量，是以需要配置DNS
PRIMARY_NODE_HOST=$(hostname)
PGHOME=/usr/local/pgsql
ARCHIVEDIR=/home/pg/data/archivedir
REPL_USER=postgres

logger -i -p local1.info recovery_1st_stage: start: pg_basebackup for Standby node [email protected]{$DEST_NODE_HOST}

## Test passwrodless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null [email protected]${DEST_NODE_HOST} -i ~/.ssh/id_rsa ls /tmp > /dev/null

if [ $? -ne 0 ]; then
    logger -i -p local1.error recovery_1st_stage: passwrodless SSH to [email protected]${DEST_NODE_HOST} failed. Please setup passwrodless SSH.
    exit 1
fi

## Get PostgreSQL major version
PGVERSION=`${PGHOME}/bin/initdb -V | awk '{print $3}' | sed 's/\..*//' | sed 's/\([0-9]*\)[a-zA-Z].*/\1/'`
if [ $PGVERSION -ge 12 ]; then
    RECOVERYCONF=${DEST_NODE_PGDATA}/myrecovery.conf
else
    RECOVERYCONF=${DEST_NODE_PGDATA}/recovery.conf
fi

## Execute pg_basebackup to recovery Standby node
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null [email protected]$DEST_NODE_HOST -i ~/.ssh/id_rsa "

    set -o errexit

    rm -rf $DEST_NODE_PGDATA
    rm -rf $ARCHIVEDIR/*

    ${PGHOME}/bin/pg_basebackup -h ${PRIMARY_NODE_HOST} -U ${REPL_USER} -p ${PRIMARY_NODE_PORT} -D ${DEST_NODE_PGDATA} -X stream

    if [ ${PGVERSION} -ge 12 ]; then
        sed -i -e \"\\\$ainclude_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'\" \
               -e \"/^include_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'/d\" ${DEST_NODE_PGDATA}/postgresql.conf
    fi

    cat > ${RECOVERYCONF} << EOT
primary_conninfo = 'host=${PRIMARY_NODE_HOST} port=${PRIMARY_NODE_PORT} user=${REPL_USER} passfile=''/home/postgres/.pgpass'''
recovery_target_timeline = 'latest'
restore_command = 'scp ${PRIMARY_NODE_HOST}:${ARCHIVEDIR}/%f %p'
EOT

    if [ ${PGVERSION} -ge 12 ]; then
        touch ${DEST_NODE_PGDATA}/standby.signal
    else
        echo \"standby_mode = 'on'\" >> ${RECOVERYCONF}
    fi

    sed -i \"s/#*port = .*/port = ${DEST_NODE_PORT}/\" ${DEST_NODE_PGDATA}/postgresql.conf
"

if [ $? -ne 0 ]; then
    logger -i -p local1.error recovery_1st_stage: end: pg_basebackup failed. online recovery failed
    exit 1
fi

logger -i -p local1.info recovery_1st_stage: end: recovery_1st_stage complete
exit 0

授權

chmod +x /home/pg/data/recovery_1st_stage.sh

12 編寫pgpool_remote_start腳本（注意沒有sh擴充名）

pgpool_remote_start負責啟動資料庫

vi /home/pg/data/pgpool_remote_start

編輯

#!/bin/bash
# This script is run after recovery_1st_stage to start Standby node.

set -o xtrace
logger -i -p local1.info pgpool_remote_start begin

PGHOME=/usr/local/pgsql
DEST_NODE_HOST="$1"
DEST_NODE_PGDATA="$2"


logger -i -p local1.info pgpool_remote_start: start: remote start Standby node [email protected]$DEST_NODE_HOST

## Test passwrodless SSH
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null [email protected]${DEST_NODE_HOST} -i ~/.ssh/id_rsa ls /tmp > /dev/null

if [ $? -ne 0 ]; then
    logger -i -p local1.error pgpool_remote_start: passwrodless SSH to [email protected]${DEST_NODE_HOST} failed. Please setup passwrodless SSH.
    exit 1
fi

## Start Standby node
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null [email protected]$DEST_NODE_HOST -i ~/.ssh/id_rsa "
$PGHOME/bin/pg_ctl -l /dev/null -w -D $DEST_NODE_PGDATA start
"

if [ $? -ne 0 ]; then
    logger -i -p local1.error pgpool_remote_start: [email protected]$DEST_NODE_HOST start failed.
    exit 1
fi

logger -i -p local1.info pgpool_remote_start: end: [email protected]$DEST_NODE_HOST started successfully.
exit 0

授權

chmod +x /home/pg/data/pgpool_remote_start.sh

13 配置pgpool.conf

複制Pgpool配置模闆檔案

cp /usr/local/etc/pgpool.conf.sample-stream /usr/local/etc/pgpool.conf
vi /usr/local/etc/pgpool.conf

修改

#CONNECTIONS
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898

#- Backend Connection Settings -

backend_hostname0 ='【叢集内第1個節點的IP，多個節點配置一緻】'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/home/pg/data'
backend_flag0 = ‘ALLOW_TO_FAILOVER’

backend_hostname1 ='【叢集内第2個節點的IP，多個節點配置一緻】'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/home/pg/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 ='【叢集内第3個節點的IP，多個節點配置一緻】'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/home/pg/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

#- Authentication -
enable_pool_hba = on
pool_passwd ='pool_passwd'

#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------

# - Concurrent session and pool size -

num_init_children = 400
max_pool = 4

#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------
log_destination = 'syslog'
syslog_facility = 'LOCAL1'

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

pid_file_name = '/usr/local/pgpool/pgpool.pid'

#------------------------------------------------------------------------------
# REPLICATION MODE
#------------------------------------------------------------------------------
replication_mode = off
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on
#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = on
master_slave_sub_mode = 'stream'
# - Streaming -
sr_check_period = 5
sr_check_user = 'postgres'
sr_check_password ='123456'
sr_check_database = 'postgres'
follow_master_command = '/usr/local/pgpool/follow_master.sh %d %h %p %D %m %H %M %P %r %R'
#------------------------------------------------------------------------------
#HEALTH CHECK
#------------------------------------------------------------------------------

health_check_period = 10
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password ='123456' 
health_check_database = 'postgres'

#------------------------------------------------------------------------------
#FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
# 注意failover_stream.sh後的參數，要與failover_stream.sh中的腳本比對
failover_command = '/usr/local/pgpool/failover_stream.sh %d %h %p %D %m %H %M %P %r %R'

#------------------------------------------------------------------------------
#WATCHDOG
#------------------------------------------------------------------------------
use_watchdog = on
wd_hostname = '【本機IP】'
wd_port = 9000
delegate_IP = '【虛拟IP】'
if_cmd_path = '/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev 【網卡名稱（通過ip addr檢視）】 label 【網卡名稱（通過ip addr檢視）】:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev 【網卡名稱（通過ip addr檢視）】'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = '【其他節點1的IP】'
heartbeat_destination_port0 = 9694
heartbeat_device0 = '【網卡名稱（通過ip addr檢視）】'
heartbeat_destination1 = '【其他節點2的IP】'
heartbeat_destination_port1 = 9694
heartbeat_device1 = '【網卡名稱（通過ip addr檢視）】'

#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------

recovery_user = 'postgres'
recovery_password = '123456'
recovery_1st_stage_command = 'recovery_1st_stage.sh'
#- Other pgpool Connection Settings -

other_pgpool_hostname0 ='【其他節點1的IP】'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
other_pgpool_hostname1 ='【其他節點2的IP】'
other_pgpool_port1 = 9999
other_wd_port1 = 9000

14 生成pool_passwd

/usr/local/bin/pg_md5 -p --md5auth --username=postgres pool_passwd
chown postgres:postgres /usr/local/etc/pool_passwd

操作完成後，在/usr/local/etc目錄下會生成一個pool_passwd檔案，該檔案在pgpool.conf中通過“pool_passwd”選項配置。

15 配置pool_hba.conf

  用戶端認證配置檔案，在pgpool.conf中配置“enable_pool_hba = on”時，将使用該檔案認證用戶端連接配接。

root使用者執行

cp /usr/local/etc/pool_hba.conf.sample /usr/local/etc/pool_hba.conf
chown postgres:postgres /usr/local/etc/pool_hba.conf
vi /usr/local/etc/pool_hba.conf

修改

local all all md5
host all all 0.0.0.0/0 md5
host all all 0/0 md5

16 配置pcp.conf

  Pgpool-II為管理者提供了執行管理操作的接口，例如擷取Pgpool-II狀态或遠端終止Pgpool-II程序。pcp.conf是此接口用于身份驗證的使用者名/密碼檔案。所有操作模式都需要設定pcp.conf檔案。資料庫中使用PCP進行健康檢測和複制延遲檢測的使用者，其使用者名和密碼必須在pcp.conf中聲明。

16.1 生成密碼摘要

/usr/local/bin/pg_md5 【密碼】

16.2 生成和配置檔案

cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf
vi /usr/local/etc/pcp.conf

編輯

# Example:
postgres:【上一步生成的密碼摘要】

17 設定PCP密碼配置檔案

  由于follow_master_command腳本必須在不輸入密碼的情況下執行PCP指令，是以我們在Pgpool-II啟動使用者的主目錄中建立.pcppass。

echo 'localhost:9898:【資料庫使用者名稱】:【資料庫使用者密碼】' > ~/.pcppass
chmod 600 ~/.pcppass

18 允許postgres使用者以root身份執行相關指令

root使用者執行

chmod u+s /sbin/ip
chmod u+s /sbin/arping

19 配置開機自啟

vi /etc/systemd/system/pgpool.service

編輯

[Unit]
Description=Pgpool-II
After=syslog.target network.target

[Service]
Type=forking

User=root

ExecStart=/usr/local/bin/pgpool -C -D
ExecStop=/usr/local/bin/pgpool -m fast stop
ExecReload=/usr/local/bin/pgpool reload
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=30s
TimeoutSec=0

[Install]
WantedBy=multi-user.target

開機自啟

systemctl enable pgpool

20 在所有節點啟動pgpool

systemctl start pgpool

21 通過pcp_recovery_node初始化備節點資料庫

  pcp_recovery_node會先在primary節點執行SELECT pgpool_recovery(‘recovery_1st_stage’, ‘【需要啟動的standby的IP】’, ‘【資料目錄】’, ‘5432’, 【節點編号】)，可以通過查詢PG日志檢視異常資訊；然後執行recovery_1st_stage.sh和pgpool_remote_start.sh

pcp_recovery_node -h 【主節點的IP】 -p 9898 -U 【PCP使用者名（資料庫）】 -n 【node-id】

22 檢視節點狀态

22.1 Pgpool節點狀态

pcp_watchdog_info -h 【虛拟IP】 -p 9898 -U 【Pgpool使用者】

22.2 資料庫節點狀态

通過虛拟IP的9999端口開啟PostgreSQL資料庫指令行，執行

注意：如果使用navicat的指令行功能，可能需要關閉并重新開機指令行界面（建立新的session）才能看到正确的節點狀态

23 高可用測試

23.1 Pgpool 高可用測試

  • master停止pgpool服務，某個standby接管master服務
  • 重新開機之前停止的master的pgpool服務，節點狀态變為standby
  • standby停止pgpool服務，節點狀态變為shutdown
  • 重新開機standby，節點狀态恢複為standby

23.2 資料庫高可用測試

  • primary停止資料庫服務，某個standby晉升為primary，其他standby執行follow

    master，從新的primary同步資料

  • 執行pcp_recovery_node，将之前停止服務的primary重新開機
  • standby停止資料庫服務，其他節點無變化
  • 執行pcp_recovery_node，将之前停止服務的standby重新開機

24 負載均衡測試

  使用pgbench進行負載均衡測試

24.1 資料初始化

pgbench -i -h 【主節點IP】 -p 5432 postgres
24.2	測試
```shell
pgbench -c 【用戶端數量】 -T 【持續時間（秒）】 -h 【叢集虛拟IP】 postgres -p 9999 -U postgres -S（表示隻讀測試）

24.3 使用show pool_nodes檢視查詢請求在各節點的負載

25 非正常關閉pgpool後的故障恢複

  • 非正常關閉pgpool，要清理緩存檔案： 
rm /tmp/.s.PGSQL.9999
rm /tmp/.s.PGPOOLWD_CMD.9000
rm /tmp/.s.PGSQL.9898

  • 如果啟動第一個節點狀态為standby，重新開機pgpool并使用

    pcp_attach_node加入該節點

# PostgreSQL postgresql
上一篇: postgresql yum安裝和配置
下一篇: 搭建postgresql叢集的問題彙總

繼續閱讀