Postgres pg_dump dumps database in a different order every time

I am writing a PHP script (which also uses linux bash commands) which will run through test cases by doing the following:

I am using a PostgreSQL database (8.4.2)…

1.) Create a DB 2.) Modify the DB 3.) Store a database dump of the DB (pg_dump)

4.) Do regression testing by doing steps 1.) and 2.), and then take another database dump and compare it (diff) with the original database dump from step number 3.)

However, I am finding that pg_dump will not always dump the database in the same way. It will dump things in a different order every time. Therefore, when I do a diff on the two database dumps, the comparison will result in the two files being different, when they are actually the same, just in a different order.

Is there a different way I can go about doing the pg_dump?

Thanks!

Answer

It is impossible to force pg_dump to dump data in any particular order, as it dumps data in disk order – it is much faster this way.

You can use “-a -d” options for pg_dump and then “sort” output, but newlines in data will make sorted output unusable. But for basic comparison, whether anything changed, it would suffice.