설치 환경

운영체제 : 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;

현재 설치된 mariaDB 버전 : 10.4.17

yum을 통하여 설치 시, 기본 경로가 /var/lib/mysql에 DB가 저장된다.

이 DB 경로는 root 파티션에 해당되어, 추후 파티션 용량부족 상황을 초래할 수 있기에 data위치를 변경할 필요가 있다.

1. 경로 정보 확인

#mysql -u root -p

>select @@datadir;

+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.000 sec)

2. mariadb 서비스 정지

#systemctl stop mariadb

3. 새로운 data 디렉터리 생성 및 데이터 복사하기

MariaDB는 오픈 소스의 관계형 데이터베이스 관리 시스템(RDBMS)이다. MySQL과 동일한 소스 코드를 기반으로 하며, GPL v2 라이선스를 따른다

1. MariaDB yum repo 등록

수동 등록 : 원하는 버전의 마리아 DB를 설치할 수 있음

#vi yum.repos.d/MariaDB.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

혹은 설치파일 생성 : 최신 버전의 마리아 DB가 설치, 아래 명령어 입력 시 /etc/yum.repos.d/경로에 mariadb.repo 파일이 생성

#curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

2. MariaDB 설치 및 확인

#yum -y install MariaDB(수동으로 등록된 경우)

#yun -y install MariaDB-server MariaDB-client(설치 파일로 등록된 경우)

# rpm -qa | grep MariaDB
MariaDB-compat-10.4.12-1.el7.centos.x86_64
MariaDB-client-10.4.12-1.el7.centos.x86_64
MariaDB-common-10.4.12-1.el7.centos.x86_64
MariaDB-server-10.4.12-1.el7.centos.x86_64

# mariadb --version
mariadb Ver 15.1 Distrib 10.4.12-MariaDB, for Linux (x86_64) using readline 5.1

3. MariaDB 실행 및 비밀번호 변경

mariadb 리부팅 시 자동실행 

#systemctl enable mariadb

#systemctl disable mariadb

자동실행 상태 확인

#systemctl is-enable mariadb

mariadb 시작 및 종료

#systemctl start mariadb 

#systemctl stop mariadb  

비밀번호 변경 
#/usr/bin/mysqladmin -u root password '변경할 비밀번호 입력' 

포트 및 데몬 이름 확인 
#netstat -anp | grep 3306

4. CharaterSet utf8mb4로 변경

#vi /etc/my.cnf

[mysqld]
default_storage_engine=innodb
init-connect='SET NAMES utf8mb4'
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

[client]
port=3306
default-character-set = utf8mb4
[mysqldump]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4

설정 후 mariadb 재시작

#systemctl restart mariadb

5. 접속방법

#mysql -u root -p

Enter password : 설정한 비밀번호 입력

characterset 확인>show variables like 'c%';

6. MariaDB 삭제

#yum list installed mariadb\*

설치된 mariadb 삭제(조회되는 목록 전부 삭제)

#yum -y remove MariaDB-server-10.5.8-1.el7.centos.x86_64

설치 환경

운영체제 : 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