一、安装篇

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、安装skytools

1.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、两节点复制模式

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

sql

2013-09-20 04:03:34,121 31459 INFO Installing pgq_node

sql

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

----------+-------------------------+----------+----------+------------+-------------

(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

sql

2013-09-20 04:09:23,235 18763 INFO Installing pgq_node

sql

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+

(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

{测时可以看到数据已经同步，不过该方式的同步速度比较慢，不会将主库端的变更立刻在备库端体现，需要等待一段时间}

原文链接：/postgresql/195678.html

Skytools安装配置管理（一）