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.
