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 trouble shooting 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;

This 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