Postgresql DB 백업 및 복구하여 서버 이전하는 방법

백업 서버에서 DB 서버에 원격 접속하여 pg_dump로 백업 파일을 생성하는 경우, pg_dump 클라이언트 버전은 대상 PostgreSQL 서버 버전보다 낮을 수 없다.

 

예를 들어, PostgreSQL 16 버전 DB 서버를 백업 서버에 설치된 pg_dump 14 버전 클라이언트로 백업하는 것은 불가능하다.

이 경우 백업 서버에 PostgreSQL 16 버전 클라이언트(pg_dump) 를 추가로 설치한 뒤, 해당 버전의 pg_dump를 사용하여 백업 파일을 생성하고 복구를 수행해야 한다.

 

DB 백업 파일은 전체 백업과 특정 DB의 백업으로 구분된다.

실무에서는 트러블슈팅이 어렵기 때문에  pg_dumpall으로 데이터, 글로벌 객체 모두 백업하지 않는다. 

Postgresql은 pg_dump와 pg_dumpall을 통해서 백업 가능하며, 증분백업이 불가능하다.

 

백업 생성 방법

우선 백업은 postgres 계정으로 진행한다.

 

1. pg_dumpall을 사용하여 cluster 전체의 글로벌 객체(사용자, 권한, tablespace)를 먼저 백업하는 globals-only 백업 필요하다.

pg_dumpall -U postgres -v --roles-only -f "/backup/role.sql"

 

2. 특정 DB을 백업하는 경우 pg_dump 사용

백업파일 용량이 크면 파일 압축을 해서 gz 파일로 백업을 한다.

pg_dump {DB명} | gzip > /backup/backup-ex.gz

 

백업 복원 방법

우선 백업은 postgres 계정으로 진행한다.

 

1. Role 복구

psql -f /data/backup/pgsql_backup/dev/platform_bizchat_role_root.sql postgres

# 계정 목록 확인(psql에서 확인)
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;

 

2. DB 복구

대상DB만 없다면 대상DB를 생성한 뒤 DB 복구한다.

15GB 데이터 복구 시 20분 정도 소요되며, 14버전의 스키마와 데이터를 16버전으로 복구했을때 특이사항은 없었다.

# DB 생성(psql로 진행)
CREATE DATABASE {DB명} OWNER {DB계정} LC_COLLATE 'C.UTF-8' LC_CTYPE
'C.UTF-8' TEMPLATE template0;

# DB 조회
select * from PG_DATABASE; 

# 백업 복구
gunzip < backup-ex.gz | psql -d {DB명}

 

3. 복구 완료 후 통계정보 갱신

ANALYZE; 

 

 

Troubleshooting

ROLE 복구 시 주의할 점

1)

오류코드: ERROR: role "existing_role" already exists

-> 백업 파일 복구 시 기존 role이 있다면 에러코드가 나오고 복구할 때 role 생성하지 않는다. (기존 role의 권한/속성 자체는 안 바뀜)

but, 아래 ALTER문으로 role의 비밀번호가 바뀔수있다.

ALTER ROLE ex WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'XXXXXXXXXXX';

해결방법:  --no-role-passwords로 globals 다시 뜨거나 OR 수동으로 기존 Role의 비밀번호를 ALTER 문을 없앤다.

 

2)

오류 코드: ERROR: require a VALID UNTIL option

원인: credcheck extension 설정으로 인해 CREATE ROLE 시 에러 발생

해결방법

# 해당 세션에서만 credcheck 비활성화
SET credcheck.password_valid_until = 0;
SET credcheck.password_valid_max = 0;
SELECT pg_reload_conf(); // 재시작 없이 변경사항 저장

# credcheck 확인
SHOW credcheck.password_valid_max;
SHOW credcheck.encrypted_password_allowed;

# 시스템상 영구 적용이기에 추천하지 않는 방법이다.
ALTER SYSTEM SET credcheck.password_valid_until = 0;
ALTER SYSTEM SET credcheck.password_valid_max = 0;
SELECT pg_reload_conf();

# credcheck를 제외 계정 추가
# 보통 시스템 계정이 해당
ALTER SYSTEM SET credcheck.whitelist = '{계정명}';
SELECT pg_reload_conf();