Subject: Re: PostgreSQL equivalents to dbms_metadata.get_ddl &
 dbms_metadata.get_dependent_ddl
To: pgsql-admin@lists.postgresql.org
References: 
 <CAGA3vBtNLpGdPG=4gdzvD9zoTSz+52JUkfYCsNSi+mv5Yr+GVw@mail.gmail.com>
 <A6F7CC72-F6AC-49B6-8DEC-19787D18D7F5@gmail.com>
 <CAGA3vBsD_xiwuXWua6bceGToc9ZX1fmOfiaDXkw29zfqLhQ=Uw@mail.gmail.com>
 <ac9c17a9-016a-e84b-1ef3-14ce4380f05d@sqlexec.com>
 <CAGA3vBv-BkLgTyf-DRW9DYjnF2=BMuKOpT0Gp7cCD1oWDvSxqw@mail.gmail.com>
 <938034.1679325008@sss.pgh.pa.us>
 <CAGA3vBsvSs6ga3UJ2nbj3+FopLAkyukorjSabJH4ez6qe5DSgQ@mail.gmail.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <aaba797d-7d79-b075-8aaf-223ec6fc1bbf@gmx.net>
Date: Mon, 20 Mar 2023 17:10:42 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: 
 <CAGA3vBsvSs6ga3UJ2nbj3+FopLAkyukorjSabJH4ez6qe5DSgQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:mE1p/i0EIf4=;1d5TiPeIdWG9shszx7W3z7FQZZG
 cuFkdEqq/+A1fz6sdXdXtmMFpsoaJHchk8vP3E/hQqNfMEpe6nTi4DiR+fGkvm6zS7PhKpGrX
 f7ymtkSE8E3tMgZCH4Vd2Q3i9D5cHYcL4cSs5y228wNm/fvS4VzWtay/LGpepTxCin/IhxttF
 j2KcE5bdNxQZr5tjDnfOQ7yXqTLPpekO5zbv7bWql+h8SQ46n3yWWtw6UvUJofsE8skrUVht+
 S/xL2zl+BWCI851C/EAyeVDMq4cEKnDxDFed30Uz7cZ0FHP3XZzPaey7GjsQSj8R7fwv37t5K
 tGzRB+nqHGgFuy6ad0cjbvI1lAnEwlZKN5ProE3bhcfj5U4ro6PYwvUnWg2a37GIbennLZB6V
 g19vHM6rJZTUK5Cd3uykl1epz1rpE+q/RkLY4LPgI7W8HW0EHLqbgmjvYrAS/AaDCz1wSDBif
 2vSw0lTb42bHX7ek+uMuZo8P0qa1NIMCYyBy0brjfZ3teV5rGadAZoFCqOB1zrBqwLfzogeOZ
 dFdrzQzGHx4v3GAH1eeDYa9j7+J1ioozCjIBU+YvFWnt+Tv6VcZMUvrjsiRYHPXEbQOKwOY/g
 EWzCgXIYnKuj2uwHNzLQN97y66IhxwsT5l8SfekoSpdBK8SsmNPNerK9wPyzNUMleJHNuIpz5
 mqN1jV+GhMh6wRolVyPH1Qc9+4xHovdCrMFpZpbk+Wn6pZeRo0VCtFyLhcxz0/Fi/s50mPp60
 xoQ3NDctJg3POF/XYqe8LXNt5iu1BI6pq9lNNUI7RUYP9tQZ0v5rYyyiy1XIg8DVYG+B7uv2G
 KePFtjUjJ6/huexbsO9SgzkSKR49JOZDGBOR/UG03cVXBRSnj0tjkcnW5vxnq1mLQq8YqgXoh
 IpOWR09rdVcBRLizvYzsqo6de05Mjwtvd+xuYAdHMq7g0omet2JSP7Irv+wje5EmGPQRFP4FJ
 PRszTg==
Archived-At: 
 <https://www.postgresql.org/message-id/aaba797d-7d79-b075-8aaf-223ec6fc1bbf%40gmx.net>
Precedence: bulk

richard coleman schrieb am 20.03.2023 um 16:48:
> My use case is copying and maintaining the same tables in Oracle and
> PostgreSQL as well as detecting and applying DDL changes between the
> two systems.
We are using Liquibase as a schema migration tool which allows us to
specify a single "changelog" (using their XML format) that can be
applied to Oracle and Postgres alike.

So we never need to "compare" definitions, we just run the changelog
script to update the database schema.

Some differences in e.g. available data types can be dealt with using
Liquibase's properties or if everything else fails using different
changesets for Postgres and Oracle (which is typically only needed
for views and procedural code).


Of course it might not be feasible to re-structure your existing
infrastructure for schema migrations to use a completely different
approach.

Thomas