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

Michal Šťava stavamichal at gmail.com
Wed Dec 3 20:12:39 JST 2014 

%/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
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20141203/9d0e2160/attachment-0001.html>

More information about the pgpool-general mailing list