Thread: Restore from dumps
Hello,
We perform a pg_dump in production to restore daily in a preprod env.
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.
Can anyone help?
Thank you,
Nicola
> 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.
Hello
these are the commands we use pretty much:
tar -xf tarname.tar -C /backupdatadir --strip-components=4
pg_restore -l /backupdatadir | sed '/MATERIALIZED VIEW DATA/d' > /restore.lst
pg_restore -U postgres -L /restore.lst -d DBNAME -j 32 /backupdatadir
vacuumdb --analyze-in-stages -U postgres --jobs 32 -d DBNAME
pg_restore -l /backupdatadir | grep 'MATERIALIZED VIEW DATA' > /refresh.lst
pg_restore -U postgres -L /refresh.lst -d DBNAME -j 32 /backupdatadir
all_days is a table yes. bdays instead is a function and it include in the first file.
Both servers have 10.4 for psql commands, we take the backup with 10.4 and we restore with 10.4
We used to have postgres9.6.6 in production and pì10.4 in preprod, and the restore went always fine. After switching to 10.4 in prod we started having the problem.
2018-07-25 11:28 GMT+02:00 Alban Hertroys <haramrae@gmail.com>:
> 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
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.albe@cybertec.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; I suspect that it has to do with the recent security fixes around the "public" schema.
>
> 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.
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
That worked.
Thanks guys.
2018-07-25 16:33 GMT+02:00 Nicola Contu <nicola.contu@gmail.com>:
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