It isn't guaranteed, but I think a clustered index on the attrs you want
the dump ordered by will give an ordered dump.
This may depend on your filesystem, and on what else your system
is doing at the time, as interupted disk reads may disrupt the sequence.
It has worked for me on Suse Linux with Reiser FS when the dump was
the only (major) process running.
You can also cut out the data text of the dump, if you used COPY format
& not inserts, then use sort & awk to order the records appropriately,
then paste them back in (at least on Linux/UNIX you can, on Windows this
would need Cygwin installed).
None of which is ideal or robust, & having pg_dump able to generate
ordered dumps natively would be useful.
Cheers,
Brent Wood
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> Josh Williams <joshwilliams@ij.net> 11/11/08 8:04 PM >>>
On Mon, 2008-11-10 at 17:05 -0800, Jeff Davis wrote:
> Is there any interest in an optional mode for pg_dump to order the
> output so that it's easier to use diff?
>
> I don't think it would make the output 100% deterministic, but it would
> make it easier to at least compare the data for small databases.
That'd be cool. I'd done some poking around on the topic a little while
back. The goal was to make the output more predictable so that backups
would be more efficient, specifically with a product that does binary
diffs of some sort.
I may still have some notes somewhere if you're interested. But I
believe the idea was to use COPY with a SELECT statement. The
non-trivial part was to figure out a proper ordering to use.
Or did you plan on combining it with -t, where you could then specify
the ordering for each table?
> I think this has been brought up before, but I couldn't find the thread,
> so I don't know what conclusion was reached.
>
> Regards,
> Jeff Davis
(... Plus, you potentially get a free CLUSTER on a reload.)
- Josh Williams
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.