2.2、级联复制模式

HOSTNAME

IP

PG_VERSION

USER

PASSWORD

PORT

DB_NAME

ROLE

londiste1

192.168.100.30

9.3rc1 for centos64

postgres

highgo

5432

db1

master

londiste2

192.168.100.31

9.0.13 for fedora32

postgres

highgo

5432

db2

slave1

londiste3

192.168.100.24

9.0.13 for centos64

postgres

highgo

5432

db3

slave2

londiste4

192.168.100.25

9.0.13 for ubuntu64

postgres

highgo

5432

db4

slave3

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、合并复制模式

HOSTNAME

IP

PG_VERSION

USER

PASSWORD

PORT

DB_NAME

ROLE

localhost

localhost

9.0.4 for centos32bit

postgres

highgo

5432

part1

root1

localhost

localhost

9.0.4 for centos32bit

postgres

highgo

5432

part2

root2

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