PostgreSQL/PostGIS磁盘空间不足，如何配置临时存储与限制？

首先明确：你遇到的报错是因为新表hgb默认存储在根分区的PostgreSQL默认表空间中，而根分区剩余空间不足。你设置的temp_file_limit是控制查询执行时临时工作文件（如排序、哈希连接溢出到磁盘的文件）的大小，和新表的永久存储无关，因此无法解决当前问题。

1. 能否将外接硬盘设为PostgreSQL的临时文件夹？ #

可以，分两种场景处理：

场景1：让新创建的hgb表存储到外接硬盘（解决当前报错的核心） #

直接在硬盘上创建PostgreSQL表空间，指定表存储位置：

• 挂载外接硬盘到目录（如/mnt/postgres_ext），确保挂载稳定
• 创建表空间目录并修改权限：

  sudo mkdir -p /mnt/postgres_ext/tablespace
  sudo chown postgres:postgres /mnt/postgres_ext/tablespace
  

• 登录PostgreSQL创建表空间：

  CREATE TABLESPACE ext_disk_ts LOCATION '/mnt/postgres_ext/tablespace';
  

• 修改查询语句，指定表空间创建hgb：

  psql -d gis -c "create table hgb tablespace ext_disk_ts as (select osm.*, h.geometry from osm_polygon osm join hautegaronne h on ST_contains(h.geometry,osm.way));"
  

场景2：让查询过程中的临时工作文件存储到外接硬盘 #

如果后续有大量需要生成临时文件的查询，可修改临时文件存储路径：

• PostgreSQL 10及以上版本：
  1. 创建临时文件目录并改权限：

     sudo mkdir -p /mnt/postgres_ext/pg_temp
     sudo chown postgres:postgres /mnt/postgres_ext/pg_temp
     

  2. 编辑postgresql.conf添加配置：

     pg_temp_directory = '/mnt/postgres_ext/pg_temp'
     

  3. 重启PostgreSQL服务：

     sudo systemctl restart postgresql
     

• PostgreSQL 9.x及更早版本：
  1. 停止服务：

     sudo systemctl stop postgresql
     

  2. 替换默认临时目录为外接硬盘的符号链接：

     # 替换<version>为你的PostgreSQL版本，如12
     sudo mv /var/lib/postgresql/<version>/main/pgsql_tmp /var/lib/postgresql/<version>/main/pgsql_tmp_old
     sudo ln -s /mnt/postgres_ext/pg_temp /var/lib/postgresql/<version>/main/pgsql_tmp
     sudo chown postgres:postgres /var/lib/postgresql/<version>/main/pgsql_tmp
     

  3. 重启服务：

     sudo systemctl start postgresql
     

2. 能否设置临时文件大小上限？ #

可以，temp_file_limit参数就是用于控制单个会话中所有临时工作文件的总大小上限，但需注意：

• 参数默认单位为KB，你之前设置的10000000约等于9.5GB
• 可全局配置（修改postgresql.conf）或会话级配置：

  # 全局设置为10GB（10*1024*1024 KB）
  temp_file_limit = 10485760
  

  -- 会话级设置为10GB
  SET temp_file_limit = '10GB';
  

• 但再次强调：当前你的报错不是临时文件超限导致，因此这个参数无法解决当前问题。

额外优化建议 #

针对13亿条数据的关联查询，可大幅降低开销：

• 确保osm_polygon表的way字段有空间索引：

  CREATE INDEX idx_osm_polygon_way ON osm_polygon USING GIST(way);
  

• 利用空间索引提前过滤数据（PostgreSQL会自动优化，但显式写边界框判断可更直观）：

  create table hgb tablespace ext_disk_ts as 
  select osm.*, h.geometry 
  from osm_polygon osm 
  join hautegaronne h 
  on ST_contains(h.geometry, osm.way)
  where osm.way && h.geometry;
  

内容的提问来源于stack exchange，提问作者user19413311