流复制实现standby数据库【双机热备】及异步、同步流复制测试

主库（primary）：192.168.1.141

备库（standby）：192.168.1.150

1、

主库参数设置：

vim postgresql.conf

archive_mode = on

listen_addresses = '*' # what IP address(es) to listen on;

# comma-separated list of addresses;

# defaults to 'localhost'; use '*' for all

# (change requires restart)

max_wal_senders = 5 # max number of walsender processes

# (change requires restart)

//wal发送进程数，有几个备库就设置为几

wal_keep_segments = 16 # in logfile segments, 16MB each; 0 disables

vim pg_hba.conf

host replication postgres 0.0.0.0/0 md5

通过md5密码验证，默认postgres是没有密码的，需要先用超级用户指定密码：

postgres=# alter user postgres with password 'postgres';

ALTER ROLE

2、

主库创建具有replication权限的用户：

create user rep replication login encrypted password 'rep';

由于pg_hba.conf文件已经配置postgres用户【超级用户】具有此权限，此处可以省略

3、

重启主库

pg_ctl restart -m f

4、

查看所有表空间，如果如果主库中有多个表空间则需要在备库中建立相关的表空间的目录，如果没有就不用建。

postgres=# \db

List of tablespaces

Name | Owner | Location

------------+----------+----------

pg_default | postgres |

pg_global | postgres |

tian_1 | postgres | /tian

(3 rows)

或者：

[postgres@single ~]$ ls -l /opt/pgsql-9.4.4/data/pg_tblspc/

total 0

lrwxrwxrwx 1 postgres postgres 5 Feb 15 14:58 16690 -> /tian

查看linux下所有用户：

[root@pg962 ~]# cat /etc/passwd|grep -v nologin|grep -v halt|grep -v shutdown|awk -F":" '{ print $1"|"$3"|"$4 }'|more

root|0|0

sync|5|0

tian|500|500

pg2|501|501

在备端创建/tian，指定所属用户为pg2:pg2

[root@pg962 ~]# mkdir /tian

[root@pg962 ~]# chown pg2:pg2 /tian -R

5、删除data目录

若备库只安装数据库软件，不进行数据库初始化。

则无需删除data目录，只需要根据主库相关路径配置，创建日志目录，表空间目录。

rm -rf data/

6、

在备库上生成基础备份：

[pg94@pg962 pg94]$ pg_basebackup -D$PGDATA -F p -X stream -v -P -R -h 192.168.1.141 -p 5432 -U postgres

Password:

transaction log start point: 0/36000100 on timeline 2

pg_basebackup: starting background WAL receiver

37124/37124 kB (100%), 2/2 tablespaces

transaction log end point: 0/36000200

pg_basebackup: waiting for background process to finish streaming ...

pg_basebackup: base backup completed

确保备份目录的所属组及用户有权限

-F p:默认输出格式，原样复制

-P：显示进度

-x：备份时将备份产生的xlog文件也备份出来

-X stream：避免使用-x时主库上的WAL日志有可能被覆盖而导致失败的问题。【要求max_wal_senders>=2】

-R：在输出目录中写入一个最小的recovery.conf（或在使用tar格式时写入基本归档文件）

【若没有指定—R参数需要在恢复数据库的时候自行创建recovery.conf文件】

-l : 指定备份的一个标识

2017/02/28 13:11

pg_basebackup -D$PGDATA -F p -X stream -v -P -h 192.168.100.77 -p 5432 -U postgres

可以直接指定值data目录，这样后面就不用复制过去了。

7、修改备库参数

hot_standby = on # "on" allows queries during recovery

8、启动备库，查看服务进程，请注意有一个wal receiver progress进程，这个是接收wal日志的进程

[pg94@pg962 data]$ pg_ctl start

server starting

[pg94@pg962 data]$ LOG: redirecting log output to logging collector process

HINT: Future log output will appear in directory "pg_log".

[pg94@pg962 data]$ ps -ef| grep postgres

pg94 13086 1 0 15:28 pts/3 00:00:00 /pg94/bin/postgres

pg94 13087 13086 0 15:28 ? 00:00:00 postgres: logger process

pg94 13088 13086 0 15:28 ? 00:00:00 postgres: startup process recovering 000000020000000000000037

pg94 13089 13086 0 15:28 ? 00:00:00 postgres: checkpointer process

pg94 13090 13086 0 15:28 ? 00:00:00 postgres: writer process

pg94 13091 13086 0 15:28 ? 00:00:00 postgres: wal receiver process streaming 0/37001728

pg94 13104 12378 0 15:30 pts/3 00:00:00 grep postgres

主库查看进程，同样主库也多了一个wal sender process进程，用于日志发送

[postgres@single ~]$ ps -ef|grep postgres

highgo 2279 1 0 08:44 ? 00:00:00 /home/highgo/HighgoDB/bin/postgres -D /home/highgo/HighgoDB/data

highgo 2281 2279 0 08:44 ? 00:00:00 postgres: checkpointer process

highgo 2282 2279 0 08:44 ? 00:00:15 postgres: writer process

highgo 2283 2279 0 08:44 ? 00:00:00 postgres: wal writer process

highgo 2284 2279 0 08:44 ? 00:00:14 postgres: autovacuum launcher process

highgo 2285 2279 0 08:44 ? 00:00:00 postgres: stats collector process

postgres 2342 1 0 08:44 ? 00:00:01 /opt/pgsql-9.4.4/bin/postmaster -D /opt/pgsql-9.4.4/data

postgres 2396 2342 0 08:44 ? 00:00:00 postgres: logger process

postgres 2398 2342 0 08:44 ? 00:00:00 postgres: checkpointer process

postgres 2399 2342 0 08:44 ? 00:00:00 postgres: writer process

postgres 2400 2342 0 08:44 ? 00:00:07 postgres: wal writer process

postgres 2401 2342 0 08:44 ? 00:00:00 postgres: autovacuum launcher process

postgres 2402 2342 0 08:44 ? 00:00:00 postgres: archiver process last was 000000020000000000000036.00000100.backup

postgres 2403 2342 0 08:44 ? 00:00:00 postgres: stats collector process

postgres 5483 2342 0 15:28 ? 00:00:00 postgres: wal sender process postgres 192.168.1.150(43590) streaming 0/37001728

root 5519 5499 0 15:32 pts/1 00:00:00 su - postgres

postgres 5520 5519 0 15:32 pts/1 00:00:00 -bash

postgres 5551 5520 1 15:33 pts/1 00:00:00 ps -ef

postgres 5552 5520 0 15:33 pts/1 00:00:00 grep postgres

9、测试同步

[pg94@pg962 data]$ psql

psql: FATAL: role "pg94" does not exist

默认打开操作系统用户命名的pg94的数据库，但是集群中没有。

换个用户。

[pg94@pg962 data]$ psql testdb qwe

psql (9.4.4)

Type "help" for help.

主端：

testdb=# select * from baby;

name | age | note | tel

------+-----+--------+-----

西门 | 20 | 水浒传 |

(1 row)

testdb=# insert into baby values('Tom',18,'拉阿里',1234567);

INSERT 0 1

testdb=# select * from baby;

name | age | note | tel

------+-----+--------+---------

西门 | 20 | 水浒传 |

Tom | 18 | 拉阿里 | 1234567

(2 rows)

备端：

testdb=# select * from baby;

name | age | note | tel

------+-----+--------+---------

西门 | 20 | 水浒传 |

Tom | 18 | 拉阿里 | 1234567

(2 rows)

成功

关于同步和异步流复制的测试：

主库：

create table t1 (id int,name varchar(40),note text);

insert into t1 select generate_series(1,1000000),'testcontent哈哈哈‘’二哈的幸福生活2345';

select * from t1;

通过系列生成函数插入主端数据库的时候

可以发现，主库可以查询到t1表中信息的时候，备库仍是读取状态（hang住），稍等几秒后才存入数据

同步流复制：

在以上异步流复制基础上：

关闭主端

关闭备端

pg_ctl stop -m f （关库顺序无影响）

主库vim postgresql.conf

synchronous_standby_names = '*' # standby servers that provide sync rep

# comma-separated list of application_name

# from standby(s); '*' = all

一般来说都是创建两个或两个以上的standby，此处仅创建一个作为测试

备库：vim recovery.conf

[pg94@pg962 data]$ vim recovery.conf

standby_mode = 'on'

primary_conninfo = 'application_name=standby01 user=postgres password=postgres host=192.168.1.141 port=5432 sslmode=disable sslcompression=1'

主端：

[postgres@single ~]$ LOG: redirecting log output to logging collector process

HINT: Future log output will appear in directory "pg_log".

[postgres@single ~]$ psql testdb qwe

psql (9.4.4)

Type "help" for help.

testdb=# select * from baby;

name | age | note | tel

------+-----+--------+---------

西门 | 20 | 水浒传 |

Tom | 18 | 拉阿里 | 1234567

(2 rows)

testdb=# insert into baby values('Rose',19,'泰坦',null);

查询可以正常进行，但是更新操作会被hang住

此时启动备库standby，主端插入成功！

create table t1 (id int,name varchar(40),note text);

insert into t1 select generate_series(1,500000),'testcontent哈哈哈‘’二哈的幸福生活2345';

select * from t1;

可以发现主库和备库是同时可以查询到的。

遇到的问题：

1、

【因为没有关闭postgresql服务，直接删除了data目录，导致报错如下】

[pg94@pg962 ~]$ LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No such file or directory

LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No such file or directory

LOG: could not open temporary statistics file "pg_stat_tmp/global.tmp": No such file or directory

。。。。。。

查看进程：

pg94@pg962 ~]$ ps -ef | grep postgres

pg94 12466 1 0 11:26 pts/2 00:00:00 /pg94/bin/postgres

pg94 12468 12466 0 11:26 ? 00:00:00 postgres: checkpointer process

pg94 12469 12466 0 11:26 ? 00:00:00 postgres: writer process

pg94 12470 12466 0 11:26 ? 00:00:00 postgres: wal writer process

pg94 12471 12466 0 11:26 ? 00:00:00 postgres: autovacuum launcher process

pg94 12472 12466 0 11:26 ? 00:00:00 postgres: stats collector process

pg94 12568 12334 0 11:45 pts/2 00:00:00 grep postgres

查看数据库该端口的PID：

[pg94@pg962 ~]$ lsof -i :5432

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

postgres 12466 pg94 3u IPv6 52547 0t0 TCP localhost.localdomain:postgres (LISTEN)

postgres 12466 pg94 4u IPv4 52548 0t0 TCP localhost.localdomain:postgres (LISTEN)

[pg94@pg962 ~]$ kill -9 12466

-bash: kill: (12466) - No such process

2、

data 目录的权限不足报错：

[pg2@pg962 stadbybac]$ FATAL: data directory "/usr/local/pgsql/data" has group or world access

DETAIL: Permissions should be u=rwx (0700).

chmod 700 data/

3、

不同版本之间无法做standby

[pg2@pg962 stadbybac]$ FATAL: database files are incompatible with server

DETAIL: The data directory was initialized by PostgreSQL version 9.4, which is not compatible with this version 9.6.2.

主库：postgres=# select * from version();

version

--------------------------------------------------------------------------------------------------------------

PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

(1 row)

备库：

[pg2@pg962 stadbybac]$ psql -l --version

psql (PostgreSQL) 9.6.2