linux同步触发器,PostgreSQL的Slony-I数据同步实践（基于触发器）

Slony-I是一个“一主多备”的复制系统，支持级联复制(ascading)和失效转移failover.

官方文档：

slony-I安装配置使用

环境：

主库：centos linux 32bit虚拟机，ip为192.168.100.240

PostgreSQL9.2.13

Slony-I 2.2

备库： centos linux 32bit虚拟机， ip为192.168.100.241

PostgreSQL9.2.13

Slony-I 2.2

1.源码编译安装PostgreSQL

在主库和备库都，进行源码编译、安装、配置PostgreSQL数据库如下：

源码编译安装PostgbreSQL9.2。

安装目录：/opt/pgsql2

源码安装完毕后，要配置pg_hba.conf和postgresql.conf确保主、备库可以远程访问。

2.准备slony-I复制的主、备库

2.1声明环境变量

在主库和备库都，执行：

su - postgres

export CLUSTERNAME=lyy_cluster1

export MASTERDBNAME=masterdb

export SLAVEDBNAME=slavedb

export MASTERHOST=192.168.13.128

export SLAVEHOST=192.168.100.236

export REPLICATIONUSER=postgres

注意：

1.REPLICATIONUSER通常是PostgreSQL数据库的超级用户。

2.修改MASTERHOST和SLAVEHOST时，尽量不要使用localhost，因为可能会导致错误：ERROR remoteListenThread_1: db_getLocalNodeId() returned 2 - wrong database?。

2.2根据环境变量准备数据库

在主库和备库都，根据环境变量来创建相应对象：

--在主服务器中

cd /opt/pgsql2/bin

./createuser  -p 5432 -U postgres -SRD -P $PGBENCHUSER  --若已创建则无需再创建

input password for new user :(此处输入新用户的密码即可)

input password again:(此处输入新用户的密码即可)

password:(此处输入超级用户postgres的密码即可)

./createdb -p 5432 -U postgres -O $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME

password:(此处输入超级用户postgres的密码即可)

--在备用服务器中

cd /opt/pgsql2/bin

./createuser -p 5432 -U postgres -SRD -P $PGBENCHUSER  --若已创建则无需再创建

input password for new user :(此处输入新用户的密码即可)

input password again:(此处输入新用户的密码即可)

password:(此处输入超级用户postgres的密码即可)

./createdb -p 5432 -U postgres  -O $PGBENCHUSER -h $SLAVEHOST $SLAVEDBNAME

password:(此处输入超级用户postgres的密码即可)

--在主服务器，创建要同步的数据表(数据表必须有主键或者唯一键，才能通过slony-i实现数据同步)

[postgres@localhost bin]$ ./psql -U $PGBENCHUSER -h $MASTERHOST -d $MASTERDBNAME

psql (9.2.13)

Type "help" for help..

masterdb=# CREATE TABLE lyy(id int primary key, name varchar);

CREATE TABLE

2.3创建pl/pgsql过程语言

Slony-I 需要数据库有 pl/pgSQL 过程语言，如果模板数据 template1已经安装了pl/pgSQL，那么新建的$MASTERDBNAME也就也有了pl/pgSQL，如果已经存在了，则不需要执行以下语句：

在主库和备库都执行：

--在bin目录下

./createlang -h $MASTERHOST plpgsql $MASTERDBNAME

2.4手动从主库将表定义导入备库

当备库Slony-I 订阅主库之后，Slony-I不能自动从主库拷贝表定义到备库，所以我们需要把表定义从主库导入备库，我们通过 pg_dump来实现表定义的主、备同步：

--在主服务器中执行pg_dump，将表定义备份到备库中

--在bin目录下

[postgres@localhost bin]$ ./pg_dump -s -U postgres -h 192.168.100.240 masterdb | ./psql -U postgres -h 192.168.100.241 slavedb

3.源码编译安装Slony-I

在主库和备库都，按照以下步骤安装slony-i。

下载并解压slony-2.2，然后进行编译配置安装，要确保每一步安装正确后再进行下一步。

cd ../slony-2.2

./configure --with-pgconfigdir=/opt/pgsql944/bin --with-perltools

gmake all

gamke install

注意：如果slony-I后面的配置过程使用altperl脚本，则configure时必须指定--with-perltools。

4. 配置slony并启动同步复制

4.0配置并启用slony同步复制的理论基础(仅供理解)：

Configuring the Database For Replication.

Creating the configuration tables, stored procedures, triggers and configuration is all done through thesloniktool. It is a specialized scripting aid that mostly calls stored procedures in the master/slave (node) databases.

The example that follows usesslonikdirectly (or embedded directly into scripts). This is not necessarily the most pleasant way to get started; there exist tools for buildingslonikscripts under the tools directory, including:

Section 6.1.1- a set of Perl scripts that buildslonikscripts based on a single slon_tools.conf file.

Section 6.1.2- a shell script (e.g. - works with Bash) which, based either on self-contained configuration or on shell environment variables, generates a set ofslonikscripts ‍‍to configure a whole cluster.

方法一：Using slonik Command Directly

方法二：Using the altperl Scripts

4.1直接使用slonik命令(以上方法一)

在主库配置slony cluster

在主库，创建并执行slony_setup.sh文件，实现slony cluster的配置：

[postgres@localhost data]$ vi slony_setup.sh

CLUSTERNAME=lyy_cluster1

MASTERDBNAME=masterdb

SLAVEDBNAME=slavedb

MASTERHOST=192.168.100.240

SLAVEHOST=192.168.100.241

REPLICATIONUSER=postgres

/opt/pgsql92/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 masterdb tables');

set add table (set id=1, origin=1, id=1,

fully qualified name = 'public.lyy',

comment='lyy 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@localhost data]$sh slony_setup.sh

注释：本步执行完毕后，初始化完毕一个名为lyy_cluster1的slony集群。

并相应的产生一个名为_lyy_cluster1的模式，里面含有slony运行所需的配置表、序列、函数、触发器等(主要是通过slony-i安装过程中在/opt/pgsql92/share下生成的slony1_base.2.2.4.sql和slony1_funcs.2.2.4.sql)。

还会在同步表lyy下创建相应的触发器。

在主库：

masterdb=# \d lyy

Table "public.lyy"

Column |       Type        | Modifiers

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

id     | integer           | not null

name   | character varying |

Indexes:

"lyy_pkey" PRIMARY KEY, btree (id)

Triggers:

_lyy_cluster1_logtrigger AFTER INSERT OR DELETE OR UPDATE ON lyy FOR EACH ROW EXECUTE PROCEDURE _lyy_cluster1.logtrigger('_lyy_cluster1', '1', 'k')

_lyy_cluster1_truncatetrigger BEFORE TRUNCATE ON lyy FOR EACH STATEMENT EXECUTE PROCEDURE _lyy_cluster1.log_truncate('1')

_lyy_cluster_logtrigger AFTER INSERT OR DELETE OR UPDATE ON lyy FOR EACH ROW EXECUTE PROCEDURE _lyy_cluster.logtrigger('_lyy_cluster', '1', 'k')

_lyy_cluster_truncatetrigger BEFORE TRUNCATE ON lyy FOR EACH STATEMENT EXECUTE PROCEDURE _lyy_cluster.log_truncate('1')

Disabled triggers:

_lyy_cluster1_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON lyy FOR EACH ROW EXECUTE PROCEDURE _lyy_cluster1.denyaccess('_lyy_cluster1')

_lyy_cluster1_truncatedeny BEFORE TRUNCATE ON lyy FOR EACH STATEMENT EXECUTE PROCEDURE _lyy_cluster1.deny_truncate()

_lyy_cluster_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON lyy FOR EACH ROW EXECUTE PROCEDURE _lyy_cluster.denyaccess('_lyy_cluster')

_lyy_cluster_truncatedeny BEFORE TRUNCATE ON lyy FOR EACH STATEMENT EXECUTE PROCEDURE _lyy_cluster.deny_truncate()

在备库：

slavedb=# \d lyy

Table "public.lyy"

Column |       Type        | Modifiers

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

id     | integer           | not null

name   | character varying |

Indexes:

"lyy_pkey" PRIMARY KEY, btree (id)

Triggers:

_lyy_cluster1_denyaccess BEFORE INSERT OR DELETE OR UPDATE ON lyy FOR EACH ROW EXECUTE PROCEDURE _lyy_cluster1.denyaccess('_lyy_cluster1')

_lyy_cluster1_truncatedeny BEFORE TRUNCATE ON lyy FOR EACH STATEMENT EXECUTE PROCEDURE _lyy_cluster1.deny_truncate()

Disabled triggers:

_lyy_cluster1_logtrigger AFTER INSERT OR DELETE OR UPDATE ON lyy FOR EACH ROW EXECUTE PROCEDURE _lyy_cluster1.logtrigger('_lyy_cluster1', '1', 'k')

_lyy_cluster1_truncatetrigger BEFORE TRUNCATE ON lyy FOR EACH STATEMENT EXECUTE PROCEDURE _lyy_cluster1.log_truncate('1')

在主库启动主库节点及监视器

在主库，执行以下命令启动slon daemon：

[postgres@localhost bin]$./slon lyy_cluster1 "dbname=masterdb user=postgres  host=192.168.100.240"&

执行当前命令的终端会不断地返回检测信息，所以该终端不要关闭也不要再执行其他操作。

在备库启动备库节点及监视器

在备库。执行以下命令启动slon deamon：

[postgres@localhost bin]$./slon slony_example "dbname=slavedb user=postgres  host=192.168.100.241" &

执行当前命令的终端会不断地返回检测信息，所以该终端不要关闭也不要再执行其他操作。

在主库执行备库订阅主库

在主库，执行订阅过程, 通过脚本文件subscribe_set.sh来执行订阅过程：

[postgres@localhost data]$ vi subscribe_set.sh

CLUSTERNAME=lyy_cluster1

MASTERDBNAME=masterdb

SLAVEDBNAME=slavedb

MASTERHOST=192.168.100.240

SLAVEHOST=192.168.100.241

REPLICATIONUSER=postgres

/opt/pgsql92/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@localhost data]$sh subscribe_set.sh

目前，本次数据库的slony的同步复制已经配置完毕。

验证slony-I同步复制生效

在主库，向表lyy插入数据：

[postgres@localhost bin]$ ./psql -U postgres -d masterdb

psql (9.2.13)

Type "help" for help.

masterdb=# insert into lyy values(1,'lyy');

INSERT 0 1

在备库，查询表lyy中的数据情况：

postgres@localhost bin]$ ./psql -U postgres -d slavedb

psql (9.2.13)

Type "help" for help.

slavedb=# select * from lyy;

id | name

----+------

(0 rows)

slavedb=# select * from lyy;

id | name

----+------

1 | lyy

(1 row)

可以在主库执行增删操作，然后在备库执行查询操作，进行比对。

注意：slony-I 同步复制的备库是不能对同步的表数据进行修改的：

slavedb=# insert into test values(4,'fff');

ERROR:  Slony-I: Table test is replicated and cannot be modified on a subscriber node - role=0

4.2使用altperl脚本配置并启动(以上方法二)：

配置slony_tool.conf并初始化slony集群

默认情况下，slony的配置文件样本已默认安装至/usr/local/etc/slon_tools.conf-sample。

cd /usr/local/etc

--复制一个slon_tools.conf-sample，命名为slon_tools.conf

[root@localhost etc]#cp slon_tools.conf-sample /usr/local/etc/slon_tools.conf

--开始编辑slon_tools.conf

[root@localhost etc]#vi slon_tools.conf

配置详情：

#修改CLUSTER_NAME为前面我们设置的

# The name of the replication cluster.  This will be used to

# create a schema named _$CLUSTER_NAME in the database which will

# contain Slony-related data.

$CLUSTER_NAME = 'lyy_cluster1';

#slony的pid文件目录。如果没有，则根据提示创建，并授予要求的权限。

# The directory where Slony store PID files.  This

# directory will need to be writable by the user that invokes

# Slony.

$PIDFILE_DIR = '/var/run/slony1';

#日志文件存储目录，如果没有，则修改配置或者根据配置创建目录。

# The directory where Slony should record log messages.  This

# directory will need to be writable by the user that invokes

# Slony.

$LOGDIR = '/var/log/slony1';

#修改节点信息为我们的主、备节点的信息，样本中提供的多出的节点可以注释或者删除掉。

# Include add_node lines for each node in the cluster.  Be sure to

# use host names that will resolve properly on all nodes

# (i.e. only use 'localhost' if all nodes are on the same host).

# Also, note that the user must be a superuser account.

add_node(node     => 1,

host     => '162.168.100.240',

dbname   => 'masterdb',

port     => 5432,

user     => 'postgres',

password => 'postgres');

add_node(node     => 2,

host     => '192.168.100.241',

dbname   => 'slavedb',

port     => 5432,

user     => 'postgres',

password => 'postgres');

#修改要同步的表或者序列

# This array contains a list of tables that already have primary keys.

"pkeyedtables" => [

'public.lyy',

],

#如果没有序列和无主键表需要同步，则将以下示例注释掉。

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

# key, enter their names and indexes here.

#lyy comment this

#       "keyedtables" => {

#           'table3' => 'index_on_table3',

#           'table4' => 'index_on_table4',

#       },

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

# lyy comment this

#       "sequences" => ['sequence1',

#                       'sequence2',

#                       ],

},

#在主库，初始化slony cluster

[root@localhost etc]# slonik_init_cluster | /opt/pgsql92/bin/slonik

:10: Set up replication nodes

:13: Next: configure paths for each node/origin

:16: Replication nodes prepared

:17: Please start a slon replication daemon for each node

注释：同样的，本步执行完毕后，初始化完毕一个名为lyy_cluster1的slony集群。

并相应的产生一个名为_lyy_cluster1的模式，里面含有slony运行所需的配置表、序列、函数、触发器等(主要是通过slony-i安装过程中在/opt/pgsql92/share下生成的slony1_base.2.2.4.sql和slony1_funcs.2.2.4.sql)。

启动slon并进行数据集合订阅：

# 在主库，启动节点1的slon

[root@localhost etc]# slon_start 1

Invoke slon for node 1 - /opt/pgsql92/bin//slon -p /var/run/slony1/lyy_cluster2_node1.pid -s 1000 -d2  lyy_cluster1 'host=192.168.100.240 dbname=master user=postgres port=5432 password=postgres' > /var/log/slony1/node1/master-2015-09-15.log 2>&1 &

Slon successfully started for cluster lyy_cluster1, node node1

PID [7839]

Start the watchdog process as well...

# 在备库，启动节点2的slon

[root@localhost etc]# slon_start 2

Invoke slon for node 2 - /opt/pgsql92/bin//slon -p /var/run/slony1/cluster1_node2.pid -s 1000 -d2  cluster1 'host=192.168.100.241 dbname=slavedb user=postgres port=5432 password=postgres' > /var/log/slony1/node2/slavedb-2015-09-15.log 2>&1 &

Slon successfully started for cluster lyy_cluster1, node node2

PID [7613]

Start the watchdog process as well...

# 在主库，创建数据集合 (此处 1 是一个 set 集合号)

[root@localhost etc]# slonik_create_set 1 | /opt/pgsql92/bin/slonik

:11: Subscription set 1 (set1_name) created

:12: Adding tables to the subscription set

:16: Add primary keyed table public.lyy

:19: Adding sequences to the subscription set

:20: All tables added

# 在主库，订阅 集合1 到 节点2 (1= set ID, 2= node ID)

[root@localhost etc]# slonik_subscribe_set 1 2 | /opt/pgsql92/bin/slonik

:6: Subscribed nodes to set 1

目前，本次数据库的slony的同步复制已经配置完毕。

验证slony-I同步复制生效

在主库，向表lyy插入数据：

[postgres@localhost bin]$ ./psql -U postgres -d masterdb

psql (9.2.13)

Type "help" for help.

masterdb=# insert into lyy values(1,'lyy');

INSERT 0 1

在备库，查询表lyy中的数据情况：

postgres@localhost bin]$ ./psql -U postgres -d slavedb

psql (9.2.13)

Type "help" for help.

slavedb=# select * from lyy;

id | name

----+------

(0 rows)

slavedb=# select * from lyy;

id | name

----+------

1 | lyy

(1 row)

可以在主库执行增删操作，然后在备库执行查询操作，进行比对。

5.Slony-I的其他操作

slony的switchover操作(也就是把主节点改成从节点，从节点升级为主节点)：

slonik_move_set set1 node1 node2 | /opt/pgsql92/bin/slonik

slony的failver操作:

slonik_failover node1 node2 | /opt/pgsql92/bin/slonik

7.slony-I的使用限制

Slony-I只能同步以下内容：

1.表数据(不能同步DDL，表必须含有主键或者唯一键)

2.序列

Slony-I不能自动同步以下内容：

1.对大对象(BLOBS)的变更

2.对DDL(数据定义语句)的变更

3.对用户和角色的变更

这些使用限制的主要原因是：slony-I是通过触发器收集变更情况的，而触发器不能够捕获定义和大对象的修改。对于DDL的变更，slony-I提供了SLONIK EXECUTE SCRIPT命令来执行DDL的SQL脚本，但slony-I不会自动执行，你得手动组织变更的DDL sql语句并用SLONIK EXECUTE SCRIPT(当然这也可以通过直接到备库执行sql语句来实现定义修改)。

另外，如果表定义中with oid，那么原库中的表记录oid的取值不能同步为相同的值。

建议：如果您无法接受以上使用限制，那您值得尝试一下PostgrSQL 8.0之后的PITR(Point in Time Recovery)，PITR对于远程节点是基于WAL日志的。

参考资料：

也可以只用pgbench来测试slony-i的数据同步。