Tuning for PostGIS performance is much like tuning for any PostgreSQL workload. The only additional consideration is that geometries and rasters are usually large, so memory-related optimizations generally have more of an impact on PostGIS than other types of PostgreSQL queries.
For general details about optimizing PostgreSQL, refer to Tuning your PostgreSQL Server.
For PostgreSQL 9.4+ configuration can be set at the server level without touching postgresql.conf or postgresql.auto.conf
by using the ALTER SYSTEM command.
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;
In addition to the Postgres settings, PostGIS has some custom settings which are listed in Section 5.23, “Grand Unified Custom Variables (GUCs)”.
These settings are configured in postgresql.conf:
Default: partition
This is generally used for table partitioning. The default for this is set to "partition" which is ideal for PostgreSQL 8.4 and above since it will force the planner to only analyze tables for constraint consideration if they are in an inherited hierarchy and not pay the planner penalty otherwise.
Default: ~128MB in PostgreSQL 9.6
Set to about 25% to 40% of available RAM. On windows you may not be able to set as high.
max_worker_processes This setting is only available for PostgreSQL 9.4+. For PostgreSQL 9.6+ this setting has additional importance in that it controls the max number of processes you can have for parallel queries.
Default: 8
Sets the maximum number of background processes that the system can support. This parameter can only be set at server start.
work_mem - sets the size of memory used for sort operations and complex queries
Default: 1-4MB
Adjust up for large dbs, complex queries, lots of RAM
Adjust down for many concurrent users or low RAM.
If you have lots of RAM and few developers:
SET work_mem TO '256MB';
maintenance_work_mem - the memory size used for VACUUM, CREATE INDEX, etc.
Default: 16-64MB
Generally too low - ties up I/O, locks objects while swapping memory
Recommend 32MB to 1GB on production servers w/lots of RAM, but depends on the # of concurrent users. If you have lots of RAM and few developers:
SET maintenance_work_mem TO '1GB';
max_parallel_workers_per_gather
This setting is only available for PostgreSQL 9.6+ and will only affect PostGIS 2.3+, since only PostGIS 2.3+ supports parallel queries.
If set to higher than 0, then some queries such as those involving relation functions like ST_Intersects can use multiple processes and can run
more than twice as fast when doing so. If you have a lot of processors to spare, you should change the value of this to as many processors as you have.
Also make sure to bump up max_worker_processes to at least as high as this number.
Default: 0
Sets the maximum number of workers that can be started by a single
Gathernode. Parallel workers are taken from the pool of processes established bymax_worker_processes. Note that the requested number of workers may not actually be available at run time. If this occurs, the plan will run with fewer workers than expected, which may be inefficient. Setting this value to 0, which is the default, disables parallel query execution.
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 5.23, “Grand Unified Custom Variables (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"
![[Note]](images/note.png) | |
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/ where 10 refers to version of PostgreSQL and main refers to the cluster.10/main/environment
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.
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
| |
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.1/
# 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
Some packaged distributions of PostGIS (in particular the Win32 installers
for PostGIS >= 1.1.5) load the PostGIS functions into a template
database called template_postgis. If the
template_postgis database exists in your PostgreSQL
installation then it is possible for users and/or applications to create
spatially-enabled databases using a single command. Note that in both
cases, the database user must have been granted the privilege to create
new databases.
From the shell:
# createdb -T template_postgis my_spatial_db
From SQL:
postgres=# CREATE DATABASE my_spatial_db TEMPLATE=template_postgis
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.
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, then you should upgrade using the sql script way. Please refer to the appropriate.
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.1.12dev/*_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
| |
If you can't find the
|
The PostGIS_Full_Version function should inform you about the need to run this kind of upgrade using a "procs need upgrade" message.
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.
ALTER EXTENSION postgis UPDATE TO "3.1.12dev"; ALTER EXTENSION postgis_topology UPDATE TO "3.1.12dev";
If you get an error notice something like:
No migration path defined for ... to 3.1.12dev
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 ontop of this new database.
If you get a notice message like:
Version "3.1.12dev" 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.1.12devnext"; ALTER EXTENSION postgis_topology UPDATE TO "3.1.12devnext";
| |
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 |
| |
If you are upgrading PostGIS extension from a version prior to 3.0.0 you'll end up with an unpackaged PostGIS Raster support. You can repackage the raster support using:
CREATE EXTENSION postgis_raster FROM unpackaged;
And then, if you don't need it, drop it with:
DROP EXTENSION postgis_raster;
|
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.pl 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.sqlfor 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 loadinguninstall_legacy.sql.Restore your backup into your fresh
newdbdatabase using postgis_restore.pl. Unexpected errors, if any, will be printed to the standard error stream by psql. Keep a log of those.perl utils/postgis_restore.pl "/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.sqlscript 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 thelegacy.sqlway works for you, don't forget to fix your code to stop using deprecated functions and drop them loadinguninstall_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.pl. So, after having run :
perl utils/postgis_restore.pl "/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