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.

☞ “[Tibero] DBLINK 설정_Tibero to PostgreSQL (3)”에서 계속됩니다.​​