<div dir="auto">Hi,<div dir="auto"><br></div><div dir="auto">Thanks for the quick reply! I think you are right, I didn't catch that. I'll try creating the extension in template1 but in the mean time, can I ask why pgpool is using the template1 database? </div><div dir="auto"><br></div><div dir="auto">I'm connecting as postgres, not specifying a database. I would expect to connect to the postgres database. Have I misconfigured pgpool? </div><div dir="auto"><br></div><div dir="auto">If not, does that mean that pgpool requires template1 to exist on all clusters? That would seem an odd requirement to me. </div><div dir="auto"><br></div><div dir="auto">Thanks, </div><div dir="auto"><br></div><div dir="auto">Gavin </div></div><br><div class="gmail_quote"><div dir="ltr">On Wed, Aug 29, 2018, 19:48 Bo Peng <<a href="mailto:pengbo@sraoss.co.jp">pengbo@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
> > [2018-08-29 16:05:16.995 UTC - template1:postgres] ERROR: function<br>
> > pgpool_recovery(unknown, unknown, unknown, unknown) does not exist at<br>
> > character 8<br>
> > [2018-08-29 16:05:16.995 UTC - template1:postgres] HINT: No function<br>
> > matches the given name and argument types. You might need to add explicit<br>
> > type casts.<br>
> > [2018-08-29 16:05:16.995 UTC - template1:postgres] STATEMENT: SELECT<br>
> > pgpool_recovery('recover.sh', '<a href="http://database-0.int.domain.org" rel="noreferrer noreferrer" target="_blank">database-0.int.domain.org</a>',<br>
> > '/var/lib/pgsql/9.6/data', '5432')<br>
<br>
The log means that pgpool_recovery function isn't installed in template1.<br>
<br>
> I know what the response is going to be: "You need to use CREATE EXTENSION<br>
> to make your pgpool_recovery extension." I've done that. <br>
<br>
Did you run CREATE EXTENSION in template1?<br>
<br>
<a href="http://www.pgpool.net/docs/latest/ja/html/install-pgpool-recovery.html" rel="noreferrer noreferrer" target="_blank">http://www.pgpool.net/docs/latest/ja/html/install-pgpool-recovery.html</a><br>
<br>
On Wed, 29 Aug 2018 12:11:48 -0600<br>
Gavin Medley <<a href="mailto:medley@unavco.org" target="_blank" rel="noreferrer">medley@unavco.org</a>> wrote:<br>
<br>
> Hi all,<br>
> <br>
> I'm setting up online recovery for a simple setup of two database that will<br>
> "flip back and forth" if a master fails. My recovery.sh script (set in<br>
> recovery_1st_stage_command) works fine when run manually on the (newly<br>
> promoted) master to recover the downed previous primary to a standby state.<br>
> However, pgPool seems to be having trouble automating the process through<br>
> pcp_recovery_node.<br>
> <br>
> When I stop my primary and try to connect, failover is initiated, followed<br>
> by online recovery. I see in the postgres logs:<br>
> <br>
> > [2018-08-29 16:05:16.995 UTC - template1:postgres] ERROR: function<br>
> > pgpool_recovery(unknown, unknown, unknown, unknown) does not exist at<br>
> > character 8<br>
> > [2018-08-29 16:05:16.995 UTC - template1:postgres] HINT: No function<br>
> > matches the given name and argument types. You might need to add explicit<br>
> > type casts.<br>
> > [2018-08-29 16:05:16.995 UTC - template1:postgres] STATEMENT: SELECT<br>
> > pgpool_recovery('recover.sh', '<a href="http://database-0.int.domain.org" rel="noreferrer noreferrer" target="_blank">database-0.int.domain.org</a>',<br>
> > '/var/lib/pgsql/9.6/data', '5432')<br>
> <br>
> This output is also there if I manually run pcp_recovery_node.<br>
> <br>
> I know what the response is going to be: "You need to use CREATE EXTENSION<br>
> to make your pgpool_recovery extension." I've done that. I'm using version<br>
> 3.7.5 for both pgPool and its extensions and I'm on CentOS7 running<br>
> Postgres 9.6 from the official repo. Here's the yum output:<br>
> <br>
> > Installed Packages<br>
> > pgpool-II-96.x86_64<br>
> > 3.7.5-1.rhel7<br>
> > @pgdg96<br>
> > pgpool-II-96-extensions.x86_64<br>
> > 3.7.5-1.rhel7<br>
> > @pgdg96<br>
> <br>
> <br>
> Here's the confusing part. If I copy and paste the STATEMENT from the<br>
> postgresql log and run it in the psql prompt, it works!<br>
> <br>
> > SELECT pgpool_recovery('recover.sh', '<a href="http://database-0.int.domain.org" rel="noreferrer noreferrer" target="_blank">database-0.int.domain.org</a>',<br>
> > '/var/lib/pgsql/9.6/data', '5432')<br>
> <br>
> returns 't' and I see my script output in the postgres logs. My new standby<br>
> comes back up as expected.<br>
> <br>
> Am I missing something? How can I debug this?<br>
> <br>
> Cheers,<br>
> <br>
> Gavin<br>
> <br>
> -- <br>
> Gavin Medley<br>
> Software Engineer I<br>
> <br>
> 6350 Nautilus Drive<br>
> Boulder, CO 80301-5394<br>
> <br>
> o: 303-381-7584<br>
> <a href="mailto:medley@unavco.org" target="_blank" rel="noreferrer">medley@unavco.org</a><br>
<br>
<br>
-- <br>
Bo Peng <<a href="mailto:pengbo@sraoss.co.jp" target="_blank" rel="noreferrer">pengbo@sraoss.co.jp</a>><br>
SRA OSS, Inc. Japan<br>
<br>
</blockquote></div>