Hi list 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.
Alternatively, we could allow SET TABLESPACE in the current database, which
seems less ugly to me. A code comment says "Obviously can't move the tables
of my own database", but it's not obvious to me why. If I'm the only
connected backend, it seems that any caches and open files could be
invalidated. But I don't know how big of an undertaking that would be.
----
While working on this, I also noticed other things that pg_dumpall (and
this pg_upgrade) misses:
* Nothing at all is dumped for the template0 database, although ACLs,
settings and the tablespace can be changed by the user
* template1 & postgres databases retain ACLs and settings, but not
attributes like TABLESPACE or CONNECTION LIMIT. Other attributes like
LC_COLLATE and LC_CTYPE can't be changed without recreating the DB, but
those don't matter for the pg_upgrade case anyway.
It seems those would be good material for another patch?
Regards,
Marti Raudsepp
0001-Fix-pg_upgrade-when-postgres-template1-aren-t-in-def.patch
Description: binary/octet-stream
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
