[pgpool-general: 6217] Re: Running pgpool_recovery via pcp_recovery_node Results in Confusing Failure

[Tatsuo Ishii]

Pgpool-II assumes that template1 always exists.  Dropping template1 is
not a good idea. For example, when you create a new database,
template1 is used as a database template by default so that extensions
can be copied into the new database.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

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