Chapter 3. PostGIS 管理
Prev     Next

Chapter 3. PostGIS 管理

Table of Contents

3.1. 性能调优

PostGIS 性能的调优与任何 PostgreSQL 工作负载的调优非常相似。 唯一需要额外考虑的是，几何图形和栅格通常很大，因此与内存相关的优化通常对 PostGIS 的影响比其他类型的 PostgreSQL 查询更大。

有关优化 PostgreSQL 的一般详细信息，请参阅 调整 PostgreSQL 服务器。

可以通过 ALTER SYSTEM 命令在服务器层面设置 PostgreSQL 配置，这样就不用去改 postgresql.conf 或 postgresql.auto.conf 。

ALTER SYSTEM SET work_mem = '256MB';
-- this forces non-startup configs to take effect for new connections
SELECT pg_reload_conf();
-- show current setting value
-- use SHOW ALL to see all settings
SHOW work_mem;

除了 Postgres 设置之外，PostGIS 还有一些自定义设置，请参见Section 7.22, “大一统自定义变量 (GUCs)”。

3.1.1. 启动

这些设置在 postgresql.conf 中配置：

constraint_exclusion

  • 默认：分区

  • 这一般用在分区表场景。默认值 "partition" 的含义是：只有当表位于继承层级结构里时，优化器才会去做约束检查；否则就不分析，避免白白增加优化器的开销。

共享缓冲区

  • Default: ~128MB

  • 将其设置为可用RAM的约 25% 到 40%。在 Windows 上，您可能无法设置得那么高。

max_worker_processes 用来控制服务器可用的后台进程数量上限，限制能参与并行查询的工作进程数量。

  • 默认值：8

  • 设置系统可以支持的最大后台进程数。 该参数只能在服务器启动时设置。

3.1.2. 运行

work_mem - 设置用于排序操作和复杂查询的内存大小

  • 默认：1-4MB

  • 针对大型数据库、复杂查询、大量 RAM 进行调整

  • 针对许多并发用户或 RAM 较低的情况进行下调。

  • 如果您有大量 RAM 而开发人员很少： 

    SET work_mem TO '256MB';

Maintenance_work_mem - 用于 VACUUM、CREATE INDEX 等的内存大小。

  • 默认值：16-64MB

  • 通常太低 - 占用 I/O，在交换内存时锁定对象

  • 建议在具有/大量 RAM 的生产服务器上使用 32 MB 到 1GB，但取决于并发用户数。 如果您有大量 RAM 而开发人员很少： 

    SET maintenance_work_mem TO '1GB';

max_parallel_workers_per_gather

PostgreSQL / PostGIS 的并行查询机制会根据这个设置来决定一个执行计划最多能申请多少个并行工作进程。只要把它设成大于 0，像 ST_Intersects 这类关系函数的就能利用多个进程来跑，速度有机会提升到原来的两倍以上。如果你的机器 CPU 富余，建议把这个值调到你拥有的处理器数量。同时要把 max_worker_processes 也一起调高，至少不小于这个数。

  • 默认:0

  • 设置由单个 Gather 节点启动的最大工作进程数。并行工作进程来自 max_worker_processes 建立的进程池。请注意，在运行时，请求的工作进程数量可能实际上无法使用。如果出现这种情况，计划将以比预期更少的工作进程运行，这可能效率低下。将此值设置为默认值 0 将禁用并行查询执行。

3.2. 配置栅格支持

如果启用了栅格的支持，则可能需要读一下下面的内容来正确配置它。

Out-of-db 栅格（就是放在数据库外部的 raster）和所有 raster 驱动默认都是禁用的。要重新启用它们，需要在服务器环境里设置环境变量 POSTGIS_GDAL_ENABLED_DRIVERS 和 POSTGIS_ENABLE_OUTDB_RASTERS。你也可以采用更跨平台的方式，直接设置对应的 Section 7.22, “大一统自定义变量 (GUCs)”。

如果要启用离线栅格：

POSTGIS_ENABLE_OUTDB_RASTERS=1

如果包括任何其它值或不包含值，则会禁用离线栅格。

若想启用已经安装的 GDAL 驱动，请设置以下环境变量

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

如果只想启用某些驱动，请按如下所示设置环境变量：

POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF XYZ"
[Note]

在 Windows 上，不要在驱动程序列表中加上引号

设置环境变量的方式会因操作系统而异。如果是在 Ubuntu 或 Debian 上通过 apt-postgresql 安装的 PostgreSQL，推荐的方式是编辑/etc/postgresql/MAJOR/CLUSTER/environment 文件，其中占位符分别表示 PostgreSQL 的主版本号和集群名称。

在 Windows 上，如果您作为服务运行，则可以通过系统变量进行设置，对于 Windows 7，您可以通过右键单击计算机 -> 属性高级系统设置或在资源管理器中导航至 控制面板\所有 控制面板项目\系统。 然后点击高级系统设置->高级->环境变量并添加新的系统变量。

设置环境变量后，您需要重启 PostgreSQL 服务才能使更改生效。

3.3. 创建空间数据库

3.3.1. 使用扩展（EXTENSION）启用空间数据库

如果你已经编译并安装好了 extensions/postgis 模块，就可以通过 EXTENSION 机制把一个普通数据库变成空间数据库。

postgis 的核心扩展包括几何、地理、spatial_ref_sys 以及所有函数和注释。栅格（Raster）和拓扑（Topology）打包为单独的扩展模块。

在要启用空间的数据库中运行以下SQL代码段： 

CREATE EXTENSION IF NOT EXISTS plpgsql;
      CREATE EXTENSION postgis;
      CREATE EXTENSION postgis_raster; -- OPTIONAL
      CREATE EXTENSION postgis_topology; -- OPTIONAL

3.3.2. 不通过扩展（EXTENSION）的方式启用空间数据库（不建议）

[Note]

通常，仅当你不能或不想在PostgreSQL扩展的目录中安装PostGIS时（例如，在测试或开发期间，或在受限环境中），才需要这样做。

将 PostGIS 对象和函数定义添加到数据库中是通过加载位于构建阶段指定的 [prefix]/share/contrib 中的各种 sql 文件来完成的。

核心 PostGIS 对象（几何和地理类型及其支持函数）位于 postgis.sql 脚本中。 栅格对象位于 rtpostgis.sql 脚本中。 拓扑对象位于 topology.sql 脚本中。

对于完整的 EPSG 坐标系定义标识符集，您还可以加载 spatial_ref_sys.sql 定义文件并填充 spatial_ref_sys 表。 这将允许您对几何图形执行 ST_Transform() 操作。

如果您希望向 PostGIS 函数添加注释，可以在 postgis_comments.sql 脚本中找到它们。 只需从 psql 终端窗口输入 \dd [function_name] 即可查看注释。

在终端中运行以下 shell 命令： 

DB=[yourdatabase]
    SCRIPTSDIR=`pg_config --sharedir`/contrib/postgis-3.6/

    # Core objects
    psql -d ${DB} -f ${SCRIPTSDIR}/postgis.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/spatial_ref_sys.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/postgis_comments.sql # OPTIONAL

    # Raster support (OPTIONAL)
    psql -d ${DB} -f ${SCRIPTSDIR}/rtpostgis.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/raster_comments.sql # OPTIONAL

    # Topology support (OPTIONAL)
    psql -d ${DB} -f ${SCRIPTSDIR}/topology.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/topology_comments.sql # OPTIONAL

3.4. 升级空间数据库

当您需要替换或部署新的 PostGIS 对象定义时，升级现有空间数据库可能会很棘手。

遗憾的是，并非所有定义都可以在正在运行的数据库中轻松替换，因此转储/重装可能是最好的选择。

PostGIS 为次要版本升级和错误修复提供软升级，为主要升级提供硬升级。

在尝试升级PostGIS之前备份数据始终是值得的。 可以将 -fc 标志与 pg_dump 一起使用，以始终通过硬升级还原转储。

3.4.1. 软升级

如果使用扩展安装数据库，则必须使用扩展模型对其进行升级。 如果使用较旧的 SQL 脚本进行安装，则应切换到扩展，因为不再支持 SQL 脚本。

3.4.1.1. 使用 EXTENSION 软升级

如果你最初是用 EXTENSION 的方式安装 PostGIS，那升级的时候也必须继续用 EXTENSION 的方式。用 EXTENSION 做小版本升级一般都比较省心，过程也挺简单。

使用 PostGIS_Extensions_Upgrade 函数可以把 PostGIS 升级到你当前已安装的最新版本。

SELECT postgis_extensions_upgrade();

如果你要升级的数据库还在跑 PostGIS 2.5，先执行一次 ALTER EXTENSION，然后再调用 postgis_extensions_upgrade()，这样可以把旧的 postgis_raster 遗留对象合并回主扩展，避免残留在外面。

ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();
-- This second call is needed to rebundle postgis_raster extension
SELECT postgis_extensions_upgrade();

如果安装了多个版本的 PostGIS，并且不想升级到最新版本，则可以指定显式版本。 执行以下操作：

ALTER EXTENSION postgis UPDATE TO "3.7.0dev";
ALTER EXTENSION postgis_topology UPDATE TO "3.7.0dev";

您可能会看到类似于以下内容的错误通知：

No migration path defined for … to 3.7.0dev

在这种情况下，必须备份数据库，按照 Section 3.3.1, “使用扩展（EXTENSION）启用空间数据库”中所述生成新数据库，然后将备份还原到新数据库。

您可能会收到类似于以下内容的消息：

Version "3.7.0dev" of extension "postgis" is already installed

那么一切都已经是最新的，您可以安全地忽略它。 除非您正在尝试从开发版本升级到下一个版本（不会获得新的版本号）； 在这种情况下，您可以将“next”附加到版本字符串，下次您需要再次删除“next”后缀： 

ALTER EXTENSION postgis UPDATE TO "3.7.0devnext";
ALTER EXTENSION postgis_topology UPDATE TO "3.7.0devnext";
[Note]

如果您最初安装 PostGIS 时没有指定版本，则通常可以在恢复之前跳过 postgis 扩展的重新安装，因为备份只有 CREATE EXTENSION postgis ，因此在恢复期间会选择最新版本。
[Note]

如果您要从 3.0.0 之前的版本升级 PostGIS 扩展，您将拥有一个新的扩展 postgis_raster，如果您不需要栅格支持，则可以安全地删除该扩展。 您可以按如下方式删除：

DROP EXTENSION postgis_raster;

3.4.1.2. 不使用 EXTENSION 软升级

扩展是 PostGIS 官方支持的安装方式。如果你现在还依赖非打包安装，那就需要执行一次 HARD UPGRADE，在一个用扩展方式创建的新库里完成升级，然后把数据迁移过去。

3.4.1.3. 硬升级

硬升级意味着完全转储/重新加载 PostGIS 中可用的数据。如果 PostGIS 对象的内部存储状态发生更改或无法进行软升级，则需要硬升级。 附录中的发行说明指示每个版本是否需要转储/重新加载（硬升级）。

转储/重新加载操作由脚本辅助postgis_restore。 此脚本跳过属于 PostGIS 的所有定义（包括旧定义）。 您还可以将方案和数据恢复到 PostGIS 安装的数据库，而不会传递重复的符号错误或已弃用的对象。

有关 Windows 的其他信息，请参阅 Windows 硬升级。

程序如下：

  1. 创建要升级的数据库的“自定义格式”转储（我们称之为 olddb），包括二进制 blob (-b) 和详细 (-v) 输出。 用户可以是数据库的所有者，不必是postgres超级帐户。 

    pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb

  2. 在新数据库中全新安装 PostGIS - 我们将此数据库称为 newdb。 请参阅 Section 3.3.2, “不通过扩展（EXTENSION）的方式启用空间数据库（不建议）” 和 Section 3.3.1, “使用扩展（EXTENSION）启用空间数据库” 了解如何执行此操作的说明。

    转储中的spatial_ref_sys将还原，但不会覆盖现有spatial_ref_sys。 这是为了确保对正式数据集的更正被传送到要恢复的数据库。 如果要覆盖标准条目，只需在生成 newdb 时不要加载spaltial_ref_sys.sql文件。

    如果您的数据库确实很旧，或者您知道在视图和函数中使用了长期不推荐使用的函数，则可能需要为所有函数和视图等加载 legacy.sql 才能正确返回 。 仅在确实需要时才执行此操作。 如果可能的话，请考虑在转储之前升级您的视图和函数。 稍后可以通过加载 uninstall_legacy.sql 来删除已弃用的函数。

  3. 使用 postgis_restore 将备份恢复到新的 newdb 数据库中。 意外错误（如果有）将由 psql 打印到标准错误流。 记录这些内容。 

    postgis_restore "/somepath/olddb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt

在以下情况下可能会发生错误：

  1. 您的某些视图或函数使用已弃用的 PostGIS 对象。 为了解决这个问题，您可以尝试在恢复之前加载 legacy.sql 脚本，或者您必须恢复到仍包含这些对象的 PostGIS 版本，并在移植代码后再次尝试迁移。 如果 legacy.sql 方式适合您，请不要忘记修复您的代码以停止使用已弃用的函数并将其加载 uninstall_legacy.sql。

  2. 一些在转储文件中的自定义空间参考系统记录具有无效的SRID值。有效的SRID值应大于0且小于999000。值在999000.999999范围内被保留供内部使用，而大于999999的值则根本不能使用。所有具有无效SRID的自定义记录将被保留，其中大于999999的值将被移动到保留范围内，但空间_ref_sys表将失去维护该不变条件的检查约束，可能还会失去其主键（当多个无效的SRID值转换为相同的保留SRID值时）。

    为了解决此问题，您应该将自定义SRS复制到 具有有效值的 SRID（可能在 910000..910999 范围），将所有表转换为新的 srid （请参阅UpdateGeometrySRID），删除无效的 从spatial_ref_sys输入并重建检查： 

    ALTER TABLE spatial_ref_sys ADD CONSTRAINT spatial_ref_sys_srid_check check (srid 
> 0 AND srid < 999000 );

    ALTER TABLE spatial_ref_sys ADD PRIMARY KEY(srid));

    如果要升级包含法国 IGN 映射的旧数据库，则可能会超出 SRID 的范围，并在导入数据库时遇到以下问题： 

    WARNING: SRID 310642222 converted to 999175 (in reserved zone)

    在这种情况下，您可以尝试以下步骤： 完全丢弃最初从 SQL postgis_restore中产生的 IGN。 为此，请运行以下命令： 

    postgis_restore "/somepath/olddb.backup" > olddb.sql

    运行以下命令： 

    grep -v IGNF olddb.sql > olddb-without-IGN.sql

    之后，生成一个新数据库，启用必要的 PostGIS 扩展，并确保使用此 脚本插入法语 IGN 系统。 完成这些过程后，按如下方式导入数据： 

    psql -h localhost -p 5432 -U postgres -d newdb -f olddb-without-IGN.sql  2> errors.txt