[PostgreSQL]dblink

dblink는 물리적으로 떨어져 있는 원격 테이블에 접속하기 위해 사용합니다. 또한 같은 물리 공간에 있더라도 다른 DB에 접근 시에는 dblink 를 사용해야 합니다.

dblink 

dblink 설치 및 명령어

CREATE EXTENSION [ IF NOT EXISTS ] extension_name [ WITH ] [ SCHEMA schema_name ] [ VERSION version ] [ FROM old_version ]

간단하게 설치하는 방법은 CREATE EXTENSION dblink 입니다. 또한 스키마를 지정하지 않으면 default로 public에 설치가 됩니다.

여기서 유의해야 할 것은 dblink extension이 데이터베이스 내에 설치가 되면 (사용자가 정한 디비 스키마라던지, default인 public 스키마 라던지..) 지우지 않은 이상 재설치가 불가능합니다.

따라서 스키마 별로 사용이 필요한 부분에서는 alter로 schema를 변경하여 사용하거나 지운 다음 사용 합니다.

CREATE EXTENSION  IF NOT EXISTS dblink SCHEMA other_db -- default 스키마 (public)에 설치를 하지 않을 경우엔 SCHEMA를 정의 -- IF NOT EXISTS는 해당 디비에 EXTENSION이 없을 경우에만 설치합니다. 만약 해당 명령어가 없는데 이미 설치가 되어 있을 경우엔 에러를 뱉습니다.   ALTER EXTENSION SCHEMA other_db1 -- 다른 스키마로 dblink을 이동시켜 사용할 때 사용합니다.   DROP EXTENSION dblink -- dblink를 삭제합니다.

dblink 사용법

사용법은 여러 방법이 있지만 다음과 같이 사용하는 것이 좀 더 깔끔하게 분리하여 관리할 수 있는 것 같은 생각이므로 한 가지 방법만 소개합니다.

SELECT dblink_connect('{1}', 'hostaddr={2} user={3} password={4} dbname={5} port={6}')

{1} : 앞으로 dblink를 사용할 id ( 사용자 임의로 입력 가능)

{2} : 연결할 원격디비의 주소

{3} : 연결할 원격디비의 유저

{4} : 연결할 원격디비 유저의 비밀번호

{5} : 연결할 원격디비의 이름

{6} : 연결할 원격디비의 포트번호

SELECT {3}.* FROM dblink('{1}', '{2}')AS {3} ({4})

{1} : 앞에서 정의한 dblink id

{2} : 연결한 원격디비에 날릴 쿼리

{3} : 원격디비에 날린 쿼리 결과에 대한 임의의 이름 

{4} : 원격디비에 날린 테이블 스키마

만약 {2}의 쿼리에서 select * 와 같이 테이블 전체를 호출하였으면 {4}에 명시할 테이블 스키마는 전체이고 select name, id 와 같이 되어 있다면, 명시할 테이블 스키마는 name과 id 2개만 해주면 됩니다.

dblink 사용예시

CREATE EXTENSION dblink ALTER EXTENSION dblink SCHEMA other_db   SELECT dblink_connect('using_name', 'hostaddr=199.199.199.199 user=user password=1234 dbname=db2 port=5432')   SELECT * FROM dblink('using_name', 'SELECT * FROM abcd') AS name (name character varying(5), etc character varying(20)) -- 원격 db에 존재하는 abcd 테이블 전체 출력   SELECT name.name FROM dblink('using_name', 'SELECT id FROM abcd') AS name (id character varying(5)) -- 원격 db에 존재하는 abcd 테이블에서 id라는 컬럼의 값만 가져와 출력   SELECT * FROM (dblink ('using_name', 'SELECT * FROM abcd') AS name (name character varying(5), etc character varying(20))) AS remote_table, table2 -- 다음과 같이 원격 db에 존재하는 abcd테이블을 가져와 remote_table이라고 이름을 지은 다음, 기존 db에 있는 table2라는 테이블과 join하여 전체 출력   SELECT dblink DISCONNECT('using_name') -- 연결한 원격 db 종료

문제점

1. dblink를 사용하기 위해선 주체가 되는 디비의 유저가 superuser 권한을 가지고 있어야 함
2. 원격 데이터베이스의 정보가 노출될 가능성이 있음
3. 문제라기보단 귀찮은 건데 extension은 디비에 1개밖에 설치가 안되므로 다른 스키마에서 사용하려고 하면 해당 스키마로 extension을 전부 alter 시키던지, 그대로 냅두고 쿼리 상에 데이터베이스 스키마를 전부 명시해 줘야함.

PYTHON(DJANGO)에서 사용

파이썬에서 사용하는 것은 위의 쿼리를 실행만 시키면 됩니다. 여기서 조심해야 할 점은 문제점 3. 에서 명시한 것처럼 스키마를 코드 상에서 변경하였다고 해서 dblink도 변경되지 않습니다. extension 자체를 변경하는 명령어를 실행하여 변경하고자 하는 디비 스키마로 옮긴 뒤 실행해야 합니다.

예를 들어, 기본 스키마(public)에서 schema1로 스키마를 변경하여 쿼리를 그대로 사용한다고 하면 ALTER EXTENSION dblink SCHEMA schema1 라고 명시해야 에러가 나지 않습니다.