未以扩展形式使用PostGIS时的升级方法求助

核心问题原因 #

你的CKAN数据库未通过PostGIS扩展管理空间对象，而是直接创建了依赖旧版本PostGIS库（postgis-2.2）的函数/类型，导致系统升级后旧库缺失，无法正常导出或迁移。

方案一：预处理关联扩展后迁移（推荐） #

步骤1：Ubuntu18环境下的预处理 #

• 全量备份数据库：

  pg_dump -Fc -U ckan_user ckan_db > ckan_backup.dump
  

• 安装匹配当前库版本的PostGIS扩展（对应postgis-2.2）：

  sudo apt install postgresql-10-postgis-2.2
  

• 进入CKAN数据库，将现有空间对象关联到PostGIS扩展：
  1. 创建PostGIS扩展（若未创建）：

     CREATE EXTENSION postgis;
     

  2. 查询未关联到扩展的PostGIS相关函数：

     SELECT proname 
     FROM pg_proc 
     WHERE probin LIKE '%postgis%' 
       AND pronamespace = 'public'::regnamespace 
       AND NOT EXISTS (
         SELECT 1 FROM pg_depend 
         WHERE objid = pg_proc.oid AND deptype = 'e'
       );
     

  3. 批量将这些函数关联到扩展（示例）：

     ALTER EXTENSION postgis ADD FUNCTION public.box2d_in(cstring);
     ALTER EXTENSION postgis ADD FUNCTION public.box2d_out(public.box2d);
     -- 重复上述命令处理所有查询到的函数
     

步骤2：升级Ubuntu到20.04 #

执行标准系统升级流程：

sudo apt update && sudo apt upgrade -y
sudo do-release-upgrade

升级过程中系统会自动安装PostgreSQL12与PostGIS3，保留旧的PostgreSQL10集群。

步骤3：迁移集群并更新PostGIS #

• 迁移PostgreSQL10集群到12：

  sudo pg_upgradecluster 10 main
  

• 进入新集群的CKAN数据库，更新PostGIS扩展：

  ALTER EXTENSION postgis UPDATE TO '3.0';
  

• 验证功能正常后，可删除旧集群：

  sudo pg_dropcluster 10 main --stop
  

方案二：文本导出修改库路径后导入 #

如果方案一的扩展关联无法完成，可直接修改导出的SQL文件：

步骤1：Ubuntu18环境下文本导出数据库 #

pg_dump -U ckan_user ckan_db > ckan_backup.sql

步骤2：修改SQL文件中的库路径 #

批量替换所有'$libdir/postgis-2.2'为Ubuntu20下PostGIS3的库路径（通常为'$libdir/postgis-3'）：

sed -i 's/\$libdir\/postgis-2.2/\$libdir\/postgis-3/g' ckan_backup.sql

步骤3：Ubuntu20环境下导入数据 #

1. 创建新的CKAN数据库并安装PostGIS3：

   CREATE DATABASE ckan_db;
   CREATE EXTENSION postgis;
   

2. 导入修改后的SQL文件：

   psql -U ckan_user ckan_db < ckan_backup.sql
   

3. 测试CKAN核心功能，确认空间操作正常。

关键注意事项 #

• 所有操作前必须完成数据库备份，避免数据丢失。
• 迁移完成后需全面测试CKAN的空间数据上传、查询、地图渲染等功能。

内容的提问来源于stack exchange，提问作者J.B.