Обсуждение: Exclude pg_largeobject form pg_dump
Hi all.
Is there a way to exclude pg_largeobject from pg_dump? -T pg_largeobject doesn't work. I've tried to exclude tables using OID-datatype also but that didn't work either.
Thanks.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På tirsdag 08. mars 2016 kl. 15:43:37, skrev Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/08/2016 01:53 AM, Andreas Joseph Krogh wrote:
> Hi all.
> Is there a way to exclude pg_largeobject from pg_dump? -T pg_largeobject
> doesn't work. I've tried to exclude tables using OID-datatype also but
> that didn't work either.
Well pg_largeobject is a system catalog so pretty sure it cannot be
excluded.
What tables are you trying to exclude with OID?
Do you care about not dumping the pg_largeobject table or not dumping
the data it contains?
I have several tables with OID-columns and I'd like to dump my DB without any data in pg_largeobject (> 95% of the space is occupied by data in pg_largeobject).
I've tried to exclude (using -T) the tables containing OID-columns but pg_largeobject is still dumped containing the data it seems.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På tirsdag 08. mars 2016 kl. 16:30:36, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Andreas Joseph Krogh <andreas@visena.com> writes:
> P�� tirsdag 08. mars 2016 kl. 15:43:37, skrev Adrian Klaver <
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:
> Do you care about not dumping the pg_largeobject table or not dumping
> the data it contains?
>
> I have several tables with OID-columns and I'd like to dump my DB without any
> data in pg_largeobject (> 95% of the space is occupied by data in
> pg_largeobject).
> I've tried to exclude (using -T) the tables containing OID-columns but
> pg_largeobject is still dumped containing the data it seems.
A look at the pg_dump source code says that it skips blobs if any of
-s, -n, -t are used. There's a -b switch to undo that and include
them anyway, but no "inverse -b" to skip them in an otherwise-complete
dump.
So you could do something along the lines of pg_dump -t '*' ...
although this will result in *all* non-schema-named objects being
excluded, I believe, which might be a problem.
regards, tom lane
Hm:
pg_dump -v -t '*' > andreak-noblob.dmp
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation pg_authid
pg_dump: [archiver (db)] query was: LOCK TABLE pg_catalog.pg_authid IN ACCESS SHARE MODE
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation pg_authid
pg_dump: [archiver (db)] query was: LOCK TABLE pg_catalog.pg_authid IN ACCESS SHARE MODE
What I'm looking for is "inverse -b" in an otherwise complete dump. Any plans to add that?
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På tirsdag 08. mars 2016 kl. 16:54:19, skrev Adrian Klaver <adrian.klaver@aklaver.com>:
On 03/08/2016 07:46 AM, Andreas Joseph Krogh wrote:
> På tirsdag 08. mars 2016 kl. 16:30:36, skrev Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>>:
>
> Andreas Joseph Krogh <andreas@visena.com> writes:
> > P�� tirsdag 08. mars 2016 kl. 15:43:37, skrev Adrian Klaver <
> > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:
> > Do you care about not dumping the pg_largeobject table or not
> dumping
> > the data it contains?
> >
> > I have several tables with OID-columns and I'd like to dump my DB
> without any
> > data in pg_largeobject (> 95% of the space is occupied by data in
> > pg_largeobject).
> > I've tried to exclude (using -T) the tables containing
> OID-columns but
> > pg_largeobject is still dumped containing the data it seems.
>
> A look at the pg_dump source code says that it skips blobs if any of
> -s, -n, -t are used. There's a -b switch to undo that and include
> them anyway, but no "inverse -b" to skip them in an otherwise-complete
> dump.
>
> So you could do something along the lines of pg_dump -t '*' ...
> although this will result in *all* non-schema-named objects being
> excluded, I believe, which might be a problem.
>
> regards, tom lane
>
> Hm:
> pg_dump -v -t '*' > andreak-noblob.dmp
> pg_dump: reading extensions
> pg_dump: identifying extension members
> pg_dump: reading schemas
> pg_dump: reading user-defined tables
> pg_dump: [archiver (db)] query failed: ERROR: permission denied for
> relation pg_authid
Off hand I would say you are running pg_dump as a user that is not a
superuser:
[snip]
Yes, since when should I not be able to dump a DB (owned by a non-superuser) as that user?
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På tirsdag 08. mars 2016 kl. 16:57:01, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Andreas Joseph Krogh <andreas@visena.com> writes:
> What I'm looking for is "inverse -b" in an otherwise complete dump. Any plans
> to add that?
[ shrug... ] Nobody ever asked for it before.
regards, tom lane
It surely helps testing production-datasets which contain lots of BLOBs where one wants to dump the production-data into a test-env. We have >1TB databases containing > 95% blobs so it would help us tremendously to have this option.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
On Tue, Mar 8, 2016 at 11:02 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På tirsdag 08. mars 2016 kl. 16:57:01, skrev Tom Lane <tgl@sss.pgh.pa.us>:Andreas Joseph Krogh <andreas@visena.com> writes:
> What I'm looking for is "inverse -b" in an otherwise complete dump. Any plans
> to add that?
[ shrug... ] Nobody ever asked for it before.
regards, tom laneIt surely helps testing production-datasets which contain lots of BLOBs where one wants to dump the production-data into a test-env. We have >1TB databases containing > 95% blobs so it would help us tremendously to have this option.
Probably you need to redesign the schema.
Move the blobs to a new/separate child table. Then you can exclude them.
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Вложения
På tirsdag 08. mars 2016 kl. 17:10:43, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Andreas Joseph Krogh <andreas@visena.com> writes:
> P�� tirsdag 08. mars 2016 kl. 16:54:19, skrev Adrian Klaver <
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:
> Off hand I would say you are running pg_dump as a user that is not a
> superuser:
> Yes, since when should I not be able to dump a DB (owned by a non-superuser)
> as that user?
The problem is that -t '*' is being interpreted as matching system
catalogs. You might be able to get somewhere with
pg_dump -t '*' -N pg_catalog ...
Probably we should fix pg_dump so it doesn't try to dump system catalogs
as tables, even if the switches seem to ask it to.
regards, tom lane
That didn't work either:
pg_dump -t '*' -N pg_catalog
pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation pg_authid
pg_dump: [archiver (db)] query was: LOCK TABLE pg_catalog.pg_authid IN ACCESS SHARE MODE
pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation pg_authid
pg_dump: [archiver (db)] query was: LOCK TABLE pg_catalog.pg_authid IN ACCESS SHARE MODE
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På tirsdag 08. mars 2016 kl. 17:38:04, skrev Joshua D. Drake <jd@commandprompt.com>:
On 03/08/2016 08:02 AM, Andreas Joseph Krogh wrote:
> På tirsdag 08. mars 2016 kl. 16:57:01, skrev Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>>:
>
> Andreas Joseph Krogh <andreas@visena.com> writes:
> > What I'm looking for is "inverse -b" in an otherwise complete
> dump. Any plans
> > to add that?
>
> [ shrug... ] Nobody ever asked for it before.
>
> regards, tom lane
>
> It surely helps testing production-datasets which contain lots of BLOBs
> where one wants to dump the production-data into a test-env. We have
> >1TB databases containing > 95% blobs so it would help us tremendously
> to have this option.
I have quite a few customers that would benefit from the ability to not
have blobs present in dumps.
Great! So how do we proceed to get "--no-blobs" added to pg_dump?
Maybe CommandPrompt and Visena should co-fund development of such an addition, if it's accepted by -hackers?
We'd be willing to pay for such an addition for the 9.5 branch, as a patch.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På tirsdag 08. mars 2016 kl. 21:03:01, skrev David G. Johnston <david.g.johnston@gmail.com>:
På tirsdag 08. mars 2016 kl. 17:38:04, skrev Joshua D. Drake <jd@commandprompt.com>:On 03/08/2016 08:02 AM, Andreas Joseph Krogh wrote:
> På tirsdag 08. mars 2016 kl. 16:57:01, skrev Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>>:
>
> Andreas Joseph Krogh <andreas@visena.com> writes:
> > What I'm looking for is "inverse -b" in an otherwise complete
> dump. Any plans
> > to add that?
>
> [ shrug... ] Nobody ever asked for it before.
>
> regards, tom lane
>
> It surely helps testing production-datasets which contain lots of BLOBs
> where one wants to dump the production-data into a test-env. We have
> >1TB databases containing > 95% blobs so it would help us tremendously
> to have this option.
I have quite a few customers that would benefit from the ability to not
have blobs present in dumps.Great! So how do we proceed to get "--no-blobs" added to pg_dump?Maybe CommandPrompt and Visena should co-fund development of such an addition, if it's accepted by -hackers?We'd be willing to pay for such an addition for the 9.5 branch, as a patch.Unfortunately this doesn't qualify as a bug fix - it is a new feature and thus is ineligible for inclusion in official 9.5David J.
Of course. That's why I mentioned that, if possible, an unofficial patch to 9.5 could be developed, funded partly by Visena (my company). Given that someone is willing to do this of course.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
Вложения
På søndag 23. oktober 2016 kl. 17:06:57, skrev Guillaume Lelarge <guillaume@lelarge.info>:
2016-03-08 21:06 GMT+01:00 Andreas Joseph Krogh <andreas@visena.com>:På tirsdag 08. mars 2016 kl. 21:03:01, skrev David G. Johnston <david.g.johnston@gmail.com>:På tirsdag 08. mars 2016 kl. 17:38:04, skrev Joshua D. Drake <jd@commandprompt.com>:On 03/08/2016 08:02 AM, Andreas Joseph Krogh wrote:
> På tirsdag 08. mars 2016 kl. 16:57:01, skrev Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>>:
>
> Andreas Joseph Krogh <andreas@visena.com> writes:
> > What I'm looking for is "inverse -b" in an otherwise complete
> dump. Any plans
> > to add that?
>
> [ shrug... ] Nobody ever asked for it before.
>
> regards, tom lane
>
> It surely helps testing production-datasets which contain lots of BLOBs
> where one wants to dump the production-data into a test-env. We have
> >1TB databases containing > 95% blobs so it would help us tremendously
> to have this option.
I have quite a few customers that would benefit from the ability to not
have blobs present in dumps.Great! So how do we proceed to get "--no-blobs" added to pg_dump?Maybe CommandPrompt and Visena should co-fund development of such an addition, if it's accepted by -hackers?We'd be willing to pay for such an addition for the 9.5 branch, as a patch.Unfortunately this doesn't qualify as a bug fix - it is a new feature and thus is ineligible for inclusion in official 9.5David J.Of course. That's why I mentioned that, if possible, an unofficial patch to 9.5 could be developed, funded partly by Visena (my company). Given that someone is willing to do this of course.That probably should look like the patch attached. It applies cleanly on HEAD, and works AFAICT. If this patch seems interesting enough, I'll add it to the next commit fest (note for myself, update the ref/pg_dump.sgml documentation file).
For Andreas' information, it also applies on 9.5, though I didn't check if it worked afterwards.
+1 for adding it to the commitfest.
It's almost scary how simple this patch is and noone ever got around to implement it.
Thanks, I'll test it on 9.5 soon.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
På søndag 23. oktober 2016 kl. 19:15:17, skrev Andreas Joseph Krogh <andreas@visena.com>:
På søndag 23. oktober 2016 kl. 17:06:57, skrev Guillaume Lelarge <guillaume@lelarge.info>:2016-03-08 21:06 GMT+01:00 Andreas Joseph Krogh <andreas@visena.com>:På tirsdag 08. mars 2016 kl. 21:03:01, skrev David G. Johnston <david.g.johnston@gmail.com>:På tirsdag 08. mars 2016 kl. 17:38:04, skrev Joshua D. Drake <jd@commandprompt.com>:On 03/08/2016 08:02 AM, Andreas Joseph Krogh wrote:
> På tirsdag 08. mars 2016 kl. 16:57:01, skrev Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>>:
>
> Andreas Joseph Krogh <andreas@visena.com> writes:
> > What I'm looking for is "inverse -b" in an otherwise complete
> dump. Any plans
> > to add that?
>
> [ shrug... ] Nobody ever asked for it before.
>
> regards, tom lane
>
> It surely helps testing production-datasets which contain lots of BLOBs
> where one wants to dump the production-data into a test-env. We have
> >1TB databases containing > 95% blobs so it would help us tremendously
> to have this option.
I have quite a few customers that would benefit from the ability to not
have blobs present in dumps.Great! So how do we proceed to get "--no-blobs" added to pg_dump?Maybe CommandPrompt and Visena should co-fund development of such an addition, if it's accepted by -hackers?We'd be willing to pay for such an addition for the 9.5 branch, as a patch.Unfortunately this doesn't qualify as a bug fix - it is a new feature and thus is ineligible for inclusion in official 9.5David J.Of course. That's why I mentioned that, if possible, an unofficial patch to 9.5 could be developed, funded partly by Visena (my company). Given that someone is willing to do this of course.That probably should look like the patch attached. It applies cleanly on HEAD, and works AFAICT. If this patch seems interesting enough, I'll add it to the next commit fest (note for myself, update the ref/pg_dump.sgml documentation file).
For Andreas' information, it also applies on 9.5, though I didn't check if it worked afterwards.+1 for adding it to the commitfest.It's almost scary how simple this patch is and noone ever got around to implement it.Thanks, I'll test it on 9.5 soon.
It's totally OK for me to use 9.6 (now that it's released) to dump 9.5 DBs, so I'm all good with this patch, thanks!
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения
Вложения
På søndag 23. oktober 2016 kl. 20:44:34, skrev Guillaume Lelarge <guillaume@lelarge.info>:
[snip]Remember that, if it gets commited, it'll be for next release (aka 10), and not 9.6 and earlier.
The patch working for 9.6 is fine for me, but getting this in master is of course the best.
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963