Hello,
first off I realize my approach may not be the best solution to my problem and I am open to other approaches. I'm looking for suggestions/feedback on the issue below, and if confirmation of if the collation order is what dictates the column order for table output.
The main goal is I'd like our CI system to create a new database, run all the migrations, dump the database schema and then compare it to what we have recorded and error out if there is a difference. We've had issues in the past due to a developer's database having left bad changes in their local DB which then get pushed into the schema, but not into the migrations, not super critical to the system but causes confusion.
unfortunately pg_dump has two problems for this usecase.
1) schema-only generates superfluous comments, which are inconsistent between version. easily fixed post dump, but would be nice to add a option to not dump these comments.
2) column ordering within a table is inconsistent. couple of our tables columns get reordered (in a consistent manner) depending on which developer generates the schema.
The inconsistent columns is the big problem, I suspect it is somehow related to the collation order of the database. What is particularly interesting are the differences between each OS's collation order for the same collation value, a
great post about it.
Beyond that, a quick search shows this to be a fairly common question, with no real good solutions.
--