trying to upgrade and keep large object IDs intact...

Started by Ron Snyderabout 24 years ago2 messagesgeneral
Jump to latest
#1Ron Snyder
snyder@roguewave.com

We're trying to move from 7.1.3 to 7.2, and we have a table that stores the
loid from pg_largeobject. The table that references the loid is storing it
as a text field. When we go through the dump/restore process, the loid (for
the attachment, in pg_largeobject) is changed in the new database, which
causes inconsistencies in our data.

We're doing the pg_dump like this:
pg_dump --blobs --format=c --quotes --oids --compress=5 quickview > qv.dump

and the restore like this:
pg_restore -o --dbname=quickview --superuser=pgsql72 < qv.dump

The field (in table "attachments") that references the loid (in
pg_largeobject) is storing the loid as text-- is that possibly what's making
it difficult for us to do this upgrade?

I've read the man pages for pg_dump/pg_restore, as well as searched the web
and the postgres web site, but I'm still lacking the necessary clue to make
this upgrade succeed. Pointers to additional documents happily accepted.

-ron

        Table "attachments"
 Attribute  |     Type     | Modifier 
------------+--------------+----------
 id         | integer      | 
 atype      | character(1) | 
 data       | text         | 
 compressed | boolean      | 
Index: attachments_id
Rule: att_lo_remover
Trigger: RI_ConstraintTrigger_11991716
#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ron Snyder (#1)
Re: trying to upgrade and keep large object IDs intact...

Ron Snyder <snyder@roguewave.com> writes:

The field (in table "attachments") that references the loid (in
pg_largeobject) is storing the loid as text-- is that possibly what's making
it difficult for us to do this upgrade?

Yup. pg_dump/pg_restore know about updating LO references that are
stored as OIDs or as type "lo" (see contrib/lo and friends). They
have no reason to think that those digit strings in your text column
have anything to do with your large objects.

regards, tom lane