PostgreSQL could not fork autovacuum worker process: Cannot allocate memory

Question

Running into some issues every few days with postgres crashing and entering recovery mode. The logs from postgres look like so

... Lots of this for 5-10minutes

2015-09-24 10:07:27 GMT LOG:  could not fork autovacuum worker process: Cannot allocate memory
2015-09-24 10:07:28 GMT LOG:  could not fork autovacuum worker process: Cannot allocate memory
2015-09-24 10:07:29 GMT LOG:  could not fork autovacuum worker process: Cannot allocate memory
2015-09-24 10:07:30 GMT LOG:  could not fork autovacuum worker process: Cannot allocate memory
2015-09-24 10:07:32 GMT LOG:  server process (PID 16244) was terminated by signal 9: Killed
2015-09-24 10:07:32 GMT DETAIL:  Failed process was running: SELECT 1
2015-09-24 10:07:32 GMT LOG:  terminating any other active server processes
2015-09-24 10:07:32 GMT WARNING:  terminating connection because of crash of another server process
2015-09-24 10:07:32 GMT DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2015-09-24 10:07:32 GMT HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2015-09-24 10:07:32 GMT WARNING:  terminating connection because of crash of another server process
2015-09-24 10:07:32 GMT DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.


.... for some time repeats this log:

2015-09-24 10:07:33 GMT HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2015-09-24 10:07:33 GMT WARNING:  terminating connection because of crash of another server process
2015-09-24 10:07:33 GMT DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2015-09-24 10:07:33 GMT HINT:  In a moment you should be able to reconnect to the database and repeat your command.


.... then

2015-09-24 10:07:33 GMT FATAL:  the database system is in recovery mode
2015-09-24 10:07:33 GMT FATAL:  the database system is in recovery mode
2015-09-24 10:07:33 GMT FATAL:  the database system is in recovery mode
2015-09-24 10:07:33 GMT FATAL:  the database system is in recovery mode
2015-09-24 10:07:33 GMT FATAL:  the database system is in recovery mode
2015-09-24 10:07:33 GMT FATAL:  the database system is in recovery mode
2015-09-24 10:07:33 GMT FATAL:  the database system is in recovery mode

This database is running on a 2GB ram digital ocean droplet. and my postgresql.conf looks like the following (have left commented out settings in case you need to see what the default it will be using is)

max_connections = 250
shared_buffers = 768mb
temp_buffers = 8MB

#work_mem = 1MB             # min 64kB
#maintenance_work_mem = 16MB        # min 1MB
#max_stack_depth = 2MB          # min 100kB

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms        # 0-100 milliseconds
#vacuum_cost_page_hit = 1       # 0-10000 credits
#vacuum_cost_page_miss = 10     # 0-10000 credits
#vacuum_cost_page_dirty = 20        # 0-10000 credits
#vacuum_cost_limit = 200        # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms         # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100        # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0      # 0-10.0 multipler on buffers scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1       # 1-1000. 0 disables prefetching


#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

wal_level = 'hot_standby'            # minimal, archive, or hot_standby
                    # (change requires restart)
#fsync = on             # turns forced synchronization on or off
#synchronous_commit = on        # synchronization level; on, off, or local
#wal_sync_method = fsync        # the default is the first option
                    # supported by the operating system:
                    #   open_datasync
                    #   fdatasync (default on Linux)
                    #   fsync
                    #   fsync_writethrough
                    #   open_sync
#full_page_writes = on          # recover from partial page writes
#wal_buffers = -1           # min 32kB, -1 sets based on shared_buffers
                    # (change requires restart)
#wal_writer_delay = 200ms       # 1-10000 milliseconds

#commit_delay = 0           # range 0-100000, in microseconds
#commit_siblings = 5            # range 1-1000

# - Checkpoints -

#checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min      # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s       # 0 disables

# - Archiving -

archive_mode = on     # allows archiving to be done
                # (change requires restart)
archive_command = 'cd .'       # command to use to archive a logfile segment
#archive_timeout = 0        # force a logfile segment switch after this
                # number of seconds; 0 disables


#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------

# - Master Server -

# These settings are ignored on a standby server

max_wal_senders = 1        # max number of walsender processes
                # (change requires restart)
#wal_sender_delay = 1s      # walsender cycle time, 1-10000 milliseconds
wal_keep_segments = 100      # in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0   # number of xacts by which cleanup is delayed
#replication_timeout = 60s  # in milliseconds; 0 disables
#synchronous_standby_names = '' # standby servers that provide sync rep
                # comma-separated list of application_name
                # from standby(s); '*' = all

# - Standby Servers -

# These settings are ignored on a master server

hot_standby = on          # "on" allows queries during recovery
                    # (change requires restart)
#max_standby_archive_delay = 30s    # max delay before canceling queries
                    # when reading WAL from archive;
                    # -1 allows indefinite delay
#max_standby_streaming_delay = 30s  # max delay before canceling queries
                    # when reading streaming WAL;
                    # -1 allows indefinite delay
#wal_receiver_status_interval = 10s # send replies at least this often
                    # 0 disables
#hot_standby_feedback = off     # send info from standby to prevent
                    # query conflicts

Any help much appreciated!

Answer 1

Hardware problems...

This log entry:

The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

can be a result of at least two different underlying problems. The first one is a bad executable or failing hardware - this is why I suggested moving your database to a better place (be it whatever).

You are currently on a Digital Ocean droplet, which (as I just checked) is a virtual private server. This, at least to me, does not necessarily mean that it is a separate piece of hardware - and if it is not, there are chances that other users are also affected and the problem is handled quickly by the provider. Hopefully, the configuration there excludes the possibility of foreign systems having an adverse effect on yours.

That much about clouds and shared hosting :) As you see from the comments above, the more probable root of your issue is something you can solve.

... or memory handling issues?

The second (and I think more common) reason of the error is memory pressure. If you are short on memory (which can be the case, see the calculation below), the operating system may decide to kill some process to allocate memory for some other processes. If memory overcommit is allowed for the OS, the chances for this is much higher than without it.

See what the PostgreSQL documentation has to say about this:

In Linux 2.4 and later, the default virtual memory behavior is not optimal for PostgreSQL. Because of the way that the kernel implements memory overcommit, the kernel might terminate the PostgreSQL postmaster (the master 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.

Further down, it describes how to change this. What is interesting and important that you cannot fully disallow the OOM killer - this is important for keeping the OS running as long as possible. So you set the overcommit behaviour to strict by

sysctl -w vm.overcommit_memory=2

(or editing sysctl.conf and reloading it via sysctl).

Alternatively, you can set the target score of the postmaster process to the lowest possible value, therefore making it highly unlikely to be chosen when the OOM killer looks for a victim. This should be done in a root-owned startup script - editing the already used one seems appropriate. This is what you need:

echo -1000 > /proc/self/oom_score_adj

Check the linked documentation page for further details, there are smaller details to be observed with each solution.

It's good to know that the OOM killer wakes up only when the physical memory and the swap space are both exhausted. A cheap way out is increasing the swap space - however, relying on it is usually too slow for normal database operation. Depending on your use case, it might be the solution, however.

Note that for both approaches you need root access to the OS.

An approach that may work without root access

If hardware problems can be excluded as the root cause, and you don't have root access, you can still work around the problem. This will not give a bulletproof solution, but can decrease the possibility of the issue reoccurring.

Let's quickly check how much memory you original setup uses:

max_connections = 250
shared_buffers = 768MB
temp_buffers = 8MB
# work_mem = 1MB        # a commented-out value means it is at the default - 
                        # in 9.4 it is 4MB

You have 2 GB of physical memory.

Let's count how much is used (calculating the worst-case scenario):

• shared_buffers is taken always: 768MB
• work_mem and temp_buffers are allocated per session (that is, connection), and max_connections is 250: (4MB + 8MB) * 250 = 3000MB
  Of course, it is highly unlikely that all connections use up all this space. And as you state in a comment, you don't use more than 70 connections at a time, that lowers the number to 840MB
• maintenance_work_mem and (optionally) autovacuum_work_mem can consume some more. You seem to have them at their default value, that is 64MB.

All these add up to 1672MB. What is left for everything else is 2048MB - 1672MB = 376MB. Checking how much is needed for a linux server installation, I took Ubuntu as an example. The documentation says that 192MiB should be enough for a minimal setup - this way, you could survive with your settings. Apparently, there are other processes (all consuming memory) are running there, occasionally exhausting the RAM.

To avoid it, you can lower the settings mentioned above. Depending on the database size and the typical queries, you can lower any of them. Check which one is used for what before changing the config.