From: | Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2 |
Date: | 2021-06-20 20:40:38 |
Message-ID: | YM+nxjszK2LAB9Ll@hermes.hilbert.loc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
And this is the restore:
sudo -u postgres pg_restore --verbose --create --dbname=template1 --exit-on-error -p ${GM_PORT} ${BACKUP}.dir/
Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2021-06-20 20:52:10 | Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2 |
Previous Message | Adrian Klaver | 2021-06-20 20:14:06 | Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2 |