How to recover from this?

[kevinelliott]

I got into a weird state where the previous primary (node_4) went into read-only with report_lsn state during an upgrade of the monitor on everything.

node_1    10    yes       50 10.90.31.21:5001  42: 8B3/19FF6C38         5s    read-only          1s          report_lsn          report_lsn
node_2    11    yes       50 10.90.31.22:5002  42: 8B2/D79FA9B0         5s  read-only !          9s           secondary          report_lsn
node_3    13    yes       50 10.90.31.23:5432  42: 8B3/19FF6C38         5s    read-only          1s          report_lsn          report_lsn
node_4    14    yes       50 10.90.31.24:5432  42: 8B3/1A0000A0         5s    read-only          1s          report_lsn          report_lsn

Now I'm wanting to activate node_4 as the primary again, because it has the latest LSN and thus should be considered the primary. Secondary will not recover because it has a busted checkpoint.

Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 WARN  Postgres logs from "/srv/db/node_2/log/postgresql-2023-01-11_190805.log":
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.191 UTC [56205] LOG:  starting PostgreSQL 13.9 (Ubuntu 13.9-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.191 UTC [56205] LOG:  listening on IPv4 address "0.0.0.0", port 5002
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.191 UTC [56205] LOG:  listening on IPv6 address "::", port 5002
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.193 UTC [56205] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5002"
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.200 UTC [56207] LOG:  database system was shut down in recovery at 2023-01-11 18:33:55 UTC
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.200 UTC [56207] WARNING:  specified neither primary_conninfo nor restore_command
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.200 UTC [56207] HINT:  The database server will regularly poll the pg_wal subdirectory to check for files placed there.
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.200 UTC [56207] LOG:  entering standby mode
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.201 UTC [56207] LOG:  invalid resource manager ID in primary checkpoint record
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.201 UTC [56207] PANIC:  could not locate a valid checkpoint record
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.412 UTC [56205] LOG:  startup process (PID 56207) was terminated by signal 6: Aborted
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.412 UTC [56205] LOG:  aborting startup due to startup process failure
Jan 11 19:08:15 af-db-node2 pg_autoctl[6536]: 19:08:15 6536 ERROR 2023-01-11 19:08:05.420 UTC [56205] LOG:  database system is shut down

Any thoughts here?

[kevinelliott]

I was successful in getting node_4 back into primary state by executing a drop on node_2 (i.e. pg_autoctl drop node --name node_2) and the cluster is running as expected again. I then re-added node_2 (fresh) and it is currently iniatilizing from the cluster.