[pgcluster: 642] Re: pg_restoreをすると、レプリケーションサーバが反応しなくなる

[kazunari takahashi]

三谷様

高橋です．

> そうですか．．．
> こちらでも(Solaris 8は手元に無いので）Solaris 9で試してみました．
> 結果はOKでした．

再度コンパイルしてやってみたのですが、NGでした．
レプリケーションログは、まったく同じでした．
また、手動で\COPYコマンドを実行したのですが、同じ現象になりました．
三谷さんの環境で再現しないということなので、再度設定を見直したですが、大丈夫のようです．
#createdbやcreate tableが出来ているので、大丈夫だと思うのですが．．．

インストールは、tarを展開後、
postgresユーザで、./configure --prefix=/usr/local/src , make ,makeinstall
をし
---------------------------------
検証1 psql+pg_dumpでのリストア
---------------------------------
serverA% id
uid=101(postgres) gid=101(postgres)
serverA% psql -l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
 test      | test     | SQL_ASCII


serverA% psql -U test test
Welcome to psql 7.3.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=> select * From test;
 id | name
----+------
  1 | aaa
  2 | aaa
  3 | aaa
  4 | aaa
  5 | ccc
  6 | ddd
(6 rows)

serverA% pg_dump test > test.dmp
serverA% dropdb test;
DROP DATABASE
serverA% createdb --owner=test test
CREATE DATABASE
serverA%
serverA%
serverA% psql test < test.dmp
You are now connected as new user test.
SET
NOTICE:  CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
CREATE TABLE
 setval
--------
      6
(1 row)

serverA% psql -U test test
test=> select * from test;
 id | name
----+------
  1 | aaa
  2 | aaa
  3 | aaa
  4 | aaa
  5 | ccc
  6 | ddd
(6 rows)

serverB% psql -U test test
Welcome to psql 7.3.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=> select * from test;
 id | name
----+------
(0 rows)

---------------------------------
検証2 手動で\copy
---------------------------------

test=> select * from test;
 id | name
----+------
  1 | aaa
  2 | aaa
  3 | aaa
  4 | aaa
  5 | ccc
  6 | ddd
(6 rows)

test=> \copy test to '/tmp/test.dmp'
test=> DROP TABLE test;
DROP TABLE
test=> create table test (id serial,name varchar(20));
NOTICE:  CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
CREATE TABLE
test=>
test=>
test=> \copy test from '/tmp/test.dmp'
\.
test=> select * from test;
 id | name
----+------
  1 | aaa
  2 | aaa
  3 | aaa
  4 | aaa
  5 | ccc
  6 | ddd
(6 rows)

---------------------------------------
手動で\copyしたときのレプリケーションログ
---------------------------------------
DEBUG:PGRdo_replicate():query :: COPY test FROM stdin
DEBUG:cmdSts=Q
DEBUG:cmdType=C
DEBUG:port=5432
DEBUG:pid=19035
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105096139
DEBUG:recieve usec=887594
DEBUG:query_size=20
DEBUG:query=COPY test FROM stdin
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[3,1105096139,887594,2]
DEBUG:PGRreturn_result():128 send
DEBUG:sem_lock[2]
DEBUG:sem_unlock[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 FROM stdin
DEBUG:PGRsend_replicate_packet_to_server():sync_command(SELECT PGR_SYSTEM_COMMAND_FUNCTION(3,1105096139,887594,2) )
DEBUG:PGRsend_replicate_packet_to_server():PQexec send :COPY test FROM stdin
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=19035
DEBUG:from_host=serverA
DEBUG:dbName=test
DEBUG:userName=test
DEBUG:recieve sec=1105096139
DEBUG:recieve usec=913227
DEBUG:query_size=39
DEBUG:query=
DEBUG:sem_lock[1]
DEBUG:PGRreturn_result():PGRreturn_result[]
DEBUG:PGRreturn_result():128 send
DEBUG:sem_lock[2]
DEBUG:sem_unlock[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:


高橋 一成 <kazunari.takahashi ＠ ctc-g.co.jp>