Re: Stuck Checkpoint Process

[Nikhil Shetty <nikhil.dba04@xxxxxxxxx>]

Hi Team,

Any assist here would be helpful 

Thanks,

Nikhil

On Wed, 3 Nov 2021 at 2:47 PM, Nikhil Shetty <nikhil.dba04@xxxxxxxxx> wrote:

Hi Team,

PostgreSQL Version - 11.7

OS - RHEL 7.9

Problem:

We see that the backups were not happening on one of the instances. On further checking, we saw that the checkpoint process was not working. Last checkpoint was 7 days ago. Manual checkpoint goes into hang state.

Checkpoint Process

>   pid  |         backend_start         | wait_event_type |     wait_event      | backend_type
> -------+-------------------------------+-----------------+---------------------+--------------
>  21237 | 2021-06-29 21:27:51.806746+00 | LWLock          | replication_slot_io | checkpointer
> (1 row)

Time of latest checkpoint:            Wed 27 Oct 2021 03:45:09 PM PDT

I see it is waiting for I/O on replication slots. I see no issue on standby and replication is working fine. Need help to further debug this

Replication Slots

> dbname=# select pg_current_wal_lsn();
>  pg_current_wal_lsn
> --------------------
>  2B0/75042DE0

> dbname=# select * from pg_replication_slots ;
>    slot_name   | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn  | confirmed_flush_lsn
> ---------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+--------------+---------------------
>  repmgr_slot_5 |        | physical  |        |          | f         | t      |       4285 |      |              | 2B0/75042DE0 |
>  repmgr_slot_1 |        | physical  |        |          | f         | t      |       3402 |      |              | 2B0/75042DE0 |

Thanks and Regards,

Nikhil