[ PostgreSql ] 서로 다른 곳의 DB dblink로 연결하기

아마 이런 일은 드물겠지만 연구과제 프로젝트 개발 중 연구과제를 같이 진행하는 타 회사의 PostgreSql DB를 연결해서 정보를 조회해야하는 일이 생겼다.

즉, 그림을 그리면 아래와 같아졌다.

 

 

좀 그림은 이상하지만 간단하게 설명하면 타사의 DB의 데이터를 가져와서 사용해야한다는 점!!

타사 DB의 데이터는 계속 업데이트 되므로 데이터만 받아서 따로 우리 회사 내 서버 DB에 데이터를 넣을 수도 없었다.

 

그래서 이런 저런 방법을 찾아보다가 PostgreSql의 DBLink라는 기능을 사용하기로 했다.

 

 

1. dblink 기능 설치

PostgreSql이 설치되어 있다면 dblink를 사용할 수 있지만 사전 준비가 필요하다. 마치 PostgreSql에서 geometry 컬럼 타입을 사용하기 위해 사전 준비가 필요한 것처럼 말이다.

dblink 또한 geometry 컬럼 타입처럼 바로 사용할 수 있는 것이 아닌 아래 명령어를 통해 dblink 기능을 해당 데이터베이스에 설치해줘야한다.

create extension dblink;

 

2. DB 링크 기능 사용하기

dblink를 설치했다면 바로 dblink를 사용할 수 있다. dblink를 사용하는 방법에는 DB에 dblink 연결 정보를 등록하고 사용하는 방법과 DB에 dblink 연결 정보를 등록하지 않고 쿼리에서 1회성으로 사용하는 방법이 있다.

 

 

2.1 DB에 dblink 연결 정보 등록하고 사용하기

1회성이 아닌 여러 번 똑같은 다른 곳의 DB에 연결해서 데이털르 사용할 일이 있을 때 사용하는 방법이다. 처음에 DB에 dblink 연결 정보를 등록하고 필요한 만큼 사용한 후 사용이 다 끝나면 등록된 연결을 해제한다.

host, port와 같은 dblink connect 정보를 여러 번 입력하지 않아도 된다.

 

2.1.1 DB에 dblink 연결 등록

아래 쿼리를 통해 연결해야할 타 DB의 접속 정보를 특정 DB연결 정보 이름으로 입력한다.

select dblink_connect('${db 연결 정보 이름}', 'host=${host} port=${port} dbname=${database} user=${user} password=${password}');

 

위 쿼리에 접속 정보에 에러가 없다면 아래와 같이 OK를 반환한다.

만일 에러가 발생하면 접속 정보가 달라졌다거나 dblink_connect를 사용하는 방법이 다를 수도 있으니(PostgreSql 버전마다) 에러 메시지를 잘 살펴보길 바란다.

(그럴 일은 적지만 만일 해당 데이터베이스에 따로 인증 없이 접근할 수 있도록 설정해놨다면 user, password 등을 생략할 수 있다.)

 

하지만 이 연결 또한 영구적인 것은 아니고 세션 단위로 이루어져 있어서 세션이 끊어지면 새로 연결 정보를 등록해서 사용해야 한다.

 

2.1.2 연결된 외부 PostgreSql 데이터 조회하기

이제 dblink를 통해 연결을 했다면 등록된 연결 정보를 통해 외부 데이터를 조회해 사용할 수 있다.

사용하는 방법은 아래와 같다.

select *
from dblink('${db 연결 정보 이름}'
	   , ${타 DB에서 수행할 SQL}
     ) as ${반환 받을 테이블 Alias}(${컬럼명1 타입1, 컬럼명2 타입2});

 

dblink라는 함수 안에서 등록한 타 DB 연결 정보 이름과 해당 DB에서 수행할 SQL문을 입력하고 반환 받을 테이블 alias를 지정해주고 반환 받을 컬럼들을 입력한다. 여기서 '반환 받을 테이블 Alias'와 반환 받는 컬럼을 명시하는 부분의 타입까지 명시를 해줘야한다. 복잡해보일 수 있는데 테스트 쿼리는 아래와 같다.

select * 
from dblink('dblink-test'
	    , 'SELECT id, name, date FROM another_test'
) as test(id int, name text, date timestamptz);

 

참고로 아래와 같이 dblink()안의 SQL에 'select * ...'로 쿼리를 작성하고서 반환 컬럼 명시를 일부만 할 경우 아래 에러가 발생한다.

- remote query result rowtype does not match the specified FROM clause rowtype

 

 

2.1.3 DB dblink 연결 해제

이와 같이 등록된 dblink 연결 정보로 필요한 데이터 조회를 다 마쳤다면 아래 쿼리로 연결된 정보를 제거하고 연결을 해제한다.

select dblink_disconnect('${db 연결 정보 이름}');

 

연결 해제를 성공적으로 하면 연결 등록할 때와 마찬가지로 OK를 반환한다. 만일 여기서 에러가 발생한다면 연결 등록이 정상적으로 등록된게 맞는지 의심해봐야 한다.

 

2.2 쿼리에서 일회성으로 dblink 사용하기

쿼리에서 일회성으로 dblink를 사용할 때는 위처럼 연결 등록/해제 절차를 밟을 필요 없이 쿼리에서 dblink() 함수에 접속 정보를 넣어 바로 연결하면 된다.

나는 mybatis에서 이 일회성 dblink를 사용해서 타 DB의 데이터를 활용했었다.

 

방법은 아래처럼 사용하면 되는 것으로 매우 간단하다.

select *
from dblink('host=${host} port=${port} dbname=${database} user=${user} password=${password}'
	   , ${타 DB에서 수행할 SQL}
     ) as ${반환 받을 테이블 Alias}(${컬럼명1 타입1, 컬럼명2 타입2});

 

위에 작성했던 '2.1.2 연결된 외부 PostgreSql 데이터 조회하기'에서 '${db 연결 정보 이름}' 부분에 그냥 해당 타 DB의 연결 정보를 입력하면 된다. 이 때는 일회성으로 연결이 되는 것으로 해당 쿼리를 수행하고 나면 연결이 끊어진다.

 

 

 

 

✋ PostgreSql dblink document

https://www.postgresql.org/docs/12/dblink.html

F.10. dblink