728x90
반응형
DB 형상 관리 - Liquibase Rollback
https://docs.liquibase.com/tools-integrations/maven/commands/maven-rollback.html
Maven rollback
Maven rollback rollback is a Maven goal that allows you to roll back change you made to the database. It also marks changesets that were deployed as unapplied. You can roll back changesets using the liquibase.rollbackCount, liquibase.rollbackTag, or liquib
docs.liquibase.com
Liquibase Rollback
- Rollback 옵션을 사용하기 위해서는 maven-plugin 설정이 정상적으로 되어야 사용이 가능하다.
pom.xml
<plugin>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-maven-plugin</artifactId>
<version>4.28.0</version>
<configuration>
<propertyFile>liquibase.properties</propertyFile>
</configuration>
</plugin> liquibase.properties
changeLogFile=db/changelog/changelog-master.sql
url=jdbc:postgresql://localhost:5432/postgres
username=${your-user-name}
password=${your-password}
driver=org.postgresql.DriverLiquibase Rollback 명령어.
- 특정 변경 세트까지 롤백하는 방법:
- 특정 태그기준 롤백
- databasechangelog.tag column 기준
mvn liquibase:rollback -Dliquibase.rollbackTag=1.0- 특정 날짜 기준 롤백
-- databasechnage.log.dateexecuted column 기준
mvn liquibase:rollback -Dliquibase.rollbackToDate=2023-06-12|
기인하다
|
정의
|
|
liquibase.rollbackCount
|
롤백할 변경 세트 수를 정의 . 기본값은 1
|
|
liquibase.rollbackDate
|
데이터베이스를 롤백하려는 날짜를 정의. 날짜 형식은 ISO 날짜 형식이나 DateFormat.getDateInstance()플러그인이 실행되는 플랫폼의 날짜 형식과 일치해야 함.
|
|
liquibase.rollbackTag
|
데이터베이스를 롤백하려는 태그를 정의.
|
테스트
changelog-master.sql
--liquibase formatted sql
--changeset mgkang1:1
CREATE TABLE test_sql_table (test_id INT, test_column VARCHAR, PRIMARY KEY (test_id))
--changeset mgakng1:2
ALTER TABLE test_sql_table ADD COLUMN new_column VARCHAR(255);
--changeset mgkang1:3
ALTER TABLE test_sql_table ADD COLUMN new_column_2 VARCHAR(255);
- 새로운 changeset 3번 추가
mvn liquibase:update
실패 - 1
mvn liquibase:rollback -Dliquibase.rollbackCount=1
- Liquibase가 Raw SQL 변경 사항에 대해 자동 롤백 스크립트를 생성 하지 않는다.
- Liquibase는 일반적으로 XML 또는 YAML 포맷의 changelog 파일을 사용하여 데이터베이스 변경을 관리한다.
실패 - 2
mvn liquibase:rollbackSQL -Dliquibase.rollbackCount=1Liquibase does not support automatic rollback generation for raw sql changes- [ 실패 - 1 ] 과 동일한 내용으로 Raw SQL 구문은 자동 롤백이 되질 않음.
실패 - 3
<?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"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.0.xsd">
<changeSet id="1" author="mgkang1">
<createTable tableName="test_sql_table">
<column name="test_id" type="INT">
<constraints primaryKey="true" primaryKeyName="pk_test_sql_table"/>
</column>
<column name="test_column" type="VARCHAR"/>
</createTable>
</changeSet>
<changeSet id="2" author="mgakng1">
<addColumn tableName="test_sql_table">
<column name="new_column" type="VARCHAR(255)"/>
</addColumn>
</changeSet>
<changeSet id="3" author="mgkang1">
<addColumn tableName="test_sql_table">
<column name="new_column_2" type="VARCHAR(255)"/>
</addColumn>
</changeSet>
</databaseChangeLog>- changelog-master.xml 파일로 변경 후 진행
[INFO] ------------------------------------------------------------------------
[INFO] ####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## ##
####################################################
Starting Liquibase at 11:29:28 (version 4.28.0 #2272 built at 2024-05-16 19:00+0000)
[INFO] Set default schema name to public
[INFO] Parsing Liquibase Properties File liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:postgresql://localhost:5432/test
[INFO] Reading from databasechangelog
[INFO] Successfully acquired change log lock
[INFO] Reading from databasechangelog
[INFO] INFO: 0 changesets rolled back.
[INFO] Rollback command completed successfully.
[INFO] Successfully released change log lock
[INFO] Command execution complete
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 3.584 s
[INFO] Finished at: 2024-06-13T11:29:29+09:00
[INFO] ------------------------------------------------------------------------
- 성공헀다고 나왔지만 실제 DB에는 아무런 변경이 존재하지 않음.
- INFO: 0 changesets rolled back. 해당 로그를 봐도 아무런 변경이 진행되지 않음을 알 수 있음.
실패 - 3 이유
- 기존에 changelog-master.sql 을 통해서 작업을 진행했음.
- 그러나, SQL 문은 Rollback 기능을 제공하지 않기 때문에 changelog-master.xml 로 변경함.
- 변경 하는 과정에 clearChangeSums 명령어를 통해 change sum 을 초기화 하고, udpate 를 통해 반영하였지만, changeSetId=1 부터 시작됨.
- 따라서, 테이블을 DROP 하고 다시 테스트 진행
성공 - 1 : ADD COLUMN RollBack
chnagelog-master.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"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.0.xsd">
<changeSet id="1" author="mgkang1">
<createTable tableName="test_sql_table">
<column name="test_id" type="INT">
<constraints primaryKey="true" primaryKeyName="pk_test_sql_table"/>
</column>
<column name="test_column" type="VARCHAR"/>
</createTable>
</changeSet>
<changeSet id="2" author="mgakng1">
<addColumn tableName="test_sql_table">
<column name="new_column" type="VARCHAR(255)"/>
</addColumn>
</changeSet>
<changeSet id="3" author="mgkang1">
<addColumn tableName="test_sql_table">
<column name="new_column_2" type="VARCHAR(255)"/>
</addColumn>
</changeSet>
<changeSet id="4" author="mgkang1">
<addColumn tableName="test_sql_table">
<column name="rollback_column_1" type="VARCHAR(255)"/>
</addColumn>
</changeSet>
<changeSet id="5" author="mgkang1">
<addColumn tableName="test_sql_table">
<column name="rollback_column_2" type="VARCHAR(255)"/>
</addColumn>
</changeSet>
<changeSet id="6" author="mgkang1">
<addColumn tableName="test_sql_table">
<column name="rollback_column_3" type="VARCHAR(255)"/>
</addColumn>
</changeSet>
<changeSet id="7" author="mgkang1">
<addColumn tableName="test_sql_table">
<column name="rollback_column_4" type="VARCHAR(255)"/>
</addColumn>
</changeSet>
<changeSet id="8" author="mgkang1">
<addColumn tableName="test_sql_table">
<column name="rollback_column_5" type="VARCHAR(255)"/>
</addColumn>
</changeSet>
</databaseChangeLog>- 기존 테스트 하던 것에서 rollback_column_5 까지 컬럼을 새롭게 추가.
- [ 실패 - 3 ] 이유를 해결하기 위해 기존 테이블을 DROP 하고 처음부터 해당 xml 파일로 liquibase 관리 진행
mvn liquibase:rollback -Dliquibase.rollbackCount=1[INFO] ####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## ##
####################################################
Starting Liquibase at 13:34:21 (version 4.28.0 #2272 built at 2024-05-16 19:00+0000)
[INFO] Set default schema name to public
[INFO] Parsing Liquibase Properties File liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:postgresql://localhost:5432/test
[INFO] Reading from databasechangelog
[INFO] Successfully acquired change log lock
[INFO] Reading from databasechangelog
[INFO] Rolling Back Changeset: db/changelog/changelog-master.xml::8::mgkang1
[INFO] Rollback command completed successfully.
[INFO] Successfully released change log lock
[INFO] Command execution complete
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 3.485 s
[INFO] Finished at: 2024-06-13T13:34:23+09:00
[INFO] ------------------------------------------------------------------------[INFO] Rolling Back Changeset: db/changelog/changelog-master.xml::8::mgkang1rollbackCount=1 명령어를 통해 최근 하나의 ChangeSet 만 RollBack
- rollback_column_5 컬럼 제거.
mvn liquibase:rollback -Dliquibase.rollbackCount=4[INFO] ####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## ##
####################################################
Starting Liquibase at 13:41:29 (version 4.28.0 #2272 built at 2024-05-16 19:00+0000)
[INFO] Set default schema name to public
[INFO] Parsing Liquibase Properties File liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:postgresql://localhost:5432/test
[INFO] Reading from databasechangelog
[INFO] Successfully acquired change log lock
[INFO] Reading from databasechangelog
[INFO] Rolling Back Changeset: db/changelog/changelog-master.xml::7::mgkang1
[INFO] Rolling Back Changeset: db/changelog/changelog-master.xml::6::mgkang1
[INFO] Rolling Back Changeset: db/changelog/changelog-master.xml::5::mgkang1
[INFO] Rolling Back Changeset: db/changelog/changelog-master.xml::4::mgkang1
[INFO] Rollback command completed successfully.
[INFO] Successfully released change log lock
[INFO] Command execution complete
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 3.613 s
[INFO] Finished at: 2024-06-13T13:41:30+09:00
[INFO] ------------------------------------------------------------------------
- 모든 rollback_column_${number} 컬럼 롤백 완료.
성공 - 2 : Rollback 된 ChangeSet 다시 적용
mvn liquibase:updateSQLSET SEARCH_PATH TO public, "$user","public";
-- Lock Database
UPDATE databasechangeloglock SET LOCKED = TRUE, LOCKEDBY = 'KangMyoungGwanui-MacBookPro.local (10.106.8.69)', LOCKGRANTED = NOW() WHERE ID = 1 AND LOCKED = FALSE;
SET SEARCH_PATH TO public, "$user","public";
SET SEARCH_PATH TO public, "$user","public";
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: db/changelog/changelog-master.xml
-- Ran at: 6/13/24 1:43 PM
-- Against: gwanii@jdbc:postgresql://localhost:5432/test
-- Liquibase version: 4.28.0
-- *********************************************************************
SET SEARCH_PATH TO public, "$user","public";
-- Changeset db/changelog/changelog-master.xml::4::mgkang1
SET SEARCH_PATH TO public, "$user","public";
ALTER TABLE test_sql_table ADD rollback_column_1 VARCHAR(255);
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('4', 'mgkang1', 'db/changelog/changelog-master.xml', NOW(), 9, '9:1c32e6beaa9600bb6f90a56d681c5cba', 'addColumn tableName=test_sql_table', '', 'EXECUTED', NULL, NULL, '4.28.0', '8253834087');
-- Changeset db/changelog/changelog-master.xml::5::mgkang1
SET SEARCH_PATH TO public, "$user","public";
ALTER TABLE test_sql_table ADD rollback_column_2 VARCHAR(255);
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('5', 'mgkang1', 'db/changelog/changelog-master.xml', NOW(), 10, '9:58f63282d713a6ebc585205808d9078e', 'addColumn tableName=test_sql_table', '', 'EXECUTED', NULL, NULL, '4.28.0', '8253834087');
-- Changeset db/changelog/changelog-master.xml::6::mgkang1
SET SEARCH_PATH TO public, "$user","public";
ALTER TABLE test_sql_table ADD rollback_column_3 VARCHAR(255);
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('6', 'mgkang1', 'db/changelog/changelog-master.xml', NOW(), 11, '9:dffa5c51d4962f017481e8772b9931cc', 'addColumn tableName=test_sql_table', '', 'EXECUTED', NULL, NULL, '4.28.0', '8253834087');
-- Changeset db/changelog/changelog-master.xml::7::mgkang1
SET SEARCH_PATH TO public, "$user","public";
ALTER TABLE test_sql_table ADD rollback_column_4 VARCHAR(255);
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('7', 'mgkang1', 'db/changelog/changelog-master.xml', NOW(), 12, '9:33a07a8b5181865ce34976d2106c24b8', 'addColumn tableName=test_sql_table', '', 'EXECUTED', NULL, NULL, '4.28.0', '8253834087');
-- Changeset db/changelog/changelog-master.xml::8::mgkang1
SET SEARCH_PATH TO public, "$user","public";
ALTER TABLE test_sql_table ADD rollback_column_5 VARCHAR(255);
INSERT INTO databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('8', 'mgkang1', 'db/changelog/changelog-master.xml', NOW(), 13, '9:a7e66cef566d100b0e997e5bbc73eebb', 'addColumn tableName=test_sql_table', '', 'EXECUTED', NULL, NULL, '4.28.0', '8253834087');
-- Release Database Lock
SET SEARCH_PATH TO public, "$user","public";
UPDATE databasechangeloglock SET LOCKED = FALSE, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
SET SEARCH_PATH TO public, "$user","public";
mvn liquibase:update[INFO] ####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## ##
####################################################
Starting Liquibase at 13:45:17 (version 4.28.0 #2272 built at 2024-05-16 19:00+0000)
[INFO] Set default schema name to public
[INFO] Parsing Liquibase Properties File liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:postgresql://localhost:5432/test
[INFO] Reading from databasechangelog
[INFO] Successfully acquired change log lock
[INFO] Using deploymentId: 8253918764
[INFO] Reading from databasechangelog
[INFO] Running Changeset: db/changelog/changelog-master.xml::4::mgkang1
[INFO] Columns rollback_column_1(VARCHAR(255)) added to test_sql_table
[INFO] ChangeSet db/changelog/changelog-master.xml::4::mgkang1 ran successfully in 7ms
[INFO] Running Changeset: db/changelog/changelog-master.xml::5::mgkang1
[INFO] Columns rollback_column_2(VARCHAR(255)) added to test_sql_table
[INFO] ChangeSet db/changelog/changelog-master.xml::5::mgkang1 ran successfully in 6ms
[INFO] Running Changeset: db/changelog/changelog-master.xml::6::mgkang1
[INFO] Columns rollback_column_3(VARCHAR(255)) added to test_sql_table
[INFO] ChangeSet db/changelog/changelog-master.xml::6::mgkang1 ran successfully in 3ms
[INFO] Running Changeset: db/changelog/changelog-master.xml::7::mgkang1
[INFO] Columns rollback_column_4(VARCHAR(255)) added to test_sql_table
[INFO] ChangeSet db/changelog/changelog-master.xml::7::mgkang1 ran successfully in 2ms
[INFO] Running Changeset: db/changelog/changelog-master.xml::8::mgkang1
[INFO] Columns rollback_column_5(VARCHAR(255)) added to test_sql_table
[INFO] ChangeSet db/changelog/changelog-master.xml::8::mgkang1 ran successfully in 2ms
UPDATE SUMMARY
Run: 5
Previously run: 3
Filtered out: 0
-------------------------------
Total change sets: 8
[INFO] UPDATE SUMMARY
[INFO] Run: 5
[INFO] Previously run: 3
[INFO] Filtered out: 0
[INFO] -------------------------------
[INFO] Total change sets: 8
[INFO] Update summary generated
[INFO] Update command completed successfully.
[INFO] Liquibase: Update has been successful. Rows affected: 5
[INFO] Successfully released change log lock
[INFO] Command execution complete
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 3.959 s
[INFO] Finished at: 2024-06-13T13:45:18+09:00
[INFO] ------------------------------------------------------------------------
성공 - 3 : CREATE TABLE RollBack
<!-- 생략 -->
<changeSet id="9" author="mgkang1">
<createTable tableName="test_rollback_table">
<column name="test_id" type="INT">
<constraints primaryKey="true" primaryKeyName="pk_test_rollback_table"/>
</column>
<column name="test_column" type="VARCHAR"/>
</createTable>
</changeSet>
<!-- 생략 -->mvn liquibase:update
[INFO] ####################################################
## _ _ _ _ ##
## | | (_) (_) | ##
## | | _ __ _ _ _ _| |__ __ _ ___ ___ ##
## | | | |/ _` | | | | | '_ \ / _` / __|/ _ \ ##
## | |___| | (_| | |_| | | |_) | (_| \__ \ __/ ##
## \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___| ##
## | | ##
## |_| ##
## ##
## Get documentation at docs.liquibase.com ##
## Get certified courses at learn.liquibase.com ##
## ##
####################################################
Starting Liquibase at 13:51:41 (version 4.28.0 #2272 built at 2024-05-16 19:00+0000)
[INFO] Set default schema name to public
[INFO] Parsing Liquibase Properties File liquibase.properties for changeLog parameters
[INFO] Executing on Database: jdbc:postgresql://localhost:5432/test
[INFO] Reading from databasechangelog
[INFO] Successfully acquired change log lock
[INFO] Reading from databasechangelog
[INFO] Rolling Back Changeset: db/changelog/changelog-master.xml::9::mgkang1
[INFO] Rollback command completed successfully.
[INFO] Successfully released change log lock
[INFO] Command execution complete
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 3.630 s
[INFO] Finished at: 2024-06-13T13:51:42+09:00
[INFO] ------------------------------------------------------------------------
- 삭제 완료.
728x90
반응형
'1.프로그래밍 > DB' 카테고리의 다른 글
| [DB] DB 형상관리 - Liquibase Tip (4) | 2024.09.04 |
|---|---|
| [DB] DB 형상 관리 - Liquibase Best Practices (5) | 2024.09.01 |
| [DB] DB 형상관리 - Liquibase (0) | 2024.08.31 |
| [DB] DB 형상 관리 - FlyWay (0) | 2024.08.30 |
| [DataSource] DataSource 설정 정리 (0) | 2023.03.23 |
