Thread: [GENERAL] Restore LargeObjects on different server
Dear Members!
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?
Thank you for the info/help!
Best regards
dd
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>:
Dear Members!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?Thank you for the info/help!Best regardsdd
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