PostgreSQL stream repication can implement between FreeBSD and CentOS

环境 : 

主节点

CentOS 5.8 x64
PostgreSQL 9.3.2
编译器 : gcc
pg_config
BINDIR = /opt/pgsql9.3.2/bin
DOCDIR = /opt/pgsql9.3.2/share/doc
HTMLDIR = /opt/pgsql9.3.2/share/doc
INCLUDEDIR = /opt/pgsql9.3.2/include
PKGINCLUDEDIR = /opt/pgsql9.3.2/include
INCLUDEDIR-SERVER = /opt/pgsql9.3.2/include/server
LIBDIR = /opt/pgsql9.3.2/lib
PKGLIBDIR = /opt/pgsql9.3.2/lib
LOCALEDIR = /opt/pgsql9.3.2/share/locale
MANDIR = /opt/pgsql9.3.2/share/man
SHAREDIR = /opt/pgsql9.3.2/share
SYSCONFDIR = /opt/pgsql9.3.2/etc
PGXS = /opt/pgsql9.3.2/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/opt/pgsql9.3.2' '--with-pgport=5432' '--with-perl' '--with-python' '--with-tcl' '--with-openssl' '--with-pam' '--without-ldap' '--with-libxml' '--with-libxslt' '--enable-thread-safety' '--with-wal-blocksize=16'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fpic
LDFLAGS = -L../../../src/common -Wl,-rpath,'/opt/pgsql9.3.2/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgport -lpgcommon -lxslt -lxml2 -lpam -lssl -lcrypto -lz -lreadline -ltermcap -lcrypt -ldl -lm
VERSION = PostgreSQL 9.3.2

standby 节点

FreeBSD 10 x64
PostgreSQL 9.3.4
编译器 : cc
pg_config
BINDIR = /opt/pgsql9.3.4/bin
DOCDIR = /opt/pgsql9.3.4/share/doc
HTMLDIR = /opt/pgsql9.3.4/share/doc
INCLUDEDIR = /opt/pgsql9.3.4/include
PKGINCLUDEDIR = /opt/pgsql9.3.4/include
INCLUDEDIR-SERVER = /opt/pgsql9.3.4/include/server
LIBDIR = /opt/pgsql9.3.4/lib
PKGLIBDIR = /opt/pgsql9.3.4/lib
LOCALEDIR = /opt/pgsql9.3.4/share/locale
MANDIR = /opt/pgsql9.3.4/share/man
SHAREDIR = /opt/pgsql9.3.4/share
SYSCONFDIR = /opt/pgsql9.3.4/etc
PGXS = /opt/pgsql9.3.4/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = 'CPPFLAGS=-I/usr/local/include' '--prefix=/opt/pgsql9.3.4' '--with-pgport=5432' '--with-perl' '--with-python' '--with-tcl' '--with-openssl' '--with-pam' '--without-ldap' '--with-libxml' '--with-libxslt' '--enable-thread-safety' '--with-wal-blocksize=16'
CC = cc
CPPFLAGS = -I/usr/local/include -I/usr/local/include/libxml2 -I/usr/include
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fPIC -DPIC
LDFLAGS = -L../../../src/common -L/usr/local/lib -L/usr/lib -Wl,--as-needed -Wl,-R'/opt/pgsql9.3.4/lib'
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgport -lpgcommon -lxslt -lxml2 -lpam -lssl -lcrypto -lz -lreadline -lcrypt -lm
VERSION = PostgreSQL 9.3.4

以上环境libpq binary-compatible, 所以可以作为流复制的主备(已测试一台60G的数据库成功复制, 以及打开测试完全没有问题). 各位客官可模仿.

这里用到BSD, 主要为了用它的zfs, zfsonlinux目前存在一定的性能问题, 在等brain的回复, 具体是否可以通过模块的参数来优化请期待.

FreeBSD下的standby激活后的读写测试 :

postgres@digoal:~ % pg_ctl promote
server promoting
postgres@digoal:~ % psql
psql (9.3.4)
Type "help" for help.

postgres=> create table test(id int primary key, info text, crt_time timestamp);
CREATE TABLE
postgres=> create or replace function f(v_id int) returns void as $$
postgres$> declare
postgres$> begin
postgres$>   update test set info=md5(now()::text),crt_time=now() where id=v_id;
postgres$>   if not found then
postgres$>     insert into test values (v_id, md5(now()::text), now());
postgres$>   end if;
postgres$>   exception when others then
postgres$>     return;
postgres$> end;
postgres$> $$ language plpgsql strict;
sCREATE FUNCTION
postgres=> select f(1);
 f
---

(1 row)

postgres=> select * from test;
 id |               info               |          crt_time
----+----------------------------------+----------------------------
  1 | 9de5370fe00c7ed52b48080e2fb3efc9 | 2014-06-27 19:07:14.725201
(1 row)

postgres=> select f(1);
 f
---

(1 row)

postgres=> select * from test;
 id |               info               |          crt_time
----+----------------------------------+----------------------------
  1 | f91841b8d4a52cb4ce82e835aa161283 | 2014-06-27 19:07:20.969022
(1 row)

postgres@digoal:~ % cd
postgres@digoal:~ % vi test.sql
\setrandom v_id 1 50000000
select f(:v_id);

postgres@digoal:~ % pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 30
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 30 s
number of transactions actually processed: 1512165
tps = 50368.843235 (including connections establishing)
tps = 50416.031069 (excluding connections establishing)
statement latencies in milliseconds:
        0.001875        \setrandom v_id 1 50000000
        0.313934        select f(:v_id);

[参考]
1. http://blog.163.com/digoal@126/blog/static/163877040201451181344545/

2. http://blog.163.com/digoal@126/blog/static/1638770402014526992910/

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