<div dir="ltr"><div><div><div><div><div><div>Hi Tatsuo,<br><br></div>this tutorial i tried before using replication mode. But there is one problem i can't figure.<br><br></div>I have master and slave server but only master is accepting my queries. If i try to create new db by:<br>createdb -p 9999 -U postgres -h localhost DB5 <br></div>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.<br><br></div>Is there anything i am doing wrong?<br><br></div>PS:<br></div>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<br><div><div><br>Thanks,<br>Michal Stava<br><div><div><br>LOG when connecting new DB:<br>2014-12-03 08:55:28: pid 21237: LOG: pool_read_kind: error message from <br>1 th backend:database "DB5" does not exist<br>2014-12-03 08:55:28: pid 21237: ERROR: unable to read message kind<br>2014-12-03 08:55:28: pid 21237: DETAIL: kind does not match between master(53) slot[1] (45)<br><br>LOG when creating new DB:<br>2014-12-03 08:51:03: pid 21153: DEBUG: I am 21153 accept fd 11<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading startup packet<br>2014-12-03 08:51:03: pid 21153: DETAIL: Protocol Major: 1234 Minor: 5679 database: user: <br>2014-12-03 08:51:03: pid 21153: DEBUG: selecting backend connection<br>2014-12-03 08:51:03: pid 21153: DETAIL: SSLRequest from client<br>2014-12-03 08:51:03: pid 21153: DEBUG: SSL is requested but SSL support is not available<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading startup packet<br>2014-12-03 08:51:03: pid 21153: DETAIL: application_name: createdb<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading startup packet<br>2014-12-03 08:51:03: pid 21153: DETAIL: Protocol Major: 3 Minor: 0 database: postgres user: postgres<br>2014-12-03 08:51:03: pid 21153: DEBUG: sending backend key data<br>2014-12-03 08:51:03: pid 21153: DETAIL: send pid 15378 to frontend<br>2014-12-03 08:51:03: pid 21153: DEBUG: do_command: Query:"SET application_name TO 'createdb'"<br>2014-12-03 08:51:03: pid 21153: DEBUG: waiting for query response<br>2014-12-03 08:51:03: pid 21153: DETAIL: waiting for backend:0 to complete the query<br>2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S<br>2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'S'<br>2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'C'<br>2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'Z'<br>2014-12-03 08:51:03: pid 21153: DEBUG: do_command: Query:"SET application_name TO 'createdb'"<br>2014-12-03 08:51:03: pid 21153: DEBUG: waiting for query response<br>2014-12-03 08:51:03: pid 21153: DETAIL: waiting for backend:1 to complete the query<br>2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S<br>2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'S'<br>2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'C'<br>2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'Z'<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing transaction isolation. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing writing transaction. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing failed transaction. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing failed transaction. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing skip reading from backends. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing ignore till sync. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: processing frontend response<br>2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'Q'(51) from frontend<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing doing extended query messaging. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting query in progress. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: decide where to send the queries<br>2014-12-03 08:51:03: pid 21153: DETAIL: destination = 0 for query= "CREATE DATABASE "DB5";<br> "<br>2014-12-03 08:51:03: pid 21153: DEBUG: waiting for query response<br>2014-12-03 08:51:03: pid 21153: DETAIL: waiting for backend:0 to complete the query<br>2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: C<br>2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: C<br>2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: C<br>2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: C<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind<br>2014-12-03 08:51:03: pid 21153: DETAIL: backend:0 of 2 kind = 'C'<br>2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response<br>2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'C'(43) from backend<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting command success. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind<br>2014-12-03 08:51:03: pid 21153: DETAIL: backend:0 of 2 kind = 'Z'<br>2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response<br>2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'Z'(5a) from backend<br>2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response<br>2014-12-03 08:51:03: pid 21153: DETAIL: Ready For Query received<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading message length<br>2014-12-03 08:51:03: pid 21153: DETAIL: slot: 0 length: 5<br>2014-12-03 08:51:03: pid 21153: DEBUG: processing ReadyForQuery<br>2014-12-03 08:51:03: pid 21153: DETAIL: transaction state 'I'(49)<br>2014-12-03 08:51:03: pid 21153: DEBUG: processing frontend response<br>2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'X'(58) from frontend<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing doing extended query messaging. DONE<br>2014-12-03 08:51:03: pid 21153: LOG: Frontend terminated<br>2014-12-03 08:51:03: pid 21153: DETAIL: received message kind 'X' from frontend<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting query in progress. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: decide where to send the queries<br>2014-12-03 08:51:03: pid 21153: DETAIL: destination = 3 for query= " DISCARD ALL"<br>2014-12-03 08:51:03: pid 21153: DEBUG: waiting for query response<br>2014-12-03 08:51:03: pid 21153: DETAIL: waiting for backend:0 to complete the query<br>2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S<br>2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S<br>2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S<br>2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind<br>2014-12-03 08:51:03: pid 21153: DETAIL: parameter name: is_superuser value: "on"<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind<br>2014-12-03 08:51:03: pid 21153: DETAIL: parameter name: session_authorization value: "postgres"<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind<br>2014-12-03 08:51:03: pid 21153: DETAIL: parameter name: application_name value: "createdb"<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind<br>2014-12-03 08:51:03: pid 21153: DETAIL: backend:0 of 2 kind = 'C'<br>2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response<br>2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'C'(43) from backend<br>2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting command success. DONE<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind<br>2014-12-03 08:51:03: pid 21153: DETAIL: backend:0 of 2 kind = 'Z'<br>2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response<br>2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'Z'(5a) from backend<br>2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response<br>2014-12-03 08:51:03: pid 21153: DETAIL: Ready For Query received<br>2014-12-03 08:51:03: pid 21153: DEBUG: reading message length<br>2014-12-03 08:51:03: pid 21153: DETAIL: slot: 0 length: 5<br>2014-12-03 08:51:03: pid 21153: DEBUG: processing ReadyForQuery<br>2014-12-03 08:51:03: pid 21153: DETAIL: transaction state 'I'(49)<br>2014-12-03 08:51:03: pid 21153: DEBUG: setting backend connection close timer<br>2014-12-03 08:51:03: pid 21153: DETAIL: close time 1417593063<br></div></div></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">2014-12-03 0:05 GMT+01:00 Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="HOEnZb"><div class="h5">> Hi,<br>
><br>
> frstly I want to say "Good job, your technology is really good, thank you."<br>
> and secondly "Sorry for my bad english, I do my best."<br>
><br>
> Ok, so what's my problem:<br>
><br>
> I created replication model for my application (1 server with pgpool2<br>
> instance and 2 servers like host0 and host1). Everything is ok except<br>
> online_recovery part which I really have problem to understand.<br>
><br>
> For now I have 3 empty scripts so adding node works, but there is no<br>
> sychronization of DBs:<br>
> basebackup<br>
> pgpool_recovery_pitr<br>
> pgpool_remote_start<br>
><br>
><br>
> There is a sample of basebackup script:<br>
><br>
> #! /bin/sh<br>
> DATA=$1<br>
> RECOVERY_TARGET=$2<br>
> RECOVERY_DATA=$3<br>
> psql -c "select pg_start_backup('pgpool-recovery')"<br>
> postgres echo "restore_command = 'scp $HOSTNAME:/data/archive_log/%f<br>
> %p'" > /data/recovery.conf<br>
> tar -C /data -zcf pgsql.tar.gz pgsql<br>
> psql -c 'select pg_stop_backup()' postgres<br>
> scp pgsql.tar.gz $RECOVERY_TARGET:$RECOVERY_DATA<br>
><br>
> I really need some advices there, because can't understand it.<br>
><br>
> 1] there is something like /data/ so its $DATA or its other data?<br>
> 2] there is tar on pgsql in data directory, but i have no pgsql directory<br>
> there<br>
> 3] do you have some working example? not only common sample?<br>
> 4] very similar problem I have with other two scripts<br>
><br>
> Last 2 days I spent many hours by trying to understand this online recovery<br>
> mechanism, but<br>
> my karma is probably too low.<br>
><br>
> Thank you for any advice,<br>
> Michal Stava,<br>
> CZECH REPUBLIC<br>
<br>
</div></div>I recommend to start with this tutorial:<br>
<br>
<a href="http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/index.html" target="_blank">http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/index.html</a><br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
</blockquote></div><br></div>