Thread: pg_upgrade 9.5 -> 9.6 fails when pg_largeobject is in separate tablespace

pg_upgrade 9.5 -> 9.6 fails when pg_largeobject is in separate tablespace

From
Andreas Joseph Krogh
Date:
Hi.
 
(if this is not the right forum, please point me to it)
 
I have an issue with pg_upgrade upgrading 9.5 to 9.6. (my system is Ubuntu-16.04 and packages from http://apt.postgresql.org/)
 
In short pg_upgrade fails with:
 
Linking user relation files
No match found in new cluster for old relation with OID 16388 in database "andreak": "pg_toast.pg_toast_2613" which is the TOAST table for "pg_catalog.pg_largeobject"
No match found in new cluster for old relation with OID 16390 in database "andreak": "pg_toast.pg_toast_2613_index" which is an index on "pg_toast.pg_toast_2613" which is the TOAST table for "pg_catalog.pg_largeobject"
Failed to match up old and new tables in database "andreak"
Failure, exiting
 
I issued the following command:
PG_NEW_VERSION=9.6
PG_OLD_VERSION=9.5
/usr/lib/postgresql/$PG_NEW_VERSION/bin/pg_upgrade \
          --old-bindir=/usr/lib/postgresql/$PG_OLD_VERSION/bin/ \
          --new-bindir=/usr/lib/postgresql/$PG_NEW_VERSION/bin/ \
          --old-datadir=/var/lib/postgresql/$PG_OLD_VERSION/main \
          --new-datadir=/var/lib/postgresql/$PG_NEW_VERSION/main \
          -o " -c config_file=/etc/postgresql/$PG_OLD_VERSION/main/postgresql.conf" \
          -O " -c config_file=/etc/postgresql/$PG_NEW_VERSION/main/postgresql.conf" \
          --link
 
I have pg_largeobject in a separate tablespace, moved by the following command:
psql -U postgres -c "alter table pg_largeobject set tablespace andreak_lo" -d andreak
 
 
(I've set allow_system_table_mods=on in postgresql.conf)
 
Else I have tablespaces configured outside PGDATA:
mkdir /var/lib/postgresql/9.5/tablespaces/andreak
mkdir /var/lib/postgresql/9.5/tablespaces_lo/andreak
 
These are symlinked:
ln -s /storage/wal/9.5/pg_xlog /var/lib/postgresql/9.5/main/pg_xlog
ln -s /storage/fast_ssd/9.5/tablespaces /var/lib/postgresql/9.5/tablespaces
ln -s /storage/fast_ssd/9.5/tablespaces_lo /var/lib/postgresql/9.5/tablespaces_lo
 
 
psql -c "create tablespace andreak OWNER andreak location '/var/lib/postgresql/9.5/tablespaces/andreak'" postgres;
psql -c "create tablespace andreak_lo OWNER andreak location '/var/lib/postgresql/9.5/tablespaces_lo/andreak'" postgres;
createdb --tablespace=andreak -O andreak andreak
psql -U postgres -c "alter table pg_largeobject set tablespace andreak_lo" -d andreak
Is this a bug or not a supported configuration by pg_upgraded?
Any hints on how to proceed?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment

Re: pg_upgrade 9.5 -> 9.6 fails when pg_largeobject is in separate tablespace

From
Robert Haas
Date:
On Sat, Oct 8, 2016 at 9:02 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
(I've set allow_system_table_mods=on in postgresql.conf)

Any configuration that includes this step is considered unsupported by the PostgreSQL community.

It might be a good idea if we supported storing large objects in an alternate tablespace, or in multiple tables in the same or different tablespaces.  However, if you can only get there by enabling allow_system_table_mods, then we don't.

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

Re: pg_upgrade 9.5 -> 9.6 fails when pg_largeobject is in separate tablespace

From
Andreas Joseph Krogh
Date:
På søndag 09. oktober 2016 kl. 23:43:23, skrev Robert Haas <robertmhaas@gmail.com>:
On Sat, Oct 8, 2016 at 9:02 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
(I've set allow_system_table_mods=on in postgresql.conf)
Any configuration that includes this step is considered unsupported by the PostgreSQL community.
 
It might be a good idea if we supported storing large objects in an alternate tablespace, or in multiple tables in the same or different tablespaces.  However, if you can only get there by enabling allow_system_table_mods, then we don't.
 
Note that pg_largeobject can be moved without changing allow_system_table_mods, namely by starting in single-user-mode, so I really don't se why this is considered unsupported? I would assume that having pg_largeobject in a separate tablespace is more and more common these days, having real-cheap SAN vs. fast-SSD for normal tables/indexes/wal.
 
AFAICT the very existence of pg_largeobject is an implementation-detail(and it being a system-catalog considered a defect) so saying that by moving it you're not able to use tools like pg_upgrade feels like being left out in the cold...
 
Is fixing this in any plans? Is this something we can pay for getting fixed, if so - what would it take?
 
PS: I cannot see this shortcoming being documented anywhere in pg_upgrade's docs ( https://www.postgresql.org/docs/9.6/static/pgupgrade.html ), is it mentioned anywhere?
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment