문서유형ㅣ기술정보
분야ㅣ마이그레이션
적용제품버전ㅣTibero6, Tibero7
문서번호ㅣTMITI037
개요
Table Migrator를 활용하여 PostgreSQL 데이터를 Tibero로 이관하는 절차를 기술합니다.
구성 환경
- PostgreSQL : PostgreSQL 15.15
- Tibero : Tibero7.2.4
방법
1. 이관 대상 확인
PostgreSQL은 스키마(Schema)와 소유주(Owner)가 별개의 개념으로 관리됩니다.
Tibero로 이관할 경우 소유주가 아닌 스키마 기준으로 객체를 매핑해야 하며, 소유주 기준으로 매핑할 경우 객체 조회 오류가 발생할 수 있습니다.
[root@Gdb1 ~]# su - postgres
[postgres@Gdb1 ~]$ psql -U nam -d testdb
psql (15.15)
Type "help" for help.
1) 버전
testdb=# select * from version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 15.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-28), 64-bit
(1 row)
2) DB별 용량
testdb=# SELECT datname AS db_name, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;
db_name | size
-----------+---------
template1 | 7533 kB
template0 | 7297 kB
testdb | 7725 kB
postgres | 7453 kB
(4 rows)
3) 스키마
testdb=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
test | 16392 | f | t | f | f | ******** | |
nam | 16424 | f | t | f | f | ******** | |
(3 rows)
4) 객체 정보
testdb=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------------+----------+----------
public | departments | table | postgres
public | departments_dept_id_seq | sequence | postgres
public | employee_logs | table | postgres
public | employee_logs_log_id_seq | sequence | postgres
public | employees | table | postgres
public | employees_emp_id_seq | sequence | postgres
public | mig_test | table | postgres
5) 테이블 정보
testdb=# \d public.mig_test
Table "public.mig_test"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
a | integer | | |
b | character varying(10) | | |
2. DDL 이관
데이터 이관 전 PostgreSQL과 동일한 구조(DDL)를 Tibero에 생성합니다.
1. PostgreSQL
1) 스크립트 덤프
# pg_dump -U postgres -h localhost -p 5432 -s -t mig_test testdb > mig_test.sql
2) 스크립트 확인
# cat mig_test.sql
....
CREATE TABLE public.mig_test (
a integer,
b character varying(10)
);
...
2. Tibero
1) tibero에서 테이블 생성
SQL> create table mig_test (a int, b varchar(10));
Table 'MIG_TEST' created.
참고
PostgreSQL 테이블 단위 덤프
pg_dump -U [유저명] -h [호스트] -p [포트] -s -t [스키마명].[테이블명] [DB명] > [결과 파일]PostgreSQL 스키마 단위 덤프
pg_dump -U [유저명] -h [호스트] -p [포트] -s -n [스키마명] [DB명] > [결과 파일]※ DDL 변환(테이블/PK/FK/Index/Sequence) 확인 필요
참고
PostgreSQL / Tibero Data type 비교
카테고리 PostgreSQL Tibero 비고 숫자 smallint NUMBER(5) integer / int NUMBER(10) 가장 일반적 bigint NUMBER(19) numeric(p,s) / decimal(p,s) NUMBER(p,s) 동일 serial NUMBER + SEQUENCE Auto increment 직접 구성 문자 varchar(n) VARCHAR2(n) 동일 char(n) CHAR(n) text CLOB 사이즈 제한 주의 날짜/시간 date DATE 동일 timestamp TIMESTAMP timestamp with time zone TIMESTAMP WITH TIME ZONE 이진/LOB bytea BLOB Boolean boolean CHAR(1) 또는 NUMBER(1) Tibero에 Boolean 없음 JSON json / jsonb CLOB 기능 차이 큼 Enum enum VARCHAR2 + CHECK 구현 필요 URL : https://www.postgresql.org/docs/current/datatype.html 참고
3. Table migrator 설정
PostgreSQL의 JDBC Driver를 준비하여 Table migrator 폴더에 복사합니다.
1. JDBC 준비 $ ls -al postgres* -rw-r--r-- 1 root root 1082113 Dec 3 18:18 postgresql-42.6.2.jar 2. migrator.sh 수정 $ cat migrator.sh java -classpath migrator_cli.jar:ojdbc7.jar:mariadb-java-client-3.0.7.jar:mysql-connector-java-8.0.25.jar:osdt_core.jar:osdt_cert.jar:oraclepki.jar:postgresql-42.6.2.jar:msllogger-14.jar:toolcom.jar:internal-jdbc-16.jar:antlr-4.3-complete.jar com.m.migrator.Main "$@" 3. 환경 설정 $ migrator.properties ####################### # Source DB Connection ####################### SOURCE_TYPE=GENERIC SOURCE_DRIVER=org.postgresql.Driver SOURCE_URL=jdbc:postgresql://localhost:5432/testdb SOURCE_USER=postgres SOURCE_PASSWORD=postgres #대소문자 구별 SOURCE_SCHEMA="public" SOURCE_TABLE="mig_test" ####################### # Target DB Connection ####################### TARGET_TYPE=DEFAULT TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver TARGET_URL=jdbc:internal:thin:@localhost:21724:tb724 TARGET_USER=nam TARGET_PASSWORD=nam TARGET_SCHEMA=nam TARGET_TABLE=mig_test ############## # POSTGRESQL ############## # Convert bytea type to blob type. (Default=N) # BYTEA_TO_BLOB=N # Convert text type to clob type. (Default=N) # TEXT_TO_CLOB=N # 이외의 파라미터는 사이트 상황에 맞게 설정
4. 데이터 이관
Table Migrator를 이용하여 PostgreSQL 데이터를 Tibero로 이관합니다.
이관 완료 후에는 로그 및 테이블 건수 비교 등을 통해 데이터 검증을 수행합니다.
1. migrator.sh 수행
$ sh migrator.sh PROPERTY_FILE=migrator.properties
INFO[18:34:42,783][main]TableMigrator.initAndDoMigrate()(1315)
********************************************************************************
*
* Start Log for table migrator.
* Vendor: tibero
* Product: table migrator
* Version: 285621
*
* Trace is started on 18:34:42.782
* Log level: INFO
*
********************************************************************************
INFO[18:34:42,784][main]TableMigrator.initAndDoMigrate()(1356)
=== Parameters ===
PROPERTY_FILE=migrator.properties
SOURCE_TYPE=GENERIC
SOURCE_DRIVER=org.postgresql.Driver
SOURCE_URL=jdbc:postgresql://localhost:5432/testdb
SOURCE_USER=postgres
SOURCE_PASSWORD=************
SOURCE_SCHEMA="public"
SOURCE_SCHEMAORIGN="public"
SOURCE_TABLE="mig_test"
SELECT_SUBPARTITION=
BATCH_THRESHOLD=128
EXTRACT_PARALLEL=N
EXTRACT_THREAD_COUNT=1
SOURCE_TABLE_DEDUPLICATE=N
USE_PARALLEL_HINT=N
PARALLEL_HINT_CNT=-1
USE_OBJECT_PARALLEL_HINT=N
TARGET_TYPE=DEFAULT
TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver
TARGET_URL=jdbc:internal:thin:@localhost:21724:tb724
TARGET_USER=nam
TARGET_PASSWORD=************
TARGET_SCHEMA=nam
TARGET_TABLE=mig_test
COMMIT_SIZE=-1
INDEX_DISABLE=N
SELECT_FETCH_SIZE=1024
SELECT_AS_BYTE=N
DB_FILE_MULTIBLOCK_READ_COUNT=-1
INSERT_METHOD=dpl
INSERT_BATCH=Y
INSERT_PARALLEL=N
INSERT_THREAD_COUNT=1
INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
DPL_LOG_UNIT=500
LOG_FILE_NAME=tableMigrator.log
LOG_LEVEL=INFO
CHECK_COLUMN_METADATA=N
SOURCE_OBJECT_NAME_CASE_TYPE=AUTO
TARGET_OBJECT_NAME_CASE_TYPE=AUTO
==================
INFO[18:34:42,855][TabMig[18:34:42.784]]TbCmdConnection.connect()(54) Connect To The Source- URL : jdbc:postgresql://localhost:5432/testdb ID : postgres
INFO[18:34:42,897][TabMig[18:34:42.784]]TbCmdConnection.connect()(54) Connect To The Target- URL : jdbc:internal:thin:@localhost:21724:tb724 ID : nam
INFO[18:34:42,947][TabMig[18:34:42.784]]TableMigrator.migrate()(269) SOURCE_TYPE : GENERIC
INFO[18:34:42,947][TabMig[18:34:42.784]]TableMigrator.migrate()(270) SOURCE_SCHEMA : "public"
INFO[18:34:42,947][TabMig[18:34:42.784]]TableMigrator.migrate()(291) SOURCE_TABLE : "mig_test"
INFO[18:34:42,947][TabMig[18:34:42.784]]TableMigrator.migrate()(295) TARGET_TYPE : DEFAULT
INFO[18:34:42,947][TabMig[18:34:42.784]]TableMigrator.migrate()(296) TARGET_SCHEMA : "NAM"
INFO[18:34:42,947][TabMig[18:34:42.784]]TableMigrator.migrate()(313) TARGET_TABLE : "MIG_TEST"
INFO[18:34:42,948][TabMig[18:34:42.784]]AbstractTableInfo.getTableInfo()(69) SELECT * FROM "public"."mig_test" WHERE 1=0
INFO[18:34:42,959][TabMig[18:34:42.784]]TableMigrator.migrate()(399) start extracting thread...
INFO[18:34:42,960][TabMig[18:34:42.784].E0]DataExtractor.run()(102) run
INFO[18:34:42,960][TabMig[18:34:42.784].E0]DataExtractor.loggingTabAndColInfo()(564) Extract Information
INFO[18:34:42,960][TabMig[18:34:42.784].E0]DataExtractor.loggingTabAndColInfo()(565) table name : "public"."mig_test"
INFO[18:34:42,960][TabMig[18:34:42.784].E0]DataExtractor.loggingTabAndColInfo()(570) columns [2] - "a" "b"
INFO[18:34:42,960][TabMig[18:34:42.784].E0]DataExtractor.run()(108) TabMig[18:34:42.784].E0 - sql : SELECT t."a",t."b" FROM "public"."mig_test" t
INFO[18:34:42,962][TabMig[18:34:42.784].E0]DataExtractor.run()(308)
Total Extract Time : 2ms
Time For Putting Row on Queue : 0ms
Time For Executing Query Time : 1ms
Time For Executing Fetch Time : 0ms
Percentage of Query and Fetch => 50.00%
INFO[18:34:42,962][TabMig[18:34:42.784].E0]DataExtractor.run()(309) TOTAL Extracted ROWS: 4 (source schema = "public", source table = "mig_test")
INFO[18:34:42,962][TabMig[18:34:42.784].E0]DataExtractor.setTermFlag()(575) Set termFlag and isError : [false]
INFO[18:34:42,962][TabMig[18:34:42.784].E0]DataExtractor.setTermFlag()(576) complete
INFO[18:34:42,966][TabMig[18:34:42.784]]SqlUtility.getWithSqlForDbaTabColsSql()(36) SELECT view_name FROM DBA_VIEWS WHERE VIEW_NAME = ?
INFO[18:34:42,966][TabMig[18:34:42.784]]SqlUtility.getWithSqlForDbaTabColsSql()(39) setString(1) :
INFO[18:34:42,970][TabMig[18:34:42.784]]SqlUtility.getWithSqlForDbaTabColsSql()(51) SELECT column_name FROM DBA_TAB_COLUMNS WHERE TABLE_NAME = ?
INFO[18:34:42,970][TabMig[18:34:42.784]]SqlUtility.getWithSqlForDbaTabColsSql()(54) setString(1) : DBA_TBL_COLS
INFO[18:34:42,970][TabMig[18:34:42.784]]DefaultTableInfo.getTableInfo()(48) with COLS as (
SELECT owner,
column_id,
table_name,
column_name,
data_type,
VIRTUAL_COLUMN virtual_column,
HIDDEN_COLUMN hidden_column
FROM DBA_TBL_COLS)
SELECT column_name, data_type, virtual_column, hidden_column FROM COLS WHERE OWNER=? AND TABLE_NAME=? order by column_id
INFO[18:34:42,970][TabMig[18:34:42.784]]DefaultTableInfo.getTableInfo()(51) setString(1) : NAM
INFO[18:34:42,970][TabMig[18:34:42.784]]DefaultTableInfo.getTableInfo()(54) setString(2) : MIG_TEST
INFO[18:34:42,971][TabMig[18:34:42.784]]TableMigrator.migrate()(497) start loading thread...
INFO[18:34:42,971][TabMig[18:34:42.784]]DataLoader.setRemainThr()(89) Set Remain Threads [0], to [1]
INFO[18:34:42,973][TabMig[18:34:42.784]]TableMigrator.migrate()(534) Extractor termFlag : [true], isError : [false]
INFO[18:34:42,973][L0]TbDPLDataLoader.setDirPathStream()(494) DPL Loading Information
INFO[18:34:42,973][L0]TbDPLDataLoader.setDirPathStream()(495) table name : "NAM"."MIG_TEST"
INFO[18:34:42,973][L0]TbDPLDataLoader.setDirPathStream()(500) columns [2] - "A" "B"
INFO[18:34:42,978][L0.TbDPLDataLoader0]DataLoaderManager.run()(51) run
INFO[18:34:42,985][L0.TbDPLDataLoader0]DataLoaderManager.run()(266) Total Insert Time : 12ms
Time For Taking Row From Queue: 0ms
Time For Executing Batch : 1ms
Time For Executing Commit : 1ms
Percentage of Batch and Commit Time => 16.67%
INFO[18:34:42,986][L0.TbDPLDataLoader0]DataLoaderManager.run()(268) [L0.TbDPLDataLoader0]1764754482986 TOTAL Loaded ROWS: 4 (target schema = "NAM", target table = "MIG_TEST")
INFO[18:34:42,990][TabMig[18:34:42.784]]TableMigrator.migrate()(570) Loader remainThr : [0], isError : [false]
INFO[18:34:42,990][L0.TbDPLDataLoader0]DataLoader.finish()(83) Decrement remainThr : [0] and isError : [false]
INFO[18:34:42,990][TabMig[18:34:42.784]]TableMigrator.resetThrs()(1237) terminate extract thread...1
INFO[18:34:42,991][TabMig[18:34:42.784]]TableMigrator.resetThrs()(1252) terminate loader thread...1
INFO[18:34:42,991][TabMig[18:34:42.784]]TableMigrator.migrate()(585) [VERIFICATION] Source Info : "public"."mig_test" : 4 Rows, Target Info : nam.mig_test : 4 Rows [OK]
INFO[18:34:42,991][TabMig[18:34:42.784]]TableMigrator.migrate()(644) extract conneciton close
INFO[18:34:42,991][TabMig[18:34:42.784]]TableMigrator.migrate()(654) loader connection close
INFO[18:34:42,991][TabMig[18:34:42.784]]TableMigrator.migrate()(664) Elapsed Time (milliseconds) : 207
INFO[18:34:42,991][TabMig[18:34:42.784]]TableMigrator.migrate()(665) End Migration
2. 데이터 검증
1) PostgreSQL
testdb=# select * from public.mig_test;
a | b
-----+----------
10 | mig_test
100 | mig_test
10 | mig_test
100 | mig_test
(4 rows)
2) Tibero
SQL> select * from mig_test;
A B
---------- ----------
10 mig_test
100 mig_test
10 mig_test
100 mig_test
4 rows selected.