pg_restore: error: could not execute query: ERROR: schema "my_schema" does not exist

Question

I have a PostgreSQL 13 dump file done with this command:

$ sudo -u postgres pg_dump --no-privileges -Fc -n my_schema --file="/path/to/my_schema.sql" my_database

But when I try to restore it into a new database (PG15), I'm facing an issue that I do not expect because of the --clean --if-exists option:

$ sudo -u postgres pg_restore --single-transaction --clean --if-exists --schema=my_schema --no-owner --role="app-user" --dbname=my_database /path/to/my_schema.sql
pg_restore: error: could not execute query: ERROR: schema "my_schema" does not exist
LINE 1: CREATE TABLE my_schema.my_table (
 ^
Command was: CREATE TABLE my_schema.my_table (
 id integer NOT NULL,
 name character varying(128),
 description text
);

Shouldn't the schema be recreated before the tables are actually created?

If this can help, here is the relevant header of the dump file (sorry, it's a custom binary format):

$ head -n27 my_schema.sql
PGDMP0my_database13.16 (Debian 13.16-0+deb11u1)13.16 (Debian 13.16-0+deb11u1)-=0ENCODINENCODINGSET client_encoding = 'UTF8';
false=00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
false=00
SEARCHPATH
SEARCHPATH8SELECT pg_catalog.set_config('search_path', '', false);
false=126217698my_databasDATABASEgCREATE DATABASE my_database WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_US.UTF-8';
"DROP DATABASE my_database;
postgresfalse2615200013my_schemaSCHEMACREATE SCHEMA my_schema;
DROP SCHEMA my_schema;
app-userfalseo12592000138my_tableTABLECREATE TABLE my_schema.my_table (
 id integer NOT NULL,
 name character varying(128),
 description text
);
!DROP TABLE my_schema.my_table;
my_schemaheaapp-userfalse14p12592000144my_table_id_seSEQUENCE}CREATE SEQUENCE my_schema.my_table_id_seq
 START WITH 1
 INCREMENT BY 1
 NO MINVALUE
 NO MAXVALUE
 CACHE 1;
+DROP SEQUENCE my_schema.my_table_id_seq;
my_schemaapp-userfalse62314=00my_table_id_seqSEQUENCE OWNED BYMALTER SEQUENCE my_schema.my_table_id_seq OWNED BY my_schema.my_table.id;

Is there anything I can do and why is this error occurring?

Answer 1

Omit the option --schema=my_schema with pg_restore. That option causes only objects in the schema to be restored, not the schema itself.

• s.k: Thank you for bringing this important but subtle detail to my attention. I hadn't understood the doc like that when I read it. I'll make a note of it somewhere ! Oh, and I also fixed the call the option -n of pg_restore in my original question. Thanks.