Re: Restore from dumps

Thanks Laurenz
I will try that.
Btw, just wondering why it works if I refresh it later, even if the
definition is still without public

2018-07-25 12:06 GMT+02:00 Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>:

> Nicola Contu wrote:
> > we recently moved from postgres 9.6.6 to 10.4
> >
> > We perform a pg_dump in production to restore daily in a preprod env.
> > This process used to work perfectly, but now we have a tiny problem.
> >
> > We first restore data, we perform a vacuum and then we restore matviews.
> > Restoring matviews now we have :
> >
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 23090; 0 1912379424
> MATERIALIZED VIEW DATA matview_vrs_request_sla postgres
> > pg_restore: [archiver (db)] could not execute query: ERROR: relation
> "all_days" does not exist
> > LINE 3: from all_days
> > ^
> > QUERY:
> > select count(*)::numeric
> > from all_days
> > where (("date" between $2::date and $1::date) or ("date" between
> $1::date and $2::date))
> > and dow not in (0,6)
> >
> > CONTEXT: SQL function "bdays" during inlining
> > Command was: REFRESH MATERIALIZED VIEW public.matview_vrs_request_
> sla;
> >
> > The relation is there, in fact if I go there when I get in to the
> office, the same command works.
> >
> > I'm not sure why it does not work here, this seems really strange to me.
>
> I suspect that it has to do with the recent security fixes around the
> "public" schema.
>
> Try to ALTER the materialized view so that it refers to "public.all_days"
> rather than "all_days".
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>