PostgreSQL Command - goddes4/python-study-wiki GitHub Wiki

SQL 테스트 사이트

http://sqlfiddle.com/

함수 생성

CREATE OR REPLACE FUNCTION custom_random(start_number integer, end_number integer)
RETURNS integer
LANGUAGE plpgsql
STRICT
AS $BODY$
    BEGIN
        RETURN trunc(random() * ((end_number + 1) - start_number) + start_number);
    END;
$BODY$;

#\df
#SELECT custom_random(1,5);

함수 삭제

DROP FUNCTION custom_random(start_number integer, end_number integer);

스키마 복제

스키마 / 데이터 모두 복제

# pg_dump -U username -n source_schema dbname | sed 's/source_schema/dest_schema/g' | psql -U username -d dbname

스키마 만 복제

# pg_dump -s -U username -n source_schema dbname | sed 's/source_schema/dest_schema/g' | psql -U username -d dbname

데이터 만 복제

# pg_dump -a -U username -n source_schema dbname | psql -U username -d dbname -n 

스키마 생성

CREATE SCHEMA schema_name AUTHORIZATION username;

스키마 이름 변경

ALTER SCHEMA schema_name RENAME TO new_schema_name;

스키마 소유자 변경

ALTER SCHEMA username OWNER TO new_username;

스키마 삭제

DROP SCHEMA schema_name CASCADE;

VACUUM

DB 전체 풀 실행

vacuum full analyze;

DB 전체 간단하게 실행

vacuum verbose analyze;

해당 테이블만 간단하게 실행

vacuum analyse [테이블 명];

특정 테이블만 풀 실행

vacuum full [테이블명];

튜플 정보 실행

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid) as total_tuple,
    pg_stat_get_live_tuples(c.oid) AS live_tuple,
    pg_stat_get_dead_tuples(c.oid) AS dead_tupple,
    round(100*pg_stat_get_live_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as live_tuple_rate,
    round(100*pg_stat_get_dead_tuples(c.oid) / (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid)),2) as dead_tuple_rate,
    pg_size_pretty(pg_total_relation_size(c.oid)) as total_relation_size,
    pg_size_pretty(pg_relation_size(c.oid)) as relation_size
FROM pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
WHERE pg_stat_get_live_tuples(c.oid) > 0
AND c.relname NOT LIKE 'pg_%'
ORDER BY dead_tupple DESC;

물리적 파일 위치 조회

SELECT oid, pg_relation_filepath(oid), relname, relfilenode FROM pg_class LIMIT 10;

사용하지 않는 인덱스 확인

SELECT
    schemaname AS schema_name,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;