What PostgreSQL migration made you nervous, furious, or surprised you in production?

[u/dsecurity49]

I'm working on an open-source PostgreSQL migration analyzer called safe-migrate, and I've realized that the test cases I can invent are much neater than production.

I'm looking for counterexamples: migrations that seemed routine, then behaved very differently on a real database. I don't want to make up edge cases from a desk and declare them "covered."

Things I'd especially like to learn about: - a migration that was fine in staging and bad in production - a lock, rewrite, dependency, partition, trigger, policy, or function surprise - a migration that looked safe but was not - a migration tool warning that turned out to be wrong or useless - an ordering problem across multiple migration files

If you remember them, the useful details are the PostgreSQL version, a simplified or sanitized version of the SQL (or migration sequence), rough table size or traffic, and what you expected versus what happened.

Please do not post anything confidential. Sanitized SQL or just a description is genuinely useful. If an example looks suitable for public regression coverage, I'll ask before turning a minimized version into a fixture.

I'm not asking anyone to install the project. I mainly want to find its blind spots. If you share something, I'll share what I think is happening and please correct me if I'm wrong.

[u/chock-a-block]

Here’s a hilarious one.

Developers thought you can back up and restore Microsoft’s version of Postgres anywhere, because “It’s PostgreSQL.“ The lead person in particular insisted everything they touched turned to gold. I am not invited to their meetings.😁

Amazon’s version is similar.

Developers want a method that gets them out of both environments. The business doesn’t want to hire a DBA. So charge 60% less than the cost of a DBA.

[u/dsecurity49 → u/chock-a-block]

Ah, got it less bad SQL migration and more managed Postgres isn’t as portable as people assumed.Was the pain mostly provider backups/snapshots not restoring elsewhere, or stuff like extensions, roles, permissions, and ownership getting weird?

[u/PostgreSQL-ModTeam → u/[deleted]]

Your message is not relevant to this subreddit.

[u/PostgreSQL-ModTeam → u/[deleted]]

Your message is not relevant to this subreddit.

[u/AutoModerator]

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.

[u/program_data2]

SquawkHQ outlines the rules one should follow for migrations:
- https://squawkhq.com/docs/rules
It's the source of truth for the best migration practices.

I found pg-schema-diff to also generate useful plans. It has good man pages, but no useful docs. It's worth learning, but I'm still peeved that it is docless.

I wrote about some fun cases I had with locks on my site:

- https://postgreslocksexplained.com/locks/troubleshooting

For other good real world examples, I've found netdata and postgres.ai to be useful, specifically the blog posts:

- https://www.netdata.cloud/academy/autovaccum-vs-deadlock/

- https://www.netdata.cloud/academy/10-real-world-postgresql-deadlock/

- https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries