[pgpool-general: 9003] can simply restart a streaming backup system.
Mark Gordon
mark at ordertech.com
Thu Jan 25 02:54:48 JST 2024
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>
More information about the pgpool-general
mailing list