※ 목차 ※
업그레이드 주의사항
Role & Tablespace
Extension
업그레이드 성능
Backup 성능
Restore 성능
pg_upgrade 성능
필요 Disk 여유공간
정리
Data 검증
Object 개수 확인
Procedure 개수 확인
Trigger 개수 확인
Sequence Last Value 확인
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. 25GB 100 200MB 1,000,000 Rows Integer
Char(100)
Cahr(100)Set 2. 100GB 400 200MB 1,000,000 Rows Integer
Char(100)
Cahr(100)더보기-- 25GB Database Size postgres=# \l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+-----------+---------+-------+-----------------------+---------+------------+-------------------------------------------- postgres | postgres | SQL_ASCII | C | C | | 25 GB | pg_default | default administrative connection database template0 | postgres | SQL_ASCII | C | C | =c/postgres +| 7257 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | SQL_ASCII | C | C | =c/postgres +| 7257 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | | (3 rows) -- Table/Index Counts SELECT relnamespace::regnamespace, CASE WHEN relkind = 'i' THEN 'Index' WHEN relkind = 'r' THEN 'Table' END , COUNT(*) FROM pg_catalog.pg_class pc WHERE relnamespace::regnamespace not IN ('pg_toast','information_schema','pg_catalog') GROUP BY relnamespace, relkind ; relnamespace | case | count --------------+-------+------- exem | Index | 100 exem | Table | 100 (2 rows)더보기-- 100GB Database Size postgres=# \l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 98 GB | pg_default | default administrative connection database template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7257 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7257 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | | (3 rows) -- Table/Index Counts SELECT relnamespace::regnamespace, CASE WHEN relkind = 'i' THEN 'Index' WHEN relkind = 'r' THEN 'Table' END , COUNT(*) FROM pg_catalog.pg_class pc WHERE relnamespace::regnamespace not IN ('pg_toast','information_schema','pg_catalog') GROUP BY relnamespace, relkind ; relnamespace | case | count --------------+-------+------- exem | Index | 400 exem | Table | 400 (2 rows)
| 업그레이드 주의사항 | 목차 |
응용프로그램 별 성능 체크 이전에, 업그레이드 주의사항에 대해 알아보도록 하겠습니다.
개별 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 정보 확인
-- Role 확인
SELECT * FROM pg_roles ;
또는 \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
exem | Superuser, Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
-- Tablespace 확인
SELECT * FROM pg_tablespace ;
또는 \db
List of tablespaces
Name | Owner | Location
------------+----------+----------------------------
exem | postgres | /home/maxgauge/EXEM/PG_TBS
pg_default | postgres |
pg_global | postgres |
(3 rows)
[AS-IS] Role & Tablespace Backup
pg_dumpall --globals-only --host=localhost --port=5432 --username=postgres --database=exem --file=/PostgreSQL/dump/global_only.dump
## Backup File 확인
... 생략 ...
--
-- Roles
--
CREATE ROLE exem;
ALTER ROLE exem WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
--
-- Tablespaces
--
CREATE TABLESPACE exem OWNER postgres LOCATION '/home/maxgauge/EXEM/PG_TBS';
... 생략 ...
[TO-BE] Role & Tablespace Restore
psql --host=localhost --port=5433 --username=postgres --dbname=postgres --file=/PostgreSQL/dump/global_only.dump
Tablespace 생성 확인
ls -l /home/maxgauge/EXEM/PG_TBS
drwx------. 2 maxgauge maxgauge 6 Nov 7 22:35 PG_12_201909212
drwx------. 3 maxgauge maxgauge 19 Jun 22 11:39 PG_9.6_201608131📢 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
## postgresql-contrib 또는 Source File의 경우 contrib Directory
[postgres@/home/postgres/postgresql-12.12/contrib] ls
Makefile citext hstore ltree pg_stat_statements spi uuid-ossp
README contrib-global.mk hstore_plperl ltree_plpython pg_trgm sslinfo vacuumlo
adminpack cube hstore_plpython oid2name pg_visibility start-scripts xml2
amcheck dblink intagg pageinspect pgcrypto tablefunc
auth_delay dict_int intarray passwordcheck pgrowlocks tcn
auto_explain dict_xsyn isn pg_buffercache pgstattuple test_decoding
bloom earthdistance jsonb_plperl pg_freespacemap postgres_fdw tsm_system_rows
btree_gin file_fdw jsonb_plpython pg_prewarm seg tsm_system_time
btree_gist fuzzystrmatch lo pg_standby sepgsql unaccent
Extension 확인
SELECT * FROM pg_extension ;
또는 \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
Extension Update
-- [TO-BE] PostgreSQL에서 기본으로 제공해주는 Extension의 경우 PostgreSQL 업그레이드 후 UPDATE 수행
ALTER EXTENSION {extension_name} 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 방법을 달리해야 하기 때문에, 최고의 효율을 가질 수 있는 방안을 고려해야 합니다.
PostgreSQL Backup은 pg_dump와 pg_dumpall 응용프로그램을 통해 수행 가능합니다.
① 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% 이상의 속도 향상을 얻을 수 있습니다. 테스트에서 조정한 파라미터는 아래와 같습니다.
shared_buffers = 4GB ## Default = 128MB
wal_level = minimal ## Default = replica
max_wal_senders = 0 ## Default = 10 (wal_level=minimal로 변경 시 해당 파라미터를 0으로 변경해야 Database 기동가능)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
## [AS-IS] Backup
pg_dump --port=5432 --file=/dump/dump.dump
## [TO-BE] Restore
psql --port=5433 --file=/dump/dump.dump## [AS-IS] Backup
pg_dump --port=5432 --format=d --file=/dump/dumpdir
## [TO-BE] Restore
pg_restore --port=5433 --format=d --jobs=4 --dbname=postgres /dump/dumpdir## Backup
## [AS-IS] Global-Only Backup
pg_dumpall --port=5432 --globals-only --file=/dump/global.dump
## [AS-IS] Schema-Only Backup
pg_dumpall --port=5432 --schema-only --file=/dump/schema.dump
## [AS-IS] Data-Only Backup
pg_dump --port=5432 --data-only --format=d --jobs=4 --file=/dump/dumpdir_data
## Restore
## [TO-BE] Global-Only Restore
psql --port=5433 --file=/dump/global.dump
## [TO-BE] Schema-Only Restore
psql --port=5433 --file=/dump/schema.dump
## [TO-BE] Modify Table Attribute (Logged -> Unlogged)
psql --port=5433 --tuples-only --command="
SELECT 'ALTER TABLE ' || relnamespace::regnamespace || '.' || relname || ' SET UNLOGGED ;'
FROM pg_class
WHERE relnamespace::regnamespace NOT IN ('pg_toast','information_schema','pg_catalog')
AND relkind = 'r'
" > modify_unlogged.sql
## [TO-BE]
psql --port=5433 --file=modify_unlogged.sql
## [TO-BE] Data-Only Restore
pg_restore --port=5433 --data-only -format=d --jobs=4 --dbname=postgres /dump/dumpdir_data
## [TO-BE] Modify Table Attribute (Unlogged -> Logged)
psql --port=5433 --tuples-only --command="
SELECT 'ALTER TABLE ' || relnamespace::regnamespace || '.' || relname || ' SET LOGGED ;'
FROM pg_class
WHERE relpersistence = 'u'
AND relkind = 'r'
AND relnamespace::regnamespace NOT IN ('pg_toast','information_schema','pg_catalog')
" > modify_logged.sql
## [TO-BE]
psql --port=5433 --file=modify_logged.sql## Upgrade
/home/postgres/PostgreSQL12_Engine/bin/pg_upgrade \
--old-bindir=/home/postgres/PostgreSQL96_Engine/bin \
--new-bindir=/home/postgres/PostgreSQL12_Engine/bin \
--old-datadir=/home/postgres/PostgreSQL96_Data \
--new-datadir=/home/postgres/PostgreSQL12_Data## Upgrade
/home/postgres/PostgreSQL12_Engine/bin/pg_upgrade --link \
--old-bindir=/home/postgres/PostgreSQL96_Engine/bin \
--new-bindir=/home/postgres/PostgreSQL12_Engine/bin \
--old-datadir=/home/postgres/PostgreSQL96_Data \
--new-datadir=/home/postgres/PostgreSQL12_Data
- 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 마다 확인을 해야 합니다.
SELECT pc.relnamespace::regnamespace::text AS schema_name ,
CASE WHEN pc.relkind = 'r' THEN 'Table'
WHEN pc.relkind = 'i' THEN 'Index'
WHEN pc.relkind = 'S' THEN 'Sequence'
WHEN pc.relkind = 't' THEN 'Toast'
WHEN pc.relkind = 'v' THEN 'View'
WHEN pc.relkind = 'm' THEN 'Materialized View'
WHEN pc.relkind = 'f' THEN 'Foreign table'
WHEN pc.relkind = 'c' THEN 'Composite Type'
WHEN pc.relkind = 'p' THEN 'Partition Table'
WHEN pc.relkind = 'I' THEN 'Partition Index'
END AS object_type ,
COUNT(1)
FROM pg_catalog.pg_class pc
WHERE 1=1
--AND pc.relnamespace::regnamespace IN ('exem'::regnamespace) -- Schema Name
AND pc.relnamespace::regnamespace NOT IN ('information_schema'::regnamespace , 'pg_catalog'::regnamespace , 'pg_toast'::regnamespace)
GROUP BY pc.relnamespace , pc.relkind
ORDER BY schema_name , object_type ;
schema_name | object_type | count
-------------+-------------+-------
exem | Index | 100
exem | Table | 100
public | Sequence | 1
(3 rows)
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 버전 이하
SELECT pp.pronamespace::regnamespace AS schema_name ,
CASE WHEN pp.proisagg = true THEN 'Aggregate Function'
WHEN pp.proiswindow = true THEN 'Window Function'
ELSE 'Function'
END AS proc_type ,
COUNT(1)
FROM pg_catalog.pg_proc pp
WHERE 1=1
--AND pp.pronamespace::regnamespace IN ( 'exem'::regnamespace ) -- Schema Name
AND pp.pronamespace::regnamespace NOT IN ('information_schema'::regnamespace , 'pg_catalog'::regnamespace , 'pg_toast'::regnamespace)
GROUP BY pp.pronamespace , pp.proisagg , pp.proiswindow ;
schema_name | proc_type | count
-------------+-----------+-------
public | Function | 1
(1 row)PostgreSQL 11 버전 이상
SELECT pp.pronamespace::regnamespace AS schema_name ,
CASE WHEN pp.prokind = 'f' THEN 'Normal Function'
WHEN pp.prokind = 'p' THEN 'Procedure'
WHEN pp.prokind = 'a' THEN 'Aggregate Function'
WHEN pp.prokind = 'w' THEN 'Window Function'
END AS proc_type ,
COUNT(1)
FROM pg_catalog.pg_proc pp
WHERE 1=1
--AND pp.pronamespace::regnamespace IN ( 'exem'::regnamespace ) -- Schema Name
AND pp.pronamespace::regnamespace NOT IN ('information_schema'::regnamespace , 'pg_catalog'::regnamespace , 'pg_toast'::regnamespace)
GROUP BY pp.pronamespace , pp.prokind ;
schema_name | proc_type | count
-------------+-----------------+-------
public | Normal Function | 1
(1 row)
Trigger 개수 확인
SELECT 'Trigger' AS name ,
CASE WHEN pt.tgenabled = 'O' THEN 'origin, local mode'
WHEN pt.tgenabled = 'D' THEN 'disabled'
WHEN pt.tgenabled = 'R' THEN 'replica mode'
WHEN pt.tgenabled = 'A' THEN 'always'
END AS trigger_mode ,
COUNT(1)
FROM pg_catalog.pg_trigger pt
GROUP BY pt.tgenabled ;
name | trigger_mode | count
---------+--------------------+-------
Trigger | origin, local mode | 56
(1 row)
Sequence Last Value 확인
PostgreSQL 10 버전부터 pg_catalog.pg_sequences Dictionary Veiw가 도입되어 Sequence의 Last Value를 쉽게 확인할 수 있습니다. 아래 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 9.6 버전 이하
SELECT 'SELECT ''' || pc.relname || ''' AS sequence_name , last_value FROM ' || pc.relname || ' UNION ALL'
FROM pg_catalog.pg_class pc
WHERE relkind = 'S'
UNION ALL
SELECT 'SELECT ''LIST END'', 0';
-- 위의 결과 실행
SELECT 'seq01' AS sequence_name , last_value FROM seq01 UNION ALL
SELECT 'LIST END', 0 ;
sequence_name |last_value
------------------+-----------
seq01 | 23
LIST END | 0PostgreSQL 10 버전 이상
SELECT schemaname ,
sequencename ,
last_value
FROM pg_catalog.pg_sequences ps
WHERE 1=1
--AND schemaname IN ('exem')
ORDER BY schemaname, sequencename ;
schemaname | sequencename | last_value
------------+--------------+------------
public | seq01 | 23
(1 row)
기획 및 글 | 기술기획팀
'엑셈 경쟁력 > DB 인사이드' 카테고리의 다른 글
| DB 인사이드 | PostgreSQL HOT - 1. Page와 관리 (1) | 2023.02.22 |
|---|---|
| DB 인사이드 | PostgreSQL Vacuum - Monitoring : XMIN’s Horizon (2) | 2023.01.19 |
| DB 인사이드 | PostgreSQL Setup - Major Upgrade (0) | 2022.09.28 |
| DB 인사이드 | PostgreSQL Setup - Minor Upgrade (0) | 2022.09.28 |
| DB 인사이드 | PostgreSQL Setup - Installation (0) | 2022.08.25 |
댓글