Search code examples
postgresqlliquibase

Liquibase keep applying old changes in changelog after restoring backup

I have Postgis 13 and liquibase 4.17.2 both in docker.

If I run Liquibase with a blank database it will run fine. It will create a database with all the changes in the changelog.xml.

But once I restore a backup and run the liquibase with new changes for some reason it will try and reapply changes in my changelog.xml.

<changeSet id="125" author="xxx">
   <validCheckSum>ANY</validCheckSum>
           <sqlFile
        encoding="utf8"
        endDelimiter=";"
        path="125/addfavorite.sql"
        relativeToChangelogFile="true"
        splitStatements="true"
        stripComments="false"/>
</changeSet>

In the backup of the database is the databasechangelog and the lock version of the table. With all the records of the changes in it. Picture below is the record of the change above.

Record in DB

[{"id":"125","author":"xxx","filename":"../db.bestekchecker.xml","dateexecuted":"2022-11-29T21:46:37.210688","orderexecuted":121,"exectype":"EXECUTED","md5sum":"8:b02e4337396cdf48e077606e7712f4e1","description":"sqlFile","comments":"","tag":null,"liquibase":"4.17.2","contexts":null,"labels":null,"deployment_id":"9754796893"}]

I run the liquibase via an bash script. With a loop in it, since I want to apply the same changes to multiple databases and schemas.

/liquibase/liquibase  --url $URL --classpath="/liquibase/postgres-jdbc/postgresql-42.2.5.jar"   --driver=org.postgresql.Driver --changelog-file=db.bestekchecker.xml --username=xxxx --password=xxxxx --defaultSchemaName=$schemaname --log-level=warning update -Dschemanaam=$schemaname

The log from liquibase gives an error since it can't execute the SQL.

[2022-12-08 11:14:12] SEVERE [liquibase.integration] Migration failed for changeset db.bestekchecker.xml::125::XXX:
        Reason: liquibase.exception.DatabaseException: ERROR: column "favorite" of relation "beeldkwaliteit_type_werkzaamheden" already exists [Failed SQL: (0) ALTER TABLE schemaname.beeldkwaliteit_type_werkzaamheden

You guys got any clue? Quite frustrating.

Solution

  • I found the problem thanks to a_horse_with_no_name.

    The migration from local server to docker it changed the filename in the database. For some reason, the local version had ./changelog.xml and the docker named it just changelog.xml.

    To combat this I added a new entry to my changelog to check if it has records with ./ and removes it. Problem solved.