Chapter 3. PostGIS Administration

Chapter 3. PostGIS Administration
Prev		Next

3.1. 性能调优

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

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

对于 PostgreSQL 9.4+，可以使用 ALTER SYSTEM 命令在服务器级别设置配置，而无需触及 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"，适用于 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. Configuring raster support

If you enabled raster support you may want to read below how to properly configure it.

As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment. For PostGIS 2.2, you can use the more cross-platform approach of setting the corresponding Section 7.22, “大一统自定义变量 (GUCs)”.

If you want to enable offline raster:

POSTGIS_ENABLE_OUTDB_RASTERS=1

Any other setting or no setting at all will disable out of db rasters.

In order to enable all GDAL drivers available in your GDAL install, set this environment variable as follows

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

If you want to only enable specific drivers, set your environment variable as follows:

POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF XYZ"


	If you are on windows, do not quote the driver list

Setting environment variables varies depending on OS. For PostgreSQL installed on Ubuntu or Debian via apt-postgresql, the preferred way is to edit /etc/postgresql/10/main/environment where 10 refers to version of PostgreSQL and main refers to the cluster.

On windows, if you are running as a service, you can set via System variables which for Windows 7 you can get to by right-clicking on Computer->Properties Advanced System Settings or in explorer navigating to Control Panel\All Control Panel Items\System. Then clicking Advanced System Settings ->Advanced->Environment Variables and adding new system variables.

After you set the environment variables, you'll need to restart your PostgreSQL service for the changes to take effect.

3.3. Creating spatial databases

3.3.1. Spatially enable database using EXTENSION

If you are using PostgreSQL 9.1+ and have compiled and installed the extensions/postgis modules, you can turn a database into a spatial one using the EXTENSION mechanism.

Core postgis extension includes geometry, geography, spatial_ref_sys and all the functions and comments. Raster and topology are packaged as a separate extension.

Run the following SQL snippet in the database you want to enable spatially:

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

3.3.2. Spatially enable database without using EXTENSION (discouraged)


	This is generally only needed if you cannot or don't want to get PostGIS installed in the PostgreSQL extension directory (for example during testing, development or in a restricted environment).

Adding PostGIS objects and function definitions into your database is done by loading the various sql files located in [prefix]/share/contrib as specified during the build phase.

The core PostGIS objects (geometry and geography types, and their support functions) are in the postgis.sql script. Raster objects are in the rtpostgis.sql script. Topology objects are in the topology.sql script.

For a complete set of EPSG coordinate system definition identifiers, you can also load the spatial_ref_sys.sql definitions file and populate the spatial_ref_sys table. This will permit you to perform ST_Transform() operations on geometries.

If you wish to add comments to the PostGIS functions, you can find them in the postgis_comments.sql script. Comments can be viewed by simply typing \dd [function_name] from a psql terminal window.

Run the following Shell commands in your terminal:

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. Upgrading spatial databases

Upgrading existing spatial databases can be tricky as it requires replacement or introduction of new PostGIS object definitions.

Unfortunately not all definitions can be easily replaced in a live database, so sometimes your best bet is a dump/reload process.

PostGIS provides a SOFT UPGRADE procedure for minor or bugfix releases, and a HARD UPGRADE procedure for major releases.

Before attempting to upgrade PostGIS, it is always worth to backup your data. If you use the -Fc flag to pg_dump you will always be able to restore the dump with a HARD UPGRADE.

3.4.1. Soft upgrade

If you installed your database using extensions, you'll need to upgrade using the extension model as well. If you installed using the old sql script way, you are advised to switch your install to extensions because the script way is no longer supported.

3.4.1.1. Soft Upgrade 9.1+ using extensions

If you originally installed PostGIS with extensions, then you need to upgrade using extensions as well. Doing a minor upgrade with extensions, is fairly painless.

If you are running PostGIS 3 or above, then you should use the PostGIS_Extensions_Upgrade function to upgrade to the latest version you have installed.

SELECT postgis_extensions_upgrade();

If you are running PostGIS 2.5 or lower, then do the following:

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

If you have multiple versions of PostGIS installed, and you don't want to upgrade to the latest, you can explicitly specify the version as follows:

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

If you get an error notice something like:

No migration path defined for … to 3.5.4dev

Then you'll need to backup your database, create a fresh one as described in Section 3.3.1, “Spatially enable database using EXTENSION” and then restore your backup on top of this new database.

If you get a notice message like:

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

Then everything is already up to date and you can safely ignore it. UNLESS you're attempting to upgrade from an development version to the next (which doesn't get a new version number); in that case you can append "next" to the version string, and next time you'll need to drop the "next" suffix again:

ALTER EXTENSION postgis UPDATE TO "3.5.4devnext";
ALTER EXTENSION postgis_topology UPDATE TO "3.5.4devnext";


	If you installed PostGIS originally without a version specified, you can often skip the reinstallation of postgis extension before restoring since the backup just has `CREATE EXTENSION postgis` and thus picks up the newest latest version during restore.

If you are upgrading PostGIS extension from a version prior to 3.0.0, you will have a new extension postgis_raster which you can safely drop, if you don't need raster support. You can drop as follows:

DROP EXTENSION postgis_raster;

3.4.1.2. Soft Upgrade Pre 9.1+ or without extensions

This section applies only to those who installed PostGIS not using extensions. If you have extensions and try to upgrade with this approach you'll get messages like:

can't drop … because postgis extension depends on it

NOTE: if you are moving from PostGIS 1.* to PostGIS 2.* or from PostGIS 2.* prior to r7409, you cannot use this procedure but would rather need to do a HARD UPGRADE.

After compiling and installing (make install) you should find a set of *_upgrade.sql files in the installation folders. You can list them all with:

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

Load them all in turn, starting from postgis_upgrade.sql.

psql -f postgis_upgrade.sql -d your_spatial_database

The same procedure applies to raster, topology and sfcgal extensions, with upgrade files named rtpostgis_upgrade.sql, topology_upgrade.sql and sfcgal_upgrade.sql respectively. If you need them:

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

You are advised to switch to an extension based install by running

psql -c "SELECT postgis_extensions_upgrade();"


	If you can't find the `postgis_upgrade.sql` specific for upgrading your version you are using a version too early for a soft upgrade and need to do a HARD UPGRADE.

The PostGIS_Full_Version function should inform you about the need to run this kind of upgrade using a "procs need upgrade" message.

3.4.2. Hard upgrade

By HARD UPGRADE we mean full dump/reload of postgis-enabled databases. You need a HARD UPGRADE when PostGIS objects' internal storage changes or when SOFT UPGRADE is not possible. The Release Notes appendix reports for each version whether you need a dump/reload (HARD UPGRADE) to upgrade.

The dump/reload process is assisted by the postgis_restore script which takes care of skipping from the dump all definitions which belong to PostGIS (including old ones), allowing you to restore your schemas and data into a database with PostGIS installed without getting duplicate symbol errors or bringing forward deprecated objects.

Supplementary instructions for windows users are available at Windows Hard upgrade.

The Procedure is as follows:

Create a "custom-format" dump of the database you want to upgrade (let's call it olddb) include binary blobs (-b) and verbose (-v) output. The user can be the owner of the db, need not be postgres super account.
```
pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb
```
Do a fresh install of PostGIS in a new database -- we'll refer to this database as newdb. Please refer to Section 3.3.2, “Spatially enable database without using EXTENSION (discouraged)” and Section 3.3.1, “Spatially enable database using EXTENSION” for instructions on how to do this.

The spatial_ref_sys entries found in your dump will be restored, but they will not override existing ones in spatial_ref_sys. This is to ensure that fixes in the official set will be properly propagated to restored databases. If for any reason you really want your own overrides of standard entries just don't load the spatial_ref_sys.sql file when creating the new db.

If your database is really old or you know you've been using long deprecated functions in your views and functions, you might need to load legacy.sql for all your functions and views etc. to properly come back. Only do this if _really_ needed. Consider upgrading your views and functions before dumping instead, if possible. The deprecated functions can be later removed by loading uninstall_legacy.sql.
Restore your backup into your fresh newdb database using postgis_restore. Unexpected errors, if any, will be printed to the standard error stream by psql. Keep a log of those.
```
postgis_restore "/somepath/olddb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt
```

Errors may arise in the following cases:

Some of your views or functions make use of deprecated PostGIS objects. In order to fix this you may try loading legacy.sql script prior to restore or you'll have to restore to a version of PostGIS which still contains those objects and try a migration again after porting your code. If the legacy.sql way works for you, don't forget to fix your code to stop using deprecated functions and drop them loading uninstall_legacy.sql.
Some custom records of spatial_ref_sys in dump file have an invalid SRID value. Valid SRID values are bigger than 0 and smaller than 999000. Values in the 999000.999999 range are reserved for internal use while values > 999999 can't be used at all. All your custom records with invalid SRIDs will be retained, with those > 999999 moved into the reserved range, but the spatial_ref_sys table would lose a check constraint guarding for that invariant to hold and possibly also its primary key ( when multiple invalid SRIDS get converted to the same reserved SRID value ).

In order to fix this you should copy your custom SRS to a SRID with a valid value (maybe in the 910000..910999 range), convert all your tables to the new srid (see UpdateGeometrySRID), delete the invalid entry from spatial_ref_sys and re-construct the check(s) with:
```
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));
    
```
If you are upgrading an old database containing french IGN cartography, you will have probably SRIDs out of range and you will see, when importing your database, issues like this :
```
WARNING: SRID 310642222 converted to 999175 (in reserved zone)
```
In this case, you can try following steps : first throw out completely the IGN from the sql which is resulting from postgis_restore. So, after having run :
```
postgis_restore "/somepath/olddb.backup" > olddb.sql
```
run this command :
```
grep -v IGNF olddb.sql > olddb-without-IGN.sql
```
Create then your newdb, activate the required Postgis extensions, and insert properly the french system IGN with : this script After these operations, import your data :
```
psql -h localhost -p 5432 -U postgres -d newdb -f olddb-without-IGN.sql  2> errors.txt
```