2.2、级联复制模式 | | | | | | | | londiste1 | 192.168.100.30 | 9.3rc1 for centos64 | postgres | highgo | 5432 | db1 | master | | 192.168.100.31 | 9.0.13 for fedora32 | postgres | highgo | 5432 | db2 | | londiste3 | 192.168.100.24 | 9.0.13 for centos64 | postgres | highgo | 5432 | db3 | slave2 | | 192.168.100.25 | 9.0.13 for ubuntu64 | postgres | highgo | 5432 | db4 | | londiste5 | 192.168.100.20 | 9.2.4 for suse32 | postgres | highgo | 5432 | db5 | slave4 |
注:以下实验所有配置只在londiste1上进行,其它节点需要安装skytools。 2.2.1、创建数据库[postgres@londiste1 londiste3]$ psql -h 192.168.100.30 -p 5432 -U postgres -c "create database db1" Password for user postgres: CREATE DATABASE [postgres@londiste1 londiste3]$ psql -h 192.168.100.31 -p 5432 -U postgres -c "create database db2" Password for user postgres: CREATE DATABASE [postgres@londiste1 londiste3]$ psql -h 192.168.100.24 -p 5432 -U postgres -c "create database db3" Password for user postgres: CREATE DATABASE [postgres@londiste1 londiste3]$ psql -h 192.168.100.25 -p 5432 -U postgres -c "create database db4" Password for user postgres: CREATE DATABASE [postgres@londiste1 londiste3]$ psql -h 192.168.100.20 -p 5432 -U postgres -c "create database db5" Password for user postgres: CREATE DATABASE 2.2.2、基本配置2.2.2.1 配置ticker[postgres@londiste1 londiste3]$ cat pgqd.ini [pgqd] #database_list = db1,db2,db3,db4,db5 logfile = /opt/skytools/londiste3/log/pgqd.log pidfile = /opt/skytools/londiste3/pid/pgqd.pid 2.2.2.2 数据库连接进程配置[postgres@londiste1 londiste3]$ cat db1.ini [londiste3] job_name = londiste_db1 db = host=192.168.100.30 port=5432 user=postgres password=highgo dbname=db1 queue_name = replika logfile = /opt/skytools/londiste3/log/londiste_db1.log pidfile = /opt/skytools/londiste3/pid/londiste_db1.pid
pgq_autocommit = 1 pgq_lazy_fetch = 0
[postgres@londiste1 londiste3]$ cat db2.ini [londiste3] job_name = londiste_db2 db = host=192.168.100.31 port=5432 user=postgres password=highgo dbname=db2 queue_name = replika logfile = /opt/skytools/londiste3/log/londiste_db2.log pidfile = /opt/skytools/londiste3/pid/londiste_db2.pid
pgq_autocommit = 1 pgq_lazy_fetch = 0
[postgres@londiste1 londiste3]$ cat db3.ini [londiste3] job_name = londiste_db3 db = host=192.168.100.24 port=5432 user=postgres password=highgo dbname=db3 queue_name = replika logfile = /opt/skytools/londiste3/log/londiste_db3.log pidfile = /opt/skytools/londiste3/pid/londiste_db3.pid
pgq_autocommit = 1 pgq_lazy_fetch = 0
[postgres@londiste1 londiste3]$ cat db4.ini [londiste3] job_name = londiste_db4 db = host=192.168.100.25 port=5432 user=postgres password=highgo dbname=db4 queue_name = replika logfile = /opt/skytools/londiste3/log/londiste_db4.log pidfile = /opt/skytools/londiste3/pid/londiste_db4.pid
pgq_autocommit = 1 pgq_lazy_fetch = 0
[postgres@londiste1 londiste3]$ cat db5.ini [londiste3] job_name = londiste_db5 db = host=192.168.100.149 port=5432 user=postgres password=highgo dbname=db5 queue_name = replika logfile = /opt/skytools/londiste3/log/londiste_db5.log pidfile = /opt/skytools/londiste3/pid/londiste_db5.pid
pgq_autocommit = 1 pgq_lazy_fetch = 0
2.2.2.3 初始化节点[postgres@londiste1 londiste3]$ londiste3 db1.ini create-root node1 'host=192.168.100.30 port=5432 user=postgres password=highgo dbname=db1' 2013-09-21 02:01:13,851 17906 INFO plpgsql is installed 2013-09-21 02:01:13,852 17906 INFO Installing pgq 2013-09-21 02:01:13,853 17906 INFO Reading from /opt/skytools/share/skytools3/pgq.sql 2013-09-21 02:01:14,458 17906 INFO pgq.get_batch_cursor is installed 2013-09-21 02:01:14,459 17906 INFO Installing pgq_ext 2013-09-21 02:01:14,460 17906 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql 2013-09-21 02:01:14,790 17906 INFO Installing pgq_node 2013-09-21 02:01:14,791 17906 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql 2013-09-21 02:01:15,170 17906 INFO Installing londiste 2013-09-21 02:01:15,171 17906 INFO Reading from /opt/skytools/share/skytools3/londiste.sql 2013-09-21 02:01:15,685 17906 INFO londiste.global_add_table is installed 2013-09-21 02:01:15,726 17906 INFO Initializing node 2013-09-21 02:01:15,728 17906 INFO Location registered 2013-09-21 02:01:16,126 17906 INFO Node "node1" initialized for queue "replika" with type "root" 2013-09-21 02:01:16,139 17906 INFO Done
[postgres@londiste1 londiste3]$ londiste3 db2.ini create-branch node2 'host=192.168.100.31 port=5432 user=postgres password=highgo dbname=db2' --provider='host=192.168.100.30 port=5432 user=postgres password=highgo dbname=db1' 2013-09-21 02:03:35,666 17932 INFO plpgsql is installed 2013-09-21 02:03:35,667 17932 INFO Installing pgq 2013-09-21 02:03:35,668 17932 INFO Reading from /opt/skytools/share/skytools3/pgq.sql 2013-09-21 02:03:36,319 17932 INFO pgq.get_batch_cursor is installed 2013-09-21 02:03:36,320 17932 INFO Installing pgq_ext 2013-09-21 02:03:36,320 17932 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql 2013-09-21 02:03:36,686 17932 INFO Installing pgq_node 2013-09-21 02:03:36,687 17932 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql 2013-09-21 02:03:37,137 17932 INFO Installing londiste 2013-09-21 02:03:37,137 17932 INFO Reading from /opt/skytools/share/skytools3/londiste.sql 2013-09-21 02:03:37,587 17932 INFO londiste.global_add_table is installed 2013-09-21 02:03:37,693 17932 INFO Initializing node 2013-09-21 02:03:37,737 17932 INFO Location registered 2013-09-21 02:03:37,755 17932 INFO Location registered 2013-09-21 02:03:37,783 17932 INFO Subscriber registered: node2 2013-09-21 02:03:37,802 17932 INFO Location registered 2013-09-21 02:03:37,827 17932 INFO Location registered 2013-09-21 02:03:38,165 17932 INFO Node "node2" initialized for queue "replika" with type "branch" 2013-09-21 02:03:38,179 17932 INFO Done
[postgres@londiste1 londiste3]$ londiste3 db3.ini create-branch node3 'host=192.168.100.24 port=5432 user=postgres password=highgo dbname=db3' --provider='host=192.168.100.30 port=5432 user=postgres password=highgo dbname=db1' 2013-09-21 02:39:37,639 18358 INFO plpgsql is installed 2013-09-21 02:39:37,640 18358 INFO Installing pgq 2013-09-21 02:39:37,640 18358 INFO Reading from /opt/skytools/share/skytools3/pgq.sql 2013-09-21 02:39:38,347 18358 INFO pgq.get_batch_cursor is installed 2013-09-21 02:39:38,348 18358 INFO Installing pgq_ext 2013-09-21 02:39:38,348 18358 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql 2013-09-21 02:39:38,692 18358 INFO Installing pgq_node 2013-09-21 02:39:38,693 18358 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql 2013-09-21 02:39:39,378 18358 INFO Installing londiste 2013-09-21 02:39:39,378 18358 INFO Reading from /opt/skytools/share/skytools3/londiste.sql 2013-09-21 02:39:39,892 18358 INFO londiste.global_add_table is installed 2013-09-21 02:39:39,991 18358 INFO Initializing node 2013-09-21 02:39:40,035 18358 INFO Location registered 2013-09-21 02:39:40,063 18358 INFO Location registered 2013-09-21 02:39:40,093 18358 INFO Subscriber registered: node3 2013-09-21 02:39:40,118 18358 INFO Location registered 2013-09-21 02:39:40,133 18358 INFO Location registered 2013-09-21 02:39:40,158 18358 INFO Location registered 2013-09-21 02:39:40,540 18358 INFO Node "node3" initialized for queue "replika" with type "branch" 2013-09-21 02:39:40,553 18358 INFO Done
[postgres@londiste1 londiste3]$ londiste3 db4.ini create-branch node4 'host=192.168.100.25 port=5432 user=postgres password=highgo dbname=db4' --provider='host=192.168.100.31 port=5432 user=postgres password=highgo dbname=db2' 2013-09-21 02:40:35,441 18372 INFO plpgsql is installed 2013-09-21 02:40:35,442 18372 INFO Installing pgq 2013-09-21 02:40:35,442 18372 INFO Reading from /opt/skytools/share/skytools3/pgq.sql 2013-09-21 02:40:36,208 18372 INFO pgq.get_batch_cursor is installed 2013-09-21 02:40:36,209 18372 INFO Installing pgq_ext 2013-09-21 02:40:36,210 18372 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql 2013-09-21 02:40:36,601 18372 INFO Installing pgq_node 2013-09-21 02:40:36,602 18372 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql 2013-09-21 02:40:37,032 18372 INFO Installing londiste 2013-09-21 02:40:37,033 18372 INFO Reading from /opt/skytools/share/skytools3/londiste.sql 2013-09-21 02:40:37,496 18372 INFO londiste.global_add_table is installed 2013-09-21 02:40:37,639 18372 INFO Initializing node 2013-09-21 02:40:37,704 18372 INFO Location registered 2013-09-21 02:40:37,718 18372 INFO Location registered 2013-09-21 02:40:37,742 18372 INFO Subscriber registered: node4 2013-09-21 02:40:37,762 18372 INFO Location registered 2013-09-21 02:40:37,789 18372 INFO Location registered 2013-09-21 02:40:37,814 18372 INFO Location registered 2013-09-21 02:40:37,839 18372 INFO Location registered 2013-09-21 02:40:38,221 18372 INFO Node "node4" initialized for queue "replika" with type "branch" 2013-09-21 02:40:38,260 18372 INFO Done
[postgres@londiste1 londiste3]$ londiste3 db5.ini create-branch node5 'host=192.168.100.20 port=5432 user=postgres password=highgo dbname=db5' --provider='host=192.168.100.24 port=5432 user=postgres password=highgo dbname=db3' 2013-09-21 05:50:55,190 20501 INFO plpgsql is installed 2013-09-21 05:50:55,191 20501 INFO Installing pgq 2013-09-21 05:50:55,191 20501 INFO Reading from /opt/skytools/share/skytools3/pgq.sql 2013-09-21 05:50:55,886 20501 INFO pgq.get_batch_cursor is installed 2013-09-21 05:50:55,887 20501 INFO Installing pgq_ext 2013-09-21 05:50:55,888 20501 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql 2013-09-21 05:50:56,218 20501 INFO Installing pgq_node 2013-09-21 05:50:56,219 20501 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql 2013-09-21 05:50:56,603 20501 INFO Installing londiste 2013-09-21 05:50:56,603 20501 INFO Reading from /opt/skytools/share/skytools3/londiste.sql 2013-09-21 05:50:57,031 20501 INFO londiste.global_add_table is installed 2013-09-21 05:50:57,111 20501 INFO Initializing node 2013-09-21 05:50:57,164 20501 INFO Location registered 2013-09-21 05:50:57,176 20501 INFO Location registered 2013-09-21 05:50:57,206 20501 INFO Subscriber registered: node5 2013-09-21 05:50:57,228 20501 INFO Location registered 2013-09-21 05:50:57,249 20501 INFO Location registered 2013-09-21 05:50:57,266 20501 INFO Location registered 2013-09-21 05:50:57,282 20501 INFO Location registered 2013-09-21 05:50:57,307 20501 INFO Location registered 2013-09-21 05:50:57,656 20501 INFO Node "node5" initialized for queue "replika" with type "branch" 2013-09-21 05:50:57,677 20501 INFO Done
2.2.2.4 启动tricker[postgres@londiste1 londiste3]$ pgqd -d pgqd.ini 2013-09-21 06:03:56.064 20726 LOG Starting pgqd 3.1.5
拓扑图如下: [postgres@londiste1 londiste3]$ londiste3 db1.ini status Queue: replika Local node: node1
node1 (root) | Tables: 0/0/0 | Lag: 24s, Tick: 17, NOT UPTODATE +--: node2 (branch) | | Tables: 0/0/0 | | Lag: 4h3m29s, Tick: 1, NOT UPTODATE | +--: node4 (branch) | Tables: 0/0/0 | Lag: 4h1m7s, Tick: 1, NOT UPTODATE +--: node3 (branch) | Tables: 0/0/0 | Lag: 4h3m29s, Tick: 1, NOT UPTODATE +--: node5 (branch) Tables: 0/0/0 Lag: 3h25m5s, Tick: 1, NOT UPTODATE
2.2.2.5 启动worker[postgres@londiste1 londiste3]$ londiste3 -d db1.ini worker [postgres@londiste1 londiste3]$ londiste3 -d db2.ini worker [postgres@londiste1 londiste3]$ londiste3 -d db3.ini worker [postgres@londiste1 londiste3]$ londiste3 -d db4.ini worker [postgres@londiste1 londiste3]$ londiste3 -d db5.ini worker [postgres@londiste1 londiste3]$ ps -ef | grep londiste postgres 20766 1 0 06:05 ? 00:00:00 /usr/local/bin/python /opt/skytools/bin/londiste3 -d db1.ini worker postgres 20770 1 0 06:05 ? 00:00:00 /usr/local/bin/python /opt/skytools/bin/londiste3 -d db2.ini worker postgres 20773 1 0 06:05 ? 00:00:00 /usr/local/bin/python /opt/skytools/bin/londiste3 -d db3.ini worker postgres 20789 1 0 06:05 ? 00:00:00 /usr/local/bin/python /opt/skytools/bin/londiste3 -d db4.ini worker postgres 20791 1 0 06:06 ? 00:00:00 /usr/local/bin/python /opt/skytools/bin/londiste3 -d db5.ini worker postgres 20795 18340 0 06:06 pts/1 00:00:00 grep londiste 2.2.2.6 查看各节点状态[postgres@londiste1 londiste3]$ londiste3 db1.ini status Queue: replika Local node: node1
node1 (root) | Tables: 0/0/0 | Lag: 12s, Tick: 27 +--: node2 (branch) | | Tables: 0/0/0 | | Lag: 12s, Tick: 27 | +--: node4 (branch) | Tables: 0/0/0 | Lag: 12s, Tick: 27 +--: node3 (branch) | Tables: 0/0/0 | Lag: 12s, Tick: 27 +--: node5 (branch) Tables: 0/0/0 Lag: 12s, Tick: 27
[postgres@londiste1 londiste3]$ londiste3 db1.ini members Member info on node1@replika: node_name dead node_location --------------- --------------- ---------------------------------------------------------------------- node1 False host=192.168.100.30 port=5432 user=postgres password=highgo dbname=db1 node2 False host=192.168.100.31 port=5432 user=postgres password=highgo dbname=db2 node3 False host=192.168.100.24 port=5432 user=postgres password=highgo dbname=db3 node4 False host=192.168.100.25 port=5432 user=postgres password=highgo dbname=db4 node5 False host=192.168.100.20 port=5432 user=postgres password=highgo dbname=db5 2.2.3、测试2.2.3.1 创建测试表[postgres@londiste1 londiste3]$ psql db1 -c "create table t1 (id serial primary key, data text)" CREATE TABLE 2.2.3.2 root节点加入同步表[postgres@londiste1 londiste3]$ londiste3 db1.ini add-table t1 2013-09-21 06:16:50,763 21003 INFO Table added: public.t1 2.2.3.3 为子节点创建并加入同步表[postgres@londiste1 londiste3]$ londiste3 db2.ini add-table t1 --create 2013-09-21 06:17:33,922 21022 INFO Creating public.t1 2013-09-21 06:17:33,997 21022 INFO Creating t1_pkey 2013-09-21 06:17:34,067 21022 INFO Table added: public.t1
[postgres@londiste1 londiste3]$ londiste3 db3.ini add-table t1 --create 2013-09-21 06:18:27,701 21046 INFO Creating public.t1 2013-09-21 06:18:27,773 21046 INFO Creating t1_pkey 2013-09-21 06:18:27,875 21046 INFO Table added: public.t1
[postgres@londiste1 londiste3]$ londiste3 db4.ini add-table t1 --create 2013-09-21 06:18:31,035 21048 INFO Creating public.t1 2013-09-21 06:18:31,119 21048 INFO Creating t1_pkey 2013-09-21 06:18:31,218 21048 INFO Table added: public.t1
[postgres@londiste1 londiste3]$ londiste3 db5.ini add-table t1 --create 2013-09-21 06:18:38,220 21050 INFO Creating public.t1 2013-09-21 06:18:38,282 21050 INFO Creating t1_pkey 2013-09-21 06:18:38,350 21050 INFO Table added: public.t1 2.2.3.4 插入测试数据[postgres@londiste1 londiste3]$ psql db1 -c "insert into t1(data) values('row1'),('row2'),('row3')" INSERT 0 3 2.2.3.5 检查同步情况[postgres@londiste1 londiste3]$ psql db1 -c "select * from t1" id | data ----+------ 1 | row1 2 | row2 3 | row3 (3 rows)
[postgres@londiste1 londiste3]$ psql -h 192.168.100.31 -d db2 -c "select * from t1" Password: id | data ----+------ 1 | row1 2 | row2 3 | row3 (3 rows)
[postgres@londiste1 londiste3]$ psql -h 192.168.100.24 -d db3 -c "select * from t1" Password: id | data ----+------ 1 | row1 2 | row2 3 | row3 (3 rows)
[postgres@londiste1 londiste3]$ psql -h 192.168.100.25 -d db4 -c "select * from t1" Password: id | data ----+------ 1 | row1 2 | row2 3 | row3 (3 rows)
[postgres@londiste1 londiste3]$ psql -h 192.168.100.20 -d db5 -c "select * from t1" Password: id | data ----+------ 1 | row1 2 | row2 3 | row3 (3 rows)
2.3、合并复制模式 | | | | | | | | localhost | localhost | 9.0.4 for centos32bit | postgres | highgo | 5432 | part1 | root1 | | localhost | 9.0.4 for centos32bit | postgres | highgo | 5432 | part2 | | localhost | localhost | 9.0.4 for centos32bit | postgres | highgo | 5432 | full | full |
2.3.1 创建数据库create database full1; create database part1; create database part2; 2.3.2 基本配置2.3.2.1 配置ticker[postgres@localhost conf]$ cat pgqd.ini [pgqd] database_list = part1,part2,full1 logfile = /opt/skytools/londiste/log/pgqd.log pidfile = /opt/skytools/londiste/pid/pgqd.pid 2.3.2.2 数据库连接进程配置[postgres@localhost conf]$ cat part1.ini [londiste3] job_name = l3_part1 db = dbname=part1 queue_name = l3_part1_q logfile = /opt/skytools/londiste/log/%(job_name)s.log pidfile = /opt/skytools/londiste/pid/%(job_name)s.pid
[postgres@localhost conf]$ cat part2.ini [londiste3] job_name = l3_part2 db = dbname=part2 queue_name = l3_part2_q logfile = /opt/skytools/londiste/log/%(job_name)s.log pidfile = /opt/skytools/londiste/pid/%(job_name)s.pid
[postgres@localhost conf]$ cat part1_full1.ini [londiste3] job_name = l3_part1_full1 db = dbname=full1 queue_name = l3_part1_q logfile = /opt/skytools/londiste/log/%(job_name)s.log pidfile = /opt/skytools/londiste/pid/%(job_name)s.pid
[postgres@localhost conf]$ cat part2_full1.ini [londiste3] job_name = l3_part2_full1 db = dbname=full1 queue_name = l3_part2_q logfile = /opt/skytools/londiste/log/%(job_name)s.log pidfile = /opt/skytools/londiste/pid/%(job_name)s.pid
|