설치 환경

운영체제 : centos 7 3-10.0-1127.13.1.el7.x86_64

DB 버전 : 11.9

구성도

설치 방법

특이사항

• replication 설정 후 시작, replication 설정은 https://peryh.tistory.com/8?category=871555 참조
• pgpool을 두가지 방식으로 사용하는 방법을 기술(pgpool이 load balance만 하는 경우, pgpool이 replication을 담당하는 경우)
• postgres 기본 경로 변경(/var/lib/pgsql/11/data -> /pgsql/11/data)하여 수행, 변경하지 않는 경우 scp로 파일을 생성할 때, root의 권한이 있는 디렉터리에 생성되지 않음, 설정은 peryh.tistory.com/7?category=871555 참조

master 쪽 설정

Pgpool-II 설치

yum -y install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-release-4.0-1.noarch.rpm

yum -y install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-pg11-4.0.1-1pgdg.rhel7.x86_64.rpm

yum -y install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-pg11-debuginfo-4.0.1-1pgdg.rhel7.x86_64.rpm

yum -y install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-pg11-devel-4.0.1-1pgdg.rhel7.x86_64.rpm

yum -y install http://www.pgpool.net/yum/rpms/4.0/redhat/rhel-7-x86_64/pgpool-II-pg11-extensions-4.0.1-1pgdg.rhel7.x86_64.rpm

확인

#rpm -qa | grep pgpool

pgpool-II-release-4.0-1.noarch

pgpool-II-pg11-debuginfo-4.0.1-1pgdg.rhel7.x86_64

pgpool-II-pg11-extensions-4.0.1-1pgdg.rhel7.x86_64

pgpool-II-pg11-4.0.1-1pgdg.rhel7.x86_64

pgpool-II-pg11-devel-4.0.1-1pgdg.rhel7.x86_64

pgpool디렉터리 생성(로그 보기 위한 디렉터리)

mkdir -p /var/log/pgpool

Step 2. pgpool 설정을 한다.

pgpool.conf 복사 및 수정

어떻게 작동시킬 것인가에 따라 필요한 conf 파일을 복사해서 사용한다

스트리밍 복제모드(Streaming replication mode) : stream PostgreSQL이 데이터베이스를 동기화하고 Pgpool은 Load Balancing을 담당

논리 복제모드(logical replication mode) : logical PostgreSQL이 테이블 동기화를 담당하며 Pgpool은 Load Balancing을 담당

마스터 슬레이브 모드(master slave mode(slony mode)) : master-slavePgpool이 데이터베이스 동기화와 Load Balancing을 담당

네이티브 복제모드(native replication mode) : replication동기식으로 동기화를 수행

원시 모드(raw mode) : sample

설명 문서 : https://www.pgpool.net/docs/latest/en/html/configuring-pgpool.html

#cd /etc/pgpool-II

#rm -rf pgpool.conf

#cp pgpool.conf.sample-stream pgpool.conf

#chmod 764 pgpool.conf

#vi /etc/pgpool-II/pgpool.conf

---------------------------------------------------------------------

# pgpool 외부 접근정보

# 외부 접속 정보

#  '*' for all, '' for no TCP/IP connections

listen_addresses = '*'

port = 9999

#------------------------------------------------------------------------------

# - Backend Connection Settings -

#------------------------------------------------------------------------------

# backend_hostname : Pgpool이 서버에 접속할 때 사용할 hostname 또는 IP

# backend_port : 포트번호

# backend_weight : 백엔드의 Load balancing 비율

# backend_data_directory : 백엔드의 데이터베이스 클러스터 디렉터리

# backend_flag : 백엔드 동작을 제어

# 백엔드를 여러 개 사용할 경우 매개변수 이름 끝에 숫자(DB node ID)를 추가한다.

# 0은 서버를 하나만 사용한다. 숫자가 가장 낮은 DB noid ID가 마스터 노드가 된다.

backend_hostname0 = '10.178.0.1'                      => 1번 OS ip

backend_port0 = 5432

backend_weight0 = 1

backend_data_directory0 = '/pgsql/11/data'

backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '10.178.0.2'                      => 2번 OS ip

backend_port1 = 5432

backend_weight1 = 1

backend_data_directory1 = '/pgsql/11/data'

backend_flag1 = 'ALLOW_TO_FAILOVER'

#------------------------------------------------------------------------------

# FILE LOCATIONS

#------------------------------------------------------------------------------

#클라이언트 인증을 위해 pool_hba.conf를 참조, 기본값은 false

#서버 수가 둘 이상인 경우 postgresql에서 정의한 인증방법이 trust이면 off로 설정 가능

enable_pool_hba = off

# 로그 파일 저장 위치 지정

logdir = '/var/log/pgpool'

#------------------------------------------------------------------------------

# REPLICATION MODE

#------------------------------------------------------------------------------

# replication 모드 활성화/비활성화

replication_mode = off

replicate_select = off

insert_lock = off

#------------------------------------------------------------------------------

# - Degenerate handling -

#------------------------------------------------------------------------------

# 데이터 정합성 검사

# insert나 update, delete시에 반영되는 로우가 다를시 쿼리실행이 중지됨

replication_stop_on_mismatch = off    (1/5 현재 on상태)

#------------------------------------------------------------------------------

# LOAD BALANCING MODE

#------------------------------------------------------------------------------

#pgpool의 load_balance 기능, 위에서 설정한 backend host에 맘대로 왔다갔다하여 붙음

#확인 방법은 $psql -h <pgpool 설치된곳 ip> -p 9999 -U <유저=롤> 이후 select inet_server_addr();

load_balance_mode = on

#------------------------------------------------------------------------------

# MASTER/SLAVE MODE

#------------------------------------------------------------------------------

# Streaming Replication

# 마스터 / 슬레이브 모드 확성화/비활성화

# replication_mode 와 함께 사용할 수 없다. 둘 중 하나만 활성화해서 사용.

//master_slave_mode =  off      설정을 off로 하면 pgpool에서 자체적으로 replication을 함

master_slave_mode = on

master_slave_sub_mode = ‘stream’

#------------------------------------------------------------------------------

# - Streaming -

#------------------------------------------------------------------------------

# Streaming Replication 방식을 사용할 경우 설정

# sr_check_user 설정을 정확히 안하면 primary node 를 찾지 못함(DB 유저 postgres의 계정, 비밀번호, DB정보)

sr_check_period = 10

sr_check_user = 'postgres'

sr_check_password = 'psql'

sr_check_database = 'postgres'

delay_threshold = 0

#------------------------------------------------------------------------------

# FAILOVER AND FAILBACK

#------------------------------------------------------------------------------

# 기본 노드를 찾는 시간

search_primary_node_timeout = 10

---------------------------------------------------------------------

pgpool에서 master, slave 서버에 접근할 수 있도록 설정한다.

vi /etc/pgpool-II/pool_hba.conf

---------------------------------------------------------------------

host all all 10.178.0.0/24 trust

---------------------------------------------------------------------

pgpool을 기동한다.

# pgpool -f /etc/pgpool-II/pgpool.conf -n -D -d > /var/log/pgpool/pgpool.log 2>&1 &

여기서 에러가 나면

https://qastack.kr/ubuntu/50621/cannot-connect-to-postgresql-on-port-5432

오류 메시지는 유닉스 도메인 소켓을 참조하므로 netstat제외하지 않으려면 호출을 조정해야 합니다.

옵션없이 시도하십시오 

netstat -nlp | grep 5432

클라이언트가 연결하려고하는 /tmp/.s.PGSQL.5432것이 아니라 서버가 실제로 소켓에서 수신 대기 하고 /var/run/postgresql/.s.PGSQL.5432있다고 생각합니다. 이것은 유닉스 도메인 소켓 디렉토리의 소스 기본값 /tmp이 데비안 패키징에서로 변경 되기 때문에 데비안 또는 우분투에서 수동 컴파일 또는 타사 PostgreSQL 패키지를 사용할 때 발생하는 일반적인 문제 /var/run/postgresql입니다.

가능한 해결 방법 :

타사 패키지에서 제공 한 클라이언트를 사용하십시오 (call /opt/djangostack-1.3-0/postgresql/bin/psql). 우분투 제공 패키지를 모두 제거 할 수 있습니다 (다른 역 종속성으로 인해 어려울 수 있음).

타사 패키지의 소켓 디렉토리가 Debian / Ubuntu와 호환되도록 수정하십시오.

-H localhost대신 TCP / IP를 통해 연결하는 데 사용하십시오 .

올바른 디렉토리를 가리 키려면 -h /tmp이와 동등한 PGHOST설정을 사용하십시오 .

타사 패키지를 사용하지 마십시오.

vi /var/lib/pgsql/11/data/postgresql.conf의 unix_socket_directories = '/var/run/postgresql, /tmp' 주석 해제 후

pid관련 파일 삭제 후 재부팅하여 해결하였음

pgpool 관련 에러 발생 시, pg_hba.conf 파일 수정

[postgres@policyserver1 ~]$ psql -h localhost -p 9999 -U postgres

psql: ERROR:  unable to read message kind

DETAIL:  kind does not match between master(45) slot[1] (52)

---------------

pgpool 정지

#pgpool stop

-pgpool 기동하고 접속

$psql -h 10.178.0.1 -p 9999 -U postgres

# master, slave 노드정보 확인

postgres=# \x

postgres=# show pool_nodes;

# 현재 접속된 노드 확인

# 로드밸런싱 설정을 해놔서 master, slave 왔다갔다 하면서 붙음(안바뀜)

select inet_server_addr();

Step 3. failover 설정

failover시, master 서버가 slave 서버한테 failover trigger 파일을 전송한다. 이 때 비밀번호 없이 전송하기 위해 ssh key 복사를 해준다.

master 노드 (postgres 계정으로 진행)

ssh-keygen -C postgres@10.178.0.1

엔터- 엔터- 엔터

ssh-copy-id postgres@10.178.0.2     <=이 IP는 상대 IP 넣기

slave 노드 (postgres 계정으로 진행)

ssh-keygen -C 10.178.0.2로 생성

엔터 3번

클라우드 환경이라면 cd .ssh

내부의 id_rsa.pub 파일 내용을 메타데이터의 ssh키에 저장

ssh-copy-id postgres@10.178.0.1

정상 동작 확인(master에서)

ssh -T postgres@10.178.0.2 touch /home/postgres/testfile.txt

해당 위치에 파일 생성된 것 확인

failover_command 설정, pgpool은 master의 장애를 감지하면 failover_command를 수행한다.(master에서 수행)

#vi /etc/pgpool-II/pgpool.conf

-------------------------------------------------------------------------------

failover_command = '/usr/pgsql-11/share/failover.sh %d %H /pgsql/11/data/failover_trigger'

health check 설정 : 자동으로 failover기능이 활성화 되도록 설정

health_check_period = 0 --> 15

health_check_timeout= 20 --> 10

health_check_max_retries = 0 --> 3

health_check_retry_delay = 1

health_check_user = 'postgres'

-------------------------------------------------------------------------------

# failover 스크립트 작성

master 서버에 장애가 생기면 slave 서버에게 trigger 파일을 전송

slave에 trigger 파일이 있으면 master로 전환, trigger 파일 경로는 slave의 /var/lib/pgsql/11/data/recovery.conf 에도 설정(master에서 설정)

$ cd /usr/pgsql-11/share

$ vi failover.sh

-------------------------------------------------------------------------------

if [ $# -ne 3 ]

then

        echo "failover failed_node new_master trigger_file"

        exit 1

fi

FAILED_NODE=$1

NEW_MASTER=$2

TRIGGER_FILE=$3

# Do nothing if standby server goes down

if [$FAILED_NODE = 1]; then

        echo "Standby Server is downed\n" >> /var/log/pgpool/failover.log

        exit 0

fi

echo "failover.sh FAILED_NODE:${FAILED_NODE}; NEW_MASTER:${NEW_MASTER}; at $(date)\n" >> /var/log/pgpool/failover.log

sudo -u postgres  ssh -T postgres@$NEW_MASTER touch $TRIGGER_FILE

exit 0

-------------------------------------------------------------------------------

# failover.sh 권한 변경

$ chmod 775 /usr/pgsql-1/share/failover.sh

pgpool을 재시작

pgpool stop

pgpool -f /etc/pgpool-II/pgpool.conf -n -D -d > /var/log/pgpool/pgpool.log 2>&1 &

master 쪽 stop

$pg_ctl -D /pgsql/11/data/ stop

slave쪽에서 접속하여 확인

$psql -h 10.178.0.2 -p 9999 -U postgres

postgres=# show pool_nodes;

<참고>일반적인 수동 active 전환 방법

$pg_ctl promote -D /var/lib/pgsql/11/data 

failover 후 추가작업(new master가 살아있어야 함, pgpool있는 곳에서 작업)

failover 후 old master를 올려도 자동으로 slave 로 추가되는 기능은 제공되지 않기 때문에 아래와 같은 과정을 수동으로 진행

old master 서버가 failover 되어 new master 가 된 상황 1번 서버를 올려도 자동으로 slave로 추가되지 않으며

pgpool은 이 서버를 인식하지 못함

10.178.0.2(new master)쪽 설정(최초에 설정했으면 수정할 필요 없음)

vi /pgsql/11/data/pg_hba.conf

host     replication      all    0.0.0.0/0   md5

추가

이후 재기동

$pg_ctl -D /pgsql/11/data start

10.178.0.1(old master)에서 진행

$ cd /pgsql/11

$ mv data data_old

$pg_ctl -D /pgsql/11/data stop

Standby Server의 Data 폴더를 보면 이전에 recovery.conf로 만들어 놓은 파일이 recovery.done으로 변경되어 있는 것을 확인할 수 있음, 새로 master가 된 2번기의 데이터 파일 가져오기

$ pg_basebackup -h 10.178.0.2 -D /pgsql/11/data -U repl -v -P -X stream

recovery.done을 .conf로 바꾸고 host ip도 변경

$cd /pgsql/11/data

$mv recovery.done recovery.conf

vi recovery.conf

---------------------------------------------------------------------

standby_mode='on'

# master 노드의 정보를 적는다.

primary_conninfo='host=10.178.0.2 port=5432 user=repl password=repl'

# slave노드에 trigger file이 있으면, slave가 master로 전환된다.

trigger_file='/var/lib/pgsql/11/data/failover_trigger'

---------------------------------------------------------------------

old master up

$ cd /usr/pgsql-11/bin/

$./pg_ctl -D /var/lib/pgsql/11/data/ start

pgpool node로 추가(pgpool에서 up으로 인식되어지는 작업)

# echo "postgres:$(pg_md5 --username=postgres postgres)" >> /etc/pgpool-II/pcp.conf

이거 때문에 password 지정해야 함

$ pcp_attach_node -n 0

Password : postgres (echo에서 설정한 비밀번호 입력해야 함)

new master가 죽었을 경우 slave를 pgpool에 붙여주기 위해서는

pgpool 재기동이 필요

설치 환경

운영체제 : centos 7 3-10.0-1127.13.1.el7.x86_64

DB 버전 : 11.9

구성도

설치 방법

특이사항

• DB 설치 완료 및 기동 상태에서 작업 시작(master만 해당, slave는 설치 후 바로 작업 가능)
• 방화벽은 slave가 master에 접근하여 가져가는 방식이므로 master의 5432를 열어줘야 함
• 기능 분할을 위해 replication 전용의 role을 생성하여 작업(repl)
• 설정파일(postgresql.conf, pg_hba.conf)변경 시 재기동하여야 적용됨(#systemctl restart postgresql-11 혹은 $pg_ctl -D /var/lib/pgsql/11/data restart)
• postgres 설치법은 링크 https://peryh.tistory.com/2 참조하여 설치

master쪽 작업

방화벽 작업

#systemctl stop firewalld                              => 방화벽 자체를 내리는 방법

#fireall-cmd --permanent --add-port=5432/tcp => 5432 port만 영구적으로 열기

#firewall-cmd --reload

#firewall-cmd --list-ports

selinux 작업

#vi /etc/selinux/config

SELINUX=disabled 로 변경                           => SELINUX 자체를 내리는 방법

혹은 아래처럼 옵션 주기

#getsebool -a | grep postgres                      => postgres 옵션에 해당하는 것만 내리는 방법

#setsebool -P postgresql_can_rsync on

#setsebool -P selinuxuser_postgresql_connect_enabled

postgres계정으로 repl role 생성

#su - postgres

$psql

postgres=# create role repl with replication login password 'repl';

확인

postgres=# \du

나가기

postgres=# \q

설정파일 변경(ip대역 및 비밀번호 입력이 번거롭다면 아래 처럼 설정, 제한을 하고 싶다면 ip 제한(192.168.0.0/24) 및 Method 설정(md5)하여 사용)

아래 설정 파일은 접속대역 및 연결 방법에 대한 파일

$vi /var/lib/pgsql/11/data/pg_hba.conf

<METHOD 설정 내용>

trust : 무조건 연결 허용, password나 임의의 다른 인증 없이 연결 가능

md5 : 인증을 위해 double-MD5-hashed 패스워드 제공

ident : 클라이언트의 ident 서버에 접촉하여 운영체제 사용자 이름을 획득하고 데이터베이스 사용자 이름과 일치하는지 검증, TCP/IP에서만 사용 가능

peer : 운영체제 이름과 사용자의 이름이 일치하는지 확인, 로컬연결에서만 사용 가능

postgres 설정 전반에 관한 파일

$vi /var/lib/pgsql/11/data/postgresql.conf

#연결 접속 허용 주소와 포트 주석 해제

listen_addresses =  '*'
port=5432

#wal_level 설정
wal_level = replica
minimal : 충돌 또는 즉시 셧다운으로부터 복구하기 위해 필요한 정보만 기록
replica : WAL 아카이브에 필요한 로깅과 대기 서버에서 읽기 전용 쿼리에 필요한 정보를 추가
              9.6이전버전에는 archive와 hot_standby옵션이 있지만 replica와 동일하게 취급됨
logical : 논리적 디코딩을 지원하는데 필요한 정보를 추가

max_wal_senders = 2

# slave 노드에 WAL 파일을 전송할 때 사용하는 프로세스 개수, WAL sender 역할을 하는 process의 갯수를 의미, slave 개수 + 1 만큼을 설정(ex 현재 구성도에서는 slave가 1개여서 2로 설정, 꼭 최소설정(2개)을 할 필요는 없음)

#wal_keep_segments = 8 # 8-32가 기준 
master서버에 보관할 wal파일의 수를 결정하는 항목, default 0, wal파일의 갯수가 32개가 되었을 때, 33번째 파일이 생성되는 것이 아니라, 1번째 WAL부터 다시 overwrite 하게됨, 주석해지 필수 아님

# Replication slot 생성 시(필수 X)
#max_replication_slots = 10

# Archive 사용시
archive_mode = on
archive_command = 'cp %p /home/postgres/PostgreSQL/9.5/archive%f'

#아카이브 저장 경로(폴더는 수동으로 생성)
-- %p is replaced by the path name of the file to archive
-- %f is replaced by only the file name

# Archive 미사용시
archive_mode = off

저장 후 서비스 재시작
#systemctl restart postgresql-11 혹은 $pg_ctl -D /var/lib/pgsql/11/data restart

<슬롯을 사용하는 경우(선택옵션)>

위 작업을 다 한 후, postgresql 접속하여 작업 수행, replication slot 에러 발생하는 경우가 생겨 추천하지 않음

standby 서버가 replication을 따라가지 못하는 경우를 대비하여 wal로그를 replication_slot에 두고 보관함

사용 시, wal_keep_segments 설정이 필요없음

replication slot 생성(master) 
postgres=# SELECT * FROM pg_create_physical_replication_slot('repl_slot_01');
slot_name | lsn
--------------+-----
repl_slot_01 |
(1 row)

postgres=# select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirme | d_flush_lsn
--------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------+------------
repl_slot_01 | | physical | | | f | f | | | | |
(1 row)

replication slot에러나 문제가 생길 시 해당 슬롯 삭제
postgres=# select pg_drop_replication_slot('repl_slot_01');

이후 서비스 재시작
#systemctl restart postgresql-11 혹은 $/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data restart

slave쪽 작업

패키지(rpm -qa | grep postgres)만 설치 후 작업 수행 가능

기존에 사용하던 것이 있다면 DB종료 후 옮기고 작업 시작

#systemctl stop postgresql-11

#su - postgres

$cd /var/lib/pgsq/11/

$mv data data_old

시스템 계정으로 설치 후 지우고 OS postgres계정을 만들었다면 mv 명령 시 허가거부가 발생할 수 있음

이런 경우 /var/lib/pgsql의 소유자 권한을 확인 후 변경

#chown -R postgres:postgres /var/lib/pgsql

master의 DB 정보를 복사해 옴(master ip를 입력)

$ /usr/pgsql-11/bin/pg_basebackup -h <master OS ip> -D /var/lib/pgsql/11/data -U repl -v -P -X stream

-D : 받아올 master OS의 postgres data경로
-v : 부가적 정보를 표시
-P : 전송 진행 상황을 표기
-X : WAL도 같이 backup하라는 옵션
-stream : 백업이 생성되는 동안 트랜잭션 로그도 같이 함, 이렇게 하면 서버에 대한 두번째 연결이 열리고 백업을 실행하는 동안 트랜잭션 로그 스트리밍이 병렬로 시작됨, 따라서 max_wal_sender 매개 변수로 구성된 두 개의 연결을 사용하게 됨
-fetch 방식은 트랜잭션 로그 파일은 백업이 끝날 때 수집되며, wal_keep_segment 매개 변수를 높게 설정하여 백업이 끝나기 전에 로그가 제거되지 않음

비밀번호 입력

repl -->trust라면 입력 안하고 복사 되어옴

slave 노드가 hot_standby 역할을 하도록 설정

vi /var/lib/pgsql/11/data/postgresql.conf

# 해당 부분의 주석 해제 및 변경

hot_standby = on
hot_standby_feedback = on

해당 DB가 slave임을 알려주는 파일 생성, 생성 안되어 있으면 wal process가 뜨지 않음

slave 노드가 master 노드로 바뀌어야 할 때(failover)를 위한 설정이기도 함

vi /var/lib/pgsql/11/data/recovery.conf

# standby mode 적용

standby_mode='on'

# master 노드의 정보 기입
primary_conninfo='host=<master OS ip> port=5432 user=repl password=repl'

# 슬롯 사용 시 적용

primary_slot_name='repl_slot_01'

# slave노드에 trigger file이 있으면, slave가 master로 전환된다.
trigger_file='/var/lib/pgsql/11/data/failover_trigger'


# Archive 사용시
restore_command = 'cp /home/postgres/PostgreSQL/9.5/archive%f %p'
primary_slot_name = 'stnby1'

slave DB를 기동한다.
$/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data start

master에서 walsender가 뜨지 않으면 /var/lib/pgsql/11/data/log 디렉터리에서 내용 확인

아래같은 로그 발생 시

openssl-libs RPM의 일부인 잘못된 버전의 libcrypto.so가 설치되어 있다는 것을 의미

www.postgresql.org/message-id/7748ff129ea2966e1943ba41f0a6a1f1702edb05.camel%40cybertec.at

따라서, openssl을 설치해 주시면 정상적으로 동작(삭제 안해도 가능)

#yum -y install openssl

이후 다시 기동

$/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data start

양쪽에서 확인

wal 관련 process 떠있는지 확인

$ps -ef | grep wal

DB내에서 master인지 확인, recovery모드가 f(false)여야 master

postgres=# select pg_is_in_recovery();

slave DB를 수동으로 master로 전환하고 싶다면

$/usr/pgsql-11/bin/pg_ctl promote -D /var/lib/pgsql/11/data/

설치 환경

운영체제 : centos 7 3-10.0-1127.13.1.el7.x86_64

DB 버전 : 11.9

Postgres 기본 경로 변경하기

기본 설치 경로(/var/lib/pgsql/11/data)에서 (/pgsql/11/data)로 변경하는 작업

주의 사항

데이터베이스 생성 후, 한번이라도 데이터 베이스를 실행해야함(postmaster.opts 파일이 생성되어야 되기 때문)

data 디렉터리는 권한이 700이여야 함(rwx------) 다른 권한이 있는 경우 permission 에러 발생

상위 디렉터리(pgsql, 11는 권한을 변경해 보았으나 정상적으로 동작, 단 원래 700이니 굳이 설정을 변경하진 말것

또한, 아래는 이미 DB를 실행하여 사용 후 변경한 것이나, 초기 변경하여 실행 시에는 에러 발생했던걸로 기억...)

변경하는 방법

postgres 설치 후 데이터 베이스를 생성

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

데이터 베이스 실행

#systemctl start postgresql-11

생성된 postmaster.opts 파일 수정

#cd /var/lib/pgsql/11/data

#vi postmaster.opts

변경하고자 하는 경로로 변경(#으로 주석처리가 되지 않으니 첫번째 줄 초기 설정은 삭제)

실제 data 디렉터리를 해당 경로로 이동

#mv /var/lib/pgsql /

postgres 유저로 DB 실행

#su - postgres

$/usr/pgsql-11/bin/pg_ctl -D /pgsql/11/data start

root 유저에서 부팅 시 자동수행하고 싶다면(위에서 시작한 DB 종료후 할것, pg_ctl -D /pgsql/11/data stop)

#systemctl enable postgresql11시 나오는 경로의 파일로 진입

#vi /usr/lib/systemd/system/postgresql-11.service

아래 위치 디렉터리 경로 변경

데몬 리로드

#systemctl daemon-reload

이후에 재시작

#systemctl start postgresql-11

정상 부팅 확인

#ps -ef | grep postgres

데이터베이스 접속하여 확인

#su - postgres

$psql -U postgres -d postgres(db명과 계정명이 동일하다면 -U -d 옵션 생략 가능)

postgres=# show data_directory;

설치 환경

운영체제 : Linux centos7.9 3.10.0-1160.36.2.el7.x86_64

DB 버전 : 11.9

특이 사항

• postgres 사용자 생성 후 설치(postgres 패키지 설치 시, 자동으로 시스템 계정의 postgres가 생성되나 추후 failover까지 고려하여 파일을 보내는 경우 등을 고려하면 생성하는 것이 편함, 필수사항 아님)
• 기본적으로 운영체제(Linux Centos 7.9가 설치 완료된 환경에서 시작

설치 방법

OS의 계정을 생성

#useradd postgres

#passwd postgres

postgres

만약 OS 계정 생성 전, 설치를 하여 postgres 유저가 있다면 삭제 후 생성

가급적 시스템 유저를 삭제하기 보단, 패키지를 삭제하고 OS postgres 유저를 생성 후 

패키지 재설치를 추천(시스템 유저 postgres가 소유하고 있던 디렉터리 전부 찾아서 소유권 변경해야 함)

#userdel -r postgres 혹은 

#yum -y remove postgresql11-server postgresql11-contrib

패키지 설치

• yum이 가능한 환경

repository RPM 설치

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

설치 후 확인

#ls -l /etc/yum.repos.d

postgres 설치

#yum install -y postgresql11-server postgresql11-contrib

yum repository를 수정하지 않고 설치 시, 설치가 되지 않거나

#yum -y install postgresql 실행 시 9.x 가 설치된다면 repository를 수정해야함(-U : 패키지를 업그레이드)

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

• 인터넷이 안되는 환경

https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/postgresqldbserver11.group.html 에서 아래 4개를 받아서 리눅스 환경에 옮기기

postgresql11-11.9-1PGDG.rhel7.x86_64.rpm

postgresql11-contrib-11.9-1PGDG.rhel7.x86_64.rpm

postgresql11-libs-11.9-1PGDG.rhel7.x86_64.rpm

postgresql11-server-11.9-1PGDG.rhel7.x86_64.rpm

중간에 의존성 오류 발생 시 발생한 의존성 패키지만 설치 후 다시 설치

설치 순서

postgresql11-libs -> postgresql11 -> postgresql11-server -> postgresql11-contrib 순으로 설치

# rpm -ivh postgresql11-libs-11.5-1PGDG.rhel7.x86_64.rpm

# rpm -ivh postgresql11-11.5-1PGDG.rhel7.x86_64.rpm

# rpm -ivh postgresql11-server-11.5-1PGDG.rhel7.x86_64.rpm

# rpm -ivh postgresql11-contrib-11.5-1PGDG.rhel7.x86_64.rpm

설치 패키지 확인

rpm -qa | grep postgresql

설치 패키지 위치 확인

rpm –qs postgresql11

기본 데이터베이스 생성 및 서비스 등록

Initdb 명령으로 기본 데이터베이스 설치 및 자동실행, postgres 시작

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

#systemctl enable postgresql-11

#systemctl start postgresql-11

postgres 시스템 유저가 있던 상황에서 삭제 후 OS의 postgres유저를 만들 경우

/var/run/postgresql의 디렉터리에 .s.PGSQL.5432.lock 파일을 생성할 수 없어 기동이 불가

해당 디렉터리 권한 변경하여 해결

#chown postgres:postgres /var/run/postgresql

/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql11/data start로는 가급적 실행하지 않길 권고함

systemd(시스템 데몬, 시스템 관리자)가 이것에 대해 알 수 없기 때문에 systemctl로 서버를 시작 및 중지할 수 없음, 따라서 항상 systemctl 사용을 권장하며, pg_ctl reload로 새로고침은 괜찮음

확인

#systemctl status postgresql-11 혹은 #ps -ef | grep postgres

계정, 그룹 생성 및 사용자 프로파일 설정

postgresql 접속

#su - postgres -c 'psql' 혹은  #su - postgres이후 $psql

postgres는 접속 시, OS 유저와 DB 유저, database의 이름이 동일하다면 옵션 없이 바로 접속 가능

원래는 $psql -U postgres -d postgres 하여 postgres유저가 postgres디비에 접속하는 형태로 접속해야 함

DB계정 패스워드 변경

postgres=# alter user postgres password 'psql'; 

postgres=# \password <role name>

OS에서 관리자 Password 변경(-c : shell 명령어를 실행)

#su - postgres -c 'psql'

롤 생성/삭제

postgres=# create role <지정할 role name> with superuser createdb createrole login password '<지정할 password>';

postgres=# drop role <role name>

OS에서 생성/삭제

#sudo -u postgres createuser –interactive

#sudo –u postgres dropuser <role name>

테이블 스페이스 생성(생성 전, OS에서 해당 디렉터리 만들어야 함)

#mkdir /var/lib/pgsql/11/TABLESP

postgres=# create tablespace TABLESP owner dbsec location '/var/lib/pgsql/11/TABLESP';

데이터베이스 생성

postgres=# create database <지정할 데이터베이스 이름> with owner='<role name>';

권한 주기

Alter role <롤 이름> superuser createdb createrole;

특정 데이터베이스 모든 권한 주는 경우

Grant all on database <database name> to <role name>

테이블 모든 권한 주는 경우

GRANT ALL ON <table name> TO <role name>;

다른 role을 사용해서 postgresql 접속

#psql -U <role name> -d <database name> 

단, 이렇게 하기 위해서는 pg_hba.conf 파일 수정

<Method 설정 내용>

trust : 무조건 연결 허용, password나 임의의 다른 인증 없이 연결 가능

md5 : 인증을 위해 double-MD5-hashed 패스워드 제공

ident : 클라이언트의 ident 서버에 접촉하여 운영체제 사용자 이름을 획득하고 데이터베이스 사용자 이름과 일치하는지 검증, TCP/IP에서만 사용 가능

peer : 운영체제 이름과 사용자의 이름이 일치하는지 확인, 로컬연결에서만 사용 가능

따라서, md5나 trust 로 설정하여 지정(vi /var/lib/pgsql/11/data/pg_hba.conf)

IPv4 local connections: 만 변경해도 동작

외부 접속 허용 설정(vi /var/lib/pgsql/11/data/postgresql.conf) 

listen_addresss='*' 로 변경

리스타트

#systemctl restart postgresql-11

• 테스트 쿼리

기본 설정이 완료되었다면, http://www.postgresqltutorial.com/postgresql-sample-database/ 에서 dvdrental tar 파일을 다운받아 restore 수행, 수행 전 전용 데이터베이스 생성(여기서는 dvdrental 이름의 데이터베이스 생성 후 작업)

/var/lib/pgsql/11 에 dvdrental.tar 를 옮겨두기

#su - postgres

$pg_restore -U postgres -d dvdrental /var/lib/pgsql/11/dvdrental.tar

이후 확인

postgres=# \connect dvdrental

postgres=# \dt