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
