-
PostgresSQL 설치 및 설정카테고리 없음 2021. 10. 20. 14:25반응형
준비
- 운영체제 : 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 초기 설정 데이터 위치
PostgreSQL 구동 & 종료
postgresql-10.service 수정
$ vi /usr/lib/systemd/system/postgresql-10.serviceEnvironment=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]'
- 로그 라인 시작부분 문자열
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 /tblspPostgreSQL 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
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)
반응형