🐘[PostgreSQL] DB Link(DB접근)

다른 데이터베이스에 연결해서 쿼리를 실행해야 할 때 PostgreSQL에서는 dblink 기능을 사용할 수 있습니다.

이 글에서는 PostgreSQL에서 DBLink을 설정하고 사용하는 방법을 정리해보겠습니다.

---

✅ DB Link란?

: DB link는 PostgreSQL의 외부 확장 모듈로,
다른 PostgreSQL 서버에 접속하여 SQL 쿼리를 실행할 수 있도록 도와주는 기능입니다.

• 현재 세션 내에서 물리적으로 다른 곳에 위치한 데이터베이스 연결을 지원하는 확장성 모듈
• 서로 다른 서버에 위치한 데이터베이스 간에 데이터를 조회하거나 전송할 수 있음
• Oracle의 데이터베이스 링크와 유사한 기능을 제공

📌 즉, 한 서버에서 다른 PostgreSQL DB의 데이터를 직접 조회하거나 조작할 수 있게 해줘요!

 

 

※ DB Link를 생성하기 위해서는 먼저 확장 모듈을 설치 해야함

※ 스키마 지정이 없으면 default로 public에 설치가 되므로 스키마 별로 사용이 필요한 경우, alter로 변경하거나 삭제 후 재설치하여 사용

 

---

📦 1. DB link 설치

• DB link 설치

postgres=# create extension dblink;

  ※ CREATE EXTENSION [IF NOT EXISTS] EXTENSION_NAME
        [WITH] [SCHEMA schema_name]
                  [VERSION version]
                  [FROM old_version]

※ Superuser가 아니면 create extension 명령이 동작하지 않음

 

• 권한이 없다면 임시로 superuser로 만든 후 다시 원복

postgres=# alter user {USERNAME} superuser;     # DB link 설치하려는 유저에게 슈퍼유저 권한주기
postgres=# create extension dblink;             # DB link 설치      
postgres=# alter user {USERNAME} nosuperuser;   # 슈퍼유저 권한 회수

 

 

* DB Link 설치 시 아래와 같이 오류가 발생

# 오류 발생 시
postgres=# create extension dblink;  

ERROR:  extension "dblink" is not available
DETAIL:  Could not open extension control file "/usr/pgsql-15/share/extension/dblink.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.

# 해결방안
$> yum install postgresql15-contrib

 

 

---

 

📥 2. DB Link 등록

postgres=# select dblink_connect(
   '{DBLINK이름}',
   'host = {원격DB_주소}
    user = {원격DB_유저}
    password = {원격DB_패스워드}
    dbname = {원격DB_이름}
    port = {원격DB_포트}'
);

 

예시)

 postgres=# select dblink_connect(
		'dblink-test',
		'host=3.87.207.213
		user=postgres
		password=postgres
		dbname=postgres
		port=5432'
		);

 

 

* DB Link 등록 시 아래와 같이 오류가 발생

postgres=# select dblink_connect('dblink-test','host=3.87.207.213 user=postgres password=postgres dbname=postgres port=5432');
ERROR:  could not establish connection
DETAIL:  connection to server at "3.87.207.213", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

# 문제 : dblink_connect 함수를 사용하여 원격 데이터베이스에 연결하려고 할 때,

          서버가 해당 호스트에서 실행 중이지 않거나 TCP/IP 연결을 수락하지 않아서 발생하는 오류입니다.

 

# 해결방안 : pg_hba.conf 파일에서 아래와 같이 'host all all 0.0.0.0/0 md5' 추가

# 해결방안

# 1. pg_hba.conf 파일 설정
$> cd /var/lib/pgsql/15/data
$> vi pg_hba.conf
[pg_hba.conf]
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               md5               # 추가

 

그 외 아래 정보들 확인하고 조치

1. 서버 실행 여부 확인
2. 포트 개방 여부 확인
3. DBLink 정보 확인
4. 네트워크 설정 확인

( AWS EC2를 이용하는 저는 인바운드 설정을 통해 접근 해결)

 

---

🔌 3. DBLink 사용하기

• 연결된 DB 사용하기

# DBLink 쿼리 문법
select * from dblink (
	'{DBLink이름}',
	'{사용할 쿼리}'
) as {임시테이블이름} (
	{임시 컬럼이름} {임시 컬럼타입}
);

 

ex)

# 예시1
 select * from dblink(
	'dblink-test1',
	'select * from tb01'
) as test_table(
	a varchar(10)
);

# 예시2
postgres=# select *from dblink('dblink-test1','select * from tb01') as test_table(a varchar(10));

a 
---
 1
 2
 3
(3 rows)

 

---

 

⚠️ 주의사항

• 🔐 보안: 비밀번호를 쿼리에 직접 쓰기 때문에 보안에 유의해야 합니다.
  ⇒ 가능하면 pg_service.conf 또는 postgresql_fdw 사용 추천.
• ⚙️ 성능: dblink는 일종의 임시 연결이므로 빈번한 호출 시 비효율적일 수 있어요.
• 💥 트랜잭션 처리: 서로 다른 DB 간 트랜잭션은 원자성 보장 불가.
  ⇒ 트랜잭션 분리해서 처리 필요.

---

🎯 참고용 명령 모음

-- 확장 설치
CREATE EXTENSION dblink;

-- 데이터 조회
SELECT * 
FROM dblink('host=... dbname=... user=... password=...', 'SELECT * FROM table') 
AS t(col1 TYPE, col2 TYPE);

-- 데이터 조작
SELECT dblink_exec('host=... dbname=... user=... password=...', 
                   'INSERT INTO table (col) VALUES (''data'')');