moritetuのIT関連技術メモ

Liquibase

インストール

アーカイブ

パッケージを落とす。

$ wget https://github.com/liquibase/liquibase/releases/download/v3.9.0/liquibase-3.9.0.tar.gz
$ mkdir liquibase 
$ tar xzf liquibase-3.9.0.tar.gz -C liquibase
$ cd liquibase
$ ./liquibase --version
Starting Liquibase at 金, 12 62020 20:42:24 JST (version 3.9.0 #11 built at Thu May 14 04:03:56 UTC 2020)
Liquibase Version: 3.9.0
Liquibase Community 3.9.0 by Datical
Running Java under /Library/Java/JavaVirtualMachines/adoptopenjdk-11.jdk/Contents/Home (Version 11.0.7)

URL

HomeBrew

macユーザであれば、homebrewでも可能だ。

brew install liquibase

URL https://formulae.brew.sh/formula/liquibase

Gradle

springbootのようなアプリケーションの中で使用する場合は、以下のような依存関係を追加する。

dependencies {
    implementation 'org.liquibase:liquibase-core:3.9.0'
}

changelogの作成

定義は以下のような構造である。
これはアーカイブのexample/xmlにあるサンプルchangelogである。

databaseChangeLog
  changeSet
    ...

liquibase/examples/xml

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet id="1" author="your.name">
        <createTable tableName="person">
            <column name="id" type="int">
                <constraints primaryKey="true"/>
            </column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="address1" type="varchar(50)"/>
            <column name="address2" type="varchar(50)"/>
            <column name="city" type="varchar(30)"/>
        </createTable>
    </changeSet>

    <changeSet id="2" author="your.name">
        <createTable tableName="company">
            <column name="id" type="int">
                <constraints primaryKey="true"/>
            </column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="address1" type="varchar(50)"/>
            <column name="address2" type="varchar(50)"/>
            <column name="city" type="varchar(30)"/>
        </createTable>
    </changeSet>

    <changeSet id="3" author="other.dev">
        <addColumn tableName="person">
            <column name="country" type="varchar(2)"/>
        </addColumn>
    </changeSet>
</databaseChangeLog>

変更の適用

postgresqlで試す。 jdbcをダウンロードしておく。

cd liquibase/examples/xml
wget https://repo1.maven.org/maven2/org/postgresql/postgresql/42.2.14/postgresql-42.2.14.jar

データベースを作成しておく。
maindbは、変更適用するデータベース。
refdbは、比較対象のデータベース。

createdb maindb
createdb refdb

liquibase.propertiesの定義を以下のようにする。

url=jdbc:postgresql://localhost:5432/maindb
username: dbuser
password:

変更を適用する前に実行されるSQLを確認する。

$ liquibase --classpath postgresql-42.2.14.jar updateSQL
Liquibase Community 3.9.0 BY Datical
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: sample.changelog.xml
-- Ran at: 2020/06/12 21:19
-- Against: t-moriyasu@jdbc:postgresql://localhost:5432/maindb
-- Liquibase version: 3.9.0
-- *********************************************************************

-- Create Database Lock Table
CREATE TABLE public.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT DATABASECHANGELOGLOCK_PKEY PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM public.databasechangeloglock;

INSERT INTO public.databasechangeloglock (ID, LOCKED) VALUES (1, FALSE);

-- Lock Database
UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = '192.168.33.1 (192.168.33.1)', LOCKGRANTED = '2020-06-12 21:19:50.117' WHERE ID = 1 AND LOCKED = FALSE;

-- Create Database Change Log Table
CREATE TABLE public.databasechangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP WITHOUT TIME ZONE NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10));

-- Changeset sample.changelog.xml::1::your.name
CREATE TABLE public.person (id INTEGER NOT NULL, name VARCHAR(50) NOT NULL, address1 VARCHAR(50), address2 VARCHAR(50), city VARCHAR(30), CONSTRAINT PERSON_PKEY PRIMARY KEY (id));

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'your.name', 'sample.changelog.xml', NOW(), 1, '8:53e2f0de46cd10d57096d0cb20a26a17', 'createTable tableName=person', '', 'EXECUTED', NULL, NULL, '3.9.0', '1964391568');

-- Changeset sample.changelog.xml::2::your.name
CREATE TABLE public.company (id INTEGER NOT NULL, name VARCHAR(50) NOT NULL, address1 VARCHAR(50), address2 VARCHAR(50), city VARCHAR(30), CONSTRAINT COMPANY_PKEY PRIMARY KEY (id));

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('2', 'your.name', 'sample.changelog.xml', NOW(), 2, '8:5ac2cabd76a2a2b455e9c31ad8693e7b', 'createTable tableName=company', '', 'EXECUTED', NULL, NULL, '3.9.0', '1964391568');

-- Changeset sample.changelog.xml::3::other.dev
ALTER TABLE public.person ADD country VARCHAR(2);

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('3', 'other.dev', 'sample.changelog.xml', NOW(), 3, '8:3155ae8c6923f7bc8c39581748116285', 'addColumn tableName=person', '', 'EXECUTED', NULL, NULL, '3.9.0', '1964391568');

-- Release Database Lock
UPDATE public.databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
 

問題ないようであれば変更を適用する。
本番適用する前に別の参照dbでチェックする。

$ liquibase --classpath postgresql-42.2.14.jar --url jdbc:postgresql://localhost:5432/refdb update
Liquibase Community 3.9.0 by Datical
Liquibase: Update has been successful.
$ psql -d refdb
psql (12.1)
Type "help" for help.

refdb=# \d
                  List of relations
 Schema |         Name          | Type  |   Owner    
--------+-----------------------+-------+------------
 public | company               | table | t-moriyasu
 public | databasechangelog     | table | t-moriyasu
 public | databasechangeloglock | table | t-moriyasu
 public | person                | table | t-moriyasu
(4 rows)

refdb=# \d company;
                      Table "public.company"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 id       | integer               |           | not null | 
 name     | character varying(50) |           | not null | 
 address1 | character varying(50) |           |          | 
 address2 | character varying(50) |           |          | 
 city     | character varying(30) |           |          | 
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

refdb=# \d person
                       Table "public.person"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 id       | integer               |           | not null | 
 name     | character varying(50) |           | not null | 
 address1 | character varying(50) |           |          | 
 address2 | character varying(50) |           |          | 
 city     | character varying(30) |           |          | 
 country  | character varying(2)  |           |          | 
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)

refdb=# table databasechangelog
refdb-# ;
 id |  author   |       filename       |        dateexecuted        | orderexecuted | exectype |               md5sum               |          description          | comments | tag | liquibase | contexts | labels | deployment_id 
----+-----------+----------------------+----------------------------+---------------+----------+------------------------------------+-------------------------------+----------+-----+-----------+----------+--------+---------------
 1  | your.name | sample.changelog.xml | 2020-06-12 21:22:37.36131  |             1 | EXECUTED | 8:53e2f0de46cd10d57096d0cb20a26a17 | createTable tableName=person  |          |     | 3.9.0     |          |        | 1964557331
 2  | your.name | sample.changelog.xml | 2020-06-12 21:22:37.378014 |             2 | EXECUTED | 8:5ac2cabd76a2a2b455e9c31ad8693e7b | createTable tableName=company |          |     | 3.9.0     |          |        | 1964557331
 3  | other.dev | sample.changelog.xml | 2020-06-12 21:22:37.386811 |             3 | EXECUTED | 8:3155ae8c6923f7bc8c39581748116285 | addColumn tableName=person    |          |     | 3.9.0     |          |        | 1964557331
(3 rows)

refdb=# table databasechangeloglock;
 id | locked | lockgranted | lockedby 
----+--------+-------------+----------
  1 | f      |             | 
(1 row)

問題ないようであれば、実際に適用する。

$ liquibase --classpath postgresql-42.2.14.jar --url jdbc:postgresql://localhost:5432/maindb update
Liquibase Community 3.9.0 by Datical
Liquibase: Update has been successful.

差分の確認

先ほどの変更差分を見てみる。
maindbとrefdbは同じスキーマなので違いはない。

$ liquibase --classpath postgresql-42.2.14.jar --url jdbc:postgresql://localhost:5432/maindb --referenceUrl jdbc:postgresql://localhost:5432/refdb diff
Liquibase Community 3.9.0 by Datical

Diff Results:
Reference Database: t-moriyasu @ jdbc:postgresql://localhost:5432/refdb (Default Schema: public)
Comparison Database: t-moriyasu @ jdbc:postgresql://localhost:5432/maindb (Default Schema: public)
Compared Schemas: public
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): 
     refdb
          name changed from 'refdb' to 'maindb'
Missing Column(s): NONE
Unexpected Column(s): NONE
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): NONE
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s): NONE
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE
Liquibase command 'diff' was executed successfully.

新しいchangelogを以下のように作成する。

$ cat sample.changelog-2.xml 
<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet id="4" author="other.dev">
        <addColumn tableName="person">
            <column name="age" type="integer"/>
        </addColumn>
    </changeSet>
</databaseChangeLog>

refdbに変更を適用する。
まずは、SQLを確認する。

$ liquibase --classpath postgresql-42.2.14.jar --changeLogFile=sample.changelog-2.xml --referenceUrl jdbc:postgresql://localhost:5432/refdb updateSQL
Liquibase Community 3.9.0 by Datical
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: sample.changelog-2.xml
-- Ran at: 2020/06/12 21:35
-- Against: t-moriyasu@jdbc:postgresql://localhost:5432/maindb
-- Liquibase version: 3.9.0
-- *********************************************************************

-- Lock Database
UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = '192.168.33.1 (192.168.33.1)', LOCKGRANTED = '2020-06-12 21:35:56.174' WHERE ID = 1 AND LOCKED = FALSE;

-- Changeset sample.changelog-2.xml::4::other.dev
ALTER TABLE public.person ADD age INTEGER;

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('4', 'other.dev', 'sample.changelog-2.xml', NOW(), 4, '8:8d12fa5b77eea7b016da04713c076655', 'addColumn tableName=person', '', 'EXECUTED', NULL, NULL, '3.9.0', '1965358616');

-- Release Database Lock
UPDATE public.databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

[2020-06-12 21:35:58] t-moriyasu@ToruMoriyasu-no-iMac ~/workspace/liquibase/3.9.0/liquibase/examples/xml 
$ liquibase --classpath postgresql-42.2.14.jar --changeLogFile=sample.changelog-2.xml --url jdbc:postgresql://localhost:5432/refdb updateSQL
Liquibase Community 3.9.0 by Datical
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: sample.changelog-2.xml
-- Ran at: 2020/06/12 21:36
-- Against: t-moriyasu@jdbc:postgresql://localhost:5432/refdb
-- Liquibase version: 3.9.0
-- *********************************************************************

-- Lock Database
UPDATE public.databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = '192.168.33.1 (192.168.33.1)', LOCKGRANTED = '2020-06-12 21:36:06.569' WHERE ID = 1 AND LOCKED = FALSE;

-- Changeset sample.changelog-2.xml::4::other.dev
ALTER TABLE public.person ADD age INTEGER;

INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('4', 'other.dev', 'sample.changelog-2.xml', NOW(), 4, '8:8d12fa5b77eea7b016da04713c076655', 'addColumn tableName=person', '', 'EXECUTED', NULL, NULL, '3.9.0', '1965369469');

-- Release Database Lock
UPDATE public.databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

続いて変更を適用する。

$ liquibase --classpath postgresql-42.2.14.jar --changeLogFile=sample.changelog-2.xml --url jdbc:postgresql://localhost:5432/refdb update
Liquibase Community 3.9.0 by Datical
Liquibase: Update has been successful.

この状態でmaindbとrefdbは差分があるはずだ。

$ liquibase --classpath postgresql-42.2.14.jar --url jdbc:postgresql://localhost:5432/maindb --referenceUrl jdbc:postgresql://localhost:5432/refdb diff
Liquibase Community 3.9.0 by Datical

Diff Results:
Reference Database: t-moriyasu @ jdbc:postgresql://localhost:5432/refdb (Default Schema: public)
Comparison Database: t-moriyasu @ jdbc:postgresql://localhost:5432/maindb (Default Schema: public)
Compared Schemas: public
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): 
     refdb
          name changed from 'refdb' to 'maindb'
Missing Column(s): 
     public.person.age
Unexpected Column(s): NONE
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): NONE
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s): NONE
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE
Liquibase command 'diff' was executed successfully.

maindbに適用しよう。

$ liquibase --classpath postgresql-42.2.14.jar --changeLogFile=sample.changelog-2.xml --url jdbc:postgresql://localhost:5432/maindb update
Liquibase Community 3.9.0 by Datical
Liquibase: Update has been successful.

再度差分を見てみよう。

$ liquibase --classpath postgresql-42.2.14.jar --url jdbc:postgresql://localhost:5432/maindb --referenceUrl jdbc:postgresql://localhost:5432/refdb diff
Liquibase Community 3.9.0 by Datical

Diff Results:
Reference Database: t-moriyasu @ jdbc:postgresql://localhost:5432/refdb (Default Schema: public)
Comparison Database: t-moriyasu @ jdbc:postgresql://localhost:5432/maindb (Default Schema: public)
Compared Schemas: public
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): 
     refdb
          name changed from 'refdb' to 'maindb'
Missing Column(s): NONE
Unexpected Column(s): NONE
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): NONE
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s): NONE
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Table(s): NONE
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE
Liquibase command 'diff' was executed successfully.

差分のあったカラム追加は表示されなくなっていることがわかる。
念のためdbも確認する。

$ echo "\d person" | psql -d maindb 
                       Table "public.person"
  Column  |         Type          | Collation | Nullable | Default 
----------+-----------------------+-----------+----------+---------
 id       | integer               |           | not null | 
 name     | character varying(50) |           | not null | 
 address1 | character varying(50) |           |          | 
 address2 | character varying(50) |           |          | 
 city     | character varying(30) |           |          | 
 country  | character varying(2)  |           |          | 
 age      | integer               |           |          | 
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)

changelogの形式

https://docs.liquibase.com/concepts/basic/changelog.html

参考リンク


トップ   差分 バックアップ リロード   一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
目次
ダブルクリックで閉じるTOP | 閉じる
GO TO TOP