Hello, today I stumbled across a interesting question about the order rows are dumped out while exporting a database with pg_dump. I know questions like this are around this list sometimes, but I think this is a bit more special.
First of all I know that dumping a database is a somewhat nondeterministic process. It's hard to determine in which order objects are dumped. If my understanding is correct when it's about dumping the rows from a specific table the rows will appear in the dump in disk-order. This order is changed everytime there are updates to rows in that table and how often a vacuum occurs to release old row versions. >From some testing I "verified" this by experimentation - just created a new >table with a known order of rows and dumped it. The dump was ordered in the >same way the rows were inserted. Same again with updating some of the rows >those rows appeard at the end of the dump. At last I vacuumed the database and >updated some rows they appeared in the "spaces" the previous updated rows left >behind. Exactly what I expected :-) Now for my question - we have a case where rows are inserted in order and are updated rarely (about 10% of the rows and often in order) and we are seeking for a solution to make the dump (e.g. the backup of this database) more random at database level (by influencing the on disk order?). Obvious way would be to update all rows randomly by software before dumping but that would be a manual process. So anyone out there with some other ideas (I found this interesting as I'm getting a more insight view of my favourite database)? Thanks in advance, Jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
