Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

On 6/20/21 12:34 PM, Karsten Hilbert wrote:
> Am Sun, Jun 20, 2021 at 01:47:47PM -0400 schrieb Tom Lane:
> 
>> Hence, the only way to make this scenario work would be for the
>> restore script to explicitly override default_transaction_read_only.
> [...]
>> Also, doing so would result in ignoring default_transaction_read_only
>> no matter what the source of that was.  I think it's probably not
>> hard to construct scenarios where someone would really not be happy
>> about such behavior.
> 
> Any chance pg_dump (and/or pg_restore) might gain an option
> --ignore-read-only ?   That way, PostgreSQL need not decide
> for users.

How about:

1) pg_dump -Fc -d read_only_db -U postgres -f read_only.out

2) In new cluster:
    A) psql -d postgres -U postgres
       CREATE DATABASE read_only;

    B) pg_restore -d read_only -U postgres read_only.out

    C)  psql -d read_only -U postgres
      alter database read_only SET default_transaction_read_only TO 'on';

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com