PostgreSQL Setup - Migration & Upgrade 주의사항 및 성능

PostgreSQL의 신규 기능들을 사용하기 위해서, 혹은 EOL 등의 이유로 PostgreSQL Major 업그레이드를 고려할 수 있습니다. PostgreSQL Major 업그레이드하는 방법은 여러 가지가 존재하며 어떠한 업그레이드 방법을 사용할지, Database 다운타임을 최소한으로 줄일 수 있는 방법은 무엇인지 등에 대한 충분한 검증이 필요할 것입니다. 본 문서에서는 앞서 소개한 PostgreSQL Major 업그레이드 방법들의 성능 측면을 다룹니다. 또한 사전 준비사항 및 주의사항을 포함하지만, Vacuum과 관련된 내용은 포함되지 않습니다.

📢 PostgreSQL Major 업그레이드 방법은 [PostgreSQL Setup - Major Upgrade]에서 확인.

PostgreSQL 9.6.3에서 PostgreSQL 12.12로 업그레이드 과정을 아래 서버에서 확인합니다.

AWS EC2 (c5.2xlarge)

CPU : 8core / RAM : 16GB

Storage : EBS(gp3)

Data Set

ㅤ	Total Size	Table Count	Table Size (Each)	Table Rows (Each)	Column List
Set 1.	25 GB	100	200 MB	1,000,000 Rows	Integer Char(100) Char(100)
Set 2.	100 GB	400	200 MB	1,000,000 Rows	Integer Char(100) Char(100)

25GB Database

100GB Database

업그레이드 주의사항

응용프로그램 별 성능 체크 이전에, 업그레이드 주의사항에 대해 알아보도록 하겠습니다.

개별 Database에 대해 pg_dump를 이용해 업그레이드하는 경우, Role, Tablespace, Extension과 같이 함께 이전할 메타데이터에 대한 선정과 사전작업이 필요합니다.

반면, pg_dumpall과 pg_upgrade를 이용한 업그레이드 시에는 사용 중인 Extension에 대한 버전 관리를 신경 써야 합니다.

Role & Tablespace

pg_dump 응용프로그램을 사용하여 업그레이드를 한다면 Role과 Tablespace와 같은 Global Object는 별도의 이관 작업을 필요로 합니다. pg_dump 응용프로그램으로는 Global Object를 Backup 할 수 없기 때문에, pg_dumpall 응용프로그램을 사용하여 Backup File을 생성합니다.

[AS-IS] Role, Tablespace 정보 확인

[AS-IS] Role & Tablespace Backup

[TO-BE] Role & Tablespace Restore

Tablespace 생성 확인

📢 Tablespace Naming → PG_{Major-Version}_{Catalog-Version}

Extension

PostgreSQL에서 기본으로 제공되는 Extension의 경우(주로 postgresql-contrib) Restore시 자동으로 Extension이 설치됩니다. Restore 이후 PostgreSQL 버전에 맞는 Extension 버전으로 Update를 해야 합니다. 반면에 기본으로 제공되지 않는(Github 등에서 제공되는) Extension의 경우 해당 Extension을 제공하는 곳에서 PostgreSQL 버전에 맞는 Extension 버전을 새로 설치해야 합니다.(CREATE EXTENSION)

PostgreSQL 기본 제공 Extension

Extension 확인

Extension Update

업그레이드 성능

PostgreSQL을 업그레이드를 할 수 있는 여러 가지 방법의 공통점은 Backup과 Restore입니다. PostgreSQL에서 제공하는 pg_dump, pg_dumpall 응용프로그램으로 Backup 하고 psql, pg_restore 응용프로그램으로 Restore 합니다. pg_upgrade의 경우 Global Object와 Database별 Schema 구조를 Backup&Restore 응용프로그램을 사용하고, OS Copy를 통해 Data File을 Copy&Paste 합니다.

즉, pg_upgrade 응용프로그램을 제외하면 Backup과 Restore를 통한 업그레이드로 볼 수 있으며, ① pg_dumpall로 전체 Database Backup → psql로 Restore, ② pg_dump로 개별 Database Backup → psql 또는 pg_restore로 Restore 이 두 가지가 대표적인 조합으로 볼 수 있습니다.

본 단락에서는 업그레이드 시간에 가장 큰 영향을 끼치는 Backup과 Restore에 대해 소요시간을 기준으로 성능 비교를 진행하겠습니다.

Backup 성능

PostgreSQL Backup은 pg_dump와 pg_dumpall 응용프로그램을 통해 수행 가능합니다.

① pg_dump

② pg_dump + Directory Foramt + Parallel(4)

③ pg_dumpall

위의 세 가지 방법을 통한 Backup 소요시간을 측정한 결과는 아래 차트와 같습니다.

사용하는 응용프로그램에 따른 수행 시간 차이는 미비하다.

파라미터 변경에 따른 Backup 성능 차이는 없다.

Parallel 수행은 Backup 성능에 미치는 영향은 미비하다.

결과적으로 응용프로그램에 따른 Backup 성능은 큰 차이가 없으므로, Restore 방법에 따라 Backup 방법을 선택해야 합니다.

📢 참고 - Dump File Size 25GB Database → 약 20GB File 생성 100GB Database → 약 78GB File 생성

Restore 성능

PostgreSQL Restore는 psql과 pg_restore 응용프로그램을 통해 수행 가능합니다. Restore 방식에 따라 Backup 방법을 달리해야 하기 때문에, 최고의 효율을 가질 수 있는 방안을 고려해야 합니다.

① psql

② pg_restore + Directory Format Restore + Parallel(4)

③ pg_restore + Table Unlogged Option + Directory Format Restore + Parallel(4)

위의 세 가지 방법을 통한 Restore 소요시간을 측정한 결과는 아래 차트와 같습니다.

📢 pg_dumpall로 Backup 한 File을 Restore 하는 방식은 pg_dump로 Backup 한 File을 Restore 하는 방식과 동일합니다. pg_dump로 Backup 한 File보다 다소 많은 정보를 포함하고 있지만, Restore 성능은 별반 차이가 없으므로, 본 포스팅에서는 pg_dumpall의 Restore성능은 다루지 않습니다.

Backup과 달리 Restore는 Database 크기에 따라 차이는 있지만, 파라미터 조정으로 세 가지 케이스에서 모두 약 15% 이상의 속도 향상을 얻을 수 있습니다. 테스트에서 조정한 파라미터는 아래와 같습니다.

wal_level 파라미터는 Restore 성능에 가장 큰 영향을 미치는 파라미터이다.

wal_level = minimal로 설정 시 WAL 발생량이 최소화되어 Restore 성능이 향상된다.

Table Unlogged Option을 사용하면 Restore 성능은 눈에 띄게 향상되지만, Restore 후 Table을 Logged로 변경하는 과정까지 고려하면 실질적인 성능 향상 효과는 없다. 하지만, psql을 통한 Restore보다는 조금의 성능 향상 효과는 얻을 수 있다.

Restore 전체적으로 봤을 경우 Parallel Restore 방법이 최선이다.

※ Restore 성능에 영향을 미치는 요소들

wal_level Parameter

wal_level은 WAL(Write-Ahead Log)에 기록되는 정보의 양을 나타냅니다. 지정할 수 있는 값으로는 minimal, replica(archive, hot_standby), logical이 있습니다. PostgreSQL 9.6 버전까지는 기본값으로 minimal이었지만, PostgreSQL 10 버전부터는 기본값이 replica로 변경되었습니다.

wal_level	설명
minimal	- System Crash, 비정상 종료 시 복구하는데 필요한 정보만 기록 - `CREATE TABLE AS` , `CREATE INDEX` , `CLUSTER` , `COPY` 작업을 빠르게 수행 가능 - 최소한의 정보만 기록하므로, Replication 구성은 하지 못함
replica	- minimal 정보포함 - Standby Server에서 Read-Only Query를 실행하는 데 필요한 정보와 WAL Archiving에 필요한 정보를 기록 - PostgreSQL 9.5 버전까지 사용되었던 archive, hot_standby를 포함
logical	- replica 정보 - Logical Decoding을 지원하는데 필요한 정보를 기록

Table Unlogged Option

Table Unlogged 옵션은 WAL을 사용하지 않아 INSERT 속도를 향상시킬 수 있습니다. 하지만 System Crash나 비정상 종료와 같은 상황에 Table이 TRUNCATE가 되므로, Restore 후 Logged 옵션으로 변경해야 합니다. 위의 차트에서와 같이 결과적으로 Restore를 가장 빠르게 수행할 수는 있지만, Restore이후 Data 손실을 방지하기 위해 Table을 Logged 옵션으로 변경하는 과정에 소요되는 시간이 적지 않습니다.

Parallel Restore

Table Unlogged 옵션으로 운영해도 상관없는 경우라면 앞서 설명한 Restore 방식을 사용하는 것이 최선의 방법이지만, Unlogged 옵션으로 운영할 수 없다면 Directory Format으로 Backup 된 File을 Parallel 옵션으로 Restore 하는 방식이 최선의 방법입니다. Parallel로 지정된 개수만큼 프로세스가 생성되어 Directory Format으로 생성된 Backup File들을 처리하기 때문에 단일 프로세스로 Restore 하는 것보다 효율적입니다.

📢 Directory Format으로 Backup시 Table별로 Backup File이 생성됩니다. 예를 들어 Table개수가 100개 일 때 Backup File은 100개+1개(관리 파일)가 생성됩니다.

pg_upgrade 성능

앞서 설명한 Backup & Restore 방식으로 업그레이드하는 방법 외에 pg_upgrade 응용프로그램을 통한 업그레이드 성능을 확인하면 아래 차트와 같습니다.

pg_upgrade 응용프로그램은 Link 옵션 사용 여부에 따라 두 가지 방식으로 사용 가능합니다.

Link 옵션을 부여하지 않은 pg_upgrade는 Global Object 및 Schema 구조를 pg_dumpall 또는 pg_dump를 통해 Backup & Restore 해야 합니다. 이후, 실제 Data File은 OS 명령어를 통해 옮겨줍니다. Global Object와 Database별 Schema 구조 Restore는 Table Data를 Restore에 비해 매우 작은 작업입니다.(보통 5~10초) Backup File도 매우 작아 파라미터 조정으로 인해 얻을 수 있는 큰 이점은 없습니다.

Link 옵션을 부여한 pg_upgrade는 Major 버전 변경에 따라 pg_wal과 같이 구조적으로 변경이 있는 Data Directory는 신규 Database(TO-BE)의 Data Directory를 사용하고, 변경이 없는 Data Directory는 기존 Database(AS-IS) Data Directory에 Hard Link로 연결됩니다.

📢 PostgreSQL 9.6에서 pg_xlog로 사용하던 WAL Directory명이 PostgreSQL 10부터 pg_wal로 변경되었습니다.

pg_upgrade 응용프로그램을 통한 업그레이드는 다른 방법들에 비해 Disk 공간을 다소 적게 사용한다는 것과 속도 또한 빠르다는 장점이 있습니다. 특히 Link 옵션을 사용한다면 Disk 공간 및 속도 측면에서 극적인 결과를 얻을 수 있습니다. 하지만, Extension 호환성 문제, Tablespace 위치 문제 등 예상치 못한 오류 발생 등으로 인해서 업그레이드 작업이 실패할 경우 Backup&Restore 방법보다 오래 걸릴 수 있으므로, 충분한 계획과 테스트가 필요합니다.

필요 Disk 여유공간

업그레이드에 사용하는 응용프로그램에 따라 Disk 여유공간이 필요합니다. 아래 차트는 원본(AS-IS) Database가 사용하는 Disk Size를 제외한 업그레이드 시 필요한 Disk 여유공간을 나타냅니다. 여기에는 PostgreSQL Engine에 대한 Disk Size는 포함되어 있지 않습니다.

pg_dump, pg_dumpall 응용프로그램 사용 시 Backup File의 공간이 필요하며, 최대 AS-IS Data File Size 정도의 Disk 공간이 필요하다.

pg_dump, pg_dumpall 응용프로그램 사용 시 TO-BE Database로 업그레이드에 최대 AS-IS Data File Size 정도의 Disk 공간이 필요하다.

Link 옵션을 부여하지 않고 pg_upgrade 응용프로그램 사용 시 AS-IS Data File Size 정도의 Disk 공간이 필요하다.

Link 옵션을 부여하여 pg_upgrade 응용프로그램 사용 시 AS-IS Data File을 Hard Link로 연결되기 때문에 추가적인 Disk 공간은 필요하지 않다.

정리

위에서 나열한 Backup, Restore, pg_upgrade를 정리하면 아래 차트와 같습니다.(파라미터 조정한 결과만 표시)

소요시간은 오로지 업그레이드를 위한 소요시간을 의미합니다. 업그레이드 이후 빈 공간 정리 및 통계 정보수집을 포함한 VACUUM 시간은 포함되지 않습니다.

테스트 Command

Case 1. pg_dump + psql

Case 2. pg_dump Parallel(4) + pg_restore Parallel(4)

Case 3. pg_dump Parallel(4) + pg_restore Parallel(4) + Table Unlogged

Case 4. pg_upgrade

Case 5. pg_upgrade Link Option

Backup 시 파라미터 조정, Parallel 작업은 성능 향상에 영향을 미치지 않는다.

Restore 시 Parallel 작업으로 성능 향상의 이점을 얻을 수 있다.

Restore 시 Table Unlogged 옵션을 활용하면 빠른 처리가 가능하지만, Logged 옵션으로 변경하는 과정에 시간이 많이 소요된다.

pg_upgrade 응용프로그램을 사용할 경우 다른 업그레이드 방법에 비해 빠른 처리가 가능하다. 단, Extension 호환성 문제, Tablespace 위치 문제 등으로 업그레이드가 실패할 경우 더 오래 걸릴 수 있다.

모든 업그레이드 작업이 완료된 이후에는 VACUUM을 수행해야 한다.

사용하는 응용프로그램에 따라 Disk 여유 공간이 필요하다.

Data 검증

PostgreSQL Upgrade 및 Migration 후에 AS-IS와 TO-BE 간의 Data 검증이 필요합니다. 대표적으로 아래와 같은 내용에 대한 검증작업이 필요하며, 이후 설명되는 스크립트를 통해 확인할 수 있습니다.

Object 개수 확인

Procedure 개수 확인

Trigger 개수 확인

Sequence Last Value 확인

Object 개수 확인

PostgreSQL Database의 Schema별 Object 개수를 확인할 수 있습니다. Database가 여러 개인 경우 Database 마다 확인을 해야 합니다.

Procedure 개수 확인

PostgreSQL 11 버전부터 Procedure Type이 도입되었습니다. 아래 PostgreSQL 버전에 맞는 스크립트를 선택하여 수행해야 합니다.

📢 10 버전까지는 pg_catalog.pg_proc View에 각 Type 별 컬럼에 TRUE/FALSE로 구분. 11 버전부터는 pg_catalog.pg_proc View에 prokind 컬럼이 추가되어 각 Type을 약어로 표시. (f : Normal Function , p : Procedure , a : Aggregate Function , w : Window Function)

PostgreSQL 10 버전 이하

PostgreSQL 11 버전 이상