I am trying to dump the schema from one of my PostgreSQL 9.3+PostGIS databases:

/usr/lib/postgresql/9.3/bin/pg_dump --schema-only 
                                    -h stg-db.....rds.amazonaws.com 
                                    -U user_name db_name > stg_db_schema.sql

The command works well on my dev db, but fails on stg and prod due to:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for relation direction_lookup
pg_dump: [archiver (db)] query was: LOCK TABLE tiger.direction_lookup IN ACCESS SHARE MODE

The problem seems to be permissions for tiger.direction_lookup. I Googled a bit, and this table seems to be related to PostGIS.

I have tried --exclude-table-data=tiger.direction_lookup, with no effect (it is not supposed to work for schemas anyway).

Any idea how to be able to dump the schema of my DB?


It seems that I had to re-run the AWS RDS PostGIS instructions:

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')

This is clearly an RDS Bug, because these instructions should not be executed more than once.

Perhaps this happened due to a database minor version upgrade.

  • Thanks Adam, your answer was little confusing, but it did drive me to right direction. so this alter statement help me create dump. > ALTER TABLE tiger.direction_lookup OWNER TO rds_superuser; – Virendra Patel Dec 6 '19 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.