[pgpool-general: 3332] Re: Pgpool and backup by PITR
Michal Šťava
stavamichal at gmail.com
Wed Dec 3 17:05:08 JST 2014
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/3810196a/attachment-0001.html>
More information about the pgpool-general
mailing list