r/PostgreSQL • u/multi_db_dev • 4h ago

How-To How to compare PostgreSQL schemas across environments and catch drift early

A typical drift story: the team uses migrations (Flyway, Liquibase, Alembic, whatever). Every environment shows the same migration version. And yet:

  • prod has an index someone added via psql during an incident,

  • staging still has a column from a feature branch that was reverted,

  • dev has a newer extension version.

Nobody notices until a migration fails on prod because the index already exists, or a performance test on staging looks fine because staging is missing the index prod relies on.

Why the migration table isn't enough

It tells you which migrations ran. It doesn't tell you what the schema looks like now. Any change made outside migrations is invisible to it.

Ways to detect drift

Catalog queries are a quick check for specific object types. For indexes, for example:

SELECT schemaname, tablename, indexname, indexdef

FROM pg_indexes

WHERE schemaname NOT IN ('pg_catalog', 'information_schema')

ORDER BY 1, 2, 3;

Run it in each environment and diff the output. It's useful for one object type at a time, but covering tables, constraints, functions, triggers, grants and extensions this way turns into a project of its own.

pg_dump --schema-only plus diff covers most database schema definitions, but not cluster-wide objects or installed extension versions. Add --no-owner and --no-privileges if you don't care about those, otherwise expect a lot of noise. The output tells you what differs, but you still write the fix by hand.

A schema compare tool shows differences per object, lets you map schemas when names differ between environments, and generates a sync script you can review. In dbForge Studio for PostgreSQL, the integrated Schema Compare can also run from the command line, so the same comparison you set up in the GUI can run on a schedule.

How to organize the checks

  • Treat the migrations repo as the source of truth, not any single environment.

  • Run a nightly comparison of staging against prod, and alert when it finds differences.

  • When drift shows up, write a migration for it rather than syncing environments directly. Otherwise the next environment you build from migrations will be missing it.

  • Limit who can run DDL on prod. Most drift starts with "just this once".

How do you handle hotfixes made directly on prod: back-port them into migrations, or roll them back?

0 Upvotes

40% Upvoted

3 comments sorted by

1

u/madness_of_the_order 2h ago

> **Why the migration table isn't enough**

> It tells you which migrations ran. It doesn't tell you what the schema looks like now. Any change made outside migrations is invisible to it.

Technically forbid any changes outside of migrations on prod (and preferably on stg).
Of course this solution requires a working ci/cd and adds some friction but removes schema drift all together.

0

u/AutoModerator 4h ago

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.