CODEWITHSUNDEEP
postgresqlsystemdubuntu-18.04postgresql-11
Pruthviraj
Pruthviraj

Reputation: 1827

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

Upvotes: 8

Views: 17182

Answers (5)

Anupa Kulathunga
Anupa Kulathunga

Reputation: 31

I recently encountered a similar issue where PostgreSQL was unable to open shared memory segments, resulting in a "Permission denied" error. My specific error was:

2024-06-27 09:52:47.665 UTC | pid : 31695 | appname:  | dbname :  | FATAL:  could not open shared memory segment "/PostgreSQL.1283452299": Permission denied

2024-06-27 09:52:47.666 UTC | pid : 31695 | appname:  | dbname :  | LOG:  database system is shut down

I found a solution for my case that might help address your problem as well:

One of the potential causes for shared memory segment issues is incorrect permissions on the /dev/shm directory. Ensure that the postgres user has the necessary permissions.

ls -ld /dev/shm

The output should look like this:

drwxrwxrwt 2 root root 40 Jun 27 11:00 /dev/shm

If the permissions are not set correctly, fix them with:

chmod 1777 /dev/shm

By ensuring the correct permissions on /dev/shm, you can prevent shared memory segment issues related to permissions. This adjustment resolved(Tested with postgresql 13.9) my issue and may help address your problem as well.

Upvotes: 1

SantaXL
SantaXL

Reputation: 674

We had the same issue and it turned out that someone set postgres user's UID bigger than 1000 (which means that postgres user was no longer a system account). And, as said here:

After hours of searching and reading, I found the culprit.
It's a setting for systemd. The /etc/systemd/logind.conf contains default configuration options, with each of them commented out.
The RemoveIPC option is set to yes by default. That option tells systemd to clean up interprocess communication (IPC) for "user accounts" who aren't logged in.

================================================
This does not affect "system accounts"
================================================

Upvotes: 1

YKun and coding
YKun and coding

Reputation: 323

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

Upvotes: 1

user4862
user4862

Reputation: 81

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

Upvotes: 8

Pruthviraj
Pruthviraj

Reputation: 1827

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

Upvotes: 2

Related Questions