一、安装篇
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
[root@londiste1 ~]# cat /etc/issue
CentOS release 5.9 (Final)
Kernel \r on an \m
[root@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
[root@londiste2 ~]# cat /etc/issue
Fedora release 17 (Beefy Miracle)
Kernel \r on an \m (\l)
[root@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、安装skytools
1.3.1 安装psycopg2
获取地址：http://initd.org/psycopg/
【londiste1】
[root@londiste1 ~]# tar -zxvf psycopg2-2.5.1.tar.gz
[root@londiste1 psycopg2-2.5.1]# python setup.py build_ext --pg-config /opt/pg93/bin/pg_config build
[root@londiste1 psycopg2-2.5.1]# python setup.py build_ext --pg-config /opt/pg93/bin/pg_config install
【londiste2】
[root@londiste2 ~]# tar -zxvf psycopg2-2.5.1.tar.gz
[root@londiste2 psycopg2-2.5.1]# python setup.py build_ext --pg-config /opt/pg90/bin/pg_config build
[root@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】
[root@londiste1 ~]# tar -zxvf skytools-3.1.5.tar.gz
[root@londiste1 ~]# cd skytools-3.1.5
[root@londiste1 skytools-3.1.5]# ./configure --prefix=/opt/skytools --with-pgconfig=/opt/pg93/bin/pg_config
[root@londiste1 skytools-3.1.5]# make
[root@londiste1 skytools-3.1.5]# make install
[root@londiste1 skytools-3.1.5]# ls /opt/skytools/
bin lib share
[root@londiste1 skytools-3.1.5]# mkdir -p /opt/skytools/londiste3/pid
[root@londiste1 skytools-3.1.5]# mkdir -p /opt/skytools/londiste3/log
[root@londiste1 skytools-3.1.5]# chown -R postgres:postgres /opt/skytools/londisite3
【londiste2】
[root@londiste2 ~]# tar -zxvf skytools-3.1.5.tar.gz
[root@londiste2 ~]# cd skytools-3.1.5
[root@londiste2 skytools-3.1.5]# ./configure --prefix=/opt/skytools --with-pgconfig=/opt/pg90/bin/pg_config
[root@londiste2 skytools-3.1.5]# make
[root@londiste2 skytools-3.1.5]# make install
[root@londiste2 skytools-3.1.5]# ls /opt/skytools/
[root@londiste2 skytools-3.1.5]# mkdir -p /opt/skytools/londiste3/pid
[root@londiste2 skytools-3.1.5]# mkdir -p /opt/skytools/londiste3/log
[root@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、两节点复制模式
HOSTNAME | IP | PG_VERSION | USER | PORT | DB_NAME | ROLE |
londiste1 | 192.168.100.30 | 9.3rc1 for linux | postgres | 5493 | db_p | master |
londiste2 | 192.168.100.31 | 9.0.13 for linux | postgres | 5490 | db_s | slave |
2.1.1、配置master
2.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、配置slave
2.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)
{测时可以看到数据已经同步，不过该方式的同步速度比较慢，不会将主库端的变更立刻在备库端体现，需要等待一段时间}
转载于:https://blog.51cto.com/lianshunke/1434527
1931
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
