Re: pg_upgrade ?deficiency

> On Wed, Nov 20, 2013 at 02:36:08PM +0100, Karsten Hilbert wrote:
> > > Karsten Hilbert wrote:
> > > > Let me try to rephrase:
> > > >
> > > > Fact: pg_upgrade can NOT properly upgrade clusters which contain
> > > > databases that are set to "default_transaction_read_only on"
> > > >
> > > > Question: Is this intended ?
> > >
> > > I am pretty sure that this is an oversight and hence a bug.
>
> Well, pg_upgrade can't handle every possible configuration.

Agreed. That would be a design decision: "no, pg_upgrade will
not support upgrading some of your databases, for example those
which are set to default_transaction_ready_only=on".

If I don't like that, fine, I can go and use other tools or
else submit a patch and hope for inclusion or apply a workaround.

That's why I tacitly suggested a hint in the docs might
help to become aware of the above limitation.

Of course, I should submit a patch to the docs just as well.

> How do we even restore into such a database?

We read the state, remember the state, change the state,
restore the data, set the initial state. But you knew that,
I assume.

> You marked the database as read-only, and pg_upgrade
> is going to honor that and not modify it.

Oh, I am extremely happy for pg_upgrade to NOT modify
ANY of my databases ! All I am wondering is whether
it is by design decision (and if so, why) that it cannot
transfer some databases from one PG version to another
one. I am more than happy if it doesn't modify the
databases in the process ;-)

> I believe a pg_dumpall restore might fail in the same way.

pg_dumpall works but a full pg_restore/psql from that dump
likely will not. I haven't tested that yet, though, and I
deliberately did not want to raise *that* question just
yet...

> You need to change the default on the old cluster before upgrading.

I know. That wasn't my question though.

> It is overly cumbersome to set the default_transaction_read_only for every
> database connection,

There is no need for that (see above).

> and there are many other settings that might also cause failures.

If so they warrant documentation as well as they become known.

> If it was a silent failure, I would be more concerned.

Absolutely, full agreement.

> What you might be able to do is to set PGOPTIONS to "-c
> default_transaction_read_only=false" and run pg_upgrade.

That is a good idea. It might have occurred to me earlier
had the pg_upgrade limitation been documented ;-)

Thanks for your work on PostgreSQL,
Karsten