[pgpool-general: 8477] Re: Issue in pgpool cluster configuration in AWS enviroment

Tue Nov 1 19:57:54 JST 2022

TCS Confidential

Hi Peng,

While troubleshooting the issue have found that Superuser privilege is only available for 'rdsadmin_sys' user. Below steps have been performed to troubleshoot the same:

  1.  Giving Superuser privilege to pgpool/postgres user by starting postgres service from backend and tried to run recovery script.
  2.  I have tried executing the recovery script using this user after changing the user name in pgpool.conf.

But every time the script failed with the same error.
For your reference sharing the user table from primary postgres -

[cid:56361a11-7d5f-4319-8f9a-615fa7a6b05d]

Any suggestion would really help in this matter.

Thanks & Regards
Banashree Biswas
Tata Consultancy Services Limited
Mail to: banashree.biswas at tcs.com
________________________________
From: pgpool-general <pgpool-general-bounces at pgpool.net> on behalf of Bo Peng <pengbo at sraoss.co.jp>
Sent: 27 October 2022 07:11 AM
To: Banashree Biswas <banashree.biswas at tcs.com>
Cc: T. V. Satya Rambabu Nallam <rambabu.nallamtvs at tcs.com>; J.Thomas Vijaya Singh <thomas.singh at tcs.com>; pgpool-general at pgpool.net <pgpool-general at pgpool.net>; Muthiraparambil Sajeev Rahul Saj <msrahul.saj at tcs.com>
Subject: [pgpool-general: 8473] Re: Issue in pgpool cluster configuration in AWS enviroment

"External email. Open with Caution"

Hello,

> postgres log:
>
> 2022-10-26 13:27:51.659 UTC [3301] ERROR:  must be superuser to use pgpool_recovery function

Pgpool-II run online recovery using the user specified in "recovery_user" parameter.
Please make sure the user specified in "recovery_user" has superuser privileges.

> 2022-10-26 13:27:51.659 UTC [3301] STATEMENT:  SELECT pgpool_recovery('recovery_1st_stage', 'standby node ip', '/var/lib/pgsql/12/data', '5432', 1, '5432')

It seems the second parameter 'standby node ip' is not correct.
It should be the hostname or IP address of standby node.
Have you installed pgpool-II-pg12-extensions and run "CREATE EXTENSION pgpool_recovery"?

Run the srcipt directly, check if any error occurs.

    <path to recovery_1st_stage> <standby node IP> /var/lib/pgsql/12/data 5432 1 5432

On Wed, 26 Oct 2022 14:02:43 +0000
Banashree Biswas <banashree.biswas at tcs.com> wrote:

> TCS Confidential
>
> Hi Peng,
>
> Thanks for the suggestion. After allowing all traffic for the necessary ports, we have overcome the last error. But now we are facing a new issue while running pcp recovery command for starting the standby nodes.
>
> [cid:7dd0734c-15af-4928-826e-d71d8965bc7d]
>
> Please find below error snippets from logs:
>
> pgpool log :
>
> 2022-10-26 13:27:46: pid 1412: LOG:  forked new pcp worker, pid=3299 socket=8
> 2022-10-26 13:27:46: pid 3299: LOG:  starting recovering node 1
> 2022-10-26 13:27:48: pid 783: LOG:  new IPC connection received
> 2022-10-26 13:27:50: pid 3299: LOG:  executing recovery
> 2022-10-26 13:27:50: pid 3299: DETAIL:  starting recovery command: "SELECT pgpool_recovery('recovery_1st_stage', 'standby node ip', '/var/lib/pgsql/12/data', '5432', 1, '5432')"
> 2022-10-26 13:27:50: pid 3299: LOG:  executing recovery
> 2022-10-26 13:27:50: pid 3299: DETAIL:  disabling statement_timeout
> 2022-10-26 13:27:51: pid 3299: ERROR:  executing recovery, execution of command failed at "1st stage"
> 2022-10-26 13:27:51: pid 3299: DETAIL:  command:"recovery_1st_stage"
> 2022-10-26 13:27:51: pid 1412: LOG:  PCP process with pid: 3299 exit with SUCCESS.
> 2022-10-26 13:27:51: pid 1412: LOG:  PCP process with pid: 3299 exits with status 0
>
>
>
> postgres log:
>
> 2022-10-26 13:27:51.659 UTC [3301] ERROR:  must be superuser to use pgpool_recovery function
> 2022-10-26 13:27:51.659 UTC [3301] STATEMENT:  SELECT pgpool_recovery('recovery_1st_stage', 'standby node ip', '/var/lib/pgsql/12/data', '5432', 1, '5432')
>
>
> We are using default user 'postgres' to perform the activity.
> We have tried giving superuser permission to postgres user but still it didn't work. Please let us know what can be done to fix this.
>
> Thanks & Regards
> Banashree Biswas
> Tata Consultancy Services Limited
> Mail to: banashree.biswas at tcs.com
> ________________________________
> From: pgpool-general <pgpool-general-bounces at pgpool.net> on behalf of Bo Peng <pengbo at sraoss.co.jp>
> Sent: 26 October 2022 11:04 AM
> To: Banashree Biswas <banashree.biswas at tcs.com>
> Cc: J.Thomas Vijaya Singh <thomas.singh at tcs.com>; Muthiraparambil Sajeev Rahul Saj <msrahul.saj at tcs.com>; pgpool-general at pgpool.net <pgpool-general at pgpool.net>; T. V. Satya Rambabu Nallam <rambabu.nallamtvs at tcs.com>
> Subject: [pgpool-general: 8470] Re: Issue in pgpool cluster configuration in AWS enviroment
>
> "External email. Open with Caution"
>
> Hello,
>
> > I have completed the setup till starting of primary postgres and all pgpool nodes.
> > But when I am trying to start standby postgres nodes using recovery command mentioned in section "8.2.9.1.
> > Set up PostgreSQL standby server", the command execution is getting stuck and not processing.
>
> Did you specify the Elastic IP to "-h" option in pcp_recovery_node command?
>
> Because I couldn't find any logs of pcp processes,
> I think pgpool can't be connected using Elastic IP.
> Can you connect to Pgpool-II using Elastic IP?
>
>   psql -h <Elastic IP> -p 9999 -U postgres
>
> Please check your security group settings and
> make sure the traffics on 9898 and 9999 port are allowed.
>
> On Tue, 25 Oct 2022 11:49:27 +0000
> Banashree Biswas <banashree.biswas at tcs.com> wrote:
>
> > TCS Confidential
> >
> > Dear Team,
> >
> > I am new to pgpool and trying to do an HA setup of postgresql DB using pgpool.
> >
> > For this setup I have taken three RHEL 8 servers and one elastic IP. Version of postgres and pgpool is 12 and 4.2.1 respectively.
> >
> > I have completed the setup till starting of primary postgres and all pgpool nodes. But when I am trying to start standby postgres nodes using recovery command mentioned in section "8.2.9.1. Set up PostgreSQL standby server", the command execution is getting stuck and not processing.
> >
> > Command execution screenshot:
> > [cid:72501fca-6a7f-4b51-96ac-c077ac6206a6]
> >
> > Log getting printed in postgres log:
> > Password:
> > Password:
> > Password:
> > Password:
> > Password:
> > Password:
> >
> > Log getting printed in pgpool log:
> > 2022-10-25 11:42:01: pid 5677: LOG:  new IPC connection received
> > 2022-10-25 11:42:11: pid 5677: LOG:  new IPC connection received
> > 2022-10-25 11:42:22: pid 5677: LOG:  new IPC connection received
> > 2022-10-25 11:42:32: pid 5677: LOG:  new IPC connection received
> > 2022-10-25 11:42:42: pid 5677: LOG:  new IPC connection received
> > 2022-10-25 11:42:52: pid 5677: LOG:  new IPC connection received
> > 2022-10-25 11:43:02: pid 5677: LOG:  new IPC connection received
> >
> > Please suggest how to proceed with this setup.
> >
> > I have followed below links for this configuration:
> > https://www.pgpool.net/docs/42/en/html/example-aws.html
> > AWS Configuration Example<https://www.pgpool.net/docs/42/en/html/example-aws.html>
> > 8.3.4. Try it out. Start Pgpool-II on each server with "-n" switch and redirect log messages to the pgpool.log file. The log message of leader/active Pgpool-II node will show the message of Elastic IP assignment.. LOG: I am the cluster leader node. Starting escalation process LOG: escalation process started with PID:23543 LOG: watchdog: escalation started Assigning Elastic IP 35.163.178.3 to ...
> > www.pgpool.net<http://www.pgpool.net>
> > 
> > 
> > 
> > https://www.pgpool.net/docs/42/en/html/example-cluster.html
> > Pgpool-II + Watchdog Setup Example<https://www.pgpool.net/docs/42/en/html/example-cluster.html>
> > 8.2. Pgpool-II + Watchdog Setup Example. This section shows an example of streaming replication configuration using Pgpool-II.In this example, we use 3 Pgpool-II servers to manage PostgreSQL servers to create a robust cluster system and avoid the single point of failure or split brain. PostgreSQL 13 is used in this configuration example. All scripts have been tested with PostgreSQL 95 and later.
> > www.pgpool.net<http://www.pgpool.net>
> > 
> > 
> >
> > Thanks & Regards
> > Banashree Biswas
> > Tata Consultancy Services Limited
> > Mail to: banashree.biswas at tcs.com
> >
> >
> > TCS Confidential
> > =====-----=====-----=====
> > Notice: The information contained in this e-mail
> > message and/or attachments to it may contain
> > confidential or privileged information. If you are
> > not the intended recipient, any dissemination, use,
> > review, distribution, printing or copying of the
> > information contained in this e-mail message
> > and/or attachments to it are strictly prohibited. If
> > you have received this communication in error,
> > please notify us by reply e-mail or telephone and
> > immediately and permanently delete the message
> > and any attachments. Thank you
> >
> >
>
>
> --
> Bo Peng <pengbo at sraoss.co.jp>
> SRA OSS LLC
> https://www.sraoss.co.jp/
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
>
> TCS Confidential

--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
https://www.sraoss.co.jp/
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general

TCS Confidential
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20221101/50e8c868/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 7437 bytes
Desc: image.png
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20221101/50e8c868/attachment.png>