Обсуждение: 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);
Pavel Popov <pavel.popov@gmail.com> writes: > 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. I think it would happen if you tried to dump/restore with a *current* 9.6 release. The problem looks to be that this function is not safe against search_path changes: > 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;$$; and recent releases of pg_dump insist on running the script with a restrictive search_path for security reasons. It might be enough to schema-qualify the function's reference to "customers", although a safer solution would be to attach a "SET search_path" clause to the function to enforce the search path it's expecting. regards, tom lane
Pavel Popov <pavel.popov@gmail.com> writes:
> 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.
I think it would happen if you tried to dump/restore with a *current* 9.6
release. The problem looks to be that this function is not safe
against search_path changes:
> 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;$$;
and recent releases of pg_dump insist on running the script with a
restrictive search_path for security reasons.
It might be enough to schema-qualify the function's reference to
"customers", although a safer solution would be to attach a
"SET search_path" clause to the function to enforce the search
path it's expecting.
regards, tom lane