Thread: Sudden spike in WAL

Hello!

I am trying to figure out why we had a sudden spike in WAL (on 12th Jun at around 6:30pm IST). The storage has not got back to its original state since then.

Please assist if there is a way we can find it out. The instance is a GCP cloud managed and PgSQL version is 13.

Please see the spike below:

Regards

Siraj

Hi,

Please check if you have any inactive replication slot.

Thanks,Mahesh.

On Sun, Jun 30, 2024, 12:22 AM Siraj G <tosiraj.g@gmail.com> wrote:

Hello!

I am trying to figure out why we had a sudden spike in WAL (on 12th Jun at around 6:30pm IST). The storage has not got back to its original state since then.

Please assist if there is a way we can find it out. The instance is a GCP cloud managed and PgSQL version is 13.

Please see the spike below:

Regards

Siraj

Hi Mahesh

Yes, I noticed an inactive replication slot.

select * From pg_replication_slots where not active;

                 slot_name                  |      plugin      | slot_type | datoid |    database    | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn  | confirmed_flush_lsn | wal_status | safe_wal_size
---------------------------------------------+------------------+-----------+--------+----------------+-----------+--------+------------+------+--------------+--------------+---------------------+------------+---------------
 pgl_marketing_prod_provider_marketin3444436 | pglogical_output | logical   |  16648 | marketing_prod | f         | f      |            |      |    244033832 | E0C/DABEE880 | E0C/DABF1870        | extended   |              
(1 row)

How do I see the timestamp it became inactive? 

On Sun, Jun 30, 2024 at 12:24 AM Mahesh mana <maheshbabumms12@gmail.com> wrote:

Hi,

Please check if you have any inactive replication slot.

Thanks,Mahesh.

On Sun, Jun 30, 2024, 12:22 AM Siraj G <tosiraj.g@gmail.com> wrote:

Hello!

I am trying to figure out why we had a sudden spike in WAL (on 12th Jun at around 6:30pm IST). The storage has not got back to its original state since then.

Please assist if there is a way we can find it out. The instance is a GCP cloud managed and PgSQL version is 13.

Please see the spike below:

Regards

Siraj

Στις 29/6/24 22:29, ο/η Mahesh mana έγραψε:

Hi,

it should from the time when your slave is down,pls check your slave .

Not sure if there is a query to check that..

select sl.*,walz.* from pg_replication_slots sl, pg_ls_waldir() walz where walz.name=pg_walfile_name(sl.confirmed_flush_lsn);

provided there was activity post the last flushed WAL file or wal switch , the above would be an estimation, of when the replication stopped working.

On Sun, Jun 30, 2024, 12:48 AM Siraj G <tosiraj.g@gmail.com> wrote:

Hi Mahesh

Yes, I noticed an inactive replication slot.

select * From pg_replication_slots where not active;

                 slot_name                  |      plugin      | slot_type | datoid |    database    | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn  | confirmed_flush_lsn | wal_status | safe_wal_size
---------------------------------------------+------------------+-----------+--------+----------------+-----------+--------+------------+------+--------------+--------------+---------------------+------------+---------------
 pgl_marketing_prod_provider_marketin3444436 | pglogical_output | logical   |  16648 | marketing_prod | f         | f      |            |      |    244033832 | E0C/DABEE880 | E0C/DABF1870        | extended   |              
(1 row)

How do I see the timestamp it became inactive? 

On Sun, Jun 30, 2024 at 12:24 AM Mahesh mana <maheshbabumms12@gmail.com> wrote:

Hi,

Please check if you have any inactive replication slot.

Thanks,Mahesh.

On Sun, Jun 30, 2024, 12:22 AM Siraj G <tosiraj.g@gmail.com> wrote:

Hello!

I am trying to figure out why we had a sudden spike in WAL (on 12th Jun at around 6:30pm IST). The storage has not got back to its original state since then.

Please assist if there is a way we can find it out. The instance is a GCP cloud managed and PgSQL version is 13.

Please see the spike below:

Regards

Siraj

-- 
Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt (as agents only)