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

[Gavin Medley]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20180829/6be8b9b6/attachment.htm>