recently, I had to dump a postgresql database by doing:

pg_dump --format=custom --compress=6 --blobs --database mydb --schema public ... -f dump.bin

this generates a binary dump file, that I convert, for manual inspection, to the plain format:

pg_restore -f - dump.bin > dump.sql

dump.sql:

-- PostgreSQL database dump

--
-- Dumped from database version 11.22

-- Dumped by pg_dump version 14.12 (Ubuntu 14.12-0ubuntu0.22.04.1)

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET xmloption = content;

SET client_min_messages = warning;

SET row_security = off;

--

-- Name: public; Type: SCHEMA; Schema: -; Owner: superuser

--

CREATE SCHEMA public;

ALTER SCHEMA public OWNER TO superuser;

--

-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: superuser

--

COMMENT ON SCHEMA public IS 'standard public schema';

[…]

At this point,

pg_restore -l dump.bin

gives:

;

; Archive created at 2024-07-22 08:00:00 UTC
;     dbname: credit-decision
;     TOC Entries: 481
;     Compression: 6
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 11.22
;     Dumped by pg_dump version: 14.12 (Ubuntu 14.12-0ubuntu0.22.04.1)


; Selected TOC Entries:

;

6; 2615 2200 SCHEMA - public superuser

5031; 0 0 COMMENT - SCHEMA public superuser

Now, when restoring by doing:

pg_restore --verbose --clean --if-exists --database mydb ... dump.bin

I get two warnings:

pg_restore: dropping SCHEMA public
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 6; 2615 2200 SCHEMA public superuser
pg_restore: error: could not execute query: ERROR:  cannot drop schema public because other objects depend on it
DETAIL:  extension pg_stat_statements depends on schema public
extension pg_buffercache depends on schema public
extension unaccent depends on schema public
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Command was: DROP SCHEMA IF EXISTS public;
pg_restore: creating SCHEMA "public"
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;

It seems that toc entry 6 is responsible for the harmless warnings, that failure is somewhat expected.

Q1) At this point I am wondering, if this toc entry 6 is somehow correlated to the “CREATE SCHEMA public” statement present at the beginning of the dump file ?

Supposing that I now dump without schema specification option.
In comparison:

• the “CREATE SCHEMA public” statement has disappeared from the dump file.

• the “CREATE EXTENSION unaccent” statement has appeared.

• from the new TOC entries, none is referenced as “operating” on the public schema.

Finaly, when restoring with same options as previously, I get absolutely no warning. All went fine.

I would have expected, in the pg_restore output, an indication that the public schema was dropped (due to the usage of the –clean option).

The last trace of a dropping statement in this ouput is:

pg_restore: dropping EXTENSION unaccent

Then it starts recreating all objects which implies that the schema already exists…

It seems that pg_restore is clever enough not to drop/recreate the schema when dealing with extensions defined outside the schema itself but which remain dependant on it.

Q2) Is my last assumption correct ?

PS: I tried to be as descriptive as possible but probably too much.