r/postgres

Hi, as I learn about and work with Postgres I will have to do some db migrations.

Most of my servers are managed Postgres but also some are on-prem on vms.

Anyway, when you are asked to migrate Postgres databases, what's your goto procedure?

Backup/restore?

Logical replication?

Cloud based migration services?

I'm a backend engineer building a tool to prevent Postgres migration outages and I'm in pure research mode right now — no product pitch, just trying to understand how widespread this is.

Our worst case: an ALTER TABLE on a 30M row table held an AccessExclusiveLock for 22 minutes. Everything queued up. Users saw timeouts. We found out from customer support, not monitoring.

Has this happened to your team? How do you currently check migrations before pushing to prod? Do you use squawk, strong_migrations, manual review, or just hope for the best?

Genuinely trying to understand the problem before I build anything. All experiences welcome.

I’m trying to clean up our Postgres workflow a bit. Right now it’s roughly. Write query, test locally, adjust migration, push to review, run in staging, hope staging is close enough, deploy, then watch logs like a nervous raccoon.

For data work, the messy part is not writing the SQL. It’s everything around it: checking row counts, validating changed data, making sure indexes exist where they should, catching schema drift, and not finding out two days later that a report quietly broke. How does your team handle the path from scratch in Postgres? Do you rely mostly on migration tools, schema compare, CI checks, manual review, dbt/Airflow jobs, or just a lot of discipline and fear?

Half our team is on SQL Server, the other half on PostgreSQL.  We've been looking at dbForge because it covers both, but honestly the harder problem isn't the tool. SQL Server people think in SSMS. Postgres people think in pgAdmin or DataGrip, and they've been doing it that way for years. 

Every time we try to standardize on something it turns into a workflow debate more than a technical one. Different habits, different expectations for the UI. 

Anyone actually using dbForge across both RDMSs in the same team? Did the mixed-engine support help, or did people just end up sticking with different tools anyway? 

When a Postgres query gets slow, what’s your first move?

EXPLAIN ANALYZE, logs, pg_stat_statements, checking indexes, staring at the query until it confesses? Trying to compare how people actually optimize queries.

I’m used to the SSMS-style workflow for checking schema changes, but now I need something similar for MySQL and PostgreSQL. Main need is simple. Compare two databases, see what changed, review the diff, and avoid doing it manually. What tools do you trust for schema comparison in MySQL/PostgreSQL? DBeaver, dbForge, DataGrip, Liquibase/Flyway, something else?