이 글에서는 PostgreSQL을 소개하고, 리눅스(Linux®) 환경에서 버전 9.3을 설치하고 구성하는 과정을 단계별로 안내합니다.
소개
PostgreSQL은 세계에서 가장 발전된 오픈소스 관계형 데이터베이스 관리 시스템(RDBMS)입니다. Apple, IMDB, Skype, Uber, Lockheed Martin, Verizon 등 수많은 선도 기업들이 PostgreSQL을 사용하고 있습니다. 이 RDBMS는 1986년 캘리포니아 대학교 버클리(UC Berkeley)의 POSTGRES 프로젝트 일환으로 시작되었으며, 핵심 플랫폼 기준 30년이 넘는 활발한 개발 역사를 자랑합니다.
PostgreSQL은 모든 주요 운영체제에서 실행되며, 2001년부터 ACID 트랜잭션 규격을 준수해 왔습니다. ACID는 다음과 같은 네 가지 요소로 구성됩니다.
- 원자성(Atomicity): 트랜잭션이 전부 성공하거나 전혀 실행되지 않음을 보장합니다.
- 일관성(Consistency): 모든 데이터가 정의된 규칙에 따라 유효한 상태를 유지함을 보장합니다. 여기서 규칙에는 데이터베이스에 적용된 제약 조건(constraints), 연쇄 작업(cascades), 트리거(triggers)가 포함됩니다.
- 고립성(Isolation): 모든 트랜잭션이 서로 독립적으로 실행됩니다. 트랜잭션은 아직 완료되지 않은 다른 트랜잭션의 데이터를 읽을 수 없습니다.
- 지속성(Durability): 트랜잭션이 커밋되면 직후에 시스템 장애가 발생하더라도 해당 데이터는 시스템에 그대로 유지됩니다.
PostGIS라는 인기 있는 지리 공간 데이터베이스 확장 기능 등 강력한 애드온 덕분에 PostgreSQL이 많은 개인과 조직에게 선호되는 오픈소스 관계형 데이터베이스가 된 것은 놀라운 일이 아닙니다.
이미지 출처: https://postgresql-database.blogspot.com/2013/08/postgresql-architecture.html
기술 지원
다음 업체들에서 프로덕션 환경용 지원 SLA를 제공합니다.
- https://www.enterprisedb.com
- https://www.2ndquadrant.com/
- https://www.revsys.com/
- https://imperoit.com/PostgreSQL_Support.htm
지원 버전: Current(12) / 11 / 10 / 9.6 / 9.5 / 9.4
개발 버전: devel
지원 종료 버전: 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2
참고: 본문에서 설치하는 9.3 버전은 현재 공식 지원이 종료되었으므로, 실제 운영 환경에서는 최신 지원 버전 사용을 권장합니다.
설치 및 구성
다음 단계를 따라 Postgres 9.3을 설치하고 구성할 수 있습니다.
리눅스 7.1에 Postgres 9.3 설치하기
Red Hat® Linux 7.1에 Postgres 9.3을 설치하려면 먼저 OS 버전을 확인합니다.
[root@snwdbsolpeprod01 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.1 (Maipo)
빈 폴더 생성하기
데이터베이스 설치용 빈 폴더를 생성합니다.
[root@snwdbsolpeprod01 mnt]# mkdir postt
[root@snwdbsolpeprod01 postt]# pwd
/mnt/postt
RPM 다운로드하기
Postgres 설치를 시작하려면 OS 버전에 맞는 RPM(Red Hat Package Manager) 패키지를 다운로드합니다.
[root@snwdbsolpeprod01 postt]# wget https://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-redhat93-9.3-2.noarch.rpm
RPM 설치하기
다음 명령어로 RPM 패키지를 설치합니다.
[root@snwdbsolpeprod01 postt]# rpm -ivh pgdg-redhat93-9.3-2.noarch.rpm
추가 패키지 설치하기
RPM 설치 후, DB 소프트웨어를 설치하는 추가 PostgreSQL 패키지들을 설치해야 합니다.
[root@snwdbsolpeprod01 postt]# yum install postgresql-contrib.x86_64
[root@snwdbsolpeprod01 postt]# yum install postgresql93-server.x86_64
PGDATA 위치 구성하기
데이터를 저장할 위치를 결정합니다. 기본 위치가 아닌 곳에 데이터를 저장하려면 PostgreSQL 서비스의 sysconfig 파일을 편집하여 PGDATA 인자를 변경합니다.
vi /etc/rc.d/init.d/postgresql
vi /etc/sysconfig/pgsql/postgresql
참고: sysconfig/pgsql 디렉터리에 PostgreSQL 파일이 없다면 새로 생성하고, 아래 예시처럼 데이터 저장 위치를 지정하는 한 줄을 추가하면 됩니다.
[root@snwdbsolpeprod01 pgsql]# cd /etc/sysconfig/pgsql/
[root@snwdbsolpeprod01 pgsql]# vi postgresql
[root@snwdbsolpeprod01 pgsql]# cat postgresql
PGDATA=/mnt/postt
데이터베이스 초기화하기
첫 번째 명령(한 번만 필요)으로 PGDATA 위치에 데이터베이스를 초기화합니다.
service <name> initdb
예를 들어 9.3 버전의 경우 다음과 같습니다.
service postgresql-9.3 initdb
또는
/usr/pgsql-9.3/bin/postgresql93-setup initdb
[root@snwdbsolpeprod01 data]# /usr/pgsql-9.3/bin/postgresql93-setup initdb
Initializing database ... OK
Postgres 자동 시작 설정하기
운영체제 부팅 시 PostgreSQL이 자동으로 시작되도록 하려면 다음 명령어를 사용합니다.
[root@snwdbsolpeprod01 data]# chkconfig postgresql-9.3 on
참고: 요청이 'systemctl enable postgresql-9.3.service'로 전달됩니다.
PostgreSQL 서비스 시작하기
PostgreSQL 서비스를 시작하려면 다음 명령어를 실행합니다.
[root@snwdbsolpeprod01 data]# systemctl start postgresql-9.3.service
데이터베이스 구성하기
다음 예시처럼 postgresql.conf 파일을 수정하여 데이터베이스를 손쉽게 구성할 수 있습니다.
vi /var/lib/pgsql/9.3/data/postgresql.conf
주요 설정 항목은 다음과 같습니다.
listen_address = '*'
port = 15000
max_connections=300
shared_buffers = 8192MB # min 128kB
# (change requires restart)
temp_buffers = 128MB # min 800kB
max_prepared_transactions = 20 # zero disables the feature
log_destination = 'csvlog'
logging_collector = on
log_directory = '/mnt/pgsql/logs'
log_filename = 'postgresql-%a.log'
#------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------
autovacuum = on
# Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
autovacuum_naptime = 10080min # time between autovacuum runs
autovacuum_vacuum_threshold = 1000 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000 # maximum Multixact age
# before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
데이터베이스 접속 설정 구성하기
데이터베이스 접속 설정을 제한하거나 관리하려면 다음 명령어를 실행합니다.
vi /var/lib/pgsql/9.3/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
local all postgres md5
local all postgres ident
# IPv4 local connections:
# IPv6 local connections:
host all all ::1/128 ident
host all all 0.0.0.0/0 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident
방화벽 구성하기
방화벽에서 포트를 열려면 다음 명령어를 실행합니다.
iptables -I INPUT -p tcp --dport 15000 --syn -j ACCEPT
service iptables save
service iptables restart
[root@snwdbsolpeprod01 postt]# service postgresql-9.3 restart
사용자 또는 롤(Role) 생성하기
데이터베이스에 새 사용자 또는 롤을 생성하려면 다음 명령어를 실행합니다.
su – postgres
psql -p 15000
postgres=# CREATE ROLE OCT1 LOGIN
UNENCRYPTED PASSWORD 'test@123'
INHERIT REPLICATION;
테이블스페이스 생성하기
데이터베이스에 새 테이블스페이스를 생성하려면 다음 명령어를 실행합니다.
postgres=# CREATE TABLESPACE OCT1_tablespace
OWNER ilusr
LOCATION '/usrdata/pgsql/data/oct';
데이터베이스 생성하기
새 데이터베이스를 생성하려면 다음 명령어를 실행합니다.
postgres=# CREATE DATABASE OCT1
WITH ENCODING='UTF8'
OWNER=test
LC_CTYPE='en_US.UTF-8'
CONNECTION LIMIT=-1
TABLESPACE=OCT1_tablespace;
기본 관리 명령어
자주 사용하는 기본 관리 명령어들은 다음과 같습니다.
PostgreSQL 중지 및 시작
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data stop
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data start
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data restart
/opt/PostgreSQL/9.3/bin/pg_ctl -D opt/PostgreSQL/9.4/data –m smart stop #wait for complete the transactions
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m fast stop #Immediate stop
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m immediate stop #Abort the DB
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m smart restart
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m fast restart
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m immediate restart
PostgreSQL 버전 확인
postgres=# select version();
특정 데이터베이스 레벨의 활동 확인
select pid,backend_xid,backend_xmin,query from pg_stat_activity ;
테이블 상태 분석
select relname,last_autoanalyze,last_analyze,n_mod_since_analyze from pg_stat_all_tables;
테이블의 물리적 경로 찾기
postgres=# SELECT pg_relation_filepath('testpitr1');
pg_relation_filepath
----------------------
base/13003/16399
[postgres@postgres221 data]$ ls -l /mnt/postt/data/base/13003/16399
-rw------- 1 postgres postgres 256024576 Feb 21 06:36 /mnt/postt/data/base/13003/16399
인스턴스 또는 클러스터 내 스키마 이름 조회
select schema_name from information_schema.schemata;
select nspname from pg_catalog.pg_namespace;
post_gre=# \dn
List of schemas
Name | Owner
--------------+----------
kailash_test | postgres
public | postgres
(2 rows)
인스턴스 또는 클러스터 내 데이터베이스 이름 조회
template1=# select datname from pg_database;
template1
template0
post_gre
template1=# \l
post_gre | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres +
| | | | | kailash_s=CTc/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | |
template1=# select usename from pg_catalog.pg_user;
kailash
kailash_s
postgres
template1=# \du
kailash | | {}
kailash_s | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
결론
PostgreSQL의 슬로건은 "세계에서 가장 진보된 오픈소스 데이터베이스"입니다. 흥미로운 점은 PostgreSQL이 단순한 관계형 데이터베이스가 아니라 객체 관계형(object relational) 데이터베이스라는 사실입니다. 이러한 차별점 덕분에 MySQL, MariaDB, Firebird 같은 다른 오픈소스 SQL 데이터베이스에 비해 몇 가지 장점을 갖습니다. 따라서 온프레미스 RDBMS를 클라우드상의 오픈소스 RDBMS로 마이그레이션할 때 PostgreSQL은 AWS 클라우드 환경에서 확실한 선택지입니다.
이 시리즈의 2부에서는 PostgreSQL의 백업, 복원 및 복구 방법을 다룰 예정입니다.
피드백 탭을 통해 의견을 남기거나 질문할 수 있으며, 지금 바로 채팅으로 문의하실 수도 있습니다.
데이터베이스 서비스에 대해 더 자세히 알아보세요.