[pgcluster: 775] pg_dump及びpg_restoreについて
nagaya
nagaya ＠ mediamagic.co.jp
2005年 4月 16日 (土) 17:54:18 JST
はじめまして、長屋と申します。
現在、PGClusterの導入の為、動作テストを行っているのですが
pg_restoreおよびpg_dumpを実行するとレプリケーションサーバが反応しなくなります。
cluster01には正常にリストアされますが、cluster02には反映されません。
ロードバランサから実行しても同様でした。
insertやcreate文は正常です。後、リカバリも問題ありません。
以下に環境及び検証結果を示します。
どうか、ご教授の程よろしくお願いいたします。
------------
環境及び構成
------------
DELL PowerEdge 750 Pen4(2.8GHz)×2
Debian GNU/Linux 3.1
kernel 2.6.8(smpはOFFでコンパイル)
pgcluster-1.1.1a(※1.3.0cではpg_restoreのみ正常でした)
clusterDB×2 (cluster01,cluster02)
pgrp×1
pglb×1
-------
検 証
-------
cluster01にて
test=# \d test
Table "public.test"
Column | Type | Modifiers
-----------+------------------------+-----------------------------------------------------------
item_id | integer | not null default nextval('public.test_item_id_seq'::text)
user_id | character varying(128) |
career_id | character varying(32) |
test=# select * from test;
item_id | user_id | career_id
---------+---------+-----------
1 | aaa | aaaa
2 | bbb | bbbb
3 | ccc | cccc
(3 rows)
$ ./pg_dump -Ft -b test > test.tar ←※この時点でレプリケーションサーバが落ちます(ok. stop all child processes)
$ ./dropdb test
$ ./createdb test
$ ./pg_restore -d test test.tar
----------------------------------------------
レストア後のレプリケーションサーバのDEBUGログ
----------------------------------------------
DEBUG:replicate_loop():replicate_loop selected
DEBUG:PGRreplicate_packet_send():cmdSts=Q
DEBUG:PGRreplicate_packet_send():cmdType=T
DEBUG:PGRreplicate_packet_send():rlog=0
DEBUG:PGRreplicate_packet_send():request_id=1
DEBUG:PGRreplicate_packet_send():replicate_id=0
DEBUG:PGRreplicate_packet_send():port=5432
DEBUG:PGRreplicate_packet_send():pid=15363
DEBUG:PGRreplicate_packet_send():from_host=mama01
DEBUG:PGRreplicate_packet_send():dbName=test
DEBUG:PGRreplicate_packet_send():userName=postgres
DEBUG:PGRreplicate_packet_send():recieve sec=1113607043
DEBUG:PGRreplicate_packet_send():recieve usec=276052
DEBUG:PGRreplicate_packet_send():query_size=30
DEBUG:PGRreplicate_packet_send():query=SET client_encoding = 'EUC_JP'
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[3,1113607043,276052,1,1]
DEBUG:PGRreturn_result():status of PGRreturn_result[0]
DEBUG:pgr_createConn():PQsetdbLogin host[mama02.is.mediamagic.co.jp] port[5432]
db[test] user[postgres]
DEBUG:pgr_createConn():PQsetdbLogin ok
DEBUG:send_sync_data():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,111360
7043,276052,1,1) )
DEBUG:sem_unlock[1]
DEBUG:replicate_loop():replicate_loop selected
DEBUG:replicate_loop():replicate_loop selected
DEBUG:PGRreplicate_packet_send():cmdSts=Q
DEBUG:PGRreplicate_packet_send():cmdType=T
DEBUG:PGRreplicate_packet_send():rlog=0
DEBUG:PGRreplicate_packet_send():request_id=2
DEBUG:PGRreplicate_packet_send():replicate_id=0
DEBUG:PGRreplicate_packet_send():port=5432
DEBUG:PGRreplicate_packet_send():pid=15363
DEBUG:PGRreplicate_packet_send():from_host=mama01
DEBUG:PGRreplicate_packet_send():dbName=test
DEBUG:PGRreplicate_packet_send():userName=postgres
DEBUG:PGRreplicate_packet_send():recieve sec=1113607043
DEBUG:PGRreplicate_packet_send():recieve usec=280022
DEBUG:PGRreplicate_packet_send():query_size=33
DEBUG:PGRreplicate_packet_send():query=SET check_function_bodies = false
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[3,1113607043,280022,2,1]
DEBUG:PGRreturn_result():status of PGRreturn_result[0]
DEBUG:send_sync_data():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,111360
7043,280022,2,1) )
DEBUG:sem_unlock[1]
DEBUG:replicate_loop():replicate_loop selected
DEBUG:replicate_loop():replicate_loop selected
DEBUG:PGRreplicate_packet_send():cmdSts=S
DEBUG:PGRreplicate_packet_send():cmdType=a
DEBUG:PGRreplicate_packet_send():rlog=0
DEBUG:PGRreplicate_packet_send():request_id=3
DEBUG:PGRreplicate_packet_send():replicate_id=0
DEBUG:PGRreplicate_packet_send():port=5432
DEBUG:PGRreplicate_packet_send():pid=15363
DEBUG:PGRreplicate_packet_send():from_host=mama01
DEBUG:PGRreplicate_packet_send():dbName=test
DEBUG:PGRreplicate_packet_send():userName=postgres
DEBUG:PGRreplicate_packet_send():recieve sec=1113607043
DEBUG:PGRreplicate_packet_send():recieve usec=281496
DEBUG:PGRreplicate_packet_send():query_size=36
DEBUG:PGRreplicate_packet_send():query=SET SESSION AUTHORIZATION 'postgres'
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():wait for answer
DEBUG:pgr_createConn():PQsetdbLogin host[mama02.is.mediamagic.co.jp] port[5432]
db[test] user[postgres]
DEBUG:answer[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG:read_answer():QUERY DONE
DEBUG:PGRreturn_result():status of PGRreturn_result[0]
DEBUG:pgr_createConn():PQsetdbLogin ok
DEBUG:send_sync_data():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,111360
7043,281496,2,1) )
DEBUG:sem_unlock[1]
DEBUG:replicate_loop():replicate_loop selected
DEBUG:PGRreplicate_packet_send():cmdSts=S
DEBUG:PGRreplicate_packet_send():cmdType=O
DEBUG:PGRreplicate_packet_send():rlog=0
DEBUG:PGRreplicate_packet_send():request_id=4
DEBUG:PGRreplicate_packet_send():replicate_id=0
DEBUG:PGRreplicate_packet_send():port=5432
DEBUG:PGRreplicate_packet_send():pid=15363
DEBUG:PGRreplicate_packet_send():from_host=mama01
DEBUG:PGRreplicate_packet_send():dbName=test
DEBUG:PGRreplicate_packet_send():userName=postgres
DEBUG:PGRreplicate_packet_send():recieve sec=1113607043
DEBUG:PGRreplicate_packet_send():recieve usec=285580
DEBUG:PGRreplicate_packet_send():query_size=39
DEBUG:PGRreplicate_packet_send():query=REVOKE ALL ON SCHEMA public FROM PUBLIC
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():wait for answer
DEBUG:send_sync_data():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,111360
7043,285580,2,1) )
DEBUG:answer[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG:read_answer():QUERY DONE
DEBUG:PGRreturn_result():status of PGRreturn_result[0]
DEBUG:sem_unlock[1]
DEBUG:replicate_loop():replicate_loop selected
DEBUG:PGRreplicate_packet_send():cmdSts=S
DEBUG:PGRreplicate_packet_send():cmdType=O
DEBUG:PGRreplicate_packet_send():rlog=0
DEBUG:PGRreplicate_packet_send():request_id=5
DEBUG:PGRreplicate_packet_send():replicate_id=0
DEBUG:PGRreplicate_packet_send():port=5432
DEBUG:PGRreplicate_packet_send():pid=15363
DEBUG:PGRreplicate_packet_send():from_host=mama01
DEBUG:PGRreplicate_packet_send():dbName=test
DEBUG:PGRreplicate_packet_send():userName=postgres
DEBUG:PGRreplicate_packet_send():recieve sec=1113607043
DEBUG:PGRreplicate_packet_send():recieve usec=297720
DEBUG:PGRreplicate_packet_send():query_size=36
DEBUG:PGRreplicate_packet_send():query=GRANT ALL ON SCHEMA public TO PUBLIC
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():wait for answer
DEBUG:send_sync_data():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,111360
7043,297720,2,1) )
DEBUG:answer[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG:read_answer():QUERY DONE
DEBUG:PGRreturn_result():status of PGRreturn_result[0]
DEBUG:sem_unlock[1]
DEBUG:replicate_loop():replicate_loop selected
DEBUG:PGRreplicate_packet_send():cmdSts=S
DEBUG:PGRreplicate_packet_send():cmdType=a
DEBUG:PGRreplicate_packet_send():rlog=0
DEBUG:PGRreplicate_packet_send():request_id=6
DEBUG:PGRreplicate_packet_send():replicate_id=0
DEBUG:PGRreplicate_packet_send():port=5432
DEBUG:PGRreplicate_packet_send():pid=15363
DEBUG:PGRreplicate_packet_send():from_host=mama01
DEBUG:PGRreplicate_packet_send():dbName=test
DEBUG:PGRreplicate_packet_send():userName=postgres
DEBUG:PGRreplicate_packet_send():recieve sec=1113607043
DEBUG:PGRreplicate_packet_send():recieve usec=299568
DEBUG:PGRreplicate_packet_send():query_size=36
DEBUG:PGRreplicate_packet_send():query=SET SESSION AUTHORIZATION 'postgres'
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():wait for answer
DEBUG:send_sync_data():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,111360
7043,299568,2,1) )
DEBUG:answer[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG:read_answer():QUERY DONE
DEBUG:PGRreturn_result():status of PGRreturn_result[0]
DEBUG:sem_unlock[1]
DEBUG:replicate_loop():replicate_loop selected
DEBUG:replicate_loop():replicate_loop selected
DEBUG:PGRreplicate_packet_send():cmdSts=S
DEBUG:PGRreplicate_packet_send():cmdType=O
DEBUG:PGRreplicate_packet_send():rlog=0
DEBUG:PGRreplicate_packet_send():request_id=7
DEBUG:PGRreplicate_packet_send():replicate_id=0
DEBUG:PGRreplicate_packet_send():port=5432
DEBUG:PGRreplicate_packet_send():pid=15363
DEBUG:PGRreplicate_packet_send():from_host=mama01
DEBUG:PGRreplicate_packet_send():dbName=test
DEBUG:PGRreplicate_packet_send():userName=postgres
DEBUG:PGRreplicate_packet_send():recieve sec=1113607043
DEBUG:PGRreplicate_packet_send():recieve usec=301108
DEBUG:PGRreplicate_packet_send():query_size=122
DEBUG:PGRreplicate_packet_send():query=CREATE TABLE test (
item_id serial NOT NULL,
user_id character varying(128),
career_id character varying(32)
)
DEBUG:sem_lock[1]
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():wait for answer
DEBUG:send_sync_data():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,111360
7043,301108,2,1) )
NOTICE: CREATE TABLE will create implicit sequence "test_item_id_seq" for "ser
ial" column "test.item_id"
DEBUG:answer[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG:read_answer():QUERY DONE
DEBUG:PGRreturn_result():status of PGRreturn_result[0]
DEBUG:sem_unlock[1]
DEBUG:replicate_loop():replicate_loop selected
DEBUG:PGRreplicate_packet_send():cmdSts=S
DEBUG:PGRreplicate_packet_send():cmdType=O
DEBUG:PGRreplicate_packet_send():rlog=0
DEBUG:PGRreplicate_packet_send():request_id=8
DEBUG:PGRreplicate_packet_send():replicate_id=0
DEBUG:PGRreplicate_packet_send():port=5432
DEBUG:PGRreplicate_packet_send():pid=15363
DEBUG:PGRreplicate_packet_send():from_host=mama01
DEBUG:PGRreplicate_packet_send():dbName=test
DEBUG:PGRreplicate_packet_send():userName=postgres
DEBUG:PGRreplicate_packet_send():recieve sec=1113607043
DEBUG:PGRreplicate_packet_send():recieve usec=320750
DEBUG:PGRreplicate_packet_send():query_size=98
DEBUG:PGRreplicate_packet_send():query=CREATE SEQUENCE id
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():wait for answer
DEBUG:send_sync_data():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,111360
7043,320750,2,1) )
DEBUG:answer[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG:read_answer():QUERY DONE
DEBUG:PGRreturn_result():status of PGRreturn_result[0]
DEBUG:sem_unlock[1]
DEBUG:replicate_loop():replicate_loop selected
DEBUG:PGRreplicate_packet_send():cmdSts=S
DEBUG:PGRreplicate_packet_send():cmdType=C
DEBUG:PGRreplicate_packet_send():rlog=0
DEBUG:PGRreplicate_packet_send():request_id=9
DEBUG:PGRreplicate_packet_send():replicate_id=0
DEBUG:PGRreplicate_packet_send():port=5432
DEBUG:PGRreplicate_packet_send():pid=15363
DEBUG:PGRreplicate_packet_send():from_host=mama01
DEBUG:PGRreplicate_packet_send():dbName=test
DEBUG:PGRreplicate_packet_send():userName=postgres
DEBUG:PGRreplicate_packet_send():recieve sec=1113607043
DEBUG:PGRreplicate_packet_send():recieve usec=345407
DEBUG:PGRreplicate_packet_send():query_size=50
DEBUG:PGRreplicate_packet_send():query=COPY test (item_id, user_id, career_id)
FROM stdin
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[3,1113607043,345407,3,1]
DEBUG:PGRreturn_result():status of PGRreturn_result[0]
DEBUG:send_sync_data():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,111360
7043,345407,3,1) )
DEBUG:sem_unlock[1]
DEBUG:replicate_loop():replicate_loop selected
DEBUG:PGRreplicate_packet_send():cmdSts=S
DEBUG:PGRreplicate_packet_send():cmdType=e
DEBUG:PGRreplicate_packet_send():rlog=0
DEBUG:PGRreplicate_packet_send():request_id=10
DEBUG:PGRreplicate_packet_send():replicate_id=0
DEBUG:PGRreplicate_packet_send():port=5432
DEBUG:PGRreplicate_packet_send():pid=15363
DEBUG:PGRreplicate_packet_send():from_host=mama01
DEBUG:PGRreplicate_packet_send():dbName=test
DEBUG:PGRreplicate_packet_send():userName=postgres
DEBUG:PGRreplicate_packet_send():recieve sec=1113607043
DEBUG:PGRreplicate_packet_send():recieve usec=347043
DEBUG:PGRreplicate_packet_send():query_size=36
DEBUG:PGRreplicate_packet_send():query=1 aaa aaaa
2 bbb bbbb
3 ccc cccc
\.
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():status of PGRreturn_result[0]
DEBUG:send_sync_data():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1113607043,347043,3,1) )
DEBUG:replicate_loop():replicate_loop selected
DEBUG:replicate_loop(): PGRread_packet failed query[(null)] cmdSys[]
DEBUG:replicate_loop():session closed
DEBUG:replicate_loop():replicate loop exit
----------------------------
レストア後のcluster01のログ
----------------------------
NOTICE: CREATE TABLE will create implicit sequence "test_item_id_seq" for "seial" column "test.item_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" fortable "test"
ERROR: column reference "oid" is ambiguous
ERROR: column reference "oid" is ambiguous
ERROR: column "oid" does not exist
ERROR: column reference "oid" is ambiguous
ERROR: column reference "oid" is ambiguous
ERROR: column "oid" does not exist
ERROR: database "test" is being accessed by other users
NOTICE: CREATE TABLE will create implicit sequence "test_item_id_seq" for "seial" column "test.item_id"
ERROR: This query is not permitted when all replication servers fell down
----------------------------
リストア後のcluster02のログ
----------------------------
NOTICE: CREATE TABLE will create implicit sequence "test_item_id_seq" for "serial" column "test.item_id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
ERROR: relation "id" does not exist
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
ERROR: current transaction is aborted, commands ignored until end of transacti on block
LOG: unexpected EOF on client connection
LOG: unexpected EOF on client connection
LOG: unexpected EOF on client connection
ERROR: database "test" does not exist
NOTICE: CREATE TABLE will create implicit sequence "test_item_id_seq" for "serial" column "test.item_id"
ERROR: COPY from stdin failed: COPY terminated by new PQexec
CONTEXT: COPY test, line 1: ""
-----------
設定ファイル
-----------
-------------------------- pgreplicate.conf --------------------------
<Cluster_Server_Info>
<Host_Name> mama01.is.mediamagic.co.jp </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7101 </Recovery_Port>
<LifeCheck_Port> 7201 </LifeCheck_Port>
</Cluster_Server_Info>
<Cluster_Server_Info>
<Host_Name> mama02.is.mediamagic.co.jp </Host_Name>
<Port> 5432 </Port>
<Recovery_Port> 7101 </Recovery_Port>
<LifeCheck_Port> 7201 </LifeCheck_Port>
</Cluster_Server_Info>
<Status_Log_File> /tmp/pgreplicate.sts </Status_Log_File>
<Error_Log_File> /tmp/pgreplicate.log </Error_Log_File>
<Replication_Port> 8001 </Replication_Port>
<Recovery_Port> 8101 </Recovery_Port>
<LifeCheck_Port> 8201 </LifeCheck_Port>
<RLOG_Port> 8301 </RLOG_Port>
<Response_Mode> normal </Response_Mode>
<Use_Replication_Log> no </Use_Replication_Log>
<Reserved_Connections> 1 </Reserved_Connections>
--------------------------------------------------------------------
---------------------------- cluster.conf ----------------------------
<Replicate_Server_Info>
<Host_Name> mama03.is.mediamagic.co.jp </Host_Name>
<Port> 8001 </Port>
<Recovery_Port> 8101 </Recovery_Port>
<LifeCheck_Port> 8201 </LifeCheck_Port>
</Replicate_Server_Info>
<Recovery_Port> 7101 </Recovery_Port>
<LifeCheck_Port> 7201 </LifeCheck_Port>
<Rsync_Path> /usr/bin/rsync </Rsync_Path>
<Rsync_Option> ssh -2 </Rsync_Option>
<When_Stand_Alone> read_only </When_Stand_Alone>
<Status_Log_File> /tmp/cluster.sts </Status_Log_File>
<Error_Log_File> /tmp/cluster.log </Error_Log_File>
--------------------------------------------------------------------
nagaya <nagaya ＠ mediamagic.co.jp>
pgcluster メーリングリストの案内