Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

> >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.

I know. I was asking in principle, whether a
felt-to-be-more-proper change in pg_dump/pg_restore for
handling default_transaction_read_only can be hoped to happen
or not (short of submitting a patch).

For the record: The kludge I have implemented now is this

CMD_PG_RESTORE="pg_restore --create --file=- ${BACKUP}.dir"
CMD_PSQL="psql --echo-errors --dbname=template1 --port=${GM_PORT} --no-psqlrc"
CMD_SUDO="sudo --user=postgres"
{
${CMD_PG_RESTORE} | grep --ignore-case --invert-match --regexp="alter database ${TARGET_DB} set default_transaction_read_only to.*on" - | ${CMD_SUDO} ${CMD_PSQL}
} &>> "${LOG}"

which I am sure can be improved (such as, I was unable to get
the quoting right for factoring out the grep from the
pipeline :-)

I would be happy to hope that pg_dump might gain knowledge to
skillfully emit

alter database ... default_transaction_read_only to 'on'

only after having emitted any data-loading statements or else
gain a --do-not-alter-database-to-read-only effectively
omitting said statement thereby allowing for a more elegant
treatment of the issue.

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