POSTGRESQL Archives - 6 중 4 번째 페이지

[DATABASE/POSTGRESQL] 단일 테이블의 VACUUM 상태 조회하기

■ 단일 테이블의 VACUUM 상태를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    RELNAME
   ,N_DEAD_TUP
   ,LAST_VACUUM
   ,LAST_AUTOVACUUM
FROM  PG_CATALOG.PG_STAT_ALL_TABLES
WHERE N_DEAD_TUP > 0
AND   RELNAME = 'sxacc-devices'
ORDER BY N_DEAD_TUP DESC;

SELECT

RELNAME

,N_DEAD_TUP

,LAST_VACUUM

,LAST_AUTOVACUUM

FROM PG_CATALOG.PG_STAT_ALL_TABLES

WHERE N_DEAD_TUP > 0

AND RELNAME = 'sxacc-devices'

ORDER BY N_DEAD_TUP DESC;

[DATABASE/POSTGRESQL] VACUUM 상태 조회하기

■ VACUUM 상태를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    RELNAME
   ,LAST_VACUUM
   ,LAST_AUTOVACUUM
   ,LAST_ANALYZE
   ,LAST_AUTOANALYZE
FROM PG_STAT_USER_TABLES;

SELECT

RELNAME

,LAST_VACUUM

,LAST_AUTOVACUUM

,LAST_ANALYZE

,LAST_AUTOANALYZE

FROM PG_STAT_USER_TABLES;

[DATABASE/POSTGRESQL] 모든 쿼리 조회하기

■ 모든 쿼리를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    SUBSTR(QUERY, 1, 100) QUERY
   ,BACKEND_TYPE
   ,APPLICATION_NAME
   ,QUERY_START
   ,WAIT_EVENT_TYPE
   ,WAIT_EVENT
   ,STATE
FROM PG_STAT_ACTIVITY;

SELECT

SUBSTR(QUERY, 1, 100) QUERY

,BACKEND_TYPE

,APPLICATION_NAME

,QUERY_START

,WAIT_EVENT_TYPE

,WAIT_EVENT

,STATE

FROM PG_STAT_ACTIVITY;

[DATABASE/POSTGRESQL] 2분 이상 실행 쿼리 조회하기

■ 2분 이상 실행 쿼리를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    NOW() - QUERY_START AS RUNTIME
   ,USENAME
   ,DATNAME
   ,STATE
   ,QUERY
FROM  PG_STAT_ACTIVITY
WHERE NOW() - QUERY_START > '2 MINUTES'::INTERVAL
ORDER BY RUNTIME DESC;

SELECT

NOW() - QUERY_START AS RUNTIME

,USENAME

,DATNAME

,STATE

,QUERY

FROM PG_STAT_ACTIVITY

WHERE NOW() - QUERY_START > '2 MINUTES'::INTERVAL

ORDER BY RUNTIME DESC;

[DATABASE/POSTGRESQL] 실행 쿼리 조회하기

■ 실행 쿼리를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    PID
   ,AGE(CLOCK_TIMESTAMP(), QUERY_START)
   ,USENAME
   ,QUERY
FROM  PG_STAT_ACTIVITY
WHERE QUERY != '<IDLE>'
AND   QUERY NOT ILIKE '%PG_STAT_ACTIVITY%'
ORDER BY QUERY_START DESC;

SELECT

PID

,AGE(CLOCK_TIMESTAMP(), QUERY_START)

,USENAME

,QUERY

FROM PG_STAT_ACTIVITY

WHERE QUERY != '<IDLE>'

AND QUERY NOT ILIKE '%PG_STAT_ACTIVITY%'

ORDER BY QUERY_START DESC;

[DATABASE/POSTGRESQL] 현재 연결 수 구하기

■ 현재 연결 수를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    MAX_CONN
   ,USED
   ,RES_FOR_SUPER
   ,MAX_CONN-USED-RES_FOR_SUPER AS RES_FOR_NORMAL
FROM (SELECT COUNT(1) USED              FROM PG_STAT_ACTIVITY)                                          T1
    ,(SELECT SETTING::INT RES_FOR_SUPER FROM PG_SETTINGS WHERE NAME=$$superuser_reserved_connections$$) T2
    ,(SELECT SETTING::INT MAX_CONN      FROM PG_SETTINGS WHERE NAME=$$max_connections$$)                T3;

SELECT

MAX_CONN

,USED

,RES_FOR_SUPER

,MAX_CONN-USED-RES_FOR_SUPER AS RES_FOR_NORMAL

FROM (SELECT COUNT(1) USED FROM PG_STAT_ACTIVITY) T1

,(SELECT SETTING::INT RES_FOR_SUPER FROM PG_SETTINGS WHERE NAME=$$superuser_reserved_connections$$) T2

,(SELECT SETTING::INT MAX_CONN FROM PG_SETTINGS WHERE NAME=$$max_connections$$) T3;

[DATABASE/POSTGRESQL] 현재 연결 수 구하기

■ 현재 연결 수를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    CLIENT_ADDR
   ,STATE
   ,COUNT(1)
FROM PG_STAT_ACTIVITY
GROUP BY
    CLIENT_ADDR
   ,STATE
ORDER BY COUNT(1) DESC;

SELECT

CLIENT_ADDR

,STATE

,COUNT(1)

FROM PG_STAT_ACTIVITY

GROUP BY

CLIENT_ADDR

,STATE

ORDER BY COUNT(1) DESC;

[DATABASE/POSTGRESQL] 현재 연결 수 구하기

■ 현재 연결 수를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT COUNT(1) FROM PG_STAT_ACTIVITY;

SELECT COUNT(1) FROM PG_STAT_ACTIVITY;

[DATABASE/POSTGRESQL] 현재 연결 수 구하기

■ 현재 연결 수를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT COUNT(DISTINCT(NUMBACKENDS)) FROM PG_STAT_DATABASE;

SELECT COUNT(DISTINCT(NUMBACKENDS)) FROM PG_STAT_DATABASE;

[DATABASE/POSTGRESQL] 1분 이상 실행되는 쿼리 조회하기

■ 1분 이상 실행되는 쿼리를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    CURRENT_TIMESTAMP - QUERY_START AS RUNTIME
   ,DATNAME
   ,USENAME
   ,QUERY
FROM  PG_STAT_ACTIVITY
WHERE STATE = 'active'
AND   CURRENT_TIMESTAMP - QUERY_START > '1 MIN'
ORDER BY 1 DESC;

SELECT

CURRENT_TIMESTAMP - QUERY_START AS RUNTIME

,DATNAME

,USENAME

,QUERY

FROM PG_STAT_ACTIVITY

WHERE STATE = 'active'

AND CURRENT_TIMESTAMP - QUERY_START > '1 MIN'

ORDER BY 1 DESC;

[DATABASE/POSTGRESQL] 현재 실행중인 SQL 상태 정보 조회하기

■ 현재 실행중인 SQL 상태 정보를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    CURRENT_TIMESTAMP - QUERY_START AS RUNTIME
   ,DATNAME
   ,USENAME
   ,QUERY
FROM  PG_STAT_ACTIVITY
WHERE STATE = 'active'
ORDER BY 1 DESC;

SELECT

CURRENT_TIMESTAMP - QUERY_START AS RUNTIME

,DATNAME

,USENAME

,QUERY

FROM PG_STAT_ACTIVITY

WHERE STATE = 'active'

ORDER BY 1 DESC;

[DATABASE/POSTGRESQL] ACTIVE 세션 정보 조회하기

■ ACTIVE 세션 정보를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
   DATNAME
  ,USENAME
  ,STATE
  ,QUERY
FROM  PG_STAT_ACTIVITY
WHERE STATE = 'active';

SELECT

DATNAME

,USENAME

,STATE

,QUERY

FROM PG_STAT_ACTIVITY

WHERE STATE = 'active';

[DATABASE/POSTGRESQL] 접속 사용자 목록 조회하기

■ 접속 사용자 목록을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    PID
   ,DATNAME
   ,USENAME
   ,QUERY
FROM PG_STAT_ACTIVITY;

SELECT

PID

,DATNAME

,USENAME

,QUERY

FROM PG_STAT_ACTIVITY;

[DATABASE/POSTGRESQL] 인덱스 정보 조회하기

■ 인덱스 정보를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    A.TABLENAME
   ,A.INDEXNAME
   ,B.COLUMN_NAME
FROM  PG_CATALOG.PG_INDEXES      A
     ,INFORMATION_SCHEMA.COLUMNS B
WHERE A.SCHEMANAME = 'public'
AND   A.TABLENAME = B.TABLE_NAME
ORDER BY
    A.TABLENAME ASC
   ,A.INDEXNAME ASC
   ,B.COLUMN_NAME;

SELECT

A.TABLENAME

,A.INDEXNAME

,B.COLUMN_NAME

FROM PG_CATALOG.PG_INDEXES A

,INFORMATION_SCHEMA.COLUMNS B

WHERE A.SCHEMANAME = 'public'

AND A.TABLENAME = B.TABLE_NAME

ORDER BY

A.TABLENAME ASC

,A.INDEXNAME ASC

,B.COLUMN_NAME;

[DATABASE/POSTGRESQL] 테이블 목록 조회하기

■ 테이블 목록을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT TABLE_NAME
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'public'
ORDER BY TABLE_NAME ASC;

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'public'

ORDER BY TABLE_NAME ASC;

[DATABASE/POSTGRESQL] 테이블 컬럼 정보 조회하기

■ 테이블 컬럼 정보를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT *
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'public'
ORDER BY
    TABLE_CATALOG    ASC
   ,TABLE_SCHEMA     ASC
   ,TABLE_NAME       ASC
   ,ORDINAL_POSITION ASC
   ,COLUMN_NAME      ASC

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = 'public'

ORDER BY

TABLE_CATALOG ASC

,TABLE_SCHEMA ASC

,TABLE_NAME ASC

,ORDINAL_POSITION ASC

,COLUMN_NAME ASC

[DATABASE/POSTGRESQL] 데이터베이스 매개 변수 조회하기

■ 데이터베이스 매개 변수를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    NAME
   ,SETTING
   ,BOOT_VAL
   ,RESET_VAL
   ,UNIT
FROM  PG_SETTINGS
ORDER BY NAME;

SELECT

NAME

,SETTING

,BOOT_VAL

,RESET_VAL

,UNIT

FROM PG_SETTINGS

ORDER BY NAME;

[DATABASE/POSTGRESQL] pg_restore.exe 프로그램 : 데이터베이스 복원하기

■ pg_restore.exe 프로그램을 사용해 데이터베이스를 복원하는 방법을 보여준다. 1. [명령 프롬프트]를 실행한다. 2. 아래 명령을 실행해 해당 디렉토리로 이동한다. ▶ 실행 명령

[DATABASE/POSTGRESQL] pg_restore.exe 프로그램 : 백업 파일 내용 조회하기

■ pg_restore.exe 프로그램을 사용해 백업 파일 내용을 조회하는 방법을 보여준다. 1. [명령 프롬프트]를 실행한다. 2. 아래 명령을 실행해 해당 디렉토리로 이동한다. ▶

[DATABASE/POSTGRESQL] pg_dump.exe 프로그램 : 데이터베이스 백업하기

■ pg_dump.exe 프로그램을 사용해 데이터베이스를 백업하는 방법을 보여준다. 1. [명령 프롬프트]를 실행한다. 2. 아래 명령을 실행해 해당 디렉토리로 이동한다. ▶ 실행 명령

[DATABASE/POSTGRESQL] psql.exe 프로그램 : 데이터베이스 복원하기

■ psql.exe 프로그램을 사용해 데이터베이스를 복원하는 방법을 보여준다. 1. [명령 프롬프트]를 실행한다. 2. 아래 명령을 실행해 해당 디렉토리로 이동한다. ▶ 실행 명령

[DATABASE/POSTGRESQL] pg_dump.exe 프로그램 : 데이터베이스 백업하기

[DATABASE/POSTGRESQL] INTERVAL 키워드 : 최근 5분 이내 데이터 조회하기

■ INTERVAL 키워드를 사용해 최근 5분 이내 데이터를 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT *
FROM   JOB
WHERE  REQUEST_TIME BETWEEN CURRENT_TIMESTAMP - INTERVAL '50 MIN' AND CURRENT_TIMESTAMP
ORDER BY REQUEST_TIME DESC;

SELECT *

FROM JOB

WHERE REQUEST_TIME BETWEEN CURRENT_TIMESTAMP - INTERVAL '50 MIN' AND CURRENT_TIMESTAMP

ORDER BY REQUEST_TIME DESC;

[DATABASE/POSTGRESQL] DATE_TRUNC 함수 : 다음 달 마지막 날짜 구하기

■ DATE_TRUNC 함수를 사용해 다음 달 마지막 날짜를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT DATE_TRUNC('MONTH', CURRENT_DATE + INTERVAL '2 MONTHS')::DATE - 1;

SELECT DATE_TRUNC('MONTH', CURRENT_DATE + INTERVAL '2 MONTHS')::DATE - 1;

[DATABASE/POSTGRESQL] GENERATE_SERIES 함수 : 해당 월 날짜의 주차 구하기

■ GENERATE_SERIES 함수를 사용해 해당 월 날짜의 주차를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    TARGET_DATE               AS "날짜"
   ,TO_CHAR(TARGET_DATE, 'W') AS "주차"
FROM
(
    SELECT DATE_TRUNC('MONTH', CURRENT_DATE)::DATE + I AS TARGET_DATE
    FROM GENERATE_SERIES(0, 30) AS T(I)
) A
WHERE EXTRACT(MONTH FROM TARGET_DATE) = EXTRACT(MONTH FROM CURRENT_DATE);

SELECT

TARGET_DATE AS "날짜"

,TO_CHAR(TARGET_DATE, 'W') AS "주차"

FROM

(

SELECT DATE_TRUNC('MONTH', CURRENT_DATE)::DATE + I AS TARGET_DATE

FROM GENERATE_SERIES(0, 30) AS T(I)

) A

WHERE EXTRACT(MONTH FROM TARGET_DATE) = EXTRACT(MONTH FROM CURRENT_DATE);