<div dir="ltr">Hi Tatsuo,<div><br></div><div>Thank you very much for your reply! That makes sense about the templates. I wasn't aware of how important they are to Postgres. </div><div><br></div><div>I took Peng's good advice and created the extensions in template1. Now pcp_recovery_node works perfectly but not automatically. When I take down my primary server, the failover command executes and promotes the standby but my recovery_1st_stage_command doesn't seem to execute. In my $PGDATA directory (on both nodes) I have recovery_1st_stage script and pgpool_remote_start script. My pgpool.conf file points to my recovery_1st_stage script and I'm sure it's in the correct place because pcp_recovery_node works. I don't see anything in the pgpool logs after failover completes, which indicates to me that recovery_1st_stage_command is possibly being ignored somehow.</div><div><br></div><div>Any ideas?</div><div><br></div><div>-Gavin</div></div><br><div class="gmail_quote"><div dir="ltr">On Wed, Aug 29, 2018 at 9:31 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@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">Pgpool-II assumes that template1 always exists. Dropping template1 is<br>
not a good idea. For example, when you create a new database,<br>
template1 is used as a database template by default so that extensions<br>
can be copied into the new database.<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" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> Hi,<br>
> <br>
> Thanks for the quick reply! I think you are right, I didn't catch that.<br>
> I'll try creating the extension in template1 but in the mean time, can I<br>
> ask why pgpool is using the template1 database?<br>
> <br>
> I'm connecting as postgres, not specifying a database. I would expect to<br>
> connect to the postgres database. Have I misconfigured pgpool?<br>
> <br>
> If not, does that mean that pgpool requires template1 to exist on all<br>
> clusters? That would seem an odd requirement to me.<br>
> <br>
> Thanks,<br>
> <br>
> Gavin<br>
> <br>
> On Wed, Aug 29, 2018, 19:48 Bo Peng <<a href="mailto:pengbo@sraoss.co.jp" target="_blank">pengbo@sraoss.co.jp</a>> wrote:<br>
> <br>
>> 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<br>
>> 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" 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<br>
>> 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" 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">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<br>
>> 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<br>
>> 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,<br>
>> 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<br>
>> 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" 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<br>
>> EXTENSION<br>
>> > to make your pgpool_recovery extension." I've done that. I'm using<br>
>> 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" 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<br>
>> 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">medley@unavco.org</a><br>
>><br>
>><br>
>> --<br>
>> Bo Peng <<a href="mailto:pengbo@sraoss.co.jp" target="_blank">pengbo@sraoss.co.jp</a>><br>
>> SRA OSS, Inc. Japan<br>
>><br>
>><br>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><span style="color:rgb(102,102,102)"><font size="2"><span style="font-family:arial,helvetica,sans-serif">Gavin Medley<br></span></font></span><div><span style="color:rgb(102,102,102)"><font size="2"><span style="font-family:arial,helvetica,sans-serif">Software Engineer I</span></font></span></div><div><span style="color:rgb(102,102,102)"><font size="2"><span style="font-family:arial,helvetica,sans-serif"></span></font><br><span><img src="https://docs.google.com/uc?export=download&id=0B8yiNfO2e9cpOTJzdTl4U3JxUFE&revid=0B8yiNfO2e9cpbkNDdEdscHhZNzBJTkE2RkFWRmdVa3FTaW1NPQ" style="font-size:12.8px" width="200" height="50"></span><font size="2"><span style="font-family:arial,helvetica,sans-serif"></span></font></span></div><div><div><span style="color:rgb(102,102,102)"><font size="2"><span style="font-family:arial,helvetica,sans-serif">6350 Nautilus Drive<br></span></font></span></div><div><span style="color:rgb(102,102,102)"><font size="2"><span style="font-family:arial,helvetica,sans-serif"><span>Boulder, CO 80301-5394</span></span></font></span></div><div><span style="color:rgb(102,102,102)"><font size="2"><span style="font-family:arial,helvetica,sans-serif"><span><br></span></span></font></span></div><div><span style="color:rgb(102,102,102)"><font size="2"><span style="font-family:arial,helvetica,sans-serif"><span>o: 303-381-7584<br></span></span></font></span></div><div><span style="color:rgb(102,102,102)"><font size="2"><span style="font-family:arial,helvetica,sans-serif"><span>c: 505-819-1270</span></span></font></span></div></div><div><span style="color:rgb(0,0,0)"><font size="2"><span style="font-family:arial,helvetica,sans-serif"><span style="color:rgb(102,102,102)"><a href="mailto:medley@unavco.org" target="_blank">medley@unavco.org</a></span><br></span></font></span></div></div></div></div></div></div></div></div></div>