How can I get the host name and query that has been executed on the slave postgres database that caused the system into the memory crashed

Question

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?

Answer 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.