PostgreSQL

1. ROLE 1-1. ROLE 생성 -- 기본 CREATE ROLE jonathan LOGIN; -- 비밀번호 포함 CREATE USER davide WITH PASSWORD 'jw8s0F4'; -- 권한 포함 CREATE ROLE admin WITH CREATEDB CREATEROLE; -- 사용 기한 포함 CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01'; -- 삭제 DELETE ROLE miriam; -- Synopsis CREATE ROLE name [ [ WITH ] option [ ... ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid 1-2. ROLE 이란? CREATE ROLE은 PostgreSQL database cluster에 새로운 ROLE을 추가한다. ROLE은 데이터베이스 object, 권한을 가질 수 있는 엔티티이다. ROLE은 사용방법에 따라 USER, GROUP 혹은 둘다로 간주될 수 있다. CREATEROLE 권한이 있어야지만 사용 가능하다. ALTER ROLE, DELETE ROLE을 통해 권한을 수정, 삭제 가능하다. 1-3. ROLE 권한별 특징 SUPERUSER - 로그인을 제외한 모든 권한 포함 (ex. Role 생성 및 권한 부여) LOGIN - 데이터베이스에 로그인하기 위한 권한 PASSWORD - 로그인 비밀번호 설정 CREATEDB - 데이터베이스 생성 CREATEROLE - ROLE 생성/삭제/수정 REPLICATION - REPLICATION 권한 CONNECTIONLIMIT - 데이터베이스 접속 카운트 INHERIT - ROLE 권한들 상속 2. USER 2-1. USER 생성 -- 기본 CREATE USER jonathan; -- 비밀번호 추가 CREATE USER davide WITH PASSWORD 'jw8s0F4'; -- 만료기한 추가 CREATE USER miriam WITH PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01'; -- 권한 추가 CREATE USER manuel WITH PASSWORD 'jw8s0F4' CREATEDB; --Synopsis CREATE USER name [ [ WITH ] option [ ... ] ] where option can be: SYSID uid | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP groupname [, ...] | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'abstime' 2-2. USER란? CREATE ROLE은 PostgreSQL database cluster에 새로운 User을 추가한다. CREATEUSER 권한이 있어야지만 사용 가능하다. 3. GROUP 3-1. GROUP 생성 --기본 CREATE GROUP staff; --유저 추가 CREATE GROUP marketing WITH USER jonathan, david; --그룹 삭제 DROP GROUP staff; -- Synopsis CREATE GROUP name [ [ WITH ] option [ ... ] ] where option can be: SYSID gid | USER username [, ...] 3-2. GROUP이란? CREATE GROUP은 USER 그룹을 생성한다. SUPERUSER 권한이 있어야지만 생성가능하다. 데이터베이스의 cluster 레벨에 접근 가능하기 위해 GROUP, USER, ROLE은 모두 cluster단에서 정의되어 있다. 4. ROLE, USER, GROUP 차이 ROLE은 Postgresql Database 관련 권한들을 모아 놓은 것으로, 8.1버전부터 USER와 GROUP의 개념이 ROLE로 통합되었다. 현재 버전에서는 USER와 ROLE의 기능은 동일하며, USER는 login 권한이 default, ROLE은 login 권한을 별도로 부여해야 하는 차이점만 있다. CREATE GROUP의 경우 PostgreSQL의 SQL 표준에는 존재하지 않으며, ROLE과 비슷한 개념을 가지고 있다. 참고 ...

PostgreSQL 제약조건 (Constrant)란? 데이터베이스는 데이터 타입 외에 제약조건들을 통해 데이터의 무결성을 유지한다. 제약조건에는 여러 가지 종류가 있으며 DMBS에 마다 다양하지만, 이번 포스트는 PostgreSQL의 5가지 제약 조건들을 설명하겠다. 1. Primary Keys(PK) 2. Foreign Keys(FK) 3. Check 4. Not-null 5. Unique 1. Primary Keys (PK) Primary Keys는 테이블의 각 ROW를 구분하는 유니크한 컬럼 혹은 컬럼의 조합이다. Not null, Unique Constraints의 조합이다. 테이블인 단 1개의 PK만 가질 수 있다. PK 생성 시 Postgresql은 B-tree 인덱스를 자동으로 부여한다. B-tree 인덱스를 사용하기 때문에 컬럼의 조합으로 PK를 설정 시 순서가 중요하다. (상세 내용은 다음 포스트에서 확인이 가능하다.) [PostgreSQL] B-tree 인덱스의 원리 및 특징 ...

1. 트랜잭션(Transaction)이란? 트랜잭션은 데이터베이스에서 실행되는 일련의 작업들이다. 트랜잭션은 데이터베이스의 무결성 및 작업 간 충돌방지, 데이터 검증을 위해 필수적인 요소이다. 단순한 DML 작업의 롤백 용도뿐 아니라, 대용량 데이터 처리의 무결성, 에러발생 시, 여러 유저의 동시작업 등에서 사용된다. 2. 트랜잭션 적용 트랜잭션을 사용하는 커맨드 예제이다. --COMMIT 혹은 ROLLBACK으로 트랜잭션을 종료하지 않으면, 해당 업데이트 건은 데이터베이스에 적용되지 않는다. BEGIN; UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice'; COMMIT; 트랜잭션 COMMIT 전에 다른 유저가 동일한 row의 balance를 업데이트하려고 한다면, 그전 트랜잭션이 commit 혹은 rollback 되는 것을 대기해야하며 이를 lock 상태라고 한다. (Lock의 개념 및 상세는 다음 포스트에 상세하게 정리되어 있다.) ...

1. Vacuum 이란? Vacuum은 postgresql에서 dead tuple이 차지하는 저장공간을 회수한다. 일반적으로 Postgresql에서 update, delete tuple 은 물리적으로 삭제되지 않으며 vacuum이 완료될 때까지 계속 존재한다. (update, delete 시 tuple의 순환은 MVCC 개념에서 확인할 수 있다.) [PostgreSQL] MVCC (Multi-Version Concurrency Control) 그렇기 때문에 특히 자주 업데이트되는 테이블의 경우 주기적인 Vacuum 수행이 필요하다. Vacuum은 특정 테이블에 한해서도 실행이 가능하고, 테이블을 지정하지 않는다면 전체 테이블 (권한을 보유한)에 대해서 실행된다. 2. Vacuum 명령어 -- DB 전체 full vacuum vacuum full analyze; -- DB 전체 간단하게 실행 vacuum verbose analyze; -- 해당 테이블만 간단하게 실행 vacuum analyze [테이블 명]; -- 특정 테이블만 full vacuum vacuum full [테이블 명]; 3. Vacuum 상세 옵션 VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ] where option can be one of: FULL [ boolean ] FREEZE [ boolean ] VERBOSE [ boolean ] ANALYZE [ boolean ] DISABLE_PAGE_SKIPPING [ boolean ] SKIP_LOCKED [ boolean ] INDEX_CLEANUP { AUTO | ON | OFF } PROCESS_MAIN [ boolean ] PROCESS_TOAST [ boolean ] TRUNCATE [ boolean ] PARALLEL integer SKIP_DATABASE_STATS [ boolean ] ONLY_DATABASE_STATS [ boolean ] BUFFER_USAGE_LIMIT [ size ] and table_and_columns is: table_name [ ( column_name [, ...] ) ] 4. Vacuum analyze Vacuum analyze 는 vacuum 후 테이블 별로 analyze를 수행(통계정보 수집)하기에 유지보수에 원활하다. ...

1. MVCC란? 동시성 제어를 위해 lock을 사용하는 대부분의 다른 데이터베이스 시스템과 달리 Postgres는 다중 버전 모델(multiversion model)을 사용하여 데이터 일관성을 유지한다. 각 트랜잭션이 데이터베이스를 쿼리 하는 동안 데이터의 현재 상태에 관계없이 얼마 전의 데이터 스냅샷을 볼 수 있음을 의미한다. 데이터를 쿼리 하기 위해 트랜잭션을 만들었다면 해당 Transaction은 데이터의 스냅샷을 보고 있는 것이다. 동일한 행에 서로 다른 트랜잭션이 동시에 업데이트를 시도할 때, 일관성 없는 데이터가 조회되지 않도록 트랜잭션을 보호하여 각 데이터베이스 세션에 대한 트랜잭션 격리를 제공한다. Multiversion과 Lock model의 주요 차이점은 MVCC에서 데이터 read를 위해 획득한 lock과 데이터 쓰기를 위해 획득한 lock이 충돌하지 않는다는 것이다. (따라서 read와 write는 서로 block 하지 않는다.) 이러한 방식을 통해서 Reading 하는 작업에 대해서 Lock을 걸지 않기에 높은 성능을 얻을 수 있게 된다. ...