How to install PostGIS extension in RDS

PostGIS is an extension of PostgreSQL database. This article explains the steps to install PostGIS in RDS.

Step1: Connect to the DB instance as the exact same user that used to create the database using psql.

#psql -h <host> -U <username> -p <port>

Here we need to replace host, username and password with the corresponding values.

Step2: Create PostGIS extensions.

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

Step3: Check the ownership of scehemas and it will be rdsadmin.

>\dn;

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

Step4: Transfer ownership of the extensions to the rds_superuser.

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

Step5: Make sure the ownership got updated.

>\dn

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

Step6: Transfer ownership of PostGIS objects to the rds_superuser.

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

Step7: Execute the following 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;

Now the PostGIS extension will be available for use.

That's all…

Leave a Reply