Document Type | Technical Information
Category | Migration
Applicable Product Versions | Tibero6, Tibero7
Document Number | TMITI037
Overview
This document describes the procedure to migrate PostgreSQL data to Tibero using Table Migrator.
Environment Setup
- PostgreSQL: PostgreSQL 15.15
- Tibero: Tibero7.2.4
Method
1. Confirm Migration Target
PostgreSQL manages schema and owner as separate concepts.
When migrating to Tibero, objects should be mapped based on schema, not owner; mapping by owner may cause object lookup errors.
[root@Gdb1 ~]# su - postgres
[postgres@Gdb1 ~]$ psql -U nam -d testdb
psql (15.15)
Type "help" for help.
1) Version
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) Database Size
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) Schemas
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) Object Information
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) Table Information
testdb=# \d public.mig_test
Table "public.mig_test"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
a | integer | | |
b | character varying(10) | | |
2. Migrate DDL
Before migrating data, create the same structure (DDL) in Tibero as in PostgreSQL.
1. PostgreSQL
1) Dump script
# pg_dump -U postgres -h localhost -p 5432 -s -t mig_test testdb > mig_test.sql
2) Check script
# cat mig_test.sql
....
CREATE TABLE public.mig_test (
a integer,
b character varying(10)
);
...
2. Tibero
1) Create table in Tibero
SQL> create table mig_test (a int, b varchar(10));
Table 'MIG_TEST' created.
Note
PostgreSQL Table-level Dump
pg_dump -U [username] -h [host] -p [port] -s -t [schema].[tablename] [DBname] > [output file]PostgreSQL Schema-level Dump
pg_dump -U [username] -h [host] -p [port] -s -n [schema] [DBname] > [output file]โป DDL conversion (table/PK/FK/Index/Sequence) needs verification
Note
PostgreSQL / Tibero Data Type Comparison
Category PostgreSQL Tibero Remarks Numeric smallint NUMBER(5) integer / int NUMBER(10) Most common bigint NUMBER(19) numeric(p,s) / decimal(p,s) NUMBER(p,s) Same serial NUMBER + SEQUENCE Auto increment configured manually Character varchar(n) VARCHAR2(n) Same char(n) CHAR(n) text CLOB Be careful of size limits Date/Time date DATE Same timestamp TIMESTAMP timestamp with time zone TIMESTAMP WITH TIME ZONE Binary/LOB bytea BLOB Boolean boolean CHAR(1) or NUMBER(1) No Boolean in Tibero JSON json / jsonb CLOB Significant functional differences Enum enum VARCHAR2 + CHECK Needs implementation URL: https://www.postgresql.org/docs/current/datatype.html reference
3. Table Migrator Configuration
Prepare the PostgreSQL JDBC Driver and copy it to the Table Migrator folder.
1. Prepare JDBC $ ls -al postgres* -rw-r--r-- 1 root root 1082113 Dec 3 18:18 postgresql-42.6.2.jar 2. Modify 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. Environment settings $ 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 # Case sensitive 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 # Other parameters should be set according to the site environment
4. Data Migration
Use Table Migrator to migrate PostgreSQL data to Tibero.
After migration, verify the data by checking logs and comparing row counts in tables.
1. Run 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. Data Verification
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.