PostgreSQL数据库主从部署

一、基础环境

二、部署主从

1、安装postgreSQL

主节点与从节点均安装postgreSQL，安装方法参考博客：CentOS7 上安装 Postgresql

2、配置主从

主节点（192.168.47.105）配置

（1）创建归档目录

[[email protected] data]# pwd
/usr/local/pgsql/data
[[email protected] data]# mkdir pg_archive

（2）创建一个数据库用户replica进行主从同步

postgres用户登录到数据库

[[email protected] ~]# cd /usr/local/pgsql/bin/
[[email protected] bin]# su postgres
#创建同步用户
[p[email protected] bin]$ ./psql 
psql (10.15)
Type "help" for help.

postgres=# create role replica login replication encrypted password '123456';
CREATE ROLE

（3）修改pg_hba.conf，增加两行配置，允许replica用户来同步

[[email protected] data]$ vi /usr/local/pgsql/data/pg_hba.conf

host    all             all             192.168.0.102/32        trust   #允许从服务器连接到主服务器
host    replication     replica         192.168.0.102/32        md5   #允许从服务器使用replica用户来复制

（4）修改postgresql.conf

[[email protected] data]$  vi /usr/local/pgsql/data/postgresql.conf
###
listen_addresses = '*'   #监听所有IP
archive_mode = on  #允许归档
archive_command = 'cp %p /usr/local/pgsql/data/pg_archive/%f'  #用该命令来归档logfile segment
wal_level = hot_standby 
max_wal_senders = 10 #这个设置可以最多有几个流复制连接
wal_keep_segments = 256 #设置流复制保留的最多的xlog数目
wal_sender_timeout = 60s #设置流复制主机发送数据的超时时间
max_connections = 100 #从库的max_connections必须要大于主库

（5）配置完两个文件后重启数据库

[[email protected] bin]# sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile restart

（6）测试从服务器能否连接主服务器数据库

从节点操作

[[email protected] bin]# su postgres
[[email protected] bin]$ pwd
/usr/local/pgsql/bin
[[email protected] bin]$ ./psql -h 192.168.47.105 -U postgres

从节点（192.168.0.102）配置

（1）从主节点拷贝数据到从节点

#关闭当前从节点的数据库
[[email protected] bin]$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l /usr/local/pgsql/logs/postgres.log stop #停数据库
[[email protected] pgsql]$ rm -rf /usr/local/pgsql/data/*  #清空从节点数据

从主节点拷贝数据到从节点（基础备份）

[[email protected] pgsql]$ /usr/local/pgsql/bin/pg_basebackup -h 192.168.0.101 -U replica -D /usr/local/pgsql/data/ -X stream -P # 从主节点拷贝数据到从节点（基础备份）

从节点创建目录

[[email protected] pgsql]$ pwd
/usr/local/pgsql
[[email protected] pgsql]$ mkdir data/pg_archive

（2）配置recovery.conf

[[email protected] pgsql]$ su postgres
[[email protected] pgsql]$ cp /usr/local/pgsql/share/recovery.conf.sample /usr/local/pgsql/data/recovery.conf 

拷贝配置文件

[[email protected] share]$ vim recovery.conf

standby_mode = on    #说明该节点是从服务器
primary_conninfo = 'host=192.168.47.105 port=5432 user=replica password=123456' #主节点的信息以及连接的用户
recovery_target_timeline = 'latest'

（3）配置postgresql.conf

[[email protected] share]$  vi /usr/local/pgsql/data/postgresql.conf

wal_level = hot_standby
max_connections = 1000 #一般查多于写的应用从库的最大连接数要比较大
hot_standby = on #说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s #数据流备份的最大延迟时间
wal_receiver_status_interval = 10s #多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间

（4）配置完启动数据库

[[email protected] share]$   /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l /usr/local/pgsql/logs/postgres.log start

三、验证主从
方法一：

登录主节点数据库执行如下命令

[[email protected] bin]# su postgres
[[email protected] bin]$ pwd
/usr/local/pgsql/bin
[[email protected] bin]$ ./psql

执行下列语句：
select client_addr,sync_state from pg_stat_replication;
结果如下：

postgres=# select client_addr,sync_state from pg_stat_replication;
  client_addr  | sync_state 
---------------+------------
 192.168.47.106 | async
(1 row)

说明192.168.47.106服务器是从节点，在接收流，而且是异步流复制

方法二：

在主节点与从节点分别执行如下命令

ps -ef | grep postgres

可以看到主节点有wal receiver进程

postgres   2903   2711  0 18:32 ?        00:00:00 postgres: wal sender process replica 192.168.47.106(32886) streaming 0/5000140

可以看到从节点有wal receiver进程

postgres   2572   2567  0 18:32 ?        00:00:00 postgres: wal receiver process   streaming 0/5000140

主服务器上插入数据或删除数据，在从服务器上能看到相应的变化。从服务器上只能查询，不能插入或删除。

这里有用ansible实现的一键部署postgresql的主从脚本，需要的可以自取哈。postgresql主从