Re: Restore LargeObjects on different server

Durumdara wrote:
> > Because of upload/download progress we used LargeObjects to store some files in one of our database (and not bytea).
> > Only this database uses the OID-s of these files.
> >
> > In the near future we must move to another server.
> > This new server is also working now, the moving of databases is continous, one by one.
> >
> > The main problem that LargeObjects are stored in the system table(s). Same OIDs could links to different LargeObjects.
> >
> > The old and new PGSQL server may have same OID values (160606 f. e.) with different content (LargeObject).
> > In old this is one of our file data, in the new this is a table's system definition.
> >
> > Can we backup this database WITH OIDs, and restore it in new server without worrying of LargeObject overwriting?
> >
> > Or how to migrate (move) this database with largeobjects in new to preserve the consistency of copied database and lobs, but preserve the existing OID/largeobject pairs in new server?
>
> Somebody wrote me that:
>
> The pg_catalog schema is system schema, but it is IN the DB.
>
> Is this true? So OID is not global (out from DB)?
>
> So we can dump and restore the DB with OIDs without collision in new server?

OIDs are assigned from a database-wide counter so that there can be no collision
within one database. But there is nothing that prevents OID collision between
different databases.

pg_dump dumps large objects with their OID, so they will have the same
OID when they are restored in another database.

This will lead to a collision if there are already large objects with the same OID
in the second database.

I'd restore the large objects and manually fix all collisions
(import the problematic large objects with a different OID and adjust
the referencing tables accordingly).

This might prove difficult if there are a lot of collisions, but I don't think
that there is a better way.

Yours,
Laurenz Albe