Postgresql主从复制的简单实现

实验环境

centos 7    192.168.73.131   eric1
centos 7    192.168.73.135   eric2

在主节点上搭建时间同步服务器

[[email protected] ~]# yum install -y ntp

配置NTP

[[email protected] ~]# vim /etc/ntp.conf                //*添加如下两行
server  127.127.1.0
fudge   127.127.1.0 stratum 8

配置服务并设置开机启动

[[email protected] ~]# systemctl restart ntpd 
[[email protected] ~]# systemctl enable ntpd

在从节点上进行时间同步

[[email protected] ~]# yum install -y ntpdate
[[email protected] ~]# ntpdate 192.168.73.131

在两台服务器上安装postgresql

[[email protected] ~]# yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm -y
[[email protected] ~]# yum install postgresql10-contrib postgresql10-server -y

初始化数据库

[[email protected] ~]# /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database ... OK

启动服务并设置开机自启

[[email protected] ~]# systemctl start postgresql-10.service 
[[email protected] ~]# systemctl enable  postgresql-10.service

配置主服务器

在主服务器登录PSQL并设置密码

[[email protected] ~]# su - postgres
-bash-4.2$ psql
psql (10.11)
输入 "help" 来获取帮助信息.

postgres=# alter user postgres with password 'abc-123';
ALTER ROLE

创建用户和数据库并授权

postgres=# alter user postgres with password 'abc-123';                          //*修改PSQL密码
ALTER ROLE
postgres=# create user db_eric with password 'abc-123';                          //*创建数据库用户
CREATE ROLE
postgres=# create database test_rose owner db_eric;                              //*创建数据库
CREATE DATABASE
postgres=# grant all privileges on database test_rose to db_eric;                //*赋予用户所有权限
GRANT
postgres=# alter role db_eric login replication encrypted password 'abc-123';    //*赋予用户登录和复制权限
ALTER ROLE    

修改pg_hba.conf,允许db_eric用户来同步

[[email protected] ~]# vim /var/lib/pgsql/10/data/pg_hba.conf                           //*添加以下内容
host    all             all             192.168.73.135/32       md5              //*使用密码验证
host    all             all             192.168.73.135/32       md5              //*允许135连接到主服务器
host    replication     db_eric         192.168.73.135/32       md5              //*允许135使用db_eric用户来复制

修改postgresql.conf

[[email protected] ~]# vim /var/lib/pgsql/10/data/postgresql.conf 
listen_addresses = '*'   
archive_mode = on  
archive_command = 'cp %p /opt/pgsql/pg_archive/%f' 
wal_level = hot_standby
max_wal_senders = 32 
wal_sender_timeout = 60s 
max_connections = 100 

重启服务

[[email protected] ~]# systemctl restart postgresql-10.service

测试从数据库是否能连接主数据库

[[email protected] ~]# psql -h 192.168.73.131 -U db_eric -d test_rose
用户 db_eric 的口令:
psql (10.11)
输入 "help" 来获取帮助信息.

test_rose=>

配置从服务器

从主服务器拷贝数据到从服务器

[[email protected] ~]# rm -rf /var/lib/pgsql/10/data/*    //*将目录下的数据都清空
[[email protected] ~]# pg_basebackup -h 192.168.73.131 -U db_eric -D /var/lib/pgsql/10/data -X stream -P
Password:                                          //*从主服务器拷贝数据到从服务器
32486/32486 kB (100%), 1/1 tablespace

复制文件

[[email protected] ~]# cp /usr/pgsql-10/share/recovery.conf.sample /var/lib/pgsql/10/data/recovery.conf

修改recovery.conf

[[email protected] ~]# vim /var/lib/pgsql/10/data/recovery.conf
standby_mode = on                                                                   //*说明该节点是从服务器
primary_conninfo = 'host=192.168.73.131 port=5432 user=db_eric password=abc-123'    //*主服务器的数据库登录信息
recovery_target_timeline = 'latest'

配置postgresql.conf

[[email protected] ~]# vim /var/lib/pgsql/10/data/postgresql.conf
wal_level = hot_standby
max_connections = 1000                //*一般查多于写的应用从库的最大连接数要比较大
hot_standby = on                      //*说明这台机器不仅仅是用于数据归档,也用于数据查询
max_standby_streaming_delay = 30s     //*数据流备份的最大延迟时间
wal_receiver_status_interval = 10s    //*多久向主报告一次从的状态,当然从每次数据复制都会向主报告状态,这里只是设置最长的间隔时间
hot_standby_feedback = on             //*如果有错误的数据复制,是否向主进行反馈

修改目录属主和属组

[[email protected] ~]# chown -R postgres:postgres /var/lib/pgsql/10/data

重启服务

[[email protected] ~]# systemctl restart postgresql-10.service

验证

在主数据库执行

[[email protected] ~]# su - postgres
上一次登录:四 12月  5 16:05:06 CST 2019pts/1 上
-bash-4.2$ psql
psql (10.11)
输入 "help" 来获取帮助信息.

postgres=# select client_addr,sync_state from pg_stat_replication;
  client_addr   | sync_state 
----------------+------------
 192.168.73.135 | async    //*说明135是从服务器,在接收流,而且是异步流复制
(1 行记录)

在主数据库创建表

postgres=# \c test_rose 
您现在已经连接到数据库 "test_rose",用户 "postgres".
test_rose=# CREATE TABLE DEPARTMENT(
test_rose(#    ID INT PRIMARY KEY      NOT NULL,
test_rose(#    DEPT           CHAR(50) NOT NULL,
test_rose(#    EMP_ID         INT      NOT NULL
test_rose(# );
CREATE TABLE
test_rose=# \d 
                 关联列表
 架构模式 |    名称    |  类型  |  拥有者  
----------+------------+--------+----------
 public   | department | 数据表 | postgres
(1 行记录)   

在从数据库验证

postgres=# \c test_rose 
您现在已经连接到数据库 "test_rose",用户 "postgres".
test_rose=# \d 
                 关联列表
 架构模式 |    名称    |  类型  |  拥有者  
----------+------------+--------+----------
 public   | department | 数据表 | postgres
(1 行记录)