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

스키마 생성

현재 연결된 데이터베이스에 스키마 생성
class="highlight">
1
  CREATE SCHEMA {SCHEMA_NAME}
pg_로 시작하는 스키마명 사용 불가
CREATE 권한이거나 슈퍼 유저일 경우 스키마 생성 가능
table 생성
timestamp 기준으로 partition 설정한 table 생성class="highlight">1
2
3
4
5
CREATE TABLE {TABLE_ID} (
idx serial, 
{COLUMN_NAME} {DATA_TYPE}, 
timestamp TIMESTAMP
) partition by range(timestamp);
파티셔닝 설정
partition 설정 위한 pg_partman 확장 사용 적용class="highlight">1
 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
partition 적용 - partition 사용할 메인 table 생성class="highlight">1
2
3
4
5
6
 CREATE TABLE {TABLE_ID} (
   idx serial,
   {COLUMN_NAME} {DATA_TYPE},
   {COLUMN_NAME} {DATA_TYPE},
   timestamp TIMESTAMP
 ) partition by range(timestamp);
partition 적용 - Index 생성class="highlight">1
 CREATE INDEX ON {SCHEMA_NAME}.{TABLE_ID} (idx, timestamp); 
추후 생성되는 partition에 자동 적용
partition 적용 - table에 partition 적용class="highlight">1
2
3
 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
timestamppartition 기준 column
partition 타입native 선언적 partition (파티셔닝 관리 용이. 속도 상승 및 partition pruning 자동)
partman 상속 partition (구버전, table 따로 관리, index, vaacuum 등 개별 table로 구분)
dailypartition 기준 시간
retentiontable 유지 시간
retention_keep_tabletrue 삭제된 table 유지, false 삭제된 table 완전 삭제
p_premakepartition 생성 table 개수
p_start_partitionpartition 시작지점
partition 적용 - partition table 생성class="highlight">1
 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 테이블 생성
partition 자동 관리 설정partition 자동 관리, 특정 Table 지정 가능class="highlight">1
2
SELECT run_maintenance();
SELECT run_maintenance('{SCHEMA_NAME}.{TABLE_ID}');
특정 table 지정 가능 (PostgreSQL 직접 실행, GCP CloudSQL 적용 안됨)
time 기준 table 자동 생성class="highlight">1
SELECT partition_data_time('{SCHEMA_NAME}.{TABLE_ID}');
데이터 없으면 table 생성 불가, GCP CloudSQL 적용 가능)
time 기준 table 자동 삭제class="highlight">1
SELECT drop_partition_time('{SCHEMA_NAME}.{TABLE_ID}')
retention 옵션 값으로 table 삭제, GCP CloudSQL 적용 가능)


인덱싱 설정
btree 인덱싱
single coulmn 인덱싱 설정class="highlight">1
CREATE INDEX {INDEX_NAME} ON only {SCHEMA_NAME}.{TABLE_ID} USING btree({COLUMN_NAME});
인덱싱 삭제class="highlight">1
DROP INDEX {INDEX_NAME};


Table 및 Coulmn 수정/삭제
table 및 coulmn 변경
coulmn 명 변경class="highlight">1
  ALTER TABLE {TABLE_ID} RENAME COLUMN {SRC_COLUMN} TO {TGT_COLUMN};
table 명 변경class="highlight">1
  ALTER TABLE {SCHEMA_NAME}.{SRC_TABLE_ID} RENAME TO {TGT_TABLE_ID};
coulmn 데이터 타입 변경class="highlight">1
  ALTER TABLE {SCHEMA_NAME}.{TABLE_ID} ALTER COLUMN {COLUMN_NAME} TYPE {DATA_TYPE};
coulmn 추가class="highlight">1
2
  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 삭제class="highlight">1
2
  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 데이터 삭제class="highlight">1
  TRUNCATE TABLE {SCHEMA_NAME}.{TABLE_ID};


데이터 조회
조회
테이블 전체 데이터 조회class="highlight">1
2
3
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 -- 정렬 기준 칼럼에 대해 오름차순 조회
테이블 인덱스 정보 조회class="highlight">1
SELECT * FROM pg_catalog.pg_indexes WHERE tablename = '{TABLE_ID}';


계정 설정
계정 생성
현재 사용자 정보 확인class="highlight">1
  SELECT * FROM PG_USER;
계정 생성class="highlight">1
  CREATE USER {USER_ID} PASSWORD '{USER_PASSWORD}';
유저 생성 확인class="highlight">1
  SELECT * FROM pg_user WHERE usename IN ('{USER_ID}');
계정 및 비밀번호 수정
계정명 수정class="highlight">1
  ALTER USER {USER_ID} WITH PASSWORD '{USER_PASSWORD}';
비밀번호 수정class="highlight">1
  ALTER USER {USER_ID} WITH PASSWORD '{USER_PASSWORD}';


권한 설정
DB 권한
Grantor권한을 부여한 role
Grantee권한을 부여받은 role
Table_schemaschema명
Table_nametable명
Privilege_type권한의 유형
Is_grantable권한을 줄 수 있을 경우 YES, 아닐 경우 NO
권한 확인class="highlight">1
  SELECT * FROM information_schema.role_table_grants WHERE grantee ='{USER_ID}'
DB 접근 권한 할당class="highlight">1
  GRANT CONNECT ON DATABASE {DB_NAME} TO {USER_ID};
schema 권한 설정
schema 모든 권한 할당class="highlight">1
  GRANT ALL ON SCHEMA {SCHEMA_NAME} TO {USER_ID};
schema 엑세스 권한 할당class="highlight">1
  GRANT USAGE ON SCHEMA {SCHEMA_NAME} TO {USER_ID};
schema SELECT 권한 할당class="highlight">1
2
3
  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 권한class="highlight">1
2
3
  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 권한 할당class="highlight">1
  GRANT SELECT ON TABLE {SCHEMA_NAME}.{TABLE_ID} TO {USER_ID};
앞으로 생길 table에 SELECT 권한class="highlight">1
2
3
  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 권한class="highlight">1
  GRANT ALL ON TABLE {SCHEMA_NAME}.{TABLE_NAME} TO {USER_ID};
전체 권한 설정
all 권한class="highlight">1
2
3
4
5
  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
특정 프로세스 killclass="highlight">1
2
  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
  Database, PostgreSQL
  postgre sql
 Share             
Recently Updated
시간 복잡도란?
분할 정복 알고리즘이란?
[Java 프로그래밍] 9강 - java.io 패키지와 스트림
[파이썬 프로그래밍 기초] 9강 - 함수
[데이터베이스 시스템] 9강 - 저장 객체
Trending Tags
 knou os database python cpp cs data information processing java ubiquitous computing linux
Contents
Further Reading
   2022-10-19 Alicloud MaxCompute Query
 테이블 생성 및 변경 테이블 생성 파티셔닝 설정한 테이블 생성 1 2 3 4 5 6 CREATE TABLE {TABLE_ID} ( `{COLUMN_NAME}` {DATA_TYPE}, `{COLUMN_NAME}` {DATA_TYPE}, `{COLUMN_NAME}` {DATA_TYPE}, `datetime` datetim...
   2025-04-26 [데이터베이스 시스템] 9강 - 저장 객체
 💡해당 게시글은 방송통신대학교 정재화 교수님의 '데이터베이스 시스템' 강의를 개인 공부 목적으로 메모하였습니다. 학습 개요 SQL은 비절차적 언어로 필요한 정보에 대한 조건만 기술할 뿐 구체적으로 처리 과정을 기술하지 못함 보다 데이터에 대한 절차적 처리가 가능하도록 SQL에 프로그래밍 언어와 유사하게 조건문이나 반복문과 같은 기...
   2025-04-30 [데이터베이스 시스템] 10강 - 데이터 저장과 파일
 💡해당 게시글은 방송통신대학교 정재화 교수님의 '데이터베이스 시스템' 강의를 개인 공부 목적으로 메모하였습니다. 학습 개요 현대의 DBMS는 사용자에게 물리적 저장 세부 사항을 숨기고, 논리적 관점에서 데이터를 정의하고 조작할 수 있도록 다양한 추상화 계층을 제공함 이러한 추상화 덕분에 응용 개발자나 일반 사용자는 저장 장치의 구...
 vi 문서 편집 Alicloud AnalyticDB MySQL Query
 © 2025 mxxikr.
Trending Tags
 knou os database python cpp cs data information processing java ubiquitous computing linux
 
      
A new version of content is available.
PostgreSQL Query

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

스키마 생성

table 생성

파티셔닝 설정

인덱싱 설정

btree 인덱싱

Table 및 Coulmn 수정/삭제

table 및 coulmn 변경

table 및 coulmn 삭제

데이터 조회

조회

계정 설정

계정 생성

계정 및 비밀번호 수정

권한 설정

DB 권한

schema 권한 설정

table 권한 설정

전체 권한 설정

프로세스 확인

프로세스 kill

Reference

Further Reading

Alicloud MaxCompute Query

[데이터베이스 시스템] 9강 - 저장 객체

[데이터베이스 시스템] 10강 - 데이터 저장과 파일
