Download as PDF, PPTX
![/db2pg/setting/
1 {
2 "comment": {
3 "postgres": "#",
4 "mysql": [
5 "--"
6 ]
7 },
8 "string": {
9 "postgres": "'",
10 "mysql": [
11 """
12 ]
13 },
14 "classify_string": {
15 "postgres": """,
16 "mysql": [
17 "`"
18 ]
19 },
20 "integer_case_1": {
21 "postgres": "SMALLINT",
22 "mysql": [
23 "^(?i)TINYINTs*(?[0-9]*)s*UNSIGNED?$",
24 "^(?i)SMALLINT$(?[0-9]*)?$",
25 "^(?i)TINYINTs*(?[0-9]*)?$"
26 ],
27 "mss": [
28 "^(?i)TINYINT$"
29 ]
각 DB별
Data Type
변환 방식을
설정](https://image.slidesharecdn.com/04-pgdaydb2pgv1-181112042107/85/Pgday-Seoul-2018-DB-PostgreSQL-Migration-DB2PG-11-320.jpg)
![/db2pg/setting/
1 {
2 "comment": {
3 "postgres": "#",
4 "mysql": [
5 "--"
6 ]
7 },
8 "string": {
9 "postgres": "'",
10 "mysql": [
11 """
12 ]
13 },
14 "classify_string": {
15 "postgres": """,
16 "mysql": [
17 "`"
18 ]
19 },
20 "integer_case_1": {
21 "postgres": "SMALLINT",
22 "mysql": [
23 "^(?i)TINYINTs*(?[0-9]*)s*UNSIGNED?$",
24 "^(?i)SMALLINT$(?[0-9]*)?$",
25 "^(?i)TINYINTs*(?[0-9]*)?$"
26 ],
27 "mss": [
28 "^(?i)TINYINT$"
29 ]
각 DB별
Data Type
변환 방식을
설정](https://image.slidesharecdn.com/04-pgdaydb2pgv1-181112042107/75/Pgday-Seoul-2018-DB-PostgreSQL-Migration-DB2PG-11-2048.jpg)
Uploaded byPgDay.Seoul
[Pgday.Seoul 2018] 이기종 DB에서 PostgreSQL로의 Migration을 위한 DB2PG
This document discusses DB2PG, a tool for migrating data between different database management systems. It began as an internal project in 2016 and has expanded its supported migration paths over time. It can now migrate schemas, tables, data types and more between Oracle, SQL Server, DB2, MySQL and other databases. The tool uses Java and supports multi-threaded imports for faster migration. Configuration files allow customizing the data type mappings and queries used during migration. The tool is open source and available on GitHub under the GPL v3 license.
![[pgday.Seoul 2022] PostgreSQL구조 - 윤성재](https://cdn.slidesharecdn.com/ss_thumbnails/pgday2022-postgresql-20221112-221114014106-bbfb1955-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[pgday.Seoul 2022] 서비스개편시 PostgreSQL 도입기 - 진소린 & 김태정](https://cdn.slidesharecdn.com/ss_thumbnails/postgresql-221121085744-f0fb1a8a-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2021] 1. 예제로 살펴보는 포스트그레스큐엘의 독특한 SQL](https://cdn.slidesharecdn.com/ss_thumbnails/sql-211217063145-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2020] SQL Tuning](https://cdn.slidesharecdn.com/ss_thumbnails/pgday-201117134901-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2017] 2. PostgreSQL을 위한 리눅스 커널 최적화 - 김상욱](https://cdn.slidesharecdn.com/ss_thumbnails/pgday-171106040432-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2017] 3. PostgreSQL WAL Buffers, Clog Buffers Deep Dive - 이근오](https://cdn.slidesharecdn.com/ss_thumbnails/pgday171104-171106041604-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2021] 2. Porting Oracle UDF and Optimization](https://cdn.slidesharecdn.com/ss_thumbnails/oracleudfmigration20211203-211227052428-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[pgday.Seoul 2022] POSTGRES 테스트코드로 기여하기 - 이동욱](https://cdn.slidesharecdn.com/ss_thumbnails/postgres-221114014538-b9df2ddf-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2017] 8. PostgreSQL 10 새기능 소개 - 김상기](https://cdn.slidesharecdn.com/ss_thumbnails/pgday-seoul-2017-pg10-new-features-171106041845-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2017] 7. PostgreSQL DB Tuning 기업사례 - 송춘자](https://cdn.slidesharecdn.com/ss_thumbnails/cjsongpostgresqldbtuningexam-20171102-171106043044-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[pgday.Seoul 2022] PostgreSQL with Google Cloud](https://cdn.slidesharecdn.com/ss_thumbnails/pgday-postgresqlwithgooglecloud-221114013605-5def484f-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2020] 포스트그레스큐엘 자국어화 이야기](https://cdn.slidesharecdn.com/ss_thumbnails/postgresqli18nko-201117135339-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2019] AppOS 고성능 I/O 확장 모듈로 성능 10배 향상시키기](https://cdn.slidesharecdn.com/ss_thumbnails/appos-2019-191218045825-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2019] Citus를 이용한 분산 데이터베이스](https://cdn.slidesharecdn.com/ss_thumbnails/citus20191207studypgday-191218045308-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2019] Advanced FDW](https://cdn.slidesharecdn.com/ss_thumbnails/tarantulav2-191218044914-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2018] PostgreSQL 11 새 기능 소개](https://cdn.slidesharecdn.com/ss_thumbnails/pgday2018-pg11-181112042714-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2018] PostgreSQL 성능을 위해 개발된 라이브러리 OS 소개 apposha](https://cdn.slidesharecdn.com/ss_thumbnails/06-pgdayseoul2018sangwook-181112042505-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2018] PostgreSQL Authentication with FreeIPA](https://cdn.slidesharecdn.com/ss_thumbnails/05-20181103pgdayseoulpostgresqlauthenticationwithfreeipa-181112042327-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2018] AWS Cloud 환경에서 PostgreSQL 구축하기](https://cdn.slidesharecdn.com/ss_thumbnails/03-pgday-flytothecloudkimdongsu-181112041825-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2018] Greenplum의 노드 분산 설계](https://cdn.slidesharecdn.com/ss_thumbnails/02-20181103pgdayseminarv1-181112041352-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2018] replacing oracle with edb postgres](https://cdn.slidesharecdn.com/ss_thumbnails/01-replacingoraclewithedbpostgres20181023-181112040354-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2017] 6. GIN vs GiST 인덱스 이야기 - 박진우](https://cdn.slidesharecdn.com/ss_thumbnails/pgday-171106044702-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2017] 5. 테드폴허브(올챙이) PostgreSQL 확장하기 - 조현종](https://cdn.slidesharecdn.com/ss_thumbnails/postgresql-171106044405-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2017] 1. PostGIS의 사례로 본 PostgreSQL 확장 - 장병진](https://cdn.slidesharecdn.com/ss_thumbnails/postgispostgresqlpgday2017-171103060406-171106044046-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
![[Pgday.Seoul 2017] 4. Composite Type/JSON 파라미터를 활용한 TVP구현(with C#, JAVA) - 지현명](https://cdn.slidesharecdn.com/ss_thumbnails/pgday-171106042708-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
[Pgday.Seoul 2018] 이기종 DB에서 PostgreSQL로의 Migration을 위한 DB2PG
- 1.
- 2. - 비용절감? - 정책이슈 - 성능, 기능이슈 - 사용상의 편의성 - Cloud등으로의 환경변화 - 기타 등등 . . .
- 3. - Oracle이외의 DB는어떤 tool로? - ora2pg의 성능(대용량) - Perl engineer? NO, java engineer? Yes - 수작업 ㅠㅠ - 국가정보자원관리원 Project진행
- 4. - 2016. 05월사내 Project로 시작 - 2017. 03월 Oracle Spatial data 지원 - 2017. 10월 MS-SQL 지원 - 2018. 05월 공개SW 기술개발 지원사업 참여 - 2018. 06월 OpenSource화 결정 및 Github - 2018. 10월 Oracle, DB2, MS-SQL, Sybase, Mysql지원
- 5. - 제품별 데이터구조의 호환성 - GIS데이터와 같은 특수한 데이터 변환 - DB별 개성적인 DDL - Resource 부족(개발자, DBA)
- 6. - PostgreSQL 시장확대 -공개SW 기술개발 지원사업 참여 - 개발자의 참여 유도(능력자필요) - K4M value up!
- 8. DBMS DDL ExtractData Migration Oracle Yes Yes MS-SQL Yes Yes DB2 Yes Sybase Yes Mysql Yes Yes cubrid, altibase Soon
- 9. - Schema - Tables -Primary key - Unique - Foreign Key - View - Sequence - Index
- 10. Github db2pg / / Images/ / setting / convert_map.json / setting / mapper / MetaExtractMapper.xml / src/main/java : java source
- 11. /db2pg/setting/ 1 { 2 "comment":{ 3 "postgres": "#", 4 "mysql": [ 5 "--" 6 ] 7 }, 8 "string": { 9 "postgres": "'", 10 "mysql": [ 11 """ 12 ] 13 }, 14 "classify_string": { 15 "postgres": """, 16 "mysql": [ 17 "`" 18 ] 19 }, 20 "integer_case_1": { 21 "postgres": "SMALLINT", 22 "mysql": [ 23 "^(?i)TINYINTs*(?[0-9]*)s*UNSIGNED?$", 24 "^(?i)SMALLINT$(?[0-9]*)?$", 25 "^(?i)TINYINTs*(?[0-9]*)?$" 26 ], 27 "mss": [ 28 "^(?i)TINYINT$" 29 ] 각 DB별 Data Type 변환 방식을 설정
- 12. /db2pg/setting/mapper/ 1 <?xml version="1.0"encoding="UTF-8"?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5 6 <mapper namespace="com.k4m.experdb.db2pg.mapper.MetaExtractMapper"> 7 8 <resultMap id="getColumnListResult" type="com.k4m.experdb.db2pg.common.LowerKeyMap"> 9 <result property="ORDINAL_POSITION" column="ORDINAL_POSITION" /> 10 <result property="COLUMN_NAME" column="COLUMN_NAME" /> 11 <result property="COLUMN_TYPE" column="COLUMN_TYPE" /> 12 <result property="COLUMN_DEFAULT" column="COLUMN_DEFAULT" jdbcType="VARCHAR" javaType="java.lang.String" /> 13 <result property="IS_NULL" column="IS_NULL" /> 14 <result property="COLUMN_COMMENT" column="COLUMN_COMMENT" /> 15 <result property="NUMERIC_PRECISION" column="NUMERIC_PRECISION" /> 16 <result property="NUMERIC_SCALE" column="NUMERIC_SCALE" /> </resultMap> 17 18 <resultMap id="getViewInformResult" type="com.k4m.experdb.db2pg.common.LowerKeyMap"> 19 <result property="VIEW_NAME" column="VIEW_NAME" /> 20 <result property="TEXT" column="TEXT" jdbcType="VARCHAR" javaType="java.lang.String" /> 21 </resultMap> 22 23 <select id="getTableNames" parameterType="map" resultType="java.lang.String" databaseId="ORA"> 24 SELECT OBJECT_NAME as TABLE_NAME FROM ALL_OBJECTS 25 WHERE OWNER=#{TABLE_SCHEMA} 26 AND OBJECT_NAME NOT IN ('TOAD_PLAN_TABLE','PLAN_TABLE') 27 AND OBJECT_NAME NOT LIKE 'MDRT%' 28 AND OBJECT_NAME NOT LIKE 'MDXT%' 29 <choose> 각 DBMS별 Object 추출 Query
- 13. db2pg.config 위치 : /db2pg/src/main/java/com/k4m/experdb/db2pg/sample/ 1SRC_EXPORT=FALSE 2 PG_CONSTRAINT_EXTRACT=FALSE 3 SRC_DDL_EXPORT=FALSE 4 5 SRC_HOST= 6 SRC_USER= 7 SRC_PASSWORD= 8 SRC_DATABASE= 9 SRC_SCHEMA= 10 SRC_DB_TYPE=ORA 11 SRC_PORT=1521 12 SRC_DB_CHARSET=UTF8 13 SRC_LOB_FETCH_SIZE=1024 14 SRC_STATEMENT_FETCH_SIZE=3000 15 SRC_TABLE_SELECT_PARALLEL=1 16 SRC_TABLE_COPY_SEGMENT_SIZE=3000 17 VERBOSE=TRUE 18 #SRC_WHERE= 19 TABLE_ONLY=TRUE 20 TRUNCATE=FALSE 21 #SRC_ALLOW_TABLES= 22 #SRC_EXCLUDE_TABLES= 23 #SRC_ROWNUM= 24 TAR_HOST= 25 TAR_USER= 26 TAR_PASSWORD= 27 TAR_DATABASE= 28 TAR_SCHEMA= 29 TAR_PORT= Source 및 Target의 Connection info
- 14. queries.xml 위치 : /db2pg/src/main/java/com/k4m/experdb/db2pg/sample/ 1<QUERIES> 2 <QUERY> 3 <NAME></NAME> 4 <SELECT> 5 </SELECT> 6 </QUERY> 7 </QUERIES> Query를 이용한 Migration시 설정
- 15. ora2pg vs DB2PG ora2pgdb2pg Language perl java Multi thread Import NO YES DB to DB Migration NO YES Data type Mapping file NO YES Support DBMS 1+1(mysql) 5 DB2PG fast than ora2pg at lease 2
- 16.
- 17. https://github.com/experdb/eXperDB-DB2PG facebook : https://www.facebook.com/experdb navercafe : http://cafe.naver.com/psqlmaster
- 19. - Support Partitiontable - cubrid data migration - altibase data migration - Everything you want!!(wish) 능력자분들의 많은 참여 부탁 드립니다.
- 20.