Tommy's Blog

配置 master 和 slave 数据库
~~~~~~~~~~~~~~~~~~~~~
+ Fedora
创建一个数据库用户 contactuser，并创建名为 contactdb 的 master库。有关下面语句的一些说明，参考：http://www.postgresql.org/docs/8.4/static/app-createuser.html
pwprompt 有密码提示，下面都是些默认选项。

$ createuser --no-superuser --no-createdb --no-createrole --pwprompt contactuser
$ createdb --owner contactuser contactdb
$ createlang -d contactdb plpgsql
$ psql --username contactuser --dbname contactdb

建一个主键由 sequence 生成的表，并插入两条数据

contactdb=> create sequence contact_seq start with 1;
contactdb=> create table contact (
 cid         int4 primary key,
 name        varchar(50),
 address     varchar(255),
 phone       varchar(15)
);
contactdb=> insert into contact (cid, name, address,phone)
 values ((select nextval('contact_seq')),'tom', '1 Street', '(10) 00000000');
contactdb=> insert into contact (cid, name, address,phone)
 values ((select nextval('contact_seq')),'bob', '2 Road', '(10) 11111111');
contactdb=> \q

把 contactdb 的 schema 备份出来，数据不备份，用于恢复到 slave 库

$ pg_dump -s -U postgres  -Fc contactdb > contactdb.dump 

将dump文件传送到ubuntu的机器上

$ scp contactdb.dump tommy@192.168.0.199:/home/tommy

创建一个内容如下的 Slonik 的脚本

$ vi cluster_setup.sh
#!/bin/sh

CLUSTER=contact_cluster
DB1=contactdb
DB2=contactdb_slave
H1=192.168.0.194
H2=192.168.0.199
U=postgres

slonik <<_EOF_

cluster name = $CLUSTER;

node 1 admin conninfo = 'dbname=$DB1 host=$H1 user=$U';
node 2 admin conninfo = 'dbname=$DB2 host=$H2 user=$U';

init cluster (id = 1, comment = 'Master Node');

create set (id = 1, origin = 1, comment = 'All contactdb tables');
set add table (set id = 1, origin = 1, id = 1,
 full qualified name = 'public.contact', comment = 'Table contact');
set add sequence (set id = 1, origin = 1, id = 2,
 full qualified name = 'public.contact_seq', comment = 'Sequence contact_seq');

store node (id = 2, comment = 'Slave node');
store path (server = 1, client = 2, conninfo = 'dbname=$DB1 host=$H1 user=$U');
store path (server = 2, client = 1, conninfo = 'dbname=$DB2 host=$H2 user=$U');

store listen (origin = 1, provider = 1, receiver = 2);
store listen (origin = 2, provider = 2, receiver = 1);
_EOF_

暂时不执行此脚本，等ubuntu准备好。

+ Ubuntu
将前面从 fedora 传过来的 contactdb.dump 挪到 postgres 用户的根目录

$ sudo mv /home/tommy/contactdb.dump /var/lib/postgresql/

参考前面fedora，创建名为 contactdb_slave 的从数据库，将contactdb.dump恢复到contactdb_slave，因为dump文件里有lang，所以不用createlang。

$ sudo -i -u postgres
$ createuser --no-superuser --no-createdb --no-createrole --pwprompt contactuser
$ createdb --owner contactuser contactdb_slave
$ psql --username contactuser --dbname contactdb_slave
$ pg_restore -d contactdb_slave contactdb.dump

$ vi subscribe.sh
#!/bin/sh

CLUSTER=contact_cluster
DB1=contactdb
DB2=contactdb_slave
H1=192.168.0.194
H2=192.168.0.199
U=postgres

/usr/lib/postgresql/8.4/bin/slonik <<_EOF_

cluster name = $CLUSTER;

node 1 admin conninfo = 'dbname=$DB1 host=$H1 user=$U';
node 2 admin conninfo = 'dbname=$DB2 host=$H2 user=$U';

# 如果未来还有node 3，并且node 2可能作为其provider，则需要forward = yes
subscribe set (id = 1, provider = 1, receiver = 2, forward = yes);
_EOF_

在 fedora上执行脚本，并启动 slon 进程

$ chmod +x cluster_setup.sh
$ ./cluster_setup.sh
$ slon contact_cluster "dbname=contactdb user=postgres host=192.168.0.194" 

ubuntu上执行脚本，并启动 slon 进程，启动ubuntu上的slon进程，此进程将每隔2秒检查master有新数据需要同步到slave，如果要改变同步间隔，可以加参数 -s 。参见：http://www.slony.info/documentation/slon.html

$ chmod +x subscribe.sh
$ ./subscribe.sh
$ /usr/lib/postgresql/8.4/bin/slon contact_cluster "dbname=contactdb_slave user=postgres host=192.168.0.199" 

Pages: 1 2 3 4 5 6 7