Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in AWS by (19.1k points)

Ok, so I am fairly new to RDS and AWS, but I can't for the life of me, get the database that I created on my laptop, onto amazon RDS, I did move it to my test VPS and also my desktop machine, with no problems at all, here is what I have tried so far..

create extension postgis;

create extension fuzzystrmatch;

create extension postgis_tiger_geocoder;

create extension postgis_topology;

alter schema tiger owner to rds_superuser;

alter schema topology owner to rds_superuser;

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

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;        

GRANT ALL PRIVILEGES ON DATABASE testgetwork TO luke;

Then I connect to the database and try and load my local copy of the new database...

[email protected]:~$ psql --host=myhost.dns.us-west-2.rds.amazonaws.com --port=5432 --username=luke --password --dbname=testgetwork < testgetwork.sql 

Password for user luke: 

SET

SET

SET

SET

SET

ERROR:  schema "topology" already exists

ALTER SCHEMA

CREATE EXTENSION

ERROR:  must be owner of extension plpgsql

CREATE EXTENSION

ERROR:  must be owner of extension postgis

CREATE EXTENSION

ERROR:  must be owner of extension postgis_topology

<more output, removed to save space>

ERROR:  permission denied for relation spatial_ref_sys

invalid command \.

 setval 

-------- 

      1

(1 row)

 setval 

<more output, removed to save space>

My question is, how can I move on from here? All of the features just seemed to be blocked by a permissions problem, by user luke is part of the rds_superuser group, does anyone who has experience with PostGIS and RDS know how I can rectify this?

testgetwork=> \du

                                  List of roles

   Role name   |         Attributes         | Member of    

---------------+------------------------------------------------+-----------------

 luke          | Create role, Create DB                         | {rds_superuser}

 rds_superuser | Cannot login                                   | {}

 rdsadmin      | Superuser, Create role, Create DB, Replication | {}

1 Answer

0 votes
by (44.3k points)

This official AWS documentation for Working with PostGIS on RDS is good and has the solution for this.

Browse Categories

...