2.4.1.3 创建函数
[postgres@localhost ~]$ cd /opt/pg93/share/extension/
[postgres@localhost extension]$ psql part_root < hashlib--1.0.sql
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
[postgres@localhost extension]$ psql part_part0 < hashlib--1.0.sql
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
[postgres@localhost extension]$ psql part_part1 < hashlib--1.0.sql
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
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;
id
----
1
2
(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;
id
----
1
(1 row)
part_part1=# select * from t1;
id
----
2
(1 row)
{可以看到之前表里的初始数据已经分配给了两个leaf节点}
part_root=# insert into t1 values (3);
INSERT 0 1
part_part1=# select * from t1;
id
----
2
3
(2 rows)
{分配给leaf2}
part_root=# insert into t1 values (4);
INSERT 0 1
part_part0=# select * from t1;
id
----
1
4
(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;
id
----
1
4
5
(3 rows)
part_part1=# select * from t1;
id
----
2
3
6
(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 p_w_picpath is:
+--[ RSA 2048]----+
| |
| |
| . . . . |
| . . . . o . |
| . . S + .o |
| . o o oE o |
| . . o .. o |
| o . + .+..|
| . o .*+ |
+-----------------+
