logo MSJO.kr

PostgreSQL 설치 및 환경설정

2025-01-03
MsJ

Database 시장에서 점유율의 순위는 크게 변화하지는 않았지만, 주목할 만한 것은 PostgreSQL의 상승세이다.1 특히 MySQL의 점유율은 갈수록 하락 추세이다. 물론 MariaDB 사용자를 포함하지 않아서일 수도 있다.2 오라클의 장점을 가지면서도 오픈소스로써 무료 데이터베이스인 PostgreSQL의 설치와 설정 과정, 튜닝포인트를 알아보고 MVCC(multiversion concurrency control)의 차이점 또한 간략하게 정리하였다.

테스트를 사용한 환경은 Windows WSL2에 Ubuntu 24.04 LTS 버전을 설치하여 진행했다.3 ?>는 Shell 프롬프트이다.

기본적인 설치
?> sudo apt install postgresql postgresql-contrib
?> sudo systemctl status postgresql
?> sudo systemctl enable/disable postgresql
?> sudo systemctl start postgresql
?> sudo -i -u postgres psql
\password postgres #패스워드
외부 접속 설정
?> sudo vi /etc/postgresql/14/main/postgresql.conf
isten_addresses = '*'
?> sudo vi /etc/postgresql/14/main/pg_hba.conf
host all all 0.0.0.0/0 md5 #추가 또는 수정
템블릿(DB) 삭제 방지(옵션)
# postgres로 로그인한 상황에서
update pg_database set datistemplate=true where datname='template0';
update pg_database set datistemplate=true where datname='template1';
신규 DB, 사용자 생성
#사용자 생성
create user mypg password 'password' superuser;

#테이블스페이스 생성
?> mkdir -p /pg_tablespace
?> sudo chown postgres:postgres /pg_tablespace 

# postgres로 로그인한 상황에서
create tablespace mytablespace owner mypg location '/pg_tablespace/';
create database mypgdb owner mypg tablespace mytablespce;

# 시작 및 종료
?> sudo systemctl status postgresql # 상태확인
?> sudo systemctl start postgresql # 시작
?> sudo systemctl stop postgresql # 종료
?> ps aux | grep postgres # 동작확인
설정(튜닝) 포인트

PostgreSQL은 동시성 제어를 위해 MVCC를 사용한다. 동시성 제어를 높이기 위해서  ’읽기 작업은 쓰기 작업을 블로킹하지 않고, 쓰기 작업은 읽기 작업을 블로킹하지 않아야 한다.’ 이때 필요한 게 MVCC 이다.4 PostgreSQL은 트랜잭션을 식별하기 위해 4바이트 정수인 트랜잭션 ID(XID)를 사용한다. 이 값은 시간이 지남에 따라 증가하며, 특정 포인트에서 ‘래핑’ 또는 오버플로가 발생할 수 있다. AutoVacuum으로 이 문제를 방지하기 위해 오래된 트랜잭션 정보를 정리한다.5

AutoVacuum 자동 설정
# postgresql.conf
autovacuum = on
autovacuum_naptime = 60s             
autovacuum_max_workers = 4	#16G 4, 32, 64 4~6           
autovacuum_vacuum_threshold = 10000    
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_threshold = 5000   
autovacuum_analyze_scale_factor = 0.01 
autovacuum_vacuum_cost_limit = 1000 
autovacuum_vacuum_cost_delay = 10ms 
log_autovacuum_min_duration = 5000
데이터베이스 설정
# 8코어, 32G 메모리
max_connections = 200		
shared_buffers = 8GB		
work_mem = 128MB
maintenance_work_mem = 2GB
effective_cache_size = 24GB
effective_io_concurrency = 8 # SSD 사용 시 8, HDD 사용 시 2
random_page_cost = 1.1 # SSD 사용 시 1.1, HDD 사용 시 4    
seq_page_cost = 1.0
wal_buffers = 128MB
default_statistics_target = 100
#huge_pages = on # 가능하면 on 부팅 안되면 try, off
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 8 # CPU 코어 수와 동일하게 설정 
max_parallel_workers_per_gather = 4
max_parallel_workers = 8 # max_worker_processes와 동일하게 설정  
max_parallel_maintenance_workers = 4
checkpoint_completion_target = 0.9
메모리 16G, 64G 기본 설정 예
# 공통으로 CPU 코어은 8개로 가정
# 16G
max_connections = 100
shared_buffers = 4GB
work_mem = 64MB
maintenance_work_mem = 1GB
effective_cache_size = 12GB
effective_io_concurrency = 4  
random_page_cost = 1.1      
seq_page_cost = 1.0
wal_buffers = 64MB
default_statistics_target = 100
#huge_pages = on      
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8  
max_parallel_workers_per_gather = 2
max_parallel_workers = 8  
max_parallel_maintenance_workers = 2
checkpoint_completion_target = 0.9

# 64G
max_connections = 400
shared_buffers = 16GB
work_mem = 256MB
maintenance_work_mem = 4GB
effective_cache_size = 48GB
effective_io_concurrency = 8 
random_page_cost = 1.1     
seq_page_cost = 1.0
wal_buffers = 256MB
default_statistics_target = 100
#huge_pages = on           
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 8  
max_parallel_workers_per_gather = 4
max_parallel_workers = 8    
max_parallel_maintenance_workers = 4
checkpoint_completion_target = 0.9
참고, 전체 설정(8코어, 32G)
#postgresql.conf

data_directory = '/var/lib/postgresql/16/main'	
hba_file = '/etc/postgresql/16/main/pg_hba.conf'
ident_file = '/etc/postgresql/16/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/16-main.pid'

listen_addresses = '*'		    
port = 5432				
unix_socket_directories = '/var/run/postgresql'
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
dynamic_shared_memory_type = posix	

max_connections = 200
shared_buffers = 8GB
work_mem = 128MB
maintenance_work_mem = 2GB
effective_cache_size = 24GB
effective_io_concurrency = 8 
random_page_cost = 1.1 
seq_page_cost = 1.0
wal_buffers = 128MB
default_statistics_target = 100
# huge_pages = on  
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 8   
max_parallel_workers_per_gather = 4
max_parallel_workers = 8   
max_parallel_maintenance_workers = 4
checkpoint_completion_target = 0.9

autovacuum = on
autovacuum_naptime = 60s             
autovacuum_max_workers = 4	#16G 4, 32, 64 4~6           
autovacuum_vacuum_threshold = 10000    
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_threshold = 5000   
autovacuum_analyze_scale_factor = 0.01 
autovacuum_vacuum_cost_limit = 1000 
autovacuum_vacuum_cost_delay = 10ms 
log_autovacuum_min_duration = 5000  

log_line_prefix = '%m [%p] %q%u@%d '
log_timezone = 'Asia/Seoul'
log_destination = 'stderr'
logging_collector = on 
log_directory = '/pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d 
log_rotation_size = 100MB  
log_autovacuum_min_duration = 5000

cluster_name = '16/main'
datestyle = 'iso, mdy'
timezone = 'Asia/Seoul'
lc_messages = 'C.UTF-8'	
lc_monetary = 'C.UTF-8'
lc_numeric = 'C.UTF-8'
lc_time = 'C.UTF-8'	
default_text_search_config = 'pg_catalog.english'
include_dir = 'conf.d'

참고로 MariaDB 11 버전에 대한 설치도 간략하게 정리해 보았다.

MariaDB 11
?> curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-11.4"

# 기본적인 설정(root패스워드 초기설정)
?> mariadb-secure-installation

# 데이터메이스 TestDB를 만든 후 root로 로그인 후
?> grant all privileges on TestDB.* to '사용자'@'%' identified by '패스워드';

# 외부접속 허용
?> sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
# bind-address=127.0.0.1 # 주석처리

# 기본설정
?> sudo vi /etc/mysql/my.cnf
skip-name-resolve # 접속부하 줄이기(IP기반 빠른 접속)

innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 10
innodb_thread_concurrency = 8
  • MariaDB 설정(참고)
# 공통
skip-name-resolve

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 8코어, 32G
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

max_connections = 500
thread_cache_size = 100
table_open_cache = 2000
query_cache_size = 0
query_cache_type = 0
tmp_table_size = 256M
max_heap_table_size = 256M
key_buffer_size = 512M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
join_buffer_size = 8M

# 8코어, 16G
innodb_buffer_pool_size = 12G  # 메모리의 약 75% 할당
innodb_buffer_pool_instances = 4  # 코어 수에 맞게 설정
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

max_connections = 300
thread_cache_size = 50
table_open_cache = 1000
query_cache_size = 0
query_cache_type = 0
tmp_table_size = 128M
max_heap_table_size = 128M
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 4M

# 8코어, 64G
innodb_buffer_pool_size = 48G 
innodb_buffer_pool_instances = 16  
innodb_log_file_size = 2G
innodb_log_buffer_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

max_connections = 1000
thread_cache_size = 200
table_open_cache = 4000
query_cache_size = 0
query_cache_type = 0
tmp_table_size = 512M
max_heap_table_size = 512M
key_buffer_size = 1G
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 32M
join_buffer_size = 16M

rastalion.dev 웹사이트에 ‘MySQL 8.0 vs PostgreSQL 16: 심층 비교 분석’으로 MySQL과 PostgreSQL의 비교를 잘 정리한 글이 있어 일부를 소개한다.

측면 MySQL 8.0 PostgreSQL 16
성능 높은 읽기/쓰기 성능, 특히 단순 쿼리에서 우수 복잡한 쿼리와 대규모 데이터 처리에서 우수한 성능
확장성 제한적, 특히 대규모 동시 연결 처리에서 한계 우수한 확장성, 대규모 동시 연결 처리에 강점
기능 기본적인 RDBMS 기능에 충실 고급 기능 다수 제공 (예: JSON 지원, 테이블 상속)
데이터 무결성 기본적인 제약 조건 지원 강력한 데이터 무결성 기능 (예: CHECK 제약조건)
설정 및 관리 간편한 설정과 관리 초기 설정과 최적화가 상대적으로 복잡
에코시스템 광범위한 도구와 리소스 지원 성장 중인 에코시스템, 일부 도구 호환성 제한
클라우드 지원 우수한 클라우드 서비스 통합 (예: AWS RDS) 클라우드 지원 개선 중, 일부 제한적
MVCC 구현 언두 로그 사용, 효율적인 구현 테이블 내 다중 버전 저장, 데드 튜플 관리 필요
조인 성능 Nested Loop 조인 중심, 작은 테이블에 효과적 다양한 조인 알고리즘, 대규모 조인에 효과적
인덱싱 클러스터드 인덱스 사용, 효율적인 검색 다양한 인덱스 유형 지원, 복잡한 쿼리에 유리

참고로 lime.log에 올라온 ‘PostgreSQL 이야기1 - 우버의 PostgreSQL에서 MySQL로 전환사례’는 읽어볼 필요가 있다. 위의 이슈에 대하여 다음과 같은 해결 방법을 생각해 볼 수 있다.

  • Write Amplification은 데이터베이스가 실제 데이터보다 더 많은 데이터를 디스크에 쓰는 현상을 의미한다.
  # Autovacuum 설정을 조정하여 불필요한 쓰기 작업을 줄인다. 
  SET autovacuum_vacuum_scale_factor = 0.2; 
  SET autovacuum_analyze_scale_factor = 0.1;
  
  # WAL (Write-Ahead Logging) 설정을 튜닝하여 쓰기 증폭을 줄인다.
  SET wal_level = 'replica'; 
  SET max_wal_size = '1GB'; 
  SET min_wal_size = '80MB';
  
  # 테이블 파티셔닝을 하여 쓰기 작업을 분산시킨다.
  CREATE TABLE tableName (
  logdate DATE NOT NULL, 
  ... 컬럼들... 
  ) PARTITION BY RANGE (logdate);
  • Replication 마스터-슬레이브 구조로 구성하는 복제를 사용
# Streaming Replication 설정 : 마스터 서버 설정
wal_level = replica 
max_wal_senders = 10 
archive_mode = on 
archive_command = 'cp %p /path/to/archive/%f'

# 슬레이브 서버 설정 
primary_conninfo = 'host=master_host port=5432 user=replication password=your_password' restore_command = 'cp /path/to/archive/%f %p' 
recovery_target_timeline = 'latest'

# Logical Replication : 동기화 실행
# 마스터 퍼블리케이션
CREATE PUBLICATION my_pub FOR TABLE my_table;
# 슬레이브 구독
CREATE SUBSCRIPTION my_sub CONNECTION 'host=master_host dbname=mydb user=replication password=your_password' PUBLICATION my_pub;
  • Replica MVCC를 효율적으로 처리하기 위한 해결책
# Hot Standby 설정 : 복제본에서 읽기 작업 허용
hot_standby = on
# 슬레이브 서버에서 쿼리 튜닝
SET max_parallel_workers_per_gather = 4;
Reference
  1. 뜨는 포스트그레SQL, 지는 MySQL…DB 시장 지형 변화, 왜?, 디지털데일리, 2024-01-17, https://m.ddaily.co.kr/page/view/2024011705473798270, 2025-01-03
  2. 지난 12개월 동안 어떤 데이터베이스를 사용하셨나요?, Jetbrains 개발자 에코시스템, 2023, https://www.jetbrains.com/ko-kr/lp/devecosystem-2023/databases/, 2025-01-03
  3. learn.microsoft.com, “WSL을 사용하여 Windows에 Linux를 설치하는 방법”
  4. todayscoding.tistory.com, “# Oracle 과 PostgreSQL의 차이점”
  5. youngjun0627.log, “Postgresql - AutoVacuum 에 대하여”
Prεv(Θld)   Nεxt(Nεw)
Content
Search     RSS Feed     BY-NC-ND