0 votes
1 view
in AWS by (19.2k 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.6k points)

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

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...