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
版主 您好,
回覆刪除請問一下, 您有做過 multi Master( Master-Master) , 推薦 雙向均可寫入的第三方工具做法嗎, 謝謝您!!