public inbox for [email protected]
help / color / mirror / Atom feedpg_restore: error: could not execute query: ERROR: schema does not exist
2+ messages / 2 participants
[nested] [flat]
* pg_restore: error: could not execute query: ERROR: schema does not exist
@ 2026-05-07 20:06 =?iso-8859-2?Q?Micha=B3_T=EAcza?= <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: =?iso-8859-2?Q?Micha=B3_T=EAcza?= @ 2026-05-07 20:06 UTC (permalink / raw)
To: [email protected] <[email protected]>
When restoring a database with some specific view, pg_restore produce an error - schema does not exists.
This behavior was reproduced on latest version - 18.3
To reproduce the bug
1. Create file foo.sql
-- begin foo.sql
CREATE SCHEMA domain;
CREATE SCHEMA reporting;
CREATE TABLE domain.customers (
id integer NOT NULL,
name text NOT NULL,
email text,
created_at timestamp without time zone DEFAULT now()
);
CREATE SEQUENCE domain.customers_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE domain.customers_id_seq OWNED BY domain.customers.id;
CREATE VIEW reporting.v_customers AS
SELECT
NULL::integer AS customer_id,
NULL::text AS name;
ALTER TABLE ONLY domain.customers
ALTER COLUMN id SET DEFAULT nextval('domain.customers_id_seq'::regclass);
ALTER TABLE ONLY domain.customers
ADD CONSTRAINT customers_email_key UNIQUE (email);
ALTER TABLE ONLY domain.customers
ADD CONSTRAINT customers_pkey PRIMARY KEY (id);
CREATE OR REPLACE VIEW reporting.v_customers AS
SELECT c.id AS customer_id,
c.name
FROM domain.customers c
GROUP BY c.id;
-- end foo.sql
2. Create a database foo
createdb --host=localhost --port=5432 --username=postgres foo
3. Load file into database
psql -U postgres -f foo.sql foo
4. create dump
pg_dump --host=localhost --port=5432 --username=postgres --format=c --file dump foo
5. create database restore_foo
createdb --host=localhost --port=5432 --username=postgres restore_foo
6. restore database
pg_restore -Fc --verbose --no-owner --clean --if-exists --no-privileges -d restore_foo -U postgres dump
pg_restore log:
pg_restore: connecting to database for restore
pg_restore: executing SELECT pg_catalog.set_config('search_path', '', false);
pg_restore: dropping RULE v_customers _RETURN
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3450; 2618 22119 RULE v_customers _RETURN postgres
pg_restore: error: could not execute query: ERROR: schema "reporting" does not exist
Command was: CREATE OR REPLACE VIEW reporting.v_customers AS
SELECT
NULL::integer AS customer_id,
NULL::text AS name;
pg_restore: dropping CONSTRAINT customers customers_pkey
pg_restore: dropping CONSTRAINT customers customers_email_key
pg_restore: dropping DEFAULT customers id
pg_restore: dropping VIEW v_customers
pg_restore: dropping SEQUENCE customers_id_seq
pg_restore: dropping TABLE customers
pg_restore: dropping SCHEMA reporting
pg_restore: dropping SCHEMA domain
pg_restore: creating SCHEMA "domain"
pg_restore: creating SCHEMA "reporting"
pg_restore: creating TABLE "domain.customers"
pg_restore: creating SEQUENCE "domain.customers_id_seq"
pg_restore: creating SEQUENCE OWNED BY "domain.customers_id_seq"
pg_restore: creating VIEW "reporting.v_customers"
pg_restore: creating DEFAULT "domain.customers id"
pg_restore: processing data for table "domain.customers"
pg_restore: executing SEQUENCE SET customers_id_seq
pg_restore: creating CONSTRAINT "domain.customers customers_email_key"
pg_restore: creating CONSTRAINT "domain.customers customers_pkey"
pg_restore: creating RULE "reporting.v_customers _RETURN"
pg_restore: warning: errors ignored on restore: 1
best regards
Michał Tęcza
_________________
Pozdrawiam/Best Regards
Michał Tęcza
Architekt Systemów Informatycznych
IT Architect
Mobile: +48 733 057 852
E-mail: [email protected] [https://softiq.pl/sig/logo.png]
[https://softiq.pl/sig/footer-line.png]
SOFTIQ Sp. z o.o., ul. Chorzowska 50, 44-100 Gliwice
Wearesoftiq Ltd, 4 Studio Court Queensway, Bletchley, Milton Keynes, England, MK2 2DG
SOFTIQ Deutschland GmbH in Gründung, Joseph-Dollinger-Bogen 14 D-80807 Munich
[https://softiq.pl/sig/1-social-media-icon.png] <https://www.facebook.com/SoftiqPL; [https://softiq.pl/sig/3-social-media-icon.png] <https://www.youtube.com/@softiq1507; [https://softiq.pl/sig/4-social-media-icon.png] <https://www.instagram.com/softiq_pl/;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pg_restore: error: could not execute query: ERROR: schema does not exist
@ 2026-05-08 14:41 Fujii Masao <[email protected]>
parent: =?iso-8859-2?Q?Micha=B3_T=EAcza?= <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Fujii Masao @ 2026-05-08 14:41 UTC (permalink / raw)
To: Michał Tęcza <[email protected]>; +Cc: [email protected] <[email protected]>
On Fri, May 8, 2026 at 1:30 PM Michał Tęcza <[email protected]> wrote:
>
> When restoring a database with some specific view, pg_restore produce an error - schema does not exists.
> This behavior was reproduced on latest version - 18.3
This issue was previously reported and discussed at [1]. As mentioned there,
the --if-exists option can suppress some "does not exist" errors,
but it does not guarantee that all such errors can be avoided.
Regards,
[1] https://postgr.es/m/[email protected]
--
Fujii Masao
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-05-08 14:41 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-07 20:06 pg_restore: error: could not execute query: ERROR: schema does not exist =?iso-8859-2?Q?Micha=B3_T=EAcza?= <[email protected]>
2026-05-08 14:41 ` Re: pg_restore: error: could not execute query: ERROR: schema does not exist Fujii Masao <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox