dblink와 마찬가지로 물리적으로 떨어져 있는 원격 테이블에 접속하기 위해 사용합니다. 차이점은 아래에서 설명하겠습니다.

CREATE EXTENSION

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

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

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

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

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

CREATE SERVER

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]


CREATE SERVER {1}
	FOREIGN DATA WRAPPER postgres_fdw
	OPTIONS (host '{2}', port '{3}', dbname '{4}');

{1}: 앞으로 원격 db를 사용할 server name (사용자 임의로 입력가능)

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

{3}: 연결할 원격디비의 포트

{4}: 연결할 원격디비의 데이터베이스명

CREATE USER MAPPING

CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
    SERVER server_name
    [ OPTIONS ( option 'value' [ , ... ] ) ]


CREATE USER MAPPING FOR {1}
	SERVER {2}
    OPTIONS (user '{3}', password '{4}');

{1}: 원격 서버와 맵핑시킬 현재 데이터베이스 내에 존재하는 user name (ex, postgres)

{2}: 위에서 정한 원격 db server name

{3}: 원격 디비의 user name

{4}: 원격 디비 user의 password

CREATE FOREIGN TABLE

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    [, ... ]
] )
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  DEFAULT default_expr }


접근하고자 하는 원격 디비의 테이블 스키마를 가져옵니다. (constraint 제외)

CREATE FOREIGN TABLE {1} (
    name text,
	id text,
	...
)
SERVER {2}
OPTIONS (schema_name '{3}', table_name '{4}');

{1}: 사용할 테이블명

{2}: 위에서 정한 원격 db server name

{3}: 원격 디비의 schema

{4}: 원격 디비에서 가져오고자 하는 원본 테이블 명

postgresql_fdw 사용예시

CREATE EXTENSION IF NOT EXISTS postgres_fdw; 
ALTER EXTENSION dblink SCHEMA other_db;
 
CREATE SERVER test1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '199.199.199.123', port '5432', dbname 'db2');

CREATE USER MAPPING FOR postgres SERVER test1 OPTIONS (user 'remote_user', password '1234')
CREATE FOREIGN TABLE IF NOT EXISTS remote_table
(
	id bigint not null,
	name text,
	pwd text
 
)

    SERVER test1
    OPTIONS (schema_name 'remote_schema', table_name 'original_table')
 
 
select * from remote_table

dblink와의 차이점

동일한 쿼리를 dblink와 postgres_fdw 둘다 20번 정도 실행한 결과 속도차이는 크게 있지 않음

단, orm을 사용하기엔 postgres_fdw가 훨씬 수월하고 쿼리 모양새도 더 깔끔함

  • dblink를 사용하여 orm을 적용하기 위해선 dblink 쿼리 부분을 view로 저장해야함
    • 조회 될 때마다 view가 계속 생성되기 때문에 속도저하 가능성 있음

postgres_fdw는 맨 처음 프로시저만 생성하면 다음부터는 일반 테이블 사용하는 것과 같이 사용법 동일

dblink를 사용하기 위해선 주체가 되는 디비의 유저가 superuser 권한이 있어야 하지만 postgres_fdw는 없어도 가능

참조페이지

foreign table : https://www.postgresql.org/docs/9.4/static/sql-createforeigntable.html

user mapping: https://www.postgresql.org/docs/9.3/static/sql-createusermapping.html

server : https://www.postgresql.org/docs/9.1/static/sql-createserver.html


'DB' 카테고리의 다른 글

[SQL] SELECT 결과 UPDATE  (0) 2016.11.18
[SQL] SQL VS CODE  (0) 2016.10.27
[SQL] Union과 Union all 차이  (0) 2016.10.12
[SQL] Joins  (0) 2016.08.24
[DB] DB Index 란?  (2) 2016.07.06
[DB] NoSQL이란?  (0) 2016.06.26


INSERT INTO table SELECT column1,column2,column3 FROM table2 

여러 행을 한번에 insert

'DB > PostgreSQL' 카테고리의 다른 글

[PostgreSQL] COPY  (0) 2016.10.21
[PostgreSQL]postgres_fdw  (0) 2016.09.27
[PostgreSQL] SELECT 결과 INSERT하기  (0) 2016.07.29
[PostgreSQL] SELECT 결과로 테이블 생성하기  (0) 2016.07.29
[PostgreSQL] select 시 serial(auto increment) 추가하기  (0) 2016.07.29
[PostgreSQL]dblink  (0) 2016.07.11


CREATE TABLE table AS(
SELECT column1, column2, column3
FROM other_table
)

from에서 받아온 테이블 내 컬럼의 속성을 그대로 타서 생성이 된다.


SELECT ROW_NUMBER() OVER (ORDER BY column1) AS NUM, column1, column2, column3
FROM SBO_BIZM

다음과 같이 할 경우, column1의 순서에 따라 serial 컬럼이 추가되어 표시가 된다.


select결과로 바로 테이블을 생성할 때, 위와 같은 방법으로 serial을 추가해주는 효과를 볼 수 있다.

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 라고 명시해야 에러가 나지 않습니다.

+ Random Posts