Slony-I 之三 1 master 节点 + 1目标节点，再添加1个目标节点

灰太狼 2023-02-17 05:06 8阅读 0赞

os: centos 7.4.1708  
db: postgresql 11.8  
Slony-I: 2.2.8

192.168.56.111  n1
    192.168.56.112  n2
    192.168.56.113  n3

这3个节点都需要安装 postgresql 11 及 Slony-I

<<[Slony-I 之二 master 节点 + 1目标节点][Slony-I _ master _ _ 1]>>  
上篇 blog 实现了1对1的功能，本次追加一个n3节点，实现 1对2。

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # 
    # 
    # yum list installed |grep -i postgresql
    postgresql11.x86_64                11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-contrib.x86_64        11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-debuginfo.x86_64      11.5-1PGDG.rhel7                    @pgdg11  
    postgresql11-devel.x86_64          11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-docs.x86_64           11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-libs.x86_64           11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-llvmjit.x86_64        11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-odbc.x86_64           12.01.0000-1PGDG.rhel7              @pgdg11  
    postgresql11-plperl.x86_64         11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-plpython.x86_64       11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-pltcl.x86_64          11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-server.x86_64         11.8-1PGDG.rhel7                    @pgdg11  
    postgresql11-tcl.x86_64            2.4.0-2.rhel7.1                     @pgdg11  
    postgresql11-test.x86_64           11.8-1PGDG.rhel7                    @pgdg11
    
    # su - postgres
    Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
    $
    $
    $ psql -c "select version();"
                                                     version                                                 
    ---------------------------------------------------------------------------------------------------------
     PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# n3 节点创建目标表 #

yewu 用户 yewudb 数据库创建参考上一篇 blog <<[Slony-I 之二 master 节点 + 1目标节点][Slony-I _ master _ _ 1]>>

表必须要有主键

$ psql -U yewu yewudb
    psql (11.8)
    Type "help" for help.
    
    yewudb=> create table tmp_t0(c0 int8 primary key,c1 varchar(100));

# 配置 slony #

# su - postgres
    $ ls -l /usr/pgsql-11/bin/sl*
    -rwxr-xr-x 1 root root 496208 Jun 10 17:10 /usr/pgsql-11/bin/slon
    -rwxr-xr-x 1 root root 315976 Jun 10 17:10 /usr/pgsql-11/bin/slonik
    -rwxr-xr-x 1 root root 176736 Jun 10 17:10 /usr/pgsql-11/bin/slony_logshipper

## n3 节点初始化数据 ##

n1 节点使用 pg\_dump 导出指定表的数据到 n3 节点。

## n1 节点(master 节点) 添加节点脚本 ##

# su - postgres
    $ vi slon_addnode_n3.sh
    
    #!/bin/sh
    
    slonik <<_EOF_
    	#--
    	cluster name = slony_example;
    
    	#--
    	node 3 admin conninfo = 'dbname=yewudb host=192.168.56.113 port=5432 user=slony password=slonyslony';
    	node 1 admin conninfo = 'dbname=yewudb host=192.168.56.111 port=5432 user=slony password=slonyslony';
    	
    	#--
    	store node (id=3, comment = 'Slave node n3', event node=1);
    
    _EOF_

执行脚本

$ slon_addnode_n3.sh

## n1 节点(master 节点) 添加path脚本 ##

# su - postgres
    $ vi slon_addnode_n3_path.sh
    
    #!/bin/sh
    
    slonik <<_EOF_
    	#--
    	cluster name = slony_example;
    
    	#--
    	node 3 admin conninfo = 'dbname=yewudb host=192.168.56.113 port=5432 user=slony password=slonyslony';
    	node 1 admin conninfo = 'dbname=yewudb host=192.168.56.111 port=5432 user=slony password=slonyslony';
    	
    	#--
    	store path (server = 1, client = 3, conninfo='dbname=yewudb host=192.168.56.111 port=5432 user=slony password=slonyslony');
    	store path (server = 3, client = 1, conninfo='dbname=yewudb host=192.168.56.113 port=5432 user=slony password=slonyslony');
    	
    _EOF_

执行脚本

$ slon_addnode_n3_path.sh

## n1 节点(master 节点) 添加订阅脚本 ##

# su - postgres
    $ vi slon_addnode_n3_subs.sh
    
    #!/bin/sh
    
    slonik <<_EOF_
    	#--
    	cluster name = slony_example;
    
    	#--
    	node 3 admin conninfo = 'dbname=yewudb host=192.168.56.113 port=5432 user=slony password=slonyslony';
    	node 1 admin conninfo = 'dbname=yewudb host=192.168.56.111 port=5432 user=slony password=slonyslony';
        
    	# subscribe
    	subscribe set ( id = 1, provider = 1, receiver = 3, forward = yes);
    
    _EOF_

执行脚本

$ slon_addnode_n3_subs.sh

## n3 节点启动守护进程 ##

# su - postgres
    $ slon slony_example 'dbname=yewudb host=192.168.56.113 port=5432 user=slony password=slonyslony';

# 验证 slony #

n1 节点插入测试数据

$ psql -U yewu yewudb
    
    yewudb=> insert into tmp_t0 select 999999,'this is slony test n3';
    
    yewudb=> select * from tmp_t0 where c0=999999;
       c0   |          c1           
    --------+-----------------------
     999999 | this is slony test n3
    (1 row)

n3 节点验证

yewudb=> select * from tmp_t0 where c0=999999;
       c0   |          c1           
    --------+-----------------------
     999999 | this is slony test n3
    (1 row)

# 查看表信息 #

slony 添加了触发器  
n3 节点查看

yewudb=# \d+ tmp_t0
                                              Table "public.tmp_t0"
     Column |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
    --------+------------------------+-----------+----------+---------+----------+--------------+-------------
     c0     | bigint                 |           | not null |         | plain    |              | 
     c1     | character varying(100) |           |          |         | extended |              | 
    Indexes:
        "tmp_t0_pkey" PRIMARY KEY, btree (c0)
    Triggers:
        _slony_example_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON tmp_t0 FOR EACH ROW EXECUTE PROCEDURE _slony_example.denyaccess('_slony_example')
        _slony_example_truncatedeny BEFORE TRUNCATE ON tmp_t0 FOR EACH STATEMENT EXECUTE PROCEDURE _slony_example.deny_truncate()
    Disabled user triggers:
        _slony_example_logtrigger AFTER INSERT OR DELETE OR UPDATE ON tmp_t0 FOR EACH ROW EXECUTE PROCEDURE _slony_example.logtrigger('_slony_example', '1', 'k')
        _slony_example_truncatetrigger BEFORE TRUNCATE ON tmp_t0 FOR EACH STATEMENT EXECUTE PROCEDURE _slony_example.log_truncate('1')

参考:  
http://slony.info/

https://www.slony.info/documentation/2.2/index.html

https://www.slony.info/documentation/2.2/modifyingthings.html

[Slony-I _ master _ _ 1]: https://ctypyb2002.blog.csdn.net/article/details/106712185