Generatechanglog for both Postgres and Oracle?

General Discussion
1

We are moving an old large application from Oracle to Postgres. The application is installed on multiple customer servers, so for a time we need to able to update both Postgres and Oracle at the same time. This situation pushes us to finally implementing Liquibase on the app.

I’ve generated changelog with “liquibase generate-changelog” command for both Oracle and Postgres database. I believe best will be to have separate changelog exported files for Postgres and Oracle, but are not sure if this is true, and in case what is best way to configure this?

databaseChangeLog:
- preConditions:
    - dbms:
        type: oracle
    - onFail: MARK_RAN

For Oracle and same for Postgres exported changelog maybe?

It would be hard to modify for all exceptions etc. between the two databases in Oracle exported changelog, that’s why I hope/believe separat exports from Oracle and Postgres would be best option.

2

If you use xml, yaml, or json format changelogs, with built-in change-types (not custom sql), then the same changesets can be applied to both database platforms.

Not all database functionality has a corresponding change-types (for example, no change-type for CREATE MATERIALIZED VIEW for oracle), so sometimes you might have to use custom sql.

3

Hi @daryldoak, thanks for your help. I’ve generated changelog from Oracle database with:

liquibase generate-changelog --changelog-file=legacy_oracle/generated-changelog.yml --url=jdbc:oracle:thin:@//[oracle_server.fqdn]:1521/BASELINE --username=XXXX --password=XXXX

When I then for example try to run:
liquibase status
I get the following error messages:

forIndexName is not allowed on postgresql, legacy_oracle/generated-changelog.yml

…

createSynonym is not supported on postgresql, legacy_oracle/generated-changelog.yml.

In this yml file, I see for example:

- changeSet:
    id: 1691589483668-905
    author: henrik (generated)
    changes:
    - addPrimaryKey:
        columnNames: KUNDE_ID, KONTRAKT, ABM_KUNDE, ABM_LOPENR, LOPENR
        constraintName: KONTRAKT_ABMP1
        forIndexName: KONTRAKT_ABMP1
        tableName: KONTRAKT_ABM

and

- changeSet:
    id: 1691589483668-1118
    author: henrik (generated)
    changes:
    - createSynonym:
        objectCatalogName: METERING
        objectName: LOCAL_2_INT
        objectSchemaName: METERING
        private: true
        synonymName: LOCAL_2_INT

Why is this?

4

Looking at the documentation…

forIndexName is only supported for db2, db2z, and oracle.

createSynonym is a Liquibase Pro feature. Do you have Pro license key?

5

Yes, I have a trial version of pro license. As mentioned I have also created an changelog exported from postgres database. This changlog file is quite different. I don’t know if it’s possible to have use two different changelog files for Oracle and Postgres, or if each changeset has to be modified so it works on both DB’s. If the last is the case - it would be a lot of work, since this is an application with >30 years of history and has multiple db updates each week (a moving target…).

I’m currently considering alternative 2 in Adding Liquibase to an Existing Project | Start Using Liquibase (“from this point forward, we are using Liquibase”). But unsure how much problem it will be if we later would like to make it possible to bootstrap an application. In case that would only be needed for Postgres, and we could maybe just make an dump of baseline db in posgress and then make our application just import this (for example if it finds that critical tables are missing) before liquibase is run.

(It’s a spring application btw)

6

Most of my customers are using option 2. They just start using Liquibase from their next change and going forward.

1 Like