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.
>
> oversight, yes ... I thought as much and was therefore a bit
> cautious of calling it a bug, chose to name it "?deficiency" ;-)

Well, pg_upgrade can't handle every possible configuration. How do we
even restore into such a database? You marked the database as
read-only, and pg_upgrade is going to honor that and not modify it. I
believe a pg_dumpall restore might fail in the same way.

You need to change the default on the old cluster before upgrading. It
is overly cumbersome to set the default_transaction_read_only for every
database connection, and there are many other settings that might also
cause failures. If it was a silent failure, I would be more concerned.

What you might be able to do is to set PGOPTIONS to "-c
default_transaction_read_only=false" and run pg_upgrade. If more people
report this problem, I could document this work-around.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +