Re: pg_dumpall SET default_transaction_read_only = off (was Re: == PostgreSQL Weekly News - January 28 2018 ==)

On Sun, Feb 25, 2018 at 04:20:41PM -0500, Tom Lane wrote:

> No; you're reading the commits in reverse order. The hack I thought we
> could use to preserve that behavior didn't work.
>
> AFAICS, the only way we could maintain something like the old behavior
> here is to promote it to a full-fledged feature, something like a
> pg_dump/pg_restore option "--ignore-read-only" to make those programs
> issue "SET default_transaction_read_only = off" at the right times,
> which in turn would need to be invoked by a similar pg_dumpall switch.
...
> While that doesn't seem like it would be terribly hard to do, I don't
> personally have the interest to go do it.

OK, I see.

Luckily, pg_upgradecluster allows running custom snippets at
appropriate times during the upgrade:

HOOK SCRIPTS

Some PostgreSQL extensions like PostGIS need metadata in
auxiliary tables which must not be upgraded from the old
version, but rather initialized for the new version
before copying the table data. For this purpose,
extensions (as well as administrators, of course) can
drop upgrade hook scripts into
/etc/postgresql-common/pg_upgradecluster.d/. Script file
names must consist entirely of upper and lower case
letters, digits, underscores, and hyphens; in particular,
dots (i. e. file extensions) are not allowed.

Scripts in that directory will be called with the following arguments:

<old version> <cluster name> <new version> <phase>

Phases:

init
A virgin cluster of version new version has been
created, i. e. this new cluster will already have
template1 and postgres, but no user databases.
Please note that you should not create tables in
this phase, since they will be overwritten by the
dump/restore or pg_upgrade operation.

finish
All data from the old version cluster has been
dumped/reloaded into the new one. The old cluster
still exists, but is not running.

Failing scripts will abort the upgrade. The scripts
are called as the user who owns the database.

which will allow solving the problem with something like the
attached sript.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346