[pgpool-general: 7406] Re: All nodes are the standby

[Luís Alves]

Thanks Bo, that help me a lot.

I have a similar setup as described in
https://www.pgpool.net/docs/42/en/html/example-cluster.html (only with 2
node), but seems that the pgpool master can only talk with the postgres
that is on the same machine. So if MASTER is not in the writing node I
cannot write.
I think it is because the machines now have more than one network interface
and probably something is not binding correctly.

Here is some info:

pgpool-II successfully started. version 4.1.5 (karasukiboshi)

...

-bash-4.2$ psql -h 10.230.4.149 -p 9999 -U pgpool postgres -c "show
pool_nodes"
 node_id |         hostname         | port | status | lb_weight |  role   |
select_cnt | load_balance_node | replication_delay | replication_state |
replication_sync_state | last_status_c
hange
---------+--------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------
-------
 0       | qs-audit-trail-01.dc.me | 5432 | up     | 0.500000  | primary |
0          | false             | 0                 |                   |
                     | 2021-01-25 18
:12:05
 1       | qs-audit-trail-02.dc.me | 5432 | up     | 0.500000  | standby |
0          | true              | 0                 | streaming         |
async                  | 2021-01-25 18
:12:05
(2 rows)

and

-bash-4.2$ pcp_watchdog_info -w -h 10.230.4.149 -p 9898 -U pgpool  --verbose
Watchdog Cluster Information
Total Nodes          : 2
Remote Nodes         : 1
Quorum state         : QUORUM EXIST
Alive Remote Nodes   : 1
VIP up on local node : YES
Master Node Name     : qs-audit-trail-01.dc.me:9999 Linux qs-audit-trail-01
Master Host Name     : qs-audit-trail-01.dc.me

Watchdog Node Information
Node Name      : qs-audit-trail-01.dc.me:9999 Linux qs-audit-trail-01
Host Name      : qs-audit-trail-01.dc.me
Delegate IP    : 10.230.4.149
Pgpool port    : 9999
Watchdog port  : 9000
Node priority  : 1
Status         : 4
Status Name    : MASTER

Node Name      : qs-audit-trail-02.dc.me:9999 Linux qs-audit-trail-02
Host Name      : qs-audit-trail-02.dc.me
Delegate IP    : 10.230.4.149
Pgpool port    : 9999
Watchdog port  : 9000
Node priority  : 1
Status         : 7
Status Name    : STANDBY

pgpool.conf:

# - Backend Connection Settings -

backend_hostname0 = 'qs-audit-trail-01.dc.me'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/export/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'qs-audit-trail-01.dc.me'

backend_hostname1 = 'qs-audit-trail-02.dc.me'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/export/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'qs-audit-trail-02.dc.me'


Any hints to figure out the problem? Everything seems to be working out
except this.

Regards,
LA


On Thu, Jan 21, 2021 at 12:41 AM Bo Peng <pengbo at sraoss.co.jp> wrote:

> Hi,
>
> On Tue, 19 Jan 2021 15:02:05 +0000
> Luís Alves <luisalves00 at gmail.com> wrote:
>
> > Hi
> >
> > Mu cluster have 2 nodes and currently I'm getting:
> >
> > find_primary_node: standby node is 1
> > find_primary_node: standby node is 0
>
> It seems that Pgpool-II can't detect the primary node.
> Pgpool executes "SELECT pg_is_in_recovery()" on each backend node to find
> the primary node.
>
> > I'm trying to issue detach or promote commands but I get this:
> >
> > -bash-4.2$ pcp_detach_node -w -h 10.230.4.148 -p 9898 -U pgpool -g  -n 1
> > ERROR: connection to host "10.230.4.148" failed with error "Connection
> > refused"
> > -bash-4.2$  pcp_promote_node -w -h 10.230.4.148 -p 9898 -U pgpool -g  -n
> 0
> > ERROR: connection to host "10.230.4.148" failed with error "Connection
> > refused"
>
> The pcp commands failed.
> Please make sure the hostname "10.230.4.148" and port "9898" is specified
> correctly.
>
> > Any idea how I can mark one as primary?
> >
> > I'm running with the -D flag: /usr/bin/pgpool -f
> /etc/pgpool-II/pgpool.conf
> > -D -n
>
> Please note that "pcp_promote_node" just changes the internal status of
> Pgpool-II
> and it does not actually promote PostgreSQL standby server.
>
> You need to connect to backend directly to promote the standby
> as primary in the following steps:
>
> 1. detach node0 using "pcp_detach_node"
> 2. promote node0 using "pg_ctl promote ..."
> 3. attach node0 using "pcp_attach_node"
>
> --
> 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/20210125/a85101fd/attachment.htm>