public inbox for [email protected]  

help / color / mirror / Atom feed

From: =?iso-8859-2?Q?Micha=B3_T=EAcza?= <[email protected]>
To: [email protected] <[email protected]>
Subject: pg_restore: error: could not execute query: ERROR:  schema does not exist
Date: Thu, 7 May 2026 20:06:23 +0000
Message-ID: <AS4P189MB1821E9F6FCFDE0413C0A12849B3C2@AS4P189MB1821.EURP189.PROD.OUTLOOK.COM> (raw)
References: <d43e0204-fae6-45ca-a06e-55aa52558f92.1a27a959-9aa3-407c-90ac-332721986f70.48c9b43b-80b8-4ef7-9941-637b4b816290@emailsignatures365.codetwo.com>

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/;

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: pg_restore: error: could not execute query: ERROR:  schema does not exist
  In-Reply-To: <AS4P189MB1821E9F6FCFDE0413C0A12849B3C2@AS4P189MB1821.EURP189.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox