Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YoBhl-0004nN-QL for pgsql-general@arkaria.postgresql.org; Fri, 01 May 2015 14:14:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YoBhl-0003un-8t for pgsql-general@arkaria.postgresql.org; Fri, 01 May 2015 14:14:13 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YoBhi-0003ox-Gw; Fri, 01 May 2015 14:14:10 +0000 Received: from mail1.bemta7.messagelabs.com ([216.82.254.110]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1YoBhf-0001xL-GI; Fri, 01 May 2015 14:14:09 +0000 Received: from [216.82.254.19] by server-14.bemta-7.messagelabs.com id AB/49-25228-B2A83455; Fri, 01 May 2015 14:14:03 +0000 X-Env-Sender: alanolya@invera.com X-Msg-Ref: server-6.tower-96.messagelabs.com!1430489642!22774624!1 X-Originating-IP: [198.168.207.245] X-StarScan-Received: X-StarScan-Version: 6.13.6; banners=-,-,- X-VirusChecked: Checked Received: (qmail 29388 invoked from network); 1 May 2015 14:14:02 -0000 Received: from cusvpn.invera.com (HELO exch1.invera.com) (198.168.207.245) by server-6.tower-96.messagelabs.com with AES256-SHA encrypted SMTP; 1 May 2015 14:14:02 -0000 Received: from EXCH1.invera.com (172.20.100.3) by exch1.invera.com (172.20.100.3) with Microsoft SMTP Server (TLS) id 15.0.1076.9; Fri, 1 May 2015 10:12:08 -0400 Received: from EXCH1.invera.com ([fe80::cc7d:e1bc:6533:f4d]) by exch1.invera.com ([fe80::cc7d:e1bc:6533:f4d%13]) with mapi id 15.00.1076.000; Fri, 1 May 2015 10:12:08 -0400 From: Alanoly Andrews To: "pgsql-general@postgresql.org" CC: "pgsql-admin@postgresql.org" Subject: How to exclude blobs (large objects) from being loaded by pg_restore? Thread-Topic: How to exclude blobs (large objects) from being loaded by pg_restore? Thread-Index: AdCEGMvV6G2d7EPARzu3oazkHGMwEA== Date: Fri, 1 May 2015 14:12:08 +0000 Message-ID: <5a847d77e03244c8aa84f65b98cbc33e@exch1.invera.com> Accept-Language: en-GB, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [172.20.200.88] Content-Type: multipart/alternative; boundary="_000_5a847d77e03244c8aa84f65b98cbc33eexch1inveracom_" MIME-Version: 1.0 X-Pg-Spam-Score: -0.9 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-general Precedence: bulk Sender: pgsql-general-owner@postgresql.org --_000_5a847d77e03244c8aa84f65b98cbc33eexch1inveracom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 anoth= er system, we want to avoid loading this particular table as it is very lar= ge (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 op= tion of pg_restore 2. Edit out the lines corresponding to the said table (with the "lo" c= olumn) 3. Run the pg_restore with the -L option to use the edited list of tab= les. I have found that what this does is to exclude only the non-lo fields of th= e table. So after the load, the table itself is not visible in the target s= ystem. 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 syste= m table and should not be excluded anyway. I'd appreciate some input on how I can get the blob data of a specific tabl= e 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 itse= lf (since the whole database is to be preserved as a monthly record), but o= nly 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. This e-mail may b= e 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 unautho= rized. 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?S= ABONNEMENT. Ce c= ourriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits= et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie d= e ce message ou des renseignements qu'il contient par une personne autre qu= e le (les) destinataire(s) d?sign?(s) est interdite. Si vous recevez ce cou= rriel par erreur, veuillez m'en aviser imm?diatement, par retour de courrie= l ou par un autre moyen. --_000_5a847d77e03244c8aa84f65b98cbc33eexch1inveracom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Hello,
 
We have a database that has been unloaded using pg_du= mp. This database has a table with a field defined as “= lo”. When restoring this database to another sys= tem, 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 cor= responding to the said table (with the “lo” column)
  3. Run the pg_restore with the &= #8211;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 i= s not visible in the target system. But the actual blob data does ge= t 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 i= s a system table and should not be excluded anyway.
&nbs= p;
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 e= xclude the blobs from the dump itself (since the whole database is to be preserved as= a monthly record), but only from the restore.
&nbs= p;
Postgres 9.1.4 on AIX.
&nbs= p;
Thanks.
 
Alanoly Andrews.
Invera Inc.
Montreal, Canada.
&nbs= p;
 
&nbs= p;
&nbs= p;

  ________________________________  
<= span style=3D"font-size:12pt;">If you no longer wish to receive any of our = emails, click on 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 rec= ipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) imme= diately.
 

<= span style=3D"font-size:12pt;">Si vous ne désirez plus recevoir de n= os courriels, veuillez appuyer sur DÉSABONNEMENT.= Ce courriel est confidentiel et protégé. L'expéditeur = ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusio= n, 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.
--_000_5a847d77e03244c8aa84f65b98cbc33eexch1inveracom_--