Exclude pg_largeobject form pg_dump

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?

Thanks.
--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På tirsdag 08. mars 2016 kl. 15:43:37, skrev Adrian Klaver <
adrian.klaver@aklaver.com <mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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:

aklaver@panda:~> pg_dump -v -d test -U aklaver -t '*'
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

aklaver@panda:~> pg_dump -v -d test -U postgres -t '*'
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
....

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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På tirsdag 08. mars 2016 kl. 16:54:19, skrev Adrian Klaver <
adrian.klaver@aklaver.com <mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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 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
andreas@visena.com
www.visena.com
<https://www.visena.com&gt;

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På tirsdag 08. mars 2016 kl. 17:10:43, 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. 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

 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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.

JD

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På tirsdag 08. mars 2016 kl. 17:38:04, skrev Joshua D. Drake <
jd@commandprompt.com <mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

On Tue, Mar 8, 2016 at 9:45 AM, Andreas Joseph Krogh <andreas@visena.com>
wrote:

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.5

David J.

På tirsdag 08. mars 2016 kl. 21:03:01, skrev David G. Johnston <
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>:
On Tue, Mar 8, 2016 at 9:45 AM, Andreas Joseph Krogh <andreas@visena.com
<mailto:andreas@visena.com>> wrote:
På tirsdag 08. mars 2016 kl. 17:38:04, skrev Joshua D. Drake <
jd@commandprompt.com <mailto: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>

<mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>>:

     Andreas Joseph Krogh <andreas@visena.com <mailto: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.5

David 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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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>:

On Tue, Mar 8, 2016 at 9:45 AM, Andreas Joseph Krogh <andreas@visena.com>
wrote:

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.5

David 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.

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com

På søndag 23. oktober 2016 kl. 17:06:57, skrev Guillaume Lelarge <
guillaume@lelarge.info <mailto:guillaume@lelarge.info>>:
2016-03-08 21:06 GMT+01:00 Andreas Joseph Krogh <andreas@visena.com
<mailto:andreas@visena.com>>: På tirsdag 08. mars 2016 kl. 21:03:01, skrev
David G. Johnston <david.g.johnston@gmail.com
<mailto:david.g.johnston@gmail.com>>:
On Tue, Mar 8, 2016 at 9:45 AM, Andreas Joseph Krogh <andreas@visena.com
<mailto:andreas@visena.com>> wrote:
På tirsdag 08. mars 2016 kl. 17:38:04, skrev Joshua D. Drake <
jd@commandprompt.com <mailto: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>

<mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>>:

     Andreas Joseph Krogh <andreas@visena.com <mailto: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.5

David 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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

På søndag 23. oktober 2016 kl. 19:15:17, skrev Andreas Joseph Krogh <
andreas@visena.com <mailto:andreas@visena.com>>:
På søndag 23. oktober 2016 kl. 17:06:57, skrev Guillaume Lelarge <
guillaume@lelarge.info <mailto:guillaume@lelarge.info>>:
2016-03-08 21:06 GMT+01:00 Andreas Joseph Krogh <andreas@visena.com
<mailto:andreas@visena.com>>: På tirsdag 08. mars 2016 kl. 21:03:01, skrev
David G. Johnston <david.g.johnston@gmail.com
<mailto:david.g.johnston@gmail.com>>:
On Tue, Mar 8, 2016 at 9:45 AM, Andreas Joseph Krogh <andreas@visena.com
<mailto:andreas@visena.com>> wrote:
På tirsdag 08. mars 2016 kl. 17:38:04, skrev Joshua D. Drake <
jd@commandprompt.com <mailto: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>

<mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>>:

     Andreas Joseph Krogh <andreas@visena.com <mailto: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.5

David 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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

2016-10-23 20:37 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:

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>:

On Tue, Mar 8, 2016 at 9:45 AM, Andreas Joseph Krogh <andreas@visena.com>
wrote:

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.5

David 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.

Done, https://commitfest.postgresql.org/11/833/

It's almost scary how simple this patch is and noone ever got around to

implement it.

Nobody had the time (like me, till now) or the motivation.

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!

Remember that, if it gets commited, it'll be for next release (aka 10), and
not 9.6 and earlier.

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com