当前位置： 首页 > news >正文

PostGIS安装与pg_dump/pg_restore排错

news 2025/7/31 5:59:02

第一章：环境准备与PostgreSQL/PostGIS核心安装

本指南适用于 Ubuntu 18.04、20.04 和 22.04 LTS 版本，目标是安装 PostgreSQL 14 和 PostGIS 3.4。

1. 添加 PostgreSQL 官方 APT 仓库

PostgreSQL 官方仓库提供最新版本的数据库和相关工具，确保您能安装到特定版本。

  • 添加仓库源文件：

    sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
    • ($(lsb_release -cs) 会自动识别Ubuntu版本代号，例如 jammy。)

  • 导入仓库签名密钥：

    wget --quiet -O - https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
    • （如果提示 apt-key is deprecated 警告，请暂时忽略，它不影响当前功能。）

  • 更新软件包列表：

    sudo apt update
    • 确保此步骤能正常获取到 apt.postgresql.org 的信息，没有连接错误。

2. 安装 PostgreSQL 14

sudo apt install postgresql-14
  • 安装完成后，PostgreSQL 服务通常会自动启动。可以使用 sudo systemctl status postgresql 检查状态。

3. 安装 PostGIS 3.4

sudo apt install postgis postgresql-14-postgis-3
  • postgis 提供了核心地理空间库，postgresql-14-postgis-3 则是为 PostgreSQL 14 集成的 PostGIS 扩展。

第二章：数据库与PostGIS配置

安装软件后，还需要在数据库内部进行配置。

1. 创建 PostgreSQL 数据库

以 postgres 超级用户身份登录 psql，并创建一个用于存储地理空间数据的新数据库。

sudo -i -u postgres # 切换到 postgres 系统用户
psql                 # 进入 psql 命令行界面

在 postgres=# 提示符下：

CREATE DATABASE postgis_test; # 创建名为 postgis_test 的数据库
\q                           # 退出 psql
exit                         # 退出 postgres 系统用户，回到您自己的用户

2. 在数据库中启用 PostGIS 扩展

切换回 postgres 系统用户，连接到 postgis_test 数据库，并激活 PostGIS 扩展。

sudo -i -u postgres # 切换到 postgres 系统用户
psql -d postgis_test # 连接到 postgis_test 数据库

在 postgis_test=# 提示符下：

CREATE EXTENSION postgis; # 激活 PostGIS 扩展
SELECT PostGIS_full_version(); # 验证 PostGIS 版本
\q                            # 退出 psql
exit                          # 退出 postgres 系统用户

3. 创建并授权普通数据库用户 (例如用户 a)

为了安全和方便，通常不直接使用 postgres 用户进行日常操作。创建一个普通用户 a 并赋予其权限。

sudo -i -u postgres # 切换到 postgres 系统用户
psql                 # 进入 psql 命令行界面

在 postgres=# 提示符下：

CREATE ROLE a WITH LOGIN PASSWORD '您的密码'; # 创建用户a，并设置密码
ALTER ROLE a CREATEDB; # （可选）赋予用户a创建数据库的权限
\q
exit
  • 务必记住密码！

然后，将用户 a 授权到 postgis_test 数据库的 public Schema 上：

psql -U postgres -d postgis_test # 以postgres用户连接postgis_test数据库

在 postgis_test=# 提示符下：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO a;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO a;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO a;
ALTER SCHEMA public OWNER TO a;
\q
exit

现在，用户 a 就可以管理 postgis_test 数据库中的 public Schema 了。

第三章：数据库备份与恢复：pg_dump与pg_restore

当需要迁移数据库到另一台电脑，或者进行完整的备份和恢复时，pg_dump 和 pg_restore 是最高效的工具。它们能将数据库结构和数据打包到一个文件中，并支持高效的二进制传输。

1. 导出/备份数据库 (使用 pg_dump)

假设您要备份 postgis_test 数据库，并使用用户 a。

在系统命令行中执行：

pg_dump -U a -Fc -d postgis_test -f /home/stu/下载/postgis_test_backup.dump
  • 这会将 postgis_test 数据库的完整备份（结构、数据、PostGIS扩展等）导出到 /home/stu/下载/postgis_test_backup.dump 文件。
  • 系统会提示您输入用户 a 的密码。

2. 导入/恢复数据库 (使用 pg_restore)

在目标电脑上，或者将备份恢复到一个新的数据库，您需要使用 pg_restore。

  • 创建新的空数据库 (如果需要)：
    例如，我们要恢复到 new_postgis_db。
    sudo -i -u postgres # 切换到 postgres 系统用户
psql                 # 进入 psql 命令行界面
CREATE DATABASE new_postgis_db; # 创建新的空数据库
\q                   # 退出 psql
exit                 # 退出 postgres 系统用户

**第四章：pg_restore常见问题与解决方案 **

问题一：pg_restore: 错误: 无法打开输入文件 "./postgis_test_backup.dump": 没有那个文件或目录

原因分析：
当通过 sudo -i -u postgres 切换到 postgres 系统用户时，您的当前工作目录会变为 postgres 用户的主目录，而不是备份文件所在的目录。./ 是相对路径，导致命令找不到文件。

解决方案：
始终使用备份文件的完整绝对路径来执行 pg_restore。

# 假设您已切换到 postgres 系统用户
# postgres@stu-VMware-Virtual:~$
pg_restore -d new_postgis_db -Fc /home/stu/下载/postgis_test_backup.dump
问题二：pg_restore: 错误: 无法打开输入文件 "/home/stu/下载/postgis_test_backup.dump": 权限不够

原因分析：
Linux 用户的家目录（/home/stu）及其子目录（如 ~/下载）默认权限非常严格，其他用户（包括 postgres 用户）默认无权访问。即使文件本身可读，目录不可进入也无法访问。

解决方案 (二选一)：

**方法 A：修改目录权限 **
允许 postgres 用户进入家目录和下载目录。

# 首先退出 postgres 用户，回到您自己的用户
# stu@stu-VMware-Virtual:~$
exit# 修改家目录权限（只允许进入，不开放读取内容）
chmod o+x /home/stu# 修改下载目录权限（允许进入和读取）
chmod o+rx /home/stu/下载# 重新切换到 postgres 用户
sudo -i -u postgres# 再次执行 pg_restore
# postgres@stu-VMware-Virtual:~$
pg_restore -d new_postgis_db -Fc /home/stu/下载/postgis_test_backup.dump

**方法 B：将备份文件移动到公共临时目录 **
将备份文件移动到所有用户都可访问的临时目录 /tmp。

# 首先退出 postgres 用户，回到您自己的用户
# stu@stu-VMware-Virtual:~$
exit# 移动备份文件
sudo mv /home/stu/下载/postgis_test_backup.dump /tmp/# 重新切换到 postgres 用户
sudo -i -u postgres# 再次执行 pg_restore (使用新路径)
# postgres@stu-VMware-Virtual:~$
pg_restore -d new_postgis_db -Fc /tmp/postgis_test_backup.dump
问题三：pg_restore: 错误: could not execute query: 错误: 角色 "a" 不存在

原因分析：
pg_dump 备份包含了对数据库用户 a 的权限授予语句。但在目标 PostgreSQL 实例中，数据库角色 a 还没有被创建，pg_restore 尝试授权时找不到该角色。

解决方案：
在执行 pg_restore 之前，确保目标 PostgreSQL 系统中已经创建了数据库角色 a。

# 假设您已在 postgres 系统用户下并进入 psql
# postgres=#
CREATE ROLE a WITH LOGIN PASSWORD '您之前为a设置的密码'; # 再次创建用户a
ALTER ROLE a CREATEDB; # （可选）赋予用户a创建数据库的权限
\q
exit
# 然后再回到 postgres 系统用户执行 pg_restore
sudo -i -u postgres
pg_restore -d new_postgis_db -Fc /home/stu/下载/postgis_test_backup.dump
问题四：pg_restore: 错误: could not execute query: 错误: 关系 "public.poi" 已经存在 (以及其他对象已存在的错误，如 函数 "update_poi_geom" 已经存在 等)

原因分析：
目标数据库 new_postgis_db 不是完全空的，其中已经存在了备份中包含的同名对象（可能手动运行过建表脚本）。pg_restore 尝试再次创建这些对象时发生冲突。

解决方案：
在恢复之前，确保目标数据库是完全空的，并使用 pg_restore 的 -c (clean) 选项。

# 假设您已在 postgres 系统用户下并进入 psql
# postgres=#
DROP DATABASE new_postgis_db; # 删除现有数据库（会删除所有数据！）
CREATE DATABASE new_postgis_db; # 重新创建新的空数据库
\q
exit
# 重新执行 pg_restore，添加 -c 选项
sudo -i -u postgres
pg_restore -d new_postgis_db -Fc -c /home/stu/下载/postgis_test_backup.dump
  • pg_restore -c 会在恢复前尝试删除所有对象（即使它们不存在也会报错“不存在”，但这些错误是无害的，pg_restore 会继续执行）。
问题五：pg_restore: 错误: 复制表"poi"失败: 错误: 函数 st_makepoint(double precision, double precision) 不存在

原因分析：
这个错误通常是由于 PostGIS 扩展或其依赖函数在数据导入（通过触发器等）时没有被数据库正确识别或加载。这可能是因为 PostGIS 扩展的创建时机或环境问题。

解决方案：
这个问题通常会被问题四的解决方案（即彻底清理目标数据库并使用 pg_restore -c）隐式解决。 pg_restore 在执行 -c 后，会严格按照备份文件中对象的依赖顺序进行创建，确保 PostGIS 扩展及其函数在数据导入之前就已经就位并可用。

问题六：psql: 错误: 连接到套接字"/var/run/postgresql/.s.PGSQL.5432"上的服务器失败:致命错误: 对用户"a"的对等认证失败

原因分析：
当您使用 psql -U a 默认进行本地 Unix 域套接字连接时，PostgreSQL 默认使用 peer (对等) 认证。这意味着它期望您的 Linux 系统用户名 (stu) 与您尝试连接的数据库用户名 (a) 相同。由于不匹配，认证失败。

解决方案：
修改 PostgreSQL 的认证配置文件 pg_hba.conf，允许本地连接通过密码认证。

  1. 编辑 pg_hba.conf 文件：
    sudo nano /etc/postgresql/14/main/pg_hba.conf
  2. 修改认证方式：
    找到类似 local all all peer 的行，并将其修改为 local all all md5。
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
-local   all             all                                     peer
+local   all             all                                     md5
    • md5 表示使用 MD5 加密的密码认证。
  3. 保存并退出 nano： Ctrl + O, Enter, Ctrl + X。
  4. 重启 PostgreSQL 服务：
    sudo systemctl restart postgresql
  5. 再次尝试连接：
    psql -U a -d new_postgis_db
    • 这次会提示输入用户 a 的密码，输入正确密码即可成功连接。
http://www.lryc.cn/news/604384.html

相关文章：

  • flutter 记录一个奇怪的问题
  • 在 Mac 上用 Vagrant 安装 K8s
  • InfluxDB 3 数据库命名与创建全攻略：规范、限制与实战指南
  • 《零基础入门AI：传统机器学习核心算法解析（KNN、模型调优与朴素贝叶斯）》
  • GaussDB 数据库架构师(十二) 数据库对象修改审计设置
  • Redis学习------缓存穿透
  • llama factory本地部署常见问题
  • Git版本控制器
  • 人工智能与家庭：智能家居的便捷与隐患
  • gdb调试的限制和配置自动生成core
  • 2023 年 NOI 最后一题题解
  • 【C++篇】哈希扩展：位图和布隆过滤器+哈希切割
  • 【Lambda】flatMap使用案例
  • c++之基础B（第一课）
  • dify离线插件打包步骤
  • 在Trae中使用MoonBit月兔
  • 【编号65】广西地理基础数据（道路、水系、四级行政边界、地级城市、DEM等）
  • 在 Elasticsearch 8.19 和 9.1 中引入更强大、更具弹性和可观测性的 ES|QL
  • Buck的Loadline和DVS区别和联系
  • Jenkinsfile 报错
  • 一篇讲清Redis中常见数据类型的用法
  • Three.js 与 WebXR：初识 VR/AR 开发
  • 国产化再进一步，杰和科技推出搭载国产芯片的主板
  • LoRaWAN协议，提升公用事业能源效率的“隐形引擎”
  • Ubuntu22.04.1搭建php运行环境
  • C++ 高性能容器：ankerl::unordered_dense::map
  • 元码智能“大眼睛”机器人首发，智启生活新纪元！
  • RabbitMQ 发送方确认的两大工具 (With Spring Boot)
  • Metering Solution for Solar + Storage光伏+储能计量解决方案 UL 2735 Certification功率表能源监测电表
  • 第2章 cmd命令基础：常用基础命令(2)