<div dir="ltr"><div><div><div>Ok, i did it.<br><br>Thanks for help, <br></div>there was probably some other problem with wal settings and connection. Now slave is in "t" when calling pg_is_in_recovery() and everything seems to be ok.<br></div><div><br></div><div>Now i have only few questions:<br></div><div>1] Can I use more slaves than one? Like backend1,backand2 and so on?<br></div><div>2] When i set loadbalancing mode to on, will pgpool server use all servers (master and slaves) for balancing read queries?<br><br></div><div>Again, <br>thanks for your help,<br>Michal Stava<br></div></div><br><br><div><div><div><div><div><div><div><div><div><div><br><div><div><div class="gmail_extra"><div class="gmail_quote">2014-12-03 12:43 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:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><span>> 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>
</span>So node 1 works as primary (or standalone PostgreSQL).<br>
<div><div><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>
> and for command (call pcp on pgpool server port 9898):<br>
> pcp_recovery_node -d 30 localhost 9898 postgres password 1<br>
><br>
> 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<br>
> 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<br>
> alive<br>
> 2014-12-03 12:31:27: pid 21555: DEBUG: initializing backend status<br>
<br>
</div></div>You need to shutdown node 1 PostgreSQL before doing pcp_recovery_node.<br>
<div><div><br>
> for node 0 i get info that primary not cannot be recovery. That is probably<br>
> ok.<br>
><br>
> 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>
> Thanks again for helping,<br>
> Michal Stava<br>
><br>
> 2014-12-03 12:25 GMT+01:00 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>>:<br>
><br>
>> 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>
>><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" target="_blank">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<br>
>> 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<br>
>> 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<br>
>> pg_hba.conf<br>
>> >> is<br>
>> >> > set to specific hosts for trust reading all databases from postgres<br>
>> 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<br>
>> 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:<br>
>> 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<br>
>> 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<br>
>> writing<br>
>> >> > transaction. DONE<br>
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing<br>
>> failed<br>
>> >> > transaction. DONE<br>
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing<br>
>> 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<br>
>> 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<br>
>> doing<br>
>> >> > extended query messaging. DONE<br>
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting<br>
>> query in<br>
>> >> > progress. DONE<br>
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG: decide where to send the<br>
>> 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<br>
>> 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<br>
>> command<br>
>> >> > success. DONE<br>
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet<br>
>> 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<br>
>> 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'<br>
>> from<br>
>> >> > frontend<br>
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting<br>
>> query in<br>
>> >> > progress. DONE<br>
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG: decide where to send the<br>
>> 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<br>
>> 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<br>
>> 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<br>
>> kind<br>
>> >> > 2014-12-03 08:51:03: pid 21153: DETAIL: parameter name:<br>
>> application_name<br>
>> >> > value: "createdb"<br>
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet<br>
>> 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<br>
>> command<br>
>> >> > success. DONE<br>
>> >> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet<br>
>> 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<br>
>> 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" target="_blank">ishii@postgresql.org</a>>:<br>
>> >> ><br>
>> >> >> > Hi,<br>
>> >> >> ><br>
>> >> >> > frstly I want to say "Good job, your technology is really good,<br>
>> 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<br>
>> pgpool2<br>
>> >> >> > instance and 2 servers like host0 and host1). Everything is ok<br>
>> 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<br>
>> 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<br>
>> $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>
>> >><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>
>><br>
</div></div></blockquote></div><br></div></div></div></div></div></div></div></div></div></div></div></div></div></div>