본문 바로가기
엑셈 경쟁력/DB 인사이드

DB 인사이드 | PostgreSQL Setup - Migration & Upgrade 성능 및 주의사항

by EXEM 2022. 11. 23.

※ 목차 ※
업그레이드 주의사항
        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         |         0

PostgreSQL 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)

 

 

 

 

기획 및 글 | 기술기획팀

 

 

 

댓글0