目錄
- 架構(gòu)圖
- 部署詳情
- postgresql的安裝與配置
- pgpool的安裝與配置
寫在安裝前
postgresql是一款很強(qiáng)大的數(shù)據(jù)庫屹篓，具體有多強(qiáng)大呢靶瘸，請谷歌晃危。。梨水。
網(wǎng)上的相關(guān)資料不是很多拭荤，參考了大神們的文檔，結(jié)合自己趟了的坑疫诽，寫了pg集群的安裝文檔舅世。可能有一些參數(shù)沒有配置好奇徒，希望大神們指出雏亚，謝謝。參照本文檔摩钙，基本能實(shí)現(xiàn)pg的高可用罢低，稍后會(huì)寫出測試文檔。
架構(gòu)圖
部署詳情
| ip | host | 系統(tǒng) | vip | 部署 |
|---|---|---|---|---|
| 192.168.123.180 | master | CentOS 6.8 | 192.168.123.183 | postgresql胖笛、pgpool |
| 192.168.123.181 | slave1 | CentOS 6.8 | 192.168.123.183 | postgresql网持、pgpool |
| 192.168.123.182 | slave2 | CentOS 6.8 | 192.168.123.183 | postgresql |
postgresql的安裝
1、安裝
1长踊、Rpm包下載地址:
https://yum.postgresql.org/rpmchart.php
下載下面的包：
postgresql95-libs-9.5.8-1PGDG.rhel6.x86_64.rpm
postgresql95-contrib-9.5.8-1PGDG.rhel6.x86_64.rpm
postgresql95-9.5.8-1PGDG.rhel6.x86_64.rpm
postgresql95-server-9.5.8-1PGDG.rhel6.x86_64.rpm
2功舀、安裝：
yum install -y postgresql95-libs-9.5.8-1PGDG.rhel6.x86_64.rpm
yum install -y postgresql95-contrib-9.5.8-1PGDG.rhel6.x86_64.rpm
yum install -y postgresql95-9.5.8-1PGDG.rhel6.x86_64.rpm
yum install -y postgresql95-server-9.5.8-1PGDG.rhel6.x86_64.rpm
3、初始化db：
service postgresql-9.5 initdb
4身弊、設(shè)置為開機(jī)啟動(dòng)：
chkconfig postgresql-9.5 on
5辟汰、安裝的目錄：
/var/lib/pgsql/9.5
配置白名單與流復(fù)制
1、 master的配置
PostgreSQL流復(fù)制默認(rèn)是異步的佑刷。在主服務(wù)器上提交事務(wù)和從服務(wù)器上變化可見之間有一個(gè)小的延遲莉擒，這個(gè)延遲遠(yuǎn)小于基于文件日志傳送，通常1秒能完成瘫絮。如果主服務(wù)器突然崩潰涨冀，可能會(huì)有少量數(shù)據(jù)丟失。
同步復(fù)制必須等主服務(wù)器和從服務(wù)器都寫完WAL后才能提交事務(wù)麦萤。這樣在一定程度上會(huì)增加事務(wù)的響應(yīng)時(shí)間鹿鳖。
配置同步復(fù)制僅需要一個(gè)額外的配置步驟： synchronous_standby_names必須設(shè)置為一個(gè)非空值扁眯。synchronous_commit也必須設(shè)置為on。
這里部署的是異步的流復(fù)制
1.2翅帜、配置白名單:
vim /var/lib/pgsql/9.5/data/pg_hba.conf
在配置文件最后加上：
host all all 192.168.123.180/32 trust
host all all 192.168.123.181/32 trust
host all all 192.168.123.182/32 trust
host replication replica 192.168.123.181/32 trust
host replication replica 192.168.123.182/32 trust
保存退出
我這里由于是內(nèi)網(wǎng)姻檀，全部用的trust，如果要用密碼涝滴，請修改為passwd
1.3绣版、修改配置文件:
vim /var/lib/pgsql/9.5/data/postgresql.conf
data_directory = '/app/pgsql/data' #自定義data目錄
listen_addresses = '*' #監(jiān)聽所有ip
archive_mode = on #允許歸檔
archive_command = 'cp %p /app/pgsql/pg_archive/%f' #使用命令歸檔
wal_level = hot_standby #選擇熱備
max_wal_senders = 16 #最多多少個(gè)流復(fù)制鏈接
wal_keep_segments = 256 #流復(fù)制保留最多的xlog數(shù)
wal_sender_timeout = 60s #流復(fù)制主機(jī)發(fā)送數(shù)據(jù)超時(shí)時(shí)間
max_connections = 99 #從庫的max_connections必須大于主庫的
1.4、創(chuàng)建data目錄歼疮，賦權(quán)并修改啟動(dòng)文件
mkdir -p /app/pgsql/data && chown postgres:postgres /app/pgsql/data
mkdir -p /app/pgsql/pg_archive && chown postgres:postgres /app/pgsql/pg_archive
cd /app/pgsql && chmod 700 data
cd /app/pgsql && chmod 700 pg_archive
1.5杂抽、修改啟動(dòng)文件
vim /etc/init.d/postgresql-9.5
PGDATA=/app/pgsql/data
1.6、把原data目錄下的文件copy到新的data的目錄下,并改變屬組：
cp -r /var/lib/pgsql/9.5/data/* /app/pgsql/data/
cd /app/pgsql && chown -R postgres:postgres data
1.7韩脏、啟動(dòng)
service postgresql-9.5 start
以后修改配置文件就在/app/pgsql/data下面修改
1.8缩麸、創(chuàng)建一個(gè)數(shù)據(jù)庫用戶進(jìn)行主從同步。創(chuàng)建用戶replica赡矢，并賦予登錄和復(fù)制的權(quán)限
登陸到數(shù)據(jù)庫里：
su postgres
psql
CREATE ROLE replica login replication encrypted password 'replica';
防火墻開放5432端口
在從庫上：
psql -h 192.168.123.180 -U postgres
登陸主庫成功
2杭朱、從庫的配置
2.1、自定義data目錄:
mkdir -p /app/pgsql/data/&&chmod 700 /app/pgsql/data/
chown -R postgres:postgres pgsql
mkdir /app/pgsql/pg_archive
cd /app/pgsql
chmod 700 pg_archive && chown postgres:postgres pg_archive/
2.2吹散、切換用戶:
su – postgres
2.3弧械、備份數(shù)據(jù)：
pg_basebackup -h 192.168.123.180 -U replica -D /app/pgsql/data -X stream –P
2.4、修改啟動(dòng)文件:
vim /etc/init.d/postgresql-9.5
PGDATA=/app/pgsql/data
2.5空民、配置recovery.conf
su postgres
cp /usr/pgsql-9.5/share/recovery.conf.sample /app/pgsql/data/recovery.conf
vim /app/pgsql/data/recovery.conf
standby_mode = on \#該節(jié)點(diǎn)為從
primary_conninfo = 'host=192.168.123.180 port=5432 user=replica password=replica'
\#主服務(wù)器的ip梦谜、user
recovery_target_timeline = 'latest'
trigger_file = '/tmp/trigger_file0'
2.6、配置postgresql.conf
vim /app/pgsql/data/postgresql.conf
max_connections = 999 #大于主的連接數(shù)
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
2.7袭景、重啟服務(wù)：
service postgresql-9.5 restart
驗(yàn)證流復(fù)制
3.1、在master上登陸psql
查看狀態(tài):
select client_addr,sync_state from pg_stat_replication;
3.2闭树、創(chuàng)建test庫
Create database test;
3.3耸棒、slave上登陸psql
查看庫
playboy => \l
發(fā)現(xiàn)已同步。
pgpool的安裝
pgpool-II是PostgreSQL服務(wù)器之間一種有效的中間件和PostgreSQL數(shù)據(jù)庫客戶端报辱。它提供了以下功能与殃。
連接池
pgpool-II保存到PostgreSQL服務(wù)器的連接，當(dāng)一個(gè)相同新連接(如用戶名碍现、數(shù)據(jù)庫幅疼、協(xié)議版本)進(jìn)來時(shí)，重用他們昼接。它減少了連接開銷爽篷，提高了系統(tǒng)的整體吞吐量。
復(fù)制
pgpool-II可以管理多個(gè)PostgreSQL服務(wù)器慢睡。使用復(fù)制功能可以使2個(gè)或更多的物理磁盤上創(chuàng)建一個(gè)實(shí)時(shí)備份逐工，這樣服務(wù)不會(huì)因服務(wù)器的磁盤故障而中斷铡溪。
負(fù)載平衡
如果數(shù)據(jù)庫是復(fù)制的，在任何服務(wù)器上執(zhí)行一個(gè)SELECT查詢會(huì)返回相同的結(jié)果泪喊。pgpool-II復(fù)制特性的優(yōu)勢在于減少每個(gè)PostgreSQL服務(wù)器上的負(fù)載棕硫，因?yàn)樗梢允褂梅植荚诙鄠€(gè)服務(wù)器之間進(jìn)行SELECT查詢，從而提高系統(tǒng)的整體吞吐量袒啼。最好是查詢和PostgreSQL服務(wù)器數(shù)量成一定比例哈扮，多用戶同時(shí)執(zhí)行多查詢達(dá)到負(fù)載均衡最好的效果。
限制連接數(shù)
PostgreSQL的最大并發(fā)連接數(shù)有一定限制的蚓再，當(dāng)超過限制的連接數(shù)后滑肉，連接會(huì)被拒絕。然而对途，設(shè)置增加最大連接數(shù)又會(huì)增加資源消耗赦邻，影響系統(tǒng)性能。pgpool-II也有最大連接數(shù)限制实檀，但超過的連接進(jìn)來時(shí)是進(jìn)行立即排隊(duì)惶洲，而不是返回一個(gè)錯(cuò)誤。
pgpool-II交互PostgreSQL的后端和前端協(xié)議時(shí)膳犹，起著繼電器的作用恬吕。因此，數(shù)據(jù)庫應(yīng)用程序(前端)認(rèn)為pgpool-II是真實(shí)的PostgreSQL服務(wù)器须床，服務(wù)器(后端)認(rèn)為pgpool-II是它的客戶端之一铐料。因?yàn)閜gpool-II在服務(wù)器和客戶端是透明的，所以pgpool-II可以使用現(xiàn)有的數(shù)據(jù)庫應(yīng)用程序而做到幾乎不修改它們豺旬。
版本：3.6
機(jī)器：192.168.123.180
192.168.123.181
4.1钠惩、免密碼登陸
安裝之前先配置密鑰使master和slave1這兩臺(tái)虛擬機(jī)的postgres用戶能免密連接
先修改postgres的密碼，在root用戶下
passwd postgres
新密碼123456
Master到slave1的免密碼登陸：
在master上切換至postgres用戶族阅，生成密鑰
su postgres ssh-keygen -t rsa
然后全輸入回車
切換到postgres用戶：
su postgres ssh-copy-id -i /var/lib/pgsql/.ssh/id_rsa 192.168.123.181
然后ssh 192.168.123.181 成功篓跛，實(shí)現(xiàn)master到slave1的免密碼登陸
Slave1到master的免密碼登陸：
流程同上
4.2、安裝
Rpm包下載地址:
http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-6-x86_64/pgpool-II-pg95-3.6.0-1pgdg.rhel6.x86_64.rpm
安裝:
yum install pgpool-II-pg95-3.6.0-1pgdg.rhel6.x86_64.rpm
4.3坦刀、配置
Master的配置:
配置白名單：
要和pg_hba.conf登陸權(quán)限一致愧沟，這里由于是私有云，都用trust
vim /etc/pgpool-II/pool_hba.conf
在最后改成:
配置pcp管理工具密碼:
pg_md5 pwd
密碼加密
vim /etc/pgpool-II/pcp.conf
把剛才加密的密碼粘貼到文件里
配置系統(tǒng)命令權(quán)限:
Root用戶下:
chmod u+s /sbin/ifconfig &&chmod u+s /usr/sbin
配置中間件配置文件:
vim /etc/pgpool-II/pgpool.conf
# CONNECTIONS
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
# - Backend Connection Settings -
backend_hostname0 = 'master'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ' /app/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'slave'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ' /app/pgsql/data''
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = off
pool_passwd = 'pool_passwd'
# FILE LOCATIONS
pid_file_name = '/opt/pgpool/pgpool.pid'
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 5
sr_check_user = 'repuser'
sr_check_password = 'repuser'
sr_check_database = 'postgres'
#------------------------------------------------------------------------------
# HEALTH CHECK 健康檢查
#------------------------------------------------------------------------------
health_check_period = 10 # Health check period
# Disabled (0) by default
health_check_timeout = 20
# Health check timeout
# 0 means no timeout
health_check_user = ' repuser '
# Health check user
health_check_password = ' repuser ' #數(shù)據(jù)庫密碼
# Password for health check user
health_check_database = 'postgres'
#必須設(shè)置鲤遥，否則primary數(shù)據(jù)庫down了沐寺，pgpool不知道，不能及時(shí)切換盖奈。從庫流復(fù)制還在連接數(shù)據(jù)混坞，報(bào)連接失敗。
#只有下次使用pgpool登錄時(shí)卜朗，發(fā)現(xiàn)連接不上拔第，然后報(bào)錯(cuò)咕村，這時(shí)候，才知道掛了蚊俺，pgpool進(jìn)行切換懈涛。
#主備切換的命令行配置
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = ' /opt/pgpool/failover_stream.sh %d %H /tmp/trigger_file0 '
#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
# - Enabling -
use_watchdog = on
# - Watchdog communication Settings -
wd_hostname = 'master'
# Host name or IP address of this watchdog
# (change requires restart)
wd_port = 9000
# port number for watchdog service
# (change requires restart)
# - Virtual IP control Setting -
delegate_IP = ' 192.168.123.183 '
# delegate IP address
# If this is empty, virtual IP never bring up.
# (change requires restart)
if_cmd_path = '/sbin'
# path to the directory where if_up/down_cmd
# (change requires restart)
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
# startup delegate IP command
# (change requires restart)
if_down_cmd = 'ifconfig eth0:0 down'
# shutdown delegate IP command
# (change requires restart)
# -- heartbeat mode --
wd_heartbeat_port = 9694
# Port number for receiving heartbeat signal
# (change requires restart)
wd_heartbeat_keepalive = 2
# Interval time of sending heartbeat signal (sec)
# (change requires restart)
wd_heartbeat_deadtime = 30
# Deadtime interval for heartbeat signal (sec)
# (change requires restart)
heartbeat_destination0 = 'slave1'
# Host name or IP address of destination 0
# for sending heartbeat signal.
# (change requires restart)
heartbeat_destination_port0 = 9694
# Port number of destination 0 for sending
# heartbeat signal. Usually this is the
# same as wd_heartbeat_port.
# (change requires restart)
heartbeat_device0 = 'eth0'
# Name of NIC device (such like 'eth0')
# used for sending/receiving heartbeat
# signal to/from destination 0.
# This works only when this is not empty
# and pgpool has root privilege.
# (change requires restart)
# - Other pgpool Connection Settings -
other_pgpool_hostname0 = 'slave' #對(duì)端
# Host name or IP address to connect to for
# (change requires restart)
other_pgpool_port0 = 9999
# Port number for othet pgpool 0
# (change requires restart)
other_wd_port0 = 9000
# Port number for othet watchdog 0
# (change requires restart)
4.4、Slave1的配置：
配置白名單：
要和pg_hba.conf登陸權(quán)限一致泳猬，這里由于是私有云批钠，都用trust
vim /etc/pgpool-II/pool_hba.conf
在最后改成:
配置pcp管理工具密碼:
pg_md5 pwd
密碼加密
vim /etc/pgpool-II/pcp.conf
把剛才加密的密碼粘貼到文件里
配置系統(tǒng)命令權(quán)限:
Root用戶下:
chmod u+s /sbin/ifconfig &&chmod u+s /usr/sbin
配置中間件配置文件:
vim /etc/pgpool-II/pgpool.conf
# CONNECTIONS
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
# - Backend Connection Settings -
backend_hostname0 = 'master'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = ' /app/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'slave1'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/app/pgsql/data '
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = off
pool_passwd = 'pool_passwd'
# FILE LOCATIONS
pid_file_name = '/opt/pgpool/pgpool.pid'
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 5
sr_check_user = 'repuser'
sr_check_password = 'repuser'
sr_check_database = 'postgres'
#------------------------------------------------------------------------------
# HEALTH CHECK 健康檢查
#------------------------------------------------------------------------------
health_check_period = 10 # Health check period
# Disabled (0) by default
health_check_timeout = 20
# Health check timeout
# 0 means no timeout
health_check_user = ' repuser '
# Health check user
health_check_password = ' repuser ' #數(shù)據(jù)庫密碼
# Password for health check user
health_check_database = 'postgres'
#必須設(shè)置，否則primary數(shù)據(jù)庫down了得封，pgpool不知道埋心，不能及時(shí)切換。從庫流復(fù)制還在連接數(shù)據(jù)忙上，報(bào)連接失敗拷呆。
#只有下次使用pgpool登錄時(shí)，發(fā)現(xiàn)連接不上疫粥，然后報(bào)錯(cuò)茬斧，這時(shí)候，才知道掛了梗逮，pgpool進(jìn)行切換项秉。
#主備切換的命令行配置
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = '/opt/pgpool/failover_stream.sh %d %H /tmp/trigger_file0 '
#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
# - Enabling -
use_watchdog = on
# - Watchdog communication Settings -
wd_hostname = 'slave1' #本端
# Host name or IP address of this watchdog
# (change requires restart)
wd_port = 9000
# port number for watchdog service
# (change requires restart)
# - Virtual IP control Setting -
delegate_IP = '192.168.123.183'
# delegate IP address
# If this is empty, virtual IP never bring up.
# (change requires restart)
if_cmd_path = '/sbin'
# path to the directory where if_up/down_cmd exists
# (change requires restart)
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
# startup delegate IP command
# (change requires restart)
if_down_cmd = 'ifconfig eth0:0 down'
# shutdown delegate IP command
# (change requires restart)
# -- heartbeat mode --
wd_heartbeat_port = 9694
# Port number for receiving heartbeat signal
# (change requires restart)
wd_heartbeat_keepalive = 2
# Interval time of sending heartbeat signal (sec)
# (change requires restart)
wd_heartbeat_deadtime = 30
# Deadtime interval for heartbeat signal (sec)
# (change requires restart)
heartbeat_destination0 = 'master' #對(duì)端
# Host name or IP address of destination 0
# for sending heartbeat signal.
# (change requires restart)
heartbeat_destination_port0 = 9694
# Port number of destination 0 for sending
# heartbeat signal. Usually this is the
# same as wd_heartbeat_port.
# (change requires restart)
heartbeat_device0 = 'eth0'
# Name of NIC device (such like 'eth0')
# used for sending/receiving heartbeat
# signal to/from destination 0.
# This works only when this is not empty
# and pgpool has root privilege.
# (change requires restart)
# - Other pgpool Connection Settings -
other_pgpool_hostname0 = 'master' #對(duì)端
# Host name or IP address to connect to for
# (change requires restart)
other_pgpool_port0 = 9999
# Port number for othet pgpool 0
# (change requires restart)
other_wd_port0 = 9000
# Port number for othet watchdog 0
# (change requires restart)
下面在master和slave1這兩臺(tái)機(jī)器上
mkdir /opt/pgpool &&chown postgres:postgres /opt/pgpool
master上：
cd /opt/pgpool
vim failover_stream.sh
# Failover command for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
#
# If standby goes down, do nothing. If primary goes down, create a
# trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.
failed_node=192.168.123.180
new_master=192.168.123.181
trigger_file=$3
# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
exit 0;
fi
# Create the trigger file.
/usr/bin/ssh -T $new_master /bin/touch $trigger_file
exit 0;
保存退出,并賦權(quán)
chown postgres:postgres failover_stream.sh &&chmod 777 failover_stream.sh
slave1上：
cd /opt/pgpool
vim failover_stream.sh
#! /bin/sh
# Failover command for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
#
# If standby goes down, do nothing. If primary goes down, create a
# trigger file so that standby takes over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.
failed_node=192.168.123.181
new_master=192.168.123.180
trigger_file=$3
# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
exit 0;
fi
# Create the trigger file.
/usr/bin/ssh -T $new_master /bin/touch $trigger_file
exit 0;
保存退出并賦權(quán):
chown postgres:postgres failover_stream.sh &&chmod 777 failover_stream.sh
在master和salve1上創(chuàng)建日志文件:
mkdir /var/log/pgpool
chown -R postgres.postgres /var/log/pgpool
mkdir /var/run/pgpool
chown -R postgres.postgres /var/run/pgpool
配置host：
在master、slave1慷彤、slave2的/etc/hosts配置ip映射
192.168.123.180 master
192.168.123.181 slave1
192.168.123.182 slave2
在master上創(chuàng)建repusr賬號(hào):
su postgres
psql
create user repuser with password 'repuser';
防火墻開放：5432 9999 9898 9000 9694
4.5娄蔼、啟動(dòng)pgpool
先在master上啟動(dòng)pgpool：
pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 &
再在slave1上啟動(dòng)pgpool:
pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 &
Master上的ifconfig會(huì)多一個(gè)虛擬ip出來
快速停止pgpool的指令:
pgpool -m fast stop
連接虛擬ip進(jìn)入數(shù)據(jù)庫:
Su postgres
psql -h 192.168.123.183 -p 9999
查看集群節(jié)點(diǎn)狀態(tài)：
show pool_nodes;
寫在安裝后
寫文檔才發(fā)現(xiàn)，邊安裝邊趟坑邊記錄底哗，還要注意格式是多么愉（xin）快（ku）的事岁诉，本著開源精神，貼出來給要用到pg集群的小伙伴們參考跋选，本文侵刪
