PostgreSQL Query

스키마, Table 생성 및 파티셔닝 설정

---

스키마 생성

• 현재 연결된 데이터베이스에 스키마 생성

    CREATE SCHEMA {SCHEMA_NAME}
  

  • pg_로 시작하는 스키마명 사용 불가
  • CREATE 권한이거나 슈퍼 유저일 경우 스키마 생성 가능

table 생성

• timestamp 기준으로 partition 설정한 table 생성

  CREATE TABLE {TABLE_ID} (
  idx serial, 
  {COLUMN_NAME} {DATA_TYPE}, 
  timestamp TIMESTAMP
  ) partition by range(timestamp);
  

파티셔닝 설정

1. partition 설정 위한 pg_partman 확장 사용 적용

    CREATE EXTENSION pg_partman SCHEMA public;
   

   • table 생성 확인
     • original_table
     • orders
     • old_nonpartitioned_table
     • part_config
     • part_config_sub
     • spatial_ref_sys
     • template_public_original_table
2. partition 적용 - partition 사용할 메인 table 생성

    CREATE TABLE {TABLE_ID} (
      idx serial,
      {COLUMN_NAME} {DATA_TYPE},
      {COLUMN_NAME} {DATA_TYPE},
      timestamp TIMESTAMP
    ) partition by range(timestamp);
   

3. partition 적용 - Index 생성

    CREATE INDEX ON {SCHEMA_NAME}.{TABLE_ID} (idx, timestamp); 
   

   • 추후 생성되는 partition에 자동 적용
4. partition 적용 - table에 partition 적용

    SELECT {SCHEMA_NAME}.create_parent('{SCHEMA_NAME}.{TABLE_ID}', 'timestamp', 'native', 'daily', p_template_table:= '{SCHEMA_NAME}.{TABLE_ID}', p_premake := 15, p_start_partition := (CURRENT_TIMESTAMP)::text);
   
    UPDATE {SCHEMA_NAME}.part_config SET retention_keep_table = false, retention = '6 month' WHERE parent_table = '{SCHEMA_NAME}.{TABLE_ID}';
   

   • {SCHEMA_NAME}.{TABLE_ID}
     • partition 기준 table
   • timestamp
     • partition 기준 column
   • partition 타입
     • native 선언적 partition (파티셔닝 관리 용이. 속도 상승 및 partition pruning 자동)
     • partman 상속 partition (구버전, table 따로 관리, index, vaacuum 등 개별 table로 구분)
   • daily
     • partition 기준 시간
   • retention
     • table 유지 시간
   • retention_keep_table
     • true 삭제된 table 유지, false 삭제된 table 완전 삭제
   • p_premake
     • partition 생성 table 개수
   • p_start_partition
     • partition 시작지점
5. partition 적용 - partition table 생성

    CREATE TABLE {SCHEMA_NAME}.{TABLE_ID}_p2022_09_01 PARTITION OF {SCHEMA_NAME}.{TABLE_ID} FOR VALUES FROM ('2022-09-01 00:00:00') TO ('2022-09-02 00:00:00');
   

   • 하루 단위로 partition 테이블 생성
6. partition 자동 관리 설정
   • partition 자동 관리, 특정 Table 지정 가능

     SELECT run_maintenance();
     SELECT run_maintenance('{SCHEMA_NAME}.{TABLE_ID}');
     

     • 특정 table 지정 가능 (PostgreSQL 직접 실행, GCP CloudSQL 적용 안됨)
   • time 기준 table 자동 생성

     SELECT partition_data_time('{SCHEMA_NAME}.{TABLE_ID}');
     

     • 데이터 없으면 table 생성 불가, GCP CloudSQL 적용 가능)
   • time 기준 table 자동 삭제

     SELECT drop_partition_time('{SCHEMA_NAME}.{TABLE_ID}')
     

     • retention 옵션 값으로 table 삭제, GCP CloudSQL 적용 가능)

인덱싱 설정

---

btree 인덱싱

• single coulmn 인덱싱 설정

  CREATE INDEX {INDEX_NAME} ON only {SCHEMA_NAME}.{TABLE_ID} USING btree({COLUMN_NAME});
  

• 인덱싱 삭제

  DROP INDEX {INDEX_NAME};
  

Table 및 Coulmn 수정/삭제

---

table 및 coulmn 변경

• coulmn 명 변경

    ALTER TABLE {TABLE_ID} RENAME COLUMN {SRC_COLUMN} TO {TGT_COLUMN};
  

• table 명 변경

    ALTER TABLE {SCHEMA_NAME}.{SRC_TABLE_ID} RENAME TO {TGT_TABLE_ID};
  

• coulmn 데이터 타입 변경

    ALTER TABLE {SCHEMA_NAME}.{TABLE_ID} ALTER COLUMN {COLUMN_NAME} TYPE {DATA_TYPE};
  

• coulmn 추가

    ALTER TABLE {SCHEMA_NAME}.{TABLE_ID} ADD {COLUMN_NAME} {DATA_TYPE} NULL;
    ALTER TABLE {SCHEMA_NAME}.{TABLE_ID} ADD {COLUMN_NAME} {DATA_TYPE} NULL, ADD {COLUMN_NAME} {DATA_TYPE} NULL;
  

table 및 coulmn 삭제

• coulmn 삭제

    ALTER TABLE {SCHEMA_NAME}.{TABLE_ID} DROP COLUMN {COLUMN_NAME};
    ALTER TABLE {SCHEMA_NAME}.{TABLE_ID} DROP COLUMN {COLUMN_NAME}, DROP COLUMN {COLUMN_NAME};
  

• table 데이터 삭제

    TRUNCATE TABLE {SCHEMA_NAME}.{TABLE_ID};
  

  
  
  

데이터 조회

---

조회

• 테이블 전체 데이터 조회

  SELECT * FROM {TABLE_ID} LIMIT 100; -- 해당 테이블 데이터 100개 조회
  SELECT * FROM {TABLE_ID} ORDER BY {COLUMN_NAME} DESC -- 정렬 기준 칼럼에 대해 내림차순 조회
  SELECT * FROM {TABLE_ID} ORDER BY {COLUMN_NAME} ASC -- 정렬 기준 칼럼에 대해 오름차순 조회
  

• 테이블 인덱스 정보 조회

  SELECT * FROM pg_catalog.pg_indexes WHERE tablename = '{TABLE_ID}';
  

계정 설정

---

계정 생성

• 현재 사용자 정보 확인

    SELECT * FROM PG_USER;
  

• 계정 생성

    CREATE USER {USER_ID} PASSWORD '{USER_PASSWORD}';
  

• 유저 생성 확인

    SELECT * FROM pg_user WHERE usename IN ('{USER_ID}');
  

계정 및 비밀번호 수정

• 계정명 수정

    ALTER USER {USER_ID} WITH PASSWORD '{USER_PASSWORD}';
  

• 비밀번호 수정

    ALTER USER {USER_ID} WITH PASSWORD '{USER_PASSWORD}';
  

권한 설정

---

DB 권한

• Grantor
  • 권한을 부여한 role
• Grantee
  • 권한을 부여받은 role
• Table_schema
  • schema명
• Table_name
  • table명
• Privilege_type
  • 권한의 유형
• Is_grantable
  • 권한을 줄 수 있을 경우 YES, 아닐 경우 NO
• 권한 확인

    SELECT * FROM information_schema.role_table_grants WHERE grantee ='{USER_ID}'
  

• DB 접근 권한 할당

    GRANT CONNECT ON DATABASE {DB_NAME} TO {USER_ID};
  

schema 권한 설정

• schema 모든 권한 할당

    GRANT ALL ON SCHEMA {SCHEMA_NAME} TO {USER_ID};
  

• schema 엑세스 권한 할당

    GRANT USAGE ON SCHEMA {SCHEMA_NAME} TO {USER_ID};
  

• schema SELECT 권한 할당

    GRANT SELECT ON ALL TABLES IN SCHEMA {SCHEMA_NAME} TO {USER_ID}; -- Single Schema
  
    GRANT SELECT ON ALL TABLES IN SCHEMA {SCHEMA_NAME_1}, {SCHEMA_NAME_2} TO {USER_ID}; -- Multi Schema
  

table 권한 설정

• 앞으로 생길 table에 all 권한

    ALTER DEFAULT PRIVILEGES IN SCHEMA {SCHEMA_NAME} GRANT ALL PRIVILEGES ON TABLES TO {USER_ID}; -- Single Schema
  
    ALTER DEFAULT PRIVILEGES IN SCHEMA {SCHEMA_NAME_1}, {SCHEMA_NAME_2} GRANT ALL PRIVILEGES ON TABLES TO {USER_ID}; -- Multi Schema
  

• table SELECT 권한 할당

    GRANT SELECT ON TABLE {SCHEMA_NAME}.{TABLE_ID} TO {USER_ID};
  

• 앞으로 생길 table에 SELECT 권한

    ALTER DEFAULT PRIVILEGES IN SCHEMA {SCHEMA_NAME} GRANT SELECT ON TABLES TO {USER_ID}; -- Single Schema
  
    ALTER DEFAULT PRIVILEGES IN SCHEMA {SCHEMA_NAME_1}, {SCHEMA_NAME_2} GRANT SELECT ON TABLES TO {USER_ID}; -- Multi Schema
  

• 특정 table 권한

    GRANT ALL ON TABLE {SCHEMA_NAME}.{TABLE_NAME} TO {USER_ID};
  

전체 권한 설정

• all 권한

    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA {SCHEMA_NAME} TO {USER_ID}; 
  
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA {SCHEMA_NAME} TO {USER_ID};
      
    ALTER DEFAULT PRIVILEGES GRANT USAGE ON SEQUENCES TO {USER_ID};
  

프로세스 확인

---

프로세스 kill

• 특정 프로세스 kill

    SELECT * FROM pg_stat_activity WHERE state = 'active';
    SELECT pg_cancel_backend({PROCESS_ID});
  

Reference

• pg_partman/pg_partman.md at master · pgpartman/pg_partman
• pg_partman/pg_partman_howto_native.md at master · pgpartman/pg_partman