[데이터베이스] PostgreSQL 공부해보기

참고 자료: http://www.gurubee.net/postgresql/basic

PostgreSQL 기본강좌

 

- 소개

 

PostgreSQL은 오픈소스 데이터베이스 중 가장 진보되고 안정적인 데이터베이스이며 ANSI/ISO 규격의 SQL을 지원한다.

PostgreSQL은 Ingres에 뿌리를 두고있고 과거 IBM에서 작성된 RDBMS의 화이트페이퍼를 기반으로 Oracle, DB2 그리고 PostgreSQL이 구현되었기 때문에 MySQL이나 MariaDB와 같은 오픈 소스 제픔과 달리 PostgreSQL의 경우 상요 RDBMS 급의 기능을 제공한다고 할 수 있다.

 

- 특징 - (Portable, Reliable, Scalable, Secure, Recovery&Availability, Advanced)

 

# Portable : PostgreSQL의 ANSI C로 개발되었으며 지원하는 플랫폼의 종류로는 Windows, Linux, MAC OS/X 또는 Unix Platform등 다양한 플랫폼을 지원하고 있다.

 

# Reliable : 트랜잭션 속성인 ACID에 대한 구현 및 MVCC 로우 레벨 라킹 등이 구현되어 있다.

 

# Scalable : PostgreSQL의 멀티-버전에 대하여 사용이 가능하고 대용량 데이터 처리를 위한 Table Partitioning과 Tablespace 기능이 구현 가능하다.

 

# Secure : DB 보안의 경우, 데이터 암호화, 접근 제어 및 감시의 3가지로 구성되어 있고 다야안 보안 방안 등을 지원한다.

 

# Recovery & Availability : Streaming Replication을 기본적으로 동기식, 비동기식의 Hot Standby 서버를 구축할 수 있으며 WAL log 아카이빙 및 Hot Backup을 통해 Point in time recovery를 할 수 있다.

 

# Advanced : pg_upgrade를 이용하여 업그레이드를 진행 할 수 있으며 웹 기반 또는 C/S 기반의 GUI관리 도구를 제공하여 모니터링 및 관리는 물론 튜닝까지 가능하다.

 

- 아키텍처

 

PostgreSQL은 Windows, Linux, Unix 등 다양한 OS를 지원하고 있고 .NET, JDBC 등의 Connector들을 제공하고 있다.

프로세스 기반으로 동작하고 포스트마스터라는 메인 프로세스가 있고 자식 프로세스로 유틸리티 프로세스가 실행되어 동작된다.

 

- 구조

postgreSQL은 우리가 알던 DB와 조금 다른 구조가 있다. 중간에 스키마라고 하는 계층이 존재한다.

최상위 계층에 Database라고 하는 레이어가 있다. 우리가 흔히 아는 데이터베이스이다. 이 데이터베이스는 물리적으로 데이터를 구분해 놓은 것이다.

데이터베이스 아래에는 스키마라는 계층이 위치한다. 데이터베이스를 생성하면 기본적으로 public 스키마가 존재한다. 스키마 레벨은 데이터베이스 내에서 논리적으로 구분된 계층이다. 그리고 그 아래에 테이블들이 생성된다.

 

- 설치

 

# 윈도우: 윈도우용 설치 파일을 다운로드 받은 후 실행 파일을 이용해 진행한다. DB에 접속할 서비스 포트 번호를 설정할 수 있고 디폴트는 5432이다. 추가적으로 Stack Builder라는 솔루션을 제공하고 있고 DB의 설치를 보완하기 위해 추가적인 도구, 드라이버 및 응용 프로그램을 다운로드하고 설치하는 데 사용할 수 있도록 제공 되어지고 있다. DB에 대한 환경 변수 값을 설정하기 위해 pg_env.bat 파일을 실행시켜준다.

 

# 리눅스: 리눅스 비트에 맞는 설치파일을 다운로드 받아서 설치할 OS에 업로드한다. DB를 root 계정으로 설치 진행한다. --ㅡmode 옵션을 통해서 text형식으로 설치를 진행할 수 있다. 설치가 진행되며 엔진을 설치할 디렉토리를 설정하고 데이터를 저장 할 디렉토리를 설정한다. Superuser의 password를 설정하고 port도 설정한다. OS의 postgres 계정에 환경변수를 설정해주기 위해 pg_env.sh 파일을 실행시켜준다. 이후 pg_ctl명령어를 이용하여 서비스를 정지/기도 시킬 수 있다.

 

- PostgreSQL 관리

 

# pgAdmin III는 PostgreSQL을 실행하는 오픈소스 개발 플랫폼이다 간단한 쿼리를 작성하거나 server-side 코드 편집기, scheduling을 할 수 있다. (MySQL WorkBench와 비슷한 거 같다.)

 

# Psql은 PostgreSQL의 CLI이다. cmd 창이 시작되면 Server, Database, Port, Username, Password를 입력하고 접속한다.

psql의 명령어에 대한 정보를 얻고 싶을 때는 \?을 입력하면 명령어에 대한 정보를 얻을 수 있고 SQL에 대한 전보를 얻고 싶을 경우 \h를 입력하면 SQL명령어에 대한 정보를 얻을 수 있다.

서버에 접속되어 있는 DB의 목록을 확인하려면 \I를 누르면 확인할 수 있다.

DB내의 Relation의 정보를 확인하고 싶을 때는 \d를 누르면 확인할 수 있다.

DB내의 System table에 대해서 확인하고 싶을 때는 \ds를 누르면 확인할 수 있다.

DB내의 Table에 대해서 확인하고 싶을 때는 \dt를 누르면 확인할 수 있다.

DB내의 View에 대해서 확인하고 싶을 때는 \dv를 이용하여 확인할 수 있다.

이 외에도 각종 명령어를 지원하고 있다.

 

- PostgreSQl 사용하기

 

-User 관리 (DB에서 Superuser 권한을 가지고 있어야 한다.)

 

# User 조회 : SELECT * FROM PG_SHADOW

# User Role 확인 : \du

# User 생성 : CREATE USER 'username' [ [ WITH ] option [ ... ] ]

username 뒤에는 여러가지 옵션이 추가될 수 있다.

# User 변경 : ALTER USER 'name' [ [ WITH ] option [ ... ] ]

# User 이름 변경 : ALTER USER 'name' RENAME TO 'name'

# User 삭제 : DROP USER 'name'

 

- Datebase / Schema 관리

Database 구조(출처: http://www.gurubee.net/lecture/2942)

Database를 생성학 위해서는 Superuser이거나 CREATEDB권한이 있어야 한다.

 

# Database 조회 :  \I

# Database 생성 : CREATE DATABASE 'name' [ [ WITH ] option [ ... ] ]

# Database 수정 : ALTER DATABASE 'name' [ [ WITH ] option [ ... ] ]

# Database 삭제 : DROP DATABASE 'name'

 

SCHEMA는 Object들의 논리적인 집합을 말한다. SCHEMA는 TABLE, VIEW, SWQUENCE, SYNONYM, DOMAIN, FUNCTION등의 Object들로 구성되어 있다. 사용하는 이유는 논리적 집합체를 만들어서 관리의 편의성을 높이고 여러 USER 들 간의 간섭 없이 접속 할 수 있게 한다.

 

# SCHEMA 조회 : \dn

# SCHEMA 생성 : CREATE SCHEMA 'schemaname' [ AUTHORIZATION user_name ] [ schema_elelment [ ... ] ]

이름을 입력하지 않을 경우 User의 이름이 Schema의 이름으로 사용된다.

AUTHORIZATION 'username' 은 스키마를 소유한 User의 이름을 입력한다.

schema_element [...] 을 입력하여 스키마 내에서 객체를 정의하는 SQL 문을 작성한다. CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER, GRANT 등이 포함될 수 있다.

# SCHEMA 이름 변경 : ALTER SCHEMA 'name' RENAME TO 'new name'

# SCHEMA 오너 변경 : ALTER SCHEMA 'name' OWNER TO 'user_name'

# SCHEMA 삭제 : DROP SCHEMA 'name'

- TABLESPACE 관리

 

TABLESPACE는 PostgreSQL에서 DBA가 DATABASE OBJECT가 저장된 파일 시스템 장소를 정의할 수 있도록 하는 기능이다.

 

# TABLESPACE 조회 : \db

# TABLESPACE 생성 : CREATE TABLESPACE 'tablespace_name' LOCATION '저장 위치'

# TABLESPACE 이름 변경 : ALTER TABLESPACE 'tablespace_name' RENAME TO 'new_name'

# TABLESPACE 삭제 : DROP TABLESPACE 'tablespace_name'

 

 

- 데이터 타입

매우 다양한 데이터 타입을 지원하고 있다.

참고 자료 : http://www.gurubee.net/lecture/2947

- 제약 조건

 

# Check contraints : 가장 일반적인 제약 조건으로 특정 컬럼의 값이 boolean식에 부합하는지를 체크한다.

CREATE TABLE products (
	product_no integer,
	name text,
	price numeric check (price > 0));

 

# Not null : 지정한 컬럼이 null값을 갖지 않아야 하는 조건이다.

CREATE TABLE products (
	product_no integer NOT NULL,
    	name text NOT NULL;
    	price numeric);

 

# Unique : 테이블의 모든 행과 열에 대해서 유일한 것을 보장합니다.

CREATE TABLE products (
	product_no integer UNIQUE,
   	name text,
    	price numeric);

 

# Primar key : 해당 컬럼에 대해 유일한 식별자이고 null이 아닌 것을 primary key 제약 조건이라고 한다.

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric);

 

# Foreign key : 지정한 컬럼이 다른 테이블의 primary key 인 것을 말한다.

CREATE TABLE products (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES products2 (c1, c2));

 

- 테이블 관리

 

# 테이블 생성

CREATE TABLE table_name ( [
   column_name1 data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
  column_name2 data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
  column_name3 data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
  …
] )

 

#  테이블 변경

ALTER TABLE table_name 
[ADD|DROP|ALTER|SET|RENAME] 
    column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ];

컬럼을 추가할 때는 ADD, 컬럼을 삭제할 때는 DROP, 존재하는 컬럼의 데이터 타입을 변경할 때는 ALTER, 테이블을 다른 TABLESPCE, SCHEMA로 이동시킬 때는 SET, 이름을 변경할 때는 RENAME을 사용한다.

 

# 테이블 삭제

DROP TABLE [ IF EXISTS ] table_name [, ...] [ CASCADE | RESTRICT ]

먼저 삭제할 테이블이름을 table_name에 넣는다. 제약조건으로 CASCADE를 사용할 경우 이 테이블과 연관된 view나 개체들도 함께 삭제한다. RESTRICT를 사용할 경우 다른 개체가 종속되어 있을 경우 테이블삭제를 제한한다.

 

- PostgreSQL 기본

# SELECT : 한 개 또는 여러 개의 테이블의 열을 검색할 수 있다. 

SELECT Syntax

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] 
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]

 

DML(Data Manipulation Language)은 데이터 조작어로서 DML에는 INSERT, UPDATE, DELTE등이 있다.

# INSERT : 예시

INSERT INTO products VALUES (1, 'Cheese', 9.99);
INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products (product_no, name, price) VALUES
   (1, 'Cheese', 9.99),
   (2, 'Bread', 1.99),
   (3, 'Milk', 2.99);

 

# UPDATE : 예시

UPDATE products 
   SET price = 10 
 WHERE price = 5;
 ---------------------
 UPDATE products 
   SET price = price * 1.10;
 ---------------------
 UPDATE mytable 
   SET a = 5
     , b = 3
     , c = 1 
 WHERE a > 0;

 

# DELTE : 예시

DELETE FROM products;
-------------------------
DELETE
  FROM products 
 WHERE price = 10;

 

- JOIN 문법 : JOIN은 FROM에서 2개 이상의 테이블을 결합하여 다수의 행을 검색하는 것이다.

 

# JOIN Syntax : T1 join_type T2 [ join_condition ]

# JOIN Type : 

[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
CROSS JOIN

 

- PostgreSQL에서 PostgreSQL DBLink 사용하기

 

서로 다른 DB를 연결하기 위해서는 같은 시스템 내에 있어도 DBLink를 이용하여 연결해야 한다.

DBLink란? 데이터베이스 세션 내에 다른 데이터베이스로의 연결을 지원하는 Extenstion Module을 이야기하며 Extenstion Module을 사용하기 위해서는 해당 모듈을 데이터베이스에 추가하여 사용하게 된다.

 

사용법 : http://www.gurubee.net/lecture/2961

 

- PostgreSQL의 확장 기능, HStore

참고자료 : https://runebook.dev/ko/docs/postgresql/hstore

PostgreSQL - F.18. hstore 이 모듈은 단일 PostgreSQL 내에 키/값 쌍 세트를 저장하기 위한 hstore 데이터 유

 

이 기능은 Key / Value 라는 단순한 구조를 갖는 테이블을 정의할 수 있는 확장이다.

우선 설치된 DB에서 HStore를 한번도 사용하지 않았다면 다음과 같은 명령을 통해서 HStore를 확성화 시켜야 한다.

# HStore 활성화 : CREATE EXTENSION hstore

 

예시를 통해 이해를 해보자.

HStore를 이용해서 간단한 주수록 테이블을 만들어보자.

CREATE TABLE AddressBook (
    id serial PRIMARY KEY,    
    name varchar,
    attributes hstore
);

HStore 타입의 Attributes 필드가 내용의 핵심이다.

INSERT INTO AddressBook (name, attributes) VALUES (
    '오태양',
    'age => 26,
     telephone => "010-1234-5678",
     email  => "abc123@naver.com"'
);
#예시 1

INSERT INTO AddressBook (name, attributes) VALUES (
    '우태양',
    'age => 27,
     telephone => "N/A",
     email  => "def456@naver.com"'
);
#예시 2

Attributes 필드의 값을 입력하는 방식은 다수의 Key, Value에 대한 문자열이다. Key 와 Vaule의 구분은 => 를 사용하고 있다.

 

이렇게 만들어진 데이터셋으로부터 쿼리를 해보자.

SELECT name, attributes FROM AddressBook;

SELECT name, attributes FROM AddressBook WHERE attributes -> 'telephone' = '010-1234-5678';

다음처럼 편리하게 Key / Value 의 형태로 데이터를 저장할 수 있고 조회할 수 있었다.

 

HStore의 각 키는 고유하고 중복 키로 HStore를 선언하면 하나만 HStore에 저장되며 어느 것이 보관되는지에 대한 보장은 없다.

값은 SQL NULL일 수 있지만 키는 NULL일 수 없다.

 

# HStore 연산자

hstore -> key : 주어진 키와 관련된 값을 반환하거나, 없으면 NULL을 반환한다.

hstore -> key[ ] : 주어진 키와 관련된 값을 반환하거나, 없으면 NULL을 반환한다.

hstore || hstore : 두 개의 hstore를 연결 한다.

hstore ? key : hstore 에 키 key가 포함되어 있는가에 대한 값을 반환한다.

hstore ?& key [ ] : hstore에 지정된 모든 키가 포함되어 있는가에 대한 값을 반환한다.

hstore ?| key [ ] : hstore에 지정된 키가 포함되어 있는가에 대한 값을 반환한다.

hstore <@ hstore : 왼쪽 피연산자가 오른쪽에 포함되어 있는가에 대한 값을 반환한다.

hstore - key : 왼쪽 피연산자에서 키를 삭제한다.

hstore - key [ ] : 왼쪽 피연산자에서 키를 삭제한다.

hstore - hstore : 오른쪽 피연산자의 쌍과 일치하는 왼쪽 피연산자의 쌍을 삭제한다.

%% hstore : hstore를 대체 키와 값의 배열로 변환한다.

%#hstore : hstore를 2차원 키/값 배열로 변환한다.

 

나머지 추가적인 내용을 해당 참고자료를 참고하며 사용하도록 하겠습니다.

 

추후에 실제로 사용해보면서 더욱 정리하고 공부해나가도록 하겠습니다.