postgis_raster 从 2.x 升级至 3.x(postgis_ Raster from 2 X upgrade to 3 x)

从 PostGIS 3.0 开始,对于栅格数据的支持就从 扩展中分离了,叫做 .

postgis
postgis_raster

主要有两个原因:

  • PostGIS 插件的栅格功能很多,有超过 150 多个函数和多个数据类型,对于没用到这些的用户可能会有些迷茫;
  • gdal 库的占体积很大,很多只用 postgis 扩展的开发者希望减少它

虽然分离出栅格的部分让一部分开发者满意了,但是意味着从 2.x 升级到 3.x 的 PostGIS 就变得有点麻烦了,即使是有经验的用户也有可能搞砸。

本文将介绍 PostGIS 栅格模块升级的正确方法,即升级 2.x 的 PostGIS 到 3.x.

你可以用 psql 或 pgAdmin 或者任意一种 PostgreSQL 工具来运行下面的步骤。

无论你现在是什么版本的 PostGIS,你都应该先把 3.x 的 PostGIS 插件安装了,也无论你怎么安装的。

如果你是 2.4 或以下版本的

-- 这一步仅版本< 2.5.4 
alter extension postgis update;

-- 其它 2.x 的都要做如下步骤:注意,要运行两次
select postgis_extensions_upgrade();
select postgis_extensions_upgrade();

随后,若你的数据库没有用到栅格相关的功能,没有包含栅格数据的表,那么可以用下面的语句删除栅格扩展:

drop extension postgis_raster;

你可能想问,为什么 这一条语句要跑两次,原因是跑第一条会将栅格部分从 扩展中分离出来,分离出函数和类型;跑第二条时,就能把这些函数和类型重新绑定至 扩展中。

select postgis_extensions_upgrade();
postgis
postgis_raster

很遗憾,官方没能通过单个 pg 函数完成这一个过程,因为官方的作者忘记了具体是为什么就没写,貌似是因为 PostgreSQL 的扩展升级、安装以及创建一个新的扩展这些操作不能在同一个事务中进行。

PostgreSQL 13 移除了 的支持,增加了一些复杂性。因此,升级 13 或者更高版本的 PostgreSQL 之前最好就升级 PostGIS 3.x.

create extension ... from unpackaged
————————

Since PostGIS 3.0, the support for raster data has been separated from the extension, called

postgis
postgis_raster

There are two main reasons:

  • PostGIS plug-in has many grid functions, including more than 150 functions and multiple data types. Users who do not use these functions may be confused;
  • GDAL library occupies a large volume, and many developers who only use PostGIS extension want to reduce it

Although some developers are satisfied with separating the part of the grid, it means from 2 X upgrade to 3 X’s PostGIS becomes a little troublesome, and even experienced users may screw it up.

This article will introduce the correct method to upgrade the PostGIS grid module, that is, upgrade 2 X PostGIS to 3 x.

You can use PSQL or pgadmin or any PostgreSQL tool to run the following steps.

No matter what version of PostGIS you are now, you should put 3. 0 first X’s PostGIS plug-in is installed, no matter how you install it.

If you are 2.4 or below

-- 这一步仅版本< 2.5.4 
alter extension postgis update;

-- 其它 2.x 的都要做如下步骤:注意,要运行两次
select postgis_extensions_upgrade();
select postgis_extensions_upgrade();

Then, if your database does not use grid related functions and does not have a table containing grid data, you can delete the grid extension with the following statement:

drop extension postgis_raster;

You may want to ask, why does this statement run twice? The reason is that running the first one will separate the grid part from the extension and separate the functions and types; When you run the second, you can rebind these functions and types into the extension.

select postgis_extensions_upgrade();
postgis
postgis_raster

Unfortunately, the official failed to complete this process through a single PG function, because the official author forgot why he didn’t write it. It seems that it is because the extension upgrade, installation and creation of a new extension of PostgreSQL can’t be carried out in the same transaction.

PostgreSQL 13 removes the support of and adds some complexity. Therefore, it is best to upgrade PostGIS 3.0 before upgrading PostgreSQL 13 or later x.

create extension ... from unpackaged