Product: PostgreSQL 10.3

We have a need to take a copy of our production database and restore that into a separate environment for testing and training purposes. The problem is that the database is huge, but 90% of the space is the result of scanned images stored in tables that use blobs (extension lo).

Since we do not need the images in the test database, the idea is to do a pg_dump with the --no-blobs flag from the LIVE database and then restore that into the TEST database. This works perfectly.

The problem that has arisen is that I was asked to see if we could do two backups: Backup # 1) Everything but the blobs Backup # 2) blobs only

We would then restore Backup #1 whenever we want fresh data in there, but only restore Backup #2 if we happen to be testing something related to the scanned images.

Since I know that all the blob data is actually stored in the pg_largeobject table and only OIDs are stored in the actual tables that have the lo fields, what I tried to do was these two backups:

pg_dump --no-blobs -Fc ${DATABASE} > ${BACKUP_FILENAME}

pg_dump -t pg_largeobject -Fc ${DATABASE} > ${BLOB_BACKUP_FILENAME}

The pg_restore of the first backup file into the TEST area went well and I can access all data except the blobs, as expected.

However, when I tried to use pg_restore to restore the contents of the pg_largeobject table, I ran into a number of errors. It failed to create the table or the indexes (because it is a system table that already exists). But it did look like it was restoring the data despite the initial errors. But when it completed, I tried to access the blob tables and get 25P02 errors.

Has anyone ever tried something similar? There must be a way to restore certain tuples from the pg_largeobject table in the event that you have a corrupted table, or need to replace some data that was inadvertently dropped?

Any ideas if this is at all possible?

I have an alternative I was going to try, which is to throw all BLOB tables into a separate PGSERVER and then use views to reach a foreign table. That way the blob data is kept entirely separate from the main database. If all else fails I'll likely try that.

Thanks for any comments/suggestions.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.