<div dir="ltr">%/home/postgres/config# psql -p 9999 -U postgres -h localhost<br>psql (9.3.5)<br>Type "help" for help.<br><br>postgres=# show pool_nodes;<br> node_id | hostname | port | status | lb_weight | role <br>---------+------------+------+--------+-----------+---------<br> 0 | 10.18.1.13 | 5444 | 2 | 0.500000 | primary<br> 1 | 10.18.1.14 | 5444 | 2 | 0.500000 | standby<br>(2 rows)<br></div><div class="gmail_extra"><br><div class="gmail_quote">2014-12-03 12:11 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">What does "show pool_nodes" show?<br>
<div class="HOEnZb"><div class="h5"><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>
<br>
> Hi Tatsuo,<br>
><br>
> this tutorial i tried before using replication mode. But there is one<br>
> problem i can't figure.<br>
><br>
> I have master and slave server but only master is accepting my queries. If<br>
> i try to create new db by:<br>
> createdb -p 9999 -U postgres -h localhost DB5<br>
> on pgpool-II server, i get info below from log but only master create this<br>
> DB5. If i try to connect by pgpool-II to this new db, i get another info<br>
> below.<br>
><br>
> Is there anything i am doing wrong?<br>
><br>
> PS:<br>
> both are hot_standby and use configuration from manual, only pg_hba.conf is<br>
> set to specific hosts for trust reading all databases from postgres and<br>
> also trust replication<br>
><br>
> Thanks,<br>
> Michal Stava<br>
><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<br>
> 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<br>
> 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<br>
> 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<br>
> 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<br>
> 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<br>
> 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<br>
> 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<br>
> 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<br>
> transaction isolation. DONE<br>
> 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing writing<br>
> transaction. DONE<br>
> 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing failed<br>
> transaction. DONE<br>
> 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing failed<br>
> transaction. DONE<br>
> 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing skip<br>
> reading from backends. DONE<br>
> 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing ignore<br>
> 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<br>
> extended query messaging. DONE<br>
> 2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting query in<br>
> 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<br>
> 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<br>
> 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<br>
> 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<br>
> 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<br>
> frontend<br>
> 2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting query in<br>
> 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= "<br>
> 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<br>
> 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<br>
> 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:<br>
> 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<br>
> 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<br>
> 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<br>
> timer<br>
> 2014-12-03 08:51:03: pid 21153: DETAIL: close time 1417593063<br>
><br>
> 2014-12-03 0:05 GMT+01:00 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>>:<br>
><br>
>> > Hi,<br>
>> ><br>
>> > frstly I want to say "Good job, your technology is really good, thank<br>
>> 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<br>
>> 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>
>> I recommend to start with this tutorial:<br>
>><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>
>><br>
</div></div></blockquote></div><br></div>