Re: [PATCH] pg_upgrade fails when postgres/template1 isn't in default tablespace

On Fri, Jun 19, 2015 at 12:10 PM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> One of my databases failed to upgrade successfully and produced this error
> in the copying phase:
>
> error while copying relation "pg_catalog.pg_largeobject"
> ("/srv/ssd/PG_9.3_201306121/1/12023" to "/PG_9.4_201409291/1/12130"): No
> such file or directory
>
> Turns out this happens when either the "postgres" or "template1" databases
> have been moved to a non-default tablespace. pg_dumpall does not dump
> attributes (such as tablespace) for these databases; pg_upgrade queries the
> new cluster about the tablespace for these relations and builds a broken
> destination path for the copy/link operation.
>
> The least bad solution seems to be generating ALTER DATBASE SET TABLESPACE
> commands for these from pg_dumpall. Previously a --globals-only dump didn't
> generate psql \connect commands, but you can't run SET TABLESPACE from
> within the same database you're connected to. So to move "postgres", it
> needs to connect to "template1" and vice versa. That seems fine for the
> purpose of pg_upgrade which can assume a freshly created cluster with both
> databases intact.
>
> I have implemented a proof of concept patch for this. Currently I'm only
> tackling the binary upgrade failure and not general pg_dumpall.

I haven't looked at the patch, but this seems like a good thing to
fix. Hopefully Bruce will take a look soon.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company