1

My slave database has undergone the memory crash(Out of memory) and in recovery stage. I want to know the query that causes this issue.

I have checked logs I get one query just before the system goes into the recovery mode;But I want to confirm it.

I am using postgres 9.4

If any one has any idea?

shruti garg
  • 177
  • 1
  • 14

1 Answers1

1

If you set log_min_error_statement to error (the default) or lower, you will get the statement that caused the out-of-memory error in the database log.

But I assume that you got hit by the Linux OOM killer, that would cause a PostgreSQL process to be killed with signal 9, whereupon the database goes into recovery mode.

The correct solution here is to disable memory overcommit by setting vm.overcommit_ratio to 2 in /etc/sysctl.conf and activate the setting with sysctl -p (you should then also tune vm.overcommit_ratio correctly).

Then you will get an error rather than a killed process, which is easier to debug.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • If a process is killed with signal 9 by OOM;then queries are also not logged.At the same time i have one long query in my logs just before the database has gone in recovery mode.Should I consider that query as cuprit or slow query? – shruti garg Apr 22 '20 at 03:45
  • Depends. If it is logged because of `log_min_duration_statement`, no (because it is logged after it is done). If it is logged because of `log_statement`, perhaps. I would still disable the OOM killer. – Laurenz Albe Apr 22 '20 at 06:18