Postgresql 13 - 운영환경 구성방법

Postgresql 운영할시에는 여러가지 고려사항을 인지하고 구성/설정이 되어있어야 한다.

Postgresql에 국한되어 있지 않고 다른 RDBMS나 Application 을 구성할때에도 유용할 것이라고 생각한다.

 

## 운영시 예상상황

* Postgresql 서비스가 정상적으로 구동되고 있는지 확인하고 싶다.

* DATA 용량이 너무 작아 OS영역의 파티션에서 용량이 큰 파티션으로 변경해야 한다.

* Postgresql 로그를 빨리 확인거나 오래된 로그는 백업 또는 삭제하고 싶다.

* 특정쿼리가 너무 오래 실행중이라 현재 실행중인 쿼리를 서버에서 확인하고 싶다.

 

## Postgresql 설치 포스팅 참고

2021/01/07 - [Database/Postgresql] - Postgresql 13을 CentOS 7.6에 RPM으로 설치하기

Postgresql 13을 CentOS 7.6에 RPM으로 설치하기

 

## 환경정보

* CentOS에서 RPM 방식으로 설치한 Postgresql

* Postgresql 13.1

* CentOS 7.6

 

## 먼저 postgres 계정의 프롬프트를 변경하자.

현재 postgres 계정으로 접속하면 -bash-4.2$ 으로 표시가 되어 권한이나 현재폴더위치를 파악하기가 힘들다.

RPM으로 설치가 되어서 postgres 계정의 ~/.bashrc 또는 ~/.bash_profile 파일이 생성되지 않아서 생기는 문제로 파악된다.

[root@postgres ~]# su - postgres
Last login: Sun Jan 10 16:24:28 KST 2021
-bash-4.2$
-bash-4.2$ env | grep PS1
-bash-4.2$

 

리눅스는 PS1 변수값으로 프롬프트를 표시한다. ~/.bash_profile 파일에 PS1 변수를 지정하자.

su - postgres
vi ~/.bash_profile
export PS1="[\u@\h \w]\\$ "
source ~/.bash_profile

[root@postgres ~]# su - postgres
Last login: Sun Jan 10 16:28:28 KST 2021
-bash-4.2$ vi ~/.bash_profile

export PS1="[\u@\h \w]\\$ "

-bash-4.2$ source ~/.bash_profile
[postgres@postgres ~]$

 

## 운영 예상상황1 - Postgresql 서비스가 정상적으로 구동되고 있는지 확인하고 싶다.

외부에서 갑자기 접속이 안되거나 Postgresql 를 연결하여 개발한 어플리케이션이 작동이 안되면 프로세스가 정상적으로 구동되고 있는지 확인해야할 순간이 있을 것이다.

systemctl status postgresql-13 명령어로 확인할 수 있지만 alias 로 등록해서 짧은 명령어(pg.status)로 확인하자.

su - postgresl
vi ~/.bash_profile
alias pg.status='env | grep ^PG; systemctl status postgresql-13'
alias pg.start='env | grep ^PG; systemctl start postgresql-13' 
source ~/.bash_profile
pg.status

 

pg.status 짧은 명령어로 PG로 시작하는 변수값 및 postgresql 상태를 확인할 수 있다.

[Postgresql] 서비스 상태 확인

!!! Postgresql 서비스의 stop을 alias 로 등록할 수 있지만 서비스 stop 를 너무 쉽게 하는 방식은 추천하지 않는다. (장애발생요소) !!!

 

## 운영 예상상황 2 - DATA 용량이 너무 작아 DATA 경로를 다른 파티션의 경로로 변경

DBMS의 DATA영역은 OS영역과 별도로 다른 파티션으로 연결하는 것이 좋다.

OS 영역은 100GB 로 설정해도 충분하지만 운영환경에서의 DB의 데이터는 DB성격에 따라 다르지만 500GB 정도는 금방 사용하기 때문이다.

처음 설치시 postgresql-13-setup initdb -D <Directory> 명령어로 DATA 영역이 지정가능하지만 다른방식으로 변경해보자.

initdb 옵션정보 - www.postgresql.org/docs/13/app-initdb.html

initdb

 

기존 PGDATA 폴더설정을 /var/lib/pgsql/13/data/ 에서 /data/pgsql/13/data 으로 변경

su - root
mkdir /data
mkdir /home/postgres
chown -R postgres:postgres /home/postgres

mv /var/lib/pgsql /data/
chmod -R 750 /data/pgsql
cp /data/pgsql/.bash_profile /home/postgres/
cp /data/pgsql/.psql_history /home/postgres/

vi /etc/passwd
postgres 계정의 홈디렉토리 /var/lib/pgsql -> /home/postgres 로 변경

vi /home/postgres/.bash_profile
export PGHOME=/usr/pgsql-13
export PGDATA=/var/lib/pgsql/13/data -> export PGDATA=/data/pgsql/13/data 로 변경

systemctl disable postgresql-13
vi /usr/lib/systemd/system/postgresql-13.service
Environment=PGDATA=/var/lib/pgsql/13/data/ -> Environment=PGDATA=/data/pgsql/13/data 로 변경
systemctl enable postgresql-13
systemctl start postgresql-13

 

Postgresql 구동시 FATAL:  data directory "/data/pgsql/13/data" has invalid permissions 메세지가 표시되면 폴더권한 변경 명령어(chmod -R 750 /data/pgsql)를 다시 실행해보자.

[root@postgres ~]# systemctl start postgresql-13
Job for postgresql-13.service failed because the control process exited with error code. See "systemctl status postgresql-13.service" and "journalctl -xe" for details.
[root@postgres ~]# journalctl -xe
-- Subject: Unit postgresql-13.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-13.service has begun starting up.
Jan 10 18:08:43 postgres postmaster[18457]: 2021-01-10 18:08:43.327 KST [18457] FATAL:  data directory "/data/pgsql/13/data" has invalid permissions
Jan 10 18:08:43 postgres postmaster[18457]: 2021-01-10 18:08:43.327 KST [18457] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
Jan 10 18:08:43 postgres systemd[1]: postgresql-13.service: main process exited, code=exited, status=1/FAILURE
Jan 10 18:08:43 postgres systemd[1]: Failed to start PostgreSQL 13 database server.
-- Subject: Unit postgresql-13.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-13.service has failed.
--
-- The result is failed.
Jan 10 18:08:43 postgres systemd[1]: Unit postgresql-13.service entered failed state.
Jan 10 18:08:43 postgres systemd[1]: postgresql-13.service failed.
Jan 10 18:08:43 postgres polkitd[5265]: Unregistered Authentication Agent for unix-process:18445:35460437 (system bus name :1.2612, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US.UTF-8) (disco

 

pg.start 를 실행하여 서비스를 확인

[Postgresql] PGDATA 변경 확인

 

## 운영 예상상황3 - Postgresql 로그를 실시간으로 확인하자.

log 폴더로 이동하여 로그를 확인해보자.

su - postgres
cd $PGDATA/log
pwd
ls -l

[postgres@postgres ~]$ cd $PGDATA/log
[postgres@postgres /data/pgsql/13/data/log]$ pwd
/data/pgsql/13/data/log
[postgres@postgres /data/pgsql/13/data/log]$ ls -l
total 232
-rwxr-x---. 1 postgres postgres      0 Jan  8 00:00 postgresql-Fri.log
-rwxr-x---. 1 postgres postgres      0 Jan  9 00:00 postgresql-Sat.log
-rwxr-x---. 1 postgres postgres   1491 Jan 10 18:10 postgresql-Sun.log
-rwxr-x---. 1 postgres postgres 231811 Jan  7 16:46 postgresql-Thu.log
[postgres@postgres /data/pgsql/13/data/log]$

너무 미국식 요일별(!?)로 로그가 생성되고 있는 것을 확인할 수가 있다. 이렇게 설정되어 로그가 쌓이게 되면 기존로그가 삭제되어 기존로그를 확인할 수 없는 상황이 발생될 것이다.

그래서 postgresql-20210110.log 처럼 년월일로 설정하면 백업 및 삭제가 편리해지고 현재 쌓이고 있는 로그를 바로 확인할 수 있다.

vi $PGDATA/postgresql.conf
log_filename = 'postgresql-%Y-%m-%d.log'
:wq
$PGHOME/bin/pg_ctl reload

[postgres@postgres /data/pgsql/13/data]$ vi $PGDATA/postgresql.conf

log_filename = 'postgresql-%Y%m%d.log'
log_truncate_on_rotation = off
log_rotation_age = 0

:wq
[postgres@postgres /data/pgsql/13/data]$ $PGHOME/bin/pg_ctl reload
server signaled
[postgres@postgres /data/pgsql/13/data]$

 

pg_ctl reload 명령어를 이용하면 postgresql.conf 설정값을 Postgresql 서비스를 내리지 않고 반영할 수 있다.

$PGDATA/log 폴더를 확인하면 로그설정이 반영된 것을 확인할 수 있다.

[Postgresql] 로그설정 변경완료 및 확인

 

하루치 로그 용량이 너무 크면 시간(%H)까지 설정하는 것을 고려해야 할 수도 있다.(postgresql-%Y%m%d%H.log)

alias - pg.log 를 등록하면 쉽게 로그를 확인할 수 있다.

vi ~/.bash_profile
alias pg.log='tail -f -n 50 $PGDATA/log/postgresql-`date +"%Y%m%d"`.log'
source ~/.bash_profile
pg.log

[postgres@postgres /data/pgsql/13/data/log]$ vi ~/.bash_profile

alias pg.log='tail -f -n 50 $PGDATA/log/postgresql-`date +"%Y%m%d"`.log'

:wq
[postgres@postgres /data/pgsql/13/data/log]$ source ~/.bash_profile
[postgres@postgres /data/pgsql/13/data/log]$ pg.log
2021-01-10 22:09:00.319 KST [16402] ERROR:  syntax error at or near "to" at character 9
2021-01-10 22:09:00.319 KST [16402] STATEMENT:  select 2to
[postgres@postgres /data/pgsql/13/data/log]$

 

Postgresql 로그옵션에 대해서 상세하게 알고 싶으면 좋은 블로그 추천

browndwarf.tistory.com/10

PostgreSQL Log 설정 - 1

 

## 운영 예상상황4 - 현재 실행중인 쿼리를 콘솔로 확인하고 싶은 경우

현재 실행되는 쿼리를 확인하거나 오래실행중인 쿼리를 확인하고 싶은 경우 pg_catalog.pg_stat_activity 테이블을 조회하면 된다.

하지만 현재 구동중(active)인 쿼리만 조회하던지 쿼리실행시간을 항목을 확인하려면 쿼리를 조금 수정해야 한다.

select datname
     , pid
     , usename
     , to_char(query_start, 'YYYY-MM-DD HH24:MI:SS') as query_start
     , to_char(now() - query_start, 'HH24:MI:SS') as runtime
     , substr(query, 1, 100) as query
  from pg_catalog.pg_stat_activity
 where state = 'active'
 order by query_start asc

 

[postgres@postgres ~]$ vi ~/.bash_profile

alias pg.aq="psql -U postgres -c \"select datname, pid, usename, to_char(query_start, 'YYYY-MM-DD HH24:MI:SS') as query_start, to_char(now() - query_start, 'HH24:MI:SS') as runtime, substr(query, 1, 100) as query from pg_catalog.pg_stat_activity where state = 'active' order by query_start asc\""

:wq
[postgres@postgres ~]$ source ~/.bash_profile
[postgres@postgres ~]$ pg.aq
 datname  | pid  | usename  |     query_start     | runtime  |                                                query
----------+------+----------+---------------------+----------+------------------------------------------------------------------------------------------------------
 postgres | 8820 | postgres | 2021-01-10 22:16:28 | 00:00:22 | select version(), pg_sleep(100)
 postgres | 9064 | postgres | 2021-01-10 22:16:51 | 00:00:00 | select datname, pid, usename, to_char(query_start, 'YYYY-MM-DD HH24:MI:SS') as query_start, to_char(
(2 rows)

[postgres@postgres /data/pgsql/13/data/log]$

pg.aq 를 입력하면 현재 실행중인 쿼리를 확인할 수 있다.

 

## 참고

.bash_profile 내용

[postgres@postgres ~]$ cat ~/.bash_profile
[ -f /etc/profile ] && source /etc/profile

export PS1="[\u@\h \w]\\$ "

export PGHOME=/usr/pgsql-13
export PGDATA=/data/pgsql/13/data

alias pg.status='env | grep ^PG; systemctl status postgresql-13'
alias pg.start='env | grep ^PG; systemctl start postgresql-13'
alias pg.log='tail -f -n 50 $PGDATA/log/postgresql-`date +"%Y%m%d"`.log'
alias pg.aq="psql -U postgres -c \"select datname, pid, usename, to_char(query_start, 'YYYY-MM-DD HH24:MI:SS') as query_start, to_char(now() - query_start, 'HH24:MI:SS') as runtime, substr(query, 1, 100) as query from pg_catalog.pg_stat_activity where state = 'active' order by query_start asc\""
[postgres@postgres ~]$

 

<끄읕>