linux下slony-i的使用

工具下载

下载：http://www.slony.info/

两边都需要安装且同步的表提前创建好！

解压缩

tar -jxvf slony1-2.1.4.tar.bz2

源端：192.168.1.100

目标端：192.168.1.101

编译安装

[highgo@pg962 slony1-2.1.4]$ ./configure --with-pgconfigdir=/home/highgo/hgdb/bin --with-perltools

make

make install 【root用户执行，否则/usr/local/etc权限不够】

All of Slony-I is successfully installed

[postgres@postgres1 data]$ which postgres

/opt/pg9.4.4/bin/postgres

[postgres@postgres1 data]$ which slon

/opt/pg9.4.4/bin/slon

[postgres@postgres1 data]$ which slonik

/opt/pg9.4.4/bin/slonik

修改文件：

postgresql.conf文件：

listen_addresses = '*'

port=5432

pg_hba.conf 文件：

# IPv4 local connections:

host all all 127.0.0.1/32 trust

host all all 0.0.0.0/0 trust

创建测试用户和数据库

postgres=# create user slony with superuser password 'slony';

postgres=# create database master;

postgres=# alter database master owner to slony;

postgres=# create user slony with superuser password 'slony';

highgo=# create database slave;

highgo=# alter database slave owner to slony;

master=# create table test01 (id int primary key,name text);

CREATE TABLE

master=# insert into test01 values(1,'测试');

INSERT 0 1

master=# select * from test01;

id | name

----+------

1 | 测试

(1 row)

/usr/local/etc 目录下的slon_tools.conf-sample拷贝成slon_tools.conf

修改配置文件slon_tools.conf

$CLUSTER_NAME = 'cluster01';

$LOGDIR = '/opt/pg9.4.4/slonylog';

add_node(node => 1,

host => '192.168.1.100',

dbname => 'master',

port => 5432,

user => 'slony',

password => 'slony');

add_node(node => 2,

host => '192.168.1.101',

dbname => 'slave',

port => 5432,

user => 'slony',

password => '');

# add_node(node => 3,

# host => 'server3',

# dbname => 'database',

# port => 5432,

# user => 'postgres',

# password => 'slony');

#

# If the node should only receive event notifications from a

# single node (e.g. if it can't access the other nodes), you can

# specify a single parent. The downside to this approach is that

# if the parent goes down, your node becomes stranded.

# add_node(node => 4,

# parent => 3,

# host => 'server4',

# dbname => 'database',

# port => 5432,

# user => 'postgres',

# password => '');

}

# This array contains a list of tables that already have

# primary keys.设置复制集的配置项，同步哪些表和序列

第一部分，复制哪些有主键的表

"pkeyedtables" => [

'test01',

],

# For tables that have unique not null keys, but no primary

# key, enter their names and indexes here.

第二部分，没有主键但是有唯一键的表（注释掉）

# "keyedtables" => {

# 'table3' => 'index_on_table3',

# 'table4' => 'index_on_table4',

# },

# Sequences that need to be replicated should be entered here.

三 同步哪些序列，注释掉

# "sequences" => ['sequence1',

# 'sequence2',

# ],

},

复制集2也不使用，注释掉

# "set2" => {

# "set_id" => 2,

# "table_id" => 6,

# "sequence_id" => 3,

# "pkeyedtables" => ["table6"],

# "keyedtables" => {},

# "sequences" => [],

# },

};

传送工具文件至目标端：

scp slon_tools.conf 192.168.1.101:/usr/local/etc/

初始化集群：

[postgres@postgres1 data]$ slonik_init_cluster | slonik

<stdin>:6: Possible unsupported PostgreSQL version (90404) 9.4, defaulting to 8.4 support

<stdin>:9: Possible unsupported PostgreSQL version (90404) 9.4, defaulting to 8.4 support

<stdin>:10: Set up replication nodes

<stdin>:13: Next: configure paths for each node/origin

<stdin>:16: Replication nodes prepared

<stdin>:17: Please start a slon replication daemon for each node

启动守护进程：

100节点：（其中1代表master节点的节点号）

[postgres@postgres1 data]$ slon_start 1

Invoke slon for node 1 - /opt/pg9.4.4/bin//slon -s 1000 -d2 replication 'host=192.168.1.100 dbname=master user=slony port=5432 password=highgo123' > /opt/pg9.4.4/slony1/slony1/node1/master-2017-03-13.log 2>&1 &

Slon successfully started for cluster replication, node node1

PID [2708]

Start the watchdog process as well...

同样，启动2节点守护进程：（其中2代表slave节点的节点号）

[postgres@postgres2 ~]$ slon_start 2

Invoke slon for node 2 - /opt/pg9.4.4/bin//slon -s 1000 -d2 cluster01 'host=192.168.1.101 dbname=slave user=slony port=5432 password=highgo123' > /opt/pg9.4.4/slonylog/slony1/node2/slave-2017-03-13.log 2>&1 &

Slon successfully started for cluster cluster01, node node2

PID [4000]

Start the watchdog process as well...

创建数据集：

[postgres@postgres1 data]$ slonik_create_set 1 | slonik

<stdin>:11: Subscription set 1 created

<stdin>:12: Adding tables to the subscription set

<stdin>:16: Add primary keyed table public.test01

<stdin>:19: Adding sequences to the subscription set

<stdin>:20: All tables added

使用slonik_subscribe_set 1 2| slonik增加数据订阅者，其中1代表同步集号，第二数字2代表数据订阅者的节点号

[postgres@postgres1 data]$ slonik_subscribe_set 1 2| slonik

<stdin>:6: Subscribed nodes to set 1

配置完成。

测试同步效果：

master=# select * from test01 ;

id | name

----+------

(0 rows)

master=# insert into test01 values (1,'test');

INSERT 0 1

master=# insert into test01 values (2,'test2');

INSERT 0 1

master=# insert into test01 values (3,'测试3');

INSERT 0 1

master=# select * from test01 ;

id | name

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

1 | test

2 | test2

3 | 测试3

(3 rows)

slave=# select * from test01 ;

id | name

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

1 | test

2 | test2

3 | 测试3

(3 rows)

取消数据订阅者

[postgres@postgres1 replication]$ slonik_unsubscribe_set 1 2 | slonik

<stdin>:11: unsubscribed node 2 from set 1

删除数据集

[postgres@postgres1 replication]$ slonik_drop_set 1 | slonik

<stdin>:9: Dropped set 1

查看状态

[postgres@postgres1 replication]$ slon_status 1

There is no such node.

关闭守护进程

[postgres@postgres1 replication]$ slon_kill 1

slon_kill.pl... Killing all slon and slon_watchdog instances for the cluster replication

1. Kill slon watchdogs

slon for cluster replication killed - PID [3418]

2. Kill slon processes

slon for cluster replication killed - PID [3404]

slon for cluster replication killed - PID [3625]

卸载节点

[postgres@postgres1 replication]$ slonik_uninstall_nodes | slonik

<stdin>:4: NOTICE: Slony-I: Please drop schema "_replication"

<stdin>:4: NOTICE: drop cascades to 158 other objects

DETAIL: drop cascades to table _replication.sl_node

drop cascades to table _replication.sl_nodelock

drop cascades to table _replication.sl_set

。。。。。。

drop cascades to function _replication.setdroptable(integer)

drop cascades to function _replication.setdroptable_int(integer)

and 58 other objects (see server log for list)

重新查看状态：

[postgres@postgres1 replication]$ slon_status 1

There is no such node.

查看配置：

[postgres@postgres1 replication]$ slony_show_configuration

Slony Configuration

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

Slony-I Cluster: replication

Logs stored under /opt/pg9.4.4/slony1

Slony Binaries in: /opt/pg9.4.4/bin/

Node information

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

Node: 1 Host: 192.168.1.100 User: slony Port: 5432 Forwarding? Parent: 0 Database: master

DSN: host=192.168.1.100 dbname=master user=slony port=5432 password=slony

Node: 2 Host: 192.168.1.101 User: slony Port: 5432 Forwarding? Parent: 0 Database: slave

DSN: host=192.168.1.101 dbname=slave user=slony port=5432 password=slony

一个关于slony-i问题的国外网站

https://sites.google.com/site/pgsqldoc/Home/slony