Re: pg_upgrade ?deficiency - Mailing list pgsql-general

On Wed, Nov 20, 2013 at 04:07:59PM +0100, Karsten Hilbert wrote:
> > 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.

I think the big question is whether a generic mention that there are
some database settings, like read-only, that can prevent updates, and
you might need to use PGOPTIONS to avoid that.  However, you are the
first case to report this, so I am hesistant.

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

Yep.

> > 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 ;-)

Yes, messing with status can often be problematic.

> > 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 ;-)

True.  Does anyone else see value in documenting this?  I can do the
docs.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +