[pgcluster: 632] pg_restoreをすると、レプリケーションサーバが反応しなくなる
kazunari takahashi
kazunari.takahashi ＠ ctc-g.co.jp
2005年 1月 6日 (木) 21:20:22 JST
高橋です．
お世話になっております．
pg_restoreでデータをリストアすると、レプレケーションサーバが反応しなくなります．
CTR+Cでプロンプトを抜けた後、clusterDBにログインして更新系のクエリーを投げても反応がなくなります．
clusterDBを1台にすると正常にリストア出来ました．
以上 報告させていただきます．
---------------------------------
環境
---------------------------------
pgcluster-1.0.8a
clusterDB×2 (serverA,serverB)
pgrp×1
---------------------------------
検証方法
---------------------------------
serverAにて
test=> \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+------------------------------------------------------
id | integer | not null default nextval('public.test_id_seq'::text)
name | character varying(20) |
test=> select * from test;
id | name
----+------
1 | aaaa
2 | aaaa
3 | aaaa
4 | aaaa
(4 rows)
$ pg_dump -t test -f test.dump -F c -U test test
$ dropdb test
$ createdb --owner=test test
$ pg_restore -d test -U test test.dump
Password:
pg_restore: NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column ' test.id'
反応がないので、ここでCTR+Cでプロンプト抜ける．
$ pgsql -d test -U test
test=> begin;
ERROR: replication server should be down, transaction aborted.
---------------------------------
レプリケーションサーバのDEBUGログ(pg_resotre以降)
---------------------------------
DEBUG:PGRdo_replicate():query :: BEGIN
DEBUG:cmdSts=T
DEBUG:cmdType=B
DEBUG:port=5432
DEBUG:pid=26034
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105012571
DEBUG:recieve usec=192380
DEBUG:query_size=5
DEBUG:query=BEGIN
DEBUG:sem_lock[1]
DEBUG:PGRsend_replicate_packet_to_server():host(serverB) : port(5432)
DEBUG:pgr_createConn():PQsetdbLogin host[serverB] port[5432] db[test] user[test]
DEBUG:pgr_createConn():PQsetdbLogin ok!!
DEBUG:PGRsend_replicate_packet_to_server():connect db:test port:5432 user:test host:serverB query:BEGIN
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1105012571,192380,2) )
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :BEGIN
DEBUG:sem_lock[2]
DEBUG:sem_unlock[1]
DEBUG:PGRreturn_result():PGRreturn_result[3,1105012571,192380,2]
DEBUG:PGRreturn_result():128 send
DEBUG:read_answer():answer[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG:read_answer():QUERY DONE
DEBUG:sem_lock[3]
DEBUG:sem_unlock[2]
DEBUG:PGRreturn_result():PGRreturn_result[6]
DEBUG:PGRreturn_result():128 send
DEBUG:sem_unlock[3]
DEBUG:PGRdo_replicate():query :: SELECT version()
DEBUG:cmdSts=T
DEBUG:cmdType=S
DEBUG:port=5432
DEBUG:pid=26034
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105012571
DEBUG:recieve usec=397591
DEBUG:query_size=16
DEBUG:query=SELECT version()
DEBUG:sem_lock[1]
DEBUG:PGRsend_replicate_packet_to_server():host(serverB) : port(5432)
DEBUG:PGRsend_replicate_packet_to_server():connect db:test port:5432 user:test host:serverB query:SELECT version()
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :SELECT version()
DEBUG:sem_lock[2]
DEBUG:sem_unlock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():128 send
DEBUG:read_answer():answer[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG:read_answer():QUERY DONE
DEBUG:sem_lock[3]
DEBUG:sem_unlock[2]
DEBUG:PGRreturn_result():PGRreturn_result[6]
DEBUG:PGRreturn_result():128 send
DEBUG:sem_unlock[3]
DEBUG:PGRdo_replicate():query :: COMMIT
DEBUG:cmdSts=T
DEBUG:cmdType=E
DEBUG:port=5432
DEBUG:pid=26034
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105012571
DEBUG:recieve usec=402855
DEBUG:query_size=6
DEBUG:query=COMMIT
DEBUG:sem_lock[1]
DEBUG:PGRsend_replicate_packet_to_server():host(serverB) : port(5432)
DEBUG:PGRsend_replicate_packet_to_server():connect db:test port:5432 user:test host:serverB query:COMMIT
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :COMMIT
DEBUG:sem_lock[2]
DEBUG:sem_unlock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():128 send
DEBUG:read_answer():answer[PGR_QUERY_DONE_NOTICE_CMD]
DEBUG:read_answer():QUERY DONE
DEBUG:sem_lock[3]
DEBUG:sem_unlock[2]
DEBUG:PGRreturn_result():PGRreturn_result[6]
DEBUG:PGRreturn_result():128 send
DEBUG:sem_unlock[3]
DEBUG:PGRdo_replicate():query :: CREATE TABLE test (
id serial NOT NULL,
name character varying(20)
)
DEBUG:cmdSts=Q
DEBUG:cmdType=O
DEBUG:port=5432
DEBUG:pid=26034
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105012571
DEBUG:recieve usec=451065
DEBUG:query_size=76
DEBUG:query=CREATE TABLE test (
id serial NOT NULL,
name character varying(20)
)
DEBUG:sem_lock[1]
DEBUG:PGRsend_replicate_packet_to_server():host(serverB) : port(5432)
DEBUG:pgr_createConn():PQsetdbLogin host[serverB] port[5432] db[test] user[test]
DEBUG:pgr_createConn():PQsetdbLogin ok!!
DEBUG:PGRsend_replicate_packet_to_server():connect db:test port:5432 user:test host:serverB qu
ery:CREATE TABLE test (
id serial NOT NULL,
name character varying(20)
)
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,110
5012571,451065,2) )
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :CREATE TABLE test (
id serial NOT NULL,
name character varying(20)
)
DEBUG:sem_lock[2]
DEBUG:sem_unlock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():128 send
DEBUG:sem_lock[3]
DEBUG:sem_unlock[2]
DEBUG:PGRreturn_result():PGRreturn_result[6]
DEBUG:PGRreturn_result():128 send
DEBUG:sem_unlock[3]
DEBUG:PGRdo_replicate():query :: COPY test (id, name) FROM stdin
DEBUG:cmdSts=Q
DEBUG:cmdType=C
DEBUG:port=5432
DEBUG:pid=26034
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105012572
DEBUG:recieve usec=222666
DEBUG:query_size=31
DEBUG:query=COPY test (id, name) FROM stdin
DEBUG:sem_lock[1]
DEBUG:PGRsend_replicate_packet_to_server():host(serverB) : port(5432)
DEBUG:PGRsend_replicate_packet_to_server():connect db:test port:5432 user:test host:serverB query:COPY test (id, name) FROM stdin
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1105012572,222666,2) )
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :COPY test (id, name) FROM stdin
DEBUG:sem_lock[2]
DEBUG:sem_unlock[1]
DEBUG:PGRreturn_result():PGRreturn_result[3,1105012572,222666,2]
DEBUG:PGRreturn_result():128 send
DEBUG:sem_lock[3]
DEBUG:sem_unlock[2]
DEBUG:sem_unlock[3]
DEBUG:PGRdo_replicate():query ::
DEBUG:cmdSts=C
DEBUG:cmdType=e
DEBUG:port=5432
DEBUG:pid=26034
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105012572
DEBUG:recieve usec=247916
DEBUG:query_size=31
DEBUG:query=
DEBUG:sem_lock[1]
DEBUG:PGRsend_replicate_packet_to_server():host(serverB) : port(5432)
DEBUG:PGRsend_replicate_packet_to_server():connect db:test port:5432 user:test host:serverB query:
DEBUG:PGRdo_replicate():query ::
DEBUG:cmdSts=C
DEBUG:cmdType=e
DEBUG:port=5432
DEBUG:pid=26034
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105012662
DEBUG:recieve usec=296976
DEBUG:query_size=31
DEBUG:query=
DEBUG:sem_lock[1]
DEBUG:PGRdo_replicate():query ::
DEBUG:cmdSts=C
DEBUG:cmdType=e
DEBUG:port=5432
DEBUG:pid=26034
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105012752
DEBUG:recieve usec=347443
DEBUG:query_size=31
DEBUG:query=
DEBUG:sem_lock[1]
DEBUG:PGRdo_replicate():query ::
DEBUG:cmdSts=C
DEBUG:cmdType=e
DEBUG:port=5432
DEBUG:pid=26034
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105012842
DEBUG:recieve usec=397973
DEBUG:query_size=31
DEBUG:query=
DEBUG:sem_lock[1]
↑↑↑↑↑↑↑↑↑↑↑↑ここで反応なくなる．CTR+Cでプロンプトを抜ける
DEBUG:PGRdo_replicate():query :: begin
DEBUG:cmdSts=T
DEBUG:cmdType=B
DEBUG:port=5432
DEBUG:pid=26424
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105013664
DEBUG:recieve usec=23106
DEBUG:query_size=5
DEBUG:query=begin
DEBUG:sem_lock[1]
DEBUG:PGRdo_replicate():query ::
DEBUG:cmdSts=C
DEBUG:cmdType=e
DEBUG:port=5432
DEBUG:pid=26034
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105013742
DEBUG:recieve usec=904152
DEBUG:query_size=31
DEBUG:query=
DEBUG:sem_lock[1]
・
・繰り返しログが出力される．
・
高橋 一成 <kazunari.takahashi ＠ ctc-g.co.jp>
pgcluster メーリングリストの案内