[PostgreSQL]postgres_fdw

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