pg_restore: error: could not execute query: ERROR: schema does not exist - Mailing list pgsql-bugs
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: michal.tecza@softiq.pl |  | ||
 | |||
pgsql-bugs by date: