[SOLVED] Postgres schema compare that works with declarative partitions

Postgres schema compare that works with declarative partitions

Are there any tools that can compare the schema of two Postgres databases to an SQL script and is able to handle declaratively partitioned tables correctly?

I've been searching high and low. DataGrip 2018.2 is able to generate DDL that correctly reproduces a declaratively partitioned table and all of the partitions, but it does not generate a script. Migra (a Python tool) generates a script, but treats partitions as standalone tables.

Solution

I had a similar issue but it was related to inheritance table in postgresql and I tried the following 2 options with success:

1st option:

pg_dump -s db1> first pg_dump -s db2> second diff first second

(obviously wont generate SQL to remedy the differences)

2nd option: TiCodeX SQL Schema Compare (https://www.ticodex.com) It's a nice tools that runs in Windows, Linux and Mac and can compare the schema of MS-SQL, MySQL and PostgreSQL database. Easy to use and effective. It may help you.