> Server

PostgreSQL 확장 기능 postgres_fdw

EXTENSION

PostgreSQL 확장 프로그램 설치 명령어

설치시 DB 사용자가 superuser 권한을 가지고 있어야 한다.

  • dblink

데이터베이스 세션 내에서 다른 PostgreSQL 데이터베이스로의 연결을 지원하는 모듈

쿼리를 view로 저장 (속도저하 가능성 존재)
세션 단위로 연결되기 때문에 세션이 끊기면 dblink도 끊긴다.

  • postgres_fdw

외부 PostgreSQL 서버에 저장된 데이터에 액세스하는 데 사용되는 외부 데이터 래퍼

맨 처음 프로시저만 생성하면 일반 테이블 사용과 다르지 않음

dblink vs postgres_fdw

한 PostgreSQL 서버에서 다른 PostgreSQL 서버로 또는 동일한 서버의 다른 데이터베이스로 연결할 수 있다.

받는 서버는 해당 방화벽을 통한 보내는 서버의 연결을 허용해야 한다.

속도 차이가 없다.

postgres_fdw는 ORM 사용 편의성을 지니고 있다. (dblink는 조회할 때마다 view를 계속 생성)

확장 프로그램 패키지 설치

  • 사용 가능한 패키지 보기

1

# yum list available | grep postgres*

  • 설치

원하는 확장 프로그램이 누락되어 있는 경우도 있다. 그럴 경우 직접 버전 지정해서 설치.

1

2

# yum install postgresql-contrib

1

# 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 ]

1

# CREATE EXTENSION dblink;

1

# CREATE EXTENSION dblink SCHEMA public FROM unpackaged;

postgres_fdw

사용 목적 : 외부 서버와 Select Union

1. 모듈 확인

  • PostgreSQL

1

2

# cd /usr/pgsql-11/share/extension/
# ls -al | grep postgres

  • Docker PostgreSQL

1

2

3

4

5

6

# 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. 확장 프로그램 설치

1

2

3

# 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.

1

# ALTER USER dico WITH SUPERUSER;

  • 권한 확인

1

2

3

4

5

6

# \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}');

Plain Text

1

2

3

4

1: 서버명. 임의 설정
2: 원격 DB IP
3: 원격 DB Port
5: 원격 DB 데이터베이스명

1

2

# CREATE SERVER main_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.54.1', port '5432', dbname 'dico_blog');
CREATE SERVER

4. 사용자 매핑 설정

외부에 접속하려는 현재 서버 사용자와 원격 서버의 사용자를 연결

CREATE USER MAPPING FOR {1}

SERVER {2}

OPTIONS (user '{3}', password '{4}');

Plain Text

1

2

3

4

1: 현재 DB 사용자명
2: 임의 설정한 서버명
3: 원격 DB 사용자명
4: 원격 DB 사용자 비밀번호

1

2

# CREATE USER MAPPING FOR dico SERVER main_server OPTIONS (user 'dico', password 'dico27love');
CREATE USER MAPPING

5. 원격 테이블 생성

접근하려는 원격 DB의 테이블 스키마를 가져온다.

원격 서버에서 테이블을 매핑할 테이블을 생성한다.

  • 방법1 : 특정 테이블 사용

CREATE FOREIGN TABLE {1} (

{5}

)

SERVER {2}

OPTIONS (schema_name '{3}, table_name {4}');

Plain Text

1

2

3

4

5

1: 사용할 테이블명
2: 임의 설정한 서버명
3: 원격 DB Schema
4: 원격 DB 테이블명
5: 원격 DB 테이블 스키마

1

2

3

4

5

6

# 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};

Plain Text

1

2

3

1: 스키마명
2: 임의 설정한 서버명
3: 스키마명 (테이블을 그룹화하기 위한 namespace => 외부 테이블을 스키마로 구별하여 이름 충돌 없음)

1

2

3

4

# CREATE SCHEMA app;
CREATE SCHEMA
# IMPORT FOREIGN SCHEMA public FROM SERVER main_server INTO app;
IMPORT FOREIGN SCHEMA

6. 사용

SELECT * FROM {1}.{2};

Plain Text

1

2

1: 스키마명
2: 원격 DB 테이블명

1

# SELECT * FROM app.members;

7. FDW Options 설정

updatable : 원격 테이블을 수정할 수 있도록 허용할지 여부 (IDM) (기본값 true)

1

2

# alter server main_server options (updatable 'true');
ALTER SERVER

8. Server 설정 확인

1

2

3

4

5

6

7

# \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 변경

1

# alter foreign table app.members add column memo text;

참고

PostgreSQL 공식 문서

Kibae Shin - PostgreSQL 작업기 #2

Amazon RDS의 PostgreSQL - PostgreSQL을 위한 일반 DBA 작업

thoughtbot - PostgreSQL's Foreign Data Wrapper

민갤

Back-End Developer

백엔드 개발자입니다.

Git Hub글 보기

[여행] 2019.07.15 일본 여행

3박 4일간 일본에 다녀온 이야기입니다.

Review

PostgreSQL pgBackRest

pgbackrest 설치 및 사용

Server

v5.0