Skytools安装配置管理（二）

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	db3	slave2
londiste4	192.168.100.25	9.0.13 for ubuntu64	db4	slave3
londiste5	192.168.100.20	9.2.4 for suse32	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"

[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	9.0.4 for centos32bit	part1	root1
localhost	9.0.4 for centos32bit	part2	root2
full	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