Skytools安装配置管理(一)
一、安装篇
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 1.3、安装skytools1.3.1 安装psycopg2获取地址:http://initd.org/psycopg/ [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 [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 [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 [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 配置环境变量 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 export PATH=/opt/pg90/bin:/opt/skytools/bin:$PATH:$HOME/bin export PGDATA=/opt/pg90/data 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、两节点复制模式
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:34,121 31459 INFO Installing pgq_node 2013-09-20 04:03:35,333 31459 INFO Node "node1" initialized for queue "replika" with type "root" [postgres@londiste1 londiste3]$ psql db_p psql (9.3rc1) Type "help" for help. db_p=# dn Listof 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+ Listof 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 job_name = l3_db_s db = host=192.168.100.31 port=5490 user=postgres password=highgo dbname=db_s 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:23,235 18763 INFO Installing pgq_node 2013-09-20 04:09:24,123 18763 INFO Initializing node LOG: unexpected EOF on client connection [postgres@londiste2 londiste3]$ psql db_s psql (9.0.13) db_s=# dn --------------------+---------- information_schema | postgres pg_catalog | postgres pg_toast | postgres pg_toast_temp_1 | postgres (9 rows) db_s=# set search_path to londiste,SimSun;font-weight:bold;vertical-align:baseline;">db_s=# d+ londiste | applied_execute | table | postgres | 0 bytes | londiste | pending_fkeys | table | postgres | 0 bytes | londiste | seq_info | table | postgres | 0 bytes | londiste | table_info | table | postgres | 0 bytes | pgq | consumer | table | postgres | 0 bytes | pgq | event_template | table | postgres | 0 bytes | pgq | queue | table | postgres | 0 bytes | pgq | retry_queue | table | postgres | 0 bytes | pgq | subscription | table | postgres | 0 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,SimSun;font-weight:bold;vertical-align:baseline;">[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、测试 db_p=# create table t1 ( db_p(# id int primary key, db_p(# name varchar(20) db_p(# ); CREATE TABLE db_p=# d 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_replikaAFTER 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') {此时,同步表会自动添加两个触发器} db_s=# create table t1 ( db_s(# id int primary key,SimSun;font-weight:bold;vertical-align:baseline;">db_s(# name varchar(20) db_s(# ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_s_pkey" for table "t1_s" db_s=# d [postgres@londiste2 londiste3]$ londiste3 db_s.ini add-table public.t1 db_s=# d t1 _londiste_replikaAFTER 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',SimSun;font-weight:bold;vertical-align:baseline;">[postgres@londiste2 londiste3]$ londiste3 db_s.ini tables public.t1 None {开始时为None,同步完成后变为ok} db_p=# insert into t1 values (1,'lsk'); INSERT 0 1 db_s=# select * from t1 ; id | name ----+------ 1| lsk {测时可以看到数据已经同步,不过该方式的同步速度比较慢,不会将主库端的变更立刻在备库端体现,需要等待一段时间} (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
- 关于如何去掉fragment_main.xml文件
- close() was never explicitly called on databaseandroid.
- 关于SQL中CTE(公用表表达式)(Common Table Expression)
- objective-c – SpriteKit自定义SKAction =弹跳?
- 如何在persistence.xml中“参数化”JPA数据库实例?
- 从任何正则表达式生成上下文无关语法的算法
- c# – Boxed Value类型比较
- 打开VB6项目时,我可以对所有打开的窗口做些什么吗?
- [Swift]LeetCode309. 最佳买卖股票时机含冷冻期 | Best Tim
- c# – XAML中的列表绑定列表?