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!
shared_buffers + 70 * temp_buffers
- that's around 1.3 GB with your original settings. Considering the needs of the OS and other PostgreSQL processes, that was quite a lot. Decreasing them is a good idea. Still, it shouldn't lead to anything more serious thanCannot allocate memory
- there must be something else, too.