상황 : EDB(운영 DB)에서 PostgreSQL 로 이관 요청

절차	방법	비고
DDL 추출	pg_dumpall	schema only
데이터이관	postgres_fdw	-

---

DDL 추출 (pg_dumpall)

샘플 데이터는 EDB에서 제공하는 샘플 데이터를 활용합니다.
참고로 아래와 같은 경로에 샘플 데이터 (.sql) 파일이 존재합니다.

/usr/edb/as14/share/edb-sample.sql

'sample' 이라는 데이터베이스를 생성하여 해당 데이터베이스 아래 데이터를 생성합니다.

$ psql -d edb -U enterprisedb
$ create database sample
$ \c sample 
sample## \i /usr/edb/as14/share/edb-sample.sql

$ pg_dumpall -s  > sample_bak.sql

postgres_fdw 튜토리얼

데이터 이관은 'postgres_fdw' extension을 사용하여 진행합니다. 실제 데이터 이관에 앞서 postgres_fdw 사용법에 대해 간단히 정리합니다.

1. extension 추가

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

2. 외부 서버 정의

CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '외부_데이터베이스_IP', port '포트', dbname '외부_데이터베이스_이름');

cf) 포트 입력을 생략할 경우 현재 DBMS의 port 번호와 동일한 포트로 자동 지정합니다.

3. 사용자 매핑 생성

CREATE USER MAPPING FOR 현재_사용자
SERVER foreign_server
OPTIONS (user '외부_데이터베이스_사용자', password '외부_데이터베이스_비밀번호');

외부_데이터베이스_사용자는 가능하면 초기 설정된 superuser 로 연결합니다.

4. 외부 테이블 정의

CREATE FOREIGN TABLE 외부_테이블
(
  컬럼1 데이터_타입,
  컬럼2 데이터_타입,
  -- 필요한 만큼 컬럼을 정의
)
SERVER foreign_server
OPTIONS (schema_name '외부_스키마', table_name '외부_테이블');

5. 쿼리 수행

SELECT * FROM 외부_테이블;

---

데이터 이관

아래의 과정은 postgresql에서 진행합니다.
postgresql에서 'target' 데이터베이스를 'agens'라는 슈퍼유저로 생성한 후 동일한 유저로 'target' 데이터베이스에서 진행합니다.

target=# CREATE EXTENSION IF NOT EXISTS postgres_fdw;

target=# CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5444', dbname 'sample');

User mapping을 진행합니다.
postgresql 유저는 us_source, EDB 유저는 기본 슈퍼유저인 enterprisedb로 매핑했습니다.

target=# CREATE USER MAPPING FOR us_source
SERVER foreign_server
OPTIONS (user 'enterprisedb', password 'edb');

EDB sample database 내 public 스키마에 위치한 테이블 세 개와 동일하게 postgresql에서 foreign table을 생성합니다 (EDB의 'sample' 데이터베이스 내의 dept, emp, jobhist 테이블과 동일한 구조로 postgresql 에서 s_dept, s_emp, s_jobhist 라는 foreign table을 생성합니다).

target=# CREATE foreign TABLE public.s_dept (
    deptno numeric(2,0) NOT NULL,
    dname character varying(14),
    loc character varying(13)
)
server foreign_server
options (schema_name 'public', table_name 'dept');


target=# CREATE foreign TABLE public.s_emp (
    empno numeric(4,0) NOT NULL,
    ename character varying(10),
    job character varying(9),
    mgr numeric(4,0),
    hiredate timestamp without time zone,
    sal numeric(7,2),
    comm numeric(7,2),
    deptno numeric(2,0),
    CONSTRAINT emp_sal_ck CHECK ((sal > (0)::numeric))
)
server foreign_server
options (schema_name 'public', table_name 'emp');



target## CREATE foreign TABLE public.s_jobhist (
    empno numeric(4,0) NOT NULL,
    startdate timestamp without time zone NOT NULL,
    enddate timestamp without time zone,
    job character varying(9),
    sal numeric(7,2),
    comm numeric(7,2),
    deptno numeric(2,0),
    chgdesc character varying(80),
    CONSTRAINT jobhist_date_chk CHECK ((startdate <= enddate))
)
server foreign_server
options (schema_name 'public', table_name 'jobhist');

이제껏 agens 라는 슈퍼유저에서 진행했기 때문에 매핑에 입력한 'us_source'라는 유저는 해당 DB의 foreign table을 조회할 권한이 없습니다. 따라서 권한을 부여해줍니다.

target=# grant all on s_emp to us_source ;
target=# grant all on s_dept to us_source ;
target=# grant all on s_jobhist to us_source ;

target DB에 us_source 유저로 접속합니다.

target=# target us_source

foreign table을 조회합니다.

target=> select * from s_emp ;
 empno | ename  |    job    | mgr  |      hiredate       |   sal   |  comm   | deptno
-------+--------+-----------+------+---------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |         |     10
(14 rows)


target=> select * from s_dept ;
 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)


target=> select * from s_jobhist ;
 empno |      startdate      |       enddate       |    job    |   sal   |  comm   | deptno |       chgdesc
-------+---------------------+---------------------+-----------+---------+---------+--------+---------------------
  7369 | 1980-12-17 00:00:00 |                     | CLERK     |  800.00 |         |     20 | New Hire
  7499 | 1981-02-20 00:00:00 |                     | SALESMAN  | 1600.00 |  300.00 |     30 | New Hire
  7521 | 1981-02-22 00:00:00 |                     | SALESMAN  | 1250.00 |  500.00 |     30 | New Hire
  7566 | 1981-04-02 00:00:00 |                     | MANAGER   | 2975.00 |         |     20 | New Hire
  7654 | 1981-09-28 00:00:00 |                     | SALESMAN  | 1250.00 | 1400.00 |     30 | New Hire
  7698 | 1981-05-01 00:00:00 |                     | MANAGER   | 2850.00 |         |     30 | New Hire
  7782 | 1981-06-09 00:00:00 |                     | MANAGER   | 2450.00 |         |     10 | New Hire
  7788 | 1987-04-19 00:00:00 | 1988-04-12 00:00:00 | CLERK     | 1000.00 |         |     20 | New Hire
  7788 | 1988-04-13 00:00:00 | 1989-05-04 00:00:00 | CLERK     | 1040.00 |         |     20 | Raise
  7788 | 1990-05-05 00:00:00 |                     | ANALYST   | 3000.00 |         |     20 | Promoted to Analyst
  7839 | 1981-11-17 00:00:00 |                     | PRESIDENT | 5000.00 |         |     10 | New Hire
  7844 | 1981-09-08 00:00:00 |                     | SALESMAN  | 1500.00 |    0.00 |     30 | New Hire
  7876 | 1987-05-23 00:00:00 |                     | CLERK     | 1100.00 |         |     20 | New Hire
  7900 | 1981-12-03 00:00:00 | 1983-01-14 00:00:00 | CLERK     |  950.00 |         |     10 | New Hire
  7900 | 1983-01-15 00:00:00 |                     | CLERK     |  950.00 |         |     30 | Changed to Dept 30
  7902 | 1981-12-03 00:00:00 |                     | ANALYST   | 3000.00 |         |     20 | New Hire
  7934 | 1982-01-23 00:00:00 |                     | CLERK     | 1300.00 |         |     10 | New Hire
(17 rows)

조회를 성공적으로 마쳤습니다.

이는 postgres_fdw 를 사용하여 테이블을 조회한 것 뿐, 마이그레이션을 진행한 것은 아닙니다.
저는 아래와 같은 방법으로 마이그레이션을 진행했습니다.

target=> CREATE TABLE emp 
AS SELECT * FROM s_emp;
SELECT 14


target=> CREATE TABLE dept 
AS SELECT * FROM s_dept;
SELECT 4


target=> CREATE TABLE jobhist
AS SELECT * FROM s_jobhist;
SELECT 17

다음과 같이 일반 테이블이 생성됨을 확인할 수 있습니다.

target=> \dt
          List of relations
 Schema |  Name   | Type  |   Owner
--------+---------+-------+-----------
 public | dept    | table | us_source
 public | emp     | table | us_source
 public | jobhist | table | us_source
(3 rows)


target=> select * from emp ;
 empno | ename  |    job    | mgr  |      hiredate       |   sal   |  comm   | deptno
-------+--------+-----------+------+---------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |         |     10
(14 rows)

target=> select * from dept;
 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

target=> select * from jobhist;
 empno |      startdate      |       enddate       |    job    |   sal   |  comm   | deptno |       chgdesc
-------+---------------------+---------------------+-----------+---------+---------+--------+---------------------
  7369 | 1980-12-17 00:00:00 |                     | CLERK     |  800.00 |         |     20 | New Hire
  7499 | 1981-02-20 00:00:00 |                     | SALESMAN  | 1600.00 |  300.00 |     30 | New Hire
  7521 | 1981-02-22 00:00:00 |                     | SALESMAN  | 1250.00 |  500.00 |     30 | New Hire
  7566 | 1981-04-02 00:00:00 |                     | MANAGER   | 2975.00 |         |     20 | New Hire
  7654 | 1981-09-28 00:00:00 |                     | SALESMAN  | 1250.00 | 1400.00 |     30 | New Hire
  7698 | 1981-05-01 00:00:00 |                     | MANAGER   | 2850.00 |         |     30 | New Hire
  7782 | 1981-06-09 00:00:00 |                     | MANAGER   | 2450.00 |         |     10 | New Hire
  7788 | 1987-04-19 00:00:00 | 1988-04-12 00:00:00 | CLERK     | 1000.00 |         |     20 | New Hire
  7788 | 1988-04-13 00:00:00 | 1989-05-04 00:00:00 | CLERK     | 1040.00 |         |     20 | Raise
  7788 | 1990-05-05 00:00:00 |                     | ANALYST   | 3000.00 |         |     20 | Promoted to Analyst
  7839 | 1981-11-17 00:00:00 |                     | PRESIDENT | 5000.00 |         |     10 | New Hire
  7844 | 1981-09-08 00:00:00 |                     | SALESMAN  | 1500.00 |    0.00 |     30 | New Hire
  7876 | 1987-05-23 00:00:00 |                     | CLERK     | 1100.00 |         |     20 | New Hire
  7900 | 1981-12-03 00:00:00 | 1983-01-14 00:00:00 | CLERK     |  950.00 |         |     10 | New Hire
  7900 | 1983-01-15 00:00:00 |                     | CLERK     |  950.00 |         |     30 | Changed to Dept 30
  7902 | 1981-12-03 00:00:00 |                     | ANALYST   | 3000.00 |         |     20 | New Hire
  7934 | 1982-01-23 00:00:00 |                     | CLERK     | 1300.00 |         |     10 | New Hire
(17 rows)