[pgpool-general: 3338] Re: Pgpool and backup by PITR

Tatsuo Ishii ishii at postgresql.org
Wed Dec 3 20:43:06 JST 2014 

> Both backends returns this (f):
> 
> # psql -U postgres -h 10.18.1.14 -p 5444 postgres
> psql (9.3.5)
> Type "help" for help.
> 
> postgres=# select pg_is_in_recovery();
>  pg_is_in_recovery
> -------------------
>  f
> (1 row)

So node 1 works as primary (or standalone PostgreSQL).

> # psql -U postgres -h 10.18.1.13 -p 5444 postgres
> psql (9.3.5)
> Type "help" for help.
> 
> postgres=# select pg_is_in_recovery();
>  pg_is_in_recovery
> -------------------
>  f
> (1 row)
> 
> and for command (call pcp on pgpool server port 9898):
> pcp_recovery_node -d 30 localhost 9898 postgres password 1
> 
> i get from log this:
> DEBUG: send: tos="R", len=46
> DEBUG: recv: tos="r", len=21, data=AuthenticationOK
> DEBUG: send: tos="D", len=6
> DEBUG: recv: tos="e", len=20, data=recovery failed
> DEBUG: command failed. reason=recovery failed
> BackendError
> DEBUG: send: tos="X", len=4
> 
> 2014-12-03 12:31:27: pid 21555: DEBUG:  received PCP packet
> 2014-12-03 12:31:27: pid 21555: DETAIL:  PCP packet type of service 'M'
> 2014-12-03 12:31:27: pid 21555: DEBUG:  PCP: sent md5 salt to client
> 2014-12-03 12:31:27: pid 21555: DEBUG:  initializing backend status
> 2014-12-03 12:31:27: pid 21555: DEBUG:  received PCP packet
> 2014-12-03 12:31:27: pid 21555: DETAIL:  PCP packet type of service 'R'
> 2014-12-03 12:31:27: pid 21555: DEBUG:  PCP: processing authentication
> request
> 2014-12-03 12:31:27: pid 21555: DETAIL:  authentication OK
> 2014-12-03 12:31:27: pid 21555: DEBUG:  initializing backend status
> 2014-12-03 12:31:27: pid 21555: DEBUG:  received PCP packet
> 2014-12-03 12:31:27: pid 21555: DETAIL:  PCP packet type of service 'O'
> 2014-12-03 12:31:27: pid 21555: DEBUG:  PCP: processing recovery request
> 2014-12-03 12:31:27: pid 21555: DETAIL:  start online recovery
> 2014-12-03 12:31:27: pid 21555: LOG:  starting recovering node 1
> 2014-12-03 12:31:27: pid 21555: ERROR:  node recovery failed, node id: 1 is
> alive
> 2014-12-03 12:31:27: pid 21555: DEBUG:  initializing backend status

You need to shutdown node 1 PostgreSQL before doing pcp_recovery_node.

> for node 0 i get info that primary not cannot be recovery. That is probably
> ok.
> 
> There is info about backends from pgpool.conf:
> backend_hostname0 = '10.18.1.13'
> backend_port0 = 5444
> backend_weight0 = 1
> backend_data_directory0 = '/home/postgres/data'
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> 
> backend_hostname1 = '10.18.1.14'
> backend_port1 = 5444
> backend_weight1 = 1
> backend_data_directory1 = '/home/postgres/data/'
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> 
> Thanks again for helping,
> Michal Stava
> 
> 2014-12-03 12:25 GMT+01:00 Tatsuo Ishii <ishii at postgresql.org>:
> 
>> Ok, probably node 1 is out of sync or streaming replication is not
>> working. To confirm you type:
>>
>> psql -h 10.18.1.14 -p 5444 postgres
>>
>> then
>>
>> select pg_is_in_recovery();
>>
>> If it returns t, it works as standby. If not, you could resync the
>> standby by using pcp_recovery_command.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>> > %/home/postgres/config# psql -p 9999 -U postgres -h localhost
>> > psql (9.3.5)
>> > Type "help" for help.
>> >
>> > postgres=# show pool_nodes;
>> >  node_id |  hostname  | port | status | lb_weight |  role
>> > ---------+------------+------+--------+-----------+---------
>> >  0       | 10.18.1.13 | 5444 | 2      | 0.500000  | primary
>> >  1       | 10.18.1.14 | 5444 | 2      | 0.500000  | standby
>> > (2 rows)
>> >
>> > 2014-12-03 12:11 GMT+01:00 Tatsuo Ishii <ishii at postgresql.org>:
>> >
>> >> What does "show pool_nodes" show?
>> >>
>> >> Best regards,
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS, Inc. Japan
>> >> English: http://www.sraoss.co.jp/index_en.php
>> >> Japanese:http://www.sraoss.co.jp
>> >>
>> >> > Hi Tatsuo,
>> >> >
>> >> > this tutorial i tried before using replication mode. But there is one
>> >> > problem i can't figure.
>> >> >
>> >> > I have master and slave server but only master is accepting my
>> queries.
>> >> If
>> >> > i try to create new db by:
>> >> > createdb -p 9999 -U postgres -h localhost DB5
>> >> > on pgpool-II server, i get info below from log but only master create
>> >> this
>> >> > DB5. If i try to connect by pgpool-II to this new db, i get another
>> info
>> >> > below.
>> >> >
>> >> > Is there anything i am doing wrong?
>> >> >
>> >> > PS:
>> >> > both are hot_standby and use configuration from manual, only
>> pg_hba.conf
>> >> is
>> >> > set to specific hosts for trust reading all databases from postgres
>> and
>> >> > also trust replication
>> >> >
>> >> > Thanks,
>> >> > Michal Stava
>> >> >
>> >> > LOG when connecting new DB:
>> >> > 2014-12-03 08:55:28: pid 21237: LOG:  pool_read_kind: error message
>> from
>> >> > 1 th backend:database "DB5" does not exist
>> >> > 2014-12-03 08:55:28: pid 21237: ERROR:  unable to read message kind
>> >> > 2014-12-03 08:55:28: pid 21237: DETAIL:  kind does not match between
>> >> > master(53) slot[1] (45)
>> >> >
>> >> > LOG when creating new DB:
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  I am 21153 accept fd 11
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading startup packet
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  Protocol Major: 1234 Minor:
>> 5679
>> >> > database:  user:
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  selecting backend connection
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  SSLRequest from client
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  SSL is requested but SSL
>> support
>> >> is
>> >> > not available
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading startup packet
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  application_name: createdb
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading startup packet
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  Protocol Major: 3 Minor: 0
>> >> > database: postgres user: postgres
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  sending backend key data
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  send pid 15378 to frontend
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  do_command: Query:"SET
>> >> > application_name TO 'createdb'"
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  waiting for query response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  waiting for backend:0 to
>> >> complete
>> >> > the query
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  detect error: kind: S
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  do_command: kind: 'S'
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  do_command: kind: 'C'
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  do_command: kind: 'Z'
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  do_command: Query:"SET
>> >> > application_name TO 'createdb'"
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  waiting for query response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  waiting for backend:1 to
>> >> complete
>> >> > the query
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  detect error: kind: S
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  do_command: kind: 'S'
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  do_command: kind: 'C'
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  do_command: kind: 'Z'
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: clearing
>> >> > transaction isolation. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: clearing
>> writing
>> >> > transaction. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: clearing
>> failed
>> >> > transaction. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: clearing
>> failed
>> >> > transaction. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: clearing skip
>> >> > reading from backends. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: clearing
>> ignore
>> >> > till sync. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  processing frontend response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  received kind 'Q'(51) from
>> >> frontend
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: clearing
>> doing
>> >> > extended query messaging. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: setting
>> query in
>> >> > progress. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  decide where to send the
>> queries
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  destination = 0 for query=
>> >> "CREATE
>> >> > DATABASE "DB5";
>> >> >     "
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  waiting for query response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  waiting for backend:0 to
>> >> complete
>> >> > the query
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  detect error: kind: C
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  detect error: kind: C
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  detect error: kind: C
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  detect error: kind: C
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading backend data packet
>> kind
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  backend:0 of 2 kind = 'C'
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  processing backend response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  received kind 'C'(43) from
>> >> backend
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: setting
>> command
>> >> > success. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading backend data packet
>> kind
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  backend:0 of 2 kind = 'Z'
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  processing backend response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  received kind 'Z'(5a) from
>> >> backend
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  processing backend response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  Ready For Query received
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading message length
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  slot: 0 length: 5
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  processing ReadyForQuery
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  transaction state 'I'(49)
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  processing frontend response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  received kind 'X'(58) from
>> >> frontend
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: clearing
>> doing
>> >> > extended query messaging. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: LOG:  Frontend terminated
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  received message kind 'X'
>> from
>> >> > frontend
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: setting
>> query in
>> >> > progress. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  decide where to send the
>> queries
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  destination = 3 for query= "
>> >> > DISCARD ALL"
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  waiting for query response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  waiting for backend:0 to
>> >> complete
>> >> > the query
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  detect error: kind: S
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  detect error: kind: S
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  detect error: kind: S
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  detect error: kind: S
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading backend data packet
>> kind
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  parameter name: is_superuser
>> >> > value: "on"
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading backend data packet
>> kind
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  parameter name:
>> >> > session_authorization value: "postgres"
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading backend data packet
>> kind
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  parameter name:
>> application_name
>> >> > value: "createdb"
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading backend data packet
>> kind
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  backend:0 of 2 kind = 'C'
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  processing backend response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  received kind 'C'(43) from
>> >> backend
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  session context: setting
>> command
>> >> > success. DONE
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading backend data packet
>> kind
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  backend:0 of 2 kind = 'Z'
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  processing backend response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  received kind 'Z'(5a) from
>> >> backend
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  processing backend response
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  Ready For Query received
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  reading message length
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  slot: 0 length: 5
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  processing ReadyForQuery
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  transaction state 'I'(49)
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG:  setting backend connection
>> close
>> >> > timer
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL:  close time 1417593063
>> >> >
>> >> > 2014-12-03 0:05 GMT+01:00 Tatsuo Ishii <ishii at postgresql.org>:
>> >> >
>> >> >> > Hi,
>> >> >> >
>> >> >> > frstly I want to say "Good job, your technology is really good,
>> thank
>> >> >> you."
>> >> >> > and secondly "Sorry for my bad english, I do my best."
>> >> >> >
>> >> >> > Ok, so what's my problem:
>> >> >> >
>> >> >> > I created replication model for my application (1 server with
>> pgpool2
>> >> >> > instance and 2 servers like host0 and host1). Everything is ok
>> except
>> >> >> > online_recovery part which I really have problem to understand.
>> >> >> >
>> >> >> > For now I have 3 empty scripts so adding node works, but there is
>> no
>> >> >> > sychronization of DBs:
>> >> >> > basebackup
>> >> >> > pgpool_recovery_pitr
>> >> >> > pgpool_remote_start
>> >> >> >
>> >> >> >
>> >> >> > There is a sample of basebackup script:
>> >> >> >
>> >> >> > #! /bin/sh
>> >> >> > DATA=$1
>> >> >> > RECOVERY_TARGET=$2
>> >> >> > RECOVERY_DATA=$3
>> >> >> > psql -c "select pg_start_backup('pgpool-recovery')"
>> >> >> > postgres echo "restore_command = 'scp
>> $HOSTNAME:/data/archive_log/%f
>> >> >> > %p'" > /data/recovery.conf
>> >> >> > tar -C /data -zcf pgsql.tar.gz pgsql
>> >> >> > psql -c 'select pg_stop_backup()' postgres
>> >> >> > scp pgsql.tar.gz $RECOVERY_TARGET:$RECOVERY_DATA
>> >> >> >
>> >> >> > I really need some advices there, because can't understand it.
>> >> >> >
>> >> >> > 1] there is something like /data/ so its $DATA or its other data?
>> >> >> > 2] there is tar on pgsql in data directory, but i have no pgsql
>> >> directory
>> >> >> > there
>> >> >> > 3] do you have some working example? not only common sample?
>> >> >> > 4] very similar problem I have with other two scripts
>> >> >> >
>> >> >> > Last 2 days I spent many hours by trying to understand this online
>> >> >> recovery
>> >> >> > mechanism, but
>> >> >> > my karma is probably too low.
>> >> >> >
>> >> >> > Thank you for any advice,
>> >> >> > Michal Stava,
>> >> >> > CZECH REPUBLIC
>> >> >>
>> >> >> I recommend to start with this tutorial:
>> >> >>
>> >> >>
>> >> >>
>> >>
>> http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/index.html
>> >> >>
>> >> >> Best regards,
>> >> >> --
>> >> >> Tatsuo Ishii
>> >> >> SRA OSS, Inc. Japan
>> >> >> English: http://www.sraoss.co.jp/index_en.php
>> >> >> Japanese:http://www.sraoss.co.jp
>> >> >>
>> >>
>>

More information about the pgpool-general mailing list