Restore LargeObjects on different server

Hi!

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?

Thank you!

dd

2017-10-12 11:35 GMT+02:00 Durumdara <durumdara@gmail.com>:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Durumdara wrote:

The pg_catalog schema is system schema, but it is IN the DB.

Is this true? So OID is not global (out from DB)?

The OID generator is global to the instance, but the unicity
checks are local to the tables that use OIDs, including
large objects.

The case when you may have a problem is if moving
large objects from the old instance/old database
to the new instance/new database but the new
database would already have large objects created
before the import. Then you would need to do a merge
rather than just an import.

But if you're merely in the case that other databases in the new
instance have consumed OIDs and some happen to be equal to the ones
you want to import in a new database, that doesn't matter: similar OIDs
won't conflict if they're in different databases or even the same
database but different tables.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general