由于某个项目前期资源紧张，使用的Postgresql单节点数据库；为解决项目数据库安全性问题，小蜗牛应要求整理Postgresql数据库主从高可用方案，以下操作为部署主从流复制测试过程，后续整理pgpool高可用、负载均衡方案。。
一、安装依赖
yum install -y gcc-c++ zlib-devel vim openssh-clients
二、安装PG
tar zxvf postgresql-9.3.4.tar.gz
mkdir /opt/pgsql-9.3
cd postgresql-9.3.4
./configure --prefix=/opt/pgsql-9.3/ --without-readline
make -j 2
make install
三、配置PG环境变量
vim /etc/profile
export PGHOME=/opt/pgsql-9.3
export PGDATA=/var/lib/pgsql/9.3/data/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib/
export PATH=$PGHOME/bin:$PATH:$HOME/bin
source /etc/profile
1、添加postgres用户
useradd postgres -d /var/lib/pgsql
四、配置PG主库
1、初始化PG数据库
su - postgres
initdb
2、启动PG数据库，并创建同步用户
pg_ctl -D /var/lib/pgsql/9.3/data/ -l logfile start
psql
postgres# CREATE ROLE replica login replication encrypted password 'replica';
3、配置用户允许远程登录
vi /var/lib/pgsql/9.3/data/pg_hba.conf
host replication replica 192.168.52.0/24 trust
4、修改PG主库配置，并重启
vi /var/lib/pgsql/9.3/data/postgresql.conf
listen_addresses = '*'
wal_level = hot_standby # 这个是设置主为wal的主机
max_wal_senders = 2 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 7 ＃ 设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
max_connections = 100 # 这个设置要注意下，从库的max_connections必须要大于主库的
pg_ctl -D /var/lib/pgsql/9.3/data/ -l logfile restart
五、配置PG备库
1、从库不用初始化，直接同步主库数据
su - postgres
pg_basebackup -F p --progress -D $PGDATA -h 192.168.52.101 -p 5432 -U replica --password
2、添加一个recovery.conf配置文件
vim $PGDATA/recovery.conf
standby_mode = on # 这个说明这台机器为从库
primary_conninfo = 'host=192.168.52.101 port=5432 user=replica password=replica' # 这个说明这台机器对应主库的信息
trigger_file = '/var/lib/pgsql/9.3/data/postgresql.trigger.192.168.52.101'
recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据
3、修改PG从库配置
vi $PGDATA/postgresql.conf
max_connections = 1000 ＃ 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 1s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈
六、验证是否正常同步
1、主库查看
$ psql
psql (9.3.4)
Type "help" for help.
postgres=# select client_addr,sync_state from pg_stat_replication;
client_addr | sync_state
----------------+------------
192.168.52.102 | async
(1 row)
postgres=#
2、通过查看进程
ps aux | grep postgres
主节点有如下进程
postgres 74442 0.0 0.2 258652 2424 ? Ss 15:02 0:00 postgres: wal sender process replica 192.168.52.102(58199) streaming 0/30101D0
从节点有如下进程
postgres 1676 0.0 0.0 113328 748 ? Ss 15:01 0:00 postgres: stats collector process
3、通过主库增删改查验证
postgres@master ~]$ psql
psql (9.3.4)
Type "help" for help.
postgres=# create table t_user(id int primary key,name varchar(30));
CREATE TABLE
postgres=# dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | t_user | table | postgres
(1 row)
postgres=# insert into t_user values(1,'kbson');
INSERT 0 1
postgres=# insert into t_user values(2,'kbsonlong');
INSERT 0 1
postgres=# select * from t_user;
id | name
----+-----------
1 | kbson
2 | kbsonlong
(2 rows)
postgres=#
从库查看是否同步
[postgres@slave ~]$ psql
psql (9.3.4)
Type "help" for help.
postgres=# dt
No relations found. ##最开始从库并没有表
postgres=# dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | t_user | table | postgres
(1 row)
postgres=# select * from t_user;
id | name
----+-----------
1 | kbson
2 | kbsonlong
(2 rows)
postgres=#
七、手动测试切换主从
1、手动停止主库，模拟主库宕机
[postgres@master data]$ pg_ctl stop
此时查看从库会提示连接不上主库
LOG: replication terminated by primary server
DETAIL: End of WAL reached on timeline 1 at 0/3000518.
FATAL: could not send end-of-streaming message to primary: no COPY in progress
LOG: record with zero length at 0/3000518
FATAL: could not connect to the primary server: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
FATAL: could not connect to the primary server: could not connect to server: Connection refused
Is the server running on host "192.168.52.101" and accepting
TCP/IP connections on port 5432?
FATAL: could not connect to the primary server: could not connect to server: Connection refused
Is the server running on host "192.168.52.101" and accepting
TCP/IP connections on port 5432?
2、从库手动生成触发文件
在从库生成一个触发文件，就是在配置从库中recovery.conf中配置的trigger_file
[postgres@kbsonlong data]$ touch /var/lib/pgsql/9.3/data/postgresql.trigger.5432
[postgres@kbsonlong data]$ ll /var/lib/pgsql/9.3/data/postgresql.trigger.5432
-rw-rw-r-- 1 postgres postgres 0 Jul 30 17:49 /var/lib/pgsql/9.3/data/postgresql.trigger.5432
此时日志会提示找到触发文件，自动切换为新的主库
LOG: trigger file found: /var/lib/pgsql/9.3/data/postgresql.trigger.5432
LOG: redo done at 0/30004B0
LOG: last completed transaction was at log time 2017-07-30 17:38:43.592351+08
LOG: selected new timeline ID: 2
LOG: archive recovery complete
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
3、新的主库生成测试数据
在新的主库写入测试数据，方便后面旧的主库恢复为从库是查看是否同步数据
postgres=# select * from t_user;
id | name
----+-----------
1 | kbson
2 | kbsonlong
(2 rows)
postgres=# insert into t_user values(3,'kbsonlong3');
insert into t_user values(4,'kbsonlong4');
insert into t_user values(5,'kbsonlong5'); INSERT 0 1
postgres=# INSERT 0 1
postgres=#
INSERT 0 1
postgres=# select * from t_user;
id | name
----+------------
1 | kbson
2 | kbsonlong
3 | kbsonlong3
4 | kbsonlong4
5 | kbsonlong5
(5 rows)
postgres=#
4、旧的主库恢复为新的从库
4.1 生成recovery.conf
[postgres@master data]$ cat recovery.conf |grep -v '^#'
standby_mode = on
primary_conninfo = 'host=192.168.52.102 port=5432 user=replica password=replica' ##注意host指向新的主库
trigger_file = '/var/lib/pgsql/9.3/data/postgresql.trigger.5432'
recovery_target_timeline = 'latest'
[postgres@master data]$
4.2 启动新的从库，并查看数据是否同步
pg_ctl start
[postgres@master data]$ psql
psql (9.3.4)
Type "help" for help.
postgres=# select * from t_user;
id | name
----+------------
1 | kbson
2 | kbsonlong
3 | kbsonlong3
4 | kbsonlong4
5 | kbsonlong5
(5 rows)
postgres=# insert into t_user values(6,'kbsonlong6');
ERROR: cannot execute INSERT in a read-only transaction
STATEMENT: insert into t_user values(6,'kbsonlong6');
ERROR: cannot execute INSERT in a read-only transaction
postgres=#
可以看到刚才在新的主库写入的测试数据已经同步，并且无法再次新的从库写入数据
八、配置启动脚本
PostgreSQL的开机自启动脚本位于PostgreSQL源码目录的contrib/start-scripts路径下
Linux文件即为linux系统上的启动脚本
1、修改linux文件属性，添加X属性
#chmod a+x linux
2、 复制linux文件到/etc/init.d目录下，更名为postgresql
#cp linux /etc/init.d/postgresql
3、修改/etc/init.d/postgresql文件的两个变量
prefix设置为postgresql的安装路径：/opt/pgsql-9.3
PGDATA设置为postgresql的数据目录路径：/var/libg/pgsql/9.3/data
4、执行service postgresql start，就可以启动PostgreSQL服务
#service postgresql start
5、设置postgresql服务开机自启动
#chkconfig --add postgresql
