PostgreSQL9.5WAL主备服务搭建

#PostgreSQL 9.5 版本主备搭建

背景

一些常用命令总结

#su - postgres #进入linux的postgres用户下

$psql #进入本地postgresql数据库管理系统 ，前提是在postgres用户下

$psql -h 10.0.4.176 -p 5455 -U postgres #进入10.0.4.176节点，该节点的数据库端口5455，进入名为postgres数据库中

主机：10.0.4.176

备机：10.0.0.173

postgresql的有效bin目录：/usr/lib/postgresql/9.5/bin

​ 注意可能有无效的bin目录，我这就有/usr/local/pgsql/bin ，但是用了什么语句判断出的前者为有效，这个怎么判断得去查下

postgresql安装目录，也是放postgresql.conf这些配置文件的地儿：/etc/postgresql/9.5/main/postgresql.conf

数据库备份目录（在备机中）：/var/libpostgresql/9.5/main

步骤

主机

1 修改postgresql.conf文件

listen_addresses = ‘*’

wal_level = hot_standby

synchronous_commit = local – 本地同步级别

max_wal_senders = 5 – 两台服务器

wal_keep_segments = 10

2 修改pg_hba.conf文件

在文件最后增加

host    all             all             0.0.0.0/0               md5
host    all             all             10.0.4.173/32       md5
host    replication     replica         10.0.4.176/32       md5
host    replication     replica         10.0.4.173/32       md5

3 创建用于复制的用户replica

root@qls-228:~# su - postgres
postgres@qls-228:~$ psql
psql (9.5.24)
Type "help" for help.

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

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

备机

1 停止postgresql服务

systemctl stop postgresql

2 修改postgresql.conf文件

listen_addresses = ‘*’

wal_level = hot_standby

synchronous_commit = local – 本地同步级别

max_wal_senders = 5 – 两台服务器

wal_keep_segments = 10

hot_standby = on

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

首先进入数据库备份目录 /var/lib/postgresql/9.5下

如果已经有main文件则需要更改main文件名字

postgres@qls-228:~/9.5$ mv main main_back1

将主机postgresql数据复制到从机

postgres@qls-228:~/9.5$ pg_basebackup -h 10.0.4.176 -U replica -p 5455 -F p -x -P -R -D /var/lib/postgresql/9.5/main3
Password: 
184742/184742 kB (100%), 1/1 tablespace

下面简单做一下参数说明（可以通过pg_basebackup --help进行查看），-h指定连接的数据库的主机名或IP地址，这里就是主库的ip。-U指定连接的用户名，此处是我们刚才创建的专门负责流复制的repl用户。-F指定了输出的格式，支持p（原样输出）或者t（tar格式输出）。-x表示备份开始后，启动另一个流复制连接从主库接收WAL日志。-P表示允许在备份过程中实时的打印备份的进度。-R表示会在备份结束后自动生成recovery.conf文件，这样也就避免了手动创建。-D指定把备份写到哪个目录，这里尤其要注意一点就是做基础备份之前从库的数据目录（/usr/local/postgresql/data）目录需要手动清空。

注意：网上各种备份方法都要自己创建一个recovery.conf文件或者是从/usr/share/postgresql/9.5/recovery.conf.sample复制过来，但总会报错。原因是这个recovery.conf只需要在pg_basebackup中用-R来自动生成就好了。

4 启动备机postgresql数据库并测试

systemctl start postgresql

主机测试

如下为成功

select pg_is_in_recovery();代表该库是主库

root@qls-228:~# su - postgres
postgres@qls-228:~$ psql
psql (9.5.24)
Type "help" for help.

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

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

备机测试

如下为成功

select pg_is_in_recovery();得到t代表该库是备库

root@qls-228:~# su - postgres
postgres@qls-228:~$ psql -h 10.0.4.173 -p 5455 -U postgres
Password for user postgres: 
psql (9.5.24)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# select client_addr,sync_state from pg_stat_replication;
 client_addr | sync_state 
-------------+------------
(0 rows)

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t       
(1 row)

再在主从机上分别执行

ps aux | grep wal #若主机上有 wal sender process 进程，从机上有 wal receiver process 进程代表成功