2013年2月4日 星期一

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)
結論:如函數對資料庫表有updatedeleteinsert操作或對資料庫其它物件修改將不能直接通過select調用,pgpool默認select 為查詢語句在Slave庫執行,如需執行此類函數需添加函數到黑名單。黑名單通過pgpool.confblack_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並自動斷開用戶端連接。

沒有留言: