[Tibero] DBLINK 설정_Tibero to PostgreSQL

출처: https://tmaxtiberotech.tistory.com/150

본 문서에서는 Tibero Java Gateway을 이용한 Tibero to PostgreSQL DB Link 설정 및

테스트 환경 구축을 위한 PostgreSQL 설치 방법을 안내 합니다.

​

​

테스트 구성 환경

OS	PostgreSQL	Java Gateway	Tibero
Version : Red Hat Enterprise Linux Server release 7.4 IP : 192.168.0.180 ​ ​	Version : PostgreSQL 12.3 OS user : postgres DB name : test DB user : pg Port : 5432 ​	Port : 9099 ​ ​ ​ ​ ​	Version : Tibero 6 (DB 6.0 FS07_CS_1912) OS user : nam DB name : tibero DB user : nam Port : 18629

​

 

​

DBLink 설정 및 확인

​

1. Java Gateway

1.1. tbJavaGW.zip 압축 해제

[nam@www:/home/nam/tibero6/client/bin]$ unzip tbJavaGW.zip Archive: tbJavaGW.zip   creating: tbJavaGW/   creating: tbJavaGW/lib/ inflating: tbJavaGW/jgw.cfg inflating: tbJavaGW/jgw_service.bat inflating: tbJavaGW/jgwlog.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/log4j-1.2.15.jar inflating: tbJavaGW/lib/tbgateway.jar inflating: tbJavaGW/tbgw

​

​

​

1.2. Java Gateway 설정

[nam@www:/home/nam/tibero6/client/bin/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 # 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=9099 # 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=MSWIN949 # Max length for Types.LONGVARCHAR MAX_LONGVARCHAR=4K # Max length for Types.LONGRAW MAX_LONGRAW=4K

​

​

​

1.3. Postgres JDBC 업로드

아래 링크를 참고해 JDK 버전에 맞는 JDBC를 사용합니다.

 

 

Download | pgJDBC

Download Binary JAR file downloads of the JDBC driver are available here and the current version with Maven Repository. Because Java is platform neutral, it is a simple process of just downloading the appropriate JAR file and dropping it into your classpat

jdbc.postgresql.org

 

[nam@www:/home/nam/tibero6/client/bin/tbJavaGW/lib]$ ls -al total 3028 drwxr-xr-x 2 nam dba 4096 Nov 4 13:44 . drwxr-xr-x 4 nam dba 4096 Nov 5 09:46 .. -rw-r--r-- 1 nam dba 559366 Dec 8 2020 commons-collections.jar -rw-r--r-- 1 nam dba 24019 Dec 8 2020 commons-daemon-1.0.6.jar -rw-r--r-- 1 nam dba 42492 Dec 8 2020 commons-pool.jar -rw-r--r-- 1 nam dba 391834 Dec 8 2020 log4j-1.2.15.jar -rw-r--r-- 1 nam dba 906887 Nov 3 15:11 postgresql-42.2.8.jre7.jar -rw-r--r-- 1 nam dba 483205 Dec 8 2020 tbgateway.jar

​

​

​

1.4. tbgw 수정

[nam@www:/home/nam/tibero6/client/bin/tbJavaGW]$ vi tbgw #! /bin/sh #Classpath commonsdaemon=./lib/commons-daemon-1.0.6.jar commonspool=./lib/commons-pool.jar commonscollections=./lib/commons-collections.jar log4j=./lib/log4j-1.2.15.jar msjdbc=./lib/sqljdbc.jar:./lib/sqljdbc4.jar asejdbc=./lib/jconn3.jar postgresqljdbc=./lib/postgresql-42.2.8.jre7.jar gateway=./lib/tbgateway.jar #log4j properties #log4jfile must be exists on classpath log4jfile=jgwlog.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.configuration=$log4jfile -classpath $commonsdaemon:$commonspool:$commonscollections:$log4j:$gateway:$msjdbc:$asejdbc: $postgresqljdbc:.​ $mainclass CONFIG=$configfile $* &      sleep 1 fi

​

​

​

1.5. tbgw 실행

[nam@www:/home/nam/tibero6/client/bin/tbJavaGW]$ ./tbgw ------------------------------- Name : TmaxData JAVA GATEWAY Database: 5 Port : 9099 -------------------------------

 

DBLink 설정 및 확인

2. PostgreSQL

2.1. 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)

​

​

​

2.2. 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)

​

​

​

3. Tibero

3.1. tbdsn.tbr 설정

[nam@www:/home/nam/tibero6/client/config]$ vi tbdsn.tbr # Generated by gen_tip.sh at Mon Jun 8 16:26:03 KST 2020 ​ # tibero to postgres pgtest=(          (GATEWAY=(LISTENER=(HOST=192.168.0.180)           (PORT=9099))           (TARGET=192.168.0.180:5432:test)           (TX_MODE=LOCAL)) )

​

​

​

3.2. DB Link 생성 및 데이터 조회

Postgres는 대소문자를 구별하므로 user명에도 “”(큰따옴표) 사용하여 DB Link를 생성합니다.

 

[nam@www:/home/nam]$ tbsql nam/nam tbSQL 6 TmaxData Corporation Copyright (c) 2008-. 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"@PG2;       col1 col2                 col3 ---------- -------------------- -----------------------------------------------------           1 name               2021/11/16 12:11:42.380601 1 row selected.

​

​

PostgreSQL 설치

1. 엔진 설치

1.1. 패키지 설치

[root@www:/root]yum -y install gcc gcc-c++ make autoconf wget readline readline-devel zlib zlib-devel openssl openssl-devel gettext gettext-devel python python-devel

 

 

​

1.2. 설치 파일 업로드 및 압축 해제

아래 링크를 통해 원하는 버전을 다운로드 합니다.

 

Index of /pub/source/

 

ftp.postgresql.org

 

[root@www:/root]cd /usr/local/src [root@www:/usr/local/src]ls -al total 196140 drwxr-xr-x. 5 root root 4096 Nov 4 09:49 . drwxr-xr-x. 15 root root 4096 Dec 7 2020 .. -rw-r--r-- 1 root root 26791694 Nov 4 09:48 postgresql-12.3.tar.gz [root@www:/usr/local/src]tar xvzf postgresql-12.3.tar.gz

 

​

​​

1.3. 사용자 생성

[root@www:/usr/local/src] groupadd dba [root@www:/usr/local/src] useradd -d /home/postgres -g dba postgres

​

​

​

1.4. Profile 변경

[root@www:/usr/local/src]su - postgres [postgres@www:/home/postgres]$ vi .bash_profile stty erase ^H ​ ### User ENV ### export PS1="[`whoami`@`hostname`:\$PWD]$ " ​ POSTGRES_HOME=/home/postgres/pgsql PGLIB=$POSTGRES_HOME/lib PGDATA=$POSTGRES_HOME/data MANPATH=$MANPATH:$POSTGRES_HOME/man ​ PATH=$POSTGRES_HOME/bin:$PATH ​ export PATH export POSTGRES_HOME export PGLIB export PGDATA export MANPATH ​ LD_LIBRARY_PATH=/home/postgres/pgsql/lib export LD_LIBRARY_PATH

​

​

​

1.5. PostgreSQL 설치

[root@www:/usr/local/src]cd postgresql-12.3 [root@www:/usr/local/src/postgresql-12.3]./configure --prefix=/home/postgres/pgsql -- enable-depend --enable-nls=ko --with-python …… [root@www:/usr/local/src/postgresql-12.3]make && make install …… make[1]: Leaving directory `/usr/local/src/postgresql-12.3/config' PostgreSQL installation complete.

​

​

​

1.6. 데이터 디렉토리 생성

[root@www:/home/postgres]mkdir -p /home/postgres/pgsql/data [root@www:/home/postgres]chown -R postgres:dba /home/postgres/pgsql

 

​

​

1.7. 클러스터 생성 및 기동

[root@www:/home/postgres]su - postgres [postgres@www:/home/postgres]$ cd /home/postgres/pgsql/bin [postgres@www:/home/postgres/pgsql/bin]$ ./initdb -E utf-8 -D /home/postgres/pgsql/data … Success. You can now start the database server using:     ./pg_ctl -D /home/postgres/pgsql/data -l logfile start [postgres@www:/home/postgres]$./pg_ctl -D /home/postgres/pgsql/data -l logfile start

​

 

​

​1.8. 프로세스 확인

[postgres@www:/home/postgres]$ ps -ef |grep postgres postgres 2544 1 0 16:40 ? 00:00:00 /home/postgres/pgsql/bin/postgres -D /home/postgres/pgsql/data postgres 2548 2544 0 16:40 ? 00:00:00 postgres: checkpointer postgres 2549 2544 0 16:40 ? 00:00:00 postgres: background writer postgres 2550 2544 0 16:40 ? 00:00:00 postgres: walwriter postgres 2551 2544 0 16:40 ? 00:00:00 postgres: autovacuum launcher postgres 2552 2544 0 16:40 ? 00:00:00 postgres: stats collector postgres 2553 2544 0 16:40 ? 00:00:00 postgres: logical replication launcher postgres 2645 31461 0 16:40 pts/8 00:00:00 psql ​ [postgres@www:/home/postgres]$ pg_ctl status pg_ctl: server is running (PID: 2544) /home/postgres/pgsql/bin/postgres "-D" "/home/postgres/pgsql/data"

​

​PostgreSQL 설치

2. DB 구성

2.1. 관리자 비밀번호 설정

[postgres@www:/home/postgres]$ psql -dpostgres Enter new password: [암호 입력] Enter it agin: [암호 재입력]

​

​

​

2.2. postgresql.conf 파일 변경

[postgres@www:/home/postgres]$ vi /home/postgres/pgsql/data/postgresql.conf #listen_addresses = 'localhost' → listen_addresses = '*' 로 변경

​

​

​

2.3. pg_hba.conf 파일 변경

[postgres@www:/home/postgres]$ vi /home/postgres/pgsql/data/pg_hba.conf          host      all          all 0.0.0.0/0 md5          host      all          all 0.0.0.0/0 password 추가

​

​

​

2.4. DB 재시작

[postgres@www:/home/postgres]$ pg_ctl -D /home/postgres/pgsql/data -l logfile restart

​

​

​

2.5. DB 설치 확인

• Postgres 접속 방법 : psql -d[DBNAME] -U[USER]
• 옵션없이 psql 접속 시 postgres 계정으로 postgres db에 접속(default)

 

[postgres@www:/home/postgres] $ psql Password for user postgres: psql (12.3) Type "help" for help. ​ 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 | 1 | template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | t | -1 | 13590 | 479 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres} 13590 | template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | t | f | -1 | 13590 | 479 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres} (3 rows)

​