pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

[Karsten Hilbert <Karsten.Hilbert@xxxxxxx>]

Dear all,

I am testing the pg_restore of a database with
default_transaction_read_only=on.

The following issue ensues

	sudo -u postgres pg_restore --verbose --create --dbname=template1 --exit-on-error -p 5432 /tmp/gnumed/gm-restore_2021-06-20_18-31-07/backup-gnumed_v22-GNUmed_Team-hermes.dir/
	pg_restore: verbinde mit der Datenbank zur Wiederherstellung
	pg_restore: erstelle DATABASE »gnumed_v22«
	pg_restore: verbinde mit neuer Datenbank »gnumed_v22«
	pg_restore: erstelle DATABASE PROPERTIES »gnumed_v22«
	pg_restore: verbinde mit neuer Datenbank »gnumed_v22«
	pg_restore: erstelle SCHEMA »au«
	pg_restore: in Phase PROCESSING TOC:
	pg_restore: in Inhaltsverzeichniseintrag 7; 2615 16753 SCHEMA au gm-dbo
	pg_restore: Fehler: could not execute query: ERROR:  cannot execute CREATE SCHEMA in a read-only transaction
	Die Anweisung war: CREATE SCHEMA au;

	pg_restore exit code: 1

Producing an SQL file instead of restoring shows which
sequence of events quite logically leads up to this:

	--
	-- PostgreSQL database dump
	--

	-- Dumped from database version 13.2 (Debian 13.2-1)
	-- Dumped by pg_dump version 13.2 (Debian 13.2-1)

	-- Started on 2021-06-20 14:04:46 CEST

	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;

	--
	-- TOC entry 9963 (class 1262 OID 130036)
	-- Name: gnumed_v22; Type: DATABASE; Schema: -; Owner: gm-dbo
	--

	CREATE DATABASE gnumed_v22 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'de_DE.UTF-8';


	ALTER DATABASE gnumed_v22 OWNER TO "gm-dbo";

	\connect gnumed_v22

	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;

	--
	-- TOC entry 9964 (class 0 OID 0)
	-- Name: gnumed_v22; Type: DATABASE PROPERTIES; Schema: -; Owner: gm-dbo
	--

	ALTER DATABASE gnumed_v22 SET lc_messages TO 'C';
	ALTER DATABASE gnumed_v22 SET default_transaction_read_only TO 'on';
	ALTER DATABASE gnumed_v22 SET check_function_bodies TO 'on';
	ALTER DATABASE gnumed_v22 SET ignore_checksum_failure TO 'off';


	\connect gnumed_v22

	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;

	--
	-- TOC entry 7 (class 2615 OID 16753)
	-- Name: au; Type: SCHEMA; Schema: -; Owner: gm-dbo
	--

	CREATE SCHEMA au;

The problem being that pg_restore sets database properties from

	-- TOC entry 9964 (class 0 OID 0)
	-- Name: gnumed_v22; Type: DATABASE PROPERTIES; Schema: -; Owner: gm-dbo

including

	ALTER DATABASE gnumed_v22 SET default_transaction_read_only TO 'on';

(which was, indeed, set to "on" in the dumped database) and
only then attempts to create schema/restore data.

Is this issue handled differently in later versions or should
I be doing something differently during restore ?

It would seem the restore script lacks a

	SET default_transaction_read_only TO 'off';

in the setup section after re-connecting to the DB following
the ALTER DATABASE section ?

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B