Обсуждение: pg_dumpall SET default_transaction_read_only = off (was Re: == PostgreSQL Weekly News - January 28 2018 ==)

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> On Mon, Jan 29, 2018 at 03:57:48AM +0100, David Fetter wrote:
>> Tom Lane pushed:
>> ... This leaves us with no solution for the
>> default_transaction_read_only issue that commit 4bd371f6f intended to work
>> around, other than "you gotta remove such settings before dumping/upgrading".
>> However, in view of the fact that parallel restore broke that hack years ago
>> and no one has noticed, it's fair to question how many people care.

> I do (I am OP from the original report). The reason no one's
> noticed is that so far Debian's pg_upgradecluster doesn't by
> default seem to use more than one job for restore (it does
> use pg_dump/pg_restore over pg_upgrade by default).

> However, reading _this_ commit message ...

>> ... Commit
>> 4bd371f6f's hack to emit "SET default_transaction_read_only = off" is gone: we
>> now dodge that problem by the expedient of not issuing ALTER DATABASE SET
>> commands until after reconnecting to the target database.

> ... am I right in assuming my use case (dumping/restoring
> databases with default_transaction_read_only=True) should
> still work without removing that setting beforehand ?

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.

(You could maybe argue for having pg_dumpall invoke that by default,
but if we're going this far we might as well go the extra mile and
make it switchable all the way down.  One of the ways in which this
was a hack, IMO, was that there wasn't a switch required to enable it.
The argument that this behavior is always appropriate for pg_dumpall
but never for pg_dump seems pretty thin to me.)

While that doesn't seem like it would be terribly hard to do, I don't
personally have the interest to go do it.

            regards, tom lane

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