PostgreSQL 확장 기능 postgres_fdw
ServerEXTENSION
PostgreSQL 확장 프로그램 설치 명령어
설치시 DB 사용자가 superuser 권한을 가지고 있어야 한다.
- dblink
데이터베이스 세션 내에서 다른 PostgreSQL 데이터베이스로의 연결을 지원하는 모듈
쿼리를 view로 저장 (속도저하 가능성 존재)
세션 단위로 연결되기 때문에 세션이 끊기면 dblink도 끊긴다.
- postgres_fdw
외부 PostgreSQL 서버에 저장된 데이터에 액세스하는 데 사용되는 외부 데이터 래퍼
맨 처음 프로시저만 생성하면 일반 테이블 사용과 다르지 않음
dblink vs postgres_fdw
한 PostgreSQL 서버에서 다른 PostgreSQL 서버로 또는 동일한 서버의 다른 데이터베이스로 연결할 수 있다.
받는 서버는 해당 방화벽을 통한 보내는 서버의 연결을 허용해야 한다.
속도 차이가 없다.
postgres_fdw는 ORM 사용 편의성을 지니고 있다. (dblink는 조회할 때마다 view를 계속 생성)
확장 프로그램 패키지 설치
- 사용 가능한 패키지 보기
# yum list available | grep postgres*- 설치
원하는 확장 프로그램이 누락되어 있는 경우도 있다. 그럴 경우 직접 버전 지정해서 설치.
# yum install postgresql-contrib
# yum -y install postgresql11-contrib.x86_64확장 프로그램 설치
스키마를 지정하지 않으면 기본값 public 에 설치된다.
데이터베이스당 extension 1개만 설치 가능
CREATE EXTENSION [ IF NOT EXISTS ] extension_name
[ WITH ] [ SCHEMA schema_name ]
[ VERSION version ]
[ FROM old_version ]
# CREATE EXTENSION dblink;# CREATE EXTENSION dblink SCHEMA public FROM unpackaged;postgres_fdw
사용 목적 : 외부 서버와 Select Union
1. 모듈 확인
- PostgreSQL
# cd /usr/pgsql-11/share/extension/
# ls -al | grep postgres- Docker PostgreSQL
# docker exec -it 405d930391ee /bin/bash
# cd /usr/share/postgresql/9.6/extension/
# ls -al | grep postgres
-rw-r--r--. 1 root root 507 Jun 20 02:50 postgres_fdw--1.0.sql
-rw-r--r--. 1 root root 172 Jun 20 02:50 postgres_fdw.control
2. 확장 프로그램 설치
# psql -U dico dico_blog
# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION- 오류 1
ERROR: permission denied to create extension "postgres_fdw"
HINT: Must be superuser to create this extension.
# ALTER USER dico WITH SUPERUSER;- 권한 확인
# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
dico | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}3. 외부 데이터 서버 생성
CREATE SERVER {1}
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '{2}', port '{3}', dbname '{4}');
1: 서버명. 임의 설정
2: 원격 DB IP
3: 원격 DB Port
5: 원격 DB 데이터베이스명# CREATE SERVER main_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.54.1', port '5432', dbname 'dico_blog');
CREATE SERVER4. 사용자 매핑 설정
외부에 접속하려는 현재 서버 사용자와 원격 서버의 사용자를 연결
CREATE USER MAPPING FOR {1}
SERVER {2}
OPTIONS (user '{3}', password '{4}');
1: 현재 DB 사용자명
2: 임의 설정한 서버명
3: 원격 DB 사용자명
4: 원격 DB 사용자 비밀번호# CREATE USER MAPPING FOR dico SERVER main_server OPTIONS (user 'dico', password 'dico27love');
CREATE USER MAPPING5. 원격 테이블 생성
접근하려는 원격 DB의 테이블 스키마를 가져온다.
원격 서버에서 테이블을 매핑할 테이블을 생성한다.
- 방법1 : 특정 테이블 사용
CREATE FOREIGN TABLE {1} (
{5}
)
SERVER {2}
OPTIONS (schema_name '{3}, table_name {4}');
1: 사용할 테이블명
2: 임의 설정한 서버명
3: 원격 DB Schema
4: 원격 DB 테이블명
5: 원격 DB 테이블 스키마# CREATE FOREIGN TABLE member_info (
id integer NOT NULL,
data text
)
SERVER member_server
OPTIONS (schema_name 'public', table_name 'member_info');- 방법2 : 전체 테이블 사용
지정한 스키마명으로 모든 테이블에 대한 외부 테이블이 생성된다.
데이터베이스 내에서 테이블을 구분할 스키마를 생성하여 해당 스키마에 외부 테이블 정보를 생성한다.
생성 후 외부 테이블 변경 시 alter를 통해 같이 변경해주거나 새로 생성해야 한다.
IMPORT FOREIGN SCHEMA {1} FROM SERVER {2} INTO {3};
1: 스키마명
2: 임의 설정한 서버명
3: 스키마명 (테이블을 그룹화하기 위한 namespace => 외부 테이블을 스키마로 구별하여 이름 충돌 없음)# CREATE SCHEMA app;
CREATE SCHEMA
# IMPORT FOREIGN SCHEMA public FROM SERVER main_server INTO app;
IMPORT FOREIGN SCHEMA6. 사용
SELECT * FROM {1}.{2};
1: 스키마명
2: 원격 DB 테이블명# SELECT * FROM app.members;7. FDW Options 설정
updatable : 원격 테이블을 수정할 수 있도록 허용할지 여부 (IDM) (기본값 true)
# alter server main_server options (updatable 'true');
ALTER SERVER8. Server 설정 확인
# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
----------+--------+-------------------------+-------------------+------+---------+---------------+-------------
main_server | dico | postgres_fdw | | | | (host '192.168.54.1', port '5432', dbname 'dico_blog', updatable 'true') |
(1 row)
9. foreign table 변경
# alter foreign table app.members add column memo text;참고
Kibae Shin - PostgreSQL 작업기 #2
