Backup/restore problem

The problem occurs when we make backup of a db on server version 11 or 12
and restore on the same server version. When we restore on server version
12 a backup made on server version 9.6 this problem DOES NOT occur.

The commands we execute are:

sudo -u postgres pg_dump om > om1.sql

sudo -u postgres psql om1 < om1.sql

There is a function chk_f_part_pricetar used for CHECK CONSTRAINT.

CREATE FUNCTION chk_f_part_pricetar(_customer integer, _pricetar integer)
RETURNS boolean
LANGUAGE plpgsql
AS $$
BEGIN
RETURN EXISTS(SELECT * FROM customers c JOIN nom.firms f ON c.invoiced_by
= f.id
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Ff.id&data=02%7C01%7C%7Cda2813cfe6e04fc37f3308d759318a39%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637075941608798780&sdata=yVnUSYC8ukKSdnKYATkXR87xVTXCP41dBdVGJuaj6xQ%3D&reserved=0>
JOIN prices.pricetar t ON f.id
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Ff.id&data=02%7C01%7C%7Cda2813cfe6e04fc37f3308d759318a39%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637075941608808791&sdata=V2lN6gCqiODa8EQEvwvmyxgfhiEcvNF7OL%2Bsrw7fp3s%3D&reserved=0>
=
t.firm
WHERE c.id
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fc.id&data=02%7C01%7C%7Cda2813cfe6e04fc37f3308d759318a39%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637075941608818797&sdata=sGGDWt7FkW%2FS%2B%2FqXwsU3A8w4A5hmTQcN2N8fq5WncsE%3D&reserved=0>
=
_customer AND t.id
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Ft.id&data=02%7C01%7C%7Cda2813cfe6e04fc37f3308d759318a39%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637075941608828796&sdata=CiZDXtS2Z%2FDW9s81ld80ruOYPxg5XET77mPbyFpWFPI%3D&reserved=0>
=
_pricetar) OR _pricetar IS NULL;
END;$$;

It seems that although previous versions of server checked only for syntax
the new ones check for availability of objects in the database. You can
download the created from the command *sudo -u postgres pg_dump om >
om1.sql* backup from attached file.

You can also have a look at the following log:
/var/log/postgresql/postgresql-12-main.log

2019-10-25 12:39:34.119 EEST [41989] postgres(at)om1 ERROR: relation
"customers" does not exist at character 29
2019-10-25 12:39:34.119 EEST [41989] postgres(at)om1 QUERY: SELECT
EXISTS(SELECT * FROM customers c JOIN nom.firms f ON c.invoiced_by = f.id
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Ff.id&data=02%7C01%7C%7Cda2813cfe6e04fc37f3308d759318a39%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637075941608848819&sdata=C192z%2BUJAwkLfTEjf0VbbDwm6SqA%2FyKijUTFoBtnFjA%3D&reserved=0>
JOIN prices.pricetar t ON
f.id
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Ff.id&data=02%7C01%7C%7Cda2813cfe6e04fc37f3308d759318a39%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637075941608848819&sdata=C192z%2BUJAwkLfTEjf0VbbDwm6SqA%2FyKijUTFoBtnFjA%3D&reserved=0>
=
t.firm
WHERE c.id
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fc.id&data=02%7C01%7C%7Cda2813cfe6e04fc37f3308d759318a39%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637075941608858836&sdata=45MnF4KqaB%2BOdFzEPxNIJvGT0dkP%2FgDYmDfqnRLU1%2F0%3D&reserved=0>
=
_customer AND t.id
<https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Ft.id&data=02%7C01%7C%7Cda2813cfe6e04fc37f3308d759318a39%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637075941608868842&sdata=RQNXQuXggkgjk8dAUPc%2B3ppnNNAdJEBKa4jeFuyjgio%3D&reserved=0>
=
_pricetar) OR _pricetar IS NULL
2019-10-25 12:39:34.119 EEST [41989] postgres(at)om1 CONTEXT: PL/pgSQL
function public.chk_f_part_pricetar(integer,integer) line 3 at RETURN
COPY part, line 1: "15 505040 16 5 2016-12-16
2017-03-14 0.00 1 1 \N"
2019-10-25 12:39:34.119 EEST [41989] postgres(at)om1 STATEMENT: COPY
public.part (id, part_nom, customer, pricetar, date_from, date_to, avmin,
inv_period, adv_calc_rule, group_izv) FROM stdin;
2019-10-25 12:39:39.781 EEST [41989] postgres(at)om1 ERROR: insert or update
on table "contract_hist_parts" violates foreign key constraint
"fk_contract_hist_parts_part"
2019-10-25 12:39:39.781 EEST [41989] postgres(at)om1 DETAIL: Key (part)=(58)
is not present in table "part".
2019-10-25 12:39:39.781 EEST [41989] postgres(at)om1 STATEMENT: ALTER TABLE
ONLY cust.contract_hist_parts
ADD CONSTRAINT fk_contract_hist_parts_part FOREIGN KEY (part)
REFERENCES public.part(id);
2019-10-25 12:39:39.837 EEST [41989] postgres(at)om1 ERROR: insert or update
on table "invoices" violates foreign key constraint "fk_invoices_part"
2019-10-25 12:39:39.837 EEST [41989] postgres(at)om1 DETAIL: Key (part)=(24)
is not present in table "part".
2019-10-25 12:39:39.837 EEST [41989] postgres(at)om1 STATEMENT: ALTER TABLE
ONLY invoices.invoices
ADD CONSTRAINT fk_invoices_part FOREIGN KEY (part) REFERENCES
public.part(id);
2019-10-25 12:39:40.363 EEST [41989] postgres(at)om1 ERROR: insert or update
on table "meters_history" violates foreign key constraint
"fk_meters_history_part"
2019-10-25 12:39:40.363 EEST [41989] postgres(at)om1 DETAIL: Key (part)=(127)
is not present in table "part".
2019-10-25 12:39:40.363 EEST [41989] postgres(at)om1 STATEMENT: ALTER TABLE
ONLY public.meters_history
ADD CONSTRAINT fk_meters_history_part FOREIGN KEY (part)
REFERENCES public.part(id);
2019-10-25 12:39:40.394 EEST [41989] postgres(at)om1 ERROR: insert or update
on table "spart_content" violates foreign key constraint
"fk_spart_content_part"
2019-10-25 12:39:40.394 EEST [41989] postgres(at)om1 DETAIL: Key (part)=(2)
is not present in table "part".
2019-10-25 12:39:40.394 EEST [41989] postgres(at)om1 STATEMENT: ALTER TABLE
ONLY public.spart_content
ADD CONSTRAINT fk_spart_content_part FOREIGN KEY (part)
REFERENCES public.part(id);

om1.zip
<https://drive.google.com/file/d/1wUSOghUxwb5G5Hn5RYdakVdfmwsDGc5F/view?usp=drive_web>