PostgreSQL Extensions

Extensions are modules that supply extra functions, operators, or types. Many extensions are available in Compose PostgreSQL. To view the available extensions, select Browser then select a database from the available databases. In the side-bar menu for the database is a tab for Extensions which lists all of the available extensions.

Extensions page in the data browser.Extensions page in the data browser.

Extensions page in the data browser.

A green circle at the start of a row indicates the extension is installed. That is followed by the extension name, version and description and finally an Install/Remove button which can toggle the availability of the extension.

PostGIS

PostGIS adds support for geographic objects allowing location queries to be run in SQL. It is part of a popular series of articles about using geographical data. If you are interested, get started with an example using OpenStreetMap data. Full documentation for PostGIS is found on their website.

PostGIS 2.2.5 to PostGIS 2.3.2

The PostGIS version available on all current deployments is 2.2.5. In order to upgrade to 2.3.2 or newer will require a completely new deployment, or a support ticket.

If you want to upgrade to 2.3.2, you will have to create a fresh Postgres 9.6.3 deployment, and unfortunately it can not be a restore from a backup.

To get PostGIS 2.3.2 running on a completely fresh deployment:

  • first add the PostGIS 2.2.5 extension from the extensions panel in the Browser
  • then run the following in psql: SELECT upgrade_postgis_23x();

If you need to get PostGIS 2.3.2 on an existing deployment, you will have to open a ticket with support and they can assist you in getting it upgraded.

Upgrading Extensions

The version number in the Version column of the extensions list is the newest version that is available on Compose. The version that is currently installed on the database is listed in the Description column. For example, this database has the uuid-ossp version 1.0 installed, but 1.1 is now available.

Details of the installed extension uuid-ossp.Details of the installed extension uuid-ossp.

Details of the installed extension uuid-ossp.

You may upgrade to the newest version of an extension through psql. To upgrade to the newest available version of uuid-ossp, run the following command:
ALTER EXTENSION "uuid-ossp" UPDATE TO "1.1";
Refresh the extensions list and the Description field will update.

Upgraded uuid-ossp.Upgraded uuid-ossp.

Upgraded uuid-ossp.

List of Extensions

address_standardizer    
address_standardizer_data_us
autoinc 
bloom       
btree_gin       
btree_gist      
chkpass     
citext      
cube        
dblink      
dict_int        
dict_xsyn   
earthdistance   
file_fdw        
fuzzystrmatch   
hstore      
hstore_plperl   
hstore_plperlu      
insert_username 
intagg      
intarray    
isn     
lo      
ltree   
moddatetime     
pageinspect     
pg_buffercache      
pg_freespacemap     
pg_prewarm      
pg_stat_statements      
pg_trgm     
pg_visibility       
pgcrypto    
pgrouting       
pgrowlocks      
pgstattuple     
plcoffee        
plls        
plperl      
plpgsql     
plv8    
postgis 
postgis_tiger_geocoder  
postgis_topology        
postgres_fdw        
refint  
seg     
sslinfo     
tablefunc       
tcn     
timetravel  
tsearch2    
tsm_system_rows 
tsm_system_time     
unaccent    
uuid-ossp

Still Need Help?

If this article didn't solve things, summon a human and get some help!