2

I am new to postgresql.

We had a full disk issue on PostgreSQL DB where data and WAL was set.

WAL was taking almost 65 GB. This is a lower environment and we don't need to restore to a point. Compared to Oracle, we can put this DB to no archive log mode.

I wasn't sure of the step I needed to take. In some posts, I read deleting WAL can be disastrous like the database going to an inconsistent state.

I understood that WAL is not supposed to be this big. No physical replicas are present for this DB. But when I checked

select * from pg_replication_slots;

I saw the below entry.

[local]:7432 =# select * from pg_replication_slots;
 |                          slot_name                            |    plugin     | slot_type | datoid |       database       | temporary | active | active_pid |  xmin  | catalog_xmin | restart_lsn | confirmed_flush_lsn|
 |---------------------------------------------------------------|---------------|-----------|--------|----------------------|-----------|--------|------------|--------|--------------|-------------|--------------------|
 |uat_postgresql_f_00016430_02c0d965_7608_c14a_8c3b_b1a259abd32d | test_decoding | logical   |  16430 | xxa_domain_services  | f         | f      |     [null] | [null] |     73310839 | 50/155EFB78 | 50/155F08E0        |
(1 row)

Is this something that not allowing WAL to get cleared?

Or how should I troubleshoot the reason for WAL getting bigger and bigger?

1

Yes, that is probably the reason. The replication slot marks a position in the WAL (50/155EFB78), indicating that something still needs WAL from that position on. So PostgreSQL doesn't discard any WAL older than the position.

Now this replication slot is not active, so nobody advances the position, which causes your trouble. Either restart the consumer that uses the replication slot or get rid of it:

SELECT pg_drop_replication_slot('uat_postgresql_f_00016430_02c0d965_7608_c14a_8c3b_b1a259abd32d');

But first you will have to restart PostgreSQL. For that, you have to extend the size of the file system. Don't randomly delete files, that will make your problem worse.

2
  • How we can understand more about this replication slot, I am sure this is a single physical server and no clusters. in the table it mentioned "logical". How we can find more information, who owns this, what is all replicated and who is subscribed and related information?
    – vijaymec50
    Nov 18 at 17:10
  • You cannot find this information in your database. Look in your company's documentation. If you cannot find information, delete the things. Whoever needs it will show up sooner or later. Nov 18 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.