[pgpool-general: 9003] can simply restart a streaming backup system.

[Mark Gordon]

I have a three node pgpool system running watchdog.
pgpool-II version 4.5.0 (hotooriboshi)
Postgres version 16
the three servers are running Rocky 9
I am using streaming backup for replication.

I used this https://www.pgpool.net/docs/45/en/html/example-cluster.html
tutorial to setup my three servers.   It is working really well.  I am just
having one issue.

If I shut down on of the standby servers, or simply shutdown pgpool and
postgres, when I restart postgres it is not getting itself back up to
date.

node_id |  hostname  | port | status | pg_status | lb_weight |  role   |
pg_role | select_cnt | load_balance_node | replication_delay |
replication_state | replication_sync_state | last_status_change

---------+------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | 10.30.0.40 | 5432 | up     | up        | 0.333333  | primary |
primary | 93681      | false             | 0                 |
      |                        | 2024-01-23 12:24:03
 1       | 10.30.0.41 | 5432 | up     | up        | 0.333333  | standby |
standby | 35143      | true              | 0                 |
      |                        | 2024-01-23 12:24:03
 2       | 10.30.0.42 | 5432 | down   | down      | 0.333333  | standby |
unknown | 2005       | false             | 0                 |
      |                        | 2024-01-24 10:45:00
(3 rows)

I then restart postgres and pgpool

node_id |  hostname  | port | status | pg_status | lb_weight |  role   |
pg_role | select_cnt | load_balance_node | replication_delay |
replication_state | replication_sync_state | last_status_change
---------+------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | 10.30.0.40 | 5432 | up     | up        | 0.333333  | primary |
primary | 93709      | false             | 0                 |
      |                        | 2024-01-23 12:24:03
 1       | 10.30.0.41 | 5432 | up     | up        | 0.333333  | standby |
standby | 35143      | true              | 0                 |
      |                        | 2024-01-23 12:24:03
 2       | 10.30.0.42 | 5432 | down   | up        | 0.333333  | standby |
standby | 2005       | false             | 0                 |
      |                        | 2024-01-24 10:45:00
(3 rows)


Here is watchdog info after restarting
10.30.0.40:9999 Linux pg0 10.30.0.40 9999 9000 4 LEADER 0 MEMBER
10.30.0.41:9999 Linux pg1 10.30.0.41 9999 9000 7 STANDBY 0 MEMBER
10.30.0.42:9999 Linux pg2 10.30.0.42 9999 9000 7 STANDBY 0 MEMBER

it looks like it is restoring log files but it never finishes... and
nothing really happening on the system.


postgres   89859       1  0 10:49 ?        00:00:00
/usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data
postgres   89860   89859  0 10:49 ?        00:00:00 postgres: logger
postgres   89861   89859  0 10:49 ?        00:00:00 postgres: checkpointer
postgres   89862   89859  0 10:49 ?        00:00:00 postgres: background
writer
postgres   89863   89859  0 10:49 ?        00:00:00 postgres: startup
recovering 0000000300000008000000A7


To fix i have to shut off postgres and run:
pcp_recovery_node -h 10.30.0.45 -p 9898 -U pgpool -n 2 -W

This gets everything going again but takes a long time of course because it
is doing a full restore.   I have used streaming backup in the past and
normally it just starts back up where it left off after restarting the
standby server.

What am I doing wrong?

THanks
Mark







-- 
*Mark Gordon*
Phoenix, AZ
mark at ordertech.com
480.285.1403

[image: OrderTech Logo]
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20240124/033c131d/attachment.htm>