2014年1月6日 星期一

[研究] 雙機 PostgreSQL 9.3.2 同步(Replication)(使用pgpool-II 3.3.1)(CentOS 6.5 x64)

[研究] 雙機 PostgreSQL 9.3.2 同步(Replication)(使用pgpool-II 3.3.1)(CentOS 6.5 x64)

2014-01-06

環境 :

centos1 : 192.168.128.101 (PostgreSQL + pgpool-II)
centos2 : 192.168.128.102 (PostgreSQL)

一、安裝

centos1 上

ntpdate  tick.stdtime.gov.tw
service  iptables  stop
chkconfig  iptables  off

yum -y install http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
yum -y install postgresql93-server postgresql93-contrib pgadmin3_93 pgpool-II-93
service postgresql-9.3 initdb
service postgresql-9.3 start
chkconfig postgresql-9.3 on

檢查

[root@centos1 ~]# ps aux | grep postmaster
postgres  3488  0.0  1.4 324188 14612 ?        S    15:26   0:00 /usr/pgsql-9.3/bin/postmaster -p 5432 -D /var/lib/pgsql/9.3/data
root      3627  0.0  0.0 103248   868 pts/1    S+   15:42   0:00 grep postmaster

[root@centos1 ~]# cat /etc/passwd | grep postgres
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

PS : 啟動 Log 在 /var/lib/pgsql/9.3/pgstartup.log

centos2 上 (少安裝 pgpool-II-93)

ntpdate  tick.stdtime.gov.tw
service  iptables  stop
chkconfig  iptables  off

yum -y install http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
yum -y install postgresql93-server postgresql93-contrib pgadmin3_93
service postgresql-9.3 initdb
service postgresql-9.3 start
chkconfig postgresql-9.3 on

說明 :
ntpdate 是網路對時,校正本系統的時間,因為後面 ./configure 會檢查
service iptables stop 是把防火牆停用
yum 安裝基本需要的套件
因為 pgpool-II 沒有提供 yum 安裝的套件,只好抓 .tar.gz 回來自己編譯

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

二、設定

(1) 設定 pgpool.conf (centos1 上)

pgpool.conf是pgpool-II的設定檔。

[root@localhost ~]# find / -name pgpool.conf.*
/etc/pgpool-II-93/pgpool.conf.sample-stream
/etc/pgpool-II-93/pgpool.conf.sample-master-slave
/etc/pgpool-II-93/pgpool.conf.sample
/etc/pgpool-II-93/pgpool.conf.sample-replication
[root@localhost ~]#


cp  /etc/pgpool-II-93/pgpool.conf.sample  /etc/pgpool-II-93/pgpool.conf
vi  /etc/pgpool-II-93/pgpool.conf

增加、修改部分內容如下

listen_addresses = '*'
replication_mode = on
load_balance_mode = on


backend_hostname0 = '192.168.128.102'
backend_port0 = 5432
backend_weight0 = 1

backend_hostname1 = '192.168.128.101'
backend_port1 = 5432
backend_weight1 = 1

(2) 設定 pcp.conf (centos1 上)

pcp.conf是藉由網路介面管理pgpool-II的PCP 命令所需使用的認證設定檔,我們設定使用者為 postgres並配上md5加密過的密碼。


[root@localhost ~]# find / -name pcp.conf*
/etc/pgpool-II-93/pcp.conf.sample
[root@localhost ~]#


cp  /etc/pgpool-II-93/pcp.conf.sample  /etc/pgpool-II-93/pcp.conf
echo  "postgres:"`pg_md5 postgres`  >>  /etc/pgpool-II-93/pcp.conf

(3) pg_hba.conf (centos1 和 centos2 上)

[root@localhost ~]# find / -name pg_hba.conf*
/usr/pgsql-9.3/share/pg_hba.conf.sample
/var/lib/pgsql/9.3/data/pg_hba.conf
[root@localhost ~]#


[root@centos1 ~]# vi /var/lib/pgsql/9.3/data/pg_hba.conf

找到這部分(在檔案最後面)


# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                      peer
# IPv4 local connections:
host    all             all             127.0.0.1/32             ident
# IPv6 local connections:
host    all             all             ::1/128                  ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident


改為 (ident sameuser 改為 trust, 另外增加一行  0.0.0.0/0 )


# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32           trust
host    all         all         0.0.0.0/0             trust
# IPv6 local connections:
host    all             all             ::1/128                trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident



cp  /var/lib/pgsql/9.3/data/pg_hba.conf  /var/lib/pgsql/9.3/data/pg_hba.conf.old
sed  -i -e  "s@ident@trust@"  /var/lib/pgsql/9.3/data/pg_hba.conf
echo  "host    all         all         0.0.0.0/0             trust"  >>  /var/lib/pgsql/9.3/data/pg_hba.conf
#echo "listen_addresses = '*'" >>  /var/lib/pgsql/9.3/data/pg_hba.conf
#echo "max_connections = 60"  >> /var/lib/pgsql/9.3/data/pg_hba.conf
tail -n 10  /var/lib/pgsql/9.3/data/pg_hba.conf

service postgresql-9.3 restart
service postgresql-9.3 status

(4) 修改 postgresql.conf (centos1 和 centos2 上)


[root@localhost ~]# find / -name postgresql.conf
/var/lib/pgsql/9.3/data/postgresql.conf
[root@localhost ~]#


[root@centos1 ~]# vi  /var/lib/pgsql/9.3/data/postgresql.conf

增加此行
listen_addresses = '*'

重新啟動,讓設定生效


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

三、啟動

先重新啟動兩台的 PostgreSQL,讓設定生效;並確定 PostgreSQL 有啟動

centos1 上

[root@centos1 ~]# service postgresql-9.3 restart
Stopping postgresql-9.3 service:                           [  OK  ]
Starting postgresql-9.3 service:                           [  OK  ]

[root@centos1 ~]# ps aux | grep postmaster
postgres  3488  0.0  1.4 324188 14612 ?        S    15:26   0:00 /usr/pgsql-9.3/bin/postmaster -p 5432 -D /var/lib/pgsql/9.3/data
root      3627  0.0  0.0 103248   868 pts/1    S+   15:42   0:00 grep postmaster

centos2 上

[root@centos2 ~]# service postgresql-9.3 restart
Stopping postgresql-9.3 service:                           [  OK  ]
Starting postgresql-9.3 service:                           [  OK  ]
[root@localhost ~]#

[root@centos2 ~]# ps aux | grep postmaster
postgres  2932  0.0  1.4 324188 14600 ?        S    18:05   0:00 /usr/pgsql-9.3/bin/postmaster -p 5432 -D /var/lib/pgsql/9.3/data
root      2962  0.0  0.0 103248   872 pts/1    S+   18:06   0:00 grep postmaster
[root@centos2 ~]#

centos1 上啟動 pgpool

檢視 pgpool 設定檔案 /etc/pgpool-II-93/pgpool.conf 內容

pid_file_name = '/var/run/pgpool-II-93/pgpool.pid'
logdir = '/var/log/pgpool-II'

因為 pgpool 會建立 /var/run/pgpool/pgpool.pid 檔案,而 /var/run/pgpool-II-93/ 目錄若不存在,執行會失敗 )
啟動pgpool- II並將log紀錄檔存到/tmp/pgpool.log

[root@centos1 ~]# mkdir -p /var/run/pgpool-II-93/
[root@centos1 ~]# chmod a+w /var/run/pgpool-II-93/

啟動 pgpool-II-93

[root@centos1 init.d]# service pgpool-II-93 start
Starting pgpool-II-93 service:                             [  OK  ]
[root@centos1 init.d]#

PS: 或 [root@centos1 ~]# pgpool  -n  -d  >  /tmp/pgpool.log  2>&1  &

要看 pgpool 的參數可以執行

[root@localhost ~]# pgpool /?
pgpool-II version 3.3.1 (tokakiboshi),
  A generic connection pool/replication/load balance server for PostgreSQL

Usage:
  pgpool [ -c] [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]
         [ -n ] [ -D ] [ -d ]
  pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ]
         [ -m SHUTDOWN-MODE ] stop
  pgpool [ -f CONFIG_FILE ] [ -F PCP_CONFIG_FILE ] [ -a HBA_CONFIG_FILE ] reload

Common options:
  -a, --hba-file=HBA_CONFIG_FILE
                      Sets the path to the pool_hba.conf configuration file
                      (default: /etc/pgpool-II-93/pool_hba.conf)
  -f, --config-file=CONFIG_FILE
                      Sets the path to the pgpool.conf configuration file
                      (default: /etc/pgpool-II-93/pgpool.conf)
  -F, --pcp-file=PCP_CONFIG_FILE
                      Sets the path to the pcp.conf configuration file
                      (default: /etc/pgpool-II-93/pcp.conf)
  -h, --help          Prints this help

Start options:
  -c, --clear         Clears query cache (enable_query_cache must be on)
  -C, --clear-oidmaps Clears query cache oidmaps when memqcache_method is memcached
                      (If shmem, discards whenever pgpool starts.)
  -n, --dont-detach   Don't run in daemon mode, does not detach control tty
  -D, --discard-status Discard pgpool_status file and do not restore previous status
  -d, --debug         Debug mode

Stop options:
  -m, --mode=SHUTDOWN-MODE
                      Can be "smart", "fast", or "immediate"

Shutdown modes are:
  smart       quit after all clients have disconnected
  fast        quit directly, with proper shutdown
  immediate   the same mode as fast
[root@localhost ~]#


檢查 pgpool 是否有啟動

失敗情況如下

[root@localhost ~]# ps aux | grep pgpool
root      3510  0.0  0.0 103244   868 pts/1    S+   15:29   0:00 grep pgpool
[1]+  Exit 1                  pgpool -n -d > /tmp/pgpool.log 2>&1
[root@localhost ~]#

成功情況如下

[root@localhost log]# ps aux | grep pgpool
root      3525  0.1  0.5  83940  5852 pts/1    S    15:33   0:00 pgpool -n -d
root      3526  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3527  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3528  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3529  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3530  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3531  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3532  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3533  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3534  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3535  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3536  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3537  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3538  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3539  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3540  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3541  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3542  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3543  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3544  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3545  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3546  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3547  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3548  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3549  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3550  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3551  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3552  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3553  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3554  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3555  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3556  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3557  0.0  0.1  83940  1660 pts/1    S    15:33   0:00 pgpool: wait for connection request
root      3558  0.0  0.1  83940  1392 pts/1    S    15:33   0:00 pgpool: PCP: wait for connection request
root      3559  0.0  0.1  83940  1520 pts/1    S    15:33   0:00 pgpool: worker process
root      3563  0.0  0.0 103244   868 pts/1    S+   15:33   0:00 grep pgpool
[root@localhost log]#

[root@centos1 ~]# netstat -tan | grep 9999
tcp        0      0 0.0.0.0:9999                0.0.0.0:*                   LISTEN

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

四、測試

在 centos1 上建立 TestDB 資料庫和一些內容,然後到 centos2 上檢查

[root@centos1 ~]# createdb -p 9999 -U postgres TestDB

[root@centos1 ~]# su - postgres
-bash-4.1$ psql -p 9999
psql (9.3.2)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileg
-----------+----------+----------+-------------+-------------+------------------
 TestDB    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
           |          |          |             |             | postgres=CTc/post
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
           |          |          |             |             | postgres=CTc/post
(5 rows)

postgres=# \c TestDB
You are now connected to database "TestDB" as user "postgres".
TestDB=# create table table1 (id serial, title text, body text);
CREATE TABLE
TestDB=# insert into table1 (title, body) values ('pgpool', 'test');
INSERT 0 1
TestDB=# select * from table1;
 id | title  | body
----+--------+------
  1 | pgpool | test
(1 row)

TestDB=# \q
-bash-4.1$ exit
logout
[root@centos1 ~]#


如果不想自己用 create table 建立資料表,可以用下面命令建立些測試資料表

[root@centos1 ~]#  createdb -p 9999 -U postgres TestDB2
[root@centos1 ~]# su - postgres
-bash-4.1$ /usr/pgsql-9.3/bin/pgbench -i -p 5432 TestDB2
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.10 s, remaining 0.00 s).
vacuum...
set primary keys...
done.
-bash-4.1$ exit
logout
[root@centos1 ~]#

centos2 上進行檢查

[root@centos2 ~]# su - postgres
-bash-4.1$ psql -p 5432 -U postgres TestDB
psql (9.3.2)
Type "help" for help.

TestDB=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 TestDB    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 TestDB2   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

TestDB=# \c TestDB
You are now connected to database "TestDB" as user "postgres".
TestDB=# select * from table1;
 id | title  | body
----+--------+------
  1 | pgpool | test
(1 row)

TestDB=# \q
-bash-4.1$ exit
logout
[root@centos2 ~]#

若執行結果資訊都顯示正常,表示建立成功。

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

五、停止服務

停止 PostgreSQL 則執行指令:

[root@centos1 ~]# service postgresql-9.3 stop

[root@centos1 ~]# ps aux | grep postmaster

停止pgpool-II則執行指令:

[root@centos1 ~]# pgpool stop
stop request sent to pgpool. waiting for termination...done.
[1]+  Done                    pgpool -n -d > /tmp/pgpool.log 2>&1

[root@centos1 ~]# ps aux | grep pgpool
root      3570  0.0  0.0 103244   868 pts/1    S+   15:34   0:00 grep pgpool
[root@centos1 ~]#

刪除資料庫為
[root@centos1 ~]# dropdb -p 9999 -U postgres TestDB
[root@centos1 ~]# dropdb -p 9999 -U postgres TestDB2

(完)

[研究] 雙機 PostgreSQL 9.3.2 同步(Replication)(使用pgpool-II)(CentOS 6.5 x64)
http://shaurong.blogspot.tw/2014/01/postgresql-932-replicationpgpool.html

[研究] PostgreSQL 9.3.2 (yum) + PgAdminIII 1.18.1(yum)安裝 (CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/postgresql-932-yum-pgadminiii-1181yum.html

[研究] PostgreSQL 9.3.2 (tgz) + PgAdminIII 1.18.1(tgz)安裝 (CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/postgresql-932-tgz-pgadminiii-1181tgz.html

[研究] PostgreSQL 9.2(yum) + PgAdminIII v1.16.0(yum) 安裝 (CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/postgresql-92yum-pgadminiii-v1160yum.html

[研究] PostgreSQL 8.4.18(yum) + PgAdminIII v1.16.0(yum) 安裝 (CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/postgresql-8.html

[研究] PostgreSQL 9.3.2 安裝(tgz)(CentOS 6.5 x64)
http://shaurong.blogspot.tw/2014/01/postgresql-932-tgzcentos-65-x64.html

[研究] PostgreSQL 9.2 (yum) 安裝 (CentOS 6.5 x64)
http://shaurong.blogspot.tw/2013/12/postgresql-92-yum-centos-65-x64.html

[研究] PgAdminIII v1.12.2 安裝 (rpm)(CentOS 6.0 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=20415

[研究] PgAdminIII v1.8.4 安裝 (rpm)(CentOS 5.6 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=20379

[研究] PostgreSQL 8.4.2-1 binary文字模式安裝 (CentOS 5.4 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=17910

[研究] PostgreSQL 8.4.2-1 binary圖形模式安裝 (CentOS 5.4 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=17911

[研究] PostgreSQL 9.0.2-1 binary版文字模式安裝 (CentOS 5.5 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=19412

[研究] PostgreSQL 9.0.2-1 binary版圖形模式安裝 (CentOS 5.5 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=19413

[研究] PostgreSQL 9.0.2-1 binary圖形版模式安裝 (Fedora 14 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=19414

[研究] PostgreSQL 9.0.2-1 安裝(tgz)(CentOS 5.5 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=19411

[研究] PostgreSQL 9.0.4-1 binary版圖形模式安裝 (CentOS 6.0 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=20251

[研究] postgresql-8.4.2.tar.gz 安裝 (CentOS 5.4 x86)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=17912

[研究] 單機架設 PostgreSQL Cluster (pgpool-II)(CentOS 5.4)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=17906

[研究] 雙機 PostgreSQL 同步(Replication)(tar.gz)(pgpool-II)(CentOS
http://forum.icst.org.tw/phpbb/viewtopic.php?t=17980

[研究] 雙機 PostgreSQL 同步(Replication)(yum)(pgpool-II)(CentOS5)
http://forum.icst.org.tw/phpbb/viewtopic.php?t=17907

1 則留言:

  1. 版主 您好,

    請問一下, 您有做過 multi Master( Master-Master) , 推薦 雙向均可寫入的第三方工具做法嗎, 謝謝您!!

    回覆刪除