PostgreSQL: LSN → WAL 파일명 변환 쉘 스크립트

※ PostgreSQL: LSN to WAL file name conversion shell script.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 그냥 제가 필요해서 만들었습니다.

 

현재의 Timline_id와 LSN을 쿼리해서 WAL 파일명을 계산해주는 쉘 스크립트입니다.

※ 접근제어 설정에 따라 .pgpass 파일 또는 비밀번호 입력이 필요할 수 있습니다.

---

1. 쉘 스크립트

#!/bin/bash

# PostgreSQL connection settings (modify as needed)
PGUSER="postgres"
PGDATABASE="postgres"
PORT=5432

# Retrieve the current timeline ID and LSN from PostgreSQL
CURRENT_TIMELINE_ID=$(psql -U $PGUSER -p $PORT -d $PGDATABASE -tA -c "SELECT timeline_id FROM pg_control_checkpoint();")
CURRENT_LSN=$(psql -U $PGUSER -p $PORT -d $PGDATABASE -tA -c "SELECT pg_current_wal_lsn();")

# Check if arguments are provided
LSN_PROVIDED=false
TIMELINE_PROVIDED=false

if [[ -n "$1" ]]; then
    if [[ "$1" =~ ^[0-9A-F]+/[0-9A-F]+$ ]]; then
        LSN="$1"
        LSN_PROVIDED=true
    else
        echo "Warning: Invalid LSN format provided. Using current LSN: $CURRENT_LSN"
        LSN="$CURRENT_LSN"
    fi
else
    LSN="$CURRENT_LSN"
fi

if [[ -n "$2" ]]; then
    if [[ "$2" =~ ^[0-9]+$ ]]; then
        TIMELINE_ID="$2"
        TIMELINE_PROVIDED=true
    else
        echo "Warning: Invalid Timeline ID provided. Using current Timeline ID: $CURRENT_TIMELINE_ID"
        TIMELINE_ID="$CURRENT_TIMELINE_ID"
    fi
else
    TIMELINE_ID="$CURRENT_TIMELINE_ID"
fi

# Extract offset from LSN
LSN_HEX=$(echo "$LSN" | awk -F'/' '{print $2}')  # Extract hexadecimal value after '/'
LSN_PREFIX=$(printf "%08X" $((0x$LSN_HEX >> 24)))  # Convert to WAL segment number

# Generate WAL file name
WAL_FILE=$(printf "%08X" $TIMELINE_ID)"00000000${LSN_PREFIX}"

# Output results
if [[ "$LSN_PROVIDED" == false && "$TIMELINE_PROVIDED" == false ]]; then
    echo "No input provided. Using current values from PostgreSQL."
fi
echo "Timeline ID: $TIMELINE_ID"
echo "LSN: $LSN"
echo "Corresponding WAL file: $WAL_FILE"

---

2. 사용법

▸ 인자값 입력 없이 실행

./lsn_to_wal.sh
Timeline ID: 2
LSN: 0/D000050
Corresponding WAL file: 00000002000000000000000D

 

▸ LSN만 입력 후 실행

./lsn_to_wal.sh 0/D000028
Timeline ID: 2
LSN: 0/D000028
Corresponding WAL file: 00000002000000000000000D

 

▸ 모든 인자값 입력 후 실행

./lsn_to_wal.sh 0/D000028 3
Timeline ID: 3
LSN: 0/D000028
Corresponding WAL file: 00000003000000000000000D

 

▸ Standby에서 가장 최근 적용된 WAL 파일 찾기

STANDBY_LSN=$(psql -U postgres -d postgres -tA -c "SELECT replay_lsn FROM pg_stat_replication;")
./lsn_to_wal.sh $STANDBY_LSN

 

▸ Standby에서 현재 받은 WAL 확인 (아직 적용되지 않은 WAL 포함)

STANDBY_LSN=$(psql -U postgres -d postgres -tA -c "SELECT flush_lsn FROM pg_stat_replication;")
./lsn_to_wal.sh $STANDBY_LSN

 

+ 응용 쉘 스크립트: 프라이머리와 스탠바이의 WAL 차이 확인 (Replication Lag 체크)

#!/bin/bash

# PostgreSQL connection settings (modify as needed)
PRIMARY_HOST="Primary"
STANDBY_HOST="Standby"
PORT=5432
PGUSER="postgres"
PGDATABASE="postgres"

# Retrieve the current WAL LSN from Primary
PRIMARY_LSN=$(psql -h $PRIMARY_HOST -p $PORT -U $PGUSER -d $PGDATABASE -tA -c "SELECT pg_current_wal_lsn();")

# Retrieve the last flushed WAL LSN from Standby
STANDBY_LSN=$(psql -h $STANDBY_HOST -p $PORT -U $PGUSER -d $PGDATABASE -tA -c "SELECT flushed_lsn FROM pg_stat_wal_receiver;")

# Error handling: If LSN values are empty, display an error message and exit
if [[ -z "$PRIMARY_LSN" || -z "$STANDBY_LSN" ]]; then
    echo "Error: Unable to retrieve LSN values."
    echo "Check if PostgreSQL replication is running."
    exit 1
fi

# Display LSN values
echo "Primary LSN: $PRIMARY_LSN"
echo "Standby Flushed LSN: $STANDBY_LSN"

# Compare LSN values to check for replication lag
if [[ "$PRIMARY_LSN" == "$STANDBY_LSN" ]]; then
    echo "Replication is up-to-date."
    exit 0
else
    echo "Replication lag detected."
fi

# Extract WAL file name from the given LSN
get_wal_file() {
    local LSN="$1"

    # Retrieve the current timeline ID
    TIMELINE_ID=$(psql -h $STANDBY_HOST -p $PORT -U $PGUSER -d $PGDATABASE -tA -c "SELECT timeline_id FROM pg_control_checkpoint();")

    # Validate LSN format
    if [[ ! "$LSN" =~ ^[0-9A-F]+/[0-9A-F]+$ ]]; then
        echo "Warning: Invalid LSN format provided. Using Standby LSN: $STANDBY_LSN"
        LSN="$STANDBY_LSN"
    fi

    # Extract offset from LSN
    LSN_HEX=$(echo "$LSN" | awk -F'/' '{print $2}')
    LSN_PREFIX=$(printf "%08X" $((0x$LSN_HEX >> 24)))

    # Generate WAL file name
    WAL_FILE=$(printf "%08X" $TIMELINE_ID)"00000000${LSN_PREFIX}"

    echo "Corresponding WAL file: $WAL_FILE"
}

# Get the WAL file for the detected Standby LSN
get_wal_file "$STANDBY_LSN"

---

오늘은 여기까지~