pg_upgrade not able to cope with pg_largeobject being in a different tablespace
Hi all.
(I tried raising this on -hackers but got "this is not supported"-answer,
which is quite dissatisfactory..)
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/)
<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
My database and tablespaces are created like this:
I have tablespaces configured outside PGDATA:
mkdir /var/lib/postgresql/9.5/tablespaces/andreak
mkdir /var/lib/postgresql/9.5/tablespaces_lo/andreak
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
(I've set allow_system_table_mods=on in postgresql.conf)
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/archive_disk/9.5/tablespaces_lo
/var/lib/postgresql/9.5/tablespaces_lo
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.
So - I'm wondering if we can fund development of pg_upgrade to cope with this
configuration or somehow motivate to getting this issue fixed?
Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
Any feedback welcome, thanks.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
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.
So common that no one has ever asked for this feature before?
So - I'm wondering if we can fund development of pg_upgrade to cope with this
configuration or somehow motivate to getting this issue fixed?
�
Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
�
Any feedback welcome, thanks.
You would need to get buy-in that that community wants the relocation of
pg_largeobject to be supported via an SQL command, and at that point
pg_upgrade would be modified to support that. It is unlikely pg_upgrade
is going to be modified to support something that isn't supported at the
SQL level. Of course, you can create a custom version of pg_upgrade to
do that.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian <bruce@momjian.us
<mailto:bruce@momjian.us>>:
On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
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.
So common that no one has ever asked for this feature before?
Sometimes one gets the feeling that one is the only one in the universe doing
something one considers "quite common":-)
So - I'm wondering if we can fund development of pg_upgrade to cope with this
configuration or somehow motivate to getting this issue fixed?
Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
Any feedback welcome, thanks.
You would need to get buy-in that that community wants the relocation of
pg_largeobject to be supported via an SQL command, and at that point
pg_upgrade would be modified to support that. It is unlikely pg_upgrade
is going to be modified to support something that isn't supported at the
SQL level. Of course, you can create a custom version of pg_upgrade to
do that.
Doesn't "ALTER TABLE pg_largeobject SET TABLESPACE myspace_lo" count as being
"at the SQL-level"?
The whole problem seems to come from the fact that BLOBs are stored in
pg_largeobject which for some reason is implemented as a system-catalogue in
PG, which imposes all kinds of weird problems, from a DBA-perspective.
Can we pay you at EDB for making such a custom version of pg_upgrade for 9.6?
Thanks.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
On Thu, Oct 13, 2016 at 7:35 AM, Andreas Joseph Krogh <andreas@visena.com>
wrote:
På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian <
bruce@momjian.us>:On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
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.
So common that no one has ever asked for this feature before?
Sometimes one gets the feeling that one is the only one in the universe
doing something one considers "quite common":-)So - I'm wondering if we can fund development of pg_upgrade to cope with
this
configuration or somehow motivate to getting this issue fixed?
Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
Any feedback welcome, thanks.
You would need to get buy-in that that community wants the relocation of
pg_largeobject to be supported via an SQL command, and at that point
pg_upgrade would be modified to support that. It is unlikely pg_upgrade
is going to be modified to support something that isn't supported at the
SQL level. Of course, you can create a custom version of pg_upgrade to
do that.Doesn't "ALTER TABLE pg_largeobject SET TABLESPACE myspace_lo" count as
being "at the SQL-level"?
Well, it requires that you set allow_system_table_mods on, which is
documented as a developer option. It's documented with things like "The
following parameters are intended for work on the PostgreSQL source code,
and in some cases to assist with recovery of severely damaged databases. There
should be no reason to use them on a production database.".
Perhaps we should add another disclaimer there saying that if you make
changes in this mode, tools like pg_upgrade (or for that matter, pg_dump or
pretty much anything at all) may not work anymore?
The whole problem seems to come from the fact that BLOBs are stored in
pg_largeobject which for some reason is implemented as a system-catalogue
in PG, which imposes all kinds of weird problems, from a DBA-perspective.
Yes, there are several issues related to how lo style large objects work.
I've often gone to similar implementations but in userspace on top of
custom tables to work around those.
Can we pay you at EDB for making such a custom version of pg_upgrade for
9.6?
You're assuming pg_upgrade is the only potential problem. If you are
willing to spend towards it, it would probably be better to spend towards
the "upper layer" problem which would be to make it possible to move
pg_largeobject to a different tablespace *without* turning on
system_table_mods.
That said, I cannot comment to the complexity of either doing that *or*
doing a custom pg_upgrade that would support it. But solving a long-term
problem seems better than solving a one-off one.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
På tirsdag 18. oktober 2016 kl. 12:39:03, skrev Magnus Hagander <
magnus@hagander.net <mailto:magnus@hagander.net>>:
On Thu, Oct 13, 2016 at 7:35 AM, Andreas Joseph Krogh <andreas@visena.com
<mailto:andreas@visena.com>> wrote: På torsdag 13. oktober 2016 kl. 16:09:34,
skrev Bruce Momjian <bruce@momjian.us <mailto:bruce@momjian.us>>:
On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
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.
So common that no one has ever asked for this feature before?
Sometimes one gets the feeling that one is the only one in the universe doing
something one considers "quite common":-)
So - I'm wondering if we can fund development of pg_upgrade to cope with this
configuration or somehow motivate to getting this issue fixed?
Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this?
Any feedback welcome, thanks.
You would need to get buy-in that that community wants the relocation of
pg_largeobject to be supported via an SQL command, and at that point
pg_upgrade would be modified to support that. It is unlikely pg_upgrade
is going to be modified to support something that isn't supported at the
SQL level. Of course, you can create a custom version of pg_upgrade to
do that.
Doesn't "ALTER TABLE pg_largeobject SET TABLESPACE myspace_lo" count as being
"at the SQL-level"?
Well, it requires that you set allow_system_table_mods on, which is documented
as a developer option. It's documented with things like "The following
parameters are intended for work on the PostgreSQL source code, and in some
cases to assist with recovery of severely damaged databases. There should be no
reason to use them on a production database.".
Perhaps we should add another disclaimer there saying that if you make
changes in this mode, tools like pg_upgrade (or for that matter, pg_dump or
pretty much anything at all) may not work anymore?
The whole problem seems to come from the fact that BLOBs are stored in
pg_largeobject which for some reason is implemented as a system-catalogue in
PG, which imposes all kinds of weird problems, from a DBA-perspective.
Yes, there are several issues related to how lo style large objects work. I've
often gone to similar implementations but in userspace on top of custom tables
to work around those.
Can we pay you at EDB for making such a custom version of pg_upgrade for 9.6?
You're assuming pg_upgrade is the only potential problem. If you are willing
to spend towards it, it would probably be better to spend towards the "upper
layer" problem which would be to make it possible to move pg_largeobject to a
different tablespace *without* turning on system_table_mods.
That said, I cannot comment to the complexity of either doing that *or* doing
a custom pg_upgrade that would support it. But solving a long-term problem
seems better than solving a one-off one.
I totally agree that investing in a long-term solution is the best. However, I
need (would like very much) to upgrade a 9.5 cluster to 9.6 and would rather
not wait for a solution to land in 10.x.
IIRC there was a discussion on -hackers not too long ago about pg_largeobject
and releasing it from being a "system catalogue", but i think it stranded and
got nowhere.
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
On Thu, Oct 13, 2016 at 04:35:35PM +0200, Andreas Joseph Krogh wrote:
P� torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian <bruce@momjian.us
:
On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote:
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.
So common that no one has ever asked for this feature before?
Sometimes one gets the feeling that one is the only one in the universe doing
something one considers "quite common":-)
Yes, I often feel the same way. :-) Like, why am I the only person who
thinks this is a natural thing to do. I find a lot of bugs that way. :-)
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general