一、安装篇
1.1、skytools简介Skytools包含三个组件：pgq、londiste、walmgr。
Pgq提供SQL API，由异步处理机制去灵活调用。用于解决实时事务的异步批处理问题。 Pgq由producer、ticker、consumer组成。Producer将events推送到queue中，ticker负责对批量queue制定相应处理规则，consumer从queue中获取events。 Londiste是基于pgq的事件传输功能的一个数据库复制工具，由python语言编写。 Walmgr是一个包含了WAL归档、基础备份及数据库运行时备份恢复功能的脚本。由python语言编写。 1.2、实验环境1.2.1 OS[iyunv@londiste1 ~]# cat /etc/issue CentOS release 5.9 (Final) Kernel \r on an \m
[iyunv@londiste1 ~]# uname -a Linux londiste1 2.6.32-042stab076.8 #1 SMP Tue May 14 20:38:14 MSK 2013 x86_64 x86_64 x86_64 GNU/Linux
[iyunv@londiste2 ~]# cat /etc/issue Fedora release 17 (Beefy Miracle) Kernel \r on an \m (\l)
[iyunv@londiste2 ~]# uname -a Linux londiste2 2.6.32-042stab076.8 #1 SMP Tue May 14 20:38:14 MSK 2013 i686 i686 i386 GNU/Linux 1.2.2 Other【londiste1】 Python 2.5.6 (编译安装，系统自带版本为2.4.3) psycopg2-2.5.1(要求python版本 >= 2.5) skytools-3.1.5
【londiste2】 Python 2.7.3 psycopg2-2.5.1 skytools-3.1.5
1.3、安装skytools1.3.1 安装psycopg2获取地址：http://initd.org/psycopg/
【londiste1】 [iyunv@londiste1 ~]# tar -zxvf psycopg2-2.5.1.tar.gz [iyunv@londiste1 psycopg2-2.5.1]# python setup.py build_ext --pg-config /opt/pg93/bin/pg_config build [iyunv@londiste1 psycopg2-2.5.1]# python setup.py build_ext --pg-config /opt/pg93/bin/pg_config install
【londiste2】 [iyunv@londiste2 ~]# tar -zxvf psycopg2-2.5.1.tar.gz [iyunv@londiste2 psycopg2-2.5.1]# python setup.py build_ext --pg-config /opt/pg90/bin/pg_config build [iyunv@londiste2 psycopg2-2.5.1]# python setup.py build_ext --pg-config /opt/pg90/bin/pg_config install {注意：--pg-config路径的指定，根据具体情况而定}
1.3.2 安装skytools获取地址：http://pgfoundry.org/frs/?group_id=1000206
【londiste1】 [iyunv@londiste1 ~]# tar -zxvf skytools-3.1.5.tar.gz [iyunv@londiste1 ~]# cd skytools-3.1.5 [iyunv@londiste1 skytools-3.1.5]# ./configure --prefix=/opt/skytools --with-pgconfig=/opt/pg93/bin/pg_config [iyunv@londiste1 skytools-3.1.5]# make [iyunv@londiste1 skytools-3.1.5]# make install [iyunv@londiste1 skytools-3.1.5]# ls /opt/skytools/ bin lib share [iyunv@londiste1 skytools-3.1.5]# mkdir -p /opt/skytools/londiste3/pid [iyunv@londiste1 skytools-3.1.5]# mkdir -p /opt/skytools/londiste3/log [iyunv@londiste1 skytools-3.1.5]# chown -R postgres:postgres /opt/skytools/londisite3
【londiste2】 [iyunv@londiste2 ~]# tar -zxvf skytools-3.1.5.tar.gz [iyunv@londiste2 ~]# cd skytools-3.1.5 [iyunv@londiste2 skytools-3.1.5]# ./configure --prefix=/opt/skytools --with-pgconfig=/opt/pg90/bin/pg_config [iyunv@londiste2 skytools-3.1.5]# make [iyunv@londiste2 skytools-3.1.5]# make install [iyunv@londiste2 skytools-3.1.5]# ls /opt/skytools/ [iyunv@londiste2 skytools-3.1.5]# mkdir -p /opt/skytools/londiste3/pid [iyunv@londiste2 skytools-3.1.5]# mkdir -p /opt/skytools/londiste3/log [iyunv@londiste2 skytools-3.1.5]# chown postgres:postgres /opt/skytools/londisite3
1.3.3 配置环境变量【londiste1】 export PATH=/opt/pg93/bin:/opt/skytools/bin:$PATH:$HOME/bin export PGDATA=/opt/pg93/data export PGUSER=postgres export PGPORT=5493 export LD_LIBRARY_PATH=/opt/pg93/lib:$LD_LIBRARY_PATH export PYTHONPATH=/opt/skytools/lib/python2.5/site-packages:$PYTHONPATH
【londiste2】 export PATH=/opt/pg90/bin:/opt/skytools/bin:$PATH:$HOME/bin export PGDATA=/opt/pg90/data export PGUSER=postgres export PGPORT=5490 export LD_LIBRARY_PATH=/opt/pg90/lib:$LD_LIBRARY_PATH export PYTHONPATH=/opt/skytools/lib/python2.7/site-packages:$PYTHONPATH
二、配置篇【首先在各个节点上配置允许连接（pg_hba.conf）并启动数据库】 2.1、两节点复制模式 | | | | | | | londiste1 | 192.168.100.30 | 9.3rc1 for linux | postgres | 5493 | db_p | master | | | | | | | |
2.1.1、配置master2.1.1.1 创建provider进程配置文件[postgres@londiste1 londiste3]$ cat db_p.ini [londiste3] job_name = l3_db_p db = host=192.168.100.30 port=5493 user=postgres password=highgo dbname=db_p queue_name = replika logfile = /opt/skytools/londiste3/log/l3_db_p.log pidfile = /opt/skytools/londiste3/pid/l3_db_p.pid 2.1.1.2 初始化londiste root节点[postgres@londiste1 londiste3]$ londiste3 db_p.ini create-root node1 'host=192.168.100.30 port=5493 user=postgres password=highgo dbname=db_p' 2013-09-20 04:03:33,298 31459 INFO plpgsql is installed 2013-09-20 04:03:33,299 31459 INFO Installing pgq 2013-09-20 04:03:33,299 31459 INFO Reading from /opt/skytools/share/skytools3/pgq.sql 2013-09-20 04:03:33,826 31459 INFO pgq.get_batch_cursor is installed 2013-09-20 04:03:33,827 31459 INFO Installing pgq_ext 2013-09-20 04:03:33,827 31459 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql 2013-09-20 04:03:34,121 31459 INFO Installing pgq_node 2013-09-20 04:03:34,121 31459 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql 2013-09-20 04:03:34,511 31459 INFO Installing londiste 2013-09-20 04:03:34,511 31459 INFO Reading from /opt/skytools/share/skytools3/londiste.sql 2013-09-20 04:03:34,857 31459 INFO londiste.global_add_table is installed 2013-09-20 04:03:34,956 31459 INFO Initializing node 2013-09-20 04:03:34,958 31459 INFO Location registered 2013-09-20 04:03:35,333 31459 INFO Node "node1" initialized for queue "replika" with type "root" 2013-09-20 04:03:35,349 31459 INFO Done
[postgres@londiste1 londiste3]$ psql db_p psql (9.3rc1) Type "help" for help.
db_p=# \dn List of schemas Name | Owner ----------+---------- londiste | postgres pgq | postgres pgq_ext | postgres pgq_node | postgres public | postgres (5 rows)
db_p=# set search_path to londiste,pgq,pgq_ext,pgq_node; SET db_p=# \d+ List of relations Schema | Name | Type | Owner | Size | Description ----------+-------------------------+----------+----------+------------+------------- londiste | applied_execute | table | postgres | 8192 bytes | londiste | pending_fkeys | table | postgres | 8192 bytes | londiste | seq_info | table | postgres | 8192 bytes | londiste | seq_info_nr_seq | sequence | postgres | 8192 bytes | londiste | table_info | table | postgres | 8192 bytes | londiste | table_info_nr_seq | sequence | postgres | 8192 bytes | pgq | batch_id_seq | sequence | postgres | 8192 bytes | pgq | consumer | table | postgres | 16 kB | pgq | consumer_co_id_seq | sequence | postgres | 8192 bytes | pgq | event_1 | table | postgres | 8192 bytes | pgq | event_1_0 | table | postgres | 8192 bytes | pgq | event_1_1 | table | postgres | 8192 bytes | pgq | event_1_2 | table | postgres | 8192 bytes | pgq | event_1_id_seq | sequence | postgres | 8192 bytes | pgq | event_1_tick_seq | sequence | postgres | 8192 bytes | pgq | event_template | table | postgres | 8192 bytes | pgq | queue | table | postgres | 16 kB | pgq | queue_queue_id_seq | sequence | postgres | 8192 bytes | pgq | retry_queue | table | postgres | 8192 bytes | pgq | subscription | table | postgres | 8192 bytes | pgq | subscription_sub_id_seq | sequence | postgres | 8192 bytes | pgq | tick | table | postgres | 16 kB | pgq_ext | completed_batch | table | postgres | 8192 bytes | pgq_ext | completed_event | table | postgres | 8192 bytes | pgq_ext | completed_tick | table | postgres | 8192 bytes | pgq_ext | partial_batch | table | postgres | 8192 bytes | pgq_node | local_state | table | postgres | 16 kB | pgq_node | node_info | table | postgres | 16 kB | pgq_node | node_location | table | postgres | 16 kB | pgq_node | subscriber_info | table | postgres | 8192 bytes | (30 rows) 2.1.1.3 启动worker[postgres@londiste1 londiste3]$ londiste3 -d db_p.ini worker [postgres@londiste1 londiste3]$ ps -ef | grep lond postgres 31494 1 0 04:06 ? 00:00:00 /usr/local/bin/python /usr/bin/londiste3 -d db_p.ini worker postgres 31498 30695 0 04:06 pts/0 00:00:00 grep lond 2.1.1.4 配置pgq ticker[postgres@londiste1 londiste3]$ cat pgqd.ini [pgqd]
logfile = /opt/skytools/londiste3/log/pgqd.log pidfile = /opt/skytools/londiste3/pid/pgqd.pid 2.1.1.5 启动ticker daemon[postgres@londiste1 londiste3]$ pgqd -d pgqd.ini 2013-09-20 05:45:25.481 32500 LOG Starting pgqd 3.1.5
2.1.2、配置slave2.1.2.1 创建provider进程配置文件[postgres@londiste2 londiste3]$ cat db_s.ini [londiste3] job_name = l3_db_s db = host=192.168.100.31 port=5490 user=postgres password=highgo dbname=db_s queue_name = replika logfile = /opt/skytools/londiste3/log/l3_db_s.log pidfile = /opt/skytools/londiste3/pid/l3_db_s.pid {注：queue_name必须一致} 2.1.2.2 初始化londiste root节点[postgres@londiste2 londiste3]$ londiste3 db_s.ini create-leaf node2 'host=192.168.100.31 port=5490 user=postgres password=highgo dbname=db_s' --provider='host=192.168.100.30 port=5493 user=postgres password=highgo dbname=db_p' 2013-09-20 04:09:22,336 18763 INFO plpgsql is installed 2013-09-20 04:09:22,337 18763 INFO Installing pgq 2013-09-20 04:09:22,337 18763 INFO Reading from /usr/share/skytools3/pgq.sql 2013-09-20 04:09:22,932 18763 INFO pgq.get_batch_cursor is installed 2013-09-20 04:09:22,933 18763 INFO Installing pgq_ext 2013-09-20 04:09:22,934 18763 INFO Reading from /usr/share/skytools3/pgq_ext.sql 2013-09-20 04:09:23,235 18763 INFO Installing pgq_node 2013-09-20 04:09:23,236 18763 INFO Reading from /usr/share/skytools3/pgq_node.sql 2013-09-20 04:09:23,615 18763 INFO Installing londiste 2013-09-20 04:09:23,615 18763 INFO Reading from /usr/share/skytools3/londiste.sql 2013-09-20 04:09:23,977 18763 INFO londiste.global_add_table is installed 2013-09-20 04:09:24,123 18763 INFO Initializing node 2013-09-20 04:09:24,169 18763 INFO Location registered 2013-09-20 04:09:24,184 18763 INFO Location registered 2013-09-20 04:09:24,225 18763 INFO Subscriber registered: node2 2013-09-20 04:09:24,259 18763 INFO Location registered 2013-09-20 04:09:24,274 18763 INFO Location registered 2013-09-20 04:09:24,293 18763 INFO Node "node2" initialized for queue "replika" with type "leaf" 2013-09-20 04:09:24,309 18763 INFO Done LOG: unexpected EOF on client connection
[postgres@londiste2 londiste3]$ psql db_s psql (9.0.13) Type "help" for help.
db_s=# \dn List of schemas Name | Owner --------------------+---------- information_schema | postgres londiste | postgres pg_catalog | postgres pg_toast | postgres pg_toast_temp_1 | postgres pgq | postgres pgq_ext | postgres pgq_node | postgres public | postgres (9 rows)
db_s=# set search_path to londiste,pgq,pgq_ext,pgq_node; SET db_s=# \d+ List of relations Schema | Name | Type | Owner | Size | Description ----------+-------------------------+----------+----------+------------+------------- londiste | applied_execute | table | postgres | 0 bytes | londiste | pending_fkeys | table | postgres | 0 bytes | londiste | seq_info | table | postgres | 0 bytes | londiste | seq_info_nr_seq | sequence | postgres | 8192 bytes | londiste | table_info | table | postgres | 0 bytes | londiste | table_info_nr_seq | sequence | postgres | 8192 bytes | pgq | batch_id_seq | sequence | postgres | 8192 bytes | pgq | consumer | table | postgres | 0 bytes | pgq | consumer_co_id_seq | sequence | postgres | 8192 bytes | pgq | event_template | table | postgres | 0 bytes | pgq | queue | table | postgres | 0 bytes | pgq | queue_queue_id_seq | sequence | postgres | 8192 bytes | pgq | retry_queue | table | postgres | 0 bytes | pgq | subscription | table | postgres | 0 bytes | pgq | subscription_sub_id_seq | sequence | postgres | 8192 bytes | pgq | tick | table | postgres | 0 bytes | pgq_ext | completed_batch | table | postgres | 0 bytes | pgq_ext | completed_event | table | postgres | 0 bytes | pgq_ext | completed_tick | table | postgres | 0 bytes | pgq_ext | partial_batch | table | postgres | 0 bytes | pgq_node | local_state | table | postgres | 8192 bytes | pgq_node | node_info | table | postgres | 8192 bytes | pgq_node | node_location | table | postgres | 8192 bytes | pgq_node | subscriber_info | table | postgres | 0 bytes | (24 rows) 2.1.2.3 启动worker[postgres@londiste2 londiste3]$ londiste3 -d db_s.ini worker [postgres@londiste2 londiste3]$ ps -ef | grep lond postgres 18812 1 0 04:13 ? 00:00:00 /usr/bin/python /bin/londiste3 -d db_s.ini worker postgres 18815 16517 0 04:13 pts/0 00:00:00 grep --color=auto lond
[postgres@londiste2 londiste3]$ londiste3 db_s.ini status Queue: replika Local node: node2
node1 (root) | Tables: 0/0/0 | Lag: 12m46s, Tick: 1 +--: node2 (leaf) Tables: 0/0/0 Lag: 12m46s, Tick: 1
[postgres@londiste2 londiste3]$ londiste3 db_s.ini members Member info on node2@replika: node_name dead node_location --------------- --------------- ----------------------------------------------------------------------- node1 False host=192.168.100.30 port=5493 user=postgres password=highgo dbname=db_p node2 False host=192.168.100.31 port=5490 user=postgres password=highgo dbname=db_s
2.1.3、测试【londiste1】 db_p=# create table t1 ( db_p(# id int primary key, db_p(# name varchar(20) db_p(# ); CREATE TABLE db_p=# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | postgres (1 row)
[postgres@londiste1 londiste3]$ londiste3 db_p.ini add-table public.t1 2013-09-20 04:44:06,562 31866 INFO Table added: public.t1
[postgres@londiste1 londiste3]$ londiste3 db_p.ini tables Tables on node table_name merge_state table_attrs --------------- --------------- --------------- public.t1 ok
db_p=# \d t1 Table "public.t1" Column | Type | Modifiers --------+-----------------------+----------- id | integer | not null name | character varying(20) | Indexes: "t1_pkey" PRIMARY KEY, btree (id) Triggers: _londiste_replika AFTER INSERT OR DELETE OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE pgq.logutriga('replika') _londiste_replika_truncate AFTER TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE PROCEDURE pgq.sqltriga('replika') {此时，同步表会自动添加两个触发器}
【londiste2】 db_s=# create table t1 ( db_s(# id int primary key, db_s(# name varchar(20) db_s(# ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_s_pkey" for table "t1_s" CREATE TABLE db_s=# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | postgres (1 row)
[postgres@londiste2 londiste3]$ londiste3 db_s.ini add-table public.t1 2013-09-20 04:44:06,562 31866 INFO Table added: public.t1
db_s=# \d t1 Table "public.t1" Column | Type | Modifiers --------+-----------------------+----------- id | integer | not null name | character varying(20) | Indexes: "t1_pkey" PRIMARY KEY, btree (id) Triggers: _londiste_replika AFTER INSERT OR DELETE OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE pgq.logutriga('replika', 'deny') _londiste_replika_truncate AFTER TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE PROCEDURE pgq.sqltriga('replika', 'deny')
[postgres@londiste2 londiste3]$ londiste3 db_s.ini tables Tables on node table_name merge_state table_attrs --------------- --------------- --------------- public.t1 None {开始时为None，同步完成后变为ok}
【londiste1】 db_p=# insert into t1 values (1,'lsk'); INSERT 0 1
【londiste2】 db_s=# select * from t1 ; id | name ----+------ 1 | lsk (1 row) {测时可以看到数据已经同步，不过该方式的同步速度比较慢，不会将主库端的变更立刻在备库端体现，需要等待一段时间}
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2014-7-4 21:22:56
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡