Re: BUG #13457: postgres_fdw, non-postgres user mapping, materialized view leads to failed pg_upgrade

I'm running into this same problem using pg_dumpall.

On Sun, Jun 21, 2015 at 9:20 PM, <gregburek@heroku.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13457
> Logged by:          Greg Burek
> Email address:      gregburek@heroku.com
> PostgreSQL version: 9.4.2
> Operating system:   Linux
> Description:
>
> Hello,
>
> A customer has run into a strange interaction between the postgres_fdw
> extension, materialized view and pg_restore, as used by pg_upgrade.
>
> Reproduction schema:
>
> CREATE SERVER redshift_dw FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
>     dbname 'reporting',
>     host 'example.com',
>     port '5439',
>     sslmode 'require'
> );
> ALTER SERVER redshift_dw OWNER TO u5cuus46hhtdfs;
> CREATE USER MAPPING FOR u5cuus46hhtdfs SERVER redshift_dw OPTIONS (
>     password '',
>     "user" 'user'
> );
>
> CREATE MATERIALIZED VIEW daily_stats_mv AS
>  SELECT daily_stats_v.campaign_id,
>     daily_stats_v.targeting_group_id,
>     daily_stats_v.creative_id,
>     daily_stats_v.date,
>     daily_stats_v.impressions,
>     daily_stats_v.clicks,
>     daily_stats_v.media_cost,
>     daily_stats_v.spend,
>     daily_stats_v.serving_fees
>    FROM daily_stats_v
>   WITH NO DATA;
> ALTER TABLE public.daily_stats_mv OWNER TO u5cuus46hhtdfs;
>
> When running upgrading from postgres version 9.3.5 to 9.4.2, the pg_upgrade
> command fails with logs that include:
>
> pg_restore: creating MATERIALIZED VIEW daily_stats_mv
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 354; 1259 266280
> MATERIALIZED VIEW daily_stats_mv u5cuus46hhtdfs
> pg_restore: [archiver (db)] could not execute query: ERROR:  user mapping
> not found for "postgres"
>     Command was:
> -- For binary upgrade, must preserve pg_type oid
> SELECT binary_upgrade.set_next_pg_type_oid('266282'::pg_catalog.oid);
>
> It appears that as part of the pg_upgrade script, the db is run through
> pg_dump and pg_restore. The schema generated by pg_dump appears to attempt
> to create the MATERIALIZED VIEW daily_stats_mv as user postgres and then
> change ownership to user u5cuus46hhtdfs. The table create fails because the
> postgres_fdw that the materialized view is based on has no user mapping for
> the postgres user, even though the correct user is set as the next
> statement.
>
> Should the schema be rendered by pg_dump so that the materialized view is
> created as the intended user to avoid a trip through the postgres user,
> which may or may not have a user mapping that dictates if the materialized
> view may be created?
>
> User worked around the issue by dropping the fdw and the materialized view
> before performing a dump and restore upgrade.
>
> Greg
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>