Skytools安装配置管理（三）

2.3.2.3 创建root节点1

[postgres@localhostconf]$ londiste3 part1.ini create-root part1_root dbname=part1

2013-09-27 14:20:10,481 28176 WARNING No host= in public connect string,bad idea

sql is installed

sql

2013-09-27 14:20:11,085 28176 INFO Installing londiste

sql

2.3.2.3 创建root节点2

[postgres@localhostconf]$ londiste3 part2.ini create-root part2_root dbname=part2

2013-09-27 14:20:30,440 28181 WARNING No host= in public connect string,538 28181 INFO plpgsql is installed

sql

2013-09-27 14:20:31,102 28181 INFO londiste.global_add_table is installed

2.3.2.4 创建leaf节点1

[postgres@localhostconf]$ londiste3 part1_full1.ini create-leaf merge_part1_full1 dbname=full1 --provider=dbname=part1

2013-09-27 14:44:14,558 28448 WARNING No host= in public connect string,566 28448 INFO plpgsql is installed

2.3.2.5 创建leaf节点2

[postgres@localhostconf]$ londiste3 part2_full1.ini create-leaf merge_part2_full1 dbname=full1 --provider=dbname=part2

2013-09-27 14:44:31,967 28457 WARNING No host= in public connect string,974 28457 INFO plpgsql is installed

2013-09-27 14:44:32,030 28457 INFO Location registered

2.3.2.6 启动tricker

[postgres@localhostconf]$ pgqd -d pgqd.ini

2013-09-27 14:39:50.675 28352 LOG Starting pgqd 3.1.5

2.3.2.7 启动worker

[postgres@localhostconf]$ londiste3 -d part1_full1.ini worker

[postgres@localhostconf]$ londiste3 -d part2_full1.ini worker

[postgres@localhostconf]$ ps -ef | grep londiste

postgres 28476 1 0 14:45 ? 00:00:00 /opt/python25/bin/python /opt/skytools/bin/londiste3 -d part1_full1.ini worker

postgres 28486 1 0 14:45 ? 00:00:00 /opt/python25/bin/python /opt/skytools/bin/londiste3 -d part2_full1.ini worker

postgres 28491 24970 0 14:46 pts/2 00:00:00 grep londiste

2.3.3 测试

2.3.3.1 创建测试表

[postgres@localhost~]$ psql -d "part1" -c "create table mydata (id int4 primary key,data text)"

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "mydata_pkey" for table "mydata"

CREATE TABLE

[postgres@localhost~]$ psql -d "part2" -c "create table mydata (id int4 primary key,SimSun;vertical-align:baseline;">CREATE TABLE

2.3.3.2 root节点加入同步表

[postgres@localhostconf]$ londiste3 part1.ini add-table mydata

2013-09-27 14:49:04,577 28534 INFO Table added: public.mydata

[postgres@localhostconf]$ londiste3 part2.ini add-table mydata

2013-09-27 14:49:12,833 28539 INFO Table added: public.mydata

[postgres@localhostconf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"

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

1| l3_part1_q | public.mydata | f | | | | |

2| l3_part2_q | public.mydata | f | | | | |

(2 rows)

{看到两个queue已经添加}

2.3.3.3 插入测试数据

[postgres@localhost~]$ psql part1

psql (9.0.4)

Type "help" for help.

part1=# INSERT INTO mydata VALUES (1,'lianshunke1');

INSERT 0 1

part1=# \c part2

You are now connected to database "part2".

part2=# INSERT INTO mydata VALUES (2,'lianshunke2');

INSERT 0 1

2.3.3.4 在full1中创建并合并同步表

[postgres@localhostconf]$ londiste3 part1_full1.ini add-table mydata --create --merge-all

2013-09-27 14:53:21,861 28611 INFO Creating public.mydata

2013-09-27 14:53:22,063 28611 INFO Creating mydata_pkey

【以下为两个queue在同步过程中的状态变化】

1| l3_part1_q | public.mydata | t | | | | |

2| l3_part2_q | public.mydata | t | | | | |

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

1| l3_part1_q | public.mydata | t | catching-up | 2669:2669: | ALTER TABLE public.mydata ADD CONSTRAINT mydata_pkey+| |

2| l3_part2_q | public.mydata | t | catching-up | 2681:2681: | | |

1| l3_part1_q | public.mydata | t | catching-up | 2669:2669: | | |

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

1| l3_part1_q | public.mydata | t | wanna-sync:27 | 2669:2669: | | |

1| l3_part1_q | public.mydata | t | ok | 2669:2669: | | |

2| l3_part2_q | public.mydata | t | wanna-sync:24 | 2681:2681: | | |

2| l3_part2_q | public.mydata | t | ok | 2681:2681: | | |

{merge_state为ok时表明同步完成}

2.3.3.5 测试同步情况

[postgres@localhost~]$ psql full1

full1=# SELECT * from mydata;

id | data

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

1| lianshunke1

2| lianshunke2

{数据已经同步}

【向part1中插入数据】

full1=# \c part1

You are now connected to database "part1".

part1=# INSERT INTO mydata VALUES (11,'lianshunke11');

【向part2中插入数据】

part2=# INSERT INTO mydata VALUES (22,'lianshunke22');

【在full1中查看同步情况】

part2=# \c full1

You are now connected to database "full1".

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

11 | lianshunke11

22 | lianshunke22

(4 rows)

【在full1中删除测试数据】

full1=# DELETE FROM mydata ;

ERROR: Table 'public.mydata' to queue 'l3_part1_q': change not allowed (D)

{同步表禁止在子节点上更改数据}

【在part1中删除数据】

part1=# SELECT * from mydata ;

part1=# DELETE FROM mydata where id=11;

DELETE 1

【在full1中查看】

part1=# \c full1

full1=# SELECT * from mydata ;

(3 rows)

{数据被删除}

2.3.4 拓扑情况

[postgres@localhostconf]$ londiste3 part1.ini status

Queue: l3_part1_q Local node: part1_root

part1_root (root)

| Tables:1/0/0

| Lag:1m0s,Tick: 33,NOT UPTODATE

+--:merge_part1_full1 (leaf)

Tables:1/0/0

Lag:1m0s,Tick: 33

[postgres@localhostconf]$ londiste3 part2.ini status

Queue: l3_part2_q Local node: part2_root

part2_root (root)

| Lag:50s,Tick: 31,SimSun;vertical-align:baseline;"> +--:merge_part2_full1 (leaf)

Lag:50s,Tick: 31

2.3.5 同步表状态

[postgres@localhostconf]$ londiste3 part1.ini tables

Tables on node

table_name merge_state table_attrs

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

public.mydata ok

[postgres@localhostconf]$ londiste3 part2.ini tables

public.mydata ok

2.3.6 node状态

[postgres@localhostconf]$ londiste3 part1.ini members

Member info on part1_root@l3_part1_q:

node_name dead node_location

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

merge_part1_full1 False dbname=full1

part1_root False dbname=part1

[postgres@localhostconf]$ londiste3 part2.ini members

Member info on part2_root@l3_part2_q:

merge_part2_full1 False dbname=full1

part2_root False dbname=part2

2.3.7 同步状态比较

[postgres@localhostconf]$ londiste3 part1.ini compare

2013-10-11 10:53:32,097 18193 INFO Checking if part1_root can be used for copy

2013-10-11 10:53:34,713 18193 INFO Counting public.mydata

{此处可以看到源端与目标端的同步行数以及校验值，执行compare操作会对当前queue所针对的表进行一次同步}

[postgres@localhostconf]$ londiste3 part2.ini compare

2013-10-11 10:53:39,763 18203 INFO Checking if part2_root can be used for copy

2013-10-11 10:53:42,331 18203 INFO Counting public.mydata

2.4、分割复制模式

HOSTNAME	IP	PG_VERSION	USER	PASSWORD	PORT	DB_NAME	ROLE
localhost	9.3rc1 for centos64	postgres	highgo	5432	part_root	root
localhost	9.3rc1 for centos64	postgres	highgo	5432	part_part0	leaf1
part_part1	leaf2

2.4.1 前期准备

2.4.1.1 创建数据库

postgres=# create database part_root;

CREATE DATABASE

postgres=# create database part_part0;

postgres=# create database part_part1;

CREATE DATABASE

2.4.1.2 创建配置模式与配置表

【part_part0】

part_part0=# create schema partconf;

CREATE SCHEMA

part_part0=# CREATE TABLE partconf.conf (

part_part0(# part_nr integer,

part_part0(# max_part integer,SimSun;font-weight:bold;vertical-align:baseline;">part_part0(# db_code bigint,SimSun;font-weight:bold;vertical-align:baseline;">part_part0(# is_primary boolean,SimSun;font-weight:bold;vertical-align:baseline;">part_part0(# max_slot integer,SimSun;font-weight:bold;vertical-align:baseline;">part_part0(# cluster_name text

part_part0(# );

part_part0=# insert into partconf.conf(part_nr,max_part) values(0,1);

【part_part1】

part_part1=# CREATE SCHEMA partconf;

part_part1=# CREATE TABLE partconf.conf (

part_part1(# part_nr integer,SimSun;font-weight:bold;vertical-align:baseline;">part_part1(# max_part integer,SimSun;font-weight:bold;vertical-align:baseline;">part_part1(# db_code bigint,SimSun;font-weight:bold;vertical-align:baseline;">part_part1(# is_primary boolean,SimSun;font-weight:bold;vertical-align:baseline;">part_part1(# max_slot integer,SimSun;font-weight:bold;vertical-align:baseline;">part_part1(# cluster_name text

part_part1(# );

part_part1=# insert into partconf.conf(part_nr,max_part) values(1,SimSun;font-size:14px;vertical-align:baseline;">【part_root】

part_root=# create schema partconf;