Part 1 of our PostGIS series is to help developers enable PostGIS for a PostgreSQL database in AWS.

Amazon’s Relational Database Service (RDS) allows users to launch PostgreSQL databases easily and quickly. PostgreSQL allows developers to extend the functionality of the core database via extensions.  For this reason, extensions loaded into the database can function just like features that are built in.

Because this database is on Amazon’s servers, they have limitations on what extensions the will allow to be hosted on their service.  These limitations are mostly for performance reasons.

PostGIS Version

By default, the database has PostGIS installed on it. This isn’t critical to run, but it may be helpful in your troubleshooting efforts.

To get the current version, use this:

SELECT PostGIS_full_version();

Result:

POSTGIS=”2.3.2 r15302″ GEOS=”3.5.1-CAPI-1.9.1 r4246″ PROJ=”Rel. 4.9.3, 15 August 2016″ GDAL=”GDAL 2.1.3, released 2017/20/01″ LIBXML=”2.9.1″ LIBJSON=”0.12″ RASTER

Normal Installation Guidance

If you follow PostGIS’s guidance here, you will get the following error.

When you run this command:
CREATE EXTENSION postgis_sfcgal;

ERROR: Extension “postgis_sfcgal” is not supported by Amazon RDS
DETAIL: Installing the extension “postgis_sfcgal” failed, because it is not on the list of extensions supported by Amazon RDS.
HINT: Amazon RDS allows users with rds_superuser role to install supported extensions. See: SHOW rds.extensions;

AWS RDS PosGIS Installation Guidance

AWS covers enabling PosGIS via their common database administrator (DBA) tasks here.

In about 3 minutes, you can run through these commands and enable PosGIS tables in your database.

AWS Supported Extensions

This is a pretty nifty command to see what extensions are displayed on the device.

SHOW rds.extensions;

Here is a list of the Posgresql Extentions that RDS does support:

address_standardizer
address_standardizer_data_us
bloom
btree_gin
btree_gist
chkpass
citext
cube
dblink
dict_int
dict_xsyn
earthdistance
fuzzystrmatch
hll
hstore

hstore_plperl
intagg
intarray

ip4r
isn
log_fdw
ltree
pgaudit

pgcrypto
pgrouting
pgrowlocks
pgstattuple
pg_buffercache
pg_freespacemap
pg_hint_plan
pg_prewarm
pg_repack
pg_stat_statements
pg_trgm
pg_visibility
plcoffee

plls
plperl
plpgsql

pltcl
plv8
postgis
postgis_tiger_geocoder
postgis_topology
postgres_fdw
sslinfo
tablefunc
test_parser
tsearch2
tsm_system_rows
tsm_system_time
unaccent
uuid-ossp