Skytools安装配置管理

最新推荐文章于 2024-07-21 02:07:35 发布

weixin_33967071

最新推荐文章于 2024-07-21 02:07:35 发布

阅读量373

点赞数

文章标签：运维数据库 python

原文链接：https://my.oschina.net/lianshunke/blog/201558

版权

2019独角兽企业重金招聘Python工程师标准>>>

一、安装篇

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

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

(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

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

(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)

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

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

Password for user postgres:

CREATE DATABASE

[postgres@londiste1 londiste3]$ psql -h 192.168.100.24 -p 5432 -U postgres -c "create database db3"

Password for user postgres:

CREATE DATABASE

[postgres@londiste1 londiste3]$ psql -h 192.168.100.25 -p 5432 -U postgres -c "create database db4"

Password for user postgres:

CREATE DATABASE

[postgres@londiste1 londiste3]$ psql -h 192.168.100.20 -p 5432 -U postgres -c "create database db5"

Password for user postgres:

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

[londiste3]

job_name = londiste_db2

db = host=192.168.100.31 port=5432 user=postgres password=highgo dbname=db2

queue_name = replika

logfile = /opt/skytools/londiste3/log/londiste_db2.log

pidfile = /opt/skytools/londiste3/pid/londiste_db2.pid

pgq_autocommit = 1

pgq_lazy_fetch = 0

[postgres@londiste1 londiste3]$ cat db3.ini

[londiste3]

job_name = londiste_db3

db = host=192.168.100.24 port=5432 user=postgres password=highgo dbname=db3

queue_name = replika

logfile = /opt/skytools/londiste3/log/londiste_db3.log

pidfile = /opt/skytools/londiste3/pid/londiste_db3.pid

pgq_autocommit = 1

pgq_lazy_fetch = 0

[postgres@londiste1 londiste3]$ cat db4.ini

[londiste3]

job_name = londiste_db4

db = host=192.168.100.25 port=5432 user=postgres password=highgo dbname=db4

queue_name = replika

logfile = /opt/skytools/londiste3/log/londiste_db4.log

pidfile = /opt/skytools/londiste3/pid/londiste_db4.pid

pgq_autocommit = 1

pgq_lazy_fetch = 0

[postgres@londiste1 londiste3]$ cat db5.ini

[londiste3]

job_name = londiste_db5

db = host=192.168.100.149 port=5432 user=postgres password=highgo dbname=db5

queue_name = replika

logfile = /opt/skytools/londiste3/log/londiste_db5.log

pidfile = /opt/skytools/londiste3/pid/londiste_db5.pid

pgq_autocommit = 1

pgq_lazy_fetch = 0

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

2013-09-21 02:01:13,852 17906 INFO Installing pgq

2013-09-21 02:01:13,853 17906 INFO Reading from /opt/skytools/share/skytools3/pgq.sql

2013-09-21 02:01:14,458 17906 INFO pgq.get_batch_cursor is installed

2013-09-21 02:01:14,459 17906 INFO Installing pgq_ext

2013-09-21 02:01:14,460 17906 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql

2013-09-21 02:01:14,790 17906 INFO Installing pgq_node

2013-09-21 02:01:14,791 17906 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql

2013-09-21 02:01:15,170 17906 INFO Installing londiste

2013-09-21 02:01:15,171 17906 INFO Reading from /opt/skytools/share/skytools3/londiste.sql

2013-09-21 02:01:15,685 17906 INFO londiste.global_add_table is installed

2013-09-21 02:01:15,726 17906 INFO Initializing node

2013-09-21 02:01:15,728 17906 INFO Location registered

2013-09-21 02:01:16,126 17906 INFO Node "node1" initialized for queue "replika" with type "root"

2013-09-21 02:01:16,139 17906 INFO Done

[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

2013-09-21 02:03:35,667 17932 INFO Installing pgq

2013-09-21 02:03:35,668 17932 INFO Reading from /opt/skytools/share/skytools3/pgq.sql

2013-09-21 02:03:36,319 17932 INFO pgq.get_batch_cursor is installed

2013-09-21 02:03:36,320 17932 INFO Installing pgq_ext

2013-09-21 02:03:36,320 17932 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql

2013-09-21 02:03:36,686 17932 INFO Installing pgq_node

2013-09-21 02:03:36,687 17932 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql

2013-09-21 02:03:37,137 17932 INFO Installing londiste

2013-09-21 02:03:37,137 17932 INFO Reading from /opt/skytools/share/skytools3/londiste.sql

2013-09-21 02:03:37,587 17932 INFO londiste.global_add_table is installed

2013-09-21 02:03:37,693 17932 INFO Initializing node

2013-09-21 02:03:37,737 17932 INFO Location registered

2013-09-21 02:03:37,755 17932 INFO Location registered

2013-09-21 02:03:37,783 17932 INFO Subscriber registered: node2

2013-09-21 02:03:37,802 17932 INFO Location registered

2013-09-21 02:03:37,827 17932 INFO Location registered

2013-09-21 02:03:38,165 17932 INFO Node "node2" initialized for queue "replika" with type "branch"

2013-09-21 02:03:38,179 17932 INFO Done

[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

2013-09-21 02:39:37,640 18358 INFO Installing pgq

2013-09-21 02:39:37,640 18358 INFO Reading from /opt/skytools/share/skytools3/pgq.sql

2013-09-21 02:39:38,347 18358 INFO pgq.get_batch_cursor is installed

2013-09-21 02:39:38,348 18358 INFO Installing pgq_ext

2013-09-21 02:39:38,348 18358 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql

2013-09-21 02:39:38,692 18358 INFO Installing pgq_node

2013-09-21 02:39:38,693 18358 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql

2013-09-21 02:39:39,378 18358 INFO Installing londiste

2013-09-21 02:39:39,378 18358 INFO Reading from /opt/skytools/share/skytools3/londiste.sql

2013-09-21 02:39:39,892 18358 INFO londiste.global_add_table is installed

2013-09-21 02:39:39,991 18358 INFO Initializing node

2013-09-21 02:39:40,035 18358 INFO Location registered

2013-09-21 02:39:40,063 18358 INFO Location registered

2013-09-21 02:39:40,093 18358 INFO Subscriber registered: node3

2013-09-21 02:39:40,118 18358 INFO Location registered

2013-09-21 02:39:40,133 18358 INFO Location registered

2013-09-21 02:39:40,158 18358 INFO Location registered

2013-09-21 02:39:40,540 18358 INFO Node "node3" initialized for queue "replika" with type "branch"

2013-09-21 02:39:40,553 18358 INFO Done

[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

2013-09-21 02:40:35,442 18372 INFO Installing pgq

2013-09-21 02:40:35,442 18372 INFO Reading from /opt/skytools/share/skytools3/pgq.sql

2013-09-21 02:40:36,208 18372 INFO pgq.get_batch_cursor is installed

2013-09-21 02:40:36,209 18372 INFO Installing pgq_ext

2013-09-21 02:40:36,210 18372 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql

2013-09-21 02:40:36,601 18372 INFO Installing pgq_node

2013-09-21 02:40:36,602 18372 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql

2013-09-21 02:40:37,032 18372 INFO Installing londiste

2013-09-21 02:40:37,033 18372 INFO Reading from /opt/skytools/share/skytools3/londiste.sql

2013-09-21 02:40:37,496 18372 INFO londiste.global_add_table is installed

2013-09-21 02:40:37,639 18372 INFO Initializing node

2013-09-21 02:40:37,704 18372 INFO Location registered

2013-09-21 02:40:37,718 18372 INFO Location registered

2013-09-21 02:40:37,742 18372 INFO Subscriber registered: node4

2013-09-21 02:40:37,762 18372 INFO Location registered

2013-09-21 02:40:37,789 18372 INFO Location registered

2013-09-21 02:40:37,814 18372 INFO Location registered

2013-09-21 02:40:37,839 18372 INFO Location registered

2013-09-21 02:40:38,221 18372 INFO Node "node4" initialized for queue "replika" with type "branch"

2013-09-21 02:40:38,260 18372 INFO Done

[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

2013-09-21 05:50:55,191 20501 INFO Installing pgq

2013-09-21 05:50:55,191 20501 INFO Reading from /opt/skytools/share/skytools3/pgq.sql

2013-09-21 05:50:55,886 20501 INFO pgq.get_batch_cursor is installed

2013-09-21 05:50:55,887 20501 INFO Installing pgq_ext

2013-09-21 05:50:55,888 20501 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql

2013-09-21 05:50:56,218 20501 INFO Installing pgq_node

2013-09-21 05:50:56,219 20501 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql

2013-09-21 05:50:56,603 20501 INFO Installing londiste

2013-09-21 05:50:56,603 20501 INFO Reading from /opt/skytools/share/skytools3/londiste.sql

2013-09-21 05:50:57,031 20501 INFO londiste.global_add_table is installed

2013-09-21 05:50:57,111 20501 INFO Initializing node

2013-09-21 05:50:57,164 20501 INFO Location registered

2013-09-21 05:50:57,176 20501 INFO Location registered

2013-09-21 05:50:57,206 20501 INFO Subscriber registered: node5

2013-09-21 05:50:57,228 20501 INFO Location registered

2013-09-21 05:50:57,249 20501 INFO Location registered

2013-09-21 05:50:57,266 20501 INFO Location registered

2013-09-21 05:50:57,282 20501 INFO Location registered

2013-09-21 05:50:57,307 20501 INFO Location registered

2013-09-21 05:50:57,656 20501 INFO Node "node5" initialized for queue "replika" with type "branch"

2013-09-21 05:50:57,677 20501 INFO Done

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, NOT UPTODATE

| +--: node4 (branch)

| Tables: 0/0/0

| Lag: 4h1m7s, Tick: 1, NOT UPTODATE

+--: node3 (branch)

| Tables: 0/0/0

| Lag: 4h3m29s, Tick: 1, NOT UPTODATE

+--: node5 (branch)

Tables: 0/0/0

Lag: 3h25m5s, Tick: 1, NOT UPTODATE

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 查看各节点状态

[postgres@londiste1 londiste3]$ londiste3 db1.ini status

Queue: replika Local node: node1

node1 (root)

| Tables: 0/0/0

| Lag: 12s, Tick: 27

+--: node2 (branch)

| | Tables: 0/0/0

| | Lag: 12s, Tick: 27

| +--: node4 (branch)

| Tables: 0/0/0

| Lag: 12s, Tick: 27

+--: node3 (branch)

| Tables: 0/0/0

| Lag: 12s, Tick: 27

+--: node5 (branch)

Tables: 0/0/0

Lag: 12s, Tick: 27

[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:33,997 21022 INFO Creating t1_pkey

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

2013-09-21 06:18:27,773 21046 INFO Creating t1_pkey

2013-09-21 06:18:27,875 21046 INFO Table added: public.t1

[postgres@londiste1 londiste3]$ londiste3 db4.ini add-table t1 --create

2013-09-21 06:18:31,035 21048 INFO Creating public.t1

2013-09-21 06:18:31,119 21048 INFO Creating t1_pkey

2013-09-21 06:18:31,218 21048 INFO Table added: public.t1

[postgres@londiste1 londiste3]$ londiste3 db5.ini add-table t1 --create

2013-09-21 06:18:38,220 21050 INFO Creating public.t1

2013-09-21 06:18:38,282 21050 INFO Creating t1_pkey

2013-09-21 06:18:38,350 21050 INFO Table added: 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:

id | data

----+------

1 | row1

2 | row2

3 | row3

(3 rows)

[postgres@londiste1 londiste3]$ psql -h 192.168.100.24 -d db3 -c "select * from t1"

Password:

id | data

----+------

1 | row1

2 | row2

3 | row3

(3 rows)

[postgres@londiste1 londiste3]$ psql -h 192.168.100.25 -d db4 -c "select * from t1"

Password:

id | data

----+------

1 | row1

2 | row2

3 | row3

(3 rows)

[postgres@londiste1 londiste3]$ psql -h 192.168.100.20 -d db5 -c "select * from t1"

Password:

id | data

----+------

1 | row1

2 | row2

3 | row3

(3 rows)

2.3、合并复制模式

HOSTNAME	IP	PG_VERSION	USER	PASSWORD	PORT	DB_NAME	ROLE
localhost	localhost	9.0.4 for centos32bit	postgres	highgo	5432	part1	root1
localhost	localhost	9.0.4 for centos32bit	postgres	highgo	5432	part2	root2
localhost	localhost	9.0.4 for centos32bit	postgres	highgo	5432	full	full

2.3.1 创建数据库

create database full1;

create database part1;

create database part2;

2.3.2 基本配置

2.3.2.1 配置ticker

[postgres@localhost conf]$ cat pgqd.ini

[pgqd]

database_list = part1,part2,full1

logfile = /opt/skytools/londiste/log/pgqd.log

pidfile = /opt/skytools/londiste/pid/pgqd.pid

2.3.2.2 数据库连接进程配置

[postgres@localhost conf]$ cat part1.ini

[londiste3]

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@localhost conf]$ cat part2.ini

[londiste3]

job_name = l3_part2

db = dbname=part2

queue_name = l3_part2_q

logfile = /opt/skytools/londiste/log/%(job_name)s.log

pidfile = /opt/skytools/londiste/pid/%(job_name)s.pid

[postgres@localhost conf]$ cat part1_full1.ini

[londiste3]

job_name = l3_part1_full1

db = dbname=full1

queue_name = l3_part1_q

logfile = /opt/skytools/londiste/log/%(job_name)s.log

pidfile = /opt/skytools/londiste/pid/%(job_name)s.pid

[postgres@localhost conf]$ cat part2_full1.ini

[londiste3]

job_name = l3_part2_full1

db = dbname=full1

queue_name = l3_part2_q

logfile = /opt/skytools/londiste/log/%(job_name)s.log

pidfile = /opt/skytools/londiste/pid/%(job_name)s.pid

2.3.2.3 创建root节点1

[postgres@localhost conf]$ londiste3 part1.ini create-root part1_root dbname=part1

2013-09-27 14:20:10,481 28176 WARNING No host= in public connect string, bad idea

2013-09-27 14:20:10,567 28176 INFO plpgsql is installed

2013-09-27 14:20:10,569 28176 INFO Installing pgq

2013-09-27 14:20:10,572 28176 INFO Reading from /opt/skytools/share/skytools3/pgq.sql

2013-09-27 14:20:10,893 28176 INFO pgq.get_batch_cursor is installed

2013-09-27 14:20:10,894 28176 INFO Installing pgq_ext

2013-09-27 14:20:10,894 28176 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql

2013-09-27 14:20:10,988 28176 INFO Installing pgq_node

2013-09-27 14:20:10,988 28176 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql

2013-09-27 14:20:11,085 28176 INFO Installing londiste

2013-09-27 14:20:11,085 28176 INFO Reading from /opt/skytools/share/skytools3/londiste.sql

2013-09-27 14:20:11,235 28176 INFO londiste.global_add_table is installed

2013-09-27 14:20:11,257 28176 INFO Initializing node

2013-09-27 14:20:11,259 28176 INFO Location registered

2013-09-27 14:20:11,331 28176 INFO Node "part1_root" initialized for queue "l3_part1_q" with type "root"

2013-09-27 14:20:11,336 28176 INFO Done

2.3.2.3 创建root节点2

[postgres@localhost conf]$ londiste3 part2.ini create-root part2_root dbname=part2

2013-09-27 14:20:30,440 28181 WARNING No host= in public connect string, bad idea

2013-09-27 14:20:30,538 28181 INFO plpgsql is installed

2013-09-27 14:20:30,539 28181 INFO Installing pgq

2013-09-27 14:20:30,540 28181 INFO Reading from /opt/skytools/share/skytools3/pgq.sql

2013-09-27 14:20:30,798 28181 INFO pgq.get_batch_cursor is installed

2013-09-27 14:20:30,799 28181 INFO Installing pgq_ext

2013-09-27 14:20:30,799 28181 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql

2013-09-27 14:20:30,892 28181 INFO Installing pgq_node

2013-09-27 14:20:30,892 28181 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql

2013-09-27 14:20:30,975 28181 INFO Installing londiste

2013-09-27 14:20:30,975 28181 INFO Reading from /opt/skytools/share/skytools3/londiste.sql

2013-09-27 14:20:31,102 28181 INFO londiste.global_add_table is installed

2013-09-27 14:20:31,129 28181 INFO Initializing node

2013-09-27 14:20:31,131 28181 INFO Location registered

2013-09-27 14:20:31,436 28181 INFO Node "part2_root" initialized for queue "l3_part2_q" with type "root"

2013-09-27 14:20:31,440 28181 INFO Done

2.3.2.4 创建leaf节点1

[postgres@localhost conf]$ londiste3 part1_full1.ini create-leaf merge_part1_full1 dbname=full1 --provider=dbname=part1

2013-09-27 14:44:14,558 28448 WARNING No host= in public connect string, bad idea

2013-09-27 14:44:14,566 28448 INFO plpgsql is installed

2013-09-27 14:44:14,567 28448 INFO pgq is installed

2013-09-27 14:44:14,569 28448 INFO pgq.get_batch_cursor is installed

2013-09-27 14:44:14,570 28448 INFO pgq_ext is installed

2013-09-27 14:44:14,571 28448 INFO pgq_node is installed

2013-09-27 14:44:14,573 28448 INFO londiste is installed

2013-09-27 14:44:14,574 28448 INFO londiste.global_add_table is installed

2013-09-27 14:44:14,592 28448 INFO Initializing node

2013-09-27 14:44:14,635 28448 INFO Location registered

2013-09-27 14:44:14,641 28448 INFO Location registered

2013-09-27 14:44:14,649 28448 INFO Subscriber registered: merge_part1_full1

2013-09-27 14:44:14,654 28448 INFO Location registered

2013-09-27 14:44:14,657 28448 INFO Location registered

2013-09-27 14:44:14,663 28448 INFO Node "merge_part1_full1" initialized for queue "l3_part1_q" with type "leaf"

2013-09-27 14:44:14,668 28448 INFO Done

2.3.2.5 创建leaf节点2

[postgres@localhost conf]$ londiste3 part2_full1.ini create-leaf merge_part2_full1 dbname=full1 --provider=dbname=part2

2013-09-27 14:44:31,967 28457 WARNING No host= in public connect string, bad idea

2013-09-27 14:44:31,974 28457 INFO plpgsql is installed

2013-09-27 14:44:31,975 28457 INFO pgq is installed

2013-09-27 14:44:31,977 28457 INFO pgq.get_batch_cursor is installed

2013-09-27 14:44:31,978 28457 INFO pgq_ext is installed

2013-09-27 14:44:31,979 28457 INFO pgq_node is installed

2013-09-27 14:44:31,980 28457 INFO londiste is installed

2013-09-27 14:44:31,981 28457 INFO londiste.global_add_table is installed

2013-09-27 14:44:31,994 28457 INFO Initializing node

2013-09-27 14:44:32,030 28457 INFO Location registered

2013-09-27 14:44:32,037 28457 INFO Location registered

2013-09-27 14:44:32,044 28457 INFO Subscriber registered: merge_part2_full1

2013-09-27 14:44:32,048 28457 INFO Location registered

2013-09-27 14:44:32,051 28457 INFO Location registered

2013-09-27 14:44:32,058 28457 INFO Node "merge_part2_full1" initialized for queue "l3_part2_q" with type "leaf"

2013-09-27 14:44:32,062 28457 INFO Done

2.3.2.6 启动tricker

[postgres@localhost conf]$ pgqd -d pgqd.ini

2013-09-27 14:39:50.675 28352 LOG Starting pgqd 3.1.5

2.3.2.7 启动worker

[postgres@localhost conf]$ londiste3 -d part1_full1.ini worker

[postgres@localhost conf]$ londiste3 -d part2_full1.ini worker

[postgres@localhost conf]$ ps -ef | grep londiste

postgres 28476 1 0 14:45 ? 00:00:00 /opt/python25/bin/python /opt/skytools/bin/londiste3 -d part1_full1.ini worker

postgres 28486 1 0 14:45 ? 00:00:00 /opt/python25/bin/python /opt/skytools/bin/londiste3 -d part2_full1.ini worker

postgres 28491 24970 0 14:46 pts/2 00:00:00 grep londiste

2.3.3 测试

2.3.3.1 创建测试表

[postgres@localhost ~]$ psql -d "part1" -c "create table mydata (id int4 primary key, data text)"

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "mydata_pkey" for table "mydata"

CREATE TABLE

[postgres@localhost ~]$ psql -d "part2" -c "create table mydata (id int4 primary key, data text)"

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "mydata_pkey" for table "mydata"

CREATE TABLE

2.3.3.2 root节点加入同步表

[postgres@localhost conf]$ londiste3 part1.ini add-table mydata

2013-09-27 14:49:04,577 28534 INFO Table added: public.mydata

[postgres@localhost conf]$ londiste3 part2.ini add-table mydata

2013-09-27 14:49:12,833 28539 INFO Table added: public.mydata

[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"

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

1 | l3_part1_q | public.mydata | f | | | | |

2 | l3_part2_q | public.mydata | f | | | | |

(2 rows)

{看到两个queue已经添加}

2.3.3.3 插入测试数据

[postgres@localhost ~]$ psql part1

psql (9.0.4)

Type "help" for help.

part1=# INSERT INTO mydata VALUES (1,'lianshunke1');

INSERT 0 1

part1=# \c part2

You are now connected to database "part2".

part2=# INSERT INTO mydata VALUES (2,'lianshunke2');

INSERT 0 1

2.3.3.4 在full1中创建并合并同步表

[postgres@localhost conf]$ londiste3 part1_full1.ini add-table mydata --create --merge-all

2013-09-27 14:53:21,861 28611 INFO Creating public.mydata

2013-09-27 14:53:22,063 28611 INFO Creating mydata_pkey

2013-09-27 14:53:22,137 28611 INFO Table added: public.mydata

【以下为两个queue在同步过程中的状态变化】

[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"

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

1 | l3_part1_q | public.mydata | t | | | | |

2 | l3_part2_q | public.mydata | t | | | | |

(2 rows)

[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"

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

(2 rows)

[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"

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

1 | l3_part1_q | public.mydata | t | catching-up | 2669:2669: | ALTER TABLE public.mydata ADD CONSTRAINT mydata_pkey+| |

2 | l3_part2_q | public.mydata | t | catching-up | 2681:2681: | | |

(2 rows)

[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"

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

1 | l3_part1_q | public.mydata | t | catching-up | 2669:2669: | | |

2 | l3_part2_q | public.mydata | t | catching-up | 2681:2681: | | |

(2 rows)

[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"

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

1 | l3_part1_q | public.mydata | t | wanna-sync:27 | 2669:2669: | | |

2 | l3_part2_q | public.mydata | t | catching-up | 2681:2681: | | |

(2 rows)

[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"

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

1 | l3_part1_q | public.mydata | t | ok | 2669:2669: | | |

2 | l3_part2_q | public.mydata | t | wanna-sync:24 | 2681:2681: | | |

(2 rows)

[postgres@localhost conf]$ psql -d "full1" -c "select * from londiste.table_info order by queue_name"

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

1 | l3_part1_q | public.mydata | t | ok | 2669:2669: | | |

2 | l3_part2_q | public.mydata | t | ok | 2681:2681: | | |

(2 rows)

{merge_state为ok时表明同步完成}

2.3.3.5 测试同步情况

[postgres@localhost ~]$ psql full1

psql (9.0.4)

Type "help" for help.

full1=# SELECT * from mydata;

id | data

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

1 | lianshunke1

2 | lianshunke2

(2 rows)

{数据已经同步}

【向part1中插入数据】

full1=# \c part1

You are now connected to database "part1".

part1=# INSERT INTO mydata VALUES (11,'lianshunke11');

INSERT 0 1

【向part2中插入数据】

part1=# \c part2

You are now connected to database "part2".

part2=# INSERT INTO mydata VALUES (22,'lianshunke22');

INSERT 0 1

【在full1中查看同步情况】

part2=# \c full1

You are now connected to database "full1".

full1=# SELECT * from mydata;

id | data

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

1 | lianshunke1

2 | lianshunke2

11 | lianshunke11

22 | lianshunke22

(4 rows)

【在full1中删除测试数据】

full1=# DELETE FROM mydata ;

ERROR: Table 'public.mydata' to queue 'l3_part1_q': change not allowed (D)

{同步表禁止在子节点上更改数据}

【在part1中删除数据】

full1=# \c part1

You are now connected to database "part1".

part1=# SELECT * from mydata ;

id | data

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

1 | lianshunke1

11 | lianshunke11

(2 rows)

part1=# DELETE FROM mydata where id=11;

DELETE 1

【在full1中查看】

part1=# \c full1

You are now connected to database "full1".

full1=# SELECT * from mydata ;

id | data

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

1 | lianshunke1

2 | lianshunke2

22 | lianshunke22

(3 rows)

{数据被删除}

2.3.4 拓扑情况

[postgres@localhost conf]$ londiste3 part1.ini status

Queue: l3_part1_q Local node: part1_root

part1_root (root)

| Tables: 1/0/0

| Lag: 1m0s, Tick: 33, NOT UPTODATE

+--: merge_part1_full1 (leaf)

Tables: 1/0/0

Lag: 1m0s, Tick: 33

[postgres@localhost conf]$ londiste3 part2.ini status

Queue: l3_part2_q Local node: part2_root

part2_root (root)

| Tables: 1/0/0

| Lag: 50s, Tick: 31, NOT UPTODATE

+--: merge_part2_full1 (leaf)

Tables: 1/0/0

Lag: 50s, Tick: 31

2.3.5 同步表状态

[postgres@localhost conf]$ londiste3 part1.ini tables

Tables on node

table_name merge_state table_attrs

--------------- --------------- ---------------

public.mydata ok

[postgres@localhost conf]$ londiste3 part2.ini tables

Tables on node

table_name merge_state table_attrs

--------------- --------------- ---------------

public.mydata ok

2.3.6 node状态

[postgres@localhost conf]$ londiste3 part1.ini members

Member info on part1_root@l3_part1_q:

node_name dead node_location

----------------- --------------- ---------------

merge_part1_full1 False dbname=full1

part1_root False dbname=part1

[postgres@localhost conf]$ londiste3 part2.ini members

Member info on part2_root@l3_part2_q:

node_name dead node_location

----------------- --------------- ---------------

merge_part2_full1 False dbname=full1

part2_root False dbname=part2

2.3.7 同步状态比较

[postgres@localhost conf]$ londiste3 part1.ini compare

2013-10-11 10:53:32,097 18193 INFO Checking if part1_root can be used for copy

2013-10-11 10:53:32,108 18193 INFO Node part1_root seems good source, using it

2013-10-11 10:53:32,109 18193 INFO public.mydata: Using node part1_root as provider

2013-10-11 10:53:32,131 18193 INFO Provider: part1_root (root)

2013-10-11 10:53:32,162 18193 INFO Locking public.mydata

2013-10-11 10:53:32,181 18193 INFO Syncing public.mydata

2013-10-11 10:53:34,713 18193 INFO Counting public.mydata

2013-10-11 10:53:34,885 18193 INFO srcdb: 2 rows, checksum=-3203416869

2013-10-11 10:53:34,887 18193 INFO dstdb: 2 rows, checksum=-3203416869

{此处可以看到源端与目标端的同步行数以及校验值，执行compare操作会对当前queue所针对的表进行一次同步}

[postgres@localhost conf]$ londiste3 part2.ini compare

2013-10-11 10:53:39,763 18203 INFO Checking if part2_root can be used for copy

2013-10-11 10:53:39,771 18203 INFO Node part2_root seems good source, using it

2013-10-11 10:53:39,772 18203 INFO public.mydata: Using node part2_root as provider

2013-10-11 10:53:39,791 18203 INFO Provider: part2_root (root)

2013-10-11 10:53:39,814 18203 INFO Locking public.mydata

2013-10-11 10:53:39,815 18203 INFO Syncing public.mydata

2013-10-11 10:53:42,331 18203 INFO Counting public.mydata

2013-10-11 10:53:42,482 18203 INFO srcdb: 2 rows, checksum=-1518757964

2013-10-11 10:53:42,484 18203 INFO dstdb: 2 rows, checksum=-1518757964

2.4、分割复制模式

HOSTNAME	IP	PG_VERSION	USER	PASSWORD	PORT	DB_NAME	ROLE
localhost	localhost	9.3rc1 for centos64	postgres	highgo	5432	part_root	root
localhost	localhost	9.3rc1 for centos64	postgres	highgo	5432	part_part0	leaf1
localhost	localhost	9.3rc1 for centos64	postgres	highgo	5432	part_part1	leaf2

2.4.1 前期准备

2.4.1.1 创建数据库

postgres=# create database part_root;

CREATE DATABASE

postgres=# create database part_part0;

CREATE DATABASE

postgres=# create database part_part1;

CREATE DATABASE

2.4.1.2 创建配置模式与配置表

【part_part0】

part_part0=# create schema partconf;

CREATE SCHEMA

part_part0=# CREATE TABLE partconf.conf (

part_part0(# part_nr integer,

part_part0(# max_part integer,

part_part0(# db_code bigint,

part_part0(# is_primary boolean,

part_part0(# max_slot integer,

part_part0(# cluster_name text

part_part0(# );

CREATE TABLE

part_part0=# insert into partconf.conf(part_nr, max_part) values(0,1);

INSERT 0 1

【part_part1】

part_part1=# CREATE SCHEMA partconf;

CREATE SCHEMA

part_part1=# CREATE TABLE partconf.conf (

part_part1(# part_nr integer,

part_part1(# max_part integer,

part_part1(# db_code bigint,

part_part1(# is_primary boolean,

part_part1(# max_slot integer,

part_part1(# cluster_name text

part_part1(# );

CREATE TABLE

part_part1=# insert into partconf.conf(part_nr, max_part) values(1,1);

INSERT 0 1

【part_root】

part_root=# create schema partconf;

CREATE SCHEMA

2.4.1.3 创建函数

[postgres@localhost ~]$ cd /opt/pg93/share/extension/

[postgres@localhost extension]$ psql part_root < hashlib--1.0.sql

CREATE FUNCTION

[postgres@localhost extension]$ psql part_part0 < hashlib--1.0.sql

CREATE FUNCTION

[postgres@localhost extension]$ psql part_part1 < hashlib--1.0.sql

CREATE FUNCTION

part_root=# \df

List of functions

Schema | Name | Result data type | Argument data types | Type

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

public | hash128_string | bytea | bytea, text | normal

public | hash128_string | bytea | bytea, text, bigint | normal

public | hash128_string | bytea | bytea, text, bigint, bigint | normal

public | hash128_string | bytea | text, text | normal

public | hash128_string | bytea | text, text, bigint | normal

public | hash128_string | bytea | text, text, bigint, bigint | normal

public | hash64_string | bigint | bytea, text | normal

public | hash64_string | bigint | bytea, text, bigint | normal

public | hash64_string | bigint | bytea, text, bigint, bigint | normal

public | hash64_string | bigint | text, text | normal

public | hash64_string | bigint | text, text, bigint | normal

public | hash64_string | bigint | text, text, bigint, bigint | normal

public | hash_int4 | integer | bigint, text | normal

public | hash_int4 | integer | integer, text | normal

public | hash_int8 | bigint | bigint, text | normal

public | hash_string | integer | bytea, text | normal

public | hash_string | integer | bytea, text, integer | normal

public | hash_string | integer | text, text | normal

public | hash_string | integer | text, text, integer | normal

(19 rows)

2.4.1.4 创建扩展

[postgres@localhost extension]$ psql part_root -c 'create extension hashlib'

CREATE EXTENSION

[postgres@localhost extension]$ psql part_part0 -c 'create extension hashlib'

CREATE EXTENSION

[postgres@localhost extension]$ psql part_part1 -c 'create extension hashlib'

CREATE EXTENSION

part_root=# \dx

List of installed extensions

Name | Version | Schema | Description

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

hashlib | 1.1 | public | Stable hash functions

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(2 rows)

2.4.2 基础配置

2.4.2.1 配置数据库连接进程信息

[postgres@localhost conf]$ cat part_root.ini

[londiste3]

job_name = part_root

db = dbname=part_root

queue_name = replika

logfile = /opt/skytools/londiste/log/part_root.log

pidfile = /opt/skytools/londiste/pid/part_root.pid

[postgres@localhost conf]$ cat part_part0.ini

[londiste3]

job_name = part_part0

db = dbname=part_part0

queue_name = replika

logfile = /opt/skytools/londiste/log/part_part0.log

pidfile = /opt/skytools/londiste/pid/part_part0.pid

[postgres@localhost conf]$ cat part_part1.ini

[londiste3]

job_name = part_part1

db = dbname=part_part1

queue_name = replika

logfile = /opt/skytools/londiste/log/part_part1.log

pidfile = /opt/skytools/londiste/pid/part_part1.pid

2.4.2.2 创建root节点（node1）并启动worker

[postgres@localhost conf]$ londiste3 part_root.ini create-root node1 dbname=part_root

2013-10-11 15:07:24,186 29307 WARNING No host= in public connect string, bad idea

2013-10-11 15:07:24,331 29307 INFO plpgsql is installed

2013-10-11 15:07:24,332 29307 INFO Installing pgq

2013-10-11 15:07:24,333 29307 INFO Reading from /opt/skytools/share/skytools3/pgq.sql

2013-10-11 15:07:25,350 29307 INFO pgq.get_batch_cursor is installed

2013-10-11 15:07:25,351 29307 INFO Installing pgq_ext

2013-10-11 15:07:25,351 29307 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql

2013-10-11 15:07:25,704 29307 INFO Installing pgq_node

2013-10-11 15:07:25,704 29307 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql

2013-10-11 15:07:26,126 29307 INFO Installing londiste

2013-10-11 15:07:26,126 29307 INFO Reading from /opt/skytools/share/skytools3/londiste.sql

2013-10-11 15:07:26,531 29307 INFO londiste.global_add_table is installed

2013-10-11 15:07:26,643 29307 INFO Initializing node

2013-10-11 15:07:26,646 29307 INFO Location registered

2013-10-11 15:07:26,912 29307 INFO Node "node1" initialized for queue "replika" with type "root"

2013-10-11 15:07:26,992 29307 INFO Done

[postgres@localhost conf]$ londiste3 -d part_root.ini worker

[postgres@localhost conf]$ ps -ef | grep worker

postgres 29327 1 0 15:08 ? 00:00:00 /opt/python25/bin/python /opt/skytools/bin/londiste3 -d part_root.ini worker

postgres 29335 27861 0 15:08 pts/2 00:00:00 grep worker

2.4.2.3 创建leaf1节点（node2_0）并启动worker

[postgres@localhost conf]$ londiste3 part_part0.ini create-leaf node2_0 dbname=part_part0 --provider=dbname=part_root

2013-10-11 15:09:33,157 29361 WARNING No host= in public connect string, bad idea

2013-10-11 15:09:33,165 29361 INFO plpgsql is installed

2013-10-11 15:09:33,166 29361 INFO Installing pgq

2013-10-11 15:09:33,167 29361 INFO Reading from /opt/skytools/share/skytools3/pgq.sql

2013-10-11 15:09:34,301 29361 INFO pgq.get_batch_cursor is installed

2013-10-11 15:09:34,302 29361 INFO Installing pgq_ext

2013-10-11 15:09:34,302 29361 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql

2013-10-11 15:09:34,474 29361 INFO Installing pgq_node

2013-10-11 15:09:34,475 29361 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql

2013-10-11 15:09:34,832 29361 INFO Installing londiste

2013-10-11 15:09:34,832 29361 INFO Reading from /opt/skytools/share/skytools3/londiste.sql

2013-10-11 15:09:36,634 29361 INFO londiste.global_add_table is installed

2013-10-11 15:09:37,523 29361 INFO Initializing node

2013-10-11 15:09:39,935 29361 INFO Location registered

2013-10-11 15:09:40,254 29361 INFO Location registered

2013-10-11 15:09:40,810 29361 INFO Subscriber registered: node2_0

2013-10-11 15:09:40,876 29361 INFO Location registered

2013-10-11 15:09:40,879 29361 INFO Location registered

2013-10-11 15:09:40,945 29361 INFO Node "node2_0" initialized for queue "replika" with type "leaf"

2013-10-11 15:09:41,001 29361 INFO Done

[postgres@localhost conf]$ londiste3 -d part_part0.ini worker

2.4.2.4 创建leaf2节点（node2_1）并启动worker

[postgres@localhost conf]$ londiste3 part_part1.ini create-leaf node2_1 dbname=part_part1 --provider=dbname=part_root

2013-10-11 15:10:05,861 29380 WARNING No host= in public connect string, bad idea

2013-10-11 15:10:05,867 29380 INFO plpgsql is installed

2013-10-11 15:10:05,869 29380 INFO Installing pgq

2013-10-11 15:10:05,928 29380 INFO Reading from /opt/skytools/share/skytools3/pgq.sql

2013-10-11 15:10:07,291 29380 INFO pgq.get_batch_cursor is installed

2013-10-11 15:10:07,293 29380 INFO Installing pgq_ext

2013-10-11 15:10:07,293 29380 INFO Reading from /opt/skytools/share/skytools3/pgq_ext.sql

2013-10-11 15:10:07,631 29380 INFO Installing pgq_node

2013-10-11 15:10:07,631 29380 INFO Reading from /opt/skytools/share/skytools3/pgq_node.sql

2013-10-11 15:10:07,871 29380 INFO Installing londiste

2013-10-11 15:10:07,872 29380 INFO Reading from /opt/skytools/share/skytools3/londiste.sql

2013-10-11 15:10:08,319 29380 INFO londiste.global_add_table is installed

2013-10-11 15:10:08,505 29380 INFO Initializing node

2013-10-11 15:10:10,610 29380 INFO Location registered

2013-10-11 15:10:10,743 29380 INFO Location registered

2013-10-11 15:10:10,927 29380 INFO Subscriber registered: node2_1

2013-10-11 15:10:10,963 29380 INFO Location registered

2013-10-11 15:10:10,966 29380 INFO Location registered

2013-10-11 15:10:10,969 29380 INFO Location registered

2013-10-11 15:10:11,041 29380 INFO Node "node2_1" initialized for queue "replika" with type "leaf"

2013-10-11 15:10:11,132 29380 INFO Done

[postgres@localhost conf]$ londiste3 -d part_part1.ini worker

[postgres@localhost conf]$ ps -ef | grep worker

postgres 29327 1 0 15:08 ? 00:00:00 /opt/python25/bin/python /opt/skytools/bin/londiste3 -d part_root.ini worker

postgres 29397 1 0 15:10 ? 00:00:00 /opt/python25/bin/python /opt/skytools/bin/londiste3 -d part_part1.ini worker

postgres 29403 1 0 15:10 ? 00:00:00 /opt/python25/bin/python /opt/skytools/bin/londiste3 -d part_part0.ini worker

postgres 29412 27861 0 15:10 pts/2 00:00:00 grep worker

2.4.2.5 配置ticker并启动

[postgres@localhost conf]$ cat pgqd.ini

[pgqd]

logfile = /opt/skytools/londiste/log/pgqd.log

pidfile = /opt/skytools/londiste/pid/pgqd.pid

[postgres@localhost conf]$ pgqd -d pgqd.ini

2013-10-11 15:12:42.278 29452 LOG Starting pgqd 3.1.5

[postgres@localhost conf]$ ps -ef | grep pgqd

postgres 29454 1 0 15:12 ? 00:00:00 pgqd -d pgqd.ini

postgres 29469 27861 0 15:12 pts/2 00:00:00 grep pgqd

2.4.3 在node1上创建同步表

part_root=# create table t1(id integer primary key);

CREATE TABLE

part_root=# insert into t1 values (1);

INSERT 0 1

part_root=# insert into t1 values (2);

INSERT 0 1

part_root=# select * from t1;

----

(2 rows)

2.4.4 向root与leaf节点添加复制表

[postgres@localhost conf]$ londiste3 part_root.ini add-table t1 --handler=part --handler-arg=key=id

2013-10-12 00:14:52,824 4394 INFO Table added: public.t1

[postgres@localhost conf]$ londiste3 part_part0.ini add-table t1 --create --handler=part --handler-arg=key=id

2013-10-12 00:14:59,093 4399 INFO Creating public.t1

2013-10-12 00:14:59,097 4399 INFO Creating t1_pkey

2013-10-12 00:14:59,133 4399 INFO Table added: public.t1

[postgres@localhost conf]$ londiste3 part_part1.ini add-table t1 --create --handler=part --handler-arg=key=id

2013-10-12 00:15:04,608 4404 INFO Creating public.t1

2013-10-12 00:15:04,612 4404 INFO Creating t1_pkey

2013-10-12 00:15:04,648 4404 INFO Table added: public.t1

part_root=# \d t1

Table "public.t1"

Column | Type | Modifiers

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

id | integer | not null

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', 'ev_extra3=''hash=''||partconf.get_hash_raw(id)')

_londiste_replika_truncate AFTER TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE PROCEDURE pgq.sqltriga('replika')

part_part0=# \d t1

Table "public.t1"

Column | Type | Modifiers

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

id | integer | not null

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', 'ev_extra3=''hash=''||partconf.get_hash_raw(id)', 'deny')

_londiste_replika_truncate AFTER TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE PROCEDURE pgq.sqltriga('replika', 'deny')

part_part1=# \d t1

Table "public.t1"

Column | Type | Modifiers

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

id | integer | not null

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', 'ev_extra3=''hash=''||partconf.get_hash_raw(id)', 'deny')

_londiste_replika_truncate AFTER TRUNCATE ON t1 FOR EACH STATEMENT EXECUTE PROCEDURE pgq.sqltriga('replika', 'deny')

2.4.5 测试

part_part0=# select * from t1;

----

(1 row)

part_part1=# select * from t1;

----

(1 row)

{可以看到之前表里的初始数据已经分配给了两个leaf节点}

part_root=# insert into t1 values (3);

INSERT 0 1

part_part1=# select * from t1;

----

(2 rows)

{分配给leaf2}

part_root=# insert into t1 values (4);

INSERT 0 1

part_part0=# select * from t1;

----

(2 rows)

{分配给leaf1}

part_root=# insert into t1 values (5);

INSERT 0 1

part_root=# insert into t1 values (6);

INSERT 0 1

part_part0=# select * from t1;

----

(3 rows)

part_part1=# select * from t1;

----

(3 rows)

【根据上面的测试可以发现其分配规律为12211221的分配方式】

2.5、使用walmgr3配置流复制

HOSTNAME	IP	PG_VERSION	USER	PASSWORD	PORT	ROLE
db1	192.168.100.37	9.3.2 for centos6.4-x64	postgres	highgo	5432	master
db2	192.168.100.38	9.3.2 for centos6.4-x64	postgres	highgo	5432	slave

2.5.1 配置hosts

[root@master ~]# cat /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

:#:1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.100.37 db1

192.168.100.38 db2

[root@slave ~]# cat /etc/hosts

127.0.0.1 localhost.localdomain localhost localhost4.localdomain4 localhost4

# Auto-generated hostname. Please do not remove this comment.

#::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.100.37 db1

192.168.100.38 db2

2.5.2 配置用户验证

[postgres@db1 ~]$ cd .ssh/

[postgres@db1 .ssh]$ ls

[postgres@db1 .ssh]$ ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/home/postgres/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /home/postgres/.ssh/id_rsa.

Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.

The key fingerprint is:

f1:87:23:aa:01:74:b7:f7:9b:74:7b:e6:e9:d6:80:2d postgres@db1

The key's randomart image is:

+--[ RSA 2048]----+

| |

| . . . . |

| . . . . o . |

| . . S + .o |

| . o o oE o |

| . . o .. o |

| o . + .+..|

| . o .*+ |

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

[postgres@db1 .ssh]$ ssh-copy-id -i id_rsa.pub db2

The authenticity of host 'db2 (192.168.100.38)' can't be established.

RSA key fingerprint is 70:02:66:0a:f5:3a:62:52:55:a2:98:b1:1c:d7:6c:73.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'db2,192.168.100.38' (RSA) to the list of known hosts.

postgres@db2 's password:

Now try logging into the machine, with "ssh 'db2'", and check in:

.ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

验证：

[postgres@db1 .ssh]$ ssh db2 date

Thu Dec 19 01:25:48 EST 2013

【当然也可通过说明文档中指出的使用walmgr3 –ssh-keygen 命令做免密码登录认证】

2.5.3 配置并启动主库

[root@db1 ~]# cd /opt/pg93/

[root@db1 pg93]# mkdir data

[root@db1 pg93]# chown postgres:postgres data/

[root@db1 pg93]# su - postgres

[postgres@db1 ~]$ initdb -D /opt/pg93/data/

[postgres@db1 data]$ vi postgresql.conf

listen_addresses = '*'

port = 5432

logging_collector = on

log_filename = 'postgresql.log'

log_connections = on

log_disconnections = on

[postgres@master data]$ pg_ctl start

2.5.4 配置walmgr3

主端配置文件：

[postgres@db1 pg93]$ cat wal-master.ini

[walmgr]

job_name = wal-master

logfile = /opt/pg93/data/log/%(job_name)s.log

pidfile = /opt/pg93/data/pid/%(job_name)s.pid

use_skylog = 0

master_db = port=5432 host=db1 dbname=template1

master_data = /opt/pg93/data

master_config = /opt/pg93/data/postgresql.conf

master_bin = /opt/pg93/bin

# set this only if you can afford database restarts during setup and stop.

# master_restart_cmd = pg_ctlcluster 9.1 main restart

slave = db2

slave_config = /opt/pg93/wal-slave.ini

walmgr_data = db2:/opt/pg93/slave_walmanager/backup

completed_wals = %(walmgr_data)s/logs.complete

partial_wals = %(walmgr_data)s/logs.partial

full_backup = %(walmgr_data)s/data.master

config_backup = %(walmgr_data)s/config.backup

# syncdaemon update frequency

loop_delay = 10.0

# use record based shipping available since 8.2

use_xlog_functions = 0

# pass -z to rsync, useful on low bandwidth links

compression = 0

# keep symlinks for pg_xlog and pg_log

keep_symlinks = 1

# tell walmgr to set wal_level to hot_standby during setup

hot_standby = 1

# periodic sync

#command_interval = 600

#periodic_command = /var/lib/postgresql/walshipping/periodic.sh

备端配置文件：

[postgres@db2 pg93]$ cat wal-slave.ini

[walmgr]

job_name = wal-standby

logfile = /opt/pg93/data/log/%(job_name)s.log

use_skylog = 0

slave_data = /opt/pg93/data

slave_bin = /opt/pg93/bin

slave_stop_cmd = /opt/pg93/bin/pg_ctl -D /opt/pg93/data stop

slave_start_cmd = /opt/pg93/bin/pg_ctl -D /opt/pg93/data start

#slave_config_dir = /tmp/test_slave

slave_config_dir = /opt/pg93/data

walmgr_data = /opt/pg93/slave_walmanager/backup

completed_wals = %(walmgr_data)s/logs.complete

partial_wals = %(walmgr_data)s/logs.partial

full_backup = %(walmgr_data)s/data.master

config_backup = %(walmgr_data)s/config.backup

backup_datadir = no

keep_backups = 0

# archive_command =

# primary database connect string for hot standby -- enabling

# this will cause the slave to be started in hot standby mode.

primary_conninfo = host=db1 user=postgres port=5432

配置复制：

[postgres@db1 pg93]$ walmgr3 wal-master.ini setup

2013-12-20 02:05:19,289 32538 INFO Configuring WAL archiving

2013-12-20 02:05:19,294 32538 INFO Sending SIGHUP to postmaster

LOG: received SIGHUP, reloading configuration files

LOG: parameter "archive_command" changed to "/opt/skytools/bin/walmgr3 /opt/pg93/wal-master.ini xarchive %p %f"

2013-12-20 02:05:19,491 32538 INFO Done

此时配置文件postgresql.conf中如下参数发生变化：

wal_level = 'hot_standby'

archive_mode = 'on'

archive_command = '/opt/skytools/bin/walmgr3 /opt/pg93/data/wal-master.ini xarchive %p %f'

并且，在db2中生成了备份目录：

[postgres@db2 pg93]$ ll slave_walmanager/backup/

total 16

drwxrwxr-x 2 postgres postgres 4096 Dec 19 22:04 config.backup

drwxrwxr-x 2 postgres postgres 4096 Dec 19 22:04 data.master

drwxrwxr-x 2 postgres postgres 4096 Dec 19 22:04 logs.complete

drwxrwxr-x 2 postgres postgres 4096 Dec 19 22:04 logs.partial

修改max_wal_senders参数：

max_wal_senders = 3

在pg_hba.conf中加入以下两条：

[postgres@db1 data]$ vi pg_hba.conf

host postgres postgres 192.168.100.0/24 trust

host replication postgres 192.168.100.0/24 trust

执行基础备份：

[postgres@db1 pg93]$ walmgr3 wal-master.ini backup

2013-12-20 02:05:58,339 28752 INFO Backup lock obtained.

2013-12-20 02:05:58,347 32547 INFO Execute SQL: select pg_start_backup('FullBackup'); [port=5432 host=db1 dbname=template1]

2013-12-20 02:05:59,015 32550 INFO {count: 1, duration: 0.518224000931}

2013-12-20 02:06:00,098 32547 INFO Checking tablespaces

2013-12-20 02:06:00,099 32547 INFO pg_log does not exist, skipping

2013-12-20 02:06:00,274 32547 INFO Backup conf files from /opt/pg93/data

2013-12-20 02:06:00,648 28826 INFO First useful WAL file is: 00000003000000000000002B

2013-12-20 02:06:00,660 32547 INFO Execute SQL: select pg_stop_backup(); [port=5432 host=db1 dbname=template1]

2013-12-20 02:06:01,313 32564 INFO {count: 1, duration: 0.562467098236}

2013-12-20 02:06:01,681 32568 INFO {count: 1, duration: 0.282044887543}

2013-12-20 02:06:02,876 28878 INFO Backup lock released.

2013-12-20 02:06:02,884 32547 INFO Full backup successful

Backup过程会在walmgr_data中生成基础备份数据，如下：

[postgres@db2 pg93]$ ls slave_walmanager/backup/

config.backup/ data.master/ logs.complete/ logs.partial/

[postgres@db2 pg93]$ ls slave_walmanager/backup/

config.backup data.master logs.complete logs.partial

[postgres@db2 pg93]$ ls slave_walmanager/backup/data.master/

PG_VERSION backup_label.old global pg_clog pg_notify pg_snapshots pg_stat_tmp pg_twophase pid

backup_label base log pg_multixact pg_serial pg_stat pg_subtrans pg_xlog recovery.conf

在备端执行恢复：

[postgres@db2 pg93]$ walmgr3 wal-slave.ini restore

2013-12-20 02:09:38,190 28909 WARNING backup_datadir is disabled, deleting old data dir

2013-12-20 02:09:38,191 28909 INFO Move /opt/pg93/slave_walmanager/backup/data.master to /opt/pg93/data

2013-12-20 02:09:38,197 28909 INFO Write /opt/pg93/data/recovery.conf

2013-12-20 02:09:38,198 28909 INFO Restoring configuration files

2013-12-20 02:09:38,200 28909 INFO Starting postmaster: /opt/pg93/bin/pg_ctl -D /opt/pg93/data start

server starting

[postgres@db2 pg93]$ LOG: database system was interrupted; last known up at 2013-12-20 02:05:58 EST

LOG: entering standby mode

2013-12-20 02:09:38,494 28916 INFO 00000003.history: not found (ignored)

2013-12-20 02:09:38,576 28917 INFO 00000003000000000000002B: Found

2013-12-20 02:09:38,603 28917 INFO {count: 1}

LOG: restored log file "00000003000000000000002B" from archive

LOG: redo starts at 0/2B000028

LOG: consistent recovery state reached at 0/2B0000F0

2013-12-20 02:09:38,776 28921 INFO 00000003000000000000002C: not found (ignored)

LOG: fetching timeline history file for timeline 3 from primary server

LOG: started streaming WAL from primary at 0/2C000000 on timeline 3

在备库上生成的recovery.conf内容如下：

[postgres@db2 pg93]$ cat data/recovery.conf

restore_command = '/opt/skytools/bin/walmgr3 /opt/pg93/wal-slave.ini xrestore %f "%p" %r'

standby_mode = 'on'

trigger_file = '/opt/pg93/slave_walmanager/backup/logs.complete/STOP'

primary_conninfo = 'host=db1 user=postgres port=5432'

archive_cleanup_command = '/opt/pg93/bin/pg_archivecleanup /opt/pg93/slave_walmanager/backup/logs.complete %r'

【至此，流复制已经实现。可将db1与db2的角色互换重复以上过程进行配置，便可实现db1与db2之间互相切换。】

2.5.5 验证

主端：

[postgres@db1 pg93]$ ps -ef | grep post

postgres 605 1 0 02:48 pts/1 00:00:00 /opt/pg93/bin/postgres

postgres 607 605 0 02:48 ? 00:00:00 postgres: checkpointer process

postgres 608 605 0 02:48 ? 00:00:00 postgres: writer process

postgres 609 605 0 02:48 ? 00:00:00 postgres: wal writer process

postgres 610 605 0 02:48 ? 00:00:00 postgres: autovacuum launcher process

postgres 611 605 0 02:48 ? 00:00:00 postgres: archiver process last was 000000030000000000000030.00000028.backup

postgres 612 605 0 02:48 ? 00:00:00 postgres: stats collector process

postgres 660 605 0 02:50 ? 00:00:00 postgres: wal sender process postgres 192.168.100.38(56960) streaming 0/310000C8

[postgres@db1 pg93]$ createdb pgbench

[postgres@db1 pg93]$ pgbench -i -s 1 pgbench

NOTICE: table "pgbench_history" does not exist, skipping

NOTICE: table "pgbench_tellers" does not exist, skipping

NOTICE: table "pgbench_accounts" does not exist, skipping

NOTICE: table "pgbench_branches" does not exist, skipping

creating tables...

100000 of 100000 tuples (100%) done (elapsed 0.20 s, remaining 0.00 s).

vacuum...

set primary keys...

done.

[postgres@db1 pg93]$ psql

psql (9.3.2)

Type "help" for help.

postgres=# select pg_current_xlog_location();

pg_current_xlog_location

--------------------------

0/31CB94B0

(1 row)

备端：

[postgres@db2 pg93]$ ps -ef | grep post

root 24384 24063 0 Dec19 pts/1 00:00:00 su - postgres

postgres 24385 24384 0 Dec19 pts/1 00:00:00 -bash

postgres 29539 1 0 02:50 pts/1 00:00:00 /opt/pg93/bin/postgres -D /opt/pg93/data

postgres 29540 29539 0 02:50 ? 00:00:00 postgres: startup process recovering 000000030000000000000031

postgres 29544 29539 0 02:50 ? 00:00:00 postgres: checkpointer process

postgres 29545 29539 0 02:50 ? 00:00:00 postgres: writer process

postgres 29546 29539 0 02:50 ? 00:00:00 postgres: stats collector process

postgres 29548 29539 0 02:50 ? 00:00:00 postgres: wal receiver process streaming 0/310000C8

[postgres@db2 pg93]$ psql pgbench

psql (9.3.2)

Type "help" for help.

pgbench=# \d+

List of relations

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

(4 rows)

pgbench=# select pg_last_xlog_receive_location();

pg_last_xlog_receive_location

-------------------------------

0/31CB94B0

(1 row)

pgbench=# select pg_last_xlog_replay_location();

pg_last_xlog_replay_location

------------------------------

0/31CB94B0

(1 row)

三、管理篇

【接2.2节环境进行以下实验】

3.1、变更拓扑关系

3.1.1 change-provider

【改变一个节点的provider】

当前拓扑关系如下：

[postgres@londiste1 londiste3]$ londiste3 db1.ini status

Queue: replika Local node: node1

node1 (root)

| Tables: 1/0/0

| Lag: 15s, Tick: 439

+--: node2 (branch)

| | Tables: 1/0/0

| | Lag: 15s, Tick: 439

| +--: node4 (branch)

| Tables: 1/0/0

| Lag: 15s, Tick: 439

+--: node3 (branch)

| Tables: 1/0/0

| Lag: 15s, Tick: 439

+--: node5 (branch)

Tables: 1/0/0

Lag: 15s, Tick: 439

将node4的provider更改为node3，如下：

[postgres@londiste1 londiste3]$ londiste3 db4.ini change-provider --provider=node3

2013-09-21 10:14:40,353 25458 INFO [node4] Consumer londiste_db4 tagged as paused

2013-09-21 10:14:40,366 25458 INFO Waiting for worker to accept

2013-09-21 10:14:41,371 25458 INFO Consumer 'londiste_db4' on node 'node4' paused

2013-09-21 10:14:41,386 25458 INFO [node3] Location registered

2013-09-21 10:14:41,409 25458 INFO [node3] Subscriber registered: node4

2013-09-21 10:14:41,416 25458 INFO [node4] Consumer provider node set to : node3

2013-09-21 10:14:41,424 25458 INFO [node4] Consumer londiste_db4 tagged as resumed

2013-09-21 10:14:41,431 25458 INFO Waiting for worker to accept

2013-09-21 10:14:42,436 25458 INFO Consumer 'londiste_db4' on node 'node4' resumed

2013-09-21 10:14:42,448 25458 INFO [node2] Subscriber unregistered: node4

[postgres@londiste1 londiste3]$ londiste3 db1.ini status

Queue: replika Local node: node1

node1 (root)

| Tables: 1/0/0

| Lag: 1s, Tick: 448

+--: node2 (branch)

| Tables: 1/0/0

| Lag: 1s, Tick: 448

+--: node3 (branch)

| Tables: 1/0/0

| Lag: 1s, Tick: 448

+--: node4 (branch)

| Tables: 1/0/0

| Lag: 1s, Tick: 448

+--: node5 (branch)

Tables: 1/0/0

Lag: 1s, Tick: 448

{node3已顺利接管node4}

将node5更改为node4的子节点，如下：

[postgres@londiste1 londiste3]$ londiste3 db5.ini change-provider --provider=node4

2013-09-21 10:16:11,661 25488 INFO [node5] Consumer londiste_db5 tagged as paused

2013-09-21 10:16:11,672 25488 INFO Waiting for worker to accept

2013-09-21 10:16:12,677 25488 INFO Consumer 'londiste_db5' on node 'node5' paused

2013-09-21 10:16:12,693 25488 INFO [node4] Location registered

2013-09-21 10:16:12,713 25488 INFO [node4] Subscriber registered: node5

2013-09-21 10:16:12,720 25488 INFO [node5] Consumer provider node set to : node4

2013-09-21 10:16:12,731 25488 INFO [node5] Consumer londiste_db5 tagged as resumed

2013-09-21 10:16:12,738 25488 INFO Waiting for worker to accept

2013-09-21 10:16:13,743 25488 INFO Consumer 'londiste_db5' on node 'node5' resumed

2013-09-21 10:16:13,756 25488 INFO [node3] Subscriber unregistered: node5

[postgres@londiste1 londiste3]$ londiste3 db1.ini status

Queue: replika Local node: node1

node1 (root)

| Tables: 1/0/0

| Lag: 3s, Tick: 451

+--: node2 (branch)

| Tables: 1/0/0

| Lag: 3s, Tick: 451

+--: node3 (branch)

| Tables: 1/0/0

| Lag: 3s, Tick: 451

+--: node4 (branch)

| Tables: 1/0/0

| Lag: 3s, Tick: 451

+--: node5 (branch)

Tables: 1/0/0

Lag: 3s, Tick: 451

{node5已成为node4的子节点}

将node4更改为node2的子节点，如下：

[postgres@londiste1 londiste3]$ londiste3 db4.ini change-provider --provider=node2

2013-09-21 10:17:20,633 25517 INFO [node4] Consumer londiste_db4 tagged as paused

2013-09-21 10:17:20,643 25517 INFO Waiting for worker to accept

2013-09-21 10:17:21,647 25517 INFO Consumer 'londiste_db4' on node 'node4' paused

2013-09-21 10:17:21,660 25517 INFO [node2] Location registered

2013-09-21 10:17:21,683 25517 INFO [node2] Subscriber registered: node4

2013-09-21 10:17:21,691 25517 INFO [node4] Consumer provider node set to : node2

2013-09-21 10:17:21,701 25517 INFO [node4] Consumer londiste_db4 tagged as resumed

2013-09-21 10:17:21,709 25517 INFO Waiting for worker to accept

2013-09-21 10:17:22,713 25517 INFO Consumer 'londiste_db4' on node 'node4' resumed

2013-09-21 10:17:22,725 25517 INFO [node3] Subscriber unregistered: node4

[postgres@londiste1 londiste3]$ londiste3 db1.ini status

Queue: replika Local node: node1

node1 (root)

| Tables: 1/0/0

| Lag: 45s, Tick: 452

+--: node3 (branch)

| Tables: 1/0/0

| Lag: 45s, Tick: 452

+--: node2 (branch)

| Tables: 1/0/0

| Lag: 45s, Tick: 452

+--: node4 (branch)

| Tables: 1/0/0

| Lag: 45s, Tick: 452

+--: node5 (branch)

Tables: 1/0/0

Lag: 45s, Tick: 452

{发现node4会携带其子节点受node2接管}

3.1.2 takeover

【使A节点直接接管B节点，接上小节】

使node3接管node5，如下：

[postgres@londiste1 londiste3]$ londiste3 db3.ini takeover node5

2013-09-21 10:19:58,929 25566 INFO old: node5

2013-09-21 10:19:58,998 25566 INFO [node5] Consumer londiste_db5 tagged as paused

2013-09-21 10:19:59,008 25566 INFO Waiting for worker to accept

2013-09-21 10:20:00,013 25566 INFO Consumer 'londiste_db5' on node 'node5' paused

2013-09-21 10:20:00,024 25566 INFO [node3] Location registered

2013-09-21 10:20:00,043 25566 INFO [node3] Subscriber registered: node5

2013-09-21 10:20:00,055 25566 INFO [node5] Consumer provider node set to : node3

2013-09-21 10:20:00,066 25566 INFO [node5] Consumer londiste_db5 tagged as resumed

2013-09-21 10:20:00,073 25566 INFO Waiting for worker to accept

2013-09-21 10:20:01,077 25566 INFO Consumer 'londiste_db5' on node 'node5' resumed

2013-09-21 10:20:01,091 25566 INFO [node4] Subscriber unregistered: node5

[postgres@londiste1 londiste3]$ londiste3 db1.ini status

Queue: replika Local node: node1

node1 (root)

| Tables: 1/0/0

| Lag: 2s, Tick: 459

+--: node2 (branch)

| | Tables: 1/0/0

| | Lag: 2s, Tick: 459

| +--: node4 (branch)

| Tables: 1/0/0

| Lag: 2s, Tick: 459

+--: node3 (branch)

| Tables: 1/0/0

| Lag: 2s, Tick: 459

+--: node5 (branch)

Tables: 1/0/0

Lag: 2s, Tick: 459

{已顺利接管}

子节点接管root节点，如下：

[postgres@londiste1 londiste3]$ londiste3 db2.ini takeover node1

2013-09-21 10:22:13,158 25616 INFO old: node1

2013-09-21 10:22:13,197 25616 INFO [node1] Consumer londiste_db1 tagged as paused

2013-09-21 10:22:13,202 25616 INFO Waiting for worker to accept

2013-09-21 10:22:22,224 25616 INFO Consumer 'londiste_db1' on node 'node1' paused

2013-09-21 10:22:22,227 25616 INFO [node1] Step 1: Writing disabled for: replika

2013-09-21 10:22:22,242 25616 INFO [node1] Step 2: Inserted last tick: replika

2013-09-21 10:22:23,260 25616 INFO [node2] Consumer londiste_db2 tagged as paused

2013-09-21 10:22:23,268 25616 INFO Waiting for worker to accept

2013-09-21 10:22:24,273 25616 INFO Consumer 'londiste_db2' on node 'node2' paused

2013-09-21 10:22:24,281 25616 INFO [node2] Branch node promoted to root

2013-09-21 10:22:24,299 25616 INFO [node2] Subscriber registered: node1

2013-09-21 10:22:24,315 25616 INFO [node1] Subscriber unregistered: node2

2013-09-21 10:22:24,327 25616 INFO [node2] Consumer londiste_db2 tagged as resumed

2013-09-21 10:22:24,334 25616 INFO Waiting for worker to accept

2013-09-21 10:22:25,338 25616 INFO Consumer 'londiste_db2' on node 'node2' resumed

2013-09-21 10:22:25,341 25616 INFO [node1] Step 3: Demoted root to branch: replika

2013-09-21 10:22:25,351 25616 INFO [node1] Consumer londiste_db1 tagged as resumed

2013-09-21 10:22:25,358 25616 INFO Waiting for worker to accept

2013-09-21 10:22:26,363 25616 INFO Consumer 'londiste_db1' on node 'node1' resumed

[postgres@londiste1 londiste3]$ londiste3 db1.ini status

Queue: replika Local node: node1

node2 (root)

| Tables: 1/0/0

| Lag: 8s, Tick: 463

+--: node4 (branch)

| Tables: 1/0/0

| Lag: 8s, Tick: 463

+--: node1 (branch)

| Tables: 1/0/0

| Lag: 8s, Tick: 463

+--: node3 (branch)

| Tables: 1/0/0

| Lag: 8s, Tick: 463

+--: node5 (branch)

Tables: 1/0/0

Lag: 8s, Tick: 463

{此时，接管节点成为root节点，远root节点变为node2的子节点，且原来与node2同级的节点也变为node2的子节点}

3.2、walmgr3常用命令

【2.5节中包括的此处不再累赘说明】

3.2.1 MASTER COMMANDS

1.列举当前备份命令(listbackups)：

[postgres@db1 pg93]$ walmgr3 wal-master.ini listbackups

2.对wal进行一次手动同步命令(sync)：

[postgres@db1 pg93]$ walmgr3 wal-master.ini sync

2013-12-23 21:44:32,280 22317 INFO 000000030000000000000030.00000028.backup: last complete

2013-12-23 21:44:32,320 22317 INFO Database state is not 'shut down', copying all

2013-12-23 21:44:32,320 22317 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000031

2013-12-23 21:44:32,822 22317 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000032

2013-12-23 21:44:33,310 22317 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000033

2013-12-23 21:44:33,850 22317 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000034

2013-12-23 21:44:34,595 22317 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000035

2013-12-23 21:44:35,057 22317 INFO Partial copy done

3.启动定时同步命令(syncdaemon)：

【同步间隔时间为配置文件中loop_delay参数指定】

[postgres@db1 pg93]$ walmgr3 wal-master.ini syncdaemon

2013-12-23 21:54:12,574 22473 INFO 000000030000000000000033: last complete

2013-12-23 21:54:12,581 22473 INFO Database state is not 'shut down', copying all

2013-12-23 21:54:12,582 22473 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000034

2013-12-23 21:54:12,987 22473 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000035

2013-12-23 21:54:13,148 22473 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000036

2013-12-23 21:54:13,575 22473 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000037

2013-12-23 21:54:13,988 22473 INFO Partial copy done

2013-12-23 21:54:23,990 22473 INFO 000000030000000000000033: last complete

2013-12-23 21:54:23,994 22473 INFO Database state is not 'shut down', copying all

2013-12-23 21:54:23,995 22473 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000034

2013-12-23 21:54:24,155 22473 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000035

2013-12-23 21:54:24,318 22473 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000036

2013-12-23 21:54:24,487 22473 INFO Syncing /opt/pg93/data/pg_xlog/000000030000000000000037

2013-12-23 21:54:24,658 22473 INFO Partial copy done

4.取消archive配置命令(stop)：

【执行后archive_command参数配置将取消】

[postgres@db1 pg93]$ walmgr3 wal-master.ini stop

2013-12-23 21:57:25,488 22524 INFO Disabling WAL archiving

2013-12-23 21:57:25,490 22524 INFO database must be restarted to disable archiving

2013-12-23 21:57:25,490 22524 INFO Setting archive_command to /bin/true to avoid WAL pileup

2013-12-23 21:57:25,493 22524 INFO Sending SIGHUP to postmaster

2013-12-23 21:57:25,494 22524 INFO Done

5.自定义定期任务命令(periodic)：

[postgres@db1 pg93]$ walmgr3 wal-master.ini periodic

该命令仅在配置文件中制定了定期执行命令（periodic_command）时有效。可以指定任何脚本，如同步、配置文件、系统定时任务crontab脚本等。

3.2.2 SLAVE COMMANDS

1.列举当前备份命令(listbackups)：

[postgres@db2 pg93]$ walmgr3 wal-slave.ini listbackups

List of backups:

Backup set Timestamp Label First WAL

--------------- ------------------------ ----------- ------------------------

data.master 2013-12-23 21:45:30 EST FullBackup 000000030000000000000032

2.暂停wal重放命令(pause)：

[postgres@db2 pg93]$ walmgr3 wal-slave.ini pause

2013-12-23 22:14:47,210 4139 INFO Pausing recovery mode

3.继续wal重放命令(continue)：

[postgres@db2 pg93]$ walmgr3 wal-slave.ini continue

2013-12-23 22:15:21,740 4149 INFO Continuing with recovery

4. 停止恢复模式命令(boot)：

【slave将变为可读写模式】

[postgres@db2 pg93]$ walmgr3 wal-slave.ini boot

2013-12-23 22:17:27,412 4164 INFO Stopping recovery mode

5.创建一个slave节点(createslave)：

【该命令使子节点的创建过程更加简单】

[postgres@db2 pg93]$ walmgr3 wal-slave.ini createslave

2013-12-23 22:21:20,216 4306 INFO Backup lock obtained.

2013-12-23 22:21:20,221 4306 INFO Starting pg_receivexlog

2013-12-23 22:21:20,224 4306 INFO Starting pg_basebackup

2013-12-23 22:21:22,772 4306 INFO pg_basebackup finished successfully

2013-12-23 22:21:22,773 4306 WARNING backup_datadir is disabled, deleting old data dir

2013-12-23 22:21:22,826 4306 INFO Move /opt/pg93/slave_walmanager/backup/data.master to /opt/pg93/data

2013-12-23 22:21:22,834 4306 INFO Write /opt/pg93/data/recovery.conf

2013-12-23 22:21:22,835 4306 INFO Starting postmaster: /opt/pg93/bin/pg_ctl -D /opt/pg93/data start

server starting

LOG: database system was interrupted; last known up at 2013-12-23 22:21:20 EST

LOG: creating missing WAL directory "pg_xlog/archive_status"

LOG: entering standby mode

2013-12-23 22:21:23,149 4358 INFO 00000003.history: Found

2013-12-23 22:21:23,154 4358 INFO {count: 1}

LOG: restored log file "00000003.history" from archive

2013-12-23 22:21:23,237 4360 INFO 000000030000000000000036: Found

2013-12-23 22:21:23,266 4360 INFO {count: 1}

LOG: restored log file "000000030000000000000036" from archive

LOG: redo starts at 0/36000028

LOG: consistent recovery state reached at 0/360000F0

LOG: database system is ready to accept read only connections

2013-12-23 22:21:23,462 4364 INFO 000000030000000000000037: not found (ignored)

LOG: started streaming WAL from primary at 0/37000000 on timeline 3

2013-12-23 22:21:27,845 4306 INFO pg_receivelog stopped

2013-12-23 22:21:27,846 4306 INFO Backup lock released.

2013-12-23 22:21:27,846 4306 INFO Streaming replication standby created successfully

四、参考文献

http://pgfoundry.org/projects/skytools/

http://skytools.projects.pgfoundry.org/skytools-3.0/

http://skytools.projects.pgfoundry.org/skytools-3.0/doc/skytools3.html

http://wiki.postgresql.org/wiki/Skytools#Documentation_3

http://wiki.postgresql.org/wiki/Londiste_Tutorial

五、错误解决

5.1、ImportError

错误现象：

[pg90@localhost conf]$ londiste3 part1.ini create-root part1_root dbname=part1

Traceback (most recent call last):

File "/opt/skytools/bin/londiste3", line 8, in <module>

import pkgloader

ImportError: No module named pkgloader

解决办法：

[root@localhost skytools-3.1.5]# python setup_pkgloader.py build

[root@localhost skytools-3.1.5]# python setup_pkgloader.py install

[root@localhost skytools-3.1.5]# python setup_skytools.py build

[root@localhost skytools-3.1.5]# python setup_skytools.py install

5.2、ProgrammingError

错误现象：

ProgrammingError: schema "partconf" does not exist

解决方法：

在root节点上也创建partconf模式：

part_root=# CREATE SCHEMA partconf;

CREATE SCHEMA

part_root=# CREATE TABLE partconf.conf (

part_root(# part_nr integer,

part_root(# max_part integer,

part_root(# db_code bigint,

part_root(# is_primary boolean,

part_root(# max_slot integer,

part_root(# cluster_name text

part_root(# );

CREATE TABLE

5.3、hash_string

part_root=# CREATE FUNCTION partconf.get_hash_raw

part_root-# ( i_input integer)

part_root-# RETURNS integer

part_root-# LANGUAGE sql

part_root-# AS $$

part_root$# -- used to wrap hashtext so that we can replace it in 8.4

part_root$# -- with older implementation to keep compatibility

part_root$# select hash_string($1::text, 'lookup2');

part_root$# $$;

ERROR: function hash_string(text, unknown) does not exist

LINE 8: select hash_string($1::text, 'lookup2');

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

5.4、rst2html

错误现象：

[postgres@localhost pghashlib-master]$ make install

/bin/mkdir -p '/opt/pgsql/lib'

/bin/mkdir -p '/opt/pgsql/share/contrib'

/bin/mkdir -p '/opt/pgsql/share/doc/contrib'

/bin/sh /opt/pgsql/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755 hashlib.so '/opt/pgsql/lib/hashlib.so'

rst2html README.rst > hashlib.html

/bin/sh: rst2html: command not found

make: *** [hashlib.html] Error 127

make: *** Deleting file `hashlib.html'

解决办法：

下载docutils并安装https://pypi.python.org/pypi/docutils

[root@localhost docutils-0.11]# python setup.py install

[root@localhost ~]# ln -s /opt/python25/bin/rst2html.py /usr/bin/rst2html

5.5、undefined symbol: le64toh

错误现象：

[postgres@localhost extension]$ psql test < hashlib--1.0.sql

ERROR: could not load library "/opt/pgsql/lib/hashlib.so": /opt/pgsql/lib/hashlib.so: undefined symbol: le64toh

解决办法：

换64位操作系统

5.6、找不到头文件

错误现象：

编译skytools报错，找不到相应头文件

[root@localhost skytools-3.1.5]# make

……

make[3]: Entering directory `/root/skytools-3.1.5/sql/pgq/lowlevel'

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -DHAVE_CONFIG_H -I. -I. -I/opt/pg93/include/server -I/opt/pg93/include/internal -D_GNU_SOURCE -c -o insert_event.o insert_event.c

insert_event.c:19:22: error: postgres.h: No such file or directory

insert_event.c:20:21: error: funcapi.h: No such file or directory

insert_event.c:22:29: error: catalog/pg_type.h: No such file or directory

insert_event.c:23:30: error: commands/trigger.h: No such file or directory

insert_event.c:24:26: error: executor/spi.h: No such file or directory

insert_event.c:25:28: error: lib/stringinfo.h: No such file or directory

insert_event.c:26:28: error: utils/builtins.h: No such file or directory

insert_event.c:27:28: error: utils/datetime.h: No such file or directory

insert_event.c:28:27: error: utils/hsearch.h: No such file or directory

insert_event.c:29:25: error: access/xact.h: No such file or directory

……

错误原因：