Computer >> 컴퓨터 >  >> 시스템 >> Linux

CentOS/RHEL에서 PostgreSQL 11 설치부터 성능 튜닝까지 완벽 가이드

이 글에서는 Linux CentOS 7에 PostgreSQL 11을 설치하고 기본 구성을 수행하는 방법을 단계별로 살펴봅니다. 아울러 주요 설정 파일 파라미터와 성능 튜닝 방법까지 함께 다룹니다. PostgreSQL은 널리 사용되는 무료 객체 관계형 데이터베이스 관리 시스템(DBMS)으로, MySQL/MariaDB만큼 대중적이지는 않지만 가장 전문적이고 강력한 오픈소스 데이터베이스로 평가받고 있습니다.

PostgreSQL의 주요 장점

  • SQL 표준 완전 준수
  • MVCC(다중 버전 동시성 제어) 기반의 뛰어난 성능
  • 높은 확장성 (고부하 환경에서 폭넓게 활용)
  • 다양한 프로그래밍 언어 지원
  • 안정적인 트랜잭션 및 복제 메커니즘
  • JSON 데이터 타입 지원

CentOS/RHEL에 PostgreSQL 설치하기

기본 CentOS 저장소에서도 PostgreSQL을 설치할 수 있지만, 항상 최신 패키지 버전을 제공하는 개발자 저장소(PostgreSQL 공식 저장소)를 추가해 설치하는 것이 좋습니다.

먼저 PostgreSQL 저장소를 추가합니다:

# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

이 저장소에는 최신 버전과 이전 버전의 PostgreSQL이 모두 포함되어 있습니다.

yum 명령으로 PostgreSQL 11을 설치합니다:

# yum install postgresql11-server -y

PostgreSQL 서버와 필수 라이브러리가 함께 설치됩니다:

Installing : libicu-50.2-3.el7.x86_64 1/4
Installing : postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4
Installing : postgresql11-11.5-1PGDG.rhel7.x86_64 3/4
Installing : postgresql11-server-11.5-1PGDG.rhel7.x86_64 4/4

데이터베이스 초기화 및 서비스 등록

패키지 설치가 완료되면 데이터베이스를 초기화해야 합니다:

# /usr/pgsql-11/bin/postgresql-11-setup initdb

이후 systemctl을 사용해 PostgreSQL 데몬을 활성화하고 부팅 시 자동 시작되도록 등록합니다:

# systemctl enable postgresql-11
# systemctl start postgresql-11

서비스 상태를 확인합니다:

# systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2020-10-18 16:02:15 +06; 26s ago
Main PID: 8719 (postmaster)
CGroup: /system.slice/postgresql-11.service
├─8719 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─8721 postgres: logger
├─8723 postgres: checkpointer
├─8724 postgres: background writer
├─8725 postgres: walwriter
├─8726 postgres: autovacuum launcher
├─8727 postgres: stats collector
└─8728 postgres: logical replication launcher

방화벽 포트 개방

외부에서 PostgreSQL에 접속하려면 기본 CentOS firewalld에서 TCP 5432 포트를 열어야 합니다:

# firewall-cmd --get-active-zones
public
interfaces: eth0
# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload

iptables를 사용하는 환경이라면 다음과 같이 설정합니다:

# iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
# service iptables restart

SELinux가 활성화되어 있다면 아래 명령을 실행합니다:

# setsebool -P httpd_can_network_connect_db 1

psql로 데이터베이스 생성, 사용자 추가, 권한 부여하기

PostgreSQL을 설치하면 기본적으로 postgres라는 슈퍼유저만 존재합니다. 이 계정을 일상적인 데이터베이스 작업에 사용하는 것은 보안상 권장하지 않으며, 데이터베이스마다 별도의 사용자를 생성하는 것이 바람직합니다.

postgres 서버에 접속하려면 다음 명령을 실행합니다:

# sudo -u postgres psql
psql (11.5)
Type "help" for help.
postgres=#

PostgreSQL 콘솔에 진입했습니다. 이제 psql 콘솔에서 자주 사용하는 관리 명령 몇 가지를 살펴보겠습니다.

기본 postgres 사용자 비밀번호 변경:

ALTER ROLE postgres WITH PASSWORD 's3tPa$$w0rd!';

새 데이터베이스와 사용자를 생성하고 권한 부여:

postgres=# CREATE DATABASE newdbtest;
postgres=# CREATE USER mydbuser WITH password '!123456789';
postgres=# GRANT ALL PRIVILEGES ON DATABASE newdbtest TO mydbuser;

데이터베이스에 접속:

postgres=# \c databasename

테이블 목록 조회:

postgres=# \dt

데이터베이스 접속 현황 조회:

postgres=# select * from pg_stat_activity where datname='dbname'

데이터베이스의 모든 연결 강제 종료:

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname = 'dbname'

현재 세션 정보 확인:

postgres=# \conninfo

psql 콘솔 종료:

postgres=# \q

명령 문법이 MariaDB나 MySQL과 상당히 유사하다는 점을 알 수 있습니다.

참고로 웹 인터페이스에서 PostgreSQL을 더 편리하게 관리하려면 pgAdmin4(Python과 JavaScript/jQuery 기반)를 사용하는 것이 좋습니다. 많은 웹 개발자에게 익숙한 phpMyAdmin과 비슷한 역할을 하는 도구입니다.

PostgreSQL 주요 설정 파라미터

PostgreSQL 구성 파일은 /var/lib/pgsql/11/data 디렉터리에 위치합니다:

  1. postgresql.conf — PostgreSQL의 핵심 구성 파일
  2. pg_hba.conf — 접근 제어 설정 파일. 사용자별 접속 제한이나 데이터베이스 연결 정책을 설정할 수 있습니다.
  3. pg_ident.conf — ident 프로토콜 기반 클라이언트 인증에 사용되는 파일

로컬 사용자가 인증 없이 postgres에 로그인하지 못하도록 하려면 pg_hba.conf에 다음을 지정합니다:

local all all md5
host all all 127.0.0.1/32 md5

postgresql.conf에서 특히 중요한 파라미터들은 다음과 같습니다:

  • listen_addresses — 서버가 클라이언트 연결을 수락할 IP 주소를 지정합니다. 기본값은 localhost로 로컬 연결만 허용됩니다. 모든 IPv4 인터페이스에서 수신하려면 0.0.0.0을 입력합니다.
  • max_connections — DB 서버의 최대 동시 접속 수
  • temp_buffers — 임시 버퍼의 최대 크기
  • shared_buffers — 데이터베이스 서버가 사용하는 공유 메모리 크기. 일반적으로 서버 전체 RAM의 25% 수준으로 설정합니다.
  • effective_cache_size — PostgreSQL 플래너가 디스크 캐싱에 사용 가능한 메모리량을 판단하도록 돕는 파라미터. 보통 서버 전체 RAM의 50~75%로 설정합니다.
  • work_mem — ORDER BY, DISTINCT, 병합(Merge) 같은 내부 정렬 작업에 사용되는 메모리 크기
  • maintenance_work_mem — VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 등 유지보수 작업에 사용되는 메모리 크기
  • fsync — 활성화하면 DBMS가 데이터가 디스크에 물리적으로 기록될 때까지 대기합니다. 시스템·하드웨어 장애 발생 시 데이터베이스 복구가 쉬워지지만 성능은 다소 저하됩니다. 비활성화한다면 full_page_writes도 함께 끄는 것이 좋습니다.
  • max_stack_depth — 최대 스택 크기 (기본값 2MB)
  • max_fsm_pages — 여유 공간 맵(FSM)을 통해 디스크 여유 공간을 관리합니다. 테이블에서 데이터 삭제 시 해당 공간은 즉시 해제되지 않고 FSM에 여유 공간으로 표시된 뒤 재사용됩니다. 데이터 쓰기/삭제가 잦은 서버라면 이 값을 크게 설정하면 성능이 향상됩니다.
  • wal_buffers — WAL 데이터를 보관하는 데 사용되는 공유 메모리 크기
  • wal_writer_delay — WAL을 디스크에 기록하는 주기 사이의 대기 시간
  • commit_delay — 트랜잭션이 WAL 버퍼에 기록된 후 디스크에 반영되기까지의 지연 시간
  • synchronous_commit — WAL 데이터가 디스크에 물리적으로 기록된 후에야 트랜잭션 성공 결과를 반환하도록 설정하는 파라미터

PostgreSQL 데이터베이스 백업 및 복원

PostgreSQL 데이터베이스는 여러 방법으로 백업할 수 있으며, 여기서는 가장 간단한 방법을 소개합니다.

먼저 서버에 존재하는 데이터베이스 목록을 확인합니다:

postgres=# \list

4개의 데이터베이스가 표시되며, 이 중 postgres와 template 계열은 시스템 데이터베이스입니다. 앞서 생성한 mydbtest 데이터베이스를 백업해 보겠습니다.

pg_dump 도구를 사용하면 손쉽게 백업할 수 있습니다:

# sudo -u postgres pg_dump mydbtest > /root/dupm.sql

postgres 사용자로 명령을 실행하고, 대상 데이터베이스와 덤프 파일을 저장할 경로를 지정합니다. 백업 시스템이 이 덤프 파일을 가져가도록 하거나, 웹 서버 환경이라면 클라우드 스토리지로 전송하는 자동화도 가능합니다.

덤프를 데이터베이스로 복원할 때는 psql을 사용합니다:

# sudo -u postgres psql mydbtest < /root/dupm.sql

특수 덤프 형식(-Fc)으로 백업을 생성해 gzip으로 압축된 형태로 관리할 수도 있습니다:

# sudo -u postgres pg_dump -Fc mydbtest > /root/dumptest.sql

이 형식의 덤프는 pg_restore 도구로 복원합니다:

# sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql

PostgreSQL 성능 튜닝 및 최적화

MariaDB에서는 my.cnf 파일을 튜너 프로그램으로 최적화할 수 있었습니다. PostgreSQL에도 PgTune이라는 도구가 있었지만, 안타깝게도 오랫동안 업데이트되지 않았습니다. 대신 온라인에서 바로 사용할 수 있는 설정 최적화 서비스가 여럿 있으며, 그중 PGTune(pgtune.leopard.in.ua)이 특히 유용합니다.

사용법은 매우 간단합니다. 서버 사양(프로파일, CPU 코어 수, 메모리, 디스크 유형)을 입력하고 "Generate" 버튼을 누르면, 주요 PostgreSQL 파라미터의 권장값이 담긴 postgresql.conf 설정을 자동으로 생성해 줍니다.

예를 들어 4GB RAM, 4 vCPU 사양의 SSD VPS 서버에 권장되는 설정은 다음과 같습니다:

# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 4 GB
# CPUs num: 4
# Connections num: 100
# Data Storage: ssd
max_connections = 100
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

물론 이것이 유일한 도구는 아닙니다. 유사한 서비스로 다음과 같은 것들이 있습니다:

  • Cybertec PostgreSQL Configurator
  • PostgreSQL Configuration Tool

이런 서비스를 활용하면 서버 하드웨어와 용도에 맞는 기본 파라미터를 빠르게 구성할 수 있습니다. 이후에는 서버 리소스뿐 아니라 실제 데이터베이스의 운영 상태, 데이터 크기, 접속 수 등을 분석해 PostgreSQL 설정을 더욱 세밀하게 조정하면 됩니다.