Thread: Exporting Table-Specified BLOBs Only?

<div class="WordSection1"><p class="MsoNormal"><b><u><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">Fundamentalissue:</span></u></b><b><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green"></span></b><p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">PostgreSQLis different than Oracle in how it
storesBLOBs</span><p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green"><a
href="http://www.postgresql.org/docs/9.1/static/catalog-pg-largeobject.html">http://www.postgresql.org/docs/9.1/static/catalog-pg-largeobject.html</a>.</span><p
class="MsoNormal"><spanstyle="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green"> </span><p
class="MsoNormal"><b><u><spanstyle="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">In PostgreSQL
…</span></u></b><pclass="MsoNormal"><span style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">In
theparticular tables BLOBS are stored as OID’s (unique numbers). </span><p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">Thereis one table for all BLOB data (see
pg_largeobjectunder Catalogs/PostgreSQL/pg_largeobject in pgadmin tree). It stores colums “OID/pageno/actual binary
data(page)”.</span><p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green"> </span><pclass="MsoNormal"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green"> </span><pclass="MsoNormal"><b><u><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">Whatdo I need?</span></u></b><p
class="MsoNormal"><spanstyle="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">A method of using
pg_dumpto selectively export BLOBs with OID’s used in the tables specified with --table <table_name1> --table
<table_name2></span><pclass="MsoNormal"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green"> </span><pclass="MsoNormal"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">Ihave four tables containing BLOBs I want to
exportw/o the BLOB data – that’s easy.</span><p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">WhatI also want is the remaining 200+ tables
exportedwith THEIR BLOB entries, if any, but NOT one BLOB from the four tables explicitly <b><u>not</u></b> specified
inpg_dump command; so JUST the BLOBs associated to any of the 200+ tables I do specify.</span><p
class="MsoNormal"><spanstyle="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green"> </span><p
class="MsoNormal"><spanstyle="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green"> </span><p
class="MsoNormal"><spanstyle="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">Thanks!</span><div
style="border:none;border-bottom:solidwindowtext 1.0pt;padding:0in 0in 1.0pt 0in"><p class="MsoNormal"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green">Alan</span><pclass="MsoNormal"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green"> </span></div><pclass="MsoNormal"><span
style="font-size:12.0pt;font-family:"Verdana","sans-serif";color:green"> </span></div>

On Mon, Jul 21, 2014 at 2:14 PM, Braunstein, Alan
<alan_braunstein@mentor.com> wrote:
> What do I need?
>
> A method of using pg_dump to selectively export BLOBs with OID’s used in the
> tables specified with --table <table_name1> --table <table_name2>

Hmm.  If you take a full backup using pg_dump -Fc, you can then use
pg_restore -l and pg_restore -L to find and selectively restore
whatever objects you want; e.g. restore the tables first, then fetch
the list of OIDs from the relevant columns and restore those
particular blobs.

But I don't think we've got a tool built into core for doing this kind
of filtering on the dump side.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

The reason this is needed from the export/dump side is that the database can become huge due to the number of
datasheetsadded to the database.  These datasheets are not necessary to troubleshoot the setup and, sometimes, the
datasheetsare secure-sensitive.  In either case, they're not necessary when we need a copy of the customer's database
totroubleshoot and they make the transport and import of the database horribly time consuming.
 

Thanks for the response.  Hopefully this can be addressed one day.

Cheers.

-----Original Message-----
From: Robert Haas [mailto:robertmhaas@gmail.com] 
Sent: Thursday, July 24, 2014 7:31 AM
To: Braunstein, Alan
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Exporting Table-Specified BLOBs Only?

On Mon, Jul 21, 2014 at 2:14 PM, Braunstein, Alan <alan_braunstein@mentor.com> wrote:
> What do I need?
>
> A method of using pg_dump to selectively export BLOBs with OID’s used 
> in the tables specified with --table <table_name1> --table 
> <table_name2>

Hmm.  If you take a full backup using pg_dump -Fc, you can then use pg_restore -l and pg_restore -L to find and
selectivelyrestore whatever objects you want; e.g. restore the tables first, then fetch the list of OIDs from the
relevantcolumns and restore those particular blobs.
 

But I don't think we've got a tool built into core for doing this kind of filtering on the dump side.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company