Обсуждение: 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
JOIN prices.pricetar t ON f.id = t.firm
WHERE c.id = _customer AND t.id = _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@om1 ERROR: relation "customers" does not exist at character 29
2019-10-25 12:39:34.119 EEST [41989] postgres@om1 QUERY: SELECT EXISTS(SELECT * FROM customers c JOIN nom.firms f ON c.invoiced_by = f.id
JOIN prices.pricetar t ON f.id = t.firm
WHERE c.id = _customer AND t.id = _pricetar) OR _pricetar IS NULL
2019-10-25 12:39:34.119 EEST [41989] postgres@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@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@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@om1 DETAIL: Key (part)=(58) is not present in table "part".
2019-10-25 12:39:39.781 EEST [41989] postgres@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@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@om1 DETAIL: Key (part)=(24) is not present in table "part".
2019-10-25 12:39:39.837 EEST [41989] postgres@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@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@om1 DETAIL: Key (part)=(127) is not present in table "part".
2019-10-25 12:39:40.363 EEST [41989] postgres@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@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@om1 DETAIL: Key (part)=(2) is not present in table "part".
2019-10-25 12:39:40.394 EEST [41989] postgres@om1 STATEMENT: ALTER TABLE ONLY public.spart_content
ADD CONSTRAINT fk_spart_content_part FOREIGN KEY (part) REFERENCES public.part(id);
