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

Gavin Medley medley at unavco.org
Fri Aug 31 01:35:14 JST 2018


Hi Tatsuo,

Thank you very much for your reply! That makes sense about the templates. I
wasn't aware of how important they are to Postgres.

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.

Any ideas?

-Gavin

On Wed, Aug 29, 2018 at 9:31 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> 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
> >>
> >>
>


-- 
Gavin Medley
Software Engineer I

6350 Nautilus Drive
Boulder, CO 80301-5394

o: 303-381-7584
c: 505-819-1270
medley at unavco.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180830/dd5d1196/attachment-0001.html>


More information about the pgpool-general mailing list