简单的Slony-I设置实例 II

时间:2022-03-23 07:20:51

 

磨砺技术珠矶,践行数据之道,追求卓越价值

 

回到上一级页面: PostgreSQL集群方案相关索引页     回到*页面:PostgreSQL索引页

接前面例子, 简单的Slony-I设置实例

这次我增加一台机器C: 192.168.10.100,我尽量从该机器上发送slonik命令

机器A和机器B启动之后:

执行初始化cluster动作:

[postgres@pg102 ~]$ cat setup.sh
#!/bin/sh

CLUSTERNAME=testdb3_cluster
MASTERDBNAME=testdb3
SLAVEDBNAME=testdb3
MASTERHOST=192.168.10.102
SLAVEHOST=192.168.10.101
REPLICATIONUSER=postgres

/usr/local/slony/bin/slonik <<_EOF_
    #--
    # define the namespace the replication system
    # uses in our example it is slony_example
    #--
    cluster name = $CLUSTERNAME;

    #--
    # admin conninfo's are used by slonik to connect to 
    # the nodes one for eachnode on each side of the cluster, 
    # the syntax is that of PQconnectdb in
    # the C-API
    # --
    node 1 admin conninfo = 'dbname=$MASTERDBNAME \
           host=$MASTERHOST user=$REPLICATIONUSER';
    node 2 admin conninfo = 'dbname=$SLAVEDBNAME \
           host=$SLAVEHOST user=$REPLICATIONUSER';

    #--
    # init the first node.  Its id MUST be 1.  This creates 
    # the schema _$CLUSTERNAME containing all replication 
    # system specific database objects.
    #--
    init cluster ( id=1, comment = 'Master Node');

    #--
    # Slony-I organizes tables into sets.  The smallest unit 
    # a node can subscribe is a set. The master or origin of 
    # the set is node 1.
    #--
    create set (id=1, origin=1, comment='All testdb3 tables');
    set add table (set id=1, origin=1, id=1, 
                   fully qualified name = 'public.t1',
                   comment='t1 table');
    set add sequence (set id=1, origin = 1, id = 1, 
                   fully qualified name = 'public.t1_id_seq',
                   comment = 't1 id sequence');

    #--
    # Create the second node (the slave) tell the 2 nodes how 
    # to connect to each other and how they should listen for events.
    #--

    store node (id=2, comment = 'Slave Node', event node=1);
    store path (server = 1, client = 2, conninfo='dbname=$MASTERDBNAME \
                host=$MASTERHOST user=$REPLICATIONUSER');
    store path (server = 2, client = 1, conninfo='dbname=$SLAVEDBNAME \
                host=$SLAVEHOST user=$REPLICATIONUSER');
_EOF_
[postgres@pg102 ~]$
sh setup.sh

然后:

分别在机器A和机器B中,看到各自建立了一个名字空间(namespace,其实是schema):

机器A:

[postgres@pg102 bin]$ ./psql
psql (9.1.2)
Type "help" for help.

postgres=# \c testdb3
You are now connected to database "testdb3" as user "postgres".
testdb3=# select * from pg_namespace;
      nspname       | nspowner |               nspacl                
--------------------+----------+-------------------------------------
 pg_toast           |       10 | 
 pg_temp_1          |       10 | 
 pg_toast_temp_1    |       10 | 
 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
 public             |       10 | {postgres=UC/postgres,=UC/postgres}
 information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 _testdb3_cluster   |       10 | {postgres=UC/postgres,=U/postgres}
(7 rows)

testdb3=# create table _testdb3_cluster.gao(id integer);
CREATE TABLE
testdb3=# drop table _testdb3_cluster;
ERROR:  table "_testdb3_cluster" does not exist
testdb3=# drop table _testdb3_cluster.gao;
DROP TABLE
testdb3=# 

 

机器B:

[postgres@pg101 bin]$ ./psql
psql (9.1.2)
Type "help" for help.

postgres=# \c testdb3
You are now connected to database "testdb3" as user "postgres".
testdb3=# select * from pg_namespace;
      nspname       | nspowner |               nspacl                
--------------------+----------+-------------------------------------
 pg_toast           |       10 | 
 pg_temp_1          |       10 | 
 pg_toast_temp_1    |       10 | 
 pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
 information_schema |       10 | {postgres=UC/postgres,=U/postgres}
 public             |       10 | {postgres=UC/postgres,=UC/postgres}
 _testdb3_cluster   |       10 | {postgres=UC/postgres,=U/postgres}
(7 rows)

testdb3=# 

可以这样说,在slony的环境中,并不存在一个中心节点来存储cluster信息。
这种方式加上各种指令为中心的处理结构,导致其架构复杂化。

接下来,看看可否另slon daemon独立于DB节点运行:

我在机器C上执行:

/usr/local/slony/bin/slon testdb3_cluster "dbname=testdb3 user=postgres host=192.168.10.102" &
/usr/local/slony/bin/slon testdb3_cluster "dbname=testdb3 user=postgres host=192.168.10.101" &

然后,此时,再在机器C上执行 subscribe 过程:

[postgres@pg102 ~]$ cat subscribe.sh
#!/bin/sh

CLUSTERNAME=testdb3_cluster
MASTERDBNAME=testdb3
SLAVEDBNAME=testdb3
MASTERHOST=192.168.10.102
SLAVEHOST=192.168.10.101
REPLICATIONUSER=postgres

/usr/local/slony/bin/slonik <<_EOF_
     # ----
     # This defines which namespace the replication system uses
     # ----
     cluster name = $CLUSTERNAME;

     # ----
     # Admin conninfo's are used by the slonik program to connect
     # to the node databases.  So these are the PQconnectdb arguments
     # that connect from the administrators workstation (where
     # slonik is executed).
     # ----
     node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST \
                              user=$REPLICATIONUSER';
     node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST \
                              user=$REPLICATIONUSER';

     # ----
     # Node 2 subscribes set 1
     # ----
     subscribe set ( id = 1, provider = 1, receiver = 2, forward = no);
_EOF_
[postgres@pg102 ~]$ sh subscribe.sh

验证:

在master数据库节点,增加数据:

简单的Slony-I设置实例 II

testdb3=# INSERT INTO t1(comment) VALUES('replication test');
INSERT 0 1
testdb3=# select * from t1;
id | comment | ins_time
----+------------------+----------------------------
2 | replication test | 2013-07-18 13:47:30.023486
(1 row)

testdb3=#

简单的Slony-I设置实例 II

此时,在 slave数据库节点,也可以看到同样的数据:已经成功。

简单的Slony-I设置实例 II
[root@pg101 ~]# su - postgres
[postgres@pg101 ~]$ psql testdb3
psql (9.1.2)
Type "help" for help.

  testdb3=# select * from t1;
  id | comment | ins_time
  ----+------------------+----------------------------
  2 | replication test | 2013-07-18 13:47:30.023486
  (1 row)

  testdb3=#

简单的Slony-I设置实例 II

已经成功完成复制。

回到上一级页面: PostgreSQL集群方案相关索引页     回到*页面:PostgreSQL索引页

磨砺技术珠矶,践行数据之道,追求卓越价值