주 콘텐츠로 건너뛰기

검색

Welcome to Tibero GTS!

DB Link (Tibero to PostgreSQL)

문서유형ㅣ기술정보

분야ㅣ인터페이스/연동

적용제품버전ㅣ7FS04PS

문서번호ㅣTIITI037

개요

Tibero Java Gateway를 이용한 Tibero to PostgreSQL DB Link 설정 방법을 안내합니다.

 

테스트 구성 환경

서버명 OS version DB Version DB Name Port Username IP
Tibero CentOS 8.4.2105 Tibero 7.2.4 tibero 8629 sys 172.16.59.110
PostgreSQL  CentOS 8.4.2105 PostgreSQL 10.17 test 5432 pg 172.16.59.111

 

Tibero Java Gateway 

설정 내용
파일 위치 $TB_HOME/client/bin/tbJavaGW.zip
Port 9093

 

방법

DB Link 설정 및 확인

Java Gateway 설정

tbJavaGW.zip 압축 해제

[tibero@kimmi bin]$ cd $TB_HOME/client/bin
[tibero@kimmi bin]$ unzip tbJavaGW.zip
Archive:  tbJavaGW.zip
  creating: tbJavaGW/
  creating: tbJavaGW/lib/
 inflating: tbJavaGW/jgw.cfg
 inflating: tbJavaGW/jgw_service.bat
 inflating: tbJavaGW/jgwlog4msl18.properties
 inflating: tbJavaGW/lib/commons-collections.jar
 inflating: tbJavaGW/lib/commons-daemon-1.0.6.jar
 inflating: tbJavaGW/lib/commons-pool.jar
 inflating: tbJavaGW/lib/msllogger-18.jar
 inflating: tbJavaGW/lib/tbgateway.jar
 inflating: tbJavaGW/tbgw

 

Java Gateway 설정

DATABASE를 POSTGRESQL로 설정합니다. 
(default port : 9093, 설정 변경이 필요하다면 LISTNER_PORT 수정)

[tibero@kimmi bin]$ cd $TB_HOME/client/bin/tbJavaGW
[tibero@kimmi tbJavaGW]$ vi jgw.cfg
# Target database
DATABASE=POSTGRESQL

# Datasource class name for target database
# use with DATABASE=JDBC30 option.
#DATASOURCE_CLASS_NAME=com.tmax.tibero.jdbc.ext.TbDataSource
DATASOURCE_CLASS_NAME=org.postgresql.Driver

# XA datasource class name for target database
# use with DATABASE=JDBC30 option.
#XA_DATASOURCE_CLASS_NAME=com.tmax.tibero.jdbc.ext.TbXADataSource

# Listener port
LISTENER_PORT=9093

# Initial thread pool size
INIT_POOL_SIZE=10

# Max thread pool size
MAX_POOL_SIZE=100

# Max cursor cache size per thread
MAX_CURSOR_CACHE_SIZE=100

# Gateway Encoding-"ASCII", "EUC-KR", "MSWIN949", "UTF-8", "UTF-16", "SHIFT-JIS"
ENCODING=UTF-8

# Max length for Types.LONGVARCHAR
MAX_LONGVARCHAR=4K

# Max length for Types.LONGRAW
MAX_LONGRAW=4K

 

PostgreSQL JDBC 업로드

jdk 버전에 맞는 JDBC를 사용합니다. (다운로드 경로 : https://jdbc.postgresql.org/download/)

[tibero@kimmi tbJavaGW]$ cd $TB_HOME/client/bin/tbJavaGW/lib
[tibero@kimmi lib]$ ls -alrt
합계 4276
-rw-r--r--. 1 tibero dba  522253 10월 13 02:12 tbgateway.jar
-rw-r--r--. 1 tibero dba 2075494 10월 13 02:12 msllogger-18.jar
-rw-r--r--. 1 tibero dba   42492 10월 13 02:12 commons-pool.jar
-rw-r--r--. 1 tibero dba   24019 10월 13 02:12 commons-daemon-1.0.6.jar
-rw-r--r--. 1 tibero dba  588337 10월 13 02:12 commons-collections.jar
-rw-r--r--. 1 tibero dba 1116727 10월 27 14:32 postgresql-42.7.8.jar
drwxr-xr-x. 4 tibero dba     161 11월  5 17:26 ..
drwxr-xr-x. 2 tibero dba     167 11월  5 17:27 .

 

tbgw 수정

postgresqljdbc 인자 값에 2.1.3에서 업로드한 jar 파일을 입력합니다. 그리고 java 실행 인자에 $postgresqljdbc를 추가합니다.

[tibero@kimmi lib]$ cd $TB_HOME/client/bin/tbJavaGW
[tibero@kimmi lib]$ vi tbgw
#! /bin/sh

#Classpath
commonsdaemon=./lib/commons-daemon-1.0.6.jar
commonspool=./lib/commons-pool.jar
commonscollections=./lib/commons-collections.jar
msllogger=./lib/msllogger-18.jar
msjdbc=./lib/sqljdbc.jar:./lib/sqljdbc4.jar
asejdbc=./lib/jconn3.jar
gateway=./lib/tbgateway.jar
mysqljdbc=./lib/mysql-connector-java-5.1.40-bin.jar
hanajdbc=./lib/ngdbc-2.20.17.jar
postgresqljdbc=./lib/postgresql-42.7.8.jar
hivepre=./lib/httpclient-4.4.jar:./lib/libthrift-0.9.3.jar:./lib/httpcore-4.4.jar:./lib/slf4j-api-1.7.25.jar:./lib/curator-client-2.12.0.jar:./lib/commons-lang-2.6.jar:./lib/guava-14.0.1.jar
hive=$hivepre:./lib/hive-jdbc-3.1.2.jar:./lib/hive-service-rpc-3.1.2.jar:./lib/hive-service-3.1.2.jar:./lib/hive-common-3.1.2.jar:./lib/hive-serde-3.1.2.jar

#log4j properties
#log4jfile must be exists on classpath
log4jfile=jgwlog4msl18.properties

#Main Class
mainclass=com.tmax.tibero.gateway.main.GatewayMain
configfile=./jgw.cfg

if [[ $# -gt 0 ]] && [[ $1 = "-v" ]] ; then
    java -jar $gateway
else
    java -Xms128m -Xmx512m -Dlog4j.configurationFile=$log4jfile -classpath $mysqljdbc:$commonsdaemon:$commonspool:$commonscollections:$msllogger:$gateway:$msjdbc:$asejdbc:$postgresqljdbc:$hive:$hanajdbc:. $mainclass CONFIG=$configfile $* &
    sleep 1
fi

 

PostgreSQL 설정

DB 및 유저 생성

[postgres@www:/home/postgres]$ psql
psql (12.3)
Type "help" for help.

postgres=# create database test;
CREATE DATABASE

postgres=# select * from pg_database;
  oid  |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |               datacl           
-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
 13591 | postgres  |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         13590 |          479 |          1 |          1663 | 
 16384 | test      |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         13590 |          479 |          1 |          1663 | 
(2 rows)

postgres=# create user pg login password 'abc1234' superuser;
CREATE ROLE

postgres=# select * from pg_user;
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          | 
 pg       |    16385 | f           | t        | f       | f            | ******** |          | 
(2 rows)

 

Table 생성 및 데이터 조회

[postgres@www:/home/postgres]$ psql -dtest -Upg
psql (12.3)
Type "help" for help.

test=# create table p_table(
test(# col1 serial primary key,
test(# col2 varchar(20) not null,
test(# col3 timestamp not null);
CREATE TABLE

test=# insert into p_table values (1, 'name', current_timestamp);
INSERT 0 1

test=# select * from p_table;
 col1 | col2 |            col3            
------+------+----------------------------
    1 | name | 2021-11-16 12:11:42.380601
(1 row)

 

Tibero 설정

tbdsn.tbr 설정

[tibero@kimmi ~]$ vi $TB_HOME/client/config/tbdsn.tbr

## tibero to postgreSQL
pgtest=(
    (GATEWAY=(LISTENER=(HOST=localhost)
              (PORT=9093))
              (TARGET=172.16.59.111:5432:test)
              (TX_MODE=LOCAL)
    )
)

 

DB Link 생성 및 데이터 조회

[tibero@kimmi ~]$ tbsql sys/tibero

tbSQL 7

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

Connected to Tibero.

SQL> create database link PG connect to "pg" identified by 'abc1234' using 'pgtest';

Database Link 'PG' created.

SQL> select * from "p_table"@PG;

      col1 col2                 col3
---------- -------------------- -----------------------------------------------------------------
         1 name                 2025/11/05 14:42:03.331876


1 row selected.

주의

postgreSQL은 user_name. table_name 대소문자를 구분하므로 ""를 사용해야 합니다.