<div dir="ltr"><div><div>Both backends returns this (f):<br><br># psql -U postgres -h 10.18.1.14 -p 5444 postgres<br>psql (9.3.5)<br>Type "help" for help.<br><br>postgres=# select pg_is_in_recovery();<br> pg_is_in_recovery <br>-------------------<br> f<br>(1 row)<br><br># psql -U postgres -h 10.18.1.13 -p 5444 postgres<br>psql (9.3.5)<br>Type "help" for help.<br><br>postgres=# select pg_is_in_recovery();<br> pg_is_in_recovery <br>-------------------<br> f<br>(1 row)<br><br></div>and for command (call pcp on pgpool server port 9898):<br>pcp_recovery_node -d 30 localhost 9898 postgres password 1<br><br></div><div>i get from log this:<br>DEBUG: send: tos="R", len=46<br>DEBUG: recv: tos="r", len=21, data=AuthenticationOK<br>DEBUG: send: tos="D", len=6<br>DEBUG: recv: tos="e", len=20, data=recovery failed<br>DEBUG: command failed. reason=recovery failed<br>BackendError<br>DEBUG: send: tos="X", len=4<br><br>2014-12-03 12:31:27: pid 21555: DEBUG: received PCP packet<br>2014-12-03 12:31:27: pid 21555: DETAIL: PCP packet type of service 'M'<br>2014-12-03 12:31:27: pid 21555: DEBUG: PCP: sent md5 salt to client<br>2014-12-03 12:31:27: pid 21555: DEBUG: initializing backend status<br>2014-12-03 12:31:27: pid 21555: DEBUG: received PCP packet<br>2014-12-03 12:31:27: pid 21555: DETAIL: PCP packet type of service 'R'<br>2014-12-03 12:31:27: pid 21555: DEBUG: PCP: processing authentication request<br>2014-12-03 12:31:27: pid 21555: DETAIL: authentication OK<br>2014-12-03 12:31:27: pid 21555: DEBUG: initializing backend status<br>2014-12-03 12:31:27: pid 21555: DEBUG: received PCP packet<br>2014-12-03 12:31:27: pid 21555: DETAIL: PCP packet type of service 'O'<br>2014-12-03 12:31:27: pid 21555: DEBUG: PCP: processing recovery request<br>2014-12-03 12:31:27: pid 21555: DETAIL: start online recovery<br>2014-12-03 12:31:27: pid 21555: LOG: starting recovering node 1<br>2014-12-03 12:31:27: pid 21555: ERROR: node recovery failed, node id: 1 is alive<br>2014-12-03 12:31:27: pid 21555: DEBUG: initializing backend status<br><br></div><div>for node 0 i get info that primary not cannot be recovery. That is probably ok.<br><br></div><div>There is info about backends from pgpool.conf:<br>backend_hostname0 = '10.18.1.13'<br>backend_port0 = 5444<br>backend_weight0 = 1<br>backend_data_directory0 = '/home/postgres/data'<br>backend_flag0 = 'ALLOW_TO_FAILOVER'<br><br>backend_hostname1 = '10.18.1.14'<br>backend_port1 = 5444<br>backend_weight1 = 1<br>backend_data_directory1 = '/home/postgres/data/'<br>backend_flag1 = 'ALLOW_TO_FAILOVER'<br><br></div><div>Thanks again for helping,<br></div><div>Michal Stava<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2014-12-03 12:25 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">Ok, probably node 1 is out of sync or streaming replication is not<br>
working. To confirm you type:<br>
<br>
psql -h 10.18.1.14 -p 5444 postgres<br>
<br>
then<br>
<br>
select pg_is_in_recovery();<br>
<br>
If it returns t, it works as standby. If not, you could resync the<br>
standby by using pcp_recovery_command.<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>
> %/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>
><br>
> 2014-12-03 12:11 GMT+01:00 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>>:<br>
><br>
>> What does "show pool_nodes" show?<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>
>> > 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.<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> 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=<br>
>> "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<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> 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>
>> >><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>
>><br>
</div></div></blockquote></div><br></div>