[PostgreSQL] DB링크(dblink)

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

(자세한 내용은 밑의 DBLINK 예시로 확인)

 

1. 설치

  - create extension dblink;

 

  ※ CREATE EXTENSION [IF NOT EXISTS] EXTENSION_NAME

        [WITH] [SCHEMA schema_name]

                  [VERSION version]

                  [FROM old_version]

 

  ps. superuser가 아니면 create extension이 동작하지 않음 (-> 임시로 superuser로 만든 후 다시 원복)

     -> alter user user명 superuser;

     -> create extension dblink;

     -> alter user user명 nosuperuser;

 

 

2. dblink 등록

  - SELECT dblink_connect('dblink_id(dblink 이름)', 'host=원격db_주소 user=원격db_user password=원격db_password dbname=원격db_dbname port=원격db_port');

 

 

3. dblink 사용

  - select * from dblink('dblink_id(db등록시 설정한 이름)', '실행할 SQL문')

     as 결과_임의의_이름(컬럼 데이터타입, 컬럼2 데이터타입 ...);

 

  - select * from 

             dblink('dbname=원격db명 port=원격db_port host=원격db_주소 user=원격db_user

             password=원격db_password, '실행할 SQL문')

             결과_임의의_이름(컬럼명1 데이터타입, 컬럼명2 데이터타입);

 

4. dblink 종료

 

- select dblink_disconnect();1. 

 

 

※ target db에 다른 디비의 테이블을 박아서 사용하고 싶다면 postges_fdw 내용 참고

     https://ssunws.tistory.com/32

[PostgreSQL] postgres_fdw

 

 

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

DBLINK 예시

 

1. test용 user 생성 (superuser 계정으로 실행)

  - create user link_test password 'link_test';

 

1. source, target db 생성 (superuser 계정으로 실행)

 target db에서 source db의 테이블 조회하려고 함

  - create database source owner to link_test;

  - create database target owner to link_test;

 

2. link test용 table 생성 (source db에서 실행)

  - create table tb_link(test_id int, test_name varchar(50));

  - insert into tb_link values(1, 'test1'), (2, 'test2);

 

3. dblink extension 생성 (target db에서 실행)

  - create extension dblink;

  - \dx로 생성된 extension 확인

 

※ link_test 유저가 superuser가 아니므로 에러가 남

     create extension dblink: ERROR #42501 permission denied to create extension "dblink"

     Hint: Must be superuser to create this extension.

  -> 임시로 superuser로 변경 후 원복

  alter user link_test superuser;       (superuser계정에서 실행)

  create extension dblink;              (target db (link_test 유저)에서 실행)

  alter user link_test nosuperuser;    (superuser계정에서 실행)

  

 

4. dblink 등록

 ※ SELECT dblink_connect('dblink_id(dblink 이름)', 'host=원격db_주소 user=원격db_user password=원격db_password      dbname=원격db_dbname port=원격db_port');

 

  - select dblink_connect('source','host=localhost user=us_source password=us_source dbname=source port=5432');

 

 

5. dblink 사용

  - select * from dblink('source', 'select * from link_test') as test(test_id int, test_name varchar);

 

 - select * 
      from DBLINK('dbname=source port=5432 host=localhost user=us_source password=us_source'::text,
                         'select test_id, test_name from link_test'::text)
                         test(test_id int, test_name varchar);

 

6. dblink 종료