경험은 언제나 옳다

PostgreSQL Backup, pg_rman 사용 방법

OS환경 : Rocky Linux 8.10 (64bit)
DB 환경 : PostgreSQL 17.4

먼저, 아래 경로에서 pg_rman을 Download 받습니다. 

pg_rman Download

https://github.com/ossc-db/pg_rman/releases

저는 Source code(tar.gz) 파일을 받아 설치 진행하였습니다. 

pg_rman Install

$ cd pg_rman-1.3.18
$ make
$ make install

그 다음 BACKUP_PATH를 bash_profile에 지정합니다.

$ vi .bash_profile
export BACKUP_PATH={백업 경로 지정}

그 다음 pg_rman.ini 파일을 생성하기 위해 아래 명령어를 수행 합니다.

-- pg_rman.ini 파일 생성
$ pg_rman init

-- pg_rman.ini 기본설정 

$ cat $BACKUP_PATH/pg_rman.ini
ARCLOG_PATH = {기존아카이브 경로} 
SRVLOG_PATH = {DB Log 경로}

BACKUP_MODE = F
COMPRESS_DATA = YES
KEEP_ARCLOG_FILES = 10
KEEP_DATA_GENERATIONS = 3
KEEP_SRVLOG_FILES = 10

ini 설정 값은 아래 참고

pg_rman Backup

-- Full Backup 수행
$ pg_rman backup --backup-mode=full --with-serverlog --progress


-- Backup 상태 확인
$ pg_rman show detail

 pg_rman show detail
======================================================================================================================
 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status
======================================================================================================================
2025-12-31 01:08:23  2025-12-31 01:09:51  FULL  5572MB    83MB   128kB   626MB        true       4          0  DONE

백업이 완료 되면 Status 값이 DONE으로 확인 됩니다.

여기서 validate명령어를 수행해서 백업 상태에 대한 유효성 검사를 해야됩니다.

$ pg_rman validate
INFO: validate: "2025-12-31 02:06:46" backup, archive log files and server log files by CRC
INFO: backup "2025-12-31 02:06:46" is valid

pg_rman Restore

-- DB 종료
$ pg_ctl stop -m immediate

$ pg_rman restore

$ pg_ctl start

Restore는 DB가 내려간 상황에서 가능합니다.   

PITR은 아래 옵션을 통해 사용 가능합니다.

--recovery-target-timeline TIMELINE
특정 타임라인으로 복구하도록 지정합니다. 지정하지 않으면 ( $PGDATA/global/pg_control)의 현재 타임라인이 사용됩니다.
--recovery-target-time TIMESTAMP
이 매개변수는 복구를 진행할 타임스탬프를 지정합니다. 지정하지 않으면 최신 시간까지 복구를 진행합니다.
--recovery-target-xid XID
이 매개변수는 복구를 진행할 트랜잭션 ID를 지정합니다. 지정하지 않으면 최신 트랜잭션 ID까지 복구를 진행합니다.
--recovery-target-inclusive
지정된 복구 대상 바로 직후(true)에 중지할지, 아니면 복구 대상 바로 직전(false)에 중지할지 지정합니다. 기본값은 true입니다.
--recovery-target-action {{ pause | promote | shutdown }}
복구 목표에 도달했을 때 서버가 수행할 작업을 지정합니다. 기본값은 일시 중지(pause)이며, 이는 복구 프로세스가 일시 중지됨을 의미합니다. 
승격(promote)은 복구 프로세스가 완료되고 서버가 연결을 수락하기 시작함을 의미합니다. 마지막으로 종료(shutdown)는 복구 목표에 도달한 후 서버를 중지합니다. 이 옵션은 1.3.12 버전 이상에서 제공됩니다.

🙌 댓글, 공감, 공유는 큰 힘이 됩니다! 😄

참조 :

https://github.com/ossc-db/pg_rman

https://ossc-db.github.io/pg_rman/index.html

https://coiy.github.io/postgresql/pg_rman_introduction/#

https://m.blog.naver.com/geartec82/222057147238

https://bboks.net/459

DML에 의해 조각 난 공간 회수하는 방법, pg_repack 

OS환경 : Rocky Linux 8.10 (64bit)
DB 환경 : PostgreSQL 17.4

pg_repack 이란?

pg_repack은 테이블/인덱스를 "온라인으로 재작성"해서 bloat(팽창)를 제거하고 디스크 공간을OS에 반환하는 툴

VACUUM은 내부 재사용만 가능하여 실질적인 OS공간 반환을 하지 않음, 그래서 테이블이 한번 커지게 되면 DELETE로 데이터를 지워도 디스크는 계속 차지함.

pg_repack 동작 원리⭐

1. 원본테이블 트리거 설치(DML 변경사항 추적)

2. 새 임시테이블 생성

3. 원본 테이블을 새 테이블로 복사

4. 그 사이 변경된 내용은 트리거로 동기화

5. 짧은 Lock(ACCESS EXCLUSIVE LOCK)으로 테이블 이름 swap 진행

6. 원본 테이블 제거 하여 디스크 공간 반환

장점 : VACUUM FULL과 달리 업무 중 사용 가능함, 온라인 작업가능, 디스크 공간 반환

단점 : 해당 테이블 용량 만큼 여유 공간 필요

추가로 pg_reorg도 존재하지만, PostgreSQL 8~9버전에서 사용되었고, 현재 공식적으로 유지보수 중단됨

pg_repack보다 lock을 길게 잡고, 장애 사례도 많고, 최신 PostgreSQL 버전과 호환이 좋지 않음

1GB이상 bloat 테이블 조회 쿼리

-- 1GB 이상 bloat 테이블 확인 
WITH tbl AS (
  SELECT
    c.oid,
    n.nspname AS schemaname,
    c.relname,
    c.reltuples::numeric AS reltuples,
    c.relpages::numeric  AS relpages,
    current_setting('block_size')::numeric AS bs
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind = 'r'
    AND n.nspname NOT IN ('pg_catalog','information_schema')
),
avgw AS (
  SELECT
    schemaname,
    tablename,
    sum((1 - null_frac) * avg_width)::numeric AS data_width
  FROM pg_stats
  GROUP BY 1,2
),
calc AS (
  SELECT
    t.*,
    COALESCE(a.data_width, 0) AS data_width,
    (t.relpages * t.bs) AS actual_bytes,
    (CEIL( (t.reltuples * (COALESCE(a.data_width, 0) + 24)) / NULLIF((t.bs - 24), 0) ) * t.bs) AS est_needed_bytes
  FROM tbl t
  LEFT JOIN avgw a
    ON a.schemaname = t.schemaname
   AND a.tablename  = t.relname
)
SELECT
  schemaname,
  relname,
  pg_size_pretty(actual_bytes::bigint) AS actual_heap,
  pg_size_pretty(est_needed_bytes::bigint) AS estimated_needed,
  pg_size_pretty(GREATEST(actual_bytes - est_needed_bytes, 0)::bigint) AS est_bloat,
  round(
    100.0 * GREATEST(actual_bytes - est_needed_bytes, 0) / NULLIF(actual_bytes, 0),
    2
  ) AS est_bloat_pct
FROM calc
WHERE actual_bytes > 1024*1024*1024
ORDER BY est_bloat_pct DESC, actual_bytes DESC
LIMIT 50;

pg_repack 다운로드 및 가이드

https://github.com/reorg/pg_repack

pg_repack 실습

스키마, 테이블 생성

CREATE SCHEMA IF NOT EXISTS lab;
DROP TABLE IF EXISTS lab.bloat_test;
CREATE TABLE lab.bloat_test (
  id        bigserial PRIMARY KEY,
  k         int NOT NULL,
  payload   text NOT NULL,
  pad       text NOT NULL,
  updated_at timestamptz DEFAULT now()
);

autovacuum 설정 변경 (autovacuum 주기를 길게 하여 테스트 진행)

ALTER TABLE lab.bloat_test SET (
  autovacuum_enabled = on,
  autovacuum_vacuum_scale_factor = 0.50,
  autovacuum_analyze_scale_factor = 0.50,
  autovacuum_vacuum_threshold = 500000,
  autovacuum_analyze_threshold = 500000
);

인덱스 bloat도 진행

CREATE INDEX bloat_test_k_idx ON lab.bloat_test (k);

200만건 데이터 적재 및 통계정보 수집 

--200만건 데이터 적재
INSERT INTO lab.bloat_test (k, payload, pad)
SELECT
  (random() * 1000000)::int,
  repeat(md5(random()::text), 20),
  repeat('X', 400)
FROM generate_series(1, 2000000);


-- 통계정보 수집
ANALYZE lab.bloat_test;

현재 사이즈 조회

SELECT
  pg_size_pretty(pg_table_size('lab.bloat_test')) AS heap,
  pg_size_pretty(pg_indexes_size('lab.bloat_test')) AS idx,
  pg_size_pretty(pg_total_relation_size('lab.bloat_test')) AS total;

bloat 만들기

-- 1차 대량 UPDATE
UPDATE lab.bloat_test
SET payload = repeat(md5(random()::text), 25),
    updated_at = now();


-- 2차 UPDATE (또 한번)
UPDATE lab.bloat_test
SET payload = repeat(md5(random()::text), 30),
    updated_at = now();
    
-- 통계정보 수집
ANALYZE lab.bloat_test;


-- 데이터 절반 가량 삭제
DELETE FROM lab.bloat_test
WHERE k % 2 = 0;
ANALYZE lab.bloat_test;

현 상태 조회

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
  pg_size_pretty(pg_table_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  last_autovacuum,
  last_vacuum
FROM pg_stat_user_tables
WHERE schemaname = 'lab' AND relname = 'bloat_test';

pg_repack 설치

$ cd pg_repack
$ make
$ sudo make install


-- 해당DB에 pg_repack extension 설치
$ psql -c "CREATE EXTENSION pg_repack" -d test

pg_repack 수행

-- 테이블 수행
pg_repack -d test -t lab.bloat_test --jobs=1


-- 인덱스 수행 
pg_repack -d test -i lab.bloat_test_k_idx --jobs=1

최종 결과😮

-- VACUUM 전 
 schemaname |  relname   | actual_heap | estimated_needed | est_bloat | est_bloat_pct
------------+------------+-------------+------------------+-----------+---------------
 lab        | bloat_test | 6131 MB     | 1185 MB          | 4946 MB   |         80.67
(1 row)

-- VACUUM 후 
 schemaname |  relname   | actual_heap | estimated_needed | est_bloat | est_bloat_pct
------------+------------+-------------+------------------+-----------+---------------
 lab        | bloat_test | 4836 MB     | 1202 MB          | 3634 MB   |         75.15
(1 row)


--pg_repack 후
 schemaname |  relname   | actual_heap | estimated_needed | est_bloat | est_bloat_pct
------------+------------+-------------+------------------+-----------+---------------
 lab        | bloat_test | 1302 MB     | 1198 MB          | 105 MB    |          8.03
(1 row)

끝.. 재밌었다.

🙌 댓글, 공감, 공유는 큰 힘이 됩니다! 😄

참조 : https://github.com/reorg/pg_repack

Idle in Transaction 개념 · 원인 · 문제점 · 해결방법

OS환경 : Rocky Linux 8.10 (64bit)
DB 환경 : PostgreSQL 17.4

Idle in Transaction이란?

PostgreSQL에서 클라이언트의 세션이 트랜잭션을 시작했지만, 아무 동작 없이 멈춰있는 상태를 말합니다.

BEGIN;   -- 트랜잭션 시작
-- 아무 작업 안 함
-- 커밋/롤백 없이 방치됨

BEGIN 유지되면 PostgreSQL은 트랜잭션을 끝났다고 판단할 수 없어서 여러 리소스를 계속 붙잡고 있게 됩니다.

Idle in Transaction이 왜 문제가 될까? (⭐중요)

문제 1: VACUUM 동작 방해

PostgreSQL은 MVCC 구조 때문에 Row Version 관리와 Vacuum이 중요한데
idle in transaction 세션은 해당 세션보다 이후에 업데이트된 튜플을 vacuum이 회수하지 못하게 만듭니다.

➡️ 결과적으로 테이블 bloat, 디스크 낭비, I/O 증가를 야기

문제 2: LOCK 점유

대부분의 ORM 또는 애플리케이션 코드는 쿼리 실행 후 세션 연결만 유지되면 다음과 같은 락이 풀리지 않습니다.

- Row-level locks

- Share locks
- AccessExclusive lock
➡️ 결국 다른 트랜잭션이 대기하거나 DeadLock 발생

문제 3: Connection Pool 자원 고갈

트랜잭션이 열린 채로 작업을 기다리면 Pooler (pgBouncer 등)는 세션을 반환할 수 없습니다.
➡️ 요청 몰리는 순간 DB 연결 수 급증 → 서버 장애

문제 4: Replication Lag 증가

Replica는 WAL 로그를 적용해야 하는데 idle in transaction이 오래 유지되면
특정 xmin이 유지되어 오래된 WAL을 소비 못해 슬레이브가 밀리기 시작합니다.
➡️ 결국 장애 복구 시 지연 증가

Idle in Transaction 상태 체크하기

# 모든 Idle Transactions 보기
SELECT pid, usename, application_name, state, xact_start, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

   pid   | usename | application_name |        state        |          xact_start           | query
---------+---------+------------------+---------------------+-------------------------------+--------
 1029469 | sbtest  | psql             | idle in transaction | 2025-11-27 04:49:48.541494+00 | BEGIN;
(1 row)




# 오래된 세션만 보기 (예: 5분 이상)
SELECT pid, now() - xact_start AS idle_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - xact_start > interval '5 minutes';
   pid   |  idle_duration  | query
---------+-----------------+--------
 1029469 | 00:05:20.750538 | BEGIN;
(1 row)

Idle in Transaction 원인 (대표적인 원인들)

1) 애플리케이션 코드에서 BEGIN 후 Commit/Rollback 누락
대부분은 개발자가 명시적으로 트랜잭션을 열었지만 예외 처리 중 Commit/Rollback이 누락되는 케이스

2) ORM(JPA, Hibernate, Sequelize 등)의 잘못된 설정
- auto-commit 꺼짐
- Lazy-loading 중간에 트랜잭션이 열리고 방치됨
- Connection pool이 트랜잭션 상태를 감지 못함

3) DBA 또는 운영 스크립트가 트랜잭션 열고 빠져나감

4) 클라이언트 어플리케이션이 세션을 끊지 않고 대기
특히 Python psycopg2, Node pg 라이브러리에서 코드 미흡 시 자주 발생.

Idle in Transaction 해결 방법 (⭐중요)

애플리케이션 코드 개선(Python)

# 올바른 패턴
try:
    cur.execute("BEGIN;")
    cur.execute("UPDATE ...")
    conn.commit()
except:
    conn.rollback()
    raise


# 잘못된 패턴 (Idle in transaction 발생)
cur.execute("BEGIN;")
time.sleep(100)  # 비즈니스 로직에서 오랫동안 지연

PostgreSQL 자체적으로 Idle Timeout 설정

① idle_in_transaction_session_timeout

    ㄴ Idle in transaction이 지정 시간 넘으면 자동으로 세션을 종료시키는 기능.

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

정석은 무조건 애플리케이션에서 고치는 것이다. DB단에서 처리하는 방법이지만 최후의 수단이다...

② Statement Timeout
   ㄴ 쿼리 자체가 오래 걸릴 때 대비

ALTER SYSTEM SET statement_timeout = '30s';

Statement Timeout은 쿼리 수행 시간 제한 파라미터

DBA가 주기적으로 비정상 세션 종료 스크립트 실행

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - xact_start > interval '10 minutes';

수행 전에 몇건 조회되는지 확인하고 수행할 것, 만약 파라미터 설정이 불가능하다면 해당 쿼리를 crontab에 설정함함

PostgreSQL에서 Idle in Transaction은

사소해 보이지만 제대로 관리하지 않으면 DB 성능 저하, Lock Wait 발생, Replication lag 등

대형 장애로 이어지는 핵심 이슈입니다. 

🙌 댓글, 공감, 공유는 큰 힘이 됩니다! 😄

필자의 Rocky Linux는 minimal install로 설치해서 없는게 많다.

이것들을 설치하기 위해서는 yum(linux 7버전까지 사용)과 dnf(linux 8버전부터 사용) 이라는 기능이 필요하다.

하지만 아래와 같이 repository를 연결하지 않으면 사용 할 수 없다.

아래 차례대로 수행하면 repository가 연결되면서 설치가 가능하다.

dnf install epel-release -y
dnf config-manager --set-enabled powertools
dnf makecache
dnf install htop -y
dnf install tar -y
dnf install unzip -y

끝. 이제 여러가지 테스트 하는데 크게 문제 없이 사용 가능 할 것이다.

설치하시는데 고생하셨습니다.

🙌 댓글, 공감, 공유는 큰 힘이 됩니다! 😄

아무래도 쉽게 Linux 서버를 간편하게 사용하려면 putty 등 콘솔로 접속해서 사용하는게 가장 편하다.

복사 붙여넣기, 파일 업로드 다운로드 하려면 콘솔 프로그램은 필수이기 때문이다.

VirtualBox에 설치한 Linux를 이용하여 접속을 해보자.

아래 설정에 들어간다

네트워크 - 어댑터1 - Advanced - 포트 포워딩

다음 빨간색 네모 3칸만 설정하면 된다

호스트 IP : 이게 조금 어려울 수 있다.

호스트 포트 : 22

게스트 포트 : 22

호스트 IP 확인 방법

아래 '명령 프롬프트' 라고 검색하거나

멋있게 하려면 키보드에 [시작버튼]+[R] 눌러서  하는 방법이 있다.

명령 프롬프트, CMD 창이 활성화 되면 ipconfig 라고 명령어를 친다.

필자는 노트북으로 하고 있기 때문에 어댑터가 2개 확인이 된다.

맨 하단에 WiFi 어댑터를 넣었다.

설정 후 VM 전원을 키고 putty 접속을 시도 한다.

putty 접속이 확인되었다.

WIFI로 하는 경우 자주 포트포워딩을 해줘야 되는 경우가 많으니 유의해야될 것 같다.

이제 거의 다 왔다. 마지막 dnf, yum만 연결하면 끝난다.

https://peri.tistory.com/entry/Linux-VMVirtualBox-dnf-yum-repository-%EC%97%B0%EA%B2%B0-%ED%95%98%EA%B8%B0

🙌 댓글, 공감, 공유는 큰 힘이 됩니다! 😄