PG on Linux about Memory Overcommit
The default virtual memory behavior on Linux is not optimal for PostgreSQL. Because of the way that the kernel implements memory overcommit,
the kernel might terminate the PostgreSQL postmaster(the supervisor server process) if the memory demands of either PostgreSQL or another process cause the system to run out of virtual memory.
If this happens, you will see a kernel message that looks like this (consult your system documentation and configuration on where to look for such a message):
Out of Memory: Killed process 12345 (postgres).
This indicates that the postgres process has been terminated due to memory pressure.
Although existing database connections will continue to function normally, no new connections will be accepted.
To recover, PostgreSQL will need to be restarted.
One way to avoid this problem is to run PostgreSQL on a machine where you can be sure that other processes will not run the machine out of memory.
If memory is tight, increasing the swap space of the operating system can help avoid the problem, because the out-of-memory (OOM) killer is invoked only when physical memory and swap space are exhausted.
If PostgreSQL itself is the cause of the system running out of memory, you can avoid the problem by changing your configuration.
In some cases, it may help to lower memory-related configuration parameters, particularly shared_buffers, work_mem, and hash_mem_multiplier.
In other cases, the problem may be caused by allowing too many connections to the database server itself.
In many cases, it may be better to reduce max_connections and instead make use of external connection-pooling software.
It is possible to modify the kernel's behavior so that it will not “overcommit” memory.
Although this setting will not prevent the OOM killer1 from being invoked altogether, it will lower the chances significantly and will therefore lead to more robust system behavior.
This is done by selecting strict overcommit mode via sysctl:
sysctl -w vm.overcommit_memory=2
or placing an equivalent entry in /etc/sysctl.conf.
You might also wish to modify the related setting vm.overcommit_ratio.
For details see the kernel documentation file https://www.ker-nel.org/doc/Documentation/vm/overcommit-accounting.
Another approach, which can be used with or without altering vm.overcommit_memory, is to set the process-specific OOM score adjustment value for the postmaster process to -1000, thereby guaranteeing it will not be targeted by the OOM killer.
The simplest way to do this is to execute
echo -1000 > /proc/self/oom_score_adj
in the postmaster's startup script just before invoking the postmaster.
Note that this action must be done as root, or it will have no effect;
so a root-owned startup script is the easiest place to do it.
If you do this,you should also set these environment variables in the startup script before invoking the postmaster:
export PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
export PG_OOM_ADJUST_VALUE=0
These settings will cause postmaster child processes to run with the normal OOM score adjustment of zero, so that the OOM killer can still target them at need.
You could use some other value for PG_OOM_ADJUST_VALUE if you want the child processes to run with some other OOM score ad-justment.
(PG_OOM_ADJUST_VALUE can also be omitted, in which case it defaults to zero.)
If you do not set PG_OOM_ADJUST_FILE, the child processes will run with the same OOM score adjust-ment as the postmaster, which is unwise since the whole point is to ensure that the postmaster has a preferential setting.