Tommy's Blog

Schema改动
~~~~~~~~~~
假设slony已经配好并且开始运行了，通常有两种方法修改schema：

方法一：直接删除master库中所有slony相关的数据

$ psql --dbname contactdb
contactdb=> drop schema "_contact_cluster" cascade;

有些表中有slony自动创建的index，也需要删除。然后修改schema，及相关的cluster_setup.sh脚本，把slave上的contactdb_slave直接删掉，然后重新执行相关的slony脚本。

方法二：使用slonik 的 Merge Set命令

$ vi changeschema.sh
#!/bin/bash

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';

execute script ( SET ID = 1, FILENAME = 'changes20050219.sql', EVENT NODE = 1);
_EOF_

$ vi changes20050219.sql
create table newtable (
 name        varchar(50),
 phone       varchar(15)
);
ALTER TABLE contact ADD COLUMN col1 varchar(30);

执行脚本，master 上的 schema 变化会同步到slave上，不过不能同步数据，需要后面通过merge set来进行数据同步。

$ chmod +x changeschema.sh
$ ./changeschema.sh

创建一个新的set，set id为999，schema的相关的改动加入此set，注意add table 的id=2不能与之前添加的id重复(即使set id不同也不行)。

$ vi createset-999.sh
#!/bin/bash

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';

table add key (node id = 1, fully qualified name = 'public.newtable');

create set (id = 999, origin = 1, comment = 'All contactdb tables' );
set add table (set id = 999, origin = 1, id = 2,
 full qualified name = 'public.newtable', comment = 'Table newtable', key = serial);

_EOF_

创建一个merge脚本，用来将 id = 999 这个set 合并到 id=1 的 set。

$ vi mergeset-999.sh
#!/bin/bash

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';

# Assuming that set 1 has direct subscriber 2
SUBSCRIBE SET (ID = 999, PROVIDER = 1, RECEIVER = 2);
WAIT FOR EVENT (ORIGIN = 2, CONFIRMED = 1);
SYNC (ID=1);
MERGE SET ( ID = 1, ADD ID = 999, ORIGIN = 1 );
_EOF_

$ chmod +x createset-999.sh
$ ./createset-999.sh
$ chmod +x mergeset-999.sh
$ ./mergeset-999.sh

Pages: 1 2 3 4 5 6 7