Re: Exclude pg_largeobject form pg_dump - Mailing list pgsql-general

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>
> <https://www.visena.com>


--
Adrian Klaver
adrian.klaver@aklaver.com