Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

On 6/20/21 1:40 PM, Karsten Hilbert wrote:
> Am Sun, Jun 20, 2021 at 01:14:06PM -0700 schrieb Adrian Klaver:
> 
>>> 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';
> 
> That would work but does not lend itself well to a fully
> scripted pg_dump/pg_restore backup "solution". The full
> pg_dump command line is this:
> 
>     pg_dump --verbose --format=directory --compress=0 --column-inserts --clean --if-exists --serializable-deferrable
"${_PG_HOST_ARG}""${_PG_PORT_ARG}" --username="${GM_DBO}" -f "${BACKUP_DATA_DIR}" "${GM_DATABASE}" 2> /dev/null
 
> 

Wouldn't the below just require the following changes?:

> And this is the restore:
> 
    sudo -u postgres psql -d postgres -f create_database.sql
>     sudo -u postgres pg_restore --verbose --create --dbname=template1 --exit-on-error -p ${GM_PORT} ${BACKUP}.dir/
         Remove --create and change dbname to the database name from 
create_database.sql

    sudo -u postgres psql -d <dbname> -f alter_database.sql

At this point version 14 is past the point of adding new features and 15 
is 1+ year out, so there is going to have to be some sort of kludge.

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com