Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

On Sun, 20 Jun 2021 at 22:17, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)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(at)db:~/playground/logical_replication$ rm -rf example
postgres(at)db:~/playground/logical_replication$ initdb -D example 2>/dev/null
>/dev/null
postgres(at)db:~/playground/logical_replication$ vim example/postgresql.conf
postgres(at)db:~/playground/logical_replication$ printf
"default_transaction_read_only=on\n" >> example/postgresql.conf #global
config setting as on
postgres(at)db:~/playground/logical_replication$ pg_ctl -D example -l logfile
start
waiting for server to start.... done
server started
postgres(at)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(at)db:~/playground/logical_replication$ pg_dumpall -f dump.sql
postgres(at)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 ?

--
Thanks,
Vijay
Mumbai, India