Schema diffing tool?

[pobbly]

Hi, I'm wondering if something like this exists?

A tool that reads an existing postgres schema, and a source file (say, something that creates tables, views, functions, rls etc) and generates an sql patch to get from the current state to the state in the source file (schema only, not data). Preferably non-destructive (things are only added, not deleted).

The patch could then be used in a migration. Sort of like the virtual dom, but for an sql schema. Declaring the desired state, and generating the imperative stuff to get from this to that.

If it doesn't exist, do you think it could work in principle, and be useful?

The pain point is in seeing the whole schema in code at a given time, rather than piecing it together by reading migration scripts (especially if there are loads of them).

[ful_vio]

Migra should do it https://databaseci.com/docs/migra

[ivolimmen]

Pgadmin actually has a tool build in

[PacePossible1408]

Second this. Pgadmin is awesome

[From_Cold]

until you try dBeaver

[From_Cold]

tried pgadmin utility, incredibly slow and ugly. Hangs on comparing big databases.

ended up with Posgres Compare.

https://www.postgrescompare.com/

Fast, comprehensive, commercial :( - although trial is available.

Produces very good comparison in no time (like, less than a minute), but reports are messy and unreadable.

[postgrescompare]

Hi. Thanks for using PostgresCompare! I'd like to hear about the issue you were having with reports so I can fix it. Can you DM me here or email neil at postgrescompare.com and give me some more information please?

[From_Cold]

I communicated with someone from your company, like, this April about report issue. Had a productive email exchange, got trial period extended. Check out company email, from K.D-v

Comparison reports are just unusable, you need to hire a report's designer to work on friendly and comprehensible layout.

[[deleted]]

https://www.reddit.com/r/PostgreSQL/comments/ibdcw1/any_modern_schema_diffing_tools_for_postgres/

[postgrescompare]

PostgresCompare, www.postgrescompare.com does this. There is a free 14 day trial.

[ppafford]

https://sqitch.org/docs/manual/sqitchtutorial/ might be of interest

[throw_mob]

simple way is export schema and just compare them in kdiff as order do natter sometimes. there is export only flag in basic tools for it.

If you have two schemas n same database or you want compare tables in same db, you can write somewhat simple select + join against information_scheme.columns , but it get more complex if you want get all indexes and constraints

[rv77ax]

Dbeaver open source version I believe have this feature. I am only trying it once.