postgresql 在 Amazon RDS 上设置 PostGis
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21838349/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting up PostGis on Amazon RDS
提问by Crooksey
Ok, so I am fairly new to RDS and AWS, but I can't for the life of me, get my 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..
好的，所以我对 RDS 和 AWS 还很陌生，但我终生无法将我在笔记本电脑上创建的数据库放到亚马逊 RDS 上，我确实将它移到了我的测试 VPS 和我的台式机上，完全没有问题，这是我迄今为止尝试过的..
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 ; RETURN ; 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...
然后我连接到数据库并尝试加载新数据库的本地副本...
luke@l-xps:~$ 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?
我的问题是，我怎样才能从这里继续前进？所有功能似乎都被权限问题阻止了，用户 luke 是 rds_superuser 组的一部分，有使用 PostGis 和 RDS 经验的人知道我该如何解决这个问题吗？
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 | {}
回答by asbjornenge
The official guide for setting up PostGIS on RDS is here.
在 RDS 上设置 PostGIS 的官方指南在这里。
That worked for me.
那对我有用。
I am still having issues import my data, getting:
我仍然有问题导入我的数据，得到：
...
must be superuser to create a base type
permission denied for language c
....
Any ideas on those?
Also, is it possible to log in as rdsadmin?
对这些有什么想法吗？
另外，是否可以以 rdsadmin 身份登录？
