|
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、分割复制模式 | | | | | | | | localhost | localhost | 9.3rc1 for centos64 | postgres | highgo | 5432 | part_root | root | | localhost | 9.3rc1 for centos64 | postgres | highgo | 5432 | part_part0 | | 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
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2014-7-4 21:24:11
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡