雙機 PostgreSQL 同步(Replication)

本範例是用 postgresql-8.4.2.tar.gz 進行安裝，下面這篇的 PostgreSQL 是用 yum 安裝

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

參考
PostgreSQL 8.4.3 Documentation - Chapter 15. Installation from Source Code
http://www.postgresql.org/docs/8.4/static/install-short.html
pgpool-II Tutorial
http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html
pgpool-II user manual
http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery
CentOS/PostgreSQL/pgpool-II
http://h2np.net/tips/wiki/index.php?CentOS%2FPostgreSQL%2Fpgpool-II

環境 :

Windows XP + VMware Workstation 7.01 上建立的 CentOS 5.4 x86 (VM)

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

一、安裝

centos1 和 centos2 上

代碼:

wget http://wwwmaster.postgresql.org/redir/359/h/source/v8.4.2/postgresql-8.4.2.tar.gz
ntpdate  tick.stdtime.gov.tw
service  iptables stop
yum -y install gcc readline  readline-devel zlib  zlib-devel
tar zxvf postgresql-8.4.2.tar.gz
cd  postgresql-8.4.2
./configure
gmake
#su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data

#初始化資料庫

su - postgres -c  "/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data"
或
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
exit
或
echo "/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data" | su - postgres


#啟動

cp /root/postgresql-8.4.2/contrib/start-scripts/linux  /etc/init.d/postgresql
chmod +x /etc/init.d/postgresql
chkconfig --add postgresql
chkconfig postgresql on

service postgresql start
# log 是 /usr/local/pgsql/data/serverlog
或
su - postgres
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
# logfile 會在 /home/postgres 目錄
exit
或
echo "/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data" | su - postgres
或
su - postgres -c  "/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data"

#測試

su - postgres
/usr/local/pgsql/bin/createdb test
exit
或
echo "/usr/local/pgsql/bin/createdb test" | su - postgres
或
su - postgres -c  "/usr/local/pgsql/bin/createdb test"

su - postgres
/usr/local/pgsql/bin/psql test
\l
或
echo "\\l" | su - postgres -c "/usr/local/pgsql/bin/psql test"

#建立環境

LD_LIBRARY_PATH=/usr/local/pgsql/lib
export LD_LIBRARY_PATH
PATH=/usr/local/pgsql/bin:$PATH
export PATH
MANPATH=/usr/local/pgsql/man:$MANPATH
export MANPATH

echo "LD_LIBRARY_PATH=/usr/local/pgsql/lib" >>  /etc/profile
echo "export LD_LIBRARY_PATH" >>  /etc/profile
echo "PATH=/usr/local/pgsql/bin:$PATH" >>  /etc/profile
echo "export PATH" >>  /etc/profile
echo "MANPATH=/usr/local/pgsql/man:$MANPATH" >>  /etc/profile
echo "export MANPATH" >>  /etc/profile

#安裝pgbench

cd contrib/pgbench
make
make install

centos1 上安裝 pgpool-II

代碼:

cd
wget http://pgfoundry.org/frs/download.php/2572/pgpool-II-2.3.2.2.tar.gz
tar zxvf  pgpool-II-2.3.2.2.tar.gz;
cd  pgpool-II-2.3.2.2/
./configure  --prefix=/usr/local/pgpool  --with-pgsq=/usr/local/pgsql 
make;  make install
cd

PATH=/usr/local/pgpool/bin:$PATH
export PATH

echo "PATH=/usr/local/pgpool/bin:$PATH" >>  /etc/profile
echo "export PATH" >>  /etc/profile

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

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

二、設定 pgpool 需要的設定

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

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

代碼:

cp  /usr/local/pgpool/etc/pgpool.conf.sample-replication  /usr/local/pgpool/etc/pgpool.conf
vim  /usr/local/pgpool/etc/pgpool.conf

修改內容如下(註解掉的是原來的值)

代碼:

#listen_addresses = 'localhost'
listen_addresses = '*'
replication_mode = true
load_balance_mode = true

#backend_hostname0 = 'host1'
backend_hostname0 = '192.168.128.101'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data'

#backend_hostname1 = 'host2'
backend_hostname1 = '192.168.128.102'
backend_port1 = 5432
backend_weight1 = 1
#backend_data_directory1 = '/data1'
backend_data_directory1 = '/data'

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

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

代碼:

cp  -f /usr/local/pgpool/etc/pcp.conf.sample   /usr/local/pgpool/etc/pcp.conf
echo  "postgres:"`pg_md5 postgres`  >>  /usr/local/pgpool/etc/pcp.conf

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

代碼:

vim  /usr/local/pgsql/data/pg_hba.conf

增加一筆

代碼:

host     all         all         0.0.0.0/0             trust

或執行下面指令增加

代碼:

sed -i -e "/127/ahost  all  all  0.0.0.0\/0  trust"  /usr/local/pgsql/data/pg_hba.conf

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

代碼:

vim  /usr/local/pgsql/data/postgresql.conf

修改下面值(沒有就增加這行)

代碼:

listen_addresses = '*'

或

代碼:

sed -i -e "s@#listen_addresses = 'localhost'@listen_addresses = '*'@"   /usr/local/pgsql/data/postgresql.conf

重新啟動 PostgreSQL，讓PostgreSQL 相關設定生效

代碼:

service  postgresql restart

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

三、啟動 pgpool

centos1 上啟動 pgpool

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

代碼:

[root@centos1 ~]# mkdir -p /var/run/pgpool
[root@centos1 ~]# pgpool  -n  -d  >  /tmp/pgpool.log  2>&1  &

檢查 pgpool 是否有啟動

代碼:

[root@centos1 pgpool-II-2.3.2.2]# ps aux | grep pgpool
root     25676  0.0  0.3   6812  1548 pts/2    S    13:24   0:00 pgpool
root     25677  0.0  0.1   6812   644 pts/2    S    13:24   0:00 pgpool:
wait for connection request
..(很多)

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

若要停止pgpool-II則執行指令 (要測試了，請勿停掉):

代碼:

[root@centos1 ~]# pgpool stop
stop request sent to pgpool. waiting for termination....done.

要看 pgpool 的參數可以執行

代碼:

[root@centos1 ~]# pgpool /?

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

四、測試

在 centos1 上建立資料庫和，然後到 centos2 上檢查

代碼:

[root@centos1 ~]# su - postgres
[postgres@centos1 ~]$ /usr/local/pgsql/bin/createuser pgpool
Shall the new role be a superuser? (y/n) y

[postgres@centos1 ~]$ /usr/local/pgsql/bin/createdb DB1 -U pgpool
[postgres@centos1 ~]$ /usr/local/pgsql/bin/createdb DB2 -U pgpool -p 9999  (這個應該會失敗)
[postgres@centos1 ~]$ /usr/local/pgsql/bin/createdb DB3 -U postgres
[postgres@centos1 ~]$ /usr/local/pgsql/bin/createdb DB4 -U postgres -p 9999

[postgres@centos1 ~]$ echo "\\l" | psql
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
DB1       | pgpool   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
DB3       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
DB4       | 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
(6 rows)

到 centos2 主機，列出資料庫應該會看到 DB4 (DB1, DB3 是建立在 centos1 上)

代碼:

[postgres@centos2 ~]# echo "\\l" | su - postgres -c "/usr/local/pgsql/bin/psql"

                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
DB4       | 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
(4 rows)

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

常用指令與範例參考

代碼:

[postgres@centos1 ~]$ psql -p 5432  連上 PostgreSQL 系統
[postgres@centos1 ~]$ psql -p 9999  連上 pgpool 系統
postgres=# \l      列出所有資料庫
postgres=# \c TestDB   連上 TestDB 資料庫
TestDB=# create table table1 (id serial, title text, body text);   建立資料表
TestDB=# \dt                        列出所有資料表
TestDB=# insert into table1 (title, body) values ('pgpool', 'test');   插入資料
TestDB=# select * from table1;                  查詢資料表
TestDB=# \q                        離開 psql
[postgres@centos1 ~]$  exit                  離開 postgres 帳號

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

代碼:

[postgres@centos1 ~]$  pgbench  -i  -p  9999  DB4