I have on a dashboard, a number of panels (numbering around 6)to display data points chart making queries to dockerised instance of PostgreSQL database.
Panels were working fine until very recently, some stop working and report an error like this:
pq: could not resize shared memory segment "/PostgreSQL.2058389254" to 12615680 bytes: No space left on device
Any idea why this? how to work around solving this. Docker container runs on remote host accessed via ssh.
EDIT
Disk space:
$df -h
Filesystem Size Used Avail Use% Mounted on
/dev/vda1 197G 140G 48G 75% /
devtmpfs 1.4G 0 1.4G 0% /dev
tmpfs 1.4G 4.0K 1.4G 1% /dev/shm
tmpfs 1.4G 138M 1.3G 10% /run
tmpfs 1.4G 0 1.4G 0% /sys/fs/cgroup
/dev/dm-16 10G 49M 10G 1% /var/lib/docker/devicemapper/mnt/a0f3c5ab84aa06d5b2db00c4324dd6bf7141500ff4c83e23e9aba7c7268bcad4
/dev/dm-1 10G 526M 9.5G 6% /var/lib/docker/devicemapper/mnt/8623a774d736ed3dc0d2db89b7d07cae85c3d1bcafc245180eec4ffd738f93a5
shm 64M 0 64M 0% /var/lib/docker/containers/260552ebcdf2bf0961329108d3d975110f8ada0a41325f5e7dd81b8ddad9d18b/mounts/shm
/dev/dm-4 10G 266M 9.8G 3% /var/lib/docker/devicemapper/mnt/6f873e62607e7cac4c4b658c72874c787b90290f74d1159eca81af61cb467cfb
shm 64M 50M 15M 78% /var/lib/docker/containers/84c66d9fb5b6ae023d051766f4d35ced87a519a1fee68ca5c89d61ff87cf1e5a/mounts/shm
/dev/dm-2 10G 383M 9.7G 4% /var/lib/docker/devicemapper/mnt/cb3df1ae654ed78802c2e5bd7a51a1b0bdd562855a7c7803750b80b33f5c206e
shm 64M 0 64M 0% /var/lib/docker/containers/22ba2ae2b6859c24623703dcb596527d64257d2d61de53f4d88e00a8e2335211/mounts/shm
/dev/dm-3 10G 99M 9.9G 1% /var/lib/docker/devicemapper/mnt/492a19fc8f3e254c4e5cc691c3300b5fee9d1a849422673bf0c19b4b2d1db571
shm 64M 0 64M 0% /var/lib/docker/containers/39abe855a9b107d4921807332309517697f024b2d169ebc5f409436208f766d0/mounts/shm
/dev/dm-7 10G 276M 9.8G 3% /var/lib/docker/devicemapper/mnt/55c6a6c17c892d149c1cc91fbf42b98f1340ffa30a1da508e3526af7060f3ce2
shm 64M 0 64M 0% /var/lib/docker/containers/bf2e7254cd7e2c6000da61875343580ec6ff5cbf40c017a398ba7479af5720ec/mounts/shm
/dev/dm-8 10G 803M 9.3G 8% /var/lib/docker/devicemapper/mnt/4e51f48d630041316edd925f1e20d3d575fce4bf19ef39a62756b768460d1a3a
shm 64M 0 64M 0% /var/lib/docker/containers/72d4ae743de490ed580ec9265ddf8e6b90e3a9d2c69bd74050e744c8e262b342/mounts/shm
/dev/dm-6 10G 10G 20K 100% /var/lib/docker/devicemapper/mnt/3dcddaee736017082fedb0996e42b4c7b00fe7b850d9a12c81ef1399fa00dfa5
shm 64M 0 64M 0% /var/lib/docker/containers/9f2bf4e2736d5128d6c240bb10da977183676c081ee07789bee60d978222b938/mounts/shm
/dev/dm-5 10G 325M 9.7G 4% /var/lib/docker/devicemapper/mnt/65a2bf48cbbfe42f0c235493981e62b90363b4be0a2f3aa0530bbc0b5b29dbe3
shm 64M 0 64M 0% /var/lib/docker/containers/e53d5ababfdefc5c8faf65a4b2d635e2543b5a807b65a4f3cd8553b4d7ef2d06/mounts/shm
/dev/dm-9 10G 1.2G 8.9G 12% /var/lib/docker/devicemapper/mnt/3216c48346c3702a5cd2f62a4737cc39666983b8079b481ab714cdb488400b08
shm 64M 0 64M 0% /var/lib/docker/containers/5cd0774a742f54c7c4fe3d4c1307fc93c3c097a861cde5f611a0fa9b454af3dd/mounts/shm
/dev/dm-10 10G 146M 9.9G 2% /var/lib/docker/devicemapper/mnt/6a98acd1428ae670e8f1da62cb8973653c8b11d1c98a8bf8be78f59d2ddba062
shm 64M 0 64M 0% /var/lib/docker/containers/a878042353f6a605167e7f9496683701fd2889f62ba1d6c0dc39c58bc03a8209/mounts/shm
tmpfs 285M 0 285M 0% /run/user/0
EDIT-2
$df -ih
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/vda1 13M 101K 13M 1% /
devtmpfs 354K 394 353K 1% /dev
tmpfs 356K 2 356K 1% /dev/shm
tmpfs 356K 693 356K 1% /run
tmpfs 356K 16 356K 1% /sys/fs/cgroup
/dev/dm-16 10M 2.3K 10M 1% /var/lib/docker/devicemapper/mnt/a0f3c5ab84aa06d5b2db00c4324dd6bf7141500ff4c83e23e9aba7c7268bcad4
/dev/dm-1 10M 19K 10M 1% /var/lib/docker/devicemapper/mnt/8623a774d736ed3dc0d2db89b7d07cae85c3d1bcafc245180eec4ffd738f93a5
shm 356K 1 356K 1% /var/lib/docker/containers/260552ebcdf2bf0961329108d3d975110f8ada0a41325f5e7dd81b8ddad9d18b/mounts/shm
/dev/dm-4 10M 11K 10M 1% /var/lib/docker/devicemapper/mnt/6f873e62607e7cac4c4b658c72874c787b90290f74d1159eca81af61cb467cfb
shm 356K 2 356K 1% /var/lib/docker/containers/84c66d9fb5b6ae023d051766f4d35ced87a519a1fee68ca5c89d61ff87cf1e5a/mounts/shm
/dev/dm-2 10M 5.6K 10M 1% /var/lib/docker/devicemapper/mnt/cb3df1ae654ed78802c2e5bd7a51a1b0bdd562855a7c7803750b80b33f5c206e
shm 356K 1 356K 1% /var/lib/docker/containers/22ba2ae2b6859c24623703dcb596527d64257d2d61de53f4d88e00a8e2335211/mounts/shm
/dev/dm-3 10M 4.6K 10M 1% /var/lib/docker/devicemapper/mnt/492a19fc8f3e254c4e5cc691c3300b5fee9d1a849422673bf0c19b4b2d1db571
shm 356K 1 356K 1% /var/lib/docker/containers/39abe855a9b107d4921807332309517697f024b2d169ebc5f409436208f766d0/mounts/shm
/dev/dm-7 10M 7.5K 10M 1% /var/lib/docker/devicemapper/mnt/55c6a6c17c892d149c1cc91fbf42b98f1340ffa30a1da508e3526af7060f3ce2
shm 356K 1 356K 1% /var/lib/docker/containers/bf2e7254cd7e2c6000da61875343580ec6ff5cbf40c017a398ba7479af5720ec/mounts/shm
/dev/dm-8 10M 12K 10M 1% /var/lib/docker/devicemapper/mnt/4e51f48d630041316edd925f1e20d3d575fce4bf19ef39a62756b768460d1a3a
shm 356K 1 356K 1% /var/lib/docker/containers/72d4ae743de490ed580ec9265ddf8e6b90e3a9d2c69bd74050e744c8e262b342/mounts/shm
/dev/dm-6 7.9K 7.3K 623 93% /var/lib/docker/devicemapper/mnt/3dcddaee736017082fedb0996e42b4c7b00fe7b850d9a12c81ef1399fa00dfa5
shm 356K 1 356K 1% /var/lib/docker/containers/9f2bf4e2736d5128d6c240bb10da977183676c081ee07789bee60d978222b938/mounts/shm
/dev/dm-5 10M 27K 10M 1% /var/lib/docker/devicemapper/mnt/65a2bf48cbbfe42f0c235493981e62b90363b4be0a2f3aa0530bbc0b5b29dbe3
shm 356K 1 356K 1% /var/lib/docker/containers/e53d5ababfdefc5c8faf65a4b2d635e2543b5a807b65a4f3cd8553b4d7ef2d06/mounts/shm
/dev/dm-9 10M 53K 10M 1% /var/lib/docker/devicemapper/mnt/3216c48346c3702a5cd2f62a4737cc39666983b8079b481ab714cdb488400b08
shm 356K 1 356K 1% /var/lib/docker/containers/5cd0774a742f54c7c4fe3d4c1307fc93c3c097a861cde5f611a0fa9b454af3dd/mounts/shm
/dev/dm-10 10M 5.2K 10M 1% /var/lib/docker/devicemapper/mnt/6a98acd1428ae670e8f1da62cb8973653c8b11d1c98a8bf8be78f59d2ddba062
shm 356K 1 356K 1% /var/lib/docker/containers/a878042353f6a605167e7f9496683701fd2889f62ba1d6c0dc39c58bc03a8209/mounts/shm
tmpfs 356K 1 356K 1% /run/user/0
EDIT-3
postgres container service:
version: "3.5"
services:
#other containers go here..
postgres:
restart: always
image: postgres:10
hostname: postgres
container_name: fiware-postgres
expose:
- "5432"
ports:
- "5432:5432"
networks:
- default
environment:
- "POSTGRES_PASSWORD=password"
- "POSTGRES_USER=postgres"
- "POSTGRES_DB=postgres"
volumes:
- ./postgres-data:/var/lib/postgresql/data
build:
context: .
shm_size: '4gb'
Database size:
postgres=# SELECT pg_size_pretty( pg_database_size('postgres'));
pg_size_pretty
----------------
42 GB
(1 row)
EDIT-4
Sorry, but none of the workaround related to this question actually work, including this one
On the dashboard, I have 5 panels intended to display data points. The queries are similar, except that each displays different parameters for temperature, relativeHumidity, illuminance, particles and O3. This is the query:
SELECT to_timestamp(floor((extract('epoch' from recvtime)/ 1800 )) * 1800) as time,
avg(attrvalue::float) as illuminance
FROM urbansense.weather WHERE attrname='illuminance' AND attrvalue<>'null' GROUP BY time ORDER BY time asc;
The difference is in the WHERE attrname=#parameterValue statement. I modified the postgresql.conf file to write logs but the logs doesn't seem to provide helpfull tips: here goes the logs:
$ vim postgres-data/log/postgresql-2019-06-26_150012.log
.
.
2019-06-26 15:03:39.298 UTC [45] LOG: statement: SELECT to_timestamp(floor((extract('epoch' from recvtime)/ 1800 )) * 1800) as time,
avg(attrvalue::float) as o3
FROM urbansense.airquality WHERE attrname='O3' AND attrvalue<>'null' GROUP BY time ORDER BY time asc;
2019-06-26 15:03:40.903 UTC [41] ERROR: could not resize shared memory segment "/PostgreSQL.1197429420" to 12615680 bytes: No space left on device
2019-06-26 15:03:40.903 UTC [41] STATEMENT: SELECT to_timestamp(floor((extract('epoch' from recvtime)/ 1800 )) * 1800) as time,
avg(attrvalue::float) as illuminance
FROM urbansense.weather WHERE attrname='illuminance' AND attrvalue<>'null' GROUP BY time ORDER BY time asc;
2019-06-26 15:03:40.905 UTC [42] FATAL: terminating connection due to administrator command
2019-06-26 15:03:40.905 UTC [42] STATEMENT: SELECT to_timestamp(floor((extract('epoch' from recvtime)/ 1800 )) * 1800) as time,
avg(attrvalue::float) as illuminance
FROM urbansense.weather WHERE attrname='illuminance' AND attrvalue<>'null' GROUP BY time ORDER BY time asc;
2019-06-26 15:03:40.909 UTC [43] FATAL: terminating connection due to administrator command
2019-06-26 15:03:40.909 UTC [43] STATEMENT: SELECT to_timestamp(floor((extract('epoch' from recvtime)/ 1800 )) * 1800) as time,
avg(attrvalue::float) as illuminance
FROM urbansense.weather WHERE attrname='illuminance' AND attrvalue<>'null' GROUP BY time ORDER BY time asc;
2019-06-26 15:03:40.921 UTC [1] LOG: worker process: parallel worker for PID 41 (PID 42) exited with exit code 1
2019-06-26 15:03:40.922 UTC [1] LOG: worker process: parallel worker for PID 41 (PID 43) exited with exit code 1
2019-06-26 15:07:04.058 UTC [39] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp39.0", size 83402752
2019-06-26 15:07:04.058 UTC [39] STATEMENT: SELECT to_timestamp(floor((extract('epoch' from recvtime)/ 1800 )) * 1800)as time,
avg(attrvalue::float) as relativeHumidity
FROM urbansense.weather WHERE attrname='relativeHumidity' AND attrvalue<>'null' GROUP BY time ORDER BY time asc;
2019-06-26 15:07:04.076 UTC [40] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp40.0", size 83681280
2019-06-26 15:07:04.076 UTC [40] STATEMENT: SELECT to_timestamp(floor((extract('epoch' from recvtime)/ 1800 )) * 1800)as time,
avg(attrvalue::float) as relativeHumidity
FROM urbansense.weather WHERE attrname='relativeHumidity' AND attrvalue<>'null' GROUP BY time ORDER BY time asc;
2019-06-26 15:07:04.196 UTC [38] LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp38.0", size 84140032
Anyone with a idea how to solve this?
This is because docker by-default restrict size of shared memory to 64MB.
You can override this default value by using --shm-size option in docker run.
docker run -itd --shm-size=1g postgres
or in docker-compose:
db:
image: "postgres:11.3-alpine"
shm_size: 1g