搭pl/proxy集群

最近朋友要上一个系统，设计百亿级数据。我去帮着搭建系统，采用pg/proxy集群，业务相关就不说了，这里就把简单技术验证放出来。相关人员比较保守，就用了OS官方的安装包，pg版本9.1.
规划中两台服务器上验证，一个上三个实例（一个实例做代理，另外两个做数据节点），一个上两个实例（做数据节点）
数据节目录如下，不过是在两台服务器上，如上所说。
/opt/pg91/pgdata0
/opt/pg91/pgdata1
/opt/pg91/pgdata2
/opt/pg91/pgdata3
/opt/pg91/pgdata4

0

第一部分，安装
1
查询可安装版本
zxw@pgproxy1:~$ apt-cache search postgres | grep 9
libpgtypes3 - shared library libpgtypes for PostgreSQL 9.1
postgresql-9.1 - object-relational SQL database, version 9.1 server
。。。
postgresql-9.1-slony1-2 - replication system for PostgreSQL: PostgreSQL 9.1 server plug-in
skytools-modules-9.1 - PostgreSQL 9.1 modules for skytools

2
装
zxw@pgproxy1:~$ sudo apt-get install -y postgresql-9.1 postgresql-client-9.1 postgresql-contrib-9.1 postgresql-server-dev-9.1
... ...
update-alternatives: using /usr/share/postgresql/9.1/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode.
 * Starting PostgreSQL 9.1 database server
   ...done.
Setting up postgresql-contrib-9.1 (9.1.14-0ubuntu0.12.04) ...
Setting up postgresql-server-dev-9.1 (9.1.14-0ubuntu0.12.04) ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place

看到上面这句就ok了，就是说有些事情在以上安装过程中给deferred（推迟）了，推迟到现在集中处理了。

3
查看，已经跑起来了
zxw@pgproxy1:~$ ps -ef | grep postgres
postgres  8362     1  0 17:22 ?        00:00:00 /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf
postgres  8364  8362  0 17:22 ?        00:00:00 postgres: writer process                                                                                                    
postgres  8365  8362  0 17:22 ?        00:00:00 postgres: wal writer process                                                                                                
postgres  8366  8362  0 17:22 ?        00:00:00 postgres: autovacuum launcher process                                                                                       
postgres  8367  8362  0 17:22 ?        00:00:00 postgres: stats collector process                                                                                           
zxw       8425  1486  0 17:34 pts/0    00:00:00 grep --color=auto postgres
zxw@pgproxy1:~$

zxw@pgproxy1:~$  /etc/init.d/postgresql status
Running clusters: 9.1/main

4
这样登录不行，默认Peer authentication
zxw@pgproxy1:~$ psql -U postgres postgres
psql: FATAL:  Peer authentication failed for user "postgres"
zxw@pgproxy1:~$

5
这样登录
zxw@pgproxy1:~$ sudo -u postgres psql
[sudo] password for zxw:
psql (9.1.14)
Type "help" for help.

postgres=# \q

5
确认Peer authentication
zxw@pgproxy1:~$ sudo nl /etc/postgresql/9.1/main/pg_hba.conf | more

    79  # Database administrative login by Unix domain socket
    80  local   all             postgres                                peer
       
    81  # TYPE  DATABASE        USER            ADDRESS                 METHOD
       
    82  # "local" is for Unix domain socket connections only
    83  local   all             all                                     peer
    84  # IPv4 local connections:
    85  host    all             all             127.0.0.1/32            md5

peer
Obtain the client’s operating system user name from the operating system and check if it
matches the requested database user name. This is only available for local connections. See
Section 19.3.7 for details.

给改成md5认证，

6
安装时没提示设置密码，超级用户密码随机了一个，给修改一下
postgres=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+