Working with the PostGIS extension

PostGIS is an extension to PostgreSQL for storing and managing spatial information. If you are not familiar with PostGIS, see PostGIS.net.

You need to perform some setup before you can use the PostGIS extension. The following list shows what you need to do. Each step is described in greater detail later in this section.

Step 1: Connect to the DB instance using the user name used to create the DB instance

First, you connect to the DB instance using the user name that was used to create the DB instance. That name is automatically assigned the rds_superuser role. You need the rds_superuser role to do the remaining steps.

The following example uses SELECT to show you the current user. In this case, the current user should be the user name you chose when creating the DB instance.

SELECT CURRENT_USER;

 current_user
 -------------
  myawsuser
 (1 row) 

Step 2: Load the PostGIS extensions

Use CREATE EXTENSION statements to load the PostGIS extensions.

CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION

You can verify the results by running the SQL query shown in this example, which lists the extensions and their owners.

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;
List of schemas
     Name     |   Owner
--------------+-----------
 public       | myawsuser
 tiger        | rdsadmin
 tiger_data   | rdsadmin
 topology     | rdsadmin
(4 rows) 

If you use the psql command-line, you can obtain the same information by running the \dn meta-command.

Note

Extra extensions aren't required for some use cases.

Step 3: Transfer ownership of the extensions to the rds_superuser role

Use the ALTER SCHEMA statements to transfer ownership of the schemas to the rds_superuser role.

ALTER SCHEMA tiger OWNER TO rds_superuser;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO rds_superuser; 
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO rds_superuser;
ALTER SCHEMA

If you want to confirm the ownership change, you can run this SQL query (or, you can use the \dn meta-command from the psql command-line).

SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
  FROM pg_catalog.pg_namespace n
  WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
  ORDER BY 1;

       List of schemas
     Name     |     Owner
--------------+---------------
 public       | myawsuser
 tiger        | rds_superuser
 tiger_data   | rds_superuser
 topology     | rds_superuser
(4 rows) 

Step 4: Transfer ownership of the objects to the rds_superuser role

Use the following function to transfer ownership of the PostGIS objects to the rds_superuser role. Run the following statement from the psql prompt to create the function.

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;

Next, run this query to run the exec function that in turn runs the statements and alters the permissions.

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser;')
  FROM (
    SELECT nspname, relname
    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
    WHERE nspname in ('tiger','topology') AND
    relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;

Step 5: Test the extensions

Add tiger to your search path using the following command.

SET search_path=public,tiger;

Test tiger by using the following SELECT statement.

SELECT na.address, na.streetname, na.streettypeabbrev, na.zip
FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev |  zip
---------+------------+------------------+-------
       1 | Devonshire | Pl               | 02109
(1 row) 

Test topology by using the following SELECT statement.

SELECT topology.createtopology('my_new_topo',26986,0.5);
 createtopology
----------------
              1
(1 row) 

Step 6: Update the PostGIS extension

PostgreSQL minor versions 13.4, 12.8, 11.13, and 10.18 now support version 3.1.4 of the PostGIS extension. This support makes it easier to upgrade to later major versions of PostgreSQL.

If you have an older version of PostGIS installed, use the following command to upgrade your extension from the older version to 3.1.4. This function is available with PostGIS 2.5.0 and higher versions.

SELECT PostGIS_Extensions_Upgrade();

Depending on the version that you're upgrading from, this function might need to run a second time. The result of the first run of the function determines if an additional upgrade function is needed.

If your application doesn't support the latest PostGIS version, you can still create an older version of PostGIS that is available in your major version as follows.

CREATE EXTENSION postgis VERSION "2.5.5"

If you want to upgrade to a specific PostGIS VERSION from an older version, you can also use the following command.

ALTER EXTENSION postgis UPDATE TO "2.5.5"

PostGIS extension versions

You can list the versions that are available in your release by using the following command.

SELECT * from pg_available_extension_versions where name='postgis';

You can also obtain information about versions for PostGIS and all other extensions for each release of RDS for PostgreSQL on the following pages:

• PostgreSQL version 13 extensions supported on Amazon RDS

• PostgreSQL version 12 extensions supported on Amazon RDS

• PostgreSQL version 11.x extensions supported on Amazon RDS

• PostgreSQL version 10.x extensions supported on Amazon RDS

• PostgreSQL version 9.6.x extensions supported on Amazon RDS

Note

PostgreSQL 10.5 added support for the libprotobuf extension version 1.3.0 to the PostGIS component.