[GDAL + pwsh] PostGIS 연속지적도 Partiton Table Backup and Upload

이 글은 pwsh, gdal, psql 에 어느정도 익숙하신 분들이 읽기에 좋은 글입니다!

작성 계기

이번에 회사에서 연속지적도를 PostgreSQL 에 업로드해야 되는데,
이걸 업로드하기 전에 전월의 연속지적도 테이블을 백업해야 되는 요구사항이 있었다.
이를 위해서 백업 먼저하고 그다음에 신규 테이블에 데이터를 업로드하는 작업을
pwsh & gdal 를 통해서 배치성으로 작업 가능하도록 ps1 스크립트를 작성했다.
(ps1 스크립트로 짠 이유는 윈도우 사용자가 압도적으로 많기 때문에 그랬다)

지금부터 그 스크립트와 관련된 내용을 기록, 공유한다.

ps1 스크립트 작업순서

 연속지적도 파티션 테이블 백업 및 신규 데이터 업로드 스크립트
 설명: 이 스크립트가 하는 작업은 다음과 같다.
 
 1. 먼저 신규 파티션 테이블을 생성한다. 이름 규칙은 $partitionMasterTableNm + "_latest" 이다.
    이 명칭의 테이블은 파티션 MASTER 테이블의 명칭으로 사용된다. (=$TEMP_LATEST_TB_NAME)
 2. $shapeFileDirectoryPath 디렉토리 경로에 있는 모든 ShapeFile 들을 찾아낸다. 
    그리고 각각의 Shape File 에 적혀있는 시도 코드 숫자값을 사용해서 $TEMP_LATEST_TB_NAME 하부 파티션 테이블들이 생성된다.
    하부 파티션 테이블들은 명칭 패턴이 [$partitionMasterTableNm_<시도코드>_latest] 와 같이 생성된다. (=$TEMP_LATEST_TB_PART_NAME)
 3. 하부 파티션 테이블들이 생성됨과 동시에 ShapeFile 에서 해당 테이블로 데이터를 IMPORT 한다. 이때 gdal(ogr2ogr) 을 사용한다.
 4. 모든 파티션 하부 테이블들의 데이터를 넣었으면, pnu, geom 컬럼에 인덱스를 생성한다. 이게 시간이 제법 걸린다.
 5. 이제 신규로 생성된 테이블($TEMP_LATEST_TB_NAME) 과 기존에 있던 테이블($partitionMasterTableNm) 의 이름을 바꿔치기한다.
    - 기존 테이블($partitionMasterTableNm) ==> $partitionMasterTableNm + "날짜(연월일_시_분_초)"
    - 신규 생성 테이블 $TEMP_LATEST_TB_NAME ===> $partitionMasterTableNm 으로 변경
    - 이때 파티션 테이블의 하부 자식 테이블들도 모두 같은 패턴으로 이름이 변경된다.

ps1 스크립트 본문

param (
  # DB Host 명 또는 IP
  [string] $dbHost=$env:BATCH_UPLOAD_DB_HOST,
  # DB Port 번호
  [string] $dbPort=$env:BATCH_UPLOAD_DB_PORT,
  # import DB 명
  [string] $importDbName=$env:BATCH_UPLOAD_DB_NAME,
  # import DB 스키마명
  [string] $importSchema=$env:BATCH_UPLOAD_DB_SCHEMA,
  # postgresql 사용자 ID
  [string] $dbConnectUserId=$env:BATCH_UPLOAD_DB_USER_ID,
  # postgresql 사용자 비번
  [string] $dbConnectUserPw=$env:BATCH_UPLOAD_DB_USER_PW,
  # postgresql 에 partition table 을 모두 담을 논리테이블(Master 테이블 명)
  [string] $partitionMasterTableNm="lsmd_cont_ldreg_all",
  # shape file 이 모두 담겨있는 디렉토리 경로. default: 스크립트 실행 디렉토리
  [string] $shapeFileDirectoryPath = $(Get-Location));

# 비번의 경우 특수 문자가 들어가는 경우가 허다합니다. 
# 아래처럼 Url 인코딩을 처리해야 에러가 덜 발생하겠죠?
$dbConnectUserPw = [System.Web.HttpUtility]::UrlEncode($dbConnectUserPw);

<#
사용 예시: 
이 스크립트를 저장하는 ps1 의 명칭이 import_multi_shape.ps1 라고 가정하면...

.\import_multi_shape.ps1 `
-dbHost "127.0.0.1"`
-dbPort "5432" `
-importDbName "postgres" `
-importSchema "public" `
-dbConnectUserId "postgres" `
-dbConnectUserPw "postgres" `
-partitionMasterTableNm "lsmd_cont_ldreg_all" `
-shapeFileDirectoryPath "C:\연속지적도_2024_directory"

#>

Write-Host "
You Had Insert Param Like Below...
"
Write-Host "dbHost=$dbHost"
Write-Host "dbPort=$dbPort"
Write-Host "importDbName=$importDbName"
Write-Host "importSchema=$importSchema"
Write-Host "dbConnectUserId=$dbConnectUserId"
Write-Host "dbConnectUserPw=$dbConnectUserPw"
Write-Host "partitionMasterTableNm=$partitionMasterTableNm"
Write-Host "shapeFileDirectoryPath=$shapeFileDirectoryPath"
Write-Host ""

$date = Get-Date -Format "yyyyMMdd_HH_mm_ss"
$PSQL_CON_STRING="postgres://${dbConnectUserId}:${dbConnectUserPw}@${dbHost}:${dbPort}/${importDbName}"
$TEMP_LATEST_TB_NAME="${importSchema}.${partitionMasterTableNm}_latest";

if (!(Test-Path -PathType Container $shapeFileDirectoryPath)) {
  throw "There is No Such Directory name [$shapeFileDirectoryPath]!";
}


####### 일단 신규 임시 (파티션 마스터) 테이블 생성
Write-Host "
[[ 임시 파티션 마스터 테이블($TEMP_LATEST_TB_NAME)을 생성합니다 ]]
";
psql -d "$PSQL_CON_STRING" -c `
"
drop table if exists $TEMP_LATEST_TB_NAME cascade;
create table $TEMP_LATEST_TB_NAME (
  sgg_oid integer,
  jibun varchar(100),
  bchk varchar(1),
  pnu varchar(19),
  col_adm_se varchar(5),
  geom geometry(MultiPolygon, 5186)
) partition by range(pnu);";

if($LASTEXITCODE -ne 0) {
  throw "임시 테이블 생성 실패"
}


Write-Host "
[[ '$shapeFileDirectoryPath' 디렉토리 내의 모든 ShapeFile 들을 재귀적으로 찾아냅니다.
각각의 Shapefile 은 하나의 파티션 하위 테이블로 생성됩니다. ]]
";


####### 디렉토리 내의 모든 shapefile 들을 재귀적으로 찾아내서 파티션 테이블을 생성하고, 데이터를 import 한다.
Get-ChildItem "$shapeFileDirectoryPath/*.shp" -Recurse | foreach-Object -ErrorAction Stop {
  $shapeFileFullPath = $_;
  $baseName = $_.BaseName;
  $lastIndexOfUnderBar = $baseName.LastIndexOf('_');
    
  # remove creation date Postfix from file name
  $partitionTableNm = $baseName.Substring(0, $lastIndexOfUnderBar).ToLower();
  $TEMP_LATEST_TB_PART_NAME="${importSchema}.${partitionTableNm}_latest";

  # getting sido code which is middle of the shape file name 
  $sidoCode = $partitionTableNm.Substring($partitionTableNm.LastIndexOf('_') + 1);

  # create pnu start range value
  $pnuStart = $sidoCode.PadRight(19, '0');

  # create pnu end range value
  $pnuEnd = (([int16]$sidoCode) + 1).ToString().PadRight(19, '0');

  #### 파티션 하위 테이블을 생성함과 동시에 파티션 마스터 테이블에 편입시킵니다.
  Write-Host "
  ## Child Table (${TEMP_LATEST_TB_PART_NAME}) Will be included into MasterTable (${TEMP_LATEST_TB_NAME})
  importing `"$_`" file To `"$partitionTableNm`" pnu range: [ '$pnuStart' ~ '$pnuEnd' ] ##
  "

  psql -d "$PSQL_CON_STRING" `
  -c "CREATE TABLE $TEMP_LATEST_TB_PART_NAME
  PARTITION OF $TEMP_LATEST_TB_NAME
  FOR VALUES FROM ('$pnuStart') TO ('$pnuEnd');";

  #### 파티션 하위 테이블에 데이터를 import 합니다.
  ogr2ogr `
    --config PG_USE_COPY YES -append -progress -f PostgreSQL `
    PG:"host=$dbHost port=$dbPort dbname=$importDbName schemas=$importSchema user=$dbConnectUserId password=$dbConnectUserPw" `
    "$shapeFileFullPath" `
    --config SHAPE_ENCODING "EUC-KR" --config OGR_TRUNCATE YES `
    -nln $TEMP_LATEST_TB_PART_NAME -nlt multipolygon;
}


####### 신규 인덱스 생성
Write-Host "
[[ creating index for partiton master table $TEMP_LATEST_TB_NAME ]]
"

# creating new index for "pnu"
Write-Host "EXECUTE: [create index ${partitionMasterTableNm}_pnu_${date}_idx on $TEMP_LATEST_TB_NAME (pnu)]";

psql -d "$PSQL_CON_STRING" -c `
"create index ${partitionMasterTableNm}_pnu_${date}_idx 
  on $TEMP_LATEST_TB_NAME (pnu);";

# creating new gist index for geometry column
Write-Host "EXECUTE: [create index ${partitionMasterTableNm}_geom_${date}_idx on $TEMP_LATEST_TB_NAME using gist (geom)]";

psql -d "$PSQL_CON_STRING" -c `
"create index ${partitionMasterTableNm}_geom_${date}_idx on $TEMP_LATEST_TB_NAME using gist (geom);";


####### 기존에 있던 partiton table (Master, Child 모두) 명칭 수정
Write-Host "
[[ RENAMING OLD MASTER TABLE AND CHILD TABLE]]
"

psql -d "$PSQL_CON_STRING" -c `
"DO `$$ 
DECLARE partition_name text;
    new_partition_name text;
    partition_oid oid;
    partition_cursor CURSOR FOR
        SELECT c.relname, c.oid
        FROM pg_inherits i
                 JOIN pg_class p ON i.inhparent = p.oid
                 JOIN pg_class c ON i.inhrelid = c.oid
        WHERE p.relnamespace = '${importSchema}'::regnamespace AND p.relname = '${partitionMasterTableNm}';
BEGIN
    OPEN partition_cursor;
    LOOP
        FETCH partition_cursor INTO partition_name, partition_oid;
        EXIT WHEN NOT FOUND;

        -- Construct the new partition name. You can modify this as needed.
        -- This example simply appends yyyyMMdd to the original partition name.
        new_partition_name := partition_name || '_${date}';

        -- Construct and execute the RENAME TABLE command.
        EXECUTE format('ALTER TABLE %I.%I RENAME TO %I', '${importSchema}', partition_name, new_partition_name);
    END LOOP;
    CLOSE partition_cursor;
END `$$;";

psql -d "$PSQL_CON_STRING" -c "alter table if exists ${importSchema}.${partitionMasterTableNm} rename to ${partitionMasterTableNm}_${date}";

####### 신규로 생성했던 partiton table (Master, Child 모두) 명칭 수정
Write-Host "
[[ RENAMING NEW MASTER TABLE AND CHILD TABLE ]]
"
psql -d "$PSQL_CON_STRING" -c `
"DO `$$ 
DECLARE partition_name text;
    new_partition_name text;
    partition_oid oid;
    partition_cursor CURSOR FOR
        SELECT c.relname, c.oid
        FROM pg_inherits i
                 JOIN pg_class p ON i.inhparent = p.oid
                 JOIN pg_class c ON i.inhrelid = c.oid
        WHERE p.relnamespace = '${importSchema}'::regnamespace AND p.relname = '${partitionMasterTableNm}_latest';
BEGIN
    OPEN partition_cursor;
    LOOP
        FETCH partition_cursor INTO partition_name, partition_oid;
        EXIT WHEN NOT FOUND;

        -- Construct the new partition name. You can modify this as needed.
        -- This example simply appends yyyyMMdd to the original partition name.
        new_partition_name := REPLACE(partition_name, '_latest', '');

        -- Construct and execute the RENAME TABLE command.
        EXECUTE format('ALTER TABLE %I.%I RENAME TO %I', '${importSchema}', partition_name, new_partition_name);
    END LOOP;
    CLOSE partition_cursor;
END `$$;";

psql -d "$PSQL_CON_STRING" -c "alter table $TEMP_LATEST_TB_NAME rename to ${partitionMasterTableNm}";

참고사항: 더 편하게 쓰는 법

참고로 아래와 같은 환경변수를 미리 세팅 해놓으면...

• $env:BATCH_UPLOAD_DB_HOST
• $env:BATCH_UPLOAD_DB_PORT
• $env:BATCH_UPLOAD_DB_NAME
• $env:BATCH_UPLOAD_DB_SCHEMA
• $env:BATCH_UPLOAD_DB_USER_ID
• $env:BATCH_UPLOAD_DB_USER_PW

.\import_multi_shape.ps1 -shapeFileDirectoryPath "C:\연속지적도_2024_directory"

처럼 더 간단하게 사용할 수 있도록 만들었다.