pg_dumpall forces plain text format

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

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

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

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

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

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.

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