Thread: Option for pg_dump to dump tables in clustered index order
Hi All,
Having pg_dump dump tables in clustered index order is something we've found we've needed a fair number of times (for ex. when copying a large logging tables or sets of tables out of one database where the order is not maintained into another for running a bunch of backend analysis) as it saves us the clustering step which is often longer then the copy step itself.
I wanted to gauge the interest in adding an option for this to pg_dump. A (not production ready) patch that we've been using off of the 9.1.2 tag to implement this is attached or can be viewed here. It adds a --cluster-order option to pg_dump. If people have any suggestions on better ways of pulling out the order clause or other improvements that would be great too.
Tim
Attachment
On Wed, Feb 22, 2012 at 6:17 PM, Timothy Garnett <tgarnett@panjiva.com> wrote: > I wanted to gauge the interest in adding an option for this to pg_dump. I was thinking about an application for much the same feature. Consider the case where you have a relatively small database such as the accounting records for a not-hugely-active business. And you'd like to handle backups via checking them into an SCM repository. Thus... #!/bin/sh cd $HOME/GitBackup/Databases pg_dump -h dbserver -p 5432 accounting > accounting.sql git add accounting.sql git commit -m "Latest backup" accounting.sql If the database's tables have gotten clustered, then the order of data will tend to be consistent, and differences between versions of "accounting.sql" will generally represent the actual differences. If, on the other hand, tables are not clustered, then dumps will find tuples ordered in rather less predictable fashions, and the backups will have more differences indicated. I was thinking about writing a script to run CLUSTER before doing backups. For that step to be part of pg_dump is certainly an interesting idea. -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"