Обсуждение: Restore from dumps

> On 25 Jul 2018, at 9:43, Nicola Contu <nicola.contu@gmail.com> wrote:
>
> Hello,
> 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.

What are the commands you used? You don't seem to mention restoring the schema?

> 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;

Is all_days a table? Or is it perhaps another view, possibly materialized even?

> The relation is there, in fact if I go there when I get in to the office, the same command works.

This sounds to me like you may be using a different version of pg_restore in the office. Are both versions 10.4 or newer?
It can't hurt to check that you used version 10.4 of pg_dump as well.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

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

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Nicola Contu wrote:
>> 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

> Try to ALTER the materialized view so that it refers to "public.all_days"
> rather than "all_days".

Per the error message, what needs fixing is the SQL function "bdays",
not the matview as such.

                        regards, tom lane

yeah, we updated that function in production to says public.all_days.

I will let you know at the next restore.

Thanks guys, appreciated.

2018-07-25 16:28 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Nicola Contu wrote:
>> 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

> Try to ALTER the materialized view so that it refers to "public.all_days"
> rather than "all_days".

Per the error message, what needs fixing is the SQL function "bdays",
not the matview as such.

                        regards, tom lane