PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup

Question

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?

Answer 1

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