0

On PostgreSQL v15 on Red Hat 9 today our database has crashed. Database has crashed because there was disk full in pg_wal directory. We have pg_wal directory on separate disk from the rest of database. We have currently 100 GB assigned as size of pg_wal disk, most of the time 50% of disk space on this disk is used, but today something has eaten the rest of 50 GB of WAL.

I assume there was some (or more) application that consumed all of the WAL disk in pg_wal directory without creating commit between SQL-s. Maybe some run away application.

I can see applications currently executing in pg_stat_activity. How to get current WAL file consumption by application? I assume to join pg_stat_activity with some other table/view?

Improve this question

2 Answers 2

Reset to default
0

You can use pg_waldump to see what kinds of records are in the WAL files from that time period. This tool is not at all user friendly, I always dread using it. It won't specifically identify a session, but if all the splurge of records are of the same theme, it could tell you what kind of thing the session was doing and therefor maybe what session it was.

Did you look in the server's log file for anything which seemed out of the ordinary? Messages about replication would be suspect. Also, messages about/from the checkpointer. If you don't have log_checkpoints set to on, I would turn it on now, so that you will have more info available for future incidents.

It seems like you got the system running again, but you didn't describe how you did it. Your options for investigation after the fact will depend on that. Did you save a copy of the crashed system, so that it is available for investigation?

Improve this answer
1
  • I have checked server log file. Last two hours no messages except of LOG: automatic vacuum of table then after last "autovacuum" message is PANIC: could not write to file "pg_wal/xlogtemp.3780092": No space left on device After PG server crashed, from log files I see it auto-started, did crash recovery and everything was up and running. No manual intervention at all. I don't know how PG works. It looks to me when starting it deletes all of unneeded WAL files and then starts crash recovery. Also replica is up to date without intervention.
    – folow
    Commented Sep 5 at 15:34
0

I wonder how you can select from pg_stat_activity when your database is crashed...

Your problem is probably not that WAL is produced, but that it is not deleted. If your pg_wal directory keeps growing, something prevents PostgreSQL from deleting old WAL segments:

  • WAL archiving is enabled, but the archiver is failing (check pg_stat_archiver)

  • it could also be that the archiver is working, but it is too slow to keep up with the rate at which your application generates WAL — in that case, the solution is to speed up the archiver

  • there is a replication slot that is not moving forward, because the standby is down (check pg_replication_slots)

  • wal_keep_size is set too high

See this article for detailed information.

To answer the actual question: you won't be able to find out which session wrote how much WAL; these statistics are not tracked.

What you can find out (from PostgreSQL v13 on) is WAL production per database statement. For that, you have to enable pg_stat_statements. The the pg_stat_statements view will show you the number of wal_bytes for each statement. That should go a long way.

Improve this answer
6
  • Parameters on master: archive_mode=off, max_wal_size = 30 GB, wal_keep_size = 50 GB, wal_level = replica. From system monitoring tool I can see disk consumption on pg_wal is very stable and then suddenly started to shrink and in about 30 minutes disk got full. Replication is working fine, no lags at all in replica (max 2 sec). In IBM Db2 I can see how much transaction log is consumed by database connection with SQL. When I see one database connection is consuming too much transaction log I can kill database connection. I would like to monitor which database connection/SQL is consuming WAL file
    – folow
    Commented Sep 5 at 6:31
  • Well, then a replication slot must be at fault.
    – Laurenz Albe
    Commented Sep 5 at 6:53
  • On primary I also have max_slot_wal_keep_size=50 GB. I assume if there was some problem with replica, then replica would be broken now, because it would stay too far away. I somehow suspect we have some run-away application or some run-away job (like vacuum) that eats whole WAL. But... is there a way I can track how much active log consumption is taken by individual database connection? I know now "postmortem" this is not easy, but I am looking into future, to monitor when something like this (pg_wal to get filled up) starts to happen.
    – folow
    Commented Sep 5 at 11:47
  • WAL only gets deleted at the end of checkpoints, not immediately upon becoming (otherwise) eligible for deletion. So another possibility is that the checkpointer is just failing to get around to it in time to prevent disk from getting full.
    – jjanes
    Commented Sep 5 at 14:10
  • I see answer was updated with pg_stat_statements. I already have this extension installed. I did select sum(wal_bytes) / sum(calls) as cals, query from pg_stat_statements group by query order by 1 desc limit 100; and the output for first hundred SQLs are just bulk INSERT commands that are expected in our big data database. If I understand correctly this wal_bytes by query and not by database connection. And data are from last reset statistics. Probably this is helpful if I reset statistics near the incident. My statistics are week old, so too much noise in there.
    – folow
    Commented Sep 5 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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