How to exclude blobs (large objects) from being loaded by pg_restore?

From: Alanoly Andrews <alanolya(at)invera(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: How to exclude blobs (large objects) from being loaded by pg_restore?
Date: 2015-05-01 14:12:08
Message-ID: 5a847d77e03244c8aa84f65b98cbc33e@exch1.invera.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

Hello,

We have a database that has been unloaded using pg_dump. This database has a table with a field defined as "lo". When restoring this database to another system, we want to avoid loading this particular table as it is very large (about 8GB of blob data) and is not needed on the target system. I tried the following:
1. Create a list of all the tables in the pg_dump file using the -l option of pg_restore
2. Edit out the lines corresponding to the said table (with the "lo" column)
3. Run the pg_restore with the -L option to use the edited list of tables.

I have found that what this does is to exclude only the non-lo fields of the table. So after the load, the table itself is not visible in the target system. But the actual blob data does get loaded since they are contained in the pg_largeobject system table. This table does not occur in the listing produced in step 1 above and so cannot be edited out. Besides it is a system table and should not be excluded anyway.

I'd appreciate some input on how I can get the blob data of a specific table to be excluded from a pg_restore. This is an operation that we need to do on a monthly basis. We do not want to exclude the blobs from the dump itself (since the whole database is to be preserved as a monthly record), but only from the restore.

Postgres 9.1.4 on AIX.

Thanks.

Alanoly Andrews.
Invera Inc.
Montreal, Canada.

________________________________

If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:unsubscribe(at)invera(dot)com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Si vous ne d?sirez plus recevoir de nos courriels, veuillez appuyer sur D?SABONNEMENT.<mailto:unsubscribe(at)invera(dot)com?subject=***Unsubscribe***> Ce courriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) d?sign?(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser imm?diatement, par retour de courriel ou par un autre moyen.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Petr Jelinek 2015-05-01 14:21:05 Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
Previous Message Andres Freund 2015-05-01 14:10:52 Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

Browse pgsql-general by date

  From Date Subject
Next Message Murphy, Kevin 2015-05-01 15:10:23 pg_dump permssion denied problem
Previous Message John McKown 2015-05-01 14:09:43 Re: Stellar Phoenix File Recovery Software