시작하기

설치

PostgreSQL 9.6 on CentOS 6.9

Install the repository RPM

↓shell

$ yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Install the client packages

↓shell

$ yum install postgresql96

Install the server packages

↓shell

$ yum install postgresql96-server

Initialize the database

↓shell

$ service postgresql-9.6 initdb

서비스 시작

↓shell

$ service postgresql-9.6 start $ service postgresql-9.6 restart # 재시작하는 경우

자동 구동 설정

↓shell

$ chkconfig postgresql-9.6 on

PostgreSQL 10 on CentOS 7

패키지 설치

↓shell

$ rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm $ yum install -y postgresql10-server postgresql10

DB 초기화

↓shell

$ /usr/pgsql-10/bin/postgresql-10-setup initdb

서비스 시작

↓shell

$ systemctl start postgresql-10 $ systemctl status postgresql-10 // 상태 확인

자동 구동 설정

↓shell

$ systemctl enable postgresql-10

서비스 시작 후, 실행 위치 확인

↓shell

$ ps aux | grep postgres

실행 구조

postgres라는 이름의 서버 프로세스가 항시 실행되면서 클라이언트의 연결 요청 시 이를 처리하고, 연결 수립 시 하위 프로세스를 생성하여 할당한다

데이터베이스 사용하기

사용 방법

psql : PostgreSQL 대화형 터미널 프로그램
pgAdmin : PostgreSQL GUI 클라이언트 프로그램

백업 - pg_dump

↓text

$ pg_dump [connection-option...] [option...] # 접속 중인 유저들을 방해하지 않고 백업한다 # 평문(plain text)으로 백업하는 경우, 임의 위치에서 psql로 복원 가능 # 다른 포맷으로 백업하는 경우, 복원에 pg_restore를 이용해야 한다 # 포맷이 directory인 경우(-Fd)에만 병렬 백업이 가능하다 Options: -a, --data-only 데이터만 백업 -C, --create 데이터베이스 생성 쿼리도 포함 -n, --schema=SCHEMA 지정 스키마만 포함. 와일드카드(*) 사용 가능 -N, --exclude-schema=SCHEMA 지정 스키마 제외. 와일드카드(*) 사용 가능 -s, --schema-only 스키마만 백업 -t, --table=TABLE 지정 테이블만 포함. 와일드카드(*) 사용 가능 -T, --exclude-table=TABLE 지정 테이블 제외. 와일드카드(*) 사용 가능 --column-inserts dump data as INSERT commands with column names --disable-dollar-quoting disable dollar quoting, use SQL standard quoting --inserts dump data as INSERT commands, rather than COPY --quote-all-identifiers quote all identifiers, even if not key words

백업 - pg_dumpall

전체 DB를 하나의 스크립트 텍스트 파일로 백업

복원

↓shell

db_name=# \i filename $ psql -f filename $ psql < filename $ pg_restore [connection-option...] [option...] [filename]

psql을 이용한 데이터베이스 사용

↓shell

psql [options...] [dbname [username]]

접속할 DB와 유저가 명시되지 않은 경우 현재 명령어를 실행하는 유저로, 같은 이름의 DB에 접속 시도
처음 PostgreSQL을 설치하면, "postgres"를 이름으로 하는 메인 유저와 데이터베이스가 생성
postgres 유저의 최초 패스워드는 알 수 없다
postgres 유저를 이용하는 것은 보안상 좋지 않으므로 프로젝트 별로 계정을 할당하는 것이 좋다

options

-c command : 명령 하나만 실행 후 종료
-d dbname : 데이터베이스 지정
-e : 쿼리 에코 출력
-h hostname : 원격지 설정
-p port : 포트 설정
-U username : 유저 설정
-s : 명령을 하나씩 확인하면서 실행

psql 사용자 관련

postgres 계정으로 db 접속

↓shell

$ su - [postgres] $ psql postgres

사용자 추가

↓shell

$ adduser username postgres=# create user username;

사용자 암호 설정

↓shell

postgres=# \password username postgres=# alter user username with password 'new_password';

유저 비밀번호를 잊은 경우

pg_hba.conf 파일을 비밀번호 없이 접속할 수 있도록 trust로 변경 후 접속(서비스 재시작 필요)
비밀번호 설정 후 pg_hba.conf 다시 원복

DB 생성 후 권한 부여

↓shell

postgres=# create database db_name ENCODING 'UTF-8'; postgres=# GRANT ALL PRIVILEGES ON DATABASE db_name TO username;

테이블 권한 부여

단일 테이블 권한 부여

↓sql

grant all privileges on table_name to user;

스키마 내 전체 테이블 권한 부여

↓sql

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;

스키마 내 전체 함수 권한 부여

↓sql

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user;

신규 테이블 기본 권한 변경

↓sql

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;

실제로 적용됐는지 확인

↓sql

select * from information_schema.role_table_grants where table_name = 'table_name';

psql 명령어

↓sql

-- 사용 가능한 데이터베이스 리스트 db_name=# \l -- 데이터베이스 연결 db_name=# \c db_name -- 테이블 리스트. 처럼 패턴 사용 가능 + \dv, ... db_name=# \dt *account* -- 테이블, 뷰 등 여러가지 리스트. 마찬가지로 패턴 사용 가능 db_name=# \d -- describe table, view, sequence, or index db_name=# \d name -- file_name으로 출력 db_name=# \o file_name -- file_name 실행 db_name=# \i file_name -- 접속 종료 db_name=# \q

외부 접속 허용

/etc/sysconfig/iptables 규칙 추가

규칙 추가

PostgreSQL 서버의 포트(--dport 5432)로 들어오는(INPUT) tcp 패킷(-p tcp)을 허용(ACCEPT)하는 규칙을 추가(-A).

strong 규칙의 순서가 중요하므로, 앞선 규칙에서 패킷이 폐기되지 않도록 주의

↓shell

$ iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

확인

↓shell

$ iptables -nL --line-numbers

iptable 재시작

↓shell

$ service iptables restart

/home/postgres/pgsql/data/postgres.conf 수정

↓postgres.conf

# 변경 전 listen_addresses='localhost' #port=5432 # 변경 후 listen_addresses='*' port=5432

/home/postgres/pgsql/data/pg_hba.conf

↓pg_hba.conf

# 보통 서브넷을 특정 192.168.0.0/24 # 전체 접근 가능 0.0.0.0/0 + host all all 0.0.0.0/0 md5 # 모든 네트워크에 대해 MD5방식 로그인 요구 + host all all 0.0.0.0/0 trust # 암호 요구 없이 접속 허용 + local all all md5 # 로컬 서버에서도 암호를 이용해 접속하도록 변경

PostgreSQL 서버 restart

timezone 변경

postgresql.conf의 log_timezone, timezone 설정 변경
SELECT pg_reload_conf();
alter database db_name set timezone to 'Asia/Seoul';

외부 DB 연결 이용

dblink 사용 설정

↓sql

CREATE EXTENSION dblink;

dblink 재사용

↓sql

SELECT dblink_connect('myconn', 'hostaddr=1.2.3.4 port=5432 dbname=history user=readonly password=password'); (SELECT player_id, last_login_time FROM v_account WHERE player_id IN (SELECT r.player_id FROM dblink('myconn', 'SELECT player_id, sum(amount) as total_amount FROM v_payment_history WHERE status=2 GROUP BY player_id HAVING sum(amount) >= 10000') AS r(player_id BIGINT, total_amount BIGINT)) ) UNION ...

SQL 언어

클러스터 : 하나의 서버가 관리하는 데이터베이스 집합 단위

테이블 생성, 삭제

생성

↓sql

CREATE TABLE t_account ( player_id bigint NOT NULL, lv integer NOT NULL, nickname text NOT NULL, start_game_date timestamp with time zone NOT NULL );

psql 안에서 각 명령은 ;로 끝나야 한다
-- : 한 줄 주석
복사 생성

↓sql

create table t_account_0 ( like t_account including all );

삭제

↓sql

DROP TABLE table_name;

테이블에 자료 입력하기

↓sql

-- 숫자가 아닌 자료형은 ''안에 적는다 INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); -- 여러 개 입력 시 콤마로 구분 INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29'), ('San Francisco', 43, 57, 0.0, '1994-11-29'); -- SELECT and INSERT insert into t_account_old select * from t_account_0; -- SELECT and INSERT order by with tmp as (delete from test returning *) insert into test select * from tmp order by v; -- COPY from file COPY weather FROM '/home/user/weather.txt'; -- COPY from CSV file COPY weather FROM '/home/user/weather.txt' WITH csv; -- COPY from STDIN COPY t_account_0 (player_id, lv, nickname, start_game_date) FROM stdin; 1\t1\t'test'\t2017-03-31 12:06:06.446+09 2\t2\t'test2'\t2017-03-31 12:06:06.446+09 \.

UPSERT

↓sql

insert into table_name(col1, col2, col3) values (val1, val2, val3) on conflict (col1) do update set col2 = val2, col3 = val3 where table_name.col1 = val1; insert into table_name(col1, col2, col3) values (val1, val2, val3) on conflict on constraint table_name_pkey do update set col2 = val2, col3 = val3 where table_name.col1 = val1;

테이블의 자료 조회하기

↓sql

SELECT * FROM weather; SELECT city, (temp_hi+temp_lo)/2 AS temp_avg FROM weather; SELECT * FROM weather WHERE city = 'San Francisco' AND prop > 0.0; SELECT * FROM weather ORDER BY city, temp_lo; SELECT DISTINCT city FROM weather; -- Cartesian product SELECT * FROM weather w, cities c WHERE w.city = c.name; SELECT weather.city, cities.location FROM weather, cities WHERE cities.name = weather.city; -- JOIN SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name); SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name); -- LEFT OUTER JOIN : 왼쪽 릴레이션 기준으로, 오른쪽 릴레이션에 만족하는 것이 없으면 null로 채운다 -- + RIGHT OUTER JOIN, FULL OUTER JOIN -- SELF JOIN : 자기 자신과의 조인 SELECT w1.city, w1.temp_lo AS low, w2.city, w2.temp_lo AS low FROM weather w1, weather w2 WHERE w1.temp_lo < w2.temp_lo;

집계 함수

↓sql

SELECT max(temp_lo) FROM weather; SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather); SELECT city, max(temp_lo) FROM weather GROUP BY city; SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40; SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%' GROUP BY city;

자료 갱신

↓sql

UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28';

자료 삭제

↓sql

DELETE FROM weather WHERE city = 'Hayward'; DELETE FROM weather;

고급 기능

뷰

↓sql

CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;

외래키

↓sql

CREATE TABLE cities ( city varchar(80) primary key, location point ); CREATE TABLE weather ( city varchar(80) references cities(city), temp_lo int, temp_hi int, prcp real, date date );

트랜잭션

트랜잭션의 시작과 끝은 BEGIN, COMMIT으로 구분하며, 이를 생략한 각 쿼리는 자동으로 감싸진다. 일부 클라이언트 라이브러리는 자동으로 BEGIN, COMMIT 명령을 포함하여, 사용자가 트랜잭션을 지정하면 오류를 내는 경우도 있다
ROLLBACK : 도중에 문제가 생겨 지금까지 작업한 내역을 모두 취소하고 트랜잭션 종료
SAVEPOINT : 중간 지점 설정. 한 중간 지점으로 회귀할 경우, 그 이후의 중간 지점은 모두 사라진다

↓sql

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice'; SAVEPOINT my_savepoint; UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob'; -- ... Wally한테 가야하는 건데... ROLLBACK TO my_savepoint; UPDATE accounts SET balance = balance + 100 WHERE name = 'Wally'; COMMIT;

윈도우 함수

행 집합을 대상으로 계산하는 함수. 집계 함수는 행 집합에 대한 하나의 행으로 결과를 보여주지만, 윈도우 함수는 각 행 단위로 결과를 출력한다
윈도우 함수 뒤에는 항상 OVER 절을 사용한다. OVER 절은 윈도우 함수의 대상이 되는 행 집합을 규정한다
PARTITION BY는 그룹을 정의한다. 이를 생략할 경우 전체 행이 하나의 그룹으로 취급된다

↓sql

-- 부서별 평균 임금과 각 직원의 급여 비교 SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

ORDER BY로 그룹 내 정렬 순서를 지정할 수 있다

↓sql

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

윈도우 프레임 : 현재 윈도우 함수가 처리하는 행집합. 많은 윈도우 함수들이 윈도우 프레임 단위로 계산한다

↓sql

-- ORDER BY로 인한 윈도우 프레임 차이 SELECT salary, sum(salary) OVER () FROM empsalary; SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

윈도우 함수의 처리 대상은 미리 결정되어야 한다

SELECT 항목 영역 안에서와 ORDER BY 절에서만 사용할 수 있다

집계 함수의 결과를 윈도우 함수의 입력으로 사용할 수는 있지만 그 반대는 불가능하다
윈도우 함수의 결과에 대한 검색, 재집계가 필요하다면 서브 쿼리를 사용한다

↓sql

-- 각 부서에서 최상위 3명 임금만 출력 SELECT depname, empno, salary, enroll_date FROM ( SELECT depname, empno, salary, enroll_date, rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos FROM empsalary ) AS ss WHERE pos <= 3; -- rank 함수의 대상은 OVER에서 특정되므로 생략되었다

같은 윈도우 프레임을 이용하는 경우, alias를 이용할 수 있다

↓sql

SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARITION BY depname ORDER BY salary DESC);

상속

객체지향 데이터베이스에서 사용하는 개념. 테이블을 상속하면 해당 테이블의 모든 열을 갖게된다. 다중 상속이 가능하다

↓sql

-- 도시 - 수도 CREATE TABLE cities ( name text, population real, altitude int -- (피트 단위) ); CREATE TABLE capitals ( state char(2) ) INHERITS (cities);

테이블을 조회할 경우, 모든 하위 테이블도 대상이 되며, 해당 테이블만 조회하려면 ONLY 키워드를 이용한다

↓sql

-- 수도를 빼고 도시 고도 500ft 초과의 도시 검색 SELECT name, altitude FROM ONLY cities WHERE altitude > 500;