专家简介

 

周正中

网名：德哥@Digoal

DBA+杭州群联合发起人之一

 

PostgreSQL中国社区发起人之一，负责杭州分会，兼任社区CTO一职。曾就职于斯凯网络，负责数据库部门。现就职于阿里巴巴，负责RDS PG内核组事务。

 

[压力测试以及一致性测试]

主192.168.173.37

备192.168.173.42

创建测试表 , 测试函数

postgres=# create table test(id int primary key, info text, crt_time timestamp);

CREATE TABLE

postgres=# create or replace function f_test(i_id int) returns void as $$

declare

begin

update test set info=md5(random()::text),crt_time=now() where id=i_id;

if not found then

insert into test values(i_id,md5(random()::text),now());

end if;

exception when others then

return;

end;

$$ language plpgsql strict;

压力测试脚本

vi test.sql

\setrandom id 1 30000000

select f_test(:id);

压力测试

postgres@db-192-168-173-37-> pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 3000

在约1分钟后, 关闭主库eth0:1 接口

[root@db-192-168-173-37 ~]# ifdown eth0:1

这期间173.37还在做压力测试, 数据库已经和42产生了差异数据.

看后面37能不能顺利转换成standby

触发切换, 备切换成主备角色

[root@db-192-168-173-42 ~]# tail -f -n 1 /tmp/sky_pg_clusterd.log

2015-01-0408:50:42 detecting eth0 192.168.173.130 exists, ps: return 0 exist, other not exist.

checkmaster check times: 3

2015-01-0408:50:45 detecting eth0 192.168.173.130 exists, ps: return 0 exist, other not exist.

checkmaster check times: 4

2015-01-0408:50:48 detecting eth0 192.168.173.130 exists, ps: return 0 exist, other not exist.

checkmaster check times: 5

2015-01-0408:50:51 detecting eth0 192.168.173.130 exists, ps: return 0 exist, other not exist.

checkmaster ipscan timeout: 5

2015-01-0408:50:53 normal fenceing, waiting...

Success: Rebooted

waiting for server to shut down............. done

server stopped

waiting for server to start....LOG: 00000: redirecting log output to logging collector process

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

LOCATION: SysLogger_Start, syslogger.c:649

.............................. done

server started

2015-01-0408:51:48 promoting database ...

server promoting

2015-01-0408:51:48 testing promote status

2015-01-0408:51:48 promoting...

2015-01-0408:51:49 testing promote status

2015-01-0408:51:49 promote success.

CHECKPOINT

pg_switch_xlog

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

1/14C58C30

(1 row)

CHECKPOINT

pg_switch_xlog

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

1/150000E8

(1 row)

CHECKPOINT

2015-01-0408:51:56 eth0:1 if upping. 1.

2015-01-0408:52:00 eth0:1 upped success.

2015-01-0408:52:00 this is m_s

2015-01-0408:52:01 detecting eth0 192.168.173.1 exists, ps: return 0 exist, other not exist.

cluster_keepalive_test

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

(1 row)

2015-01-0408:52:01 detecting 192.168.173.130 address up on eth0 ....

2015-01-0408:52:01 detecting 192.168.173.131 address up on eth0 ....

2015-01-0408:52:01 detecting postgresql listener on peer host.

connect failed!: Operation now in progress

socket created!

在新主节点(192.168.173.42)接着压

postgres@db-192-168-173-42-> pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 30

最后主备一致性检查

新主节点

postgres=# select count(*),sum(hashtext(info)) from test;

count | sum

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

1808909 | -680268294581

(1 row)

新备节点

postgres=# select count(*),sum(hashtext(info)) from test;

count | sum

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

1808909 | -680268294581

(1 row)

[参考]

1. https://github.com/digoal/PostgreSQL_HA_with_primary_standby_2vip

2. https://github.com/digoal/sky_postgresql_cluster

3. http://blog.163.com/digoal@126/blog/static/163877040201481085344535/

4. http://blog.163.com/digoal@126/blog/static/163877040201481085624211/