hdknr / note

Markdown Texts
http://qiita.com/hidelafoglia
7 stars 1 forks source link

psql: ERROR: permission denied for relation spatial_ref_sys #11

Open hdknr opened 8 years ago

hdknr commented 8 years ago
pg_dump -h $SRC -U $DBU $DBN > $DUMP 
dropdb -h $DST -U $DBU $DBN
createdb -h $DST -U $DBU $DBN
cat $DUMP | psql -h $DST -U $DBU $DBN
ERROR:  permission denied for relation spatial_ref_sys
invalid command \.
hdknr commented 8 years ago
$ echo "CREATE EXTENSION postgis;" | psql -h $DST -U $DBU $DBN
CREATE EXTENSION
$ echo "CREATE EXTENSION topology;" | psql -h $DST -U $DBU $DBN

ERROR:  Extension "topology" is not supported by Amazon RDS
DETAIL:  Installing the extension "topology" failed, because it is not on the list of extensions supported by Amazon RDS.
HINT:  Amazon RDS allows users with rds_superuser role to install supported extensions. See: SHOW rds.extensions;
hdknr commented 8 years ago
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; 
hdknr commented 8 years ago
$ sed -n '1901813,  1901820p' ~/dmp.sql 
-- Data for Name: spatial_ref_sys; Type: TABLE DATA; Schema: public; Owner: rdsadmin
--

COPY spatial_ref_sys  FROM stdin;
\.

--
hdknr commented 8 years ago

  CREATE TABLE SPATIAL_REF_SYS ( 
    SRID INTEGER NOT NULL PRIMARY KEY, 
    AUTH_NAME VARCHAR(256), 
    AUTH_SRID INTEGER, 
    SRTEXT VARCHAR(2048), 
    PROJ4TEXT VARCHAR(2048)
  )
ecoroute=# \d+ spatial_ref_sys;
                             Table "public.spatial_ref_sys"
  Column   |          Type           | Modifiers | Storage  | Stats target | Description 
-----------+-------------------------+-----------+----------+--------------+-------------
 srid      | integer                 | not null  | plain    |              | 
 auth_name | character varying(256)  |           | extended |              | 
 auth_srid | integer                 |           | plain    |              | 
 srtext    | character varying(2048) |           | extended |              | 
 proj4text | character varying(2048) |           | extended |              | 
Indexes:
    "spatial_ref_sys_pkey" PRIMARY KEY, btree (srid)
Check constraints:
    "spatial_ref_sys_srid_check" CHECK (srid > 0 AND srid <= 998999)
Has OIDs: no
ecoroute=> select count(*) from spatial_ref_sys ;
 count 
-------
  3911
(1 row)
ecoroute=> \d+ spatial_ref_sys;
                             Table "public.spatial_ref_sys"
  Column   |          Type           | Modifiers | Storage  | Stats target | Description 
-----------+-------------------------+-----------+----------+--------------+-------------
 srid      | integer                 | not null  | plain    |              | 
 auth_name | character varying(256)  |           | extended |              | 
 auth_srid | integer                 |           | plain    |              | 
 srtext    | character varying(2048) |           | extended |              | 
 proj4text | character varying(2048) |           | extended |              | 
Indexes:
    "spatial_ref_sys_pkey" PRIMARY KEY, btree (srid)
Check constraints:
    "spatial_ref_sys_srid_check" CHECK (srid > 0 AND srid <= 998999)
Has OIDs: no
ecoroute=> select count(*) from spatial_ref_sys ;
 count 
-------
  3911
(1 row)