Postgresql主从服务搭建Postgresql 9.5

Postgresql主从服务搭建

环境信息：win10 虚拟机及Ubuntu 16.04 下实现
生产环境服务器Ubuntu16.04下亲测可用

数据库版本 Postgresql 9.5

主服务 ： 192.168.40.129

从服务器：192.168.40.131

主服务配置：

1. 修改postgresql.conf文件

sudo vim /etc/postgresql/9.5/main/postgresql.conf  

原文件都有这些信息，需要仔细找到修改

listen_addresses = ‘*’

wal_level = hot_standby

synchronous_commit = local – 本地同步级别

max_wal_senders = 2 – 两台服务器

wal_keep_segments = 10

保存退出

2. 修改pg_hba.conf文件

sudo vim /etc/postgresql/9.5/main/pg_hba.conf

添加

host    all             all             0.0.0.0/0               md5
host    all             all             192.168.40.131/32       md5
host    replication     replica         192.168.40.129/32       md5
host    replication     replica         192.168.40.131/32       md5

保存退出，重启Postgresql

sudo service postgresql restart

3. 创建用于复制的用户replica

首先需要 登录postgresql

myubtu@ubuntu:~$ sudo -u postgres psql
could not change directory to "/home/myubtu": Permission denied
psql (9.5.2)
Type "help" for help.

创建用户用于复制

创建用户用于复制

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

主服务器配置结束--------END

从服务器配置

1. 停止Postgresql服务

   sudo service postgresql stop
   

2. 修改postgresql.conf文件

​ listen_addresses = ‘*’

​ wal_level = hot_standby

​ synchronous_commit = local

​ max_wal_senders = 2

​ wal_keep_segments = 10

​ hot_standby = on

​ 保存并退出。

将数据从主机复制到从机【重点】

要从主服务器同步到从服务器，从服务器上的PostgreSQL主目录必须由主服务器的主目录替换。 在从服务器中，登录postgres用户

sudo su - postgres  -- 登录postgres  需要输入密码

备份实际数据库目录：

postgres@ubuntu:~$ cd /var/lib/postgresql/9.5/
postgres@ubuntu:~/9.5$ mv main main_back

创建新的主目录，更改权限：

postgres@ubuntu:~/9.5$ mkdir main/
postgres@ubuntu:~/9.5$ chmod 700 main

此时，使用pg_basebackupR将主目录从主服务器复制到从服务器：（要输入密码replica）

postgres@ubuntu:~/9.5$ pg_basebackup -h 192.168.40.129 -U replica -D /var/lib/postgresql/9.5/main -P
Password:

传输完成后，在主目录中创建一个新的recovery.conf文件，并粘贴以下内容：

standby_mode = 'on'
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=usr_strong_pwd'
trigger_file = '/tmp/postgresql.trigger.5432'

保存退出，更改文件权限：

postgres@ubuntu:~/9.5$ vim main/recovery.conf 
postgres@ubuntu:~/9.5$ 
postgres@ubuntu:~/9.5$ chmod 600 main/recovery.conf 

重启Postgresql。从服务配置结束。

验证

1. 主服务登录验证：

myubtu@ubuntu:/home$ sudo -u postgres psql
[sudo] password for myubtu: 
psql (9.5.2)
Type "help" for help.

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

postgres=# select pg_is_in_recovery();

pg_is_in_recovery 

 f
(1 row)

出现 192.168.40.131 | async 表示成功！！！

2. 测试在数据库添加新表：
   
   129上新建表后，从库131出现所添加表

3. 测试从库修改数据（从库修改报错。权限限制）
   
   131从库中对表中数据做修改报错（从库只有查询权限，无法添加、修改）

参考连接

https://blog.csdn.net/pollydejia/article/details/96321703
https://www.cnblogs.com/guoximing/p/9429009.html
https://www.cnblogs.com/yjf512/p/4499547.html
https://my.oschina.net/robin3d/blog/2907024
https://blog.csdn.net/wlwlwlwl015/article/details/53287855
https://www.linuxidc.com/Linux/2017-08/146190.htm
https://blog.51cto.com/wn2100/2238996