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

[Andreas Joseph Krogh]

På søndag 09. oktober 2016 kl. 23:43:23, skrev Robert Haas <
robertmh...@gmail.com >:
On Sat, Oct 8, 2016 at 9:02 AM, Andreas Joseph Krogh mailto:andr...@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
andr...@visena.com 
www.visena.com 
 


 

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

[Robert Haas]

On Sat, Oct 8, 2016 at 9:02 AM, Andreas Joseph Krogh 
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

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

[Andreas Joseph Krogh]

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
andr...@visena.com 
www.visena.com