pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

Поиск
Список
Период
Сортировка
От Karsten Hilbert
Тема pg_dump/pg_restore vs default_transaction_read_only under PG 13.2
Дата
Msg-id YM9xKBUhojhlOLuP@hermes.hilbert.loc
обсуждение исходный текст
Ответы Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2  (Vijaykumar Jain <vijaykumarjain.github@gmail.com>)
Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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



В списке pgsql-general по дате отправления:

Предыдущее
От: Ray O'Donnell
Дата:
Сообщение: Re: pgTAP installation
Следующее
От: Vijaykumar Jain
Дата:
Сообщение: Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2