[PostgreSQL] 기본 강좌 정리 01

[PostgreSQL] 기본 강좌 정리 01

PostgreSQL 소개

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

PostgreSQL은 Ingres(INteractive Graphics REtrieval System)에 뿌리를 두고 있다

PostgreSQL 특징

Portable

PostgreSQL 은 ANSI C로 개발되었으며 지원하는 플랫폼의 종류로는 Windows, Linux, MAC OS/X 또는 Unix Platform 등이 있다.

Reliable

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

Scalable

postgresql 의 멀티 버전에 대하여 사용이 가능
대용량 데이터 처리를 위한 table partitioning 과 tablespace 기능이 구현 가능

Secure

DB 보안의 경우 데이터 암호화, 접근 제어 및 감사의 3가지로 구성 되어 있으며, PostgreSQL 또한 DB 보안 요구사항에 의해 호스트-기반 접근제어, object-level 권한, SSL 통신을 통한 클라이언트와 네트워크 구간의 전송 데이터를 암호화하는 방안 등을 지원

Recovery & Availability

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

Advanced

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

사용자정의 procedural 로 Perl, java, php 등의 스크립트 언어 지원이 가능하다.

라이선스

PostgreSQL License는 PostgreSQL과 함께 배포되는 라이선스로서, BSD 또는MIT 라이선스와 유사한, 매우 자유로운 라이선스 이다.

관리기관은 [The PostgreSQL Global Development Group] 이며, 라이선스 계열은 BSD 이다.

주요 오픈소스 SW 라이선스 비교

무료 이용가능, 배포 허용가능, 소스코드 취득가능 은 모두 동일함.

구분	2차적 저작물 재공개 의무	독점 SW와 결합가능
GPL	O	X
LGPX	O	O
MPL	O	O
BSD 라이선스	X	O
아파치 라이선스	X	O

PostgreSQL Limits

Limit	Value
Maximum Database Size	Unlimited
Maximum Table Size	32TB
Maximum Row Size	1.6TB
Maximum Field Size	1GB
Maximum Rows per Table	Unlimited
Maximum Columns per Table	50~1600 (Depending on Column types)
Maximum Indexes per Table	Unlimited

PostgreSQL Terminology

Industry Term	PostgresPlus Term
Table or Index	Relation
Row	Tuple
Column	Attribute
Data Block	Page(when block is on disk)
Page	Buffer(when block is memory)

PostgreSQL install 윈도우

윈도우용 설치파일을 다운로드 받은 후 실행파일을 더블 클릭하여 실행한다.
홈페이지 : http://www.postgresql.org/
다운로드 페이지 : http://www.enterprisedb.com/products-services-training/pgdownload#windows

설치 디렉토리 지정 (C:\Program Files\PostgreSQL\9.4)
data 디렉토리 지정 (C:\Program Files\PostgreSQL\9.4\data)
SUPERUSER 패스워드 지정 (Default user : postgres)
접속 포트 지정 (Default: 5432)
PostgreSQL(DB)에 대한 locale 설정

추가적으로 Stack Builder 솔루션을 제공함.
PostgreSQL(DB)의 설치를 보완하기 위해 추가적인 도구, 드라이버 및 응용 프로그램을 다운로드하고 설치하는 데 사용할 수 있도록 제공됨

디렉토리 구조

디렉토리	내용
bin	postgresql(db)에서 사용하는 실행파일 위치
data	DB 의 data 디렉토리
doc	documention 파일 위치
ppg_env.sh	환경변수 설정 파일
include	header files 위치 (Cluster 생성 시에 사용됨)
installer / scripts	Installer files
lib	PostgreSQL(DB)에 필요한 library 위치
pgAdmin3	그래픽 관리자 Tool
stackbuilder	GUI 기반의 업데이트 관리 Tool
uninstall-postgresql / uninstall-postgresql.dat	PostgreSQL 제거를 위한 uninstall 파일

PostgreSQL(DB)에서는 하나의 서버의 인스턴스에서 관리되는 DATABASE의 모음을 cluster 라고 한다.
data 디렉토리 내에 base라는 디렉토리가 위치한다.
base 디렉토리 밑으로 DATABASE가 각각 디렉토리로 생성이 되고, 해당 디렉토리에 테이블이나 인덱스와 같은 오브젝트들이 파일형식으로 저장된다.

DATABASE에 대한 oid 확인

SELECT oid, datname from ppg_database;

oid	datname
1	“template1”
12368	“template0”
12373	“postgres”
16395	“kict”

주요 환경설정 파일

pg_hba.conf : 원격접속을 위한 클라이언트 인증설정 파일

원격지에서의 접속을 위한 클라이언트 인증에 대한 설정을 지정해주는 파일로 type, database, user, address, method 로 구분하여 설정하게 된다.

postgresql.conf : PostgreSQL(db) 튜닝과 환경설정 파일

PostgreSQL 에 대한 환경설정 값에 대해서 PostgreSQL.conf 파일을 이용하여 설정할 수 있다.
PostgreSQL(DB)의 port 정보나 address(IP) 값을 설정해 줄 수 있으며 DB 에 대한 튜닝 또한 이 파일에서 관리할 수 있다.

pgAdminⅢ

pgAdminⅢ 는 PostgreSQL 을 실행하는 오픈소스 개발 플랫폼이다. 간단한 SQL 쿼리를 작성하거나 server-side 코드편집기, SQL/batch/shell job scheduling을 함

상단메뉴, object browser, information browser, SQL pane 으로 크게 네 부분으로 나뉘어 있다.

상단메뉴

Object browser

Object browser 는 서버그룹을 보여주는 창으로써 Server, Databases, Tablespaces, Group Roles, Login Roles 등을 보여준다.

Psql 명령어

Psql은 PostgreSQL 의 Command Line Interface 입니다.

명령어	설명
\?	psql 명령어 정보
\h	SQL 명령어 정보
\l	서버에 접속되어 있는 DATABASE 목록
\d	Database 내의 Relation 정보
\d+	Relation 에 대한 더 자세한 정보
\dS	Database 내의 System table
\dt	Database 내의 table
\dv	Databae 내의 view
\dl	Large object
\di	인덱스
\dn	schema
\c [DBNAME]	다른 Database 에 접속
\q	psql 종료
\e	Query 수정 및 실행
\set [name] [value]	Variable 선언

Psql에는 세팅을 변경할 수 있는 특별한 Variable이 있다.

AUTOCOMMIT, ENCODING, HISFILE, ON_EFFOR_ROLLBACK, ON_ERROR_STOP, PROMPT1, VERBOSITY 등이 special variable 이다.

\\set AUTOCOMMIT off

출처

• http://www.gurubee.net/lecture/2939
• http://www.gurubee.net/lecture/2887
• http://d2.naver.com/helloworld/227936
• http://www.gurubee.net/lecture/2889
• http://www.gurubee.net/lecture/2915
• http://www.gurubee.net/lecture/2929
• http://www.gurubee.net/lecture/2930