Re: postgres clustering interactions with pg_dump

On Mon, Jun 11, 2012 at 9:55 AM, Fitch, Britt <brittf(at)telenav(dot)com> wrote:
> Hi, I have a table that I am clustering on an index.
>
> I am then dumping that table via pg_dump –Fc and loading it into another
> database via pg_restore.
>
> It is unclear to me though if the clustering I did in the original database
> is preserved during the dump & restore or if I would still need to perform a
> CLUSTER again once the data was loaded into the new database.
>
> Can anyone confirm this?

The rows will end up in the new table in the same physical order that
they were stored in the dump file.

You might want to look at pg_stats.correlation for the clustered
column - that's often a good way to know whether things are ordered
the way you expect, and it's updated every time the table is analyzed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company