A012 Anti crash checks

1. Memory usage (OOM killer probability)

-- max_mem_usage
select 
(
	select 
		(select setting::bigint from pg_settings where name = 'autovacuum_max_workers')
		*
		(
			select
			case when setting::bigint = -1 then -- autovacuum_work_mem = -1, indicating that the value of maintenance_work_mem
				(select setting::bigint * 1024 from pg_settings where name = 'maintenance_work_mem')
			else
				setting::bigint * 1024
			end
			from pg_settings where name = 'autovacuum_work_mem'
		)
)
+
(
	select 
		(select setting::bigint from pg_settings where name = 'max_connections')
		*
		(select setting::bigint * 1024 from pg_settings where name = 'work_mem')		 
)
+
(select setting::bigint * 1024 * 8 from pg_settings where name = 'shared_buffers')

If max_mem_usage > total_ram then print notification about incorrect settings for memory usage and potential OOM killer triggering.

2. Limit files

Check LimitNOFILE in:

/usr/lib/systemd/system/postgresql-X.service
/etc/systemd/system/postgresql-X.service

If LimitNOFILE not found and max_files_per_process > 1000 (1000 is default) then set:

[Service]
LimitNOFILE=<max_files_per_process>

in /usr/lib/systemd/system/postgresql-X.service and in /etc/systemd/system/postgresql-X.service

systemctl daemon-reload

How to check limit and total opened files:

for pid in `pidof postmaster`; do echo "$(< /proc/$pid/cmdline)"; \
egrep 'files|Limit' /proc/$pid/limits; \
echo "Currently open files: $(ls -1 /proc/$pid/fd | wc -l)"; echo; done