> Instead of something like 'shake' (which more or less works, even
> though it doesn't use fallocate and friends) I frequently use either
> CLUSTER (which is what Greg Smith is suggesting) or a series of ALTER
> TABLE ... ALTER COLUMN... which rewrites the table. With PG 9 perhaps
> VACUUM FULL is more appropriate. Of course, the advice regarding
> using 'shake' (or any other defragmenter) on a "live" postgresql data
> directory is excellent - the potential for causing damage if the
> database is active during that time is very high.
>
>
I agree that unless it makes sure there are no open file handles before
moving the file, there is a high chance of corrupting data, and if it
does check, there is little chance it will do anything useful on a live
DB, since it will skip every open file.
Does vacuum full rewrite the whole table, or only the blocks with free
space? If it only rewrites the blocks with free space, the only
solution may be exclusive table lock, alter table to new name, create
old table name as select * from new table name. I also like the cluster
idea, but I am not sure if it rewrites everything, or just the blocks
that have out of order rows, in which case, it would not work well the
second time.