SheepDog1998

博客园 首页 新随笔 联系 订阅 管理
PostgreSQL 12 + PostGIS 3.4.2 完整部署+迁移+数据恢复避坑指南

PostgreSQL 12 + PostGIS 3.4.2 完整部署+迁移+数据恢复避坑指南（新手可复制，全程无报错）

在GIS业务部署中，PostgreSQL搭配PostGIS是空间数据存储的常用组合，而实际场景中，常需完成“环境部署+数据库迁移+数据恢复”全流程操作，版本兼容、密码配置、扩展安装、迁移权限等环节很容易踩坑。本文整合一次完整的实操过程，从基础环境搭建、PostGIS扩展部署，到跨服务器数据库迁移、数据恢复及索引验证，解决所有常见报错，全程可复制执行，适合新手和运维人员参考，兼顾GIS业务部署与数据库拆分迁移需求，解决新手和运维人员最关心的PostgreSQL 12部署、PostGIS 3.4.2安装等SEO高频搜索问题。

标签：PostgreSQL 12部署、PostGIS 3.4.2安装、PostgreSQL数据迁移、PostGIS避坑、Windows PostgreSQL实操、PostgreSQL数据恢复报错解决

一、环境说明（核心前提，必看）

本次实操目标：搭建可正常运行的GIS数据库环境，完成跨服务器数据库拆分迁移，恢复已有空间数据备份，确保PostGIS空间功能、索引全部可用，具体环境如下：

  • 操作系统：Windows（适配所有Windows系统，Win10/11通用，本次涉及3台Windows服务器、1台Linux业务服务器）

  • PostgreSQL版本：12.17（稳定版，适配PostGIS 3.4.2，所有服务器统一版本，避免迁移兼容问题）

  • PostGIS版本：3.4.2（针对PostgreSQL 12专用版，严格匹配PG版本，避免扩展安装失败）

  • 数据库相关：目标数据库gansueqwin（用于存储GIS业务数据），备份文件D:\gansueqwin_backup.dump（含空间数据表、索引、约束、PostGIS相关函数）

  • 服务器角色（迁移场景补充）：

    • 67服务器（原数据库服务器，Windows）：已部署PostgreSQL 12.17+PostGIS 3.4.2，承载gansueqwin（66业务用）和gansueq（40主力机用）两个库

    • 32服务器（新数据库服务器，Windows）：需全新部署环境，专用承载gansueqwin库（仅给66服务器使用）

    • 66服务器（业务服务器，Windows）：Java GIS项目，需切换数据库连接至32服务器

    • 40服务器（主力业务服务器，Linux）：Java项目，继续关联67服务器gansueq库（全程不改动，避免影响主力业务）

  • 通用配置：数据库统一账号postgres，密码postgres2024（减少配置复杂度，可后续修改），PostgreSQL默认端口5432

二、全程实操步骤（部署+迁移+恢复，避坑重点标注）

第一部分：32服务器基础环境部署（PostgreSQL 12.17 + PostGIS 3.4.2）

第一步：PostgreSQL 12.17 安装（基础环境，避坑关键）

  1. 下载PostgreSQL 12.17安装包（PostgreSQL官网下载链接，或可信镜像），安装路径选择纯英文路径（本次路径：D:\pushServer\postgresql），避免中文路径导致后续报错。

  2. 安装过程中设置postgres管理员密码（务必牢记，后续登录、操作都需要），端口默认5432，无需修改。

  3. 取消勾选“Stack Builder”（无需额外安装组件，减少冗余，避免冲突），完成安装。

⚠️ 避坑点：安装路径绝对不能含中文、特殊字符，密码建议设置简单易记（如postgres2024），后续可修改，避免忘记密码导致无法登录，这也是PostgreSQL 12部署中最易忽略的错误。

第二步：创建目标数据库（gansueqwin）

安装完成后，打开CMD（管理员身份），进入PostgreSQL的bin目录，执行以下命令创建数据库并授权：

cd D:\pushServer\postgresql\bin
psql -U postgres
-- 登录后执行创建数据库命令
CREATE DATABASE gansueqwin;
-- 给postgres用户授予该库所有权限（避免后续操作权限不足）
GRANT ALL PRIVILEGES ON DATABASE gansueqwin TO postgres;

执行成功后，会分别显示“CREATE DATABASE”和“GRANT”，说明数据库创建并授权成功，此步骤核心是确保postgres用户拥有完整权限，避免后续PostGIS扩展安装、数据恢复时出现权限不足报错。

第三步：PostGIS 3.4.2 安装（最易踩坑，版本匹配是核心）

核心原则：PostGIS版本必须与PostgreSQL版本严格匹配，这是PostGIS 3.4.2安装的核心要点，本次PostgreSQL 12.17，对应PostGIS 3.4.2（for PostgreSQL 12 x64），可通过两种方式安装（二选一，推荐方式二，适配迁移场景），避免出现版本不兼容报错。

方式一：直接安装（全新部署，无迁移需求适用）

  1. 卸载错误版本（若之前装错过）：如果之前安装过PostGIS 3.4.2（for PostgreSQL 13），必须先卸载（控制面板→卸载程序，找到带“PostgreSQL 13”的PostGIS组件，右键卸载），卸载后重启电脑，避免残留文件导致冲突，这是PostGIS扩展安装失败的常见原因之一。

  2. 正确安装PostGIS 3.4.2（for PostgreSQL 12）：运行安装包，勾选以下组件（严格对照，避免报错）：

    • ✅ PostGIS Bundle（核心主包，必勾，不勾等于未安装）

    • ❌ Create spatial database（不勾，自动创建模板库易报错，后续手动安装扩展更稳）

    • ✅ Register PROJ_LIB Env Var（必勾，坐标转换必备，不勾会导致空间功能失效）

    • ✅ Register GDAL_DATA Env Var（必勾，栅格/矢量数据处理必备）

    • ✅ Enable Key GDAL Drivers（必勾，支持常用GIS格式，如Shapefile、TIFF）

    • ❌ Enable All GDAL Drivers（不勾，避免冗余和安全风险）

    • ✅ Allow Out-db Rasters（推荐勾，提升大栅格数据处理效率）

    • ✅ Register SSL Bundle（推荐勾，生产环境远程加密连接必备）

  3. 安装过程中弹窗“Would you like to register PROJ_LIB...”，直接点击“是(Y)”，自动配置环境变量，无需手动操作。

方式二：从67服务器复制插件（迁移场景适用，无需重新下载）

若67服务器已部署相同版本的PostGIS 3.4.2，可直接复制插件文件到32服务器，高效快捷，步骤如下：

  1. 在67服务器找到PostGIS插件核心目录，复制对应文件：

    • 插件配置文件目录：C:\Program Files\PostgreSQL\12\share\extension\，复制所有以“postgis”开头的文件（如postgis--3.4.2.sql、postgis.control等）。

    • 动态库文件目录：C:\Program Files\PostgreSQL\12\lib\，复制所有以“postgis”开头的.dll文件（如postgis-3.dll、postgis_topology-3.dll等）。

  2. 将复制的文件粘贴到32服务器对应目录（32的PostgreSQL安装路径为D:\pushServer\postgresql\）：

    • 插件配置文件粘贴到：D:\pushServer\postgresql\share\extension\（覆盖原有同名文件即可）。

    • 动态库文件粘贴到：D:\pushServer\postgresql\lib\（覆盖原有同名文件即可）。

  3. 重启32服务器的PostgreSQL服务：按Win+R输入services.msc，找到postgresql-x64-12，右键重启，插件文件立即生效。

⚠️ 避坑点：版本不匹配会直接导致扩展安装失败，报错“PostGIS built for PostgreSQL 13 cannot be loaded in PostgreSQL 12”，这是PostGIS 3.4.2安装最易踩的坑，务必确认安装包/插件文件是针对PostgreSQL 12的版本，解决PostGIS扩展安装失败问题。

第四步：安装PostGIS扩展（启用空间功能，必做）

PostGIS安装完成后，需手动给目标数据库gansueqwin安装扩展，才能支持空间数据存储和查询，执行以下命令：

cd D:\pushServer\postgresql\bin
-- 安装核心空间扩展
psql -U postgres -d gansueqwin -c "CREATE EXTENSION postgis;"
-- 安装栅格数据支持扩展（GIS业务必备）
psql -U postgres -d gansueqwin -c "CREATE EXTENSION postgis_raster;"
-- 安装拓扑扩展（可选，根据业务需求）
psql -U postgres -d gansueqwin -c "CREATE EXTENSION postgis_topology;"

执行成功后，会显示“CREATE EXTENSION”，说明扩展安装成功，此步骤是启用PostGIS空间功能的关键，未执行此步骤会导致后续数据恢复时GIS表无法创建。

验证扩展版本（确认安装正确）：

psql -U postgres -d gansueqwin -c "SELECT postgis_version();"

返回“3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1”，说明PostGIS 3.4.2安装成功，且与PostgreSQL 12.17兼容。

第二部分：跨服务器数据库迁移（67→32，拆分部署）

第五步：在67服务器备份gansueqwin数据库

核心：备份时需包含表结构、数据、PostGIS相关函数和索引，确保备份文件完整，避免迁移后数据缺失，这是PostgreSQL跨服务器迁移的核心前提。

  1. 打开67服务器的CMD（以管理员身份运行），进入PostgreSQL的bin目录（默认路径，若自定义路径需调整）：
    cd C:\Program Files\PostgreSQL\12\bin

  2. 执行备份命令（直接复制，无需修改，输入postgres密码即可）：
    pg_dump -h 10.62.210.67 -p 5432 -U postgres -F c -b -v -f D:\gansueqwin_backup.dump gansueqwin

  3. 校验备份：备份完成后，去D盘查看gansueqwin_backup.dump文件，确认文件大小正常、无报错。

第六步：将备份文件传输至32服务器

通过远程桌面复制、共享文件夹等方式，将67服务器D盘的gansueqwin_backup.dump文件，复制到32服务器的D盘根目录（便于后续恢复时查找，避免路径过长导致报错），这是PostgreSQL数据迁移中确保备份文件可用的基础步骤。

第七步：32服务器重建数据库（清理环境，确保恢复无冲突）

若之前已创建gansueqwin库，需先清理旧库（避免残留数据冲突），步骤如下，此步骤可解决PostgreSQL数据库删除失败的相关问题：

  1. 打开32服务器的SQL Shell (psql)，输入postgres密码登录，切换到默认postgres库（避免无法删除当前连接的库）：
    \c postgres

  2. 强制断开gansueqwin库的所有连接（若库已存在，避免删除失败）：
    SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'gansueqwin';

  3. 删除旧库（若存在），重建gansueqwin数据库：
    DROP DATABASE IF EXISTS gansueqwin; CREATE DATABASE gansueqwin; -- 重新授权，避免权限不足 GRANT ALL PRIVILEGES ON DATABASE gansueqwin TO postgres;

  4. 切换到新创建的gansueqwin库，重新安装PostGIS扩展（确保扩展与库绑定）：
    \c gansueqwin; CREATE EXTENSION postgis; CREATE EXTENSION postgis_raster; CREATE EXTENSION postgis_topology;

第三部分：数据恢复&完整性验证（核心目标）

第八步：32服务器数据恢复（避坑重点）

扩展安装完成后，即可恢复备份文件，执行以下命令（本地恢复，无需远程连接，避免权限报错）：

cd D:\pushServer\postgresql\bin
pg_restore -p 5432 -U postgres -d gansueqwin --no-privileges --no-indexes -v D:\gansueqwin_backup.dump

输入postgres密码后，开始自动恢复，恢复过程中会显示“创建CONSTRAINT”“创建INDEX”等日志，无报错即代表恢复正常，这是PostgreSQL数据恢复的核心判断标准。

⚠️ 避坑点1：恢复前必须确保PostGIS扩展已安装，否则会报错“所需的扩展postgis未安装”；

⚠️ 避坑点2：--no-indexes参数可跳过索引错误，避免恢复中断，后续可手动重建索引；

⚠️ 避坑点3：若恢复时提示远程连接失败，去掉命令中的“-h IP”，用本地连接恢复即可。

第九步：索引完整性&安全性检查（确保可用）

数据恢复完成后，需检查索引是否完整、无损坏，尤其是PostGIS空间索引，确保GIS业务正常运行，执行以下4条命令即可全面检查：

-- 1. 查看所有索引（确认索引数量，与67服务器一致）
psql -U postgres -d gansueqwin -c "\di"

-- 2. 查看PostGIS空间索引（核心，确保空间查询正常）
psql -U postgres -d gansueqwin -c "SELECT tablename, indexname FROM pg_indexes WHERE indexdef LIKE '%gist%' ORDER BY tablename;"

-- 3. 检查索引有效性（无损坏，确保查询高效）
psql -U postgres -d gansueqwin -c "SELECT relname AS index_name, idx_scan FROM pg_stat_user_indexes ORDER BY relname;"

-- 4. 检查数据库整体健康（确认数据完整，无缺失）
psql -U postgres -d gansueqwin -c "SELECT datname, pg_database_size(datname)/1024/1024 AS 库大小_MB FROM pg_database WHERE datname='gansueqwin';"

✅ 检查标准：所有命令无报错、能看到完整的索引列表、有gist空间索引、数据库有正常大小，且表数量与67服务器一致，即代表索引完整、安全、可用，确保PostGIS空间查询功能正常，这是PostgreSQL数据恢复后的关键验证步骤。

第十步：业务切换&迁移收尾

  1. 修改66服务器Java项目配置：打开66服务器上Java项目的配置文件（application.yml），修改数据库连接地址（只改IP，其他不变）：
    spring: datasource: url: jdbc:postgresql://10.62.204.32:5432/gansueqwin username: postgres password: postgres2024

  2. 启动业务并验证：保存配置文件，启动66服务器上的Java项目，打开项目页面，测试地图加载、数据增删改查功能，确认无数据库相关报错，新数据能正常写入32服务器的数据库，完成PostgreSQL跨服务器迁移后的业务切换。

  3. 迁移收尾：

    • 观察24小时：确认66业务正常，32数据库运行稳定，无报错。

    • 备份归档：将67服务器上的备份文件（gansueqwin_backup.dump）归档保存，32服务器的备份文件也留存备用。

    • 旧库清理：67服务器上的gansueqwin库保留3~7天，确认无异常后再删除（避免回滚需求）。

三、常见报错及解决方案（重点收藏，全程避坑）

本次实操过程中遇到的所有报错，整理成解决方案，避免后续踩坑，覆盖部署、迁移、恢复全流程，解决PostgreSQL和PostGIS实操中最常见的报错问题，方便新手和运维人员快速排查：

1. 报错：用户 "postgres" Password 认证失败

原因：忘记postgres密码，或密码输入错误。

解决方案：

  1. 打开PostgreSQL安装目录下的data/pg_hba.conf文件，将“scram-sha-256”改为“trust”；

  2. 重启PostgreSQL服务（Win+R输入services.msc，找到postgresql-x64-12，右键重启）；

  3. 无密码登录：psql -U postgres -d gansueqwin；

  4. 重置密码：ALTER USER postgres PASSWORD '新密码'；

  5. 将pg_hba.conf改回“scram-sha-256”，重启服务即可。

2. 报错：PostGIS built for PostgreSQL 13 cannot be loaded in PostgreSQL 12

原因：PostGIS版本与PostgreSQL版本不匹配。

解决方案：卸载当前PostGIS，重新下载安装与PostgreSQL版本匹配的PostGIS（本次PostgreSQL 12对应PostGIS 3.4.2 for PG12）；迁移场景可直接从原服务器复制对应版本插件。

3. 报错：所需的扩展"postgis"未安装

原因：PostGIS已安装，但未给目标数据库安装扩展，或扩展安装不完整。

解决方案：执行“CREATE EXTENSION postgis;”“CREATE EXTENSION postgis_raster;”，给目标数据库安装扩展，若仍报错，重启PostgreSQL服务后重试。

4. 报错：没有用于主机 "xxx", 用户 "postgres" 的 pg_hba.conf 记录

原因：远程连接权限未开启，或恢复命令使用了远程IP，导致权限校验失败。

解决方案：去掉恢复命令中的“-h IP”，用本地连接恢复（无需修改配置，最简单高效）；若需远程连接，修改pg_hba.conf文件，最底部添加一行：host all all 0.0.0.0/0 md5，同时修改postgresql.conf文件，将listen_addresses改为listen_addresses = '*'，重启服务即可。

5. 报错：无法删除当前使用的数据库/其他用户正在使用数据库 "gansueqwin"

原因：当前连接在gansueqwin库，或有其他程序（如Navicat）正在连接该库，无法删除。

解决方案：先切换到默认postgres库（\c postgres），执行强制断开连接的SQL（SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'gansueqwin';），踢掉所有关联连接，再执行删除命令。

6. 报错：Navicat连接32服务器超时（10060错误）

报错信息：could not connect to server :Connection timed out (0x0000274C/10060)

原因：32服务器未放行5432端口，或防火墙拦截了连接。

解决方案：32服务器放行5432端口（高级安全Windows防火墙→入站规则→新建端口规则，TCP+5432，允许连接），重启防火墙和PostgreSQL服务。

四、回滚方案（万无一失，应对异常）

若迁移过程中出现异常，需快速恢复原有业务，避免业务中断，步骤如下：

  1. 停止66服务器上的Java项目。

  2. 将项目配置文件中的数据库IP改回67服务器（10.62.210.67）。

  3. 启动66项目，业务立即恢复正常，后续再排查迁移问题（如扩展安装、恢复命令错误等）。

五、最终验证&总结

最终验证结果

  • PostgreSQL 12.17 正常运行，postgres用户可正常登录，权限配置无误；

  • PostGIS 3.4.2 扩展安装成功，空间功能、坐标转换、栅格处理均可用；

  • 跨服务器迁移完成，32服务器gansueqwin数据恢复完整，表、约束、索引全部正常，与67服务器一致；

  • 66业务服务器正常连接32数据库，GIS业务（地图加载、数据查询、增删改查）无异常；

  • 40主力机业务不受影响，继续正常关联67服务器gansueq库，实现数据库拆分隔离。

总结

PostgreSQL+PostGIS部署+迁移+恢复的核心是“版本匹配”和“流程顺序”：先部署基础环境，再安装匹配版本的PostGIS扩展，最后执行备份迁移和数据恢复，全程避开版本不兼容、密码错误、权限不足、索引缺失等常见坑，这是PostgreSQL 12 + PostGIS 3.4.2 实操的核心要点。

本次实操全程无冗余步骤，所有命令可直接复制执行，兼顾了GIS业务部署和跨服务器数据库拆分迁移需求，适合中小企业GIS业务部署、数据迁移、容灾部署等场景，解决PostgreSQL 12部署、PostGIS 3.4.2安装、数据恢复等核心需求。整个流程（含环境部署、插件迁移、数据恢复、问题排查）耗时约30分钟，熟练操作后可缩短至15分钟，新手也能快速上手。

部署完成后，即可将GIS业务系统连接该数据库（主机localhost/服务器IP、端口5432、库名gansueqwin、用户postgres、密码自行设置），正常开展GIS相关业务。相关实操可参考PostgreSQL 12日常备份与维护指南，确保数据库长期稳定运行。

posted on 2026-04-02 18:20  SheepDog1998  阅读(11)  评论(0)    收藏  举报