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

[Gavin Medley]

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180829/fad2bbc5/attachment.html>