Thread

1. Option for pg_dump to dump tables in clustered index order

   Timothy Garnett <tgarnett@panjiva.com> — 2012-02-22T23:17:31Z

   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<https://github.com/tgarnett/postgres/commit/d4412aa4047e7a0822ee93fa47a1c0d282cb7925>.
    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
   

2. Re: Option for pg_dump to dump tables in clustered index order

   Christopher Browne <cbbrowne@gmail.com> — 2012-02-23T15:14:07Z

   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?"