本文共 52996 字，大约阅读时间需要 176 分钟。
set:: Group of nodes that distribute a single queue. In addition to copying events around, they also keep same batch boundaries and tick_ids.node:: A database that participates in cascaded copy of a queue.provider:: Node that provides queue data to another.subscriber:: Node that receives queue data from another.
root:: Place where queue data is generated.branch:: * Carries full contents of the queue. * (Londiste) May subscribe to all/some/none of the tables. * (Londiste) Can be provider for initial copy only if subscribes to tableleaf:: Data-only node. Events are replayed, but no queue, thus cannot be provider to other nodes. Nodes where sets from partitions are merged together are also tagged 'leaf', because in per-partition set it cannot be provider to other nodes.merge-leaf:: [Does not exist as separate type, detected as 'leaf' that has 'combined_queue' set.] Exists in per-partition set. - Does not have it's own queue. - (Londiste) Initial COPY is done with --skip-truncate, - Event data is sent to combined queue. - tick_id for each batch is sent to combined queue. - Queue reader from partition to combined-failover must lag behind combined queue coming from combined-root combined-root:: [Does not exist as separate type, detected as 'root' that has 'leaf's with 'combined_queue' set.] - Master for combined queue. Received data from several per-partition queues. - Also is merge-leaf in every per-partition queue. - Queue is filled directly from partition queues.combined-failover:: [Does not exist as separate type, detected as 'branch' that has 'leaf's with 'combined_queue' set.] - participates in combined-set, receives events. - also is queue-only node in every part-set. - but no processing is done, just tracking
skytools3.0.3root: 172.16.3.33:1919:digoal PostgreSQL 9.2beta1branch: 172.16.3.39:1921:digoal PostgreSQL 9.1.3branch: 172.16.3.40:1921:digoal PostgreSQL 9.1.3branch: 172.16.3.150:1919:digoal PostgreSQL 9.1.3branch: 172.16.3.176:1921:digoal PostgreSQL 9.1.3
一、在所有节点上安装skytools3.0.3.-- 参考《londist3 install》http://blog.163.com/digoal@126/blog/static/163877040201242945632912/二、选择一个节点运行worker进程, 当然也可以各自运行在各自的节点上. 本例选择172.16.3.33作为运行worker进程的服务器.三、在所有节点上创建用于复制的超级用户londiste(也可以叫别的名字),并且所有节点都允许172.16.3.33通过londiste访问所有库.可能需要配置防火墙,pg_hba.conf等.
postgres@db5-> psqlpostgres=# create role londiste superuser nocreatedb nocreaterole noinherit login encrypted password 'londiste';CREATE ROLE-- 在所有节点创建测试用户和测试库postgres@db5-> psqlpostgres=# create role digoal nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'digoal';CREATE ROLEpostgres=# create database digoal encoding 'UTF8' template template0 owner digoal;CREATE DATABASEpostgres=# \c digoal digoalYou are now connected to database "digoal" as user "digoal".digoal=> create schema digoal authorization digoal;CREATE SCHEMA-- 在所有节点创建测试表create table user_info(userid int,engname text,cnname text,occupation text,birthday date,signname text,email text,qq numeric,crt_time timestamp without time zone,mod_time timestamp without time zone);create table user_session(userid int,logintime timestamp(0) without time zone,login_count bigint default 0,logouttime timestamp(0) without time zone,online_interval interval default interval '0');create table user_login_rec(id serial8 primary key,userid int,login_time timestamp without time zone,ip inet);create table user_logout_rec(id serial8 primary key,userid int,logout_time timestamp without time zone,ip inet);-- 在172.16.3.33上面初始化数据,作为root节点.insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)select generate_series(1,200000),'digoal.zhou','德哥','DBA','1970-01-01',E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!','digoal@126.com',276732431,clock_timestamp(),NULL;insert into user_session (userid) select generate_series(1,200000);-- 在所有节点添加这两个表的主键set work_mem='2048MB';set maintenance_work_mem='2048MB';alter table user_info add constraint pk_user_info primary key (userid);alter table user_session add constraint pk_user_session primary key (userid);-- 在所有节点创建业务函数-- 模拟用户登录的函数create or replace function f_user_login (i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qqfrom user_info where userid=i_userid;insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());update user_session set logintime=now(),login_count=login_count+1 where userid=i_userid;return;end;$BODY$language plpgsql;-- 模拟用户退出的函数create or replace function f_user_logout(i_userid int,OUT o_result int)as $BODY$declarebegininsert into user_logout_rec (userid,logout_time,ip) values (i_userid,now(),inet_client_addr());update user_session set logouttime=now(),online_interval=online_interval+(now()-logintime) where userid=i_userid;o_result := 0;return;exception when others theno_result := 1;return;end;$BODY$language plpgsql;
mkdir -p /home/pg92/skylondist/logmkdir -p /home/pg92/skylondist/pidmkdir -p /home/pg92/skylondist/etc-- 创建pgqd配置文件, database_list表示这个QUEUE要级联的涉及的库, 本例全部用的是digoal, 所以不需要配置其他的, 如果后续增加了其他库名加入到级联, 则需要添加到database_list中,逗号隔开.
vi /home/pg92/skylondist/etc/pgqd.ini[pgqd]base_connstr = host=172.16.3.33 port=1919 user=londiste password=londisteinitial_database = template1database_list = digoallogfile = /home/pg92/skylondist/log/pgqd.logpidfile = /home/pg92/skylondist/pid/pgqd.pid-- 创建级联中包含的节点的配置文件,节点1
vi /home/pg92/skylondist/etc/digoal_33.ini[londiste3]job_name = digoal_33db = host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londistequeue_name = replikalogfile = /home/pg92/skylondist/log/%(job_name)s.logpidfile = /home/pg92/skylondist/pid/%(job_name)s.pidpgq_autocommit = 1pgq_lazy_fetch = 0parallel_copies = 16vi /home/pg92/skylondist/etc/digoal_39.ini[londiste3]job_name = digoal_39db = host=172.16.3.39 port=1921 user=londiste dbname=digoal password=londistequeue_name = replikalogfile = /home/pg92/skylondist/log/%(job_name)s.logpidfile = /home/pg92/skylondist/pid/%(job_name)s.pidpgq_autocommit = 1pgq_lazy_fetch = 0parallel_copies = 16vi /home/pg92/skylondist/etc/digoal_40.ini[londiste3]job_name = digoal_40db = host=172.16.3.40 port=1921 user=londiste dbname=digoal password=londistequeue_name = replikalogfile = /home/pg92/skylondist/log/%(job_name)s.logpidfile = /home/pg92/skylondist/pid/%(job_name)s.pidpgq_autocommit = 1pgq_lazy_fetch = 0parallel_copies = 16vi /home/pg92/skylondist/etc/digoal_150.ini[londiste3]job_name = digoal_150db = host=172.16.3.150 port=1919 user=londiste dbname=digoal password=londistequeue_name = replikalogfile = /home/pg92/skylondist/log/%(job_name)s.logpidfile = /home/pg92/skylondist/pid/%(job_name)s.pidpgq_autocommit = 1pgq_lazy_fetch = 0parallel_copies = 16vi /home/pg92/skylondist/etc/digoal_176.ini[londiste3]job_name = digoal_176db = host=172.16.3.176 port=1921 user=londiste dbname=digoal password=londistequeue_name = replikalogfile = /home/pg92/skylondist/log/%(job_name)s.logpidfile = /home/pg92/skylondist/pid/%(job_name)s.pidpgq_autocommit = 1pgq_lazy_fetch = 0parallel_copies = 16
londiste3 -v /home/pg92/skylondist/etc/digoal_33.ini create-root digoal_33 "host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste"londiste3 -v /home/pg92/skylondist/etc/digoal_39.ini create-branch digoal_39 "host=172.16.3.39 port=1921 user=londiste dbname=digoal password=londiste" --provider="host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste"londiste3 -v /home/pg92/skylondist/etc/digoal_40.ini create-branch digoal_40 "host=172.16.3.40 port=1921 user=londiste dbname=digoal password=londiste" --provider="host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste"londiste3 -v /home/pg92/skylondist/etc/digoal_150.ini create-branch digoal_150 "host=172.16.3.150 port=1919 user=londiste dbname=digoal password=londiste" --provider="host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste"londiste3 -v /home/pg92/skylondist/etc/digoal_176.ini create-branch digoal_176 "host=172.16.3.176 port=1921 user=londiste dbname=digoal password=londiste" --provider="host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste"
pg92@db-172-16-3-33-> londiste3 -v /home/pg92/skylondist/etc/digoal_33.ini create-root digoal_33 "host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste"2012-06-05 09:10:36,740 7240 DEBUG Connect 'new_node' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:10:36,745 7240 INFO plpgsql is installed2012-06-05 09:10:36,746 7240 INFO Installing pgq2012-06-05 09:10:36,746 7240 INFO Reading from /opt/python2.7.3/share/skytools3/pgq.sql2012-06-05 09:10:37,050 7240 INFO pgq.get_batch_cursor is installed2012-06-05 09:10:37,050 7240 INFO Installing pgq_ext2012-06-05 09:10:37,050 7240 INFO Reading from /opt/python2.7.3/share/skytools3/pgq_ext.sql2012-06-05 09:10:37,208 7240 INFO Installing pgq_node2012-06-05 09:10:37,208 7240 INFO Reading from /opt/python2.7.3/share/skytools3/pgq_node.sql2012-06-05 09:10:37,443 7240 INFO Installing londiste2012-06-05 09:10:37,443 7240 INFO Reading from /opt/python2.7.3/share/skytools3/londiste.sql2012-06-05 09:10:37,680 7240 INFO londiste.global_add_table is installed2012-06-05 09:10:37,681 7240 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:10:37,683 7240 INFO Initializing node2012-06-05 09:10:37,683 7240 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_33', 'host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:10:37,684 7240 INFO Location registered2012-06-05 09:10:37,685 7240 DEBUG exec_cmd: select * from pgq_node.create_node('replika', 'root', 'digoal_33', 'digoal_33', null, null, null)2012-06-05 09:10:37,872 7240 INFO Node "digoal_33" initialized for queue "replika" with type "root"2012-06-05 09:10:37,872 7240 INFO Donepg92@db-172-16-3-33-> londiste3 -v /home/pg92/skylondist/etc/digoal_39.ini create-branch digoal_39 "host=172.16.3.39 port=1921 user=londiste dbname=digoal password=londiste" --provider="host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste"2012-06-05 09:18:50,148 7309 DEBUG Connect 'new_node' to 'host=172.16.3.39 port=1921 user=londiste dbname=digoal [...]'2012-06-05 09:18:50,157 7309 INFO plpgsql is installed2012-06-05 09:18:50,158 7309 INFO pgq is installed2012-06-05 09:18:50,159 7309 INFO pgq.get_batch_cursor is installed2012-06-05 09:18:50,160 7309 INFO pgq_ext is installed2012-06-05 09:18:50,160 7309 INFO pgq_node is installed2012-06-05 09:18:50,160 7309 INFO londiste is installed2012-06-05 09:18:50,161 7309 INFO londiste.global_add_table is installed2012-06-05 09:18:50,162 7309 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:18:50,166 7309 INFO Initializing node2012-06-05 09:18:50,166 7309 DEBUG Connect 'root_db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:18:50,169 7309 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:18:50,176 7309 DEBUG db='host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste' -- type='root' provider='host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste'2012-06-05 09:18:50,176 7309 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:18:50,178 7309 DEBUG exec_query: select * from pgq_node.get_queue_locations('replika')2012-06-05 09:18:50,179 7309 DEBUG Connect 'provider_db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:18:50,181 7309 DEBUG exec_query: select node_type, node_name from pgq_node.get_node_info('replika')2012-06-05 09:18:50,188 7309 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_39', 'host=172.16.3.39 port=1921 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:18:50,191 7309 INFO Location registered2012-06-05 09:18:50,191 7309 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_39', 'host=172.16.3.39 port=1921 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:18:50,194 7309 INFO Location registered2012-06-05 09:18:50,194 7309 DEBUG exec_cmd: select * from pgq_node.register_subscriber('replika', 'digoal_39', 'digoal_39', null)2012-06-05 09:18:50,198 7309 INFO Subscriber registered: digoal_392012-06-05 09:18:50,198 7309 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_39', 'host=172.16.3.39 port=1921 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:18:50,200 7309 INFO Location registered2012-06-05 09:18:50,200 7309 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_33', 'host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste', 'False')2012-06-05 09:18:50,201 7309 INFO Location registered2012-06-05 09:18:50,202 7309 DEBUG exec_cmd: select * from pgq_node.create_node('replika', 'branch', 'digoal_39', 'digoal_39', 'digoal_33', '1', null)2012-06-05 09:18:50,261 7309 INFO Node "digoal_39" initialized for queue "replika" with type "branch"2012-06-05 09:18:50,263 7309 INFO Donepg92@db-172-16-3-33-> londiste3 -v /home/pg92/skylondist/etc/digoal_40.ini create-branch digoal_40 "host=172.16.3.40 port=1921 user=londiste dbname=digoal password=londiste" --provider="host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste"2012-06-05 09:19:00,842 7314 DEBUG Connect 'new_node' to 'host=172.16.3.40 port=1921 user=londiste dbname=digoal [...]'2012-06-05 09:19:00,852 7314 INFO plpgsql is installed2012-06-05 09:19:00,853 7314 INFO Installing pgq2012-06-05 09:19:00,853 7314 INFO Reading from /opt/python2.7.3/share/skytools3/pgq.sql2012-06-05 09:19:00,971 7314 INFO pgq.get_batch_cursor is installed2012-06-05 09:19:00,972 7314 INFO Installing pgq_ext2012-06-05 09:19:00,972 7314 INFO Reading from /opt/python2.7.3/share/skytools3/pgq_ext.sql2012-06-05 09:19:01,023 7314 INFO Installing pgq_node2012-06-05 09:19:01,023 7314 INFO Reading from /opt/python2.7.3/share/skytools3/pgq_node.sql2012-06-05 09:19:01,121 7314 INFO Installing londiste2012-06-05 09:19:01,122 7314 INFO Reading from /opt/python2.7.3/share/skytools3/londiste.sql2012-06-05 09:19:01,211 7314 INFO londiste.global_add_table is installed2012-06-05 09:19:01,212 7314 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:19:01,216 7314 INFO Initializing node2012-06-05 09:19:01,216 7314 DEBUG Connect 'root_db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:19:01,220 7314 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:19:01,227 7314 DEBUG db='host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste' -- type='root' provider='host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste'2012-06-05 09:19:01,227 7314 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:19:01,229 7314 DEBUG exec_query: select * from pgq_node.get_queue_locations('replika')2012-06-05 09:19:01,229 7314 DEBUG Connect 'provider_db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:19:01,232 7314 DEBUG exec_query: select node_type, node_name from pgq_node.get_node_info('replika')2012-06-05 09:19:01,239 7314 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_40', 'host=172.16.3.40 port=1921 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:19:01,242 7314 INFO Location registered2012-06-05 09:19:01,242 7314 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_40', 'host=172.16.3.40 port=1921 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:19:01,244 7314 INFO Location registered2012-06-05 09:19:01,244 7314 DEBUG exec_cmd: select * from pgq_node.register_subscriber('replika', 'digoal_40', 'digoal_40', null)2012-06-05 09:19:01,248 7314 INFO Subscriber registered: digoal_402012-06-05 09:19:01,248 7314 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_40', 'host=172.16.3.40 port=1921 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:19:01,250 7314 INFO Location registered2012-06-05 09:19:01,250 7314 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_33', 'host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste', 'False')2012-06-05 09:19:01,251 7314 INFO Location registered2012-06-05 09:19:01,251 7314 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_39', 'host=172.16.3.39 port=1921 user=londiste dbname=digoal password=londiste', 'False')2012-06-05 09:19:01,252 7314 INFO Location registered2012-06-05 09:19:01,252 7314 DEBUG exec_cmd: select * from pgq_node.create_node('replika', 'branch', 'digoal_40', 'digoal_40', 'digoal_33', '1', null)2012-06-05 09:19:01,298 7314 INFO Node "digoal_40" initialized for queue "replika" with type "branch"2012-06-05 09:19:01,300 7314 INFO Donepg92@db-172-16-3-33-> londiste3 -v /home/pg92/skylondist/etc/digoal_150.ini create-branch digoal_150 "host=172.16.3.150 port=1919 user=londiste dbname=digoal password=londiste" --provider="host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste"2012-06-05 09:19:07,065 7317 DEBUG Connect 'new_node' to 'host=172.16.3.150 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:19:07,073 7317 INFO plpgsql is installed2012-06-05 09:19:07,073 7317 INFO Installing pgq2012-06-05 09:19:07,074 7317 INFO Reading from /opt/python2.7.3/share/skytools3/pgq.sql2012-06-05 09:19:07,223 7317 INFO pgq.get_batch_cursor is installed2012-06-05 09:19:07,223 7317 INFO Installing pgq_ext2012-06-05 09:19:07,223 7317 INFO Reading from /opt/python2.7.3/share/skytools3/pgq_ext.sql2012-06-05 09:19:07,270 7317 INFO Installing pgq_node2012-06-05 09:19:07,271 7317 INFO Reading from /opt/python2.7.3/share/skytools3/pgq_node.sql2012-06-05 09:19:07,325 7317 INFO Installing londiste2012-06-05 09:19:07,325 7317 INFO Reading from /opt/python2.7.3/share/skytools3/londiste.sql2012-06-05 09:19:07,408 7317 INFO londiste.global_add_table is installed2012-06-05 09:19:07,409 7317 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:19:07,413 7317 INFO Initializing node2012-06-05 09:19:07,413 7317 DEBUG Connect 'root_db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:19:07,416 7317 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:19:07,423 7317 DEBUG db='host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste' -- type='root' provider='host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste'2012-06-05 09:19:07,423 7317 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:19:07,425 7317 DEBUG exec_query: select * from pgq_node.get_queue_locations('replika')2012-06-05 09:19:07,425 7317 DEBUG Connect 'provider_db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:19:07,429 7317 DEBUG exec_query: select node_type, node_name from pgq_node.get_node_info('replika')2012-06-05 09:19:07,436 7317 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_150', 'host=172.16.3.150 port=1919 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:19:07,438 7317 INFO Location registered2012-06-05 09:19:07,439 7317 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_150', 'host=172.16.3.150 port=1919 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:19:07,441 7317 INFO Location registered2012-06-05 09:19:07,441 7317 DEBUG exec_cmd: select * from pgq_node.register_subscriber('replika', 'digoal_150', 'digoal_150', null)2012-06-05 09:19:07,445 7317 INFO Subscriber registered: digoal_1502012-06-05 09:19:07,445 7317 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_150', 'host=172.16.3.150 port=1919 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:19:07,447 7317 INFO Location registered2012-06-05 09:19:07,447 7317 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_33', 'host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste', 'False')2012-06-05 09:19:07,448 7317 INFO Location registered2012-06-05 09:19:07,449 7317 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_39', 'host=172.16.3.39 port=1921 user=londiste dbname=digoal password=londiste', 'False')2012-06-05 09:19:07,449 7317 INFO Location registered2012-06-05 09:19:07,450 7317 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_40', 'host=172.16.3.40 port=1921 user=londiste dbname=digoal password=londiste', 'False')2012-06-05 09:19:07,450 7317 INFO Location registered2012-06-05 09:19:07,451 7317 DEBUG exec_cmd: select * from pgq_node.create_node('replika', 'branch', 'digoal_150', 'digoal_150', 'digoal_33', '1', null)2012-06-05 09:19:07,490 7317 INFO Node "digoal_150" initialized for queue "replika" with type "branch"2012-06-05 09:19:07,492 7317 INFO Donepg92@db-172-16-3-33-> londiste3 -v /home/pg92/skylondist/etc/digoal_176.ini create-branch digoal_176 "host=172.16.3.176 port=1921 user=londiste dbname=digoal password=londiste" --provider="host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste"2012-06-05 09:19:14,456 7321 DEBUG Connect 'new_node' to 'host=172.16.3.176 port=1921 user=londiste dbname=digoal [...]'2012-06-05 09:19:14,464 7321 INFO plpgsql is installed2012-06-05 09:19:14,465 7321 INFO Installing pgq2012-06-05 09:19:14,465 7321 INFO Reading from /opt/python2.7.3/share/skytools3/pgq.sql2012-06-05 09:19:14,922 7321 INFO pgq.get_batch_cursor is installed2012-06-05 09:19:14,923 7321 INFO Installing pgq_ext2012-06-05 09:19:14,923 7321 INFO Reading from /opt/python2.7.3/share/skytools3/pgq_ext.sql2012-06-05 09:19:15,159 7321 INFO Installing pgq_node2012-06-05 09:19:15,160 7321 INFO Reading from /opt/python2.7.3/share/skytools3/pgq_node.sql2012-06-05 09:19:15,422 7321 INFO Installing londiste2012-06-05 09:19:15,422 7321 INFO Reading from /opt/python2.7.3/share/skytools3/londiste.sql2012-06-05 09:19:15,724 7321 INFO londiste.global_add_table is installed2012-06-05 09:19:15,725 7321 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:19:15,728 7321 INFO Initializing node2012-06-05 09:19:15,729 7321 DEBUG Connect 'root_db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:19:15,731 7321 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:19:15,739 7321 DEBUG db='host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste' -- type='root' provider='host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste'2012-06-05 09:19:15,739 7321 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:19:15,740 7321 DEBUG exec_query: select * from pgq_node.get_queue_locations('replika')2012-06-05 09:19:15,741 7321 DEBUG Connect 'provider_db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:19:15,744 7321 DEBUG exec_query: select node_type, node_name from pgq_node.get_node_info('replika')2012-06-05 09:19:15,751 7321 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_176', 'host=172.16.3.176 port=1921 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:19:15,754 7321 INFO Location registered2012-06-05 09:19:15,754 7321 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_176', 'host=172.16.3.176 port=1921 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:19:15,756 7321 INFO Location registered2012-06-05 09:19:15,756 7321 DEBUG exec_cmd: select * from pgq_node.register_subscriber('replika', 'digoal_176', 'digoal_176', null)2012-06-05 09:19:15,760 7321 INFO Subscriber registered: digoal_1762012-06-05 09:19:15,760 7321 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_176', 'host=172.16.3.176 port=1921 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:19:15,761 7321 INFO Location registered2012-06-05 09:19:15,762 7321 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_33', 'host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londiste', 'False')2012-06-05 09:19:15,762 7321 INFO Location registered2012-06-05 09:19:15,763 7321 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_150', 'host=172.16.3.150 port=1919 user=londiste dbname=digoal password=londiste', 'False')2012-06-05 09:19:15,763 7321 INFO Location registered2012-06-05 09:19:15,764 7321 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_39', 'host=172.16.3.39 port=1921 user=londiste dbname=digoal password=londiste', 'False')2012-06-05 09:19:15,764 7321 INFO Location registered2012-06-05 09:19:15,765 7321 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_40', 'host=172.16.3.40 port=1921 user=londiste dbname=digoal password=londiste', 'False')2012-06-05 09:19:15,765 7321 INFO Location registered2012-06-05 09:19:15,765 7321 DEBUG exec_cmd: select * from pgq_node.create_node('replika', 'branch', 'digoal_176', 'digoal_176', 'digoal_33', '1', null)2012-06-05 09:19:16,041 7321 INFO Node "digoal_176" initialized for queue "replika" with type "branch"2012-06-05 09:19:16,043 7321 INFO Done pg92@db-172-16-3-33-> pgqd -d /home/pg92/skylondist/etc/pgqd.ini2012-06-05 09:20:57.298 7346 LOG Starting pgqd 3.0.3
pg92@db-172-16-3-33-> londiste3 -v /home/pg92/skylondist/etc/digoal_33.ini status2012-06-05 09:21:44,988 7363 DEBUG Connect 'db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:21:44,991 7363 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:21:44,999 7363 DEBUG exec_query: select * from pgq_node.get_queue_locations('replika')2012-06-05 09:21:45,000 7363 DEBUG Connect 'look_db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:21:45,014 7363 DEBUG Connect 'look_db' to 'host=172.16.3.150 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:21:45,038 7363 DEBUG Connect 'look_db' to 'host=172.16.3.39 port=1921 user=londiste dbname=digoal [...]'2012-06-05 09:21:45,058 7363 DEBUG Connect 'look_db' to 'host=172.16.3.40 port=1921 user=londiste dbname=digoal [...]'2012-06-05 09:21:45,077 7363 DEBUG Connect 'look_db' to 'host=172.16.3.176 port=1921 user=londiste dbname=digoal [...]'Queue: replika Local node: digoal_33digoal_33 (root) | Tables: 0/0/0 | Lag: 22s, Tick: 5, NOT UPTODATE +--digoal_150 (branch) | Tables: 0/0/0 | Lag: 11m7s, Tick: 1, NOT UPTODATE +--digoal_176 (branch) | Tables: 0/0/0 | Lag: 11m7s, Tick: 1, NOT UPTODATE +--digoal_39 (branch) | Tables: 0/0/0 | Lag: 11m7s, Tick: 1, NOT UPTODATE +--digoal_40 (branch) Tables: 0/0/0 Lag: 11m7s, Tick: 1, NOT UPTODATEpg92@db-172-16-3-33-> londiste3 -v /home/pg92/skylondist/etc/digoal_33.ini members2012-06-05 09:21:51,517 7366 DEBUG Connect 'db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:21:51,520 7366 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:21:51,528 7366 DEBUG exec_query: select * from pgq_node.get_queue_locations('replika')2012-06-05 09:21:51,528 7366 DEBUG display_table: select node_name, dead, node_location from pgq_node.get_queue_locations('replika') order by 1Member info on digoal_33@replika:node_name dead node_location--------------- --------------- -------------------------------------------------------------------------digoal_150 False host=172.16.3.150 port=1919 user=londiste dbname=digoal password=londistedigoal_176 False host=172.16.3.176 port=1921 user=londiste dbname=digoal password=londistedigoal_33 False host=172.16.3.33 port=1919 user=londiste dbname=digoal password=londistedigoal_39 False host=172.16.3.39 port=1921 user=londiste dbname=digoal password=londistedigoal_40 False host=172.16.3.40 port=1921 user=londiste dbname=digoal password=londiste londiste3 -d /home/pg92/skylondist/etc/digoal_33.ini workerlondiste3 -d /home/pg92/skylondist/etc/digoal_39.ini workerlondiste3 -d /home/pg92/skylondist/etc/digoal_40.ini workerlondiste3 -d /home/pg92/skylondist/etc/digoal_150.ini workerlondiste3 -d /home/pg92/skylondist/etc/digoal_176.ini worker-- 查看进程
pg92@db-172-16-3-33-> ps -ewf|grep pythonpg92 7385 1 0 09:23 ? 00:00:00 /opt/python2.7.3/bin/python /opt/skytools3.0.3/bin/londiste3 -d /home/pg92/skylondist/etc/digoal_33.ini workerpg92 7389 1 0 09:23 ? 00:00:00 /opt/python2.7.3/bin/python /opt/skytools3.0.3/bin/londiste3 -d /home/pg92/skylondist/etc/digoal_39.ini workerpg92 7392 1 0 09:23 ? 00:00:00 /opt/python2.7.3/bin/python /opt/skytools3.0.3/bin/londiste3 -d /home/pg92/skylondist/etc/digoal_40.ini workerpg92 7395 1 0 09:23 ? 00:00:00 /opt/python2.7.3/bin/python /opt/skytools3.0.3/bin/londiste3 -d /home/pg92/skylondist/etc/digoal_150.ini workerpg92 7398 1 0 09:23 ? 00:00:00 /opt/python2.7.3/bin/python /opt/skytools3.0.3/bin/londiste3 -d /home/pg92/skylondist/etc/digoal_176.ini worker-- 使用root的配置文件, 查看未加入复制的表
pg92@db-172-16-3-33-> londiste3 -v /home/pg92/skylondist/etc/digoal_33.ini missing2012-06-05 09:25:25,876 7431 DEBUG Connect 'db' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:25:25,880 7431 DEBUG display_table: select * from londiste.local_show_missing('replika')Missing objects on nodeobj_kind obj_name--------------- -----------------------------S digoal.user_login_rec_id_seqS digoal.user_logout_rec_id_seqr digoal.user_infor digoal.user_login_recr digoal.user_logout_recr digoal.user_session -- 在root节点上注册要复制的表和序列 londiste3 -v /home/pg92/skylondist/etc/digoal_33.ini add-table digoal.user_info digoal.user_login_rec digoal.user_logout_rec digoal.user_sessionlondiste3 -v /home/pg92/skylondist/etc/digoal_33.ini add-seq digoal.user_login_rec_id_seq digoal.user_logout_rec_id_seq-- 在branch节点上添加要复制的表, 由于在branch节点上, 这些表和序列都已经提前创建好了, 所以这里直接添加就行了.
londiste3 -v /home/pg92/skylondist/etc/digoal_39.ini add-table digoal.user_info digoal.user_login_rec digoal.user_logout_rec digoal.user_sessionlondiste3 -v /home/pg92/skylondist/etc/digoal_39.ini add-seq digoal.user_login_rec_id_seq digoal.user_logout_rec_id_seqlondiste3 -v /home/pg92/skylondist/etc/digoal_40.ini add-table digoal.user_info digoal.user_login_rec digoal.user_logout_rec digoal.user_sessionlondiste3 -v /home/pg92/skylondist/etc/digoal_40.ini add-seq digoal.user_login_rec_id_seq digoal.user_logout_rec_id_seqlondiste3 -v /home/pg92/skylondist/etc/digoal_150.ini add-table digoal.user_info digoal.user_login_rec digoal.user_logout_rec digoal.user_sessionlondiste3 -v /home/pg92/skylondist/etc/digoal_150.ini add-seq digoal.user_login_rec_id_seq digoal.user_logout_rec_id_seqlondiste3 -v /home/pg92/skylondist/etc/digoal_176.ini add-table digoal.user_info digoal.user_login_rec digoal.user_logout_rec digoal.user_sessionlondiste3 -v /home/pg92/skylondist/etc/digoal_176.ini add-seq digoal.user_login_rec_id_seq digoal.user_logout_rec_id_seq五、数据比较
pg92@db-172-16-3-33-> londiste3 /home/pg92/skylondist/etc/digoal_39.ini compare2012-06-05 09:43:17,395 7916 INFO Locking digoal.user_info2012-06-05 09:43:17,396 7916 INFO Syncing digoal.user_info2012-06-05 09:43:20,404 7916 INFO Counting digoal.user_info2012-06-05 09:43:21,176 7916 INFO srcdb: 200000 rows, checksum=-1549351477362012-06-05 09:43:22,267 7916 INFO dstdb: 200000 rows, checksum=-1549351477362012-06-05 09:43:22,268 7916 INFO Locking digoal.user_login_rec2012-06-05 09:43:22,269 7916 INFO Syncing digoal.user_login_rec2012-06-05 09:43:25,275 7916 INFO Counting digoal.user_login_rec2012-06-05 09:43:25,275 7916 INFO srcdb: 0 rows, checksum=None2012-06-05 09:43:25,276 7916 INFO dstdb: 0 rows, checksum=None2012-06-05 09:43:25,278 7916 INFO Locking digoal.user_logout_rec2012-06-05 09:43:25,278 7916 INFO Syncing digoal.user_logout_rec2012-06-05 09:43:28,283 7916 INFO Counting digoal.user_logout_rec2012-06-05 09:43:28,283 7916 INFO srcdb: 0 rows, checksum=None2012-06-05 09:43:28,284 7916 INFO dstdb: 0 rows, checksum=None2012-06-05 09:43:28,285 7916 INFO Locking digoal.user_session2012-06-05 09:43:28,286 7916 INFO Syncing digoal.user_session2012-06-05 09:43:31,290 7916 INFO Counting digoal.user_session2012-06-05 09:43:31,573 7916 INFO srcdb: 200000 rows, checksum=3186471605932012-06-05 09:43:32,001 7916 INFO dstdb: 200000 rows, checksum=318647160593pg92@db-172-16-3-33-> londiste3 /home/pg92/skylondist/etc/digoal_40.ini compare2012-06-05 09:43:51,611 7931 INFO Locking digoal.user_info2012-06-05 09:43:51,612 7931 INFO Syncing digoal.user_info2012-06-05 09:43:54,620 7931 INFO Counting digoal.user_info2012-06-05 09:43:55,392 7931 INFO srcdb: 200000 rows, checksum=-1549351477362012-06-05 09:43:56,422 7931 INFO dstdb: 200000 rows, checksum=-1549351477362012-06-05 09:43:56,424 7931 INFO Locking digoal.user_login_rec2012-06-05 09:43:56,424 7931 INFO Syncing digoal.user_login_rec2012-06-05 09:43:59,933 7931 INFO Counting digoal.user_login_rec2012-06-05 09:43:59,934 7931 INFO srcdb: 0 rows, checksum=None2012-06-05 09:43:59,934 7931 INFO dstdb: 0 rows, checksum=None2012-06-05 09:43:59,936 7931 INFO Locking digoal.user_logout_rec2012-06-05 09:43:59,937 7931 INFO Syncing digoal.user_logout_rec2012-06-05 09:44:03,943 7931 INFO Counting digoal.user_logout_rec2012-06-05 09:44:03,944 7931 INFO srcdb: 0 rows, checksum=None2012-06-05 09:44:03,945 7931 INFO dstdb: 0 rows, checksum=None2012-06-05 09:44:03,946 7931 INFO Locking digoal.user_session2012-06-05 09:44:03,947 7931 INFO Syncing digoal.user_session2012-06-05 09:44:07,954 7931 INFO Counting digoal.user_session2012-06-05 09:44:08,238 7931 INFO srcdb: 200000 rows, checksum=3186471605932012-06-05 09:44:08,649 7931 INFO dstdb: 200000 rows, checksum=318647160593pg92@db-172-16-3-33-> londiste3 /home/pg92/skylondist/etc/digoal_150.ini compare2012-06-05 09:44:29,421 7942 INFO Locking digoal.user_info2012-06-05 09:44:29,422 7942 INFO Syncing digoal.user_info2012-06-05 09:44:32,430 7942 INFO Counting digoal.user_info2012-06-05 09:44:33,206 7942 INFO srcdb: 200000 rows, checksum=-1549351477362012-06-05 09:44:34,310 7942 INFO dstdb: 200000 rows, checksum=-1549351477362012-06-05 09:44:34,312 7942 INFO Locking digoal.user_login_rec2012-06-05 09:44:34,313 7942 INFO Syncing digoal.user_login_rec2012-06-05 09:44:37,819 7942 INFO Counting digoal.user_login_rec2012-06-05 09:44:37,820 7942 INFO srcdb: 0 rows, checksum=None2012-06-05 09:44:37,821 7942 INFO dstdb: 0 rows, checksum=None2012-06-05 09:44:37,822 7942 INFO Locking digoal.user_logout_rec2012-06-05 09:44:37,823 7942 INFO Syncing digoal.user_logout_rec2012-06-05 09:44:40,828 7942 INFO Counting digoal.user_logout_rec2012-06-05 09:44:40,828 7942 INFO srcdb: 0 rows, checksum=None2012-06-05 09:44:40,829 7942 INFO dstdb: 0 rows, checksum=None2012-06-05 09:44:40,831 7942 INFO Locking digoal.user_session2012-06-05 09:44:40,831 7942 INFO Syncing digoal.user_session2012-06-05 09:44:43,836 7942 INFO Counting digoal.user_session2012-06-05 09:44:44,121 7942 INFO srcdb: 200000 rows, checksum=3186471605932012-06-05 09:44:44,549 7942 INFO dstdb: 200000 rows, checksum=318647160593pg92@db-172-16-3-33-> londiste3 /home/pg92/skylondist/etc/digoal_176.ini compare2012-06-05 09:44:50,190 7949 INFO Locking digoal.user_info2012-06-05 09:44:50,191 7949 INFO Syncing digoal.user_info2012-06-05 09:44:54,200 7949 INFO Counting digoal.user_info2012-06-05 09:44:54,973 7949 INFO srcdb: 200000 rows, checksum=-1549351477362012-06-05 09:44:56,100 7949 INFO dstdb: 200000 rows, checksum=-1549351477362012-06-05 09:44:56,101 7949 INFO Locking digoal.user_login_rec2012-06-05 09:44:56,102 7949 INFO Syncing digoal.user_login_rec2012-06-05 09:45:00,107 7949 INFO Counting digoal.user_login_rec2012-06-05 09:45:00,107 7949 INFO srcdb: 0 rows, checksum=None2012-06-05 09:45:00,108 7949 INFO dstdb: 0 rows, checksum=None2012-06-05 09:45:00,109 7949 INFO Locking digoal.user_logout_rec2012-06-05 09:45:00,110 7949 INFO Syncing digoal.user_logout_rec2012-06-05 09:45:04,116 7949 INFO Counting digoal.user_logout_rec2012-06-05 09:45:04,117 7949 INFO srcdb: 0 rows, checksum=None2012-06-05 09:45:04,117 7949 INFO dstdb: 0 rows, checksum=None2012-06-05 09:45:04,118 7949 INFO Locking digoal.user_session2012-06-05 09:45:04,119 7949 INFO Syncing digoal.user_session2012-06-05 09:45:08,125 7949 INFO Counting digoal.user_session2012-06-05 09:45:08,408 7949 INFO srcdb: 200000 rows, checksum=3186471605932012-06-05 09:45:08,867 7949 INFO dstdb: 200000 rows, checksum=318647160593六、角色切换(branch和branch, branch和root的互相切换)
pg92@db-172-16-3-33-> londiste3 /home/pg92/skylondist/etc/digoal_33.ini statusQueue: replika Local node: digoal_33digoal_33 (root) | Tables: 4/0/0 | Lag: 10s, Tick: 152 +--digoal_150 (branch) | Tables: 4/0/0 | Lag: 10s, Tick: 152 +--digoal_176 (branch) | Tables: 4/0/0 | Lag: 10s, Tick: 152 +--digoal_39 (branch) | Tables: 4/0/0 | Lag: 10s, Tick: 152 +--digoal_40 (branch) Tables: 4/0/0 Lag: 10s, Tick: 152-- 把digoal_40的provider从digoal_33改成digoal_150
pg92@db-172-16-3-33-> londiste3 -v /home/pg92/skylondist/etc/digoal_40.ini change-provider --provider=digoal_1502012-06-05 09:48:05,457 7990 DEBUG Connect 'db' to 'host=172.16.3.40 port=1921 user=londiste dbname=digoal [...]'2012-06-05 09:48:05,462 7990 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:48:05,472 7990 DEBUG exec_query: select * from pgq_node.get_queue_locations('replika')2012-06-05 09:48:05,474 7990 DEBUG exec_query: select consumer_name, provider_node, last_tick_id from pgq_node.get_consumer_info('replika')2012-06-05 09:48:05,475 7990 DEBUG exec_query: select consumer_name, provider_node, last_tick_id from pgq_node.get_consumer_info('replika')2012-06-05 09:48:05,476 7990 DEBUG exec_cmd: select * from pgq_node.set_consumer_paused('replika', 'digoal_40', 'True')2012-06-05 09:48:05,478 7990 INFO [digoal_40] Consumer digoal_40 tagged as paused2012-06-05 09:48:05,478 7990 INFO Waiting for worker to accept2012-06-05 09:48:05,478 7990 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_40')2012-06-05 09:48:05,480 7990 DEBUG [digoal_40] 100 Ok2012-06-05 09:48:06,480 7990 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_40')2012-06-05 09:48:06,481 7990 DEBUG [digoal_40] 100 Ok2012-06-05 09:48:06,481 7990 INFO Consumer 'digoal_40' on node 'digoal_40' paused2012-06-05 09:48:06,482 7990 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:48:06,483 7990 DEBUG exec_query: select * from pgq_node.get_queue_locations('replika')2012-06-05 09:48:06,485 7990 DEBUG exec_query: select consumer_name, provider_node, last_tick_id from pgq_node.get_consumer_info('replika')2012-06-05 09:48:06,486 7990 DEBUG Connect 'node.digoal_150' to 'host=172.16.3.150 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:48:06,490 7990 DEBUG exec_cmd: select * from pgq_node.register_location('replika', 'digoal_40', 'host=172.16.3.40 port=1921 user=londiste dbname=digoal password=londiste', false)2012-06-05 09:48:06,495 7990 INFO [digoal_150] Location registered2012-06-05 09:48:06,495 7990 DEBUG exec_cmd: select * from pgq_node.register_subscriber('replika', 'digoal_40', 'digoal_40', '153')2012-06-05 09:48:06,504 7990 INFO [digoal_150] Subscriber registered: digoal_402012-06-05 09:48:06,505 7990 DEBUG exec_cmd: select * from pgq_node.change_consumer_provider('replika', 'digoal_40', 'digoal_150')2012-06-05 09:48:06,507 7990 INFO [digoal_40] Consumer provider node set to : digoal_1502012-06-05 09:48:06,507 7990 DEBUG exec_cmd: select * from pgq_node.set_consumer_paused('replika', 'digoal_40', 'False')2012-06-05 09:48:06,508 7990 INFO [digoal_40] Consumer digoal_40 tagged as resumed2012-06-05 09:48:06,508 7990 INFO Waiting for worker to accept2012-06-05 09:48:06,509 7990 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_40')2012-06-05 09:48:06,509 7990 DEBUG [digoal_40] 100 Ok2012-06-05 09:48:07,510 7990 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_40')2012-06-05 09:48:07,511 7990 DEBUG [digoal_40] 100 Ok2012-06-05 09:48:07,512 7990 INFO Consumer 'digoal_40' on node 'digoal_40' resumed2012-06-05 09:48:07,512 7990 DEBUG Connect 'node.digoal_33' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:48:07,515 7990 DEBUG exec_cmd: select * from pgq_node.unregister_subscriber('replika', 'digoal_40')2012-06-05 09:48:07,520 7990 INFO [digoal_33] Subscriber unregistered: digoal_40 -- 查看角色切换后的状态 pg92@db-172-16-3-33-> londiste3 /home/pg92/skylondist/etc/digoal_33.ini statusQueue: replika Local node: digoal_33digoal_33 (root) | Tables: 4/0/0 | Lag: 11s, Tick: 153 +--digoal_176 (branch) | Tables: 4/0/0 | Lag: 11s, Tick: 153 +--digoal_39 (branch) | Tables: 4/0/0 | Lag: 11s, Tick: 153 +--digoal_150 (branch) | Tables: 4/0/0 | Lag: 11s, Tick: 153 +--digoal_40 (branch) Tables: 4/0/0 Lag: 11s, Tick: 153-- 使用takeover切换root节点, 把digoal_39切换成root节点, 在切换前, 在digoal_39上执行一条更新user_info操作的SQL, 看看能不能成功.
digoal=> update user_info set engname='test' where userid=1;ERROR: Insert into queue disallowedCONTEXT: PL/pgSQL function "insert_event" line 24 at RETURNSQL statement "select pgq.insert_event($1, $2, $3, $4, $5, $6, $7)"-- 触发器制止了执行
Triggers: _londiste_replika AFTER INSERT OR DELETE OR UPDATE ON user_info FOR EACH ROW EXECUTE PROCEDURE pgq.logutriga('replika') _londiste_replika_truncate AFTER TRUNCATE ON user_info FOR EACH STATEMENT EXECUTE PROCEDURE pgq.sqltriga('replika')-- 所以数据未被修改digoal=> select engname from user_info where userid =1; engname ------------- digoal.zhou(1 row) -- 接下来把digoal_39切换成root, digoal_33切换成branch. pg92@db-172-16-3-33-> londiste3 -v /home/pg92/skylondist/etc/digoal_39.ini takeover digoal_332012-06-05 09:56:36,656 8096 INFO old: digoal_332012-06-05 09:56:36,656 8096 DEBUG Connect 'db' to 'host=172.16.3.39 port=1921 user=londiste dbname=digoal [...]'2012-06-05 09:56:36,661 8096 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:56:36,671 8096 DEBUG exec_query: select * from pgq_node.get_queue_locations('replika')2012-06-05 09:56:36,672 8096 DEBUG Connect 'node.digoal_33' to 'host=172.16.3.33 port=1919 user=londiste dbname=digoal [...]'2012-06-05 09:56:36,675 8096 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:56:36,682 8096 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:56:36,684 8096 DEBUG exec_cmd: select * from pgq_node.set_consumer_paused('replika', 'digoal_33', 'True')2012-06-05 09:56:36,685 8096 INFO [digoal_33] Consumer digoal_33 tagged as paused2012-06-05 09:56:36,685 8096 INFO Waiting for worker to accept2012-06-05 09:56:36,686 8096 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_33')2012-06-05 09:56:36,687 8096 DEBUG [digoal_33] 100 Ok2012-06-05 09:56:37,687 8096 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_33')2012-06-05 09:56:37,688 8096 DEBUG [digoal_33] 100 Ok2012-06-05 09:56:38,688 8096 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_33')2012-06-05 09:56:38,688 8096 DEBUG [digoal_33] 100 Ok2012-06-05 09:56:39,688 8096 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_33')2012-06-05 09:56:39,689 8096 DEBUG [digoal_33] 100 Ok2012-06-05 09:56:39,689 8096 INFO Consumer 'digoal_33' on node 'digoal_33' paused2012-06-05 09:56:39,689 8096 DEBUG exec_cmd: select * from pgq_node.demote_root('replika', '1', 'digoal_39')2012-06-05 09:56:39,690 8096 INFO [digoal_33] Step 1: Writing disabled for: replika2012-06-05 09:56:39,690 8096 DEBUG exec_cmd: select * from pgq_node.demote_root('replika', '2', 'digoal_39')2012-06-05 09:56:39,694 8096 INFO [digoal_33] Step 2: Inserted last tick: replika2012-06-05 09:56:39,694 8096 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:56:39,696 8096 DEBUG waiting for catchup: need=164, cur=1632012-06-05 09:56:40,696 8096 DEBUG exec_query: select * from pgq_node.get_node_info('replika')2012-06-05 09:56:40,698 8096 DEBUG exec_cmd: select * from pgq_node.set_consumer_paused('replika', 'digoal_39', 'True')2012-06-05 09:56:40,701 8096 INFO [digoal_39] Consumer digoal_39 tagged as paused2012-06-05 09:56:40,701 8096 INFO Waiting for worker to accept2012-06-05 09:56:40,702 8096 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_39')2012-06-05 09:56:40,703 8096 DEBUG [digoal_39] 100 Ok2012-06-05 09:56:41,705 8096 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_39')2012-06-05 09:56:41,706 8096 DEBUG [digoal_39] 100 Ok2012-06-05 09:56:41,706 8096 INFO Consumer 'digoal_39' on node 'digoal_39' paused2012-06-05 09:56:41,706 8096 DEBUG exec_cmd: select * from pgq_node.promote_branch('replika')2012-06-05 09:56:41,711 8096 INFO [digoal_39] Branch node promoted to root2012-06-05 09:56:41,711 8096 DEBUG exec_cmd: select * from pgq_node.register_subscriber('replika', 'digoal_33', 'digoal_33', '164')2012-06-05 09:56:41,717 8096 INFO [digoal_39] Subscriber registered: digoal_332012-06-05 09:56:41,717 8096 DEBUG exec_cmd: select * from pgq_node.unregister_subscriber('replika', 'digoal_39')2012-06-05 09:56:41,720 8096 INFO [digoal_33] Subscriber unregistered: digoal_392012-06-05 09:56:41,720 8096 DEBUG exec_cmd: select * from pgq_node.set_consumer_paused('replika', 'digoal_39', 'False')2012-06-05 09:56:41,721 8096 INFO [digoal_39] Consumer digoal_39 tagged as resumed2012-06-05 09:56:41,721 8096 INFO Waiting for worker to accept2012-06-05 09:56:41,721 8096 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_39')2012-06-05 09:56:41,722 8096 DEBUG [digoal_39] 100 Ok2012-06-05 09:56:42,722 8096 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_39')2012-06-05 09:56:42,723 8096 DEBUG [digoal_39] 100 Ok2012-06-05 09:56:42,723 8096 INFO Consumer 'digoal_39' on node 'digoal_39' resumed2012-06-05 09:56:42,723 8096 DEBUG exec_cmd: select * from pgq_node.demote_root('replika', '3', 'digoal_39')2012-06-05 09:56:42,725 8096 INFO [digoal_33] Step 3: Demoted root to branch: replika2012-06-05 09:56:42,725 8096 DEBUG exec_cmd: select * from pgq_node.set_consumer_paused('replika', 'digoal_33', 'False')2012-06-05 09:56:42,726 8096 INFO [digoal_33] Consumer digoal_33 tagged as resumed2012-06-05 09:56:42,726 8096 INFO Waiting for worker to accept2012-06-05 09:56:42,726 8096 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_33')2012-06-05 09:56:42,727 8096 DEBUG [digoal_33] 100 Ok2012-06-05 09:56:43,727 8096 DEBUG exec_cmd: select * from pgq_node.get_consumer_state('replika', 'digoal_33')2012-06-05 09:56:43,728 8096 DEBUG [digoal_33] 100 Ok2012-06-05 09:56:43,728 8096 INFO Consumer 'digoal_33' on node 'digoal_33' resumed -- 查看状态 pg92@db-172-16-3-33-> londiste3 /home/pg92/skylondist/etc/digoal_39.ini statusQueue: replika Local node: digoal_39digoal_39 (root) | Tables: 4/0/0 | Lag: -1d23h46m26s, Tick: 164 +--digoal_33 (branch) | Tables: 4/0/0 | Lag: (n/a), Tick: 164 +--digoal_176 (branch) | Tables: 4/0/0 | Lag: (n/a), Tick: 164 +--digoal_150 (branch) | Tables: 4/0/0 | Lag: (n/a), Tick: 164 +--digoal_40 (branch) Tables: 4/0/0 Lag: (n/a), Tick: 164-- 在到digoal_39上执行update
digoal=> update user_info set engname='test' where userid=1;UPDATE 1digoal=> select engname from user_info where userid =1; engname --------- test(1 row)
pg92@db-172-16-3-33-> londiste3 /home/pg92/skylondist/etc/digoal_33.ini compare2012-06-05 10:00:37,847 8183 INFO Locking digoal.user_info2012-06-05 10:00:37,848 8183 INFO Syncing digoal.user_info2012-06-05 10:00:47,862 8183 ERROR Job digoal_33 crashed: Ticker seems deadTraceback (most recent call last): File "/opt/python2.7.3/lib/python2.7/site-packages/skytools/scripting.py", line 597, in run_func_safely return func() File "/opt/python2.7.3/lib/python2.7/site-packages/londiste/syncer.py", line 140, in work self.check_table(t1.dest_table, t2.dest_table, lock_db, src_db, dst_db, setup_curs) File "/opt/python2.7.3/lib/python2.7/site-packages/londiste/syncer.py", line 194, in check_table tick_id = self.force_tick(setup_curs) File "/opt/python2.7.3/lib/python2.7/site-packages/londiste/syncer.py", line 166, in force_tick raise Exception("Ticker seems dead")Exception: Ticker seems dead -- 所以在更改root后一定要更改pgqd的连接 pg92@db-172-16-3-33-> vi pgqd.ini [pgqd]base_connstr = host=172.16.3.39 port=1921 user=londiste password=londisteinitial_database = template1database_list = digoallogfile = /home/pg92/skylondist/log/pgqd.logpidfile = /home/pg92/skylondist/pid/pgqd.pid-- 重启pgqd
pg92@db-172-16-3-33-> pgqd -s /home/pg92/skylondist/etc/pgqd.ini SIGINT sentpg92@db-172-16-3-33-> pgqd -d /home/pg92/skylondist/etc/pgqd.ini 2012-06-05 10:02:04.714 8240 LOG Starting pgqd 3.0.3-- 再次比较数据是否一致
pg92@db-172-16-3-33-> londiste3 /home/pg92/skylondist/etc/digoal_33.ini compare2012-06-05 10:02:18,340 8248 INFO Locking digoal.user_info2012-06-05 10:02:18,341 8248 INFO Syncing digoal.user_info2012-06-05 10:02:21,350 8248 INFO Counting digoal.user_info2012-06-05 10:02:22,441 8248 INFO srcdb: 200000 rows, checksum=-1562184979412012-06-05 10:02:23,216 8248 INFO dstdb: 200000 rows, checksum=-1562184979412012-06-05 10:02:23,218 8248 INFO Locking digoal.user_login_rec2012-06-05 10:02:23,219 8248 INFO Syncing digoal.user_login_rec2012-06-05 10:02:26,226 8248 INFO Counting digoal.user_login_rec2012-06-05 10:02:26,227 8248 INFO srcdb: 0 rows, checksum=None2012-06-05 10:02:26,228 8248 INFO dstdb: 0 rows, checksum=None2012-06-05 10:02:26,229 8248 INFO Locking digoal.user_logout_rec2012-06-05 10:02:26,230 8248 INFO Syncing digoal.user_logout_rec2012-06-05 10:02:29,237 8248 INFO Counting digoal.user_logout_rec2012-06-05 10:02:29,238 8248 INFO srcdb: 0 rows, checksum=None2012-06-05 10:02:29,239 8248 INFO dstdb: 0 rows, checksum=None2012-06-05 10:02:29,240 8248 INFO Locking digoal.user_session2012-06-05 10:02:29,241 8248 INFO Syncing digoal.user_session2012-06-05 10:02:32,748 8248 INFO Counting digoal.user_session2012-06-05 10:02:33,171 8248 INFO srcdb: 200000 rows, checksum=3186471605932012-06-05 10:02:33,461 8248 INFO dstdb: 200000 rows, checksum=318647160593-- 查看172.16.3.33上的数据, 已经更新
pg92@db-172-16-3-33-> psql digoal digoalpsql (9.2beta1)Type "help" for help.digoal=> select engname from user_info where userid =1; engname --------- test(1 row)-- 查看172.16.3.40, 150, 176上的数据, 已经更新
digoal=> select engname from user_info where userid=1; engname --------- test(1 row)
转载地址：http://leuta.baihongyu.com/