TECHIES WORLD

For Techs.... Techniques.... Technologies....

AWSLinux

How to install PostGIS extension in RDS

admin

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