'PostgreSQL 11 Shared Memory Error: could not open shared memory segment "/PostgreSQL.XXXXXXXX": No such file or directory

Shared Memory files getting deleted some time (~15 hours) in Postgres 11

2019-07-09 08:46:41 CDT [] [6723]: [1-1] user=,db=,e=58P01 ERROR:  could not open shared memory segment "/PostgreSQL.291691635": No such file or directory
2019-07-09 08:46:41 CDT [] [6722]: [1-1] user=,db=,e=58P01 ERROR:  could not open shared memory segment "/PostgreSQL.291691635": No such file or directory
2019-07-09 08:46:41 CDT [10.40.0.204(60550)] [13880]: [1-1] user=user_name,db=db_name,e=58P01 ERROR:  could not open shared memory segment "/PostgreSQL.291691635": No such file or directory
2019-07-09 08:46:41 CDT [10.40.0.204(60550)] [13880]: [2-1] user=user_name,db=db_name,e=58P01 CONTEXT:  parallel worker
2019-07-09 08:46:41 CDT [10.40.0.204(60550)] [13880]: [3-1] user=user_name,db=db_name,e=58P01 STATEMENT:  WITH overall_reviewed AS (SQL Query)

GCP VM Config

CPU: 4
RAM: 16 GB
 OS: Ubuntu 18.04.1 LTS

kernel shared memory setting shared

kernel.shmmax=8589934592
kernel.shmall=2097152

postgresql.config

max_connections = 500
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 4194kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4

During startup: no errors/warnings After ~15 hours some of the shared memory files is getting deleted, I'm doubting is there any other process deleting files in "/dev/shm" ?

Not sure what is the root cause

postgresqlsystemdubuntu-18.04postgresql-11


Solution 1:[1]

making dynamic_shared_memory_type = none in postgresql.conf did solve the issue.

Solution 2:[2]

Got the same problem on Ubuntu 18.04 and PostgreSQL 11 and after some more research i have found a solution for us. The error occured when the backup user, which ist he same user as the PG service user, logs into the system. The following Link describes that the storeage under /dev/shm where deleted when a user logs in to the system (same user). So our solution was to change the following:

/etc/systemd/logind.conf

added the Line

RemoveIPC=no

and restart the service

systemctl restart systemd-logind.service

Sources:

https://www.postgresql-archive.org/systemd-deletes-shared-memory-segment-in-dev-shm-Postgresql-NNNNNN-td5883507.html

https://superuser.com/questions/1117764/why-are-the-contents-of-dev-shm-is-being-removed-automatically

Solution 3:[3]

Met the same issue.....when I have opened two sqldeveloper with the same user account, one of them is my remote session which I completely forgot to close the session.

I was doing some aggregation operators like count(*) or max(...), and the error on both. And the error is similar:

ERROR: could not open shared memory segment "/PostgreSQL.798235387": No such file or directory Where: parallel worker

Solution? I killed the remote session.... XD And life is peaceful and happy again :D

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Pruthviraj
Solution 2 user4862
Solution 3 Ying Cherry

Related Questions