PostgreSQL의 dblink로 원격 Database 사용하기

오픈소스 데이터베이스인 PostgreSQL의 dblink사용법에 대해 정리한다.

 

PostgreSQL 소개와 Docker를 사용하여 설정하는 방법은 아래 포스팅을 참고한다.

Docker로 PostgreSQL 사용하기

 

PostgreSQL 공식 Docker 이미지에 한글을 설정한 Docker 이미지 생성 방법은 아래 포스팅을 참고한다.

공식 PostgreSQL Docker 이미지에 한글 적용하기

 

GitHub과 DockerHub 사이트는 다음과 같다.

• DockerHub - https://hub.docker.com/_/postgres
• GitHub - https://github.com/docker-library/postgres

 

PostgreSQL은 다음과 같은 구조를 가지고 있다.

각 기능은 다음과 같다.

• Users/Groups - 사용자 정보 관리
• Databases - 실제 레코드가 저장되는 Database 관리
• Tablespaces - Database의 Object가 저장 된 파일시스템의 경로 관리
• Schemas - PostgreSQL 내부에서 Database를 논리적으로 구분하여 관리
• Tables - Row와 Column으로 구성 된 실제 레코드가 저장되는 공간
• Views - 읽기 전용의 가상 테이블로 제한 된 정보만 제공하기 위한 공간

 

 

dblink 사용하기

로컬의 Database에서 원격의 Database를 연결해서 사용하는 방법이다.

로컬 Database를 다루는 것처럼 쿼리할 수 있어  개발의 편의성과 속도면에서 장점이 있다.

 

그러나 아래와 같은 단점도 존재한다.

• dblink를 사용할 계정은 SUPERUSER 권한이 필요하다.
• 원격 Database의 주소와 계정 정보가 필요하므로 원격 Database의 정보가 노출된다.
• 원격 Database에 과도한 쿼리 등으로 인해 예상치 못한 부하와 장애가 발생할 수 있다.
• 원격 Database에 장애가 발생하면 로컬 Database도 영향을 받는다.
• dblink는 명시적으로 노출되지 않아 유지 보수 단계에서 방치되는 경우가 많다.
• 데이터의 정합성을 맞추기 어렵다. (RDBMS의 데이터는 실시간으로 변할 수 있다.)

 

dblink 연결은 Database에 1개만 설치할 수 있다. 따라서 다른 Schema에서 사용하려면 아래 방법을 사용해야 한다.

• 쿼리 상에 데이터베이스의 Schema를 전부 명시
• 해당 스키마로 extension을 전부 ALTER

 

위의 단점들로 인해 실시간 서비스에서는 장애 예방을 위해 자제하는 것이 권장된다.

데이터 마이그레이션이나 외부 데이터의 주기적 수집 등 관리적 이슈에 제한적으로 사용하는 것이 좋은 것 같다.

 

Database간의 연결은 프로그램 코드에 명시적으로 노출되어 있어야 관리가 가능하기 때문이다.

그래도 사용할 가능성을 완전히 배제할 수 없으므로 사용법을 정리해둔다.

 

추가 Docker 컨테이너 생성

먼저 테스트를 진행할 PostgreSQL 데이터베이스가 필요하다.

JOIN을 테스트했던 컨테이너를 원격 Database로 사용하고, 해당 Database에 dblink를 사용할 신규 컨테이너를 생성한다.

 

아래 명령으로 실행한다.

$ docker run -p 5433 --name sr-link \
    -e POSTGRES_PASSWORD=postgresql -e TZ=Asia/Seoul \
    -v /opt/postgres/linkdata:/var/lib/postgresql/data \
    -d 사설저장소주소:포트/postgres:14.1

 

JOIN을 테스트했던 컨테이너의 IP는 10.0.0.2이고, 새로 생성한 컨테이너의 IP는 10.0.0.3이다.

 

모듈 확인

dblink를 사용하기 위해 해당 컨테이너 내부에 dblink 모듈이 존재하는지 확인한다.

 

PostgreSQL 하위의 extension 폴더에서 dblink 모듈이 있는지 확인하면 된다.

 

PostgreSQL에 dblink 로드

PostgreSQL에 dblink 모듈을 로드한다.

 

CREATE EXTENSION 명령을 사용한다.

postgres=# CREATE EXTENSION dblink;
CREATE EXTENSION
postgres=# \dx
                                List of installed extensions
  Name   | Version |  Schema    |                        Description
---------+---------+------------+--------------------------------------------------------------
dblink   | 1.2     | public     | connect to other PostgreSQL databases from within a database
plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

 

dblink 등록

원격 Database의 정보를 사용하여 로컬 PostgreSQL에 등록한다.

SELECT dblink_connect('연결명',
    'hostaddr=원격DB주소 port=원격DB포트 dbname=사용할DB이름 
    user=사용할DB계정 password=계정비밀번호')

 

아래와 같이 사용하면 된다. 원격 Database의 주소가 10.0.0.2이므로 아래와 같이 입력한다.

postgresql=# SELECT dblink_connect('dblink-test',
    'hostaddr=10.10.0.2 port=5432 dbname=postgres user=postgres password=postgresql');
dblink_connect
----------------
OK
(1 row)

 

참고로 컨테이너 내부에서 IP 주소를 확인하는 방법은 다음과 같다.

$ docker exec -it sr-postgres /bin/bash

$ hostname -i
10.0.0.2

 

쿼리 실행

원격 Database의 데이터를 조회하기 위한 쿼리를 실행한다.

SELECT 사용할이름.컬럼명 # 전체 컬럼 조회는 사용할이름.*
    FROM dblink('연결명', 'SELECT 쿼리')
    AS 사용할이름 (컬럼명 데이터타입 ...);

 

아래와 같이 사용하면 된다. dblink를 등록할때 dblink-test로 지정한 것을 참고한다.

postgres=# SELECT dbtest.*
  FROM dblink('dblink-test', 'SELECT * FROM team')
  AS dbtest (num integer, name varchar(25));
  
num  |      name
-----+----------------
  1  | Human Resource
  2  | Finance
  3  | Infra Service
(3 rows)

 

쿼리에 지정한 컬럼 개수와 AS 구문 이후에 지정한 컬럼 개수가 일치해야 한다.

 

 

접속 제거

dblink는 두 개 이상의 동시 사용이 안되기 때문에, 사용이 끝난 dblink는 연결을 해제해야 한다.

SELECT dblink_disconnect('연결명')

 

아래와 같이 사용하면 된다.

postgres=# SELECT dblink_disconnect('dblink-test');
dblink_disconnect
-------------------
OK
(1 row)