PostgreSQL 15 → 18 pg_dump 기반 마이그레이션 무사고 절차서

오늘 미루어 놓았던 PostgreSQL 15에서 18로 업그레이드를 했다. 그러면서 좌충우돌이 많았다. 늘 하는 작업이 아니라 문서로 정리하였다.

PostgreSQL 15 → 18 pg_dump 기반 마이그레이션 무사고 절차서 (가상 DB 기준)

목표

pg_dump / pg_restore 방식으로 PostgreSQL 15 → 18 업그레이드

tablespace 분리 구조 유지

role / 권한 / extension / 검색(pg_trgm·pgroonga)까지 완전 복구

실제 발생한 실수와 오류를 사전에 차단

0. 전체 전략 요약 (중요)

OS: Debian 12 (bookworm)
방식: pg_upgrade ❌ / pg_dump + pg_restore ✅
이유:
- major version + extension(pgroonga) 구조 변경
- tablespace 다수 사용
- 롤/권한/검색 구조를 명시적으로 재구성 필요

1. 사전 점검 (절대 생략 금지)

1-1. 기존 PostgreSQL 15 상태 확인

bash

pg_lsclusters

확인 포인트:

버전 (15)
cluster 이름 (main)
port (보통 5432)
data dir

1-2. 기존 DB / encoding / locale 확인

sql

SELECT datname FROM pg_database WHERE datistemplate = false;
SHOW server_encoding;
SHOW lc_collate;
SHOW lc_ctype;

➡️ 18 클러스터 생성 시 반드시 동일하게 맞춰야 함

1-3. tablespace 전체 목록 + 실제 경로 백업

sql

SELECT
  oid,
  spcname AS tablespace_name,
  pg_tablespace_location(oid) AS physical_path
FROM pg_tablespace
ORDER BY spcname;

⚠️ 이 결과는 반드시 저장 (나중에 18용 CREATE TABLESPACE 생성 기준)

1-4. role 전체 백업 (중요)

bash

pg_dumpall --globals-only > roles.sql

포함 내용:

ROLE
PASSWORD
DEFAULT PRIVILEGES
TABLESPACE GRANT (있을 수도 있음)

2. DB별 pg_dump (실수 방지 버전)

2-1. dump 디렉토리 생성

bash

mkdir -p ~/pg15_dump
chmod 700 ~/pg15_dump

2-2. DB별 dump (CUSTOM format 권장)

bash

# 가상 DB(example_db) 기준 예시

```bash
pg_dump \
  -Fc \
  -f ~/pg15_dump/example_db_$(date +%Y%m%d_%H%M%S).dump \
  example_db


✔ DB별로 분리해야 restore/재시도/병렬 처리 가능

---

## 3. PostgreSQL 18 설치 & 클러스터 생성

### 3-1. PostgreSQL 18 설치

```bash
sudo apt install postgresql-18 postgresql-client-18

⚠️ 설치만으로 cluster는 생성되지 않음 (중요 실수 포인트)

3-2. PostgreSQL 18 클러스터 생성

bash

sudo pg_createcluster 18 main \
  --encoding=UTF8 \
  --locale=ko_KR.UTF-8

확인:

bash

pg_lsclusters

➡️ 기본적으로 5433 / down 상태

4. tablespace 재구성 (가장 많이 실수한 구간)

4-1. 경로 정책 (변경된 부분)

기존	신규
/var/lib/postgresql/data	/var/lib/postgresql/18/data
/var/lib/postgresql/index	/var/lib/postgresql/18/index

bash

# PostgreSQL 18용 데이터 디렉터리와 테이블스페이스 루트 디렉터리를 명시적으로 생성
# 중괄호({data,index})는 bash 확장 문법이므로, 가독성을 위해 풀어서 작성
mkdir -p /var/lib/postgresql/18/data
mkdir -p /var/lib/postgresql/18/index

chown -R postgres:postgres /var/lib/postgresql/18
chmod 700 /var/lib/postgresql/18/data
chmod 700 /var/lib/postgresql/18/index

4-2. tablespace CREATE (postgres로 실행)

sql

CREATE TABLESPACE example_data
  LOCATION '/var/lib/postgresql/18/data/example';

CREATE TABLESPACE example_index
  LOCATION '/var/lib/postgresql/18/index/example';
-- 나머지 동일 패턴

⚠️ IF NOT EXISTS 없음 → 중복 생성 주의

4-3. tablespace 권한 (실제 놓친 부분)

DB owner(*_own)는 tablespace에 CREATE 권한이 필요함

sql

GRANT CREATE ON TABLESPACE example_data  TO example_own;
GRANT CREATE ON TABLESPACE example_index TO example_own;
-- 모든 DB 동일하게 반복

❌ 이 단계 누락 시:

restore 중 index 생성 실패
pgroonga index 실패
"relation already exists" 연쇄 오류

5. role 복원 (순서 중요)

bash

psql -f roles.sql

주의:

tablespace가 이미 있으면 "이미 있음" 오류 발생 → 무시 가능
role 누락 시 restore 중 role does not exist 오류 발생

6. 서버 기동/중지 절차 (매우 중요 – 실제 누락되었던 부분)

원칙

동시에 두 메이저 버전이 같은 데이터/포트를 사용하면 안 됨

dump 시점 / restore 시점 / 검증 시점마다 서버 상태가 명확해야 함

6-1. pg_dump 전 서버 상태 (운영 중)

PostgreSQL 15: RUNNING
PostgreSQL 18: 아직 없음 또는 DOWN

bash

pg_lsclusters

6-2. PostgreSQL 15 완전 중지 (restore 직전 필수)

bash

sudo systemctl stop postgresql@15-main

확인:

bash

pg_lsclusters

→ 15/main 이 반드시 down 상태여야 함

❌ 이 단계 누락 시 발생한 실제 문제:

애플리케이션이 여전히 15에 접속
restore 중 데이터 불일치
커넥션 폭증 및 락 혼선

6-3. systemd 데몬 reload (누락되기 쉬운 핵심 단계)

pg_createcluster 또는 설정 파일 직접 수정 후에는 반드시 systemd 데몬 reload 필요

bash

sudo systemctl daemon-reload

확인:

bash

systemctl list-unit-files | grep postgresql

❌ 이 단계 누락 시 실제 발생 가능 문제:

새로 생성한 postgresql@18-main 서비스가 인식되지 않음
systemctl start postgresql@18-main 이 무시되거나 실패
포트 변경이 반영되지 않은 것처럼 보임

6-4. PostgreSQL 18 기동

bash

sudo systemctl start postgresql@18-main

상태 확인:

bash

pg_lsclusters

6-5. 포트 전환 (15 → 18)

bash

sudo systemctl daemon-reload

bash

sudo systemctl start postgresql@18-main

6-4. 포트 전환 (15 → 18)

PostgreSQL 18 설정 변경:

bash

sudo sed -i 's/^port =.*/port = 5432/' /etc/postgresql/18/main/postgresql.conf

bash

sudo systemctl restart postgresql@18-main

확인:

bash

ss -lntp | grep 5432

→ postgres(18) 만 떠 있어야 정상

6-5. PostgreSQL 15 재기동 금지

text

⚠️ 업그레이드 완료 전까지 postgresql@15-main 재기동 금지

7. Extension 설치 (특히 pgroonga)

bash

sudo systemctl stop postgresql@15-main

PostgreSQL 18 포트 변경:

bash

sudo sed -i 's/^port =.*/port = 5432/' /etc/postgresql/18/main/postgresql.conf
sudo systemctl restart postgresql@18-main

7. Extension 설치 (특히 pgroonga)

7-1. 설치 여부 확인

sql

SELECT * FROM pg_extension;

7-2. DB별 extension 생성 (필수)

sql

CREATE EXTENSION IF NOT EXISTS pgroonga SCHEMA public;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

⚠️ extension은 DB 단위 → 모든 DB에서 실행해야 함

8. pg_restore (가장 안전한 방식)

8-1. DB는 먼저 빈 상태로 생성

sql

CREATE DATABASE example OWNER example_own;

8-2. restore 실행

bash

pg_restore \
  --dbname=example \
  --no-owner \
  --role=example_own \
  ~/pg15_dump/example_20260121_043028.dump

권장 옵션:

--no-owner : role 충돌 방지
--role : object 소유권 통제

9. pgroonga 관련 실수와 해결

9-1. 문제

pgroonga_score(oid, tid) 함수 없음
검색 결과 0건
index가 없음

9-2. 원인

PG15 시절 SQL / VIEW / FUNCTION이 구버전 pgroonga API 사용
PG18 + 최신 pgroonga는 score 시그니처 변경

9-3. 해결 원칙

pgroonga_score(tableoid, ctid) ❌
pgroonga_score(indexed_column) ✅

필요 시:

VIEW / FUNCTION DROP & 재작성
pgroonga index 재생성

10. 최종 검증 체크리스트

10-1. 접속자 확인

sql

SELECT usename, COUNT(*) FROM pg_stat_activity GROUP BY usename;

10-2. tablespace 사용 확인

sql

SELECT relname, spcname
FROM pg_class c
JOIN pg_tablespace t ON c.reltablespace = t.oid
WHERE spcname NOT IN ('pg_default','pg_global');

10-3. 검색 검증

sql

EXPLAIN ANALYZE
SELECT * FROM examples_full_text
WHERE search_text &@~ 'postgres';

11. 결론 (이 문서의 핵심 메시지)

pg_dump 방식은 안전하지만 준비가 전부다
tablespace 권한 / extension / role 순서 하나라도 틀리면 연쇄 오류 발생
특히 pgroonga는 버전 간 API 변화가 크므로 SQL 검증 필수

➡️ 이 절차 그대로 따르면 재현 가능 + 무사고 업그레이드 가능

PostgreSQL 15 → 18 pg_dump 기반 마이그레이션 무사고 절차서

중년개발자