[PostgreSQL] PostgreSQL 정리

PostgreSQL 이란

PostgreSQL은 오픈 소스 객체-관계형 데이터베이스 시스템(ORDBMS)으로, Enterprise급 DBMS의 기능과 차세대 DBMS에서나 볼 수 있을 법한 기능들을 제공한다. 35년 이상의 활발한 개발을 통해 안정성, 기능 견고성 그리고 다른 관계형 데이터베이스 시스템과 달리 연산자, 복합 자료형, 집계 함수, 자료형 변환자, 확장 기능 등 다양한 데이터베이스 객체를 사용자가 임의로 만들 수 있는 기능을 제공함으로써 마치 새로운 하나의 프로그래밍 언어처럼 무한한 기능을 손쉽게 구현할 수 있다.

장점

1. 오픈 소스: PostgreSQL은 무료로 사용 가능한 오픈 소스 소프트웨어입니다. 상업용 라이선스 비용 없이 사용할 수 있으며, 커뮤니티의 활발한 지원과 기여를 받고 있습니다.
2. ACID 준수: 트랜잭션의 원자성, 일관성, 고립성, 지속성을 보장하는 ACID 속성을 완전히 준수하여 데이터 무결성과 신뢰성을 높입니다.
3. 다양한 데이터 타입 지원: 정수, 문자, 날짜와 같은 기본 타입뿐만 아니라, JSON, XML, hstore, 배열, 범위 타입 등 다양한 데이터 타입을 지원합니다.
4. 확장성: 사용자 정의 데이터 타입, 함수, 연산자, 집계 함수 등을 추가할 수 있으며, PostGIS, Full Text Search 등 여러 확장 기능을 제공합니다.
5. 복잡한 쿼리 처리: 하위 쿼리, 조인, 윈도우 함수, 재귀적 CTE(Common Table Expressions) 등을 포함한 복잡한 SQL 쿼리를 효율적으로 처리할 수 있습니다.
6. MVCC (다중 버전 동시성 제어): MVCC를 사용하여 높은 동시성 제어와 읽기 및 쓰기 작업 간의 충돌을 최소화합니다.
7. 지속적인 발전: 활발한 오픈 소스 커뮤니티의 지원으로 지속적인 업데이트와 새로운 기능이 추가됩니다.
8. 이식성: 다양한 운영 체제(Linux, Windows, macOS)에서 실행할 수 있으며, 여러 플랫폼 간에 호환성을 유지합니다.

단점

1. 학습 곡선: PostgreSQL의 고급 기능과 확장성은 초보자에게 다소 어려울 수 있으며, 학습 곡선이 있을 수 있습니다.
2. 설정 복잡성: 고급 설정과 최적화가 필요할 때 설정 파일이 복잡해질 수 있으며, 효율적인 성능 튜닝을 위해 깊은 지식이 필요합니다.
3. 메모리 사용: PostgreSQL은 메모리를 많이 사용하는 경향이 있으며, 대규모 데이터베이스 운영 시 충분한 메모리 자원이 필요합니다.
4. 호환성 문제: 일부 상업용 데이터베이스 시스템과의 호환성 문제나 마이그레이션 시 어려움이 있을 수 있습니다.
5. 커뮤니티 지원: 상업용 데이터베이스에 비해 공식적인 기술 지원이 부족할 수 있으며, 커뮤니티 지원에 의존해야 합니다.
6. 윈도우 환경에서의 성능: PostgreSQL은 주로 유닉스 계열 시스템에서 최적화되어 있으며, 윈도우 환경에서는 성능이 다소 떨어질 수 있습니다.

아키텍처

                                      +----------------+
                                      |   Client 1     |
                                      +----------------+
                                            |
                                            v
                                      +----------------+
                                      |   Client N     |
                                      +----------------+
                                            |
                                            v
                                     +----------------+
                                     |  Postmaster    |
                                     | (postgres)     |
                                     +----------------+
                                            |
                                            v
               +---------------------------------------------------+
               |                   Backend Process                 |
               |                      (각 클라이언트마다 하나씩)     |
               +---------------------------------------------------+
                                            |
                                            v
           +------------------+-------------------+------------------+
           |                  |                   |                  |
           v                  v                   v                  v
   +---------------+  +---------------+   +---------------+   +---------------+
   | Shared Buffers|  |   WAL Buffers |   |   Work Mem    |   | Maintenance   |
   |               |  |               |   |               |   |   Work Mem    |
   +---------------+  +---------------+   +---------------+   +---------------+
           |                  |                   |                  |
           v                  v                   v                  v
+--------------+      +----------------+   +---------------+  +----------------+
| Data Files   |      |  WAL Files    |   |  Sort/Hash Mem |  |  Vacuum Mem    |
|              |      |               |   |                |  |                |
+--------------+      +----------------+   +---------------+  +----------------+
           |                  |                   |                  |
           v                  v                   v                  v
+---------------+  +---------------+   +---------------+   +---------------+
|  Background   |  |   WAL Writer  |   |   Executor    |   |  Autovacuum   |
|   Writer      |  +---------------+   +---------------+   +---------------+
+---------------+          |                       |                  |
                           v                       v                  v
                   +----------------+       +---------------+   +---------------+
                   |  Checkpointer  |       |   Planner     |   |   Archiver    |
                   +----------------+       +---------------+   +---------------+
                                                 |
                                                 v
                                           +------------+
                                           |  Parser    |
                                           +------------+

프로세스 아키텍처

PostgreSQL 서버 프로세스

• Postmaster (postgres):
  • PostgreSQL 서버의 마스터 프로세스입니다. 클라이언트 연결을 수신하고 새로운 세션을 시작하며, 서버 전체의 관리 작업을 수행합니다.
• Backend 프로세스:
  • 각 클라이언트 연결마다 하나의 백엔드 프로세스가 생성됩니다. 이 프로세스는 클라이언트 요청을 처리하고 SQL 명령을 실행하며, 결과를 클라이언트에 반환합니다.
• Background Writer:
  • 공유 버퍼에서 더티 페이지를 디스크로 주기적으로 플러시하여 데이터베이스의 성능을 최적화합니다.
• WAL Writer:
  • WAL(Write-Ahead Logging) 버퍼에서 로그를 디스크의 WAL 파일로 기록하여 데이터베이스의 일관성과 복구 기능을 제공합니다.
• Checkpoint 프로세스:
  • 체크포인트를 수행하여 데이터베이스의 상태를 주기적으로 저장하고 복구 시간을 단축시킵니다.
• Autovacuum 프로세스:
  • 자동으로 데이터를 정리하고, 테이블과 인덱스를 최적화하여 성능을 유지합니다.
• Archiver 프로세스:
  • WAL 파일을 보관 위치로 아카이빙합니다.

메모리 아키텍처

• Shared Buffers:
  • 모든 서버 프로세스가 공유하는 메모리 영역으로, 데이터베이스 페이지를 캐싱하여 디스크 I/O를 줄이고 성능을 향상시킵니다.
• Work Mem:
  • 각 쿼리 실행 시 사용되는 메모리 공간으로, 정렬 및 해시 연산과 같은 작업에 사용됩니다.
• Maintenance Work Mem:
  • VACUUM, CREATE INDEX와 같은 유지 관리 작업에 사용되는 메모리 공간입니다.
• WAL Buffers:
  • WAL 로그를 디스크에 기록하기 전에 임시로 저장하는 메모리 공간입니다.

저장소 아키텍처

• 데이터 파일:
  • 테이블과 인덱스의 실제 데이터를 저장하는 파일입니다. 데이터 파일은 여러 페이지로 구성되며, 각 페이지는 8KB 크기입니다.
• WAL (Write-Ahead Logging):
  • 데이터 변경 사항을 먼저 로그 파일에 기록한 후 데이터 파일에 반영합니다. 이 메커니즘은 데이터베이스의 복구 및 일관성을 보장합니다.
• 테이블스페이스:
  • 데이터베이스 객체를 저장하는 논리적 저장소입니다. 테이블스페이스를 사용하여 데이터 파일을 다양한 디스크 위치에 분산할 수 있습니다.

쿼리 처리 아키텍처

• Parser:
  • SQL 명령을 구문 분석하여 파싱 트리를 생성합니다.
• Planner/Optimizer:
  • 파싱 트리를 기반으로 최적의 실행 계획을 수립합니다. 비용 기반 최적화를 통해 효율적인 쿼리 실행 경로를 선택합니다.
• Executor:
  • 실행 계획을 기반으로 실제 쿼리를 실행하고 결과를 생성합니다.

보안 아키텍처

• 인증:
  • 클라이언트가 데이터베이스에 접근할 때 사용자의 신원을 확인하는 절차입니다. PostgreSQL은 다양한 인증 방법을 지원합니다 (e.g., MD5, GSSAPI, SSPI, Kerberos).
• 권한 부여:
  • 사용자에게 데이터베이스 객체에 대한 권한을 부여하거나 제한합니다. GRANT 및 REVOKE 명령을 통해 권한을 관리합니다.
• 암호화:
  • 데이터 전송 중 SSL/TLS를 사용하여 통신을 암호화할 수 있으며, 특정 데이터베이스 테이블이나 컬럼에 대해 암호화를 적용할 수 있습니다.

 

명령줄 유틸리티

psql

psql은 PostgreSQL의 대화형 터미널 기반 명령줄 클라이언트입니다. 사용자가 SQL 명령을 실행하고 데이터베이스와 상호작용할 수 있게 합니다.

• 기능:
  • SQL 쿼리 실행: 데이터베이스에 SQL 명령을 실행할 수 있습니다.
  • 데이터베이스 관리: 테이블 생성, 수정, 삭제와 같은 데이터베이스 관리 작업을 수행할 수 있습니다.
  • 스크립트 실행: SQL 스크립트를 파일에서 읽어 실행할 수 있습니다.
• 사용 예제: psql -h hostname -p port -U username -d dbname

pg_dump

pg_dump는 PostgreSQL 데이터베이스를 백업하는 유틸리티입니다. 특정 데이터베이스를 덤프하여 SQL 파일 또는 압축된 아카이브 형식으로 저장할 수 있습니다.

• 기능:
  • 데이터베이스 백업: 데이터베이스의 스키마와 데이터를 백업할 수 있습니다.
  • 테이블 단위 백업: 특정 테이블만 선택적으로 백업할 수 있습니다.
  • 포맷 선택: SQL, 커스텀, 디렉터리, tar 포맷으로 백업할 수 있습니다.
• 사용 예제: pg_dump -U username -F c -b -v -f dbname.backup dbname

pg_restore

pg_restore는 pg_dump로 생성된 백업 파일을 복원하는 유틸리티입니다. pg_dump의 커스텀, 디렉터리, tar 포맷 백업 파일을 복원할 수 있습니다.

• 기능:
  • 데이터베이스 복원: 백업 파일에서 데이터베이스를 복원합니다.
  • 특정 객체 복원: 테이블, 스키마 등 특정 데이터베이스 객체만 선택적으로 복원할 수 있습니다.
  • 병렬 복원: 여러 작업을 병렬로 수행하여 복원 속도를 높일 수 있습니다.
• 사용 예제: pg_restore -U username -d dbname -v dbname.backup

pg_ctl

pg_ctl은 PostgreSQL 서버를 관리하는 유틸리티로, 서버 시작, 중지, 재시작, 상태 확인 등의 작업을 수행할 수 있습니다.

• 기능:
  • 서버 시작/중지: PostgreSQL 서버를 시작하거나 중지합니다.
  • 서버 상태 확인: 현재 서버의 상태를 확인합니다.
  • 서버 재시작: 서버를 재시작합니다.
• 사용 예제: pg_ctl -D /usr/local/pgsql/data start pg_ctl -D /usr/local/pgsql/data stop pg_ctl -D /usr/local/pgsql/data restart

createdb 및 dropdb

createdb와 dropdb는 PostgreSQL 데이터베이스를 생성 및 삭제하는 유틸리티입니다.

• 기능:
  • 데이터베이스 생성: 새로운 데이터베이스를 생성합니다.
  • 데이터베이스 삭제: 기존 데이터베이스를 삭제합니다.
• 사용 예제: createdb -U username dbname dropdb -U username dbname

createuser 및 dropuser

createuser와 dropuser는 PostgreSQL 사용자 계정을 생성 및 삭제하는 유틸리티입니다.

• 기능:
  • 사용자 생성: 새로운 PostgreSQL 사용자 계정을 생성합니다.
  • 사용자 삭제: 기존 PostgreSQL 사용자 계정을 삭제합니다.
• 사용 예제: createuser -U username newuser dropuser -U username olduser