남기면 좋잖아

PostgreSQL 설치 및 설정 본문

IT/Database

PostgreSQL 설치 및 설정

Beautiful Hugo 2020. 3. 19. 20:55
반응형

준비

  • 운영체제 : CentOS 7.4

설치

Repository 추가

su -
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

PostgreSQL 설치

yum install -y postgresql10-server postgresql10

/tablespace/postgres 디렉터리 생성

$ mkdir -p /tablespace/postgres

/tablespace/postgres 디렉터리 소유자 및 그룹 변경

$ chown -R /tablespace/postgres

PostgreSQL DB 초기화

$ su - postgres
$ /usr/pgsql-10/bin/initdb -D /tablespace/postgres/pgsql/data
$ exit

-D : PostgreSQL 초기 설정 데이터 위치

http://bestugi.tistory.com/17

PostgreSQL 구동 & 종료

postgresql-10.service 수정

$ vi /usr/lib/systemd/system/postgresql-10.service
Environment=PGDATA=/tablespace/postgres/pgsql/data/
$ systemctl daemon-reload

PostgreSQL 구동

$ systemctl start postgresql-10 # 시작
$ systemctl status postgresql-10 # 상태확인
$ systemctl enable postgresql-10 # 부팅시 시작

PostgreSQL 종료

$ systemctl stop postgresql-10

초기 설정

관리자 비밀번호 설정

$ su - postgres
$ psql

postgres=# \password postgres
Enter new password:[암호]
Enter it again:[암호 재입력]
postgres=# \q

PostgreSQL conf 파일 수정

  • 로컬, 원격 암호 접속 허용
  • 포트 번호 10090 설정

postgres.conf

$ vi /tablespace/postgres/pgsql/data/postgres.conf

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
port = 10090

pg_hba.conf

$ vi /tablespace/postgres/pgsql/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5

  • TYPE (접근자의 접근위치와 통신의 암호화 관련 설정)

    • local : Unix Domain Socket을 통한 접속
    • hostssl : ssl인증서를 통한 암호화 통신만 지원하며 localhost, 127.0.0.1식의 즉 TCP/IP접속에 해당
    • hostnossl : ssl접속은 불가능하며, TCP/IP통신을 지원

  • DATABASE

    • all : 모든 데이터베이스 접근
    • DB 명 : 특정한 디비에 대한 접속을 제한할 수 있으며 ,(콤마)로 여러 개의 DB로 접근 제어가 가능.
    • @DBlist file : DB가 많다면 dblist파일로 따로 만들어 PGDATA로 설정한 폴더의 안에 넣어 관리

  • USER

    • all : 모든 유저
    • user 명 : 계정설정으로 ,(콤마)구분으로 할 수 있음
    • @파일명 : 파일로 처리 가능

  • ADDRESS (CIDR 방식)

    • IPv4 CIDR구분으로 해당 C Class에 대해 모두 접근처리를 할 경우 : xxx.xxx.xxx.0/24
    • 해당 IP에 대한 접근처리를 할 경우 : xxx.xxx.xxx.xxx/32

  • METHOD (계정의 패스워드에 대한 서버로의 전송을 어떻게 할 것인가를 정하는 것)

    • trust : 패스워드 없이 접근 가능
    • reject : 거부
    • md5 : 패스워드를 md5로 암호화해서 전송
    • password : text로 패스워드를 전송하는 것.
    • krb4, krb5 : KerberOS V4, 5를 지원.
    • ident : 접속 ClientOS User이름을 확인하는 방법
    • pam : PAM(Pluggable Authentication Modules)서비스를 사용한 인증

설정 후 재시작

$ systemctl stop postgresql-10
$ systemctl start postgresql-10

firewall - 외부 접속 포트 허용

$ firewall-cmd --permanent --zone=public --add-port=10090/tcp

$ systemctl stop firewalld # 재시작
$ systemctl start firewalld

$ firewall-cmd --list-all-zones # 적용 확인

그 외 postgres.conf 설정

Connection And Authentication

max_connections = 1000
  • DB 서버에 대한 최대 동시 연결 수 설정

Resourece Usage (except WAL)

shared_buffers = 32GB
  • DB 서버가 공유 메모리 버퍼에 사용하는 메모리양 설정 (최소 128 kb).
  • 메모리가 1GB 이상의 서버라면 시스템 메모리의 25%가 적당. PostgreSQL은 운영 체제 캐시에 의존 하기 때문에 지나치게 많이 할당하면 오히려 성능이 하락할 수 있음.
temp_buffers = 10MB
  • 각 DB 세션에서 사용하는 임시 버퍼의 최대
  • 임시 테이블에 대한 엑세스에만 사용되는 세션 버퍼
  • 필요에 따라 최대 temp_buffers가 지정한 한계까지 할당
work_mem = 1GB
  • 임시 디스크 파일에 쓰기 전에 내부 정렬 작업 및 해시 테이블에서 사용할 메모리양 설정
  • 복잡한 쿼리일 수록, 여러 세션이 동시에 작업할 수 있기 때문에 사용된 전체 메모리는 work_mem의 몇 배가 될 수 있음
maintenance_work_mem = 8GB
  • create index 혹은 alter table add 등 유지 보수 작업에서 사용할 최대 메모리 양 설정
  • work_mem 값보다 크게 설정하여 안전하게 운용
  • 데이터 제거 및 DB 덤프 복원 성능 향상
max_worker_processes = 10
  • 백그라운드 프로세스의 최대 수
max_parallel_workers = 8
  • 병렬 작업을 지원할 수 있는 최대 프로세스 수
  • max_worker_processes 보다 높게 설정해도 해당 설정 프로세스 풀에서 가져온 것이므로 아무런 영향을 미치지 않음
  • 이 값을 변경할때 max_parallel_maintenance_workers 및 max_parallel_workers_per_gather 값도 조정

Write Ahead Log

synchronous_commit = off
  • 쿼리가 클라이언트에 success 메시지를 반환하기 전에 WAL 레코드가 디스크에 기록되는 동안 트랜잭션 커밋이 대기할지 여부
  • 지연 시간이 최대 wal_writer_delay 값의 3배
  • off 로 해놓더라도 DB 상태는 트랜잭션이 정상 중단된 경우와 같기 때문에 성능이 중요하다면 off를 권장
wal_buffers = 64MB
  • 디스크에 아직 기록되지 않은 WAL 데이터에 사용되는 공유 메모리 양
  • 값이 클 수록, 클라이언트가 한 번에 커밋하는 사용량이 많은 서버에서 쓰기 성능을 향상시킴
wal_writer_delay = 200ms
  • WAL 작성기가 WAL을 플러시하는 빈도
  • 플러시를 한후 wal_writer_delay 값 동안 대기
checkpoint_timeout = 5min
  • 자동 WAL 체크 포인트 간 최대 시간(초)

Replication

effective_cache_size = 32GB
  • 단일 쿼리에서 사용할 수 있는 디스크 캐시의 유효 크기
  • 값이 높을수록 인덱스 스캔이 더 많이 사용됨

Query Tuning

default_statistics_target = 100
  • 값이 클수록 analyze를 수행하는 시간은 늘지만 예상 값은 향상 됨
constraint_exclusion = on
  • 테이블 제약 조건 사용을 제어하여 쿼리를 최적화함
  • on : 모든 테이블 제약 조건 검사

Error Reporting And Logging

log_destination = 'stderr'
  • 서버 메시지를 stderr에 기록vim logging_collector = on
  • stderr로 전송 된 로그를 파일로 리다이렉션
log_directory = 'log'
  • 로그 파일 디렉토리 생성
log_filename = 'postgresql-%a.log'
  • 로그 파일 이름 설정
log_truncate_on_rotation = on
  • 설정한 시간만큼 파일에 추가됨.
  • 지나면 새로운 로그 파일 생성
log_rotation_age = 1d
  • 로그 파일의 로테이션 기간
log_rotation_size = 0
  • 로그 파일 최대 사이즈. 크기를 기반으로 안두려면 0으로 지정
log_line_prefix = '%m [%p]'
  • 로그 라인 시작부분 문자열

https://www.postgresql.org/docs/10/index.html

DB 만들기 예제

USER 생성

postgres=# create user testuser password 'testpw';
CREATE ROLE
postgres=# alter user testuser with superuser;
ALTER ROLE

Table Spcae 생성

테이블 스페이스가 될 디렉터리 생성

bash $ mkdir /tblsp $ chown -R postgres:postgres /tblsp

PostgreSQL DB에서 tablespace 위치와 owner를 지정

postgres=# create tablespace tblsp location '/tblsp';
CREATE TABLESPACE
postgres=# alter tablespace tblsp owner to testuser ;
ALTER TABLESPACE

postgres=# \db
      테이블스페이스 목록
    이름    |  소유주  |  위치
------------+----------+--------
 pg_default | postgres |
 pg_global  | postgres |
 tblsp      | testuser | /tblsp
(3개 행)

DB 생성

postgres=# create database testdb owner testuser;

DB 접근 권한 주기

postgres=# grant ALL privileges on database testdb to testuser ;
postgres=# \l

데이터베이스 목록
이름    |  소유주  | 인코딩 |   Collate   |    Ctype    |      액세스 권한
-----------+----------+--------+-------------+-------------+-----------------------
postgres  | postgres | UTF8   | ko_KR.UTF-8 | ko_KR.UTF-8 |
template0 | postgres | UTF8   | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
|          |        |             |             | postgres=CTc/postgres
template1 | postgres | UTF8   | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
|          |        |             |             | postgres=CTc/postgres
testdb    | testuser   | UTF8   | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/testuser           +
|          |        |             |             | testuser=CTc/testuser
(4개 행)

Table Spcae 지정하기

postgres=# alter database testdb set tablespace tblsp;
ALTER DATABASE

Time Scale 설치

RPM 다운로드 및 설치

wget https://timescalereleases.blob.core.windows.net/rpm/timescaledb-1.0.0-postgresql-10-0.x86_64.rpm
sudo yum install <rpm file>

postgresql.conf 수정

PostgreSQL이 timescale을 사용할 수 있도록 아래와 같이 수정
bash $ vi /tablespace/postgres/pgsql/data/postgresql.conf

#shared_preload_libraries = '' 
# (change requires restart) shared_preload_libraries = 'timescaledb' 
# (change requires restart)

Timescale DB 생성

사용하고자 하는 DB에 접근 후 TimeScaleDB를 생성한다.

\c testdb;

create extension if not exists timescaledb cascade;

성공시 아래와 같이 출력된다.

WELCOME TO
 _____ _                               _     ____________
|_   _(_)                             | |    |  _  \ ___ \
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.0.0
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/getting-started
 2. API reference documentation: https://docs.timescale.com/api
 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

CREATE EXTENSION
testdb=#

HyperText 생성

TABLE : readings, device_info

#select create_hypertable('테이블 명','시계열 변수', 옵션..);
#ex)
select create_hypertable('readings', 'time', chunk_time_interval => interval '1 day');

실행 예제

Timescale DB는 여러 함수를 제공하는데 그 중 time_bucket() 함수를 활용한 select 문이다.
time_bucket은 시계열 변수를 가져와 지정한 특정 시간 단위로 그룹화 할 수 있다.

SELECT time_bucket('1 hour', time) as "hour",
min(battery_level) min_battery_level,
max(battery_level) max_battery_level
FROM readings r
WHERE r.device_id IN (
    SELECT DISTINCT device_id FROM device_info
    WHERE model = 'pinto' OR model = 'focus'
) GROUP BY "hour" ORDER BY "hour" ASC LIMIT 12;

결과

hour          | min_battery_level | max_battery_level
------------------------+-------------------+-------------------
2016-11-15 21:00:00+09 |                20 |                99
2016-11-15 22:00:00+09 |                12 |                98
2016-11-15 23:00:00+09 |                 8 |                97
2016-11-16 00:00:00+09 |                 6 |               100
2016-11-16 01:00:00+09 |                 6 |               100
2016-11-16 02:00:00+09 |                 6 |               100
2016-11-16 03:00:00+09 |                 6 |               100
2016-11-16 04:00:00+09 |                 6 |               100
2016-11-16 05:00:00+09 |                 6 |               100
2016-11-16 06:00:00+09 |                 6 |               100
2016-11-16 07:00:00+09 |                 6 |               100
2016-11-16 08:00:00+09 |                 6 |               100
(12 rows)

https://docs.timescale.com/v1.0/main

반응형
저작자표시
Comments