0

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.

Greg Graham
  • 473
  • 7
  • 18

1 Answers1

1

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.
Andrea Spinedi
  • 46
  • 2
  • This is what I ended up doing. One caveat that I ran into is that pg_dump will dump objects in whatever order they are stored internally in postgres, so to get this to work right I had to "sort" the pg_dump output: sort columns within "create table statements", sort "create table" statements by table name, etc. Fortunately it isn't as big a pain in the ass as it sounds. – Greg Graham Dec 21 '18 at 23:12