Basic instructions for Liquibase


Liquibase is an open source tool for Database Reconstruction and migration. It records database changes in the form of log files, and then performs modifications in the log files to update or rollback the database to a consistent state. Its goal is to provide a database type independent solution to achieve migration by executing schema type files. Its advantages mainly include the following:

  1. Support almost all mainstream databases, such as MySQL, PostgreSQL, Oracle, Sql Server, DB2, etc;
  2. Support the collaborative maintenance of multiple developers;
  3. Log files support multiple formats, such as XML, YAML, JSON, SQL, etc;
  4. It supports multiple operation modes, such as command line, Spring integration, Maven plug-in, Gradle plug-in, etc.


  1. Download and unzip Liquibase:
  2. Install java (configure environment variables)
  3. Download the database driver package and put it in lib under the Liquibase directory


There are two types of usage: one is used in a completely new project, and the other is used in an old project (that is, the database already has a historical table structure and data, so it is necessary to reverse generate changeLog).

Two liquibase schemes are used for old projects:

1: You can sort out the current ddl in the project database (maybe not only ddl, but also some table data should be maintained in liquibase, depending on the specific project requirements), and maintain it in an initialized changeLog as an initialization script, In this way, the initialization script can be executed in the new environment (* * in this case, special attention should be paid to specifying context in the changeSet, and context must also be specified during execution to avoid executing the initialization script in the old environment * *).

2: liquibase has a special command generateChangeLog to reverse generate changeLog. (I haven't tried whether it's easy to use or not. I'll add it when I have tried...)


  • liquibase building directories in a project



  • src/main/db/sql/init-personal_bank_swift.sql

Define an sql file that can be directly referenced and executed by changeSet in another changeLog

INSERT INTO personal_bank_swift (bank_code, clearing_code, swift_code, create_by, create_at, updated_by, updated_at) VALUES 
('bank_code2300', '012', 'BKCHHKHHXXX', null, null, null, null),
('bank_code2302', '009', 'CCBQHKAXXXX', null, null, null, null),
('bank_code2304', '041', 'LCHBHKHHXXX', null, null, null, null),
('bank_code2305', '040', 'DSBAHKHHXXX', null, null, null, null),
('bank_code2306', '032', null, null, null, null, null);
  • src/main/db/2019/08/2600-init-tables.xml


  • Changelog:xml (* * refers to the concept of changelog in liquibase, which can reference other changelogs in multiple layers * *)
  • include: label referencing changelog
  • File: texture of the referenced changelog file
  • changeSet: a changeLog can contain multiple changeSet tags. Each changeSet is uniquely identified by the ID, author and filepath attributes. When Liquibase executes the database changeLog, it reads the changesets in sequence, and checks the databasechangelog table for each changeSet to see whether the combination of id/author/filepath has been run. If it is already running, the changeSet will be skipped unless there is a real runAlways tag. After running all the changes in changeSet, Liquibase inserts a new row with id/author/filepath and MD5Sum of changeSet into databasechangelog. The transactions of each changeSet are separate. The best practice is to ensure that each changeSet is atomically changed as much as possible to avoid the failure of the remaining unprocessed statements in the database in the unknown state.
  • author: author
  • id: id of changeSet (* * it is better to use the date format of 2019082600-01 to make it clearer. If a target database may be executed by multiple project liquibase s, this id will be written to the same databasechangelog table, which can be distinguished by adding the project name or the abbreviation of the project name before the date, such as reference-2019082600-01)
  • runAlways: perform changes to the settings at each run time, even if the changes have been run before
  • Context: it can be used to flexibly control the environment in which scripts are executed. Our system generally defines the environment (such as team2,uat,prod). If there are multiple environments, it can be used. or and segmentation is also supported! The negative form of prod (* * if context is defined, context must be specified during execution. If it is not specified, all sql will be retrieved and executed, and the context will become invalid)
  • Comment: description of changeset.
  • preConditions: preConditions that must be passed before changeSet will be executed. It can be used to perform data integrity check for unrecoverable content
  • rollback: describes how to RollBACK SQL statement or refactoring label for changeSet
  • createTable: create a label for the table
  • tableName: table name
  • column: label of table field
  • Name: field name
  • Type: field type
  • Remarks: field remarks
  • Constraints: field constraints
  • primaryKey: true means primary key
  • nullable: true,false
  • unique: true,false
  • createIndex: create index label
  • indexName: index name
  • tableName: the name of the table to create the index
  • column: label of the index field (multiple represents the union index)
  • Name: field name
  • addColumn: add field label
  • tableName: the table name of the added field
  • column: added field
  • Name: field name
  • Type: field type
  • sql: sql tag. The content can be sql statements directly
  • endDelimiter: the delimiter to apply to the end of the statement. The default value is;, Can be set to ''.
  • splitStatement: true,false
  • stripComments: set to true to delete any comments in the SQL before execution, otherwise false. If
  • If not set, the default value is false
  • Comment: comment
  • sqlFile: label referencing sql file
  • path: the address of the referenced sql file



<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns=""
   <!--The first tab creation method-->
  <changeSet author="future_zwp (generated)" id="reference-2019082600-00" context="team2,uat,prod">
        <createTable tableName="personal_bank_swift">
            <column name="id" type="serial">
                <constraints primaryKey="true"/>
            <column name="bank_code" type="text" remarks="Bank code"></column>
            <column name="clearing_code" type="text" ></column>
            <column name="swift_code" type="text" ></column>
            <column name="create_by" type="text" ></column>
            <column name="created_at" type="timestamp"></column>
            <column name="updated_by" type="text" ></column>
            <column name="updated_at" type="timestamp"></column>
            <column name="pt" type="text"></column>
            <dropTable tableName="personal_bank_swift"/>

    <changeSet author="future_zwp (generated)" id="reference-2019082600-01" context="team2,uat,prod">
        <createIndex indexName="idx_bank_info_bank_clearing" tableName="personal_bank_swift">
            <column name="bank_code"/>
            <column name="clearing_code"/>

    <changeSet author="future_zwp (generated)" id="reference-2019082600-02" context="team2,uat,prod">
        <createIndex indexName="idx_personal_bank_swift_swift_code" tableName="personal_bank_swift">
            <column name="swift_code"/>
   <!--Second sql Table creation method,be-all sql Statements are supported, with low learning cost and more flexibility-->
  <changeSet author="zhaowenpeng" id="reference-2019082600-03" context="team2,uat,prod">
        <sql splitStatements="true">
            drop table if exists personal_bank_swift;

            create table personal_bank_swift
              id serial primary key,
            bank_code     text,
            clearing_code text,
            swift_code    text,
            create_by     text,
            create_at     timestamp(6),
            updated_by    text,
            updated_at    timestamp(6)

            comment on column personal_bank_swift.bank_code
            is 'Bank code';

            create index idx_bank_info_bank_clearing on personal_bank_swift(bank_code,clearing_code);
            create index idx_personal_bank_swift_swift_code on personal_bank_swift(swift_code);
   <!--quote sql file-->
   <changeSet author="zhaowenpeng" id="reference-2019082600-04" context="team2,uat,prod">
        <sqlFile  path="sql/init-personal_bank_swift.sql"></sqlFile>
  • src/main/db/2019/driver.xml
<?xml version="1.0" encoding="UTF-8"?>

    <include file="2019/08/2600-init-tables.xml"/>

  • src/main/db/driver.xml
<?xml version="1.0" encoding="UTF-8"?>

    <include file="2019/driver.xml"/>

    <include file="2020/driver.xml"/>



Execute changeLog locally

  • src/main/db/ (this file is only executed locally. idea can install bashSupport, and right-click the file to execute it)

The contexts value corresponds to the context value of the changeSet (if it is not satisfied, the changeSet will not be executed), and the changeLogFile will get the read execution

The lines are the same as the driver xml


#!/usr/bin/env bash
liquibase   \
    --driver=org.postgresql.Driver       \
    --changeLogFile=driver.xml     \
    --url=jdbc:postgresql://   \
    --username=team2_app    \
    --password=8sVG98uziKfAEqzM     \
    --contexts=team2 \

Execution completed:




Jenkins perform steps

  • src/main/db/maycur/





function help_info() {
    echo "Please use the canonical setttings: "
    echo "./ -h <DB_HOST> -p <DB_PORT> -d <DB_NAME> -u <DB_USER> -w <PASSWORD> -t <DB_TYPE> \
          -o <OPERATOR>"
    echo "ATTENTION! DB_TYPE alternatives would be *mysql* or *postgresql*, nothing more"
    exit 1

while [[ $# -gt 0 ]]
    case "$1" in
            echo "DB_HOST: $1"
            echo "DB_PORT: $1"
            echo "DB_NAME: $1"
            echo "DB_USER: $1"
            #echo "DB_PASSWORD: $1"
            echo "DB_TYPE: $1"
            if [ "$DB_TYPE" = "mysql" ]; then
            elif [ "$DB_TYPE" = "postgresql" ]; then
            echo "DB_DRIVER: $DB_DRIVER"
            echo "CONTEXTS: $1"


/opt/liquibase/liquibase \
            --driver=$DB_DRIVER \
            --changeLogFile=driver.xml \
            --url="jdbc:$DB_TYPE://$DB_HOST:$DB_PORT/$DB_NAME?useSSL=false&useUnicode=yes" \
            --username=$DB_USER \
            --password=$DB_PASSWORD \
            --contexts=$CONTEXTS \

if [ $? -eq 0 ]; then
    echo "Congratulations! Things are all set, you are good to go!"
    echo "Oops! Something just went wrong. You're gonna have to take a look at it"
    exit 1
  • Create a project on jenkins and configure


cd src/main/db
/bin/bash -h -p 3433 -u team2_app -d team2_reference_data -w ${TEAM2_DB_PASSWORD} -t postgresql -c team2 -o update
  • Just save the execution (before execution, make sure whether the server has installed liquibase and whether the required database driver jar package is placed in the lib folder under the liquibase directory) Parameter Description:

cd src/main/db this is to enter update db SH existing directory

-h database connection host

-p database connection port

-u database connection user name

-d database name

-w database connection user password (the name configured in jenkins is referenced here)

-Tdatabase type

-c specifies the contexts. It needs to cooperate with the context in the changeSet to conditionally execute sql

-o                     update,clearCheckSums


After the first execution, there will be two more tables in the target database:


Liquibase uses the databasechangelog table to track the changesets that have been run.

The table tracks each change setting as a row, identified by a combination of the id of the path where the changelog file is stored, and the author and filename columns.

columnStandard data typedescribe
IDVARCHAR(255)id attribute value in changeSet
AUTHORVARCHAR(255)author property value in changeSet
FILENAMEVARCHAR(255)Path to changelog. This may be an absolute path or a relative path, depending on how changelog is passed to Liquibase. For best results, it should be a relative path
DATEEXECUTEDDATETIMEDate / time the changeSet was executed. Used with ORDEREXECUTED to determine rollback order
ORDEREXECUTEDINTThe order in which changeSet is executed. In addition to DATE EXECUTED, it is also used to ensure that the sequence is correct, even if the database date and time support poor precision. Note: the guaranteed value increases only in a single update run. Sometimes they restart at zero.
EXECTYPEVARCHAR(10)Description of how changeSet is executed. Possible values are EXECUTED, FAILED, SKIPPED, RERAN, and MARK_RAN
MD5SUMVARCHAR(35)Verification when changeSet is executed. Used for each run to ensure that the changeSet in the changelog file has not changed unexpectedly
DESCRIPTIONVARCHAR(255)Readable description generated by changeSet
COMMENTSVARCHAR(255)Value of the comment tag of changeSet
TAGVARCHAR(255)The trace of changeSet corresponds to label operation.
LIQUIBASEVARCHAR(20)Liquibase version used to execute changeSet

**The table has no primary key. This is to avoid any database specific restrictions on key length.


Liquibase uses the databasechangeloglock table to ensure that only one instance of liquibase is running at a time.

Because Liquibase only reads from the databasechangelog table to determine the changeSet that needs to be run, conflicts will occur if multiple Liquibase instances are executed on the same database at the same time. This can happen if multiple developers use the same database instance, or if multiple servers in the cluster automatically run Liquibase at startup.

columnStandard data typedescribe
IDINTID of the lock. There is only one lock at present, but it will be useful in the future
LOCKEDINTSet to '1' if Liquibase is running against this database. Otherwise set to "0"
LOCKGRANTEDDATETIMEDate and time the lock was acquired
LOCKEDBYVARCHAR(255)Description of who is locked

**If Liquibase does not exit cleanly, locked rows may remain locked. You can clear the current lock by running UPDATE DATABASECHANGELOGLOCK SET LOCKED=0



Common errors:

1: After modifying the historical script file, the following errors will be reported during execution

At this time, if you need to ignore the changes of this script, you can set src/main/db/update Change the update value in SH to clearCheckSums, and then execute the script again

2: If the execution cannot be completed, the database exception liquibaselock may be locked.

At this time, you can connect to the database executing the script, select * from databasechangeloglock;

If it is found that locked is locked, stop the script, change locked to 0, and then re execute the script.



ps: file package optimization

1. scenario 1:

If you put all the xml files in one package, the file structure will not be very beautiful after a long time. Driver The content in xml is also very long. Each time a script is added, it needs to be pulled to the bottom of the file. The experience is extremely bad. Therefore, it is very important to define a sustainable extended file structure from the beginning. The solution is as shown in the figure: add a new year directory in one year

  • Create db directory
  • Create a directory of the year under the db directory
  • Create month directory and year level driver under year directory xml
  • Create a script to execute under the month directory
  • Create a driver at the year level under the year directory xml (include xml files in all months under the directory of the year)
  • Create a db level driver in the db directory XML (include driver.xml files in all years under the db directory)
  • When executing, execute the driver in the db directory XML is OK


2. scenario 2:

We have two product lines maycur and ng. Both product lines use the same project. However, some sql scripts in the two product lines may be inconsistent due to differences in some product lines. At this time, it is necessary to distinguish the execution. The strategy of this scheme is to classify the executed files, put the files that can be executed by both product lines into the common folder, put the files that can only be executed by maycur into the bulletproof maycur folder, and put the files that can only be executed by ng into the ng folder. There are separate drivers under the three directories common, maycur, ng, and XML and update sh. In this way, the deployment can be differentiated:

  • maycur: execute update sh
  • ng: execute update sh



3. scenario 3:

Sometimes, because of the version problem of a specific environment database, some sql maintained in liquibase will fail to execute. At this time, the script maintained in liquibase will not be modified. Generally, the scheme of modifying sql will be directly executed in the database to achieve the goal. However, this scheme will also be accompanied by some problems (the changeset where the sql is located will not be executed successfully and cannot be executed downward). At this time, we should consider how to ignore this script.

  • Scheme I (not very recommended):

Manually insert an execution record into the databasechangelog table in the target database. Each changeSet is uniquely identified by the id, author and filepath attributes, Therefore, these three values must strictly correspond to the script to be ignored (you can also insert them from the databasechangelog record of the successfully executed database that copies the corresponding sql. Note that the mismatched values of contexts and liquibase versions need to be changed). Because the md5 values may be different, you should execute clearCheckSums and then re execute the script.


  • Scheme II (recommended):

A changeSet may have multiple sql scripts. In this scenario, we may change only one sql. At this time, we can change the corresponding sql into a meaningless sql (for example, select 1) to execute the liquibase. At this time, the databasechangelog table has the corresponding execution records. At this time, we can change the sql back to clearCheckSums.

4. scenario 4:

There are many tables with large data volume in our report service. Each time we use liquibase to perform DDL operations on these tables during publishing, because of the characteristics of polardb, it takes a very long time. After the liquibase is finished, we have to run a full number of initialization data warehouse tasks (this operation will clear the table data first), which leads to a further extension of the publishing time. In order to solve this problem, we summarized a scheme.

Release steps:

1. Update changeLogFile in SH specifies 2020/0515/driver_ 1. Execute update sh

2. Data warehouse initialization tasks related to data warehouse running

3 update The changeLogFile in SH specifies the driver Execute update sh

4. Deploy front and back end services

(the first and second steps can be executed in advance because they are both temporary tables.)



  • We create a package for each version, such as 0515.

Create driver under this package_ 1.xml,driver_2.xml two files

1,driver_1.xml include s the XML file to be executed in the first step, as shown in the following figure. When we want to_ fee_ dynamic_ When adding fields to the detail table, we will create a custom_fee_dynamic_detail_temp, and then in custom_fee_dynamic_detail_ Add fields to the temp table. The initialization task output of the data warehouse can be specified to be output to custom_fee_dynamic_detail_temp

2,driver_2.xml can include the XML file to be executed in the second step, as shown in the following figure. When the initialization task of the data warehouse is specified to be output to custom_fee_dynamic_detail_ After temp, we will drop the old data structure table custom_fee_dynamic_detail, and then we will_ fee_ dynamic_ detail_ Temp temp temp table rename back to custom_fee_dynamic_detail, and then the custom of the new data structure can be referenced elsewhere_ fee_ dynamic_ Detail table

  • Driver under 2020 package xml

  • Driver under maycur package xml








Tags: Database

Posted by nathanmaxsonadil on Fri, 03 Jun 2022 05:36:59 +0530