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

[Gavin Medley]

Never mind. I got it working. I misunderstood how pgpool_remote_start was
called and that it was required.

-Gavin

On Thu, Aug 30, 2018 at 10:35 AM Gavin Medley <medley at unavco.org> wrote:

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


-- 
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/280feb1e/attachment.html>