postgresql - pq : could not resize shared memory segment. 设备上没有剩余空间

Question

我在仪表板上有许多面板(大约 6 个)来显示数据点图表，这些面板对 PostgreSQL 数据库的 dockerised 实例进行查询。

面板直到最近都工作正常，有些面板停止工作并报告如下错误:

pq: could not resize shared memory segment "/PostgreSQL.2058389254" to 12615680 bytes: No space left on device

知道这是为什么吗？如何解决这个问题。 Docker 容器在通过 ssh 访问的远程主机上运行。

编辑

磁盘空间:

$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-3postgres容器服务:

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'

数据库大小:

postgres=# SELECT pg_size_pretty( pg_database_size('postgres'));
 pg_size_pretty
----------------
 42 GB
(1 row)

EDIT-4

抱歉，与此问题相关的解决方法均无效，包括 this one在仪表板上，我有 5 个面板用于显示数据点。查询是相似的，只是每个查询显示不同的参数:temperature、relativeHumidity、illuminance、particles 和 O3。这是查询:

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;

区别在于 WHERE attrname=#parameterValue 语句。我修改了 postgresql.conf 文件来写入日志，但日志似乎没有提供有用的提示:这里是日志:

$ 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

有人知道如何解决这个问题吗？

Answer 1

这是因为 docker by-default将共享内存的大小限制为 64MB。

您可以在 docker run 中使用 --shm-size 选项覆盖此默认值。

docker run -itd --shm-size=1g postgres

或者在 docker-compose 中:

db:
  image: "postgres:11.3-alpine"
  shm_size: 1g

检查 this出去。更多信息 here .