PostgreSQL 설치부터 이중화까지

이전에 작업했던 PostgreSQL 이중화 방법에 대한 것을 다시 정리해 보았다.

설치(Ubuntu 기준)

1. 먼저 apt-get을 이용하여 postgresql를 설치 한다.

sudo apt-get install wget ca-certificates
wget --quiet -O - <https://www.postgresql.org/media/keys/ACCC4CF8.asc> | sudo apt-key add
sudo sh -c 'echo "deb <http://apt.postgresql.org/pub/repos/apt/> `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install postgresql-10 postgresql-contrib-10

1. PostgreSQL를 설치하면 postgres 사용자가 생성된다.

postgres 사용자 비번 설정은 아래와 같이 하면 된다.

//postgres 로 변경
sudo su - postgres
//비밀번호 변경
alter user postgres with password 'test!!!'

2. 외부접속 허용

기본적인 설정에서는 모든 외부 방문을 차단해 놓았다.

일단 connection를 허용할 아이피 구간을 수정한다.(pg_hba.conf )

sudo vim /etc/postgresql/9.5/main/pg_hba.conf

pg_hba.conf 파일 중 아래 IPv4에 관한 내용을 수정 한다.

또한 listen address의 주석을 풀고 모든 것에 대하여 listen 하도록 해야 한다.(postgresql.conf)

sudo vim /etc/postgresql/9,5/main/postgresql.conf

Replication

Postgresql 에서 이중화를 할 수 있는 방법은 Log-Shipping 방식과 Streaming 방식 두가지가 있다.

여기서는 Streaming 방식으로 구현하는것을 설명 한다.

Master 설정

복제 전용 유저 생성(replication)

CREATE ROLE repluser WITH REPLICATION PASSWORD 'password' LOGIN;

생성한 게정에 대한 권한을 설정(복제 권한 부여)

복제를 위한 설정:

# vim /usr/local/pgsql/data/postgresql.conf
listen_addresses = ‘*’ # 인증/권한 관리는 pg_hba.conf 파일에서 설정
wal_level = replica # 대기 서버에 읽기전용 작업 가능 설정
max_wal=senders = 2 # WAL 파일을 전송할 수 있는 최대 서버 수
wal_keep_segments = 64 # 마스터 서버 디렉토리에 보관할 최근 WAL 파일 수

PostgreSQL 서비스 재시작

sudo service postgresql restart

복사 권한 부여(/etc/postgresql/9.5/main/pg_hba.conf)

# replication privilege.
host    replication     replication      192.168.0.127/24            md5

Slave 설정

PostgreSQL 서비스 종료

1sudo service postgresql stop
2

권한 설정: master 정보 기입(/etc/postgresql/10/main/pg_hba.conf)

host    replication     replication     192.168.0.127/24   md5

서버 설정(/etc/postgresql/10/main/postgresql.conf

listen_addresses = 'localhost,192.168.199.138'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64
hot_standby = on

데이터 디렉토리 삭제

cd /var/lib/postgresql/10/main/
rm -rfv *

Master 서버 데이터 백업

pg_basebackup -h 192.168.0.127 -D /var/lib/postgresql/10/main/ -P -U repluser --wal-method=fetch

recovery.conf 파일을 데이터 폴더에 생성

vim recovery.conf

recovery.conf 내용은 아래와 같다.

standby_mode='on'

primary_conninfo='host=192.168.0.127 port=5432 user=repluser password=test!!!'

trigger_file='/var/lib/postgresql/10/main'

PostgreSQL 서비스 재시작

sudo service postgresql restart

유용한 명령

백업 상태 보기

psql 콘솔에 접근 후

\\x on // expanded display on

아래 명령으로 상태 확인

select * from pg_stat_activity where usename ='repluser';

PostgreSQL 서버 상태

pg_lsclusters

postgreSQL 삭제

sudo apt-get --purge remove postgresql\\*
dpkg -l | grep postgres

위의 두 타입 PostgreSQL 설치 shell script는 아래와 같다.(Ubuntu 16.04 기준)

아래 Script 실행 전 pg_hba.conf, pg_pool.conf, recovery.conf 중 IP를 사용할 host IP로 수정이 필요하다.

실행 방식은 ./setup_postgresql.sh master host_ip or ./setup_postgresql.sh slave host_ip

#!bin/bash
# setup_postgresql.sh
# Run as Root
# This file will install postgreSQL and PGPool
# 1. Check the pg_hba.conf file and pg_pool.conf -> modify the ip adress to the master ip and slave ip
# 2. recovery.conf file have primary_conninfo, this value must be master DB conn string

#check parameter
if [ -z "$2" ]; then
 echo "The option is empty, please enter hostname or IP adress"
 exit 1
fi

if [ -z "$1" ]; then
 echo "Please enter mode: master of slave"
 exit 1
fi

#mode = "$1"
#host = "$2"
echo "The input of host type is ${$1}"
echo "Start to install openssh, PostgreSQL-10"
sudo apt-get install openssh-server
sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install postgresql-10 postgresql-contrib-10 #install postgresql10
29
#slave install pgpool
if [ $1 = "slave" ]; then
echo "Start to install pgpool2"
sudo apt-get install pgpool2 # install pgpool
fi
echo "Copy config file to the PostgreSQL path"
# copy different confif file
if [ $1 = "master" ]; then
sudo cp ./postgresql.conf /etc/postgresql/10/main/
else
sudo cp ./postgresql_slave.conf /etc/postgresql/10/main/postgresql.conf
fi

echo "Copy hba file and create repluser and change postgres passwd"
#setting hba file
sudo cp ./pg_hba.conf /etc/postgresql/10/main/
sudo -u postgres psql -c "CREATE ROLE repluser WITH REPLICATION PASSWORD 'test!!!' LOGIN;"
sudo -u postgres psql -c "alter user postgres with password 'test!!!';"

#setting firewall
sudo ufw enable
sudo alow 22/tcp
sudo alow 5432/tcp

#service postgresql restart
#echo $1
if [ $1 = "slave" ]; then
sudo cp ./pgpool.conf /etc/pgpool2/
rm -rfv /var/lib/postgresql/10/main/*
pg_basebackup -h $2 -D /var/lib/postgresql/10/main/ -P -U repluser --wal-method=fetch
cp ./recovery.conf /var/lib/postgresql/10/main/
chown postgres:postgres -R /var/lib/postgresql/10/main/
fi
echo "restart postgresql servser"
service postgresql restart

if [ $1 = "slave" ]; then
mkdir /var/log/pgpool/
echo "restart pgpool server"
pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 &
fi

ref :

https://linuxhint.com/setup_postgresql_replication/

https://browndwarf.tistory.com/4

https://blog.naver.com/PostView.nhn?blogId=sehyunfa&logNo=221851706103&categoryNo=93&parentCategoryNo=0&viewDate=&currentPage=1&postListTopCurrentPage=1&from=postView

https://medium.com/qodot/postgresql-replication-구축하기-dfd481c4fb04