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

[Bo Peng]

Hi,

On Mon, 25 Jan 2021 17:20:45 +0000
Luís Alves <luisalves00 at gmail.com> wrote:

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

You need to check the network settings so that
pgpool can connect to all backend nodes.

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


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan