Skytools安装配置管理（二）

2.2、级联复制模式

@H_502_8@HOSTNAME	IP	PG_VERSION	USER	PASSWORD	PORT	DB_NAME	ROLE
@H_502_8@londiste1	@H_502_8@192.168.100.30	@H_502_8@9.3rc1 for centos64	@H_502_8@postgres	@H_502_8@highgo	@H_502_8@5432	@H_502_8@db1	@H_502_8@master
londiste2	@H_502_8@192.168.100.31	@H_502_8@9.0.13 for fedora32	@H_502_8@postgres	@H_502_8@highgo	@H_502_8@5432	@H_502_8@db2	slave1
@H_502_8@londiste3	@H_502_8@192.168.100.24	@H_502_8@9.0.13 for centos64	@H_502_8@db3	@H_502_8@slave2
londiste4	@H_502_8@192.168.100.25	@H_502_8@9.0.13 for ubuntu64	@H_502_8@db4	slave3
@H_502_8@londiste5	@H_502_8@192.168.100.20	@H_502_8@9.2.4 for suse32	@H_502_8@db5	@H_502_8@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"

[postgres@londiste1 londiste3]$ psql -h 192.168.100.24 -p 5432 -U postgres -c "create database db3"

[postgres@londiste1 londiste3]$ psql -h 192.168.100.25 -p 5432 -U postgres -c "create database db4"

[postgres@londiste1 londiste3]$ psql -h 192.168.100.20 -p 5432 -U postgres -c "create database db5"

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

job_name = londiste_db2

db = host=192.168.100.31 port=5432 user=postgres password=highgo dbname=db2

logfile = /opt/skytools/londiste3/log/londiste_db2.log

pidfile = /opt/skytools/londiste3/pid/londiste_db2.pid

[postgres@londiste1 londiste3]$ cat db3.ini

job_name = londiste_db3

db = host=192.168.100.24 port=5432 user=postgres password=highgo dbname=db3

logfile = /opt/skytools/londiste3/log/londiste_db3.log

pidfile = /opt/skytools/londiste3/pid/londiste_db3.pid

[postgres@londiste1 londiste3]$ cat db4.ini

job_name = londiste_db4

db = host=192.168.100.25 port=5432 user=postgres password=highgo dbname=db4

logfile = /opt/skytools/londiste3/log/londiste_db4.log

pidfile = /opt/skytools/londiste3/pid/londiste_db4.pid

[postgres@londiste1 londiste3]$ cat db5.ini

job_name = londiste_db5

db = host=192.168.100.149 port=5432 user=postgres password=highgo dbname=db5

logfile = /opt/skytools/londiste3/log/londiste_db5.log

pidfile = /opt/skytools/londiste3/pid/londiste_db5.pid

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

sql

2013-09-21 02:01:14,458 17906 INFO pgq.get_batch_cursor is installed

sql

2013-09-21 02:01:15,170 17906 INFO Installing londiste

sql

2013-09-21 02:01:16,126 17906 INFO Node "node1" initialized for queue "replika" with type "root"

[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

sql

2013-09-21 02:03:36,319 17932 INFO pgq.get_batch_cursor is installed

sql

2013-09-21 02:03:37,137 17932 INFO Installing londiste

sql

2013-09-21 02:03:38,165 17932 INFO Node "node2" initialized for queue "replika" with type "branch"

[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

sql

2013-09-21 02:39:38,347 18358 INFO pgq.get_batch_cursor is installed

sql

2013-09-21 02:39:39,378 18358 INFO Installing londiste

sql

2013-09-21 02:39:40,035 18358 INFO Location registered

[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

sql

2013-09-21 02:40:36,208 18372 INFO pgq.get_batch_cursor is installed

sql

2013-09-21 02:40:37,032 18372 INFO Installing londiste

sql

2013-09-21 02:40:38,221 18372 INFO Node "node4" initialized for queue "replika" with type "branch"

[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

sql

2013-09-21 05:50:56,218 20501 INFO Installing pgq_node

sql

2013-09-21 05:50:57,031 20501 INFO londiste.global_add_table is installed

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,SimSun;vertical-align:baseline;"> | +--:node4 (branch)

| Lag:4h1m7s,SimSun;vertical-align:baseline;"> +--:node3 (branch)

| Lag:4h3m29s,SimSun;vertical-align:baseline;"> +--:node5 (branch)

Tables:0/0/0

Lag:3h25m5s,SimSun;font-size:19px;vertical-align:baseline;">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 查看各节点状态

| Lag:12s,Tick: 27

| | Lag:12s,SimSun;vertical-align:baseline;"> Lag:12s,SimSun;font-weight:bold;vertical-align:baseline;">[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: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

[postgres@londiste1 londiste3]$ londiste3 db4.ini add-table t1 --create

2013-09-21 06:18:31,035 21048 INFO Creating public.t1

[postgres@londiste1 londiste3]$ londiste3 db5.ini add-table t1 --create

2013-09-21 06:18:38,220 21050 INFO Creating 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:

[postgres@londiste1 londiste3]$ psql -h 192.168.100.24 -d db3 -c "select * from t1"

[postgres@londiste1 londiste3]$ psql -h 192.168.100.25 -d db4 -c "select * from t1"

[postgres@londiste1 londiste3]$ psql -h 192.168.100.20 -d db5 -c "select * from t1"

2.3、合并复制模式

localhost	@H_502_8@9.0.4 for centos32bit	@H_502_8@part1	@H_502_8@root1
localhost	@H_502_8@9.0.4 for centos32bit	@H_502_8@part2	root2
@H_502_8@full	@H_502_8@full

2.3.1 创建数据库

create database full1;

create database part1;

create database part2;

2.3.2 基本配置

2.3.2.1 配置ticker

[postgres@localhostconf]$ cat pgqd.ini

database_list = part1,part2,full1

logfile = /opt/skytools/londiste/log/pgqd.log

pidfile = /opt/skytools/londiste/pid/pgqd.pid

2.3.2.2 数据库连接进程配置

[postgres@localhostconf]$ cat part1.ini

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@localhostconf]$ cat part2.ini

job_name = l3_part2

db = dbname=part2

queue_name = l3_part2_q

[postgres@localhostconf]$ cat part1_full1.ini

job_name = l3_part1_full1

db = dbname=full1

[postgres@localhostconf]$ cat part2_full1.ini

job_name = l3_part2_full1