[postgresql] extension dblink

PostgreSQL은 서로 다른 데이터베이스 간에 직접 연결이 되지 않는다.

시스템 카탈로그 pg_database 를 select 해보면 모든 데이터베이스 정보가 들어있어서 'DB명.테이블명' 이런 형식으로 접근을 해보았는데 안된다.

서로 다른 DB를 연결하기 위해서는 같은 시스템 내에 있더라도 dblink 를 사용해야 한다.

dblink란, DB 데이터베이스 세션 내에서 다른 PostgreSQL 데이터베이스 연결을 지원하는 Extension Module 이다.

Module을 사용하기 위해서는 추가 시켜줘야 하는데 사용하고자 하는 모듈이 있는지 먼저 확인을 해봐야 한다.

PostgreSQL 설치 디렉토리내 share\extension 에서 아래의 파일을 확인할 수 있다.

dblink--1.0.sql

dblink--unpackaged--1.0.sql

설치를 하게 되면 위의 쿼리를 실행시켜 function을 추가한다.

PostgreSQL 9.1 이상 버전에서 CREATE EXTENSION 문으로 설치할 수 있다.

--CREATE EXTENSION Synopsis CREATE EXTENSION [ IF NOT EXISTS ] extension_name [ WITH ] [ SCHEMA schema_name ] [ VERSION version ] [ FROM old_version ] --install dblink CREATE EXTENSION dblink;  

삭제는 DROP EXTENSION 으로 한다.
--DROP EXTENSION Synopsis DROP EXTENSION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] --uninstall dblink DROP EXTENSION dblink;

pgAdmin에서 추가된 dblink관련 function을 확인할 수 있다.

pgAdmin에서 Extensions 메뉴를 통해서도 위에서 했던 작업도 가능하다.

그럼 실제로 dblink를 사용하는 방법은 다음과 같다. (http://www.postgresql.org/docs/9.2/static/dblink.html)

1. dblink_connect : 원격 데이터베이스에 접속한다. 한번 접속하면 disconnect 하기전까지 접속은 유지된다.

--여기서 사용되는 연결명을 별칭으로 실제 쿼리 작업시에 사용 select dblink_connect('연결명') --연결정보에는 DB접속 정보를 입력 select dblink_connect('연결명', '연결정보') --example select dblink_connect('dbconn', 'hostaddr=127.0.0.1 port=5432 dbname=mydb user=userid password=passwd');

2. dblink : 연결된 데이터베이스에서 쿼리를 실행한다.

--수행할 SQL문에서 리턴받을 필드명과 타입을 반드시 명시해줘야 함 select dblink('연결명', '수행할 SQL문') AS t1(필드명1 필드타입1, 필드명2 필드타입2) --example select dblink('dbconn', 'select proname, prosrc from pg_proc') AS t1 (proname name, prosrc text)

3. dblink_disconnect : 연결된 데이터베이스의 접속을 닫는다.

select dblink_disconnect() --example select dblink_disconnect('dbconn')

Reference

• http://www.postgresql.org/docs/9.1/static/dblink.html
• http://stackoverflow.com/questions/3862648/how-to-use-install-dblink-in-postgresql
• http://www.postgresonline.com/journal/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html