PGPool主從流複製（高可用、讀寫分離）測試

1.將master資料庫關閉

/usr/local/pgsql9.1/pg_ctl -D /data/masterdata stop -m immediate

現象:pgpool自動連接到備份庫，備份庫並具有讀寫許可權。

2.先關閉pgpool，刪除pgpool status檔，重啟原master資料庫,再啟pgpool

pgpool -f ../etc/pgpool.conf -a ../etc/pool_hba.conf  -F ../etc/pcp.conf stop

rm -rf /usr/local/pgpool/log/pgpool_status

/usr/local/pgsql9.1/pg_ctl -D /data/masterdata start

pgpool -f ../etc/pgpool.conf -a ../etc/pool_hba.conf  -F ../etc/pcp.conf

現象:通過pgpool建立表和對原有表進行insert操作均出現無法提交。原因原slave庫已不處於slave狀態，而此主從又設置的即時主從導致事務無法提交。

3.將master庫複製到slave庫份，重啟slave。建立pgpool連接，在連接狀態關閉master資料庫

cd /data/slavedata

 mv postgresql.conf postgresql.conf_bak
 mv pg_hba.conf pg_hba.conf_bak

cd ..

\cp -rp masterdata/* slavedata

cd slavedata/

mv postgresql.conf_bak postgresql.conf

mv pg_hba.conf_bak pg_hba.conf

mv recovery.done recovery.conf

su - postgres

rm -rf /data/slavedata/postmaster.pid
/usr/local/pgsql9.1/bin/pg_ctl -D /data/slavedata/ start

pgpool -f ../etc/pgpool.conf -a ../etc/pool_hba.conf  -F ../etc/pcp.conf -n

psql -p 9998 -U postgres

postgres=# create table test ( id serial8);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
CREATE TABLE

[root@database1 ~]# psql -p 5433 -U postgres 
psql (9.1.2)
Type "help" for help.

postgres=# select * from test;
 id 
----
(0 rows)

[root@database1 ~]# psql -p 5432 -U postgres 
psql (9.1.2)
Type "help" for help.

postgres=# select * from test;
 id 
----
(0 rows)

su - postgres

-bash-3.2$ /usr/local/pgsql9.1/bin/pg_ctl -D /data/masterdata/ stop -m immediate
waiting for server to shut down... done
server stopped

[root@database1 ~]# psql -p 9998 -U postgres 
psql (9.1.2)
Type "help" for help.

postgres=# select * from test;
 id 
----
(0 rows)

postgres=# select * from test;
server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

現象:如果master連接斷開則原用戶端到pgpool端的連接將斷開。連接須重新建立。

3.主從流複製及pgpool工作正常，通過函數向表中插入資料。

psql -p 9998 -U postgres

 create table test ( id serial8,name varchar(50));

CREATE OR REPLACE FUNCTION test_insert(rcount int8)
  RETURNS int8 AS
$BODY$
DECLARE 
 i int8;
BEGIN
  i=1;
         while (i<=rcount) loop
            insert into test(name) values(i||'chen');
            i=i+1;
         end loop;
         return i;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

select * from test_insert(1);
ERROR:  cannot execute INSERT in a read-only transaction
CONTEXT:  SQL statement "insert into test(name) values(i||'chen')"
PL/pgSQL function "test_insert" line 7 at SQL statement

insert into test (name) values('czg');

INSERT 0 1

select  test_insert(100000) for share;
 test_insert 
-------------
      100001
(1 row)

結論：如函數對資料庫表有update、delete、insert操作或對資料庫其它物件修改將不能直接通過select調用，pgpool默認select 為查詢語句在Slave庫執行，如需執行此類函數需添加函數到黑名單。黑名單通過pgpool.conf（black_function_list）檔配置。

4.主從流複製及pgpool工作正常，通過函數批量插入500萬條記錄，在函數執行過程中強制關閉Master資料庫

psql -p 9998 -U postgres

select count(*) from test;
  count  
---------
 1200013
(1 row)

select  test_insert(5000000);

此時執行通過其它用戶端關閉主要資料庫
su - postgres

/usr/local/pgsql9.1/bin/pg_ctl -D /data/masterdata/ -m immediate stop

這在執行函數的psql用戶端報以下錯誤

WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
CONTEXT:  SQL statement "insert into test(name) values(i||'chen')"
PL/pgSQL function "test_insert" line 7 at SQL statement
The connection to the server was lost. Attempting reset: Failed.

\q

psql -p 9998 -U postgres

select count(*) from test;
  count  
---------
 1200013
(1 row)

結論：如在事務執行過程中master資料庫異常斷開，則事務自動回滾，pgpool並自動斷開用戶端連接。