Chapter 3. PostGIS 管理

Table of Contents

3.1. 性能调优

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

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

对于 PostgreSQL 9.4+,可以使用 ALTER SYSTEM 命令在服务器级别设置配置,而无需触及 postgresql.confpostgresql.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.23, “大一统自定义变量 (GUCs)”

3.1.1. 启动

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

constraint_exclusion

  • 默认:分区

  • 这通常用于表分区。默认设置为 "partition",适用于 PostgreSQL 8.4 及更高版本,因为它将强制查询规划器仅在表处于继承层次结构中时分析约束考虑,否则将不会对规划器产生影响。

共享缓冲区

  • 默认值:PostgreSQL 9.6 中约为 128MB

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

max_worker_processes 此设置仅适用于 PostgreSQL 9.4 +。 对于 PostgreSQL 9.6+,此设置具有额外的重要性,因为它控制并行查询可以拥有的最大进程数。

  • 默认值: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 9.6+,并且只会影响 PostGIS 2.3 +,因为只有 PostGIS 2.3+ 支持并行查询。 如果设置为高于 0,则某些查询(例如涉及 ST_Intersects 等关系函数的查询)可以使用多个进程,并且运行速度可以提高两倍以上。 如果您有大量空闲处理器,则应将其值更改为您拥有的处理器数量。 还要确保将 max_worker_processes 提高到至少与此数字一样高。

  • 默认:0

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

3.2. 配置栅格支持

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

从 PostGIS 2.1.3 开始,默认情况下禁用数据库外栅格和所有栅格驱动程序要启用它们,请在服务器上设置环境变量 POSTGIS_GDAL_ENABLED_DRIVERSPOSTGIS_ENABLE_OUTDB_RASTERS。PostGIS 2.2 提供了一种跨平台方法,用于根据第 Section 7.23, “大一统自定义变量 (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/10/main/environment ,其中数字 10 是 PostgreSQL 的版本,main 表示集群。

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

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

3.3. 创建空间数据库

3.3.1. 使用扩展(EXTENSION)启用空间数据库

如果您使用的是 PostgreSQL 9.1 以上的版本,并且已经编译并安装了 postgis 扩展模块,那么数据库将在 PG 的扩展机制下成为空间数据库。

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.4/

    # 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. 使用 9.1 或更高版本的扩展进行软升级

如果使用扩展程序安装了PostGIS,则需要使用该扩展程序进行升级。使用扩展程序进行小升级非常轻松。

如果您运行的是 PostGIS 3 或更高版本,则应升级到已安装的具有PostGIS_Extensions_Upgrade功能的最新版本。

SELECT postgis_extensions_upgrade();

如果您运行的是 PostGIS 2.5 或更早版本,执行以下操作:

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.5.0dev";
ALTER EXTENSION postgis_topology UPDATE TO "3.5.0dev";

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

No migration path defined for … to 3.5.0dev

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

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

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

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

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

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

[Note]

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

DROP EXTENSION postgis_raster;

3.4.1.2. 9.1之前版本或者无扩展软升级

它适用于在不使用扩展名的情况下安装了PostGIS的人。如果您使用的是扩展并使用此方法,您将看到类似于以下内容的消息:

can't drop … because postgis extension depends on it

注: 如果要迁移到 r1 之前的 PostGIS 7429.* 或 PostGIS 2.*,则此过程不可用,但需要执行硬升级

编译并安装(make install)后,您应该在安装文件夹中找到一组 *_upgrade.sql 文件。 您可以通过以下方式列出它们:

ls `pg_config --sharedir`/contrib/postgis-3.5.0dev/*_upgrade.sql

postgis_upgrade.sql 开始依次加载它们。

psql -f postgis_upgrade.sql -d your_spatial_database

相同的过程适用于栅格、拓扑和 sfcgal 扩展,升级文件分别名为 rtpostgis_upgrade.sqltopology_upgrade.sqlsfcgal_upgrade.sql。 如果您需要它们:

psql -f rtpostgis_upgrade.sql -d your_spatial_database
psql -f topology_upgrade.sql -d your_spatial_database
psql -f sfcgal_upgrade.sql -d your_spatial_database

建议您通过运行切换到基于扩展的安装

psql -c "SELECT postgis_extensions_upgrade();"
[Note]

如果您找不到专门用于升级您的版本的 postgis_upgrade.sql,那么您使用的版本对于软升级来说还太早,需要执行硬升级

PostGIS_Full_Version函数的“进程需要升级”消息提供了有关需要执行此类升级的信息。

3.4.2. 硬升级

硬升级意味着完全转储/重新加载 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