2.3.2.3 创建root节点1
[postgres@localhost conf]$ 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
2013-09-27 14:20:10,567 28176 INFO plpgsql is installed
2013-09-27 14:20:10,569 28176 INFO Installing pgq
2013-09-27 14:20:10,572 28176 INFO Reading from /opt/skytools/share/skytools3/pgq.sql
2013-09-27 14:20:10,893 28176 INFO pgq.get_batch_cursor is installed
2013-09-27 14:20:10,894 28176 INFO Installing pgq_ext
2013-09-27 14:20:10,894 28176 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql
2013-09-27 14:20:10,988 28176 INFO Installing pgq_node
2013-09-27 14:20:10,988 28176 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql
2013-09-27 14:20:11,085 28176 INFO Installing londiste
2013-09-27 14:20:11,085 28176 INFO Reading from /opt/skytools/share/skytools3/londiste.sql
2013-09-27 14:20:11,235 28176 INFO londiste.global_add_table is installed
2013-09-27 14:20:11,257 28176 INFO Initializing node
2013-09-27 14:20:11,259 28176 INFO Location registered
2013-09-27 14:20:11,331 28176 INFO Node "part1_root" initialized for queue "l3_part1_q" with type "root"
2013-09-27 14:20:11,336 28176 INFO Done
2.3.2.3 创建root节点2
[postgres@localhost conf]$ londiste3 part2.ini create-root part2_root dbname=part2
2013-09-27 14:20:30,440 28181 WARNING No host= in public connect string, bad idea
2013-09-27 14:20:30,538 28181 INFO plpgsql is installed
2013-09-27 14:20:30,539 28181 INFO Installing pgq
2013-09-27 14:20:30,540 28181 INFO Reading from /opt/skytools/share/skytools3/pgq.sql
2013-09-27 14:20:30,798 28181 INFO pgq.get_batch_cursor is installed
2013-09-27 14:20:30,799 28181 INFO Installing pgq_ext
2013-09-27 14:20:30,799 28181 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql
2013-09-27 14:20:30,892 28181 INFO Installing pgq_node
2013-09-27 14:20:30,892 28181 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql
2013-09-27 14:20:30,975 28181 INFO Installing londiste
2013-09-27 14:20:30,975 28181 INFO Reading from /opt/skytools/share/skytools3/londiste.sql
2013-09-27 14:20:31,102 28181 INFO londiste.global_add_table is installed
2013-09-27 14:20:31,129 28181 INFO Initializing node
2013-09-27 14:20:31,131 28181 INFO Location registered
2013-09-27 14:20:31,436 28181 INFO Node "part2_root" initialized for queue "l3_part2_q" with type "root"
2013-09-27 14:20:31,440 28181 INFO Done
2.3.2.4 创建leaf节点1
[postgres@localhost conf]$ 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, bad idea
2013-09-27 14:44:14,566 28448 INFO plpgsql is installed
2013-09-27 14:44:14,567 28448 INFO pgq is installed
2013-09-27 14:44:14,569 28448 INFO pgq.get_batch_cursor is installed
2013-09-27 14:44:14,570 28448 INFO pgq_ext is installed
2013-09-27 14:44:14,571 28448 INFO pgq_node is installed
2013-09-27 14:44:14,573 28448 INFO londiste is installed
2013-09-27 14:44:14,574 28448 INFO londiste.global_add_table is installed
2013-09-27 14:44:14,592 28448 INFO Initializing node
2013-09-27 14:44:14,635 28448 INFO Location registered
2013-09-27 14:44:14,641 28448 INFO Location registered
2013-09-27 14:44:14,649 28448 INFO Subscriber registered: merge_part1_full1
2013-09-27 14:44:14,654 28448 INFO Location registered
2013-09-27 14:44:14,657 28448 INFO Location registered
2013-09-27 14:44:14,663 28448 INFO Node "merge_part1_full1" initialized for queue "l3_part1_q" with type "leaf"
2013-09-27 14:44:14,668 28448 INFO Done
2.3.2.5 创建leaf节点2
[postgres@localhost conf]$ 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, bad idea
2013-09-27 14:44:31,974 28457 INFO plpgsql is installed
2013-09-27 14:44:31,975 28457 INFO pgq is installed
2013-09-27 14:44:31,977 28457 INFO pgq.get_batch_cursor is installed
2013-09-27 14:44:31,978 28457 INFO pgq_ext is installed
2013-09-27 14:44:31,979 28457 INFO pgq_node is installed
2013-09-27 14:44:31,980 28457 INFO londiste is installed
2013-09-27 14:44:31,981 28457 INFO londiste.global_add_table is installed
2013-09-27 14:44:31,994 28457 INFO Initializing node
2013-09-27 14:44:32,030 28457 INFO Location registered
2013-09-27 14:44:32,037 28457 INFO Location registered
2013-09-27 14:44:32,044 28457 INFO Subscriber registered: merge_part2_full1
2013-09-27 14:44:32,048 28457 INFO Location registered
2013-09-27 14:44:32,051 28457 INFO Location registered
2013-09-27 14:44:32,058 28457 INFO Node "merge_part2_full1" initialized for queue "l3_part2_q" with type "leaf"
2013-09-27 14:44:32,062 28457 INFO Done
2.3.2.6 启动tricker
[postgres@localhost conf]$ pgqd -d pgqd.ini
2013-09-27 14:39:50.675 28352 LOG Starting pgqd 3.1.5
2.3.2.7 启动worker
[postgres@localhost conf]$ londiste3 -d part1_full1.ini worker
[postgres@localhost conf]$ londiste3 -d part2_full1.ini worker
[postgres@localhost conf]$ 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, data text)"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "mydata_pkey" for table "mydata"
CREATE TABLE
2.3.3.2 root节点加入同步表
[postgres@localhost conf]$ londiste3 part1.ini add-table mydata
2013-09-27 14:49:04,577 28534 INFO Table added: public.mydata
[postgres@localhost conf]$ londiste3 part2.ini add-table mydata
2013-09-27 14:49:12,833 28539 INFO Table added: public.mydata
[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"
nr | queue_name | table_name | local | merge_state | custom_snapshot | dropped_ddl | table_attrs | dest_table
----+------------+---------------+-------+-------------+-----------------+-------------+-------------+------------
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@localhost conf]$ 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
2013-09-27 14:53:22,137 28611 INFO Table added: public.mydata
【以下为两个queue在同步过程中的状态变化】
[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"
nr | queue_name | table_name | local | merge_state | custom_snapshot | dropped_ddl | table_attrs | dest_table
----+------------+---------------+-------+-------------+-----------------+-------------+-------------+------------
1 | l3_part1_q | public.mydata | t | | | | |
2 | l3_part2_q | public.mydata | t | | | | |
(2 rows)
[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"
nr | queue_name | table_name | local | merge_state | custom_snapshot | dropped_ddl | table_attrs | dest_table
----+------------+---------------+-------+-------------+-----------------+-------------+-------------+------------
1 | l3_part1_q | public.mydata | t | in-copy | | | |
2 | l3_part2_q | public.mydata | t | in-copy | | | |
(2 rows)
[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"
nr | queue_name | table_name | local | merge_state | custom_snapshot | dropped_ddl | table_attrs | dest_table
----+------------+---------------+-------+-------------+-----------------+------------------------------------------------------+-------------+------------
1 | l3_part1_q | public.mydata | t | catching-up | 2669:2669: | ALTER TABLE public.mydata ADD CONSTRAINT mydata_pkey+| |
| | | | | | PRIMARY KEY (id); | |
2 | l3_part2_q | public.mydata | t | catching-up | 2681:2681: | | |
(2 rows)
[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"
nr | queue_name | table_name | local | merge_state | custom_snapshot | dropped_ddl | table_attrs | dest_table
----+------------+---------------+-------+-------------+-----------------+-------------+-------------+------------
1 | l3_part1_q | public.mydata | t | catching-up | 2669:2669: | | |
2 | l3_part2_q | public.mydata | t | catching-up | 2681:2681: | | |
(2 rows)
[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"
nr | queue_name | table_name | local | merge_state | custom_snapshot | dropped_ddl | table_attrs | dest_table
----+------------+---------------+-------+---------------+-----------------+-------------+-------------+------------
1 | l3_part1_q | public.mydata | t | wanna-sync:27 | 2669:2669: | | |
2 | l3_part2_q | public.mydata | t | catching-up | 2681:2681: | | |
(2 rows)
[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"
nr | queue_name | table_name | local | merge_state | custom_snapshot | dropped_ddl | table_attrs | dest_table
----+------------+---------------+-------+---------------+-----------------+-------------+-------------+------------
1 | l3_part1_q | public.mydata | t | ok | 2669:2669: | | |
2 | l3_part2_q | public.mydata | t | wanna-sync:24 | 2681:2681: | | |
(2 rows)
[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"
nr | queue_name | table_name | local | merge_state | custom_snapshot | dropped_ddl | table_attrs | dest_table
----+------------+---------------+-------+-------------+-----------------+-------------+-------------+------------
1 | l3_part1_q | public.mydata | t | ok | 2669:2669: | | |
2 | l3_part2_q | public.mydata | t | ok | 2681:2681: | | |
(2 rows)
{merge_state为ok时表明同步完成}
2.3.3.5 测试同步情况
[postgres@localhost ~]$ psql full1
psql (9.0.4)
Type "help" for help.
full1=# SELECT * from mydata;
id | data
----+-------------
1 | lianshunke1
2 | lianshunke2
(2 rows)
{数据已经同步}
【向part1中插入数据】
full1=# \c part1
You are now connected to database "part1".
part1=# INSERT INTO mydata VALUES (11,'lianshunke11');
INSERT 0 1
【向part2中插入数据】
part1=# \c part2
You are now connected to database "part2".
part2=# INSERT INTO mydata VALUES (22,'lianshunke22');
INSERT 0 1
【在full1中查看同步情况】
part2=# \c full1
You are now connected to database "full1".
full1=# SELECT * from mydata;
id | data
----+--------------
1 | lianshunke1
2 | lianshunke2
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中删除数据】
full1=# \c part1
You are now connected to database "part1".
part1=# SELECT * from mydata ;
id | data
----+--------------
1 | lianshunke1
11 | lianshunke11
(2 rows)
part1=# DELETE FROM mydata where id=11;
DELETE 1
【在full1中查看】
part1=# \c full1
You are now connected to database "full1".
full1=# SELECT * from mydata ;
id | data
----+--------------
1 | lianshunke1
2 | lianshunke2
22 | lianshunke22
(3 rows)
{数据被删除}
2.3.4 拓扑情况
[postgres@localhost conf]$ 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@localhost conf]$ londiste3 part2.ini status
Queue: l3_part2_q Local node: part2_root
part2_root (root)
| Tables: 1/0/0
| Lag: 50s, Tick: 31, NOT UPTODATE
+--: merge_part2_full1 (leaf)
Tables: 1/0/0
Lag: 50s, Tick: 31
2.3.5 同步表状态
[postgres@localhost conf]$ londiste3 part1.ini tables
Tables on node
table_name merge_state table_attrs
--------------- --------------- ---------------
public.mydata ok
[postgres@localhost conf]$ londiste3 part2.ini tables
Tables on node
table_name merge_state table_attrs
--------------- --------------- ---------------
public.mydata ok
2.3.6 node状态
[postgres@localhost conf]$ 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@localhost conf]$ londiste3 part2.ini members
Member info on part2_root@l3_part2_q:
node_name dead node_location
----------------- --------------- ---------------
merge_part2_full1 False dbname=full1
part2_root False dbname=part2
2.3.7 同步状态比较
[postgres@localhost conf]$ 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:32,108 18193 INFO Node part1_root seems good source, using it
2013-10-11 10:53:32,109 18193 INFO public.mydata: Using node part1_root as provider
2013-10-11 10:53:32,131 18193 INFO Provider: part1_root (root)
2013-10-11 10:53:32,162 18193 INFO Locking public.mydata
2013-10-11 10:53:32,181 18193 INFO Syncing public.mydata
2013-10-11 10:53:34,713 18193 INFO Counting public.mydata
2013-10-11 10:53:34,885 18193 INFO srcdb: 2 rows, checksum=-3203416869
2013-10-11 10:53:34,887 18193 INFO dstdb: 2 rows, checksum=-3203416869
{此处可以看到源端与目标端的同步行数以及校验值，执行compare操作会对当前queue所针对的表进行一次同步}
[postgres@localhost conf]$ 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:39,771 18203 INFO Node part2_root seems good source, using it
2013-10-11 10:53:39,772 18203 INFO public.mydata: Using node part2_root as provider
2013-10-11 10:53:39,791 18203 INFO Provider: part2_root (root)
2013-10-11 10:53:39,814 18203 INFO Locking public.mydata
2013-10-11 10:53:39,815 18203 INFO Syncing public.mydata
2013-10-11 10:53:42,331 18203 INFO Counting public.mydata
2013-10-11 10:53:42,482 18203 INFO srcdb: 2 rows, checksum=-1518757964
2013-10-11 10:53:42,484 18203 INFO dstdb: 2 rows, checksum=-1518757964
2.4、分割复制模式
HOSTNAME | IP | PG_VERSION | USER | PASSWORD | PORT | DB_NAME | ROLE |
localhost | localhost | 9.3rc1 for centos64 | postgres | highgo | 5432 | part_root | root |
localhost | localhost | 9.3rc1 for centos64 | postgres | highgo | 5432 | part_part0 | leaf1 |
localhost | localhost | 9.3rc1 for centos64 | postgres | highgo | 5432 | part_part1 | leaf2 |
2.4.1 前期准备
2.4.1.1 创建数据库
postgres=# create database part_root;
CREATE DATABASE
postgres=# create database part_part0;
CREATE DATABASE
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,
part_part0(# db_code bigint,
part_part0(# is_primary boolean,
part_part0(# max_slot integer,
part_part0(# cluster_name text
part_part0(# );
CREATE TABLE
part_part0=# insert into partconf.conf(part_nr, max_part) values(0,1);
INSERT 0 1
【part_part1】
part_part1=# CREATE SCHEMA partconf;
CREATE SCHEMA
part_part1=# CREATE TABLE partconf.conf (
part_part1(# part_nr integer,
part_part1(# max_part integer,
part_part1(# db_code bigint,
part_part1(# is_primary boolean,
part_part1(# max_slot integer,
part_part1(# cluster_name text
part_part1(# );
CREATE TABLE
part_part1=# insert into partconf.conf(part_nr, max_part) values(1,1);
INSERT 0 1
【part_root】
part_root=# create schema partconf;
CREATE SCHEMA
