> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
