Stacks on Stacks

Comparing schemas across Postgres databases

In an ideal world, no one would ever run schema changes in production that weren’t already tested and applied in preceding environments. We’ve learned that best practices dictate you roll migrations up from local development, then test, then staging, and finally, production. This gives you plenty of chances to catch errors and furthermore, you ensure the state of these environments are consistent and repeatable(You are using source control, aren’t you?).

However, this often isn’t the ideal world. Maybe someone bypassed your process and ran SQL directly against prod? Or maybe they were goaded into it by a team who didn’t want to wait and go through the regular release process. I firmly believe this should never ever happen, but if it does, you need a way to restore balance to the force and ensure your schemas are in alignment once more. Enter, pg_dump.

$ PGPASSWORD='mypassword' pg_dump -s -h myhost -U user mydb > schema-dump.sql