Thread: pg_dumpall forces plain text format

I was trying to do a data+lo dump using pg_dumpall:

pg_dumpall -C -a -b -o -Ft > dumptest.data

Unfortunately I kept getting this error:

pg_dump: large object output is not supported for plain text dump files.
pg_dump: (Use a different output format.)

Upon looking at the pg_dumpall shell script I found out why:

PGDUMP="${PGPATH}/pg_dump $connectopts $pgdumpextraopts -Fp"

Is there a particular reason why plain text is forced?  Could this be removed
in the next version?  Or even moved to a pg_dumpall command line option?  I
really dislike using nonstandard tools.

Regards,
Andrew

Andrew Kohlsmith <akohlsmith@mixdown.org> writes:
> Is there a particular reason why plain text is forced?

Because pg_dumpall is producing a script file.

This is obviously not optimal, but it's not very clear how to do better.

            regards, tom lane

Andrew Kohlsmith <akohlsmith@mixdown.org> writes:
> Is there a way to start a transaction at the start of
> the dump and hold it throughout successive connections, finally releasing it
> at the end?

No, and since one backend can only talk to one database, I don't foresee
that changing.

> Or is that on the TODO for whenever foreign keys can be across databases?

I very seriously doubt that PG will *ever* support foreign keys across
databases.

The current development direction is not to improve communication across
databases, but to support schemas and improve privilege checking so that
users can more usefully be assigned separate schemas within a single
database.  I think that multiple databases in a cluster will eventually
be used only for the cases where you *want* airtight separation between
two collections of data.

Hence, I'm not particularly concerned by complaints about lack of
cross-database functionality --- the folks who think they want that
actually want cross-schema operations, which will work fine in 7.3.

            regards, tom lane

You could fake some of this (select only) by using the dblink stuff in
contrib.....  You could link back to yourself and make it work.  Maybe if
you REALLY need it, you could modify dblink to allow updates as well as
selects.  If you really need it that bad, you have the source, write it.

grant wrote:
> You could fake some of this (select only) by using the dblink stuff in
> contrib.....  You could link back to yourself and make it work.  Maybe if
> you REALLY need it, you could modify dblink to allow updates as well as
> selects.  If you really need it that bad, you have the source, write it.

dblink in cvs will work on 7.2.x and supports UPDATE/INSERT/DELETE.

Joe

> > Is there a particular reason why plain text is forced?
> Because pg_dumpall is producing a script file.
> This is obviously not optimal, but it's not very clear how to do better.

Agreed.  :-)

I notice that pg_dumpall just uses a query to grab a list of databases and
some information about them and then cycles through them and runs pg_dump for
each one to dump it.  I could easily do this myself but that would mean that
both pg_dumpall and whatever method I come up with do not guarantee
referential integrity across databases (I'm specifically thinking about
foreign keys).

Now IIRC, Postgres doesn't allow foreign keys across databases so this point
is moot at this time.  Is there a way to start a transaction at the start of
the dump and hold it throughout successive connections, finally releasing it
at the end?  Or is that on the TODO for whenever foreign keys can be across
databases?

Regards,
Andrew

> > Or is that on the TODO for whenever foreign keys can be across databases?
> I very seriously doubt that PG will *ever* support foreign keys across
> databases.

No problem.  I'm not that advanced of a user or admin to want it, I was just
trying to peer into the crystal ball and try to anticipate future problems.
:-)

> The current development direction is not to improve communication across
> databases, but to support schemas and improve privilege checking so that
> users can more usefully be assigned separate schemas within a single
> database.  I think that multiple databases in a cluster will eventually
> be used only for the cases where you *want* airtight separation between
> two collections of data.

Understood.

> Hence, I'm not particularly concerned by complaints about lack of
> cross-database functionality --- the folks who think they want that
> actually want cross-schema operations, which will work fine in 7.3.

Thank you for your time and comments.  I feel a lot "safer" about these
backups now.

Regards,
Andrew

Andrew Kohlsmith wrote:
> > > Is there a particular reason why plain text is forced?
> > Because pg_dumpall is producing a script file.
> > This is obviously not optimal, but it's not very clear how to do better.
>
> Agreed.  :-)
>
> I notice that pg_dumpall just uses a query to grab a list of databases and
> some information about them and then cycles through them and runs pg_dump for
> each one to dump it.  I could easily do this myself but that would mean that
> both pg_dumpall and whatever method I come up with do not guarantee
> referential integrity across databases (I'm specifically thinking about
> foreign keys).
>
> Now IIRC, Postgres doesn't allow foreign keys across databases so this point
> is moot at this time.  Is there a way to start a transaction at the start of
> the dump and hold it throughout successive connections, finally releasing it
> at the end?  Or is that on the TODO for whenever foreign keys can be across
> databases?

That is far off our TODO radar, perhaps never.  Sorry.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026