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@xxxxxx> 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@xxxxxxxxxxxxxx) 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
