Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2 - Mailing list pgsql-general

On Sun, 20 Jun 2021 at 22:17, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

Dear all,

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

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

i just tested as below.

postgres@db:~/playground/logical_replication$ rm -rf example

postgres@db:~/playground/logical_replication$ initdb -D example 2>/dev/null >/dev/null

postgres@db:~/playground/logical_replication$ vim example/postgresql.conf

postgres@db:~/playground/logical_replication$ printf "default_transaction_read_only=on\n" >> example/postgresql.conf  #global config setting as on

postgres@db:~/playground/logical_replication$ pg_ctl -D example -l logfile start

waiting for server to start.... done

server started

postgres@db:~/playground/logical_replication$ psql

psql (14beta1)

Type "help" for help.

postgres=# show default_transaction_read_only;  -- validate it is on

 default_transaction_read_only

-------------------------------

 on

(1 row)

postgres=# \q

postgres@db:~/playground/logical_replication$ pg_dumpall -f dump.sql

postgres@db:~/playground/logical_replication$ grep default_transaction_read_only dump.sql -- check what gets dumped

SET default_transaction_read_only = off;

but this is 14beta1.

Did I simulate your concern correctly ?