Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

Dear Jain,

> Did I simulate your concern correctly ?

Nearly so, to my understanding. What you did (and thanks for
the followup) was ...

> 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

... to tell the server to *treat* all databases in the
cluster as readonly. IOW, an intent rather than a property of
_a_ database (ALTER DATABASE ...).

Consequently, ...

> 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

... when the properties get dumped ...

> postgres(at)db:~/playground/logical_replication$ grep
> default_transaction_read_only dump.sql -- check what gets dumped
> SET default_transaction_read_only = off;

... the dump does not contain the "on" setting (because, I
assume, it does not dump configuration of the server, but
content and properties of databases).

Still, thanks for thinking along.

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