CentOS/RHEL에 PostgreSQL 설치 및 구성

이 문서에서는 PostgreSQL 11을 설치합니다. Linux CentOS에서 7, 기본 구성을 수행하고 주요 구성 파일 매개변수와 성능 조정 방법을 고려하십시오. PostgreSQL은 인기 있는 무료 객체 관계형 데이터베이스 관리 시스템입니다. MySQL/MariaDB보다 덜 유명하지만 가장 전문적인 것입니다.

PostgreSQL의 강점:

• SQL 표준을 완벽하게 준수합니다.
• 다중 버전 동시성 제어(MVCC)로 인한 고성능
• 확장성(고부하 환경에서 널리 사용됨)
• 여러 프로그래밍 언어 지원
• 탄력적인 트랜잭션 및 복제 메커니즘,
• JSON 데이터 지원

CentOS/RHEL에 PostgreSQL을 설치하는 방법

PostgreSQL은 기본 CentOS 리포지토리에서 설치할 수 있지만 항상 현재 패키지 버전을 찾을 수 있으므로 개발자 리포지토리를 설치합니다.

우선 PosgreSQL 저장소를 추가합니다.

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

이 저장소에는 최신 버전과 이전 버전의 PosgreSQL이 모두 포함되어 있습니다. 저장소에 대한 정보는 다음과 같습니다.

yum을 사용하여 PostrgeSQL 11을 설치해 보겠습니다.

# yum install postgresql11-server -y

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

설치 :libicu-50.2-3.el7.x86_64 1/4설치 :postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4설치 :postgresql11-11.5-1PGDG.rhel7.x36_14 설치 :postgresql164 11.5-1PGDG.rhel7.x86_64 4/4

패키지를 설치한 후 데이터베이스를 초기화해야 합니다.

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

또한 PostgreSQL 데몬을 활성화하고 systemctl을 사용하여 자동 시작에 추가합니다.

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

서비스 상태 확인:

# systemctl status postgresql-11

● postgresql-11.service - PostgreSQL 11 데이터베이스 서버 로드됨:로드됨(/usr/lib/systemd/system/postgresql-11.service; 활성화됨, 공급업체 사전 설정:비활성화됨) 활성:2020-10년 수요일부터 활성(실행 중) -18 16:02:15 +06; 26초 전문서:https://www.postgresql.org/docs/11/static/Process:8714 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (코드=exited , 상태=0/SUCCESS)메인 PID:8719(postmaster)C그룹:/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:stats2 replication└ 16:02:16 host1.woshub.com systemd[1]:PostgreSQL 11 데이터베이스 서버 시작 중...

외부에서 PostgreSQL에 액세스하려면 기본 CentOS 방화벽에서 TCP 포트 5432를 엽니다.
# firewall-cmd --get-active-zones

공개 인터페이스: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에서 권한 부여

기본적으로 PostgreSQL을 설치할 때 시스템에는 postgres라는 유일한 사용자가 있습니다. 매일 데이터베이스 작업에 이 계정을 사용하는 것은 권장하지 않습니다. 각 데이터베이스에 대해 별도의 사용자를 만드는 것이 좋습니다.

Postgres 서버에 연결하려면 다음 명령을 실행하십시오:

# sudo -u postgres psql

psql (11.5)도움을 받으려면 "help"를 입력하십시오.

postgres=#

PostgreSQL 콘솔이 나타납니다. 그런 다음 psql 콘솔에서 PostgreSQL 관리의 몇 가지 간단한 예를 보여드리겠습니다.

기본 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;

DB에 연결하려면:

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에서 다음을 지정하십시오:

로컬 모두 모두 md5host 모두 모두 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 – postgres 스케줄러가 로컬 드라이브의 캐싱에 사용할 수 있는 메모리 양을 결정할 수 있도록 하는 매개변수. 일반적으로 서버에 있는 총 RAM의 50-75%로 설정됩니다.
• work_mem – 데이터베이스 관리 시스템의 내부 정렬 작업에서 사용할 메모리 크기 — ORDER BY, DISTINCT 및 병합,
• maintenance_work_mem – 내부 작업에서 사용할 메모리 크기 — VACUUM, CREATE INDEX 및 ALTER TABLE ADD FOREIGN KEY;
• fsync – 이 매개변수가 활성화되면 DBMS는 하드 디스크에 데이터가 물리적으로 쓰기를 기다립니다. fsync가 활성화되면 시스템 또는 하드웨어 오류가 발생한 후 데이터베이스를 더 쉽게 복구할 수 있습니다. 분명히 이 매개변수가 활성화되면 데이터베이스 관리 시스템의 성능은 떨어지지만 스토리지 안정성은 높아집니다. 비활성화하면 full_page_writes도 비활성화할 가치가 있습니다.
• max_stack_depth — 최대 스택 크기(기본적으로 2MB),
• max_fsm_pages — 이 매개변수를 사용하여 서버의 여유 디스크 공간을 관리할 수 있습니다. 예를 들어, 테이블에서 일부 데이터를 삭제한 후 이전에 점유했던 공간은 여유 공간이 되지 않지만 여유 공간 맵에서 여유 공간으로 표시되고 이후 새 항목에 사용됩니다. 서버의 테이블에 데이터를 자주 쓰거나 제거하는 경우 이 매개변수의 값을 더 크게 설정하면 성능이 향상됩니다.
• wal_buffers – WAL 데이터를 유지하는 데 사용되는 공유 메모리 크기(shared_buffers)
• wal_writer_delay – 디스크에 WAL을 기록하는 연속 기간 사이의 시간
• commit_delay — WAL 버퍼에 대한 트랜잭션 쓰기와 디스크 쓰기 간의 지연
• synchronous_commit — 매개변수는 WAL 데이터가 디스크에 물리적으로 쓰여진 후 성공적인 트랜잭션의 결과가 전송되도록 설정합니다.

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

여러 가지 방법으로 PostgreSQL 데이터베이스를 백업할 수 있습니다. 가장 쉬운 것을 생각해 봅시다.

먼저 서버에서 어떤 데이터베이스가 실행되고 있는지 확인하십시오.

postgres=# \list

4개의 데이터베이스가 있으며 그 중 3개는 시스템 데이터베이스(postgres 및 템플릿)입니다.

이전에 mydbtest라는 이름으로 DB를 생성했으며 이제 백업합니다.

pg_dump를 사용하여 PostgreSQL 데이터베이스를 백업할 수 있습니다. 도구:

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

postgres 사용자로 이 명령을 실행하고 데이터베이스 덤프를 저장할 파일의 경로와 데이터베이스를 지정합니다. 백업 시스템에서 데이터베이스 덤프를 가져오거나 웹 서버를 사용하는 경우 연결된 클라우드 저장소 계정으로 보낼 수 있습니다.

데이터베이스에 덤프를 복원하려면 psql을 사용합니다. :

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

특별한 덤프 형식으로 백업을 만들고 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에는 PgTun이 있습니다. 그러나 불행히도 오랫동안 업데이트되지 않았습니다. 동시에 PostgreSQL 구성을 최적화하는 데 사용할 수 있는 온라인 서비스가 많이 있습니다. PGTune(pgtune.leopard.in.ua)을 좋아합니다. .

인터페이스는 매우 간단합니다. 서버 매개변수(프로파일, 프로세서, 메모리, 디스크 유형)를 지정하고 "생성"을 클릭하기만 하면 됩니다. 주요 PostgreSQL 매개변수의 권장 값을 포함하는 변형된 postgresql.conf가 제공됩니다.

예를 들어, 다음 postgresql.conf 설정은 4xGB RAM 및 4xvCPU가 있는 VPS SSD 서버에 권장됩니다.

# DB 버전:11# OS 유형:linux# DB 유형:web# 총 메모리(RAM):4GB# CPU 수:4# 연결 수:100# 데이터 저장:ssdmax_connections =100shared_buffers =1GBEffective_cache_size =3GBmaintenance_work_completion_memcheck ==0.7wal_buffers =16MBdefault_statistics_target =100random_page_cost =1.1Effective_io_concurrency =200work_mem =5242kBmin_wal_size =1GBmax_wal_size =1GBmax_wal_size =4GBmax_worker_processes =4max_parallel_workers 

 
 

 사실, 기사가 작성될 당시에는 이것이 유일한 자원이 아닙니다. 유사한 서비스도 이용 가능합니다.
 
 
Cybertec PostgreSQL 구성 프로그램
 
PostgreSQL 구성 도구
 
 

 이러한 서비스를 사용하여 하드웨어 및 작업에 대한 기본 PostgreSQL 매개변수를 빠르게 구성할 수 있습니다. 나중에 서버 리소스를 고려할 뿐만 아니라 데이터베이스의 작동, 크기, 연결 수를 분석하고 이러한 정보를 기반으로 PostgreSQL 매개변수를 미세 조정할 수 있습니다.