Category: Database

Database 글을 모아 놓은 카테고리

Oracle 12c 설치

Oracle 12c 설치

Silent Installation 방식이며 OFA(Optimal Flexible Architecture) 를 따른다.

환경

  • CentOS 7 64bit
  • Memory 8GB
  • Swap 16GB (Swap must be enabled double the size of RAM)
  • Storage Size 40GB

호스트네임 변경

호스트 네임 변경
ZSH
1
2
3
4
hostnamectl set-hostname oraserv1
hostnamectl set-hostname oraserv1 --static
hostnamectl set-hostname "Server1 of Oracle Database" --pretty
hostnamectl set-hostname oraserv1 --transient

 

계정생성

계정, 그룹 생성
ZSH
1
2
3
4
5
6
7
8
9
10
11
groupadd -g 500 oinstall
groupadd -g 501 dba
groupadd -g 502 oper
groupadd -g 503 backupdba
groupadd -g 504 dgdba
groupadd -g 505 kmdba
groupadd -g 506 asmdba
groupadd -g 507 asmoper
groupadd -g 508 asmadmin
 
useradd -u 500 -g oinstall -G dba,oper,backupdba,dgdba,kmdba oracle

 

의존성 패키지 설치

의존성 패키지 설치
ZSH
1
yum install compat-libcap1-1.10 compat-libstdc++-* libstdc++-devel-* sysstat-10.1.5* gcc-4.* gcc-c++-4.* ksh* glibc-devel-* libaio-* libaio-devel-*

 

OFA 디렉토리 생성

OFA 디렉토리 생성
ZSH
1
2
3
mkdir -p /u01/app/oracle/product/12.1.0.2/db_1
mkdir -p /u01/app/oraInventory
chown oracle:oinstall -R /u01

 

sysctl.conf

Vim
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
fs.aio-max-nr = 1048576
# It is recommended to have a minimum of 256 as value for every 4MB of RAM you have.
# So for 8GB RAM = 2048 * 4MB = 2048 * 256 = 524288
fs.file-max = 6815744
# It sets the maximum number of shared memory segments that server can handle.
# As Oracle recommends the value should be at least 4096,
# it says that we cannot find more than 4096 number of shared memory segments at any instance on the server.
# Note that SHMMNI value is in numbers.
#
# No change it should be 4096. It must be increased if you have more than at least one fourth (1024) Oracle Databases running on the server. Which we never recommend.
kernel.shmmni = 4096
# It defines the total amount of shared memory PAGES that can be used system-wide.
# It means that to the use all the physical memory this value should be less than or equal to total physical memory size.
# For DBA's, it means that sum of all SGA sizes on the server should be less than or equal to SHMALL value.
# Note that SHMALL value is a number of pages.
#
# If you want the maximum size of SGA on this server to be 5GB, then this parameter value should be 5*1024*1024*1024 = 5368709120 bytes. This, in turn, says that you should not have any database with more than 5GB of SGA. But you can have multiple databases with each 5GB of SGA or even less. This is the fact why Oracle recommends to have this value more than half of the memory to utilize it for SGA(s).
#
# By chance, if your SGA size is more than 5GB say it is 7GB then 2 shared memory areas will be allocated to SGA with one of 5GB and two of 2GB sizes, which doesn's perform well.
kernel.shmmax = 4294967296
 
#  It defines the total amount of shared memory PAGES that can be used system-wide. It means that to the use all the physical memory this value should be less than or equal to total physical memory size. For DBA's, it means that sum of all SGA sizes on the server should be less than or equal to SHMALL value. Note that SHMALL value is a number of pages.
# By default the page size on Linux is 4KB. The total size of RAM is 8GB. Let us leave at least 1GB of RAM for Linux kernel to run, with which consider 7GB can be used for Oracle Databases. Now value of SHMALL can be:
#
# (7*1024*1024)KB/4KB = 1835008
kernel.shmall = 1835008
 
kernel.sem = 250 32000 100 128
 
# This parameter defines the range of port numbers that system can use for programs which want to connect to the server without a specific port number.
#
# Now, it makes sense if you have come across somebody advising you not to use port numbers for listener beyond 9000. Also, just look back to documents on OEM installation, Oracle uses and advises all the default port numbers less than 9000.
net.ipv4.ip_local_port_range = 9000 65500
 
# This parameter defines the default  and maximum RECEIVE socket memory through TCP.
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
 
# This parameter defines the default  and maximum SEND socket memory through TCP.
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

 

/etc/security/limits.conf

1
2
3
4
5
6
7
# http://www.tecmint.com/setting-up-prerequisites-for-oracle-12c-installation/
oracle          soft    nofile  1024
oracle          hard    nofile  65536
oracle          soft    nproc   2047
oracle          hard    nproc   16384
oracle          soft    stack   10240
oracle          hard    stack   32768

 

/etc/security/limits.d/20-nproc.conf

1
2
*          -    nproc     16384
root       soft    nproc     unlimited

 

/etc/oraInst.loc

1
2
3
4
5
6
]# vim /etc/oraInst.loc
inventory_loc=/u01/app/oraInventory
inst_group=oinstall
 
]# chown oracle:oinstall /etc/oraInst.loc
]# chmod 664 /etc/oraInst.loc

 

oracle 계정으로 로그인 한후에 Oracle 12c 바이너리 설치 파일을 다음과 같이 압축 해제.

ZSH
1
2
3
4
5
]$ mkdir -p orainst/12.1.0.2
]$ cp *.zip orainst/12.1.0.2/
]$ cd orainst/12.1.0.2
]$ unzip linuxamd64_12102_database_1of2.zip
]$ unzip linuxamd64_12102_database_2of2.zip

 

Silent 설치를 위한 파일 작성

ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$ cd orainst/12.1.0.2/database
$ cp response/db_install.rsp inst.rsp
$ vim inst.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.1.0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=oraserv1
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper
oracle.install.db.BACKUPDBA_GROUP=backupdba
oracle.install.db.DGDBA_GROUP=dgdba
oracle.install.db.KMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=dba
DECLINE_SECURITY_UPDATES=true

 

Silent 설치 파일 설행.

1
2
3
4
5
6
7
8
9
10
11
12
[oracle@oraserv1 database]$ ./runInstaller -ignoreSysPrereqs -force -silent -responseFile /home/oracle/orainst/12.1.0.2/database/inst.rsp
[oracle@oraserv1 database]$ Oracle Database 12c의 설치을(를) 성공했습니다.
자세한 내용은 '/u01/app/oraInventory/logs/silentInstall2017-02-14_11-04-17PM.log'을(를) 확인하십시오.
 
루트 사용자로 다음 스크립트를 실행합니다.
1. /u01/app/oracle/product/12.1.0.2/db_1/root.sh
 
 
 
Successfully Setup Software.
 
[oracle@oraserv1 database]$

설치 시간은 시스템에 따라 다르지만 설치하는 동안에 java 프로세스가 동작한다. 그리고 설치가 성공하면 위와같이 메시지가 나온다.

oracle bashrc 설정.

.bashrc of Oracle
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
export ORACLE_BASE=/u01/app/oracle
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ADR_BASE=$ORACLE_BASE/diag
export ORACLE_SID=o12c
 
export LD_LIBRARY_PATH=/usr/lib64:$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export PATH=$ORACLE_HOME/bin:$PATH
 
export PS1='[\h:\u:\W:${ORACLE_SID}]$ '
 
alias cdob='cd $ORACLE_BASE'
alias cdoh='cd $ORACLE_HOME'
alias tns='cd $ORACLE_HOME/network/admin'
alias envo='env | grep ORACLE'
 
umask 022
 
envo

 

Create Database

ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#]$ cd $ORACLE_HOME/dbs
#]$ cp init.org inito12c.ora
db_name='o12c'
memory_target=1G
memory_max_target=1G
processes = 200
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=499
remote_login_passwordfile='EXCLUSIVE'
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/dbfile/o12c/control01.ctl, /u02/dbfile/o12c/control02.ctl)
job_queue_processes=10
fast_start_mttr_target=500
 
compatible ='11.2.0'

inito12c.ora 를 위와 같이 작성하고 다음과 같이 디렉토리를 작성한다.

create directory for Creating database
ZSH
1
2
3
4
5
6
7
8
$ mkdir -p /u01/app/oracle/admin/orcl/adump
$ mkdir -p /u01/app/oracle/fast_recovery_area
$ mkdir -p /u01/dbfile/o12c
$ mkdir -p /u02/dbfile/o12c
$ mkdir -p /u01/oraredo/o12c
$ mkdir -p /u02/oraredo/o12c
# chown -R oracle:dba /u01
# chown -R oracle:dba /u02

다음과 같은 내용으로 credb.sql 파일로 작성한다.

Create database SQL
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE DATABASE o12c
  MAXLOGFILES 16
  MAXLOGMEMBERS 4
  MAXDATAFILES 1024
  MAXINSTANCES 1
  MAXLOGHISTORY 680
  CHARACTER SET AL32UTF8
DATAFILE
'/u01/dbfile/o12c/system01.dbf'
   SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE undotbs1 DATAFILE
'/u01/dbfile/o12c/undotbs01.dbf'
  SIZE 800M
SYSAUX DATAFILE
'/u01/dbfile/o12c/sysaux01.dbf'
  SIZE 500M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/u01/dbfile/o12c/temp01.dbf'
  SIZE 500M
DEFAULT TABLESPACE USERS DATAFILE
'/u01/dbfile/o12c/users01.dbf'
  SIZE 20M
LOGFILE GROUP 1
       ('/u01/oraredo/o12c/redo01a.rdo',
       '/u02/oraredo/o12c/redo01b.rdo') SIZE 50M,
       GROUP 2
       ('/u01/oraredo/o12c/redo02a.rdo',
       '/u02/oraredo/o12c/redo02b.rdo') SIZE 50M,
       GROUP 3
       ('/u01/oraredo/o12c/redo03a.rdo',
       '/u02/oraredo/o12c/redo03b.rdo') SIZE 50M
USER sys    IDENTIFIED BY foo
USER system IDENTIFIED BY foo;

oracle 을 nomount 로 시작시킨다. 그리고 위 credb.sql 를 실행한다.

Oracle PL/SQL
1
2
3
4
$ sqlplus / as sysdba
SQL> startup nomount;
SQL> @credb.sql
Database created.

Create a Data Dictionary

Oracle PL/SQL
1
2
3
4
5
6
7
8
SQL> show user
USER is "SYS"
 
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
 
SQL> connect system/foo
SQL> @?/sqlplus/admin/pupbld

Configure Listener

listener.ora
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
]$ vim $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=oraserv1)(PORT=1521)))
 
SID_LIST_LISTENER=
   (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=o12c)
          (SID_NAME=o12c)
          (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
 
        )
       )

외부인증을 위한 패스워드 파일을 생성한다. 이것을 생성하지 않으면 외부로부터 접속을 할때에 인증정보가 아무리 맞아도 ora-01017 메시지를 내보낸다. 다음과 같이 패스워드 파일을 생성해 준다.

패스워드 파일 생성
ZSH
1
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=패스워드 entries=20

listener 를 시작.

lsnrctl start
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
[oracle@oraserv1 admin]$ lsnrctl start
 
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-FEB-2017 21:14:07
 
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
 
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oraserv1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraserv1)(PORT=1521)))
 
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                17-FEB-2017 21:14:07
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraserv1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraserv1)(PORT=1521)))
The listener supports no services
The command completed successfully

EM Database Express 12c 활성화.

Check DB Express 12c port
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> select dbms_xdb_config.gethttpport() from dual;
 
DBMS_XDB_CONFIG.GETHTTPPORT()
----------------------
     0
 
SQL> exec dbms_xdb_config.sethttpport(8080);
 
PL/SQL procedure successfully completed.
 
SQL> select dbms_xdb.gethttpport() from dual;
 
DBMS_XDB.GETHTTPPORT()
----------------------
  8080

lsnrctl status 에서 8080 포트가 보이면 정상. http://ip:8080/em 으로 접속해서 로그인 화면 나오면 정상입니다.

Postgresql 9.x Replication – Streaming Log

Streaming log replication 은 Postgresql 9.0 부터 도입된 기능이다.  이 기능은 Primary 에서 Standby 서버로 직접 전송함으로서 replication delay 를 줄여준다. 따라서 pg_xlog 파일전송이 필요가 없다. 또 xlog 를 Streaming 으로 받기 위해서 Primary 서버에 REPLICATION 권한의 접속 계정이 필요하다.

Replication 권한 사용자 생성.

Primary 서버에서 REPLICATION 권한의 사용자를 다음과 같이 생성해준다.

Replication 계정생성
PgSQL
1
CREATE ROLE repuser WITH REPLICATION PASSWORD '12345' LOGIN

그리고 Standby 서버에서 Primary 접속을 위해서 pg_hba.conf 파일을 Standby 서버 접속을 허용해 줍니다.

repuser 사용자에 대해서 Standby 서버 접속허용 추가
1
2
3
# The standby server must connect with a user that has replication privileges.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host       replication           repuser     192.168.96.26/32            md5

중요한 것은 DATABASE 에 반드시 ‘replication’ 이여야 한다.

Streaming Replication 을 위한 postgresql.conf 를 다음과 같이 설정한다.

Streaming Replication 을 위한 Primary 설정.
1
2
3
4
5
wal_level = archive
max_wal_senders = 2
wal_keep_segments = 32
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'

데이터백업/복구.

PostgreSQL 9.3 이후부터 pg_basebackup 명령어를 지원한다. 이 명령어는 REPLICATION 권한을 가진 사용자로 접속을해 Primary 의 데이터 디렉토리를 원격에서 로컬로 복제를 해준다.

Standby 서버를 추가할때에 Primary 서버의 데이터 디렉토리를 복제해야 한다. 앞에서 생성한 계정을 이용하면 된다.

pg_basebackup 실행.
ZSH
1
2
3
4
5
6
7
8
]# sudo -u postgres /opt/pgsql-9.5.0/bin/pg_basebackup -h 192.168.96.24 -D /opt/pgsql-9.5.0/data -U repuser -v -P --xlog-method=stream
Password:
transaction log start point: 1/48000028 on timeline 1
pg_basebackup: starting background WAL receiver
486611/486611 kB (100%), 1/1 tablespace
transaction log end point: 1/480000C0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

정상적으로 실행이 되면 위와같이 나온다.

Streaming logging replication

pg_basebackup 을 실행해 Primary 데이터 디렉토리가 복제했고 이로 인해서 postgresql.conf 파일도 Primary 것일 것이다. 이것을 최초의 파일로 교체해 준다.

그리고 recovery.conf 파일을 다음과 같이 작성해 준다.

recovery.conf 파일.
1
2
3
standby_mode = on
primary_conninfo = 'host=192.168.96.24 port=5432 user=repuser password=12345'
trigger_file = '/tmp/postgresql.trigger.5432'

그리고 Standby 서버를 시작해 준다.

로그파일을 보면 다음과 같이 나온다.

Standby log file.
ZSH
1
2
3
4
5
LOG:  database system was interrupted; last known up at 2016-10-02 01:00:13 KST
LOG:  entering standby mode
LOG:  redo starts at 1/48000098
LOG:  consistent recovery state reached at 1/480000C0
LOG:  started streaming WAL from primary at 1/49000000 on timeline 1

그리고 프로세스를 살펴보면 다음과 같다.

Standby 서버에서 PostgreSQL 프로세스 상태.
ZSH
1
2
3
4
5
6
7
]# ps aux | grep post
postgres  1740  0.3  2.4 442504 25112 ?        S    01:14   0:00 /opt/pgsql-9.5.0/bin/postgres -D /opt/pgsql-9.5.0/data
postgres  1741  0.2  0.8 442544  8392 ?        Ss   01:14   0:00 postgres: startup process   recovering 00000001000000010000004A
postgres  1746  0.0  0.7 442504  7820 ?        Ss   01:14   0:00 postgres: checkpointer process
postgres  1747  0.0  0.7 442504  8156 ?        Ss   01:14   0:00 postgres: writer process
postgres  1748  1.0  0.9 447256  9296 ?        Ss   01:14   0:00 postgres: wal receiver process   streaming 1/4B000000
root      1752  0.0  0.0 103284   764 pts/0    S+   01:14   0:00 grep post

Primary 서버에서 Streaming Replication 상태를 다음과 같이 조회해 볼수 있다.

Primary 서버에서 Replication 상태 체크.
PgSQL
1
SELECT S.pid, S.usesysid, U.rolname AS usename, S.application_name, S.client_addr, S.client_port, S.backend_start FROM pg_stat_get_activity(NULL) AS S, pg_authid U WHERE S.usesysid = U.oid AND S.datid = 0;

혹은 pg_stat_replication 을 조회해도 된다.

pg_stat_replication 조회.
PgSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 10285
usesysid         | 16668
usename          | repuser
application_name | walreceiver
client_addr      | 192.168.96.26
client_hostname  |
client_port      | 41866
backend_start    | 2016-10-02 01:14:40.954436+09
backend_xmin     |
state            | streaming
sent_location    | 1/4E000000
write_location   | 1/4E000000
flush_location   | 1/4E000000
replay_location  | 1/4E000000
sync_priority    | 0
sync_state       | async

하지만 위 방법은 Standby 서버에는 접속할 수 없다. 접속을 시도하면 다음과 같은 에러를 낸다.

ZSH
1
psql: FATAL: the database system is starting up

Hot Standby 세팅.

Hot Standby 는 Standby 서버에 접속해 읽기전용의 SQL 연산을 수행할 수 있도록 해준다. 이를 위해서는 먼저 Primary 서버에서 다음과 같이 postgresql.conf 파일에 Hot Standby 설정을 해줘야 한다.

Primary 의 Hot Standby 설정.
1
wal_level = 'hot_Standby'

Standby 서버도 다음과 같이 설정을 변경해 준다.

Standby 서버에서 Hot Standby 설정.
1
hot_Standby = on

이제 서버들을 재시작해줘야 하는데 순서가 중요하다. 다음과 같은 순서로 재시작을 해준다.

  1. Standby 서버 Shutdown
  2. Primary 서버 재시작.
  3. Standby 서버 시작.

Standby 서버의 log 파일을 살펴보면 다음과 같다.

Hot Standby 서버의 로그.
1
2
3
4
5
6
7
]# tail -f data/logfile
LOG:  database system was interrupted; last known up at 2016-10-02 01:49:20 KST
LOG:  entering standby mode
LOG:  redo starts at 1/60000060
LOG:  consistent recovery state reached at 1/60000130
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 1/61000000 on timeline 1

프로세스는 다음과 같이 나온다.

Hot Standby 서버의 프로세스들.
ZSH
1
2
3
4
5
6
7
8
]# ps aux | grep post
postgres  2049  0.4  2.4 442500 25140 ?        S    01:49   0:00 /opt/pgsql-9.5.0/bin/postgres -D /opt/pgsql-9.5.0/data
postgres  2050  0.4  0.8 442540  8440 ?        Ss   01:49   0:00 postgres: startup process   recovering 000000010000000100000062
postgres  2051  0.0  0.7 442500  7816 ?        Ss   01:49   0:00 postgres: checkpointer process
postgres  2052  0.0  0.7 442500  8096 ?        Ss   01:49   0:00 postgres: writer process
postgres  2054  0.0  0.7 163404  7648 ?        Ss   01:49   0:00 postgres: stats collector process
postgres  2055  1.1  0.9 447124  9292 ?        Ss   01:49   0:00 postgres: wal receiver process   streaming 1/62000060
root      2067  0.0  0.0 103284   764 pts/0    S+   01:50   0:00 grep post

테스트.

Primary 서버에 테이블을 하나 만들고 데이터를 입력해보고 이것이 Standby 서버로 잘 가는지를 살펴본다.

Primary 서버에서 테이블 생성 및 데이터 입력.
PgSQL
1
2
3
4
test=# CREATE TABLE guestbook (visitor_email text, vistor_id serial, date timestamp, message text);
CREATE TABLE
test=# INSERT INTO guestbook (visitor_email, date, message) values ('a@abc.com', current_date, 'This is a test.');
INSERT 0 1

그리고 Standby 서버에서 guestbook 테이블이 존재하고 데이터가 존재하는지 살펴본다.

Standby 서버에서 guestbook 테이블 및 데이터 확인.
1
2
3
4
5
6
7
8
9
10
11
12
test=# \d
                      List of relations
Schema |             Name             |   Type   |  Owner
--------+------------------------------+----------+----------
public | guestbook                    | table    | postgres
public | guestbook_vistor_id_seq      | sequence | postgres
 
test=# select * from guestbook;
visitor_email | vistor_id |        date         |     message
---------------+-----------+---------------------+-----------------
a@abc.com     |         1 | 2016-10-02 00:00:00 | This is a test.
(1 row)

잘 보이면 정상이다.

M-HA MySQL 설정

MySQL 은 인기있는 오픈소스 데이터베이스 시스템이다. 언제나 데이터베이스 시스템이라면 항상 따라오는 것이 Master-Slave 리플리케이션 환경이고 거기에 덧붙여 자동 페일오버(Auto FailOver) 를 어떻게 할 것이라는 골머리 앓는 주제가 따라서 나온다.

MySQL 의 경우에 자체 적인 FailOver 솔루션이 있다. Fabric 이 그것인데, 아직은 많이 쓰이지는 않는 모양이다. 대신에 AutoFailOver 를 지원하는 제3의 툴들이 존재하는데 M-HA 가 바로 이러한 솔루션이다.

M-HA 는 일본인이 Perl 을 이용해서 작성한 솔루션이다. github 저장소에 소스코드는 공개되어 있으며 저장소이름은 mha4mysql-manager 이다.

구조

M-HA 는 Manager 와 Node  로 이루어진다. Node 는 MySQL 이 설치된 서버에 모두 설치해줘야 한다. Mater/Slave 구조라면 Node 를 모두 설치해줘야 한다.

Manager 는 이들 Node 를 감시하고 이들 전체를 컨트롤하는 역활을 한다.

M-HA 특징

M-HA 를 AutoFailover 로 사용할만한 이유는 다음과 같다.

  • Short downtime
  • MySQL-Replication consistency
  • Easy Installation
  • No chagne to existing deployments

M-HA  를 고려할때에 중요한 부분이 짧은 다운타임이다.  Master/Slave 구조에서 장애시에  Slave 를 Master 로 빠르게 승격을 시켜주어야 한다. 그래야만 두 서버간에 데이터가 달라지는 데이터 무결성이 깨지지 않게 된다. M-HA 는 비교적 이러한 데이터 무결성을 보장하기 위해서 많은 노력을 기울인 솔루션이라고 보면 된다.

M-HA 구성도

최소한의 환경에서 M-HA 를 구성도는 대략 다음과 같다.

M-HA Architecture
M-HA Architecture

M-HA Node 는  MySQL 이 설치된 서버라면 전부 설치해주어야 한다. M-HA Manager 는 외부서버에 설치해된다.

Node 설치

설치는 M-HA 구글 저장소에 가면 각 배포판별로 바이너리로 배포하고 있어 쉽게 설치가능하다. 여기서는 소스를 가지고 컴파일 설치하도록 하겠다.

git 저장소에 소스를 다운받거나 Clone 해서 가지고 와도 된다.

Clone M-HA Source code from github.com
ZSH
1
2
3
4
5
6
7
]# git clone https://github.com/yoshinorim/mha4mysql-node.git
Cloning into 'mha4mysql-node'...
remote: Counting objects: 374, done.
remote: Total 374 (delta 0), reused 0 (delta 0), pack-reused 373
Receiving objects: 100% (374/374), 84.63 KiB | 0 bytes/s, done.
Resolving deltas: 100% (203/203), done.
Checking connectivity... done.

의존성 패키지를 다음과 같이 설치해준다.

Installation dependency package in Ubuntu
1
]# apt-get install libdbd-mysql-perl libmodule-install-perl build-essential

Perl 을 이용해서 소스를 코드를 컴파일 하고 설치해주면 된다.

mha node 설치.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
# cd mha4mysql-node/
# perl Makefile.PL PREFIX=/usr/local
include /usr/src/mha4mysql-node/inc/Module/Install.pm
include inc/Module/Install/Metadata.pm
include inc/Module/Install/Base.pm
include inc/Module/Install/Makefile.pm
include inc/Module/Install/Scripts.pm
include inc/Module/Install/AutoInstall.pm
include inc/Module/Install/Include.pm
include inc/Module/AutoInstall.pm
*** Module::AutoInstall version 1.16
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.634)
- DBD::mysql ...loaded. (4.033)
*** Module::AutoInstall configuration finished.
include inc/Module/Install/WriteAll.pm
include inc/Module/Install/Win32.pm
include inc/Module/Install/Can.pm
include inc/Module/Install/Fetch.pm
Checking if your kit is complete...
Warning: the following files are missing in your kit:
META.yml
Please inform the author.
Generating a Unix-style Makefile
Writing Makefile for mha4mysql::node
Writing MYMETA.yml and MYMETA.json
Writing META.yml
# make
cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm
cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm
cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm
cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm
cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm
cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm
cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm
cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm
cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm
cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/filter_mysqlbinlog
cp bin/save_binary_logs blib/script/save_binary_logs
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/save_binary_logs
cp bin/purge_relay_logs blib/script/purge_relay_logs
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/purge_relay_logs
cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/apply_diff_relay_logs
Manifying 4 pod documents
# make install
Manifying 4 pod documents
Installing /usr/local/share/perl/5.22.1/MHA/BinlogHeaderParser.pm
Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFinder.pm
Installing /usr/local/share/perl/5.22.1/MHA/SlaveUtil.pm
Installing /usr/local/share/perl/5.22.1/MHA/NodeConst.pm
Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFindManager.pm
Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFinderElp.pm
Installing /usr/local/share/perl/5.22.1/MHA/BinlogManager.pm
Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFinderXid.pm
Installing /usr/local/share/perl/5.22.1/MHA/NodeUtil.pm
Installing /usr/local/man/man1/save_binary_logs.1p
Installing /usr/local/man/man1/purge_relay_logs.1p
Installing /usr/local/man/man1/filter_mysqlbinlog.1p
Installing /usr/local/man/man1/apply_diff_relay_logs.1p
Installing /usr/local/bin/purge_relay_logs
Installing /usr/local/bin/filter_mysqlbinlog
Installing /usr/local/bin/apply_diff_relay_logs
Installing /usr/local/bin/save_binary_logs
Appending installation info to /usr/local/lib/x86_64-linux-gnu/perl/5.22.1/perllocal.pod

위와 같은 설치를 Master/Slave 모든 MySQL 서버에 설치해 준다.

그리고 각 서버에 M-HA 가 접속하기 위한 MySQL 계정을 생성해 준다.

mhausr MySQL 계정 생성
MySQL
1
2
mysql> grant all on *.* to 'mhauser'@'192.168.96.%'  identified by '14321';
mysql> flush privileges;

MySQL 각 서버에 /var/log/masterha 디렉토리를 만들어 준다.

디렉토리 생성
ZSH
1
]# mkdir -p /var/log/masterha

Manager 설치

한가지 주의해야 할 것은 Manager 를 설치하기 전에 Node 도 같이 설치해줘야 한다는 것이다. Manager 설치도 소스코드를 다운받아 설치한다. 그 전에 다음과 같이 의존성 패키지를 설치해준다.

mha-manager 의존성 패키지 설치.
ZSH
1
]# apt-get install libdbd-mysql-perl libmodule-install-perl build-essential libconfig-tiny-perl liblog-dispatch-perl libparallel-forkmanager-perl

소스코드를 다운받는다.

mha manager 설치
ZSH
1
2
3
4
5
6
# git clone https://github.com/yoshinorim/mha4mysql-manager.git
Cloning into 'mha4mysql-manager'...
remote: Counting objects: 1383, done.
remote: Total 1383 (delta 0), reused 0 (delta 0), pack-reused 1383
Receiving objects: 100% (1383/1383), 371.44 KiB | 305.00 KiB/s, done.
Resolving deltas: 100% (830/830), done.

컴파일 설치해 준다.

ZSH
1
2
3
]# perl Makefile.PL PREFIX=/usr/local
]# make
]# make install

Manager 에 mha.cnf 파일 작성

Manager 의 mha.cnf 파일을 다음과 같이 작성한다.

mha.cnf 설정 파일
INI
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[server default]
# mysql user and password
user=mhauser
password=14321
# working directory on the manager
manager_workdir=/var/log/masterha
# working directory on MySQL servers
remote_workdir=/var/log/masterha
 
[server1]
hostname=192.168.96.30
 
[server2]
hostname=192.168.96.32

SSH 접속 설정

M-HA 는 내부적으로 SSH를 이용해서  M-HA Node 의 릴레이 로그들을 전송하도록 한다. 그런데 자동으로 이게 되게 할라면 M-HA Manager 와 Node 들간 모두 SSH 를 무인증으로 접속이 가능해야 한다.

SSH RSA 를 생성하는데, 패스워드를 입력하지 않는다. 그러면 무인증 접속이 가능해진다.

SSH RSA 키 생성.
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:Ac6WpLXcWE58hFcxb3RPiALuejV2XZzMhn5oRb4qbxk root@192.168.96.30
The key's randomart image is:
+---[RSA 2048]----+
|      +.++..+o.oo|
|     B @o + .+B+o|
|    . O =o . .oB+|
|     . . .  .o+..|
|        S + .+.o |
|       . o o.Eo  |
|      . .  . .o  |
|       .    oo   |
|            ..   |
+----[SHA256]-----+

각각에 생성한 키들은 ~/.ssh/authorized_keys  파일에 추가해준다. 중요한 것은 각각 서버들은 서로서로 모두 무인증 SSH 접속이 가능해야 한다는 것이다.

SSH 무인증 테스트를 다음과 같이 할 수 있다.

M-HA 의 무인증 SSH 테스트.
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
masterha_check_ssh --conf=/usr/local/etc/mha.cnf
Sun Jul 17 21:01:07 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Jul 17 21:01:07 2016 - [info] Reading application default configuration from /usr/local/etc/mha.cnf..
Sun Jul 17 21:01:07 2016 - [info] Reading server configuration from /usr/local/etc/mha.cnf..
Sun Jul 17 21:01:08 2016 - [info] Starting SSH connection tests..
Sun Jul 17 21:01:09 2016 - [debug]
Sun Jul 17 21:01:08 2016 - [debug]  Connecting via SSH from root@192.168.96.30(192.168.96.30:22) to root@192.168.96.32(192.168.96.32:22)..
Sun Jul 17 21:01:08 2016 - [debug]   ok.
Sun Jul 17 21:01:09 2016 - [debug]
Sun Jul 17 21:01:08 2016 - [debug]  Connecting via SSH from root@192.168.96.32(192.168.96.32:22) to root@192.168.96.30(192.168.96.30:22)..
Sun Jul 17 21:01:09 2016 - [debug]   ok.
Sun Jul 17 21:01:09 2016 - [info] All SSH connection tests passed successfully.

Replication 체크

다음과 같에 Replication 체크를 해본다.

masterha_check_repl 로 Replication 체크
ZSH
1
2
3
4
5
6
7
8
9
10
11
# masterha_check_repl --conf=/usr/local/etc/mha.cnf
Sun Jul 17 21:26:00 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Jul 17 21:26:00 2016 - [info] Reading application default configuration from /usr/local/etc/mha.cnf..
Sun Jul 17 21:26:00 2016 - [info] Reading server configuration from /usr/local/etc/mha.cnf..
Sun Jul 17 21:26:00 2016 - [info] MHA::MasterMonitor version 0.57.
Sun Jul 17 21:26:01 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln188] There is no alive server. We can't do failover
Sun Jul 17 21:26:01 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/share/perl5/MHA/MasterMonitor.pm line 329.
Sun Jul 17 21:26:01 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Sun Jul 17 21:26:01 2016 - [info] Got exit code 1 (Not master dead).
 
MySQL Replication Health is NOT OK!

여기서 버그가 있다. /usr/local/share/perl5/MHA/DBHelper.pm 파일 198 라인에 $host 변수에 문제가 있다.

$host 변수를 다음과 같이 고친다.
diff
1
2
3
4
- 155     $self->{dsn} = "DBI:mysql:;host=[$host];port=$port;mysql_connect_timeout=4";
+ 155     $self->{dsn} = "DBI:mysql:;host=$host;port=$port;mysql_connect_timeout=4";
- 198     $self->{dsn} = "DBI:mysql:;host=[$host];port=$port;mysql_connect_timeout=4";
+ 198     $self->{dsn} = "DBI:mysql:;host=$host;port=$port;mysql_connect_timeout=4";

위와같이 수정한 후에 다시 한번 해보면 오류를 해결할 수 있다.

이러한 문제는 호스트 이름에 ‘[‘, ‘]’ 를 붙임으로 나타는 현상이다. 이러한 문제는 다음의 파일에도 있다.

1
2
가 있는 오류"]HealthCheck.pm:98:    "DBI:mysql:;host=[$self->{ip}];"
NodeUtil.pm:85:  my $ssh_user_host = $ssh_user . '@[' . $ssh_host . ']';

Manager 실행 시키기.

반드시 위에 버그를 제거한 후에 다음과 같이 실행을 한다.

mha manager 실행
ZSH
1
2
3
4
# masterha_manager --conf=/usr/local/etc/mha.cnf
Mon Jul 18 01:30:43 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 18 01:30:43 2016 - [info] Reading application default configuration from /usr/local/etc/mha.cnf..
Mon Jul 18 01:30:43 2016 - [info] Reading server configuration from /usr/local/etc/mha.cnf..

위와같이 실행을 한 후 로그를 보면 다음과 같이 나와야 정상이다.

mha manager 로그.
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Mon Jul 18 01:30:49 2016 - [info]
192.168.96.32(192.168.96.32:3306) (current master)
+--192.168.96.30(192.168.96.30:3306)
 
Mon Jul 18 01:30:49 2016 - [warning] master_ip_failover_script is not defined.
Mon Jul 18 01:30:49 2016 - [warning] shutdown_script is not defined.
Mon Jul 18 01:30:49 2016 - [debug]  Disconnected from 192.168.96.30(192.168.96.30:3306)
Mon Jul 18 01:30:49 2016 - [debug]  Disconnected from 192.168.96.32(192.168.96.32:3306)
Mon Jul 18 01:30:49 2016 - [debug] SSH check command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/log/mysql --output_file=/var/log/masterha/save_binary_logs_test --manager_version=0.57 --binlog_prefix=mysql-bin --debug
Mon Jul 18 01:30:49 2016 - [info] Set master ping interval 3 seconds.
Mon Jul 18 01:30:49 2016 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Mon Jul 18 01:30:49 2016 - [info] Starting ping health check on 192.168.96.32(192.168.96.32:3306)..
Mon Jul 18 01:30:49 2016 - [debug] Connected on master.
Mon Jul 18 01:30:49 2016 - [debug] Set short wait_timeout on master: 6 seconds
Mon Jul 18 01:30:49 2016 - [debug] Trying to get advisory lock..
Mon Jul 18 01:30:49 2016 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

내용을 보면 192.168.96.32 서버가 현재 Master 고 30번 서버가 Slave 로 인식하고 있다.

FailOver 테스트.

간단한 FailOver 테스트를 해보자. 시나리오는 간단하다. 현재 Master 서버를 정지 시켜 보는 것이다. 그러면 다음과 같이 Manager 로그가 올라온다.

FailOver 로그.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Mon Jul 18 01:27:19 2016 - [debug]  Disconnected from 192.168.96.32(192.168.96.32:3306)
Mon Jul 18 01:27:19 2016 - [info]
 
----- Failover Report -----
 
mha: MySQL Master failover 192.168.96.30(192.168.96.30:3306) to 192.168.96.32(192.168.96.32:3306) succeeded
 
Master 192.168.96.30(192.168.96.30:3306) is down!
 
Check MHA Manager logs at localhost.localdomain:/var/log/masterha/mha.log for details.
 
Started automated(non-interactive) failover.
The latest slave 192.168.96.32(192.168.96.32:3306) has all relay logs for recovery.
Selected 192.168.96.32(192.168.96.32:3306) as a new master.
192.168.96.32(192.168.96.32:3306): OK: Applying all logs succeeded.
Generating relay diff files from the latest slave succeeded.
192.168.96.32(192.168.96.32:3306): Resetting slave info succeeded.
Master failover to 192.168.96.32(192.168.96.32:3306) completed successfully.

위와같이 로그가 온다면 FailOver 가 정상적으로 된 것이다. 또, 로그 디렉토리 /var/log/masterha 디렉토리에 mha.failover.complete 파일이 생성되어 있을 것이다. 또한, manager 가 죽어 있다. 그러니까 FailOver 가 발생되면 다음과 같이 실행이 된다.

  1. Slave 를 Master 로 승격시킨다.
  2. Manager 에 지정한 로그 디렉토리에 mha.failover.complete 파일을 생성한다.
  3. Manager 가 죽는다.

FailOver 가 발생할때마다 Manager 는 정해진 동작을 수행하고 자동으로 죽게 된다.

승격된 Master 서버에서는 다음을 체크해봐야 한다.

  1. show slave status 명령을 입력했을때 아무것도 나오지 말아야 한다.
  2. show variables like ‘%read%’ 명령어를 입력했을때에 read_only 값이 OFF 여야 한다.

위 두가지가 정상적으로 나온다면 M-HA 가 정상적으로 동작한 것이다.

MongoDB 3.x 인증 알고리즘 변경

MongoDB 3.x 부터는 인증 알고리즘이 MONGODB-CR 에서 SCRAM-SHA-1 으로 변경되었다. 그런데 특정 사용자에 대해서 MONGODB-CR 방식을 적용해야할 필요가 생기기도 한다. 그래서 사용자마다 알고리즘을 달리 적용할 수 있을까?

결론부터 말하면 MongoDB 3.x 는 이를 지원하지만 지원하는 것 같지 않은 상태다. 공식적인 지원을 하지만 이를 구현하기 위해서는 서버를 중단해야하고 인증을 해제해야한 다음에 사용자 알고리즘을 고치고 다시 인증을 걸고 서버를 재시작해야 한다.

또, Replica Set 상태에서 Primary 에서 MONGODB-CR 인증을 설정한다고 하더라도 Secondary  에서는 SCRAM-SHA-1 으로 싱크가 된다. 그래서 프로그램상에서 Replica Set 에서 접속할때에 MONGODB-CR 인증방식으로 접속을 시도하면 Primary 에서는 정상으로 접속되나 Secondary 서버에서는 인증이 실패해 접속이 안된다.

이러한 한계에도 불구하고 MongoDB 3.x 인증 알고리즘 변경 은 다음과 같이하면 된다.

서버 셧다운

제일 번저 서버를 셧다운 해야한다. 이유는 인증모드를 해제하기 위해서다. 관리자로 로그인을 한 후에 다음과 같이 서버를 셧다운 한다.

mongodb 서버 셧다운
1
2
3
4
5
6
7
8
9
10
> db.shutdownServer({force:true})
server should be down...
2016-06-11T01:35:35.931+0900 I NETWORK  [thread1] trying reconnect to 127.0.0.1:27017 (127.0.0.1) failed
2016-06-11T01:35:35.932+0900 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27017, reason: errno:111 Connection refused
2016-06-11T01:35:35.932+0900 I NETWORK  [thread1] reconnect 127.0.0.1:27017 (127.0.0.1) failed failed
2016-06-11T01:35:35.934+0900 I NETWORK  [thread1] trying reconnect to 127.0.0.1:27017 (127.0.0.1) failed
2016-06-11T01:35:35.934+0900 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27017, reason: errno:111 Connection refused
2016-06-11T01:35:35.934+0900 I NETWORK  [thread1] reconnect 127.0.0.1:27017 (127.0.0.1) failed failed
> exit
bye

 

무인증 설정

이제 MongoDB 설정 파일에서 인증 부분을 주석처리해 무인증으로 만들고 서버를 시작한다.

mongod.conf 무인증 설정
YAML
1
2
3
4
security:
  #keyFile: "/home/instance1/mongodb/etc/.keyfile/mongodb_keyfile"
  #clusterAuthMode: "keyFile"
  authorization: "disabled"

이렇게 한 후에 서버를 시작하면 인증없이 모든 것을 할 수 있게 된다.

 

MONGODB-CR 인증 계정 생성

MONGODB-CR  인증을 생성하기 위해서는  MongoDB  자체의 인증 방법을 먼저 변경을 해줘야 한다. 이 변경은 역시 무인증 서버상태에서만 가능하다.

MongoDB 인증 모드 변경
1
2
3
4
5
> use admin
switched to db admin
> db.system.version.save({ "_id" : "authSchema", "currentVersion" : 3 });
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>

authSchema 버전을 3으로 변경하는데 이게 MONGODB-CR 인증 방법을 말한다. 5번은 SCRAM-SHA-1 이다.

이제 생성하고자하는 계정의 데이터베이스로 가서 계정을 생성한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
> use test
switched to db test
> db.createUser({
... user: "test1",
... pwd: "154321",
... roles: [ { role: "readWrite", db: "test" } ]
... });
Successfully added user: {
"user" : "test1",
"roles" : [
{
"role" : "readWrite",
"db" : "test"
}
]
}

생성된 계정을 확인해보면 다음과 같다.

계정 확인
1
2
3
> db.system.users.find()
{ "_id" : "test.test1", "user" : "test1", "db" : "test", "credentials" : { "MONGODB-CR" : "d742f64096c71b3a2aef03165f428f76" }, "roles" : [ { "role" : "readWrite", "db" : "test" } ] }
>

 

인증방법 복원

MONGODB-CR 을 위한 계정을 생성했다면 SCRAM-SHA-1 로 바꾸고 서버에 인증을 걸어야 한다.

SCRAM-SHA-1 인증으로 변경
1
2
3
> db.system.version.save({ "_id" : "authSchema", "currentVersion" : 5 });
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>

그리고 설정파일에 인증을 걸어준다.

Mongo 설정파일 변경
YAML
1
2
3
4
security:
  keyFile: "/home/instance1/mongodb/etc/.keyfile/mongodb_keyfile"
  clusterAuthMode: "keyFile"
  authorization: "enabled"

그리고 서버를 재시작해주면 된다.

문제점

문제점은 앞에서 이야기 했듯이 Replica Set 상태에서 Primary 에서 MONGODB-CR 인증으로 계정을 생성했다고 하더라도 Secodary 에서는 SCRAM-SHA-1 으로 싱크가 된다. 그래서 Secondary 서버에 MONGODB-CR로 인증할려면 오류를 낸다.

 

MongoDB Replica Set 구성하기

mongodb logoMongoDB 는 Replication 을 지원한다. 이는 서비스의 지속성과 안전성을 제공하는 데이터베이스 시스템의 설비다. MongoDB 는 단순하게 데이터 복제를 위한 것뿐만 아니라 Master 가 장애시에 이를 Slave 를 Master 로 자동승격시켜준다. 수 많은 Slave 중에 어떤 것을 Master 로 승격할지를 투표를 통해서 결정한다. MongoDB 는 투표에 참여하기만 하는 것으로 Arbiter 를 세팅할 수도 있다.

이글은 다음의 아키텍쳐를 기반으로 한다.

replica set primary with secondary and arbiter
replica set primary with secondary and arbiter

Replica Set 설정

replication 설정을 위해서 서버 두대에 etc/mongod.conf 파일을 다음과 같이 설정을 해준다.

Replica Set 설정
YAML
1
2
3
replication:
  oplogSizeMB: 5120
  replSetName: "rs1"

oplogSizeMB 는 Replication 을 위해사용되는 로그파일의 용량을 지정하는 것이다. 메뉴얼에 따르면 파일시스템의 활용가능한 5% 를 지정하라고 되어 있지만 고용량 스토리지 시대에는 너무 큰 것 같다.

위와같이 하고 Replica Set 을 설정해주면 된다. 보통 Replica Set 은 적어도 3대로 구성하는데 Primary, Secondary, Aribter 가 바로 그것이다.

한가지 주의해야할 사항은 Replica Set 구성을 미리해놓은다고 서버가 한대인데도 mongod.conf 에 위 설정을 하고 서버에서 명령어를 치면 다음과 같은 오류를 만나게 된다.

Replica Set 구성이 없는 상태이기 때문에 나오는 오류
1
2
3
4
5
6
7
> show dbs;
2016-06-04T22:32:51.328+0900 E QUERY    [thread1] Error: listDatabases failed:{ "ok" : 0, "errmsg" : "not master and slaveOk=false", "code" : 13435 } :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
Mongo.prototype.getDBs@src/mongo/shell/mongo.js:62:1
shellHelper.show@src/mongo/shell/utils.js:760:19
shellHelper@src/mongo/shell/utils.js:650:15
@(shellhelp2):1:1

위 설정은 Master/Slave 두 서버에 모두 해줘야 한다. 그리고 두 서버를 모두 재시작 해준다.

Replication 설정

어떤 블로그에는 Replication 설정을 위해서 다음의 명령어를 치라고 나온다.

1
> rs.initiate()

적어도 MongoDB 3.2 버전에서는 절대로 위 명령어를 먼저 쳐서는 안된다. 내 경우에 멋모르고 위 명령어를 먼저 쳤다가 MongoDB 가 Primary 로 지정되지 않아 아무것도 할 수 없었다. 이럴 경우에 강제로 Primary 임을 지정해줘야 하는데 절차는 다음과 같다.

1
2
3
> cfg = rs.conf()
> cfg.members = [cfg.members[0]] <- Primary로 지정할 멤버하나만 넣는다.
> rs.reconfig(cfg, {force : true})

force 옵션을 주어서 강제로 설정을 지정하게 해줘야한다.

아무튼, 맨 처음에 아무런 생각없이 rs.initiate() 을 해서는 안된다. 특히나 Primary 임을 지정해야하는 서버의 경우에는 이렇게 해서는 안된다. 반드시 멤버지정을 수동으로 해줘야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
> rsconf = {_id:"rs1", members:[{_id:0, host:"192.168.96.6:27017"}]}
{
"_id" : "rs1",
"members" : [
{
"_id" : 0,
"host" : "192.168.96.6:27017"
}
]
}
> rs.initiate(rsconf)
{ "ok" : 1 }

이렇게 하면 이 서버는 이제 Primary 로 지정된다. 상태를 살펴보면 다음과 같다.

Primary MongoDB 의 상태확인
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
rs1:PRIMARY> rs.status()
{
"set" : "rs1",
"date" : ISODate("2016-06-05T07:17:39.321Z"),
"myState" : 1,
"term" : NumberLong(1),
"heartbeatIntervalMillis" : NumberLong(2000),
"members" : [
{
"_id" : 0,
"name" : "192.168.96.6:27017",
"health" : 1,
"state" : 1,
"stateStr" : "PRIMARY",
"uptime" : 785,
"optime" : {
"ts" : Timestamp(1465110577, 1),
"t" : NumberLong(1)
},
"optimeDate" : ISODate("2016-06-05T07:09:37Z"),
"electionTime" : Timestamp(1465110576, 2),
"electionDate" : ISODate("2016-06-05T07:09:36Z"),
"configVersion" : 1,
"self" : true
}
],
"ok" : 1
}

“stateStr” 이 PRIMARY 라고 나와야 정상이다.

이제 슬레이브를 붙이면 되는데, MongoDB 는 이를 아주 쉽게 명령어로 할 수 있다. Slave 서버를 구동하고 다음과 같이 Primary 서버에서 명령어를 입력해주면 된다.

Slave MongoDB 추가
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
rs1:PRIMARY> rs.add("192.168.96.30:27017")
{ "ok" : 1 }
rs1:PRIMARY> rs.status()
{
"set" : "rs1",
"date" : ISODate("2016-06-05T07:19:56.103Z"),
"myState" : 1,
"term" : NumberLong(1),
"heartbeatIntervalMillis" : NumberLong(2000),
"members" : [
{
"_id" : 0,
"name" : "192.168.96.6:27017",
"health" : 1,
"state" : 1,
"stateStr" : "PRIMARY",
"uptime" : 922,
"optime" : {
"ts" : Timestamp(1465111191, 1),
"t" : NumberLong(1)
},
"optimeDate" : ISODate("2016-06-05T07:19:51Z"),
"electionTime" : Timestamp(1465110576, 2),
"electionDate" : ISODate("2016-06-05T07:09:36Z"),
"configVersion" : 2,
"self" : true
},
{
"_id" : 1,
"name" : "192.168.96.30:27017",
"health" : 1,
"state" : 5,
"stateStr" : "STARTUP2",
"uptime" : 4,
"optime" : {
"ts" : Timestamp(0, 0),
"t" : NumberLong(-1)
},
"optimeDate" : ISODate("1970-01-01T00:00:00Z"),
"lastHeartbeat" : ISODate("2016-06-05T07:19:55.545Z"),
"lastHeartbeatRecv" : ISODate("2016-06-05T07:19:51.894Z"),
"pingMs" : NumberLong(1),
"syncingTo" : "192.168.96.6:27017",
"configVersion" : 2
}
],
"ok" : 1
}

Slave 를 붙이자마자 상태를 확인해보면 stateStr 이 STARTUP2 로 나온다. 하지만 시간이 지나서 다시 확인해보면 SECONDARY 가 되어 있는 것을 확인할 수 있다.

PRIMARY 와 SECONDARY 가 동기화가 잘되고 있는지는 optime 의 수치가 모두 동일한지를 보고 판단할 수 있다.

Aribter 추가

Aribter 는 PRIMARY 가 장애가 발생했을시에 이를 감지하고 SECONDARY 중에 하나를 PRIMARY 로 선택할때 투표에 참여하는 역화을 한다. 즉, 오직 투표만을 위한 시스템이라고 보면 된다. 따라서 데이터 복제, 데이터 저장이 없기 때문에 저장소를 위한 설정은 다 필요가 없다. 따라서 mongod.conf 설정은 다음과 같다.

Aribter 의 mongod.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
# mongod.conf
 
# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/
 
# where to write logging data.
systemLog:
  verbosity: 0
  destination: file
  logAppend: true
  logRotate: rename
  timeStampFormat: ctime
  path: "/home/instance2/mongodb/log/mongod.log"
  component:
    replication:
      verbosity: 2
 
# Where and how to store data.
storage:
  dbPath: "/home/instance2/mongodb/data"
  directoryPerDB: true
  journal:
    enabled: false
#  engine: "wiredTiger"
#  mmapv1:
#  wiredTiger:
#    engineConfig:
#      cacheSizeGB: 1
 
# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: "/home/instance2/mongodb/run/mongod.pid"  # location of pidfile
 
# network interfaces
net:
  port: 27017
  bindIp: 0.0.0.0  # Listen to local interface only, comment to listen on all interfaces.
  maxIncomingConnections: 1024
  http:
    enabled: false
 
security:
  keyFile: "/home/instance2/mongodb/etc/.keyfile/mongodb_keyfile"
  clusterAuthMode: "keyFile"
  authorization: "enabled"
 
operationProfiling:
  slowOpThresholdMs: 100
  mode: off
 
replication:
  oplogSizeMB: 5120
  replSetName: "rs1"
 
#sharding:
 
## Enterprise-Only Options
 
#auditLog:
 
#snmp:

MongoDB 3.2 메뉴얼에 따르면 Aribter 로 동작하는 Node 의 경우에 storage.journal.enabled 를 false 로 설정하도록 권장하고 있다. 그리고 Engine 관련 설정은 모두 주석처리를 한다.

이렇게 설정한 후에 Aribter 서버를 시작시킨다. 그리고 Master 서버에서 다음과 같이 Aribter 를 추가해 준다.

1
rs1:PRIMARY> rs.addArb("192.168.96.31:27017")

위와같이 하면 Arbiter 가 Replica Set 에 추가 된다. 그리고 Master 가 장애가 발생하면 투표에 참여해 SECONDARY 서버중에 하나를 Master 로 지목하게 된다.

MongoDB 설치 및 환경설정

mongodb logo

이 글은 MongoDB 설치 및 환경설정에 관한 글이다. 환경설정은 슈퍼유저의 인증을 설정하는 것까지 한다.

Download and Extract

Mongodb 는 Binary 배포를 하고 있다. 홈페이지에 접속해서 설치할 컴퓨터의 운영체제에 맞는 압축 파일을 다운로드 하면 된다. Downlaod URL 도 제공하기 때문에 서버에서 wget, curl 을 이용해서 바로 다운로드도 할 수 있다.

Binary 파일이기 때문에 압축해제 하기만하면 설치가 완료 된다. 압축을 해제하고 나면 다음과 같은 파일들이 나온다.

ls -lh mongodb
1
2
3
4
5
6
7
mongodb]$ ls -lh
합계 104K
-rw-r--r-- 1 instance1 tomcat  34K  4월 28 05:32 GNU-AGPL-3.0
-rw-r--r-- 1 instance1 tomcat  17K  4월 28 05:32 MPL-2
-rw-r--r-- 1 instance1 tomcat 1.4K  4월 28 05:32 README
-rw-r--r-- 1 instance1 tomcat  36K  4월 28 05:32 THIRD-PARTY-NOTICES
drwxr-xr-x 2 instance1 tomcat 4.0K  6월  4 19:46 bin

보면알겠지만 달랑 실행을 위한 디렉토리와 파일만 존재한다. 이제 하나하나 설정을 해보자.

디렉토리 생성

먼저, 디렉토리를 생성해야 한다. 설정파일을 담을 디렉토리, 로그 파일을 담을 디렉토리, 데이터를 저장할 디렉토리등이다.

디렉토리 생성
1
2
3
4
5
6
7
8
9
10
11
12
]$ mkdir etc log data run
]$ ls -lh
합계 104K
-rw-r--r-- 1 instance1 tomcat  34K  4월 28 05:32 GNU-AGPL-3.0
-rw-r--r-- 1 instance1 tomcat  17K  4월 28 05:32 MPL-2
-rw-r--r-- 1 instance1 tomcat 1.4K  4월 28 05:32 README
-rw-r--r-- 1 instance1 tomcat  36K  4월 28 05:32 THIRD-PARTY-NOTICES
drwxr-xr-x 2 instance1 tomcat 4.0K  6월  4 19:46 bin
drwxr-xr-x 4 instance1 tomcat 4.0K  6월  4 21:05 data
drwxr-xr-x 3 instance1 tomcat   39  6월  4 21:03 etc
drwxr-xr-x 2 instance1 tomcat   23  6월  4 21:03 log
drwxr-xr-x 2 instance1 tomcat   23  6월  4 21:03 run

디렉토리를 생성하고 나면 위와같은 상태가 된다.

mongod.conf 파일 생성

mongodb 를 운영하기 위해서는 설정파일이 필요하다. 이를 위해서 설정파일을 생성해야 한다. 이는 다음의 주소에서 샘플을 구할 수 있다.

기본적인 내용만 되어 있다. 하지만 mongodb 는 Replica set 를 염두해야하기 때문에 이를 위한 설정을 포함하면 다음과 같다.

mongod.conf
YAML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
# mongod.conf
 
# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/
 
# where to write logging data.
systemLog:
  verbosity: 0
  destination: file
  logAppend: true
  logRotate: rename
  timeStampFormat: ctime
  path: /home/instance1/mongodb/log/mongod.log
  component:
    replication:
      verbosity: 2
 
# Where and how to store data.
storage:
  dbPath: /home/instance1/mongodb/data
  journal:
    enabled: true
  engine: wiredTiger
#  mmapv1:
  wiredTiger:
    engineConfig:
      cacheSizeGB: 1
 
# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /home/instance1/mongodb/run/mongod.pid  # location of pidfile
 
# network interfaces
net:
  port: 27017
  bindIp: 0.0.0.0  # Listen to local interface only, comment to listen on all interfaces.
  maxIncomingConnections: 1024
  http:
    enabled: false
 
#security:
 
operationProfiling:
  slowOpThresholdMs: 100
  mode: off
 
#replication:
 
#sharding:
 
## Enterprise-Only Options
 
#auditLog:
 
#snmp:

위 설정 파일을 실제 서버에서 운영하기에는 문제가 있다. 문제가 될만한 설정들은 다음과 같다.

  • cacheSizeGB – 엔진의 캐쉬 크기이다. 메뉴얼에 따르면 최소 1GB 이상이여야 한다.
  • oplogSizeMB – 리플리케이션을 위한 로그 파일 크기다. 메뉴얼에 따르면 활용가능한 디스크 공간의 5% 다.

실제 서버에서 운영한다면 위 두개의 파라메터를 반드시 운영환경에 맞게 설정을 해야한다. 그리고 운영환경을 위해서 슈퍼유저에 대한 인증을 반드시 설정해야 한다.

슈퍼유저 인증 설정

MongoDB 는 설치를 완료하고 나면 아무런 보안 설정이 없다. 누구나, 아무나 슈퍼유저가 될수 있다. 이를 제안하기 위해서 슈퍼유저에 인증을 설정해야 한다. 이는 서버를 구동하고 나서 해야 한다.

mongodb 서버 구동
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
]$ ./bin/mongod -f etc/mongod.conf
about to fork child process, waiting until server is ready for connections.
forked process: 16080
child process started successfully, parent exiting
]$ ./bin/mongo
MongoDB shell version: 3.2.6
connecting to: test
Server has startup warnings:
Sat Jun  4 22:24:48.990 I CONTROL  [initandlisten]
Sat Jun  4 22:24:48.990 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
Sat Jun  4 22:24:48.990 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
Sat Jun  4 22:24:48.990 I CONTROL  [initandlisten]
Sat Jun  4 22:24:48.990 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
Sat Jun  4 22:24:48.990 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
Sat Jun  4 22:24:48.990 I CONTROL  [initandlisten]
> use admin
switched to db admin
> db.createUser(
...   {
...     user: "admin",
...     pwd: "154321",
...     roles: [ { role: "root", db: "admin" } ]
...   }
... );
Successfully added user: {
"user" : "admin",
"roles" : [
{
"role" : "root",
"db" : "admin"
}
]
}
> db.shutdownServer()
server should be down...
2016-06-04T22:28:11.241+0900 I NETWORK  [thread1] trying reconnect to 127.0.0.1:27017 (127.0.0.1) failed
2016-06-04T22:28:11.241+0900 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27017, reason: errno:111 Connection refused
2016-06-04T22:28:11.241+0900 I NETWORK  [thread1] reconnect 127.0.0.1:27017 (127.0.0.1) failed failed

mongod.conf 의 인증과 Replica set 을 해제한 상태에서 해야지만 가능한다. 만일 최초 설치 후에 securiry, replication 부분이 설정이 되어 있다면 위 명령어는 듣지 않는다.

keyFile 생성

keyFile 은 sharded cluster 나 replica set 을 위해서는 keyFile 을 필요로한다. 각 노드간 자신들의 멤머인지 아닌지를 알기 위한 수단이 된다. 이 파일은 각 노드마다 모두 동일해야 한다.

keyFile 생성
ZSH
1
2
3
4
]$ mdir etc/.keyfile
]$ cd etc/.keyfile
]$ openssl rand -base64 741 > mongodb_keyfile
]$ chmod 600 mongodb_keyfile

shared cluster, replica set 을 구성한다면 구성을 위한 서버에 이 파일을 각각 배포를 해야 한다.

그리고 다음과 같이 mongod.conf 파일에 keyFile 을 인식시켜준다.

etc/mongod.conf
YAML
1
2
3
4
security:
  keyFile: "/home/instance1/mongodb/etc/.keyfile/mongodb_keyfile"
  clusterAuthMode: "keyFile"
  authorization: "enable"

위와 같이 한 후에 서버를 다시 시작한다. (앞에서 서버를 shutdown 시켰놨었다.)

mongod 서버 시작.
1
2
3
4
5
6
7
8
9
10
11
12
]$ ./bin/mongod -f etc/mongod.conf
$ ./bin/mongo
MongoDB shell version: 3.2.6
connecting to: test
> use admin
switched to db admin
> db.auth("admin","154321")
1
> show dbs
admin  0.000GB
local  0.000GB
>

위와같이 서버로 접속하고 정상적으로 인증이 될 경우에 ‘1’ 을 리턴한다. 그리고 명령어를 쳐보면 실행이 되는 것을 알 수 있다.

PostgreSQL Replication – Log Shipping

PostgreSQL 리플리케이션은 장애를 대비해 가용성을 높이는 최소한의 방법입니다. 현재 버전의 PostgreSQL 다양한 리플리케이션을 지원하는데, 이 문서는 가장 오래되고 기초적인 리플리케이션인 Log Shipping Replication 에 대해서 다룹니다.

File based replcation in PostgreSQL

이 리플리케이션은 Warm Standby 이라고 합니다. 이에 대한 설명은 PostgreSQL 문서에 다음과 같이 잘 나와 있습니다.

운영 서버에서 만드는 트랜잭션 로그 조각을 정기적으로 대기 서버로 옮기고, 그것을 적용시켜, 운영 서버가 장애로 멈추게 되면, 대기 서버를 운영해서, 가용성을 향상할 수 있다. 이 기능을 warm standby, 또는 log shipping 기능이라고 한다.

이 복제 방식은 먼저, 운영 서버와 대기 서버가 모두 실행 중이어야한다. 하지만, 두 서버 쌍방간의 연결 상태는 다른 복제 방식보다 나빠도 괜찮다. 운영 서버는 아카이브 모드로 운영 되어야하며, 운영 중에 생기는 다 쓴 WAL 세그먼트 파일(스위칭된 트랜잭션 로그 파일)을 차례대로 대기 서버로 보내고, 대기 서버는 복구 모드 전용(복구가 끝나도 다음 복구 파일이 있으면 계속 복구 작업을 하는 상태)으로 실행된다. 이 방식을 이용하면, 데이터베이스 테이블들을 수정해야할 필요가 없다. 또한 다른 복제 방식에 비해 관리 작업 비용도 적으며, 복제 작업이 운영 서버 쪽으로 끼치는 영향도 다른 복제 방식보다 적다.

이 방식의 구현 방법은 간단하다. 운영 서버에서 다 쓴 WAL 파일을 다른 서버로 운송(shipping) 하는 것 뿐이다. PostgreSQL에서는 그 로그 옮기는 작업은 한 번에 하나의 로그 파일을 옮길 수 있도록 구현되어 있다. WAL 파일(16MB)을 옮기는 작업은 데이터베이스 서버 밖에서 관리자가 정의한 방식으로 진행 되기 때문에, 같은 사이트 내로 옮겨도 되고, 전혀 다른 시스템 쪽으로 보내도 되고, 여러 시스템으로 한꺼번에 보내도 된다. 이 부분은 전적으로 관리자에게 맡긴다. 단지 고려해야할 사항은 파일이 전송될 때의 전송량 때문에 운영 서버에 영향을 줄 수도 있다. 이 부분이 염려되면, 전송 속도를 제한 할 수 있는 방법도 고려해야할 것이다. 아니면, 레코드 기반 로그 전달 방식 (스트리밍 복제)을 고려할 수 도 있다. (25.2.5절 참조)

로그 전달 방식은 비동기식임을 기억해야 한다. 다시 말하면, 전송하는 WAL 내용은 이미 커밋된 자료이기 때문에, 그 자료가 대기 서버로 미쳐 전달 되기전에 운영 서버가 멈춰버리면, 그 자료는 손실 된다. 자료 손실량을 줄이는 방법으로 archive_timeout 환경설정값을 몇 초 정도로 짧게 지정해 자주 사용하는 WAL 파일을 바꾸고, 그것을 전송하면, 되겠지만, 그 파일의 크기가 16MB이기 때문에, 잦은 WAL 파일 전송 작업으로 네트워크 사용량이 증가할 것이다. 스트리밍 복제 방식(25.2.5절 참조)을 이용하면, 이 손실 되는 자료량을 최소화 할 수 있다.

(물론 위에서 언급한 한계점이 있기는 하지만,) 대기 서버로 넘어 오는 WAL 파일이 제때에 잘 넘어 온다면, 운영 서버가 중지 되어 대기 서버가 그 역할을 맡기까지 서비스가 중지되는 시각은 극히 짧다. 이렇게 가용성을 향상 시킨다. 베이스 백업 자료를 준비하고, 지금까지 보관해둔 WAL 파일을 가지고, 서버를 복구 하는 방법은 이 방식보다 꽤 많은 서비스 중지 시간이 필요할 것이다. 서버가 복구 되는 기술적인 방식은 동일하지만, 가용성 입장에서는 차이가 난다. warm standby 방식으로 구현되면, 대기 서버 쪽에서는 어떤 쿼리도 사용할 수 없다. 대기 서버 쪽에서 읽기 전용 쿼리를 사용하려면, hot standby 방식으로 구축해야한다. 이 부분은 25.5절에서 자세히 설명한다.

 

시작하기에 앞서

WAL 파일

대부분의 데이터베이스 시스템은 데이터베이스에 변화와 관련된 모든 작업에 대해서 파일로 기록을 해 둡니다. 정확하게는 데이터를 조작하기에 앞서 어떤 SQL 를 명령했는지에 대해서 로깅을 한 후에 실제 데이터를 조작 합니다. 이는 갑자기 장애가 발생했을시에 이 로그 파일을 이용해 데이터를 복구할 수 있게 됩니다. 이를 WAL (Write Ahead Log) 파일이라고 합니다.

PostgreSQL 도 같은 방법으로 동작합니다. PostgreSQL 에서 WAL 파일은 pg_xlog 디렉토리에 생성되며 특징은 16MB 크기단위로 파일이 쪼개져 저장됩니다.

PostgreSQL WAL files
ZSH
1
2
3
4
-rw-------. 1 postgres postgres 16M  2월 20 16:49 00000001000000000000002F
-rw-------. 1 postgres postgres 16M  2월 20 15:15 000000010000000000000040
-rw-------. 1 postgres postgres 16M  2월 20 15:14 00000001000000000000003F
-rw-------. 1 postgres postgres 16M  2월 20 15:14 00000001000000000000003E

 

CheckPoint

모든 데이터베이스 시스템은 메모리 기반으로 동작합니다. 메모리에 자주 쓰이는 데이터를 캐쉬해기도 하고 트랜잭션시에도 메모리에 저장됩니다. 특정 시점이 되면 PostgreSQL 은 메모리에 있는 것을 디스크에 쓰기를 함으로써 실제 데이터가 영구적으로 저장이 됩니다.

그런데, 이러한 작업을 수동으로 해야할 때가 있습니다. 대표적으로 PITR(Point In Time Recovery) 백업을 하기 위해서는 모든 메모리에 내용을 강제로 디스크에 쓰여져야 합니다. 이때 PostgreSQL 가 하는 작업이 바로 CheckPoint 입니다.

다시말해, CheckPoint 는 메모리에 있는 내용을 디스크에 강제로 쓰기하는 작업을 말합니다.

 

리플리케이션 설정하기

설정하는 순서는 다음과 같습니다.

  1. Master 서버가 standby 서버에 postgres 계정으로 ssh 패스워드 인증없이 바로 접속할 수 있도록 설정을 합니다.
  2. Master 서버의 postgresql.conf 파일을 수정하고 서버를 재시작 합니다.
  3. Master 서버에서 PITR 백업을 합니다.
  4. 백업본을 standby 서버로 전송합니다.
  5. standby 서버에서 전송받은 백업 압축파일을 해제 합니다.
  6. standby recovery.conf 파일을 작성하고 pg_xlog 디렉토리를 생성하고 소유권을 postgres 로 바꿉니다.
  7. standby 서버를 기동 합니다.

SSH no password 접속 설정.

이는 RSA 키를 교환함으로써 가능 합니다. PostgreSQL 은 postgres 계정으로 동작하기 때문에 postgres 계정에서 Standby postgres 계정으로 RSA 키 접속을 설정해 줍니다.

먼저, Master  서버의 postgres 계정에서 다음과 같이 키를 작성해 줍니다.

SSH RSA 키 생성
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-bash-4.2$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/opt/pgsql-9.5.0/.ssh/id_rsa): [그냥 엔터]
Created directory '/opt/pgsql-9.5.0/.ssh'.
Enter passphrase (empty for no passphrase): [그냥 엔터]
Enter same passphrase again: [그냥 엔터]
Your identification has been saved in /opt/pgsql-9.5.0/.ssh/id_rsa.
Your public key has been saved in /opt/pgsql-9.5.0/.ssh/id_rsa.pub.
The key fingerprint is:
61:e4:45:21:ed:fe:56:16:22:57:4b:ff:7f:84:28:00 postgres@localhost.localdomain
The key's randomart image is:
+--[ RSA 2048]----+
|        oo+.     |
|      Eo o.   o  |
|       .+.   o o |
|       ...o o o .|
|        So o o o.|
|          o . + o|
|           o o ..|
|            o   o|
|           .    .|
+-----------------+

이렇게하면 postgres 계정에 “.ssh/id_rsa, .ssh/id_rsa.pub” 두개의 파일이 생성이 됩니다. id_rsa 는 private key 이고 id_rsa.pub 는 public key 입니다. 다음과 같이  publickey 를 Standby 의 postgres 계정을 복사해 줍니다.

publickey 복사
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
ssh-copy-id -i ./id_rsa.pub postgres@192.168.96.26
The authenticity of host '192.168.96.26 (192.168.96.26)' can't be established.
RSA key fingerprint is ba:6a:82:18:0c:88:2b:e8:6b:cb:cb:ef:53:77:ba:4a.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@192.168.96.26's password: [로그인 패스워드]
 
Number of key(s) added: 1
 
Now try logging into the machine, with:   "ssh 'postgres@192.168.96.26'"
and check to make sure that only the key(s) you wanted were added.

이제 그냥 “ssh ‘postgres@192.168.96.26′” 을 입력하면 패스워드 입력 없이 접속이 가능해 집니다.

Master 서버에 postgresql.conf 파일 설정

다음과 같이 postgresql.conf 파일을 설정해 줍니다.

Master 서버의 postgresql.conf 수정.
INI
1
2
3
4
5
6
wal_level = archive # minimal, archive, or hot_standby
archive_mode = on # allows archiving to be done
# (change requires restart)
archive_command = 'scp -i /opt/pgsql-9.5.0/.ssh/id_rsa %p postgres@192.168.96.26:/opt/pgsql-9.5.0/archive/%f' # command to use to archive a logfile segment
archive_timeout = 30 # force a logfile segment switch after this
# number of seconds; 0 disables

archive_command 에 설정한대로 Standby 서버에 archive 디렉토리를 만들어주고 postgres 소유권으로 바꿔 줍니다.

Slave 에서 archive 디렉토리 설정.
ZSH
1
2
-bash-4.1$ mkdir /opt/pgsql-9.5.0/archive
-bash-4.1$ chown postgres: archive/

서버를 재시작 해줍니다.

Standby 를 위한 데이터 백업하기 

리플리케이션의 시작은 슬레이브 데이터베이스시스템을 위해 데이터베이스를 옮겨주는 것입니다. PostgreSQL 뿐만아니라 MySQL 도 그렇지만, 데이터베이스의 파일들을 압축해서 옮겨줘야 하는데, 이를 위해서는

  1. Master 서버의 작업 중단
  2. 모든 메모리에 있는 내용을 디스크에 쓰기(CheckPoint)

뒤 두가지가 필요합니다. PostgreSQL 은 이 두가지를 명령어를 통해서 한번에 되도록 지원하는데 두가지 방법이 있습니다. 이 문서에서는 PITR 백업 방법을 설명합니다.

먼저, PostgreSQL  서버에 다음과 같이 해줍니다.

PITR 백업 실행.
ZSH
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@localhost ~]# su - postgres
Last login: Sun Feb 21 23:00:28 KST 2016 on pts/0
-bash-4.2$ /opt/pgsql-9.5.0/bin/psql -U postgres -c "select pg_start_backup('/opt/pgsql-9.5.0/20160221.tar')"
pg_start_backup
-----------------
0/30000028
(1개 행)
 
-bash-4.2$ tar -cv --exclude=/opt/pgsql-9.5.0/data/pg_xlog -f /opt/pgsql-9.5.0/20160221.tar /opt/pgsql-9.5.0/data
/opt/pgsql-9.5.0/data/backup_label
-bash-4.2$ /opt/pgsql-9.5.0/bin/psql -U postgres -c "select pg_stop_backup(), current_timestamp"
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup |             now
----------------+------------------------------
0/31000050     | 2016-02-21 23:05:05.56062+09
(1개 행)

이제 백업 완료된 파일을 Slave 서버로 옮겨 줍니다.

백업본을 Standby  서버로 옮겨주기

파일 복사
ZSH
1
2
3
-bash-4.2$ scp -i /opt/pgsql-9.5.0/.ssh/id_rsa 20160221.tar postgres@192.168.96.26:./
20160221.tar                                                                                   100%  679MB  32.3MB/s   00:21
-bash-4.2$

다음으로 Slave 서버에서 전송되어진 압축파일을 다음과 같이 압축해제해 줍니다.

Standby 서버에서 전송받은 백업 압축파일을 해제

압축해제하기.
ZSH
1
2
3
4
[root@localhost .ssh]# su - postgres
-bash-4.1$ tar xvf 20160221.tar -C /
-bash-4.1$ cd data
-bash-4.1$ mkdir pg_xlog

이제 recovery.conf 을 작성해 줍니다.

recovery.conf 파일 작성

다음과 같이 recovery.conf 파일을 작성하기 위해서 다음과 같이 data 디렉토리로 이동해 줍니다.

recovery.conf 파일 작성.
ZSH
1
2
3
[root@localhost .ssh]# su - postgres
-bash-4.1$ cd data
-bash-4.1$ vim recovery.conf

recovery.conf 는 다음과 같이 작성해 줍니다.

recovery.conf 파일 작성.
INI
1
2
3
4
restore_command = 'cp /opt/pgsql-9.5.0/archive/%f %p'
archive_cleanup_command = '/opt/pgsql-9.5.0/bin/pg_archivecleanup /opt/pgsql-9.5.0/archive/ %r'
standby_mode = 'on'
trigger_file = '/tmp/postgresql.trigger.5432'

이렇게 파일을 작성하고 역시나 소유권을 postgres 로 변경해줍니다. 그리고 서버를 시작해줍니다.

체크하기

이 렇게 해놓으면 Master 서버에서는 archive 해야할 로그파일을 Standby 서버로 전송하고 Standby 서버는 이것을 replay 하게 됩니다. 그러니까 Standby 서버는 replay 로만 동작하게 됩니다. 이는 상태를 보면 알 수 있습니다. Standby 서버의 프로세스 상태는 다음과 같습니다.

Standby 서버에서 postgres 프로세스 상태
ZSH
1
2
3
4
postgres  2380  0.0  2.4 442480 24504 ?        S    23:20   0:00 /opt/pgsql-9.5.0/bin/postgres -D /opt/pgsql-9.5.0/data
postgres  2381  0.0  0.8 442516  8192 ?        Ss   23:20   0:00 postgres: startup process   waiting 000000010000000000000038
postgres  2383  0.0  0.9 442480  9920 ?        Ss   23:20   0:00 postgres: checkpointer process
postgres  2385  0.0  0.9 442480  9628 ?        Ss   23:20   0:00 postgres: writer process

38번째 파일을 기다리고 있다고 나옵니다. . ‘/opt/pgsql-9.5.0/archive’ 에는 Master 에서 전송한 파일을 받고 받자마자 바로 replay를 합니다. 그리고 3개가 넘어가면 pg_archivecleanup 프로그램이 recovery.conf 에 명시된대로 삭제됩니다. 이는 ‘/opt/pgsql-9.5.0/archive’ 디렉토리를 모니터링 해보면 알 수 있습니다. 없던 파일이 나타나고 사라지는것을 확인할 수 있습니다.

archive 파일 체크
ZSH
1
2
3
4
5
6
7
8
-bash-4.1$ tail -f logfile
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다
cp: cannot stat `/opt/pgsql-9.5.0/archive/000000010000000000000038': 그런 파일이나 디렉터리가 없습니다

로그파일을 보면 현재 상태를 확인할 수 있습니다.

이 Replication 의 특징는 Standby 를 활용할 수 없습니다. Select 전용으로라도 활용할 수 없습니다. 왜냐하면 replay로 동작중이기 때문에 psql 를 이용해서 접속을 하면 다음과 같은 에러메시지를 만나게 됩니다.

Standby 서버에서 접속 시도
ZSH
1
2
-bash-4.1$ /opt/pgsql-9.5.0/bin/psql -U postgres
psql: FATAL:  the database system is starting up

만일 Master 서버에 여러 Standby 서버가 필요하다면 Master 서버에 postgresql.conf 파일에 ‘archive_command’ 에 스크립트를 넣어줍니다.

다중 Standby 서버
INI
1
2
3
4
5
6
archive_command = 'myarchivescript %p %f'
 
myarchivescript 파일 내용.
scp $1 $STANDBYNODE1:$PGARCHIVE/$2
scp $1 $STANDBYNODE2:$PGARCHIVE/$2
scp $1 $STANDBYNODE3:$PGARCHIVE/$2

단점

이 Log Shipping Replication 은 자세한 Replication 상태를 모니터링 할 수가 없습니다. 파일 전송은 PostgreSQL 이 담당하는게 아닌 리눅스 시스템이 담당합니다. 거기다 Standby 서버에는 접속조차 할 수 없어서 Standby 서버에서 제대로 Recovering 이 되고 있는지를 알 수가 없습니다.

동작 방법이 16MB WAL 파일 작성이 완료되어지면 전송이 되어지는데, 그래서 WAL 파일이 자주 생성된다면 파일을 전송하는데 시스템 자원 소모가 커질 우려도 있습니다.

pg_terminate_backend, pg_cancel_backend

PostgreSQL 에서 쿼리를 취소시키위한 함수로 pg_terminate_backend, pg_cancel_backend 를 제공 합니다. 공식문서에는 다음과 같이 나와 있습니다.

– pg_cancel_backend(pidint) : Cancel a backend’s current query. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.
– pg_terminate_backend(pidint) : Terminate a backend. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.

pg_cancel_backend and pg_terminate_backend send signals (SIGINT or SIGTERM respectively) to backend processes identified by process ID. The process ID of an active backend can be found from the pid column of the pg_stat_activity view, or by listing the postgres processes on the server (using ps on Unix or the Task Manager on Windows). The role of an active backend can be found from the usename column of the pg_stat_activity view.

이 함수를 이용해서 다음과 같이 30초이상 지속된 쿼리들을 차단할 수 있습니다.

30초 이상된 쿼리 차단
PgSQL
1
2
=# select pg_terminate_backend(pid) from (select pid from pg_stat_activity where query_start < now() - '30 seconds'::interval) as t;
=# select pg_cancel_backend(pid) from (select pid from pg_stat_activity where query_start < now() - '30 seconds'::interval) as t;

프로세스에 시그널(Signal)을 보내는거는 똑같은데, 하나는 SIGINT을 다른 하나는 SIGTERM을 받습니다.
SIGINT는 인터럽트(Interrupt) 시그널을 말합니다. 가장 흔하게 볼 수 있는게 어떤 프로그램을 포그라운드(Foreground)로 실행중에 Ctr+C 를 누르는 것이 바로 SIGINT 입니다. 이 시그널을 받은 프로그램은 별도의 행동을 정의하지 않았다면 프로그램을 종료 됩니다.
SIGTERM 은 Kill 시그널로 프로그램 종료 시그널 입니다. 이 시그널을 받은 프로그램은 이 신호를 무시하거나 접속을 차단하고 프로그램 종료를 위한 프로세스를 진행합니다. 한마디로 말해서 안전한 종료 입니다.
우리가 흔히 쓰는 ‘kill -9 pid’ 는 SIGKILL인데, 이것은 시그널을 받은 프로그램의 후속 행동과는 상관없이 즉시 프로그램을 강제 종료시키도록 합니다.

테스트

테스트는 PostgreSQL 의 프로세스에게 SIGTERM, SIGKILL, SIGINT를 보내 이에 어떤 반응을 보이는지를 보는 것이 주 목적입니다. 이를 위해 Linux 쉘 상에서 SIGTERM, SIGKILL을 주는 방법과 PostgreSQL 쉘상에서 위에서 소개했던 두개의 함수를 사용하는 방법을 채택했습니다.

모든 테스트는 화면과 PostgreSQL이 기록하는 로그를 기반으로 평가되었습니다.

테스트 쿼리

테스트를 위해서 실행했던 테스트 쿼리는 다음과 같습니다.

테스트 쿼리
PgSQL
1
2
3
4
testdb=> SELECT pg_sleep(10);
pg_sleep
----------
(1 row)

위와같이 pg_sleep 함수를 사용한 상태에서 pg_stat_activity 를 보면 다음과 같이 나옵니다.

pg_stat_activity 결과
PgSQL
1
2
3
datid | datname |  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | waiting | state  |              query
-------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+---------------------------------
16386 | testdb  | 14014 |    16385 | test    | psql             |             |                 |          -1 | 2015-09-22 13:18:13.553564+09 | 2015-09-22 14:43:58.054511+09 | 2015-09-22 14:43:58.054511+09 | 2015-09-22 14:43:58.054514+09 | f       | active | SELECT pg_sleep(100);

Linux 쉘상에서 SIGTERM

먼저 Linux 쉘상에서 SIGTERM 을 테스트 입니다.

SIGTERM 테스트
Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
saltmaster ~ # ps aux | grep post
postgres  6129  0.0  0.0 145096 15740 ?        S    10:57   0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf
postgres  6130  0.0  0.0 102748  1344 ?        Ss   10:57   0:00 postgres: logger process
root      6510  0.0  0.0  90232  4748 pts/10   S    11:01   0:00 su - postgres
postgres  6517  0.0  0.0  24224  5800 pts/10   S+   11:01   0:00 -su
postgres  6537  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: checkpointer process
postgres  6538  0.0  0.0 145096  4204 ?        Ss   11:01   0:00 postgres: writer process
postgres  6539  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: wal writer process
postgres  6540  0.0  0.0 145916  5800 ?        Ss   11:01   0:00 postgres: autovacuum launcher process
postgres  6541  0.0  0.0 104840  3732 ?        Ss   11:01   0:00 postgres: stats collector process
root     13977  0.0  0.0  90232  4624 pts/0    S    13:18   0:00 su - postgres
postgres 13984  0.0  0.0  24224  5932 pts/0    S    13:18   0:00 -su
postgres 14003  0.0  0.0 101452  7188 pts/0    S+   13:18   0:00 /usr/lib/postgresql/9.2/bin/psql -U test testdb
postgres 14014  0.0  0.0 146444  9552 ?        Ss   13:18   0:00 postgres: test testdb [local] SELECT    <--- 쿼리가 실행중입니다.
]# kill 14014

SIGTERM 을 Linux 쉘에서 보내자 PostgreSQL 쉘에서도 다음과 같은 반응 했습니다.

SIGTERM을 받은 PostgreSQL 의 반응
PgSQL
1
2
3
4
5
6
testdb=> SELECT pg_sleep(100);
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

다음은 PostgreSQL 로그 입니다.

SIGTERM을 받았을때의 PostgreSQL 의 반응
Shell
1
2
3
4
2015-09-22 14:47:45 KST [14014-9] test@testdb FATAL:  terminating connection due to administrator command
2015-09-22 14:47:45 KST [14014-10] test@testdb STATEMENT:  SELECT pg_sleep(200);
2015-09-22 14:47:45 KST [14014-11] test@testdb LOG:  disconnection: session time: 1:29:32.146 user=test database=testdb host=[local]
2015-09-22 14:47:45 KST [19073-1] [unknown]@[unknown] LOG:  connection received: host=[local]

PostgreSQL은 쿼리를 취소한게 아니라 Connection 을 차단하고 있음을 보여주고 있습니다.

Linux 쉘상에서 SIGKILL

이번에는 Linux 쉘상에서 SIGKILL에 대해 테스트 입니다.

Linux 쉘상에서 SIGKILL
Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
saltmaster ~ # ps aux | grep post
postgres  6129  0.0  0.0 145096 15740 ?        S    10:57   0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf
postgres  6130  0.0  0.0 102748  1344 ?        Ss   10:57   0:00 postgres: logger process
root      6510  0.0  0.0  90232  4748 pts/10   S    11:01   0:00 su - postgres
postgres  6517  0.0  0.0  24224  5800 pts/10   S+   11:01   0:00 -su
postgres  6537  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: checkpointer process
postgres  6538  0.0  0.0 145096  4204 ?        Ss   11:01   0:00 postgres: writer process
postgres  6539  0.0  0.0 145096  3488 ?        Ss   11:01   0:00 postgres: wal writer process
postgres  6540  0.0  0.0 145916  5800 ?        Ss   11:01   0:00 postgres: autovacuum launcher process
postgres  6541  0.0  0.0 104840  3732 ?        Ss   11:01   0:00 postgres: stats collector process
root     13977  0.0  0.0  90232  4624 pts/0    S    13:18   0:00 su - postgres
postgres 13984  0.0  0.0  24224  5932 pts/0    S    13:18   0:00 -su
postgres 14003  0.0  0.0 101452  7188 pts/0    S+   13:18   0:00 /usr/lib/postgresql/9.2/bin/psql -U test testdb
postgres 19073  0.0  0.0 146848  8504 ?        Ss   14:47   0:00 postgres: test testdb [local] SELECT
]# kill -9 19073

‘kill -9 ‘ 는 SIGKILL 을 의미합니다. PostgreSQL 쉘은 다음과 같이 되었습니다.

SIGKILL을 받은 PostgreSQL 결과
PgSQL
1
2
3
4
5
6
testdb=> SELECT pg_sleep(100);
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>  <----- 쉘모양도 깨졌습니다.

SIGTERM과는 달리 “FATAL: terminating connection due to administrator command” 메시지도 없이 바로 접속이 차단되었습니다. 그리고 쉘 모양도 깨졌습니다.

PostgreSQL 에서의 로그는 다음과 같습니다.

SIGKILL을 받은 PostgreSQL 로그
Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2015-09-22 14:53:28 KST [6129-6] LOG:  server process (PID 19073) was terminated by signal 9: Killed
2015-09-22 14:53:28 KST [6129-7] DETAIL:  Failed process was running: SELECT pg_sleep(200);
2015-09-22 14:53:28 KST [6129-8] LOG:  terminating any other active server processes
2015-09-22 14:53:28 KST [6540-2] WARNING:  terminating connection because of crash of another server process
2015-09-22 14:53:28 KST [6540-3] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2015-09-22 14:53:28 KST [6540-4] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2015-09-22 14:53:28 KST [19452-1] [unknown]@[unknown] LOG:  connection received: host=[local]
2015-09-22 14:53:28 KST [19452-2] test@testdb FATAL:  the database system is in recovery mode
2015-09-22 14:53:28 KST [6129-9] LOG:  all server processes terminated; reinitializing
2015-09-22 14:53:28 KST [19453-1] LOG:  database system was interrupted; last known up at 2015-09-22 11:01:35 KST
2015-09-22 14:53:28 KST [19453-2] DEBUG:  checkpoint record is at 0/176D970
2015-09-22 14:53:28 KST [19453-3] DEBUG:  redo record is at 0/176D970; shutdown TRUE
2015-09-22 14:53:28 KST [19453-4] DEBUG:  next transaction ID: 0/725; next OID: 16387
2015-09-22 14:53:28 KST [19453-5] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2015-09-22 14:53:28 KST [19453-6] DEBUG:  oldest unfrozen transaction ID: 711, in database 1
2015-09-22 14:53:28 KST [19453-7] DEBUG:  transaction ID wrap limit is 2147484358, limited by database with OID 1
2015-09-22 14:53:28 KST [19453-8] LOG:  database system was not properly shut down; automatic recovery in progress
2015-09-22 14:53:28 KST [19453-9] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2015-09-22 14:53:28 KST [19453-10] LOG:  record with zero length at 0/176D9D0
2015-09-22 14:53:28 KST [19453-11] LOG:  redo is not required
2015-09-22 14:53:28 KST [19453-12] DEBUG:  resetting unlogged relations: cleanup 0 init 1
2015-09-22 14:53:28 KST [19453-13] LOG:  checkpoint starting: end-of-recovery immediate
2015-09-22 14:53:28 KST [19453-14] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s
2015-09-22 14:53:28 KST [19457-1] LOG:  autovacuum launcher started
2015-09-22 14:53:28 KST [6129-10] LOG:  database system is ready to accept connections

SIGTERM  때와는 달리 서버가 재시작되었습니다. Recovery Mode 로 전환해 트랜잭션을 복구하고 checkpoint 를 날려 메모리를 리셋했습니다. checkpoint 는 PostgreSQL의 shared_buffer 메모리에 있는 모든 데이터를 디스크에 Flush 하도록하는 것으로 메모리 초기화로 부르기도 합니다. 이렇게 되면 메모리 그래프상으로는 갑자기 메모리 사용량이 줄어드는 현상을 보입니다.

어쨌든 SIGKILL은 PostgreSQL을 재시작하는 결과를 가지고 옵니다. 이는 프로세스의 시작시간이 바뀌것으로도 판단할 수 있습니다.

PostgreSQL의 시작시간 변화
Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
postgres@saltmaster ~/9.2/main/pg_log $ ps aux | grep post
postgres  6129  0.0  0.0 145096 15704 ?        S    10:57   0:00 /usr/lib/postgresql/9.2/bin/postgres -D /var/lib/postgresql/9.2/main -c config_file=/etc/postgresql/9.2/main/postgresql.conf
postgres  6130  0.0  0.0 102748  1344 ?        Ss   10:57   0:00 postgres: logger process
root      6510  0.0  0.0  90232  4748 pts/10   S    11:01   0:00 su - postgres
postgres  6517  0.0  0.0  24224  5800 pts/10   S+   11:01   0:00 -su
root     13977  0.0  0.0  90232  4624 pts/0    S    13:18   0:00 su - postgres
postgres 13984  0.0  0.0  24224  5936 pts/0    S+   13:18   0:00 -su
postgres 19454  0.0  0.0 145096  3488 ?        Ss   14:53   0:00 postgres: checkpointer process
postgres 19455  0.0  0.0 145096  4204 ?        Ss   14:53   0:00 postgres: writer process
postgres 19456  0.0  0.0 145096  3488 ?        Ss   14:53   0:00 postgres: wal writer process
postgres 19457  0.0  0.0 145784  5664 ?        Ss   14:53   0:00 postgres: autovacuum launcher process
postgres 19458  0.0  0.0 104840  3564 ?        Ss   14:53   0:00 postgres: stats collector process
                                                 >프로세스시작시간<

프로세스 시작시간이 14:53분으로 이전과 달라졌습니다.

PostgreSQL 쉘 상에서 SIGTERM

PostgreSQL 쉘 상에서 SIGTERM은 pg_terminate_backend 함수를 사용함으로서 이루어집니다. pg_stat_activity 테이블에서 pid를 알아낸 다음 다음과 같이 쿼리를 했습니다.

PostgreSQL 쉘 상에서 SIGTERM
PgSQL
1
2
3
4
5
postgres=# select pg_terminate_backend(19954);
pg_terminate_backend
----------------------
t
(1 row)

쿼리를 수행중이던 다른 PostgreSQL 쉘에서는 다음과 같이 메시지가 나왔습니다.

SIGTERM 을 받은 PostgreSQL 결과
PgSQL
1
2
3
4
5
6
7
testdb=> SELECT pg_sleep(200);
LOG:  disconnection: session time: 0:01:36.779 user=test database=testdb host=[local]
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

그리고 PostgreSQL 의 로그는 다음과 같습니다.

SIGTERM을 받은 PostgreSQL 로그
Shell
1
2
3
4
2015-09-22 15:03:55 KST [20142-4] postgres@postgres LOG:  duration: 2.154 ms  statement: select pg_terminate_backend(19954);
2015-09-22 15:03:55 KST [19954-3] test@testdb FATAL:  terminating connection due to administrator command
2015-09-22 15:03:55 KST [19954-4] test@testdb STATEMENT:  SELECT pg_sleep(200);
2015-09-22 15:03:55 KST [19954-5] test@testdb LOG:  disconnection: session time: 0:01:36.779 user=test database=testdb host=[local]

이는 Linux 쉘 상에서 SIGTERM을 보낸것과 같은 결과 입니다.

PostgreSQL 쉘 상에서 SIGINT

이번에는 SIGINT에 관한 것입니다.

PostgreSQL 쉘 상에서 SIGINT
PgSQL
1
2
3
4
5
postgres=# select pg_cancel_backend(20197);
pg_cancel_backend
-------------------
t
(1 row)

PostgreSQL 쉘에서 쿼리를 보냈던 화면에는 다음과 같이 나왔습니다.

SIGINT을 받은 PostgreSQL 결과
PgSQL
1
2
testdb=> SELECT pg_sleep(200);
ERROR:  canceling statement due to user request

위 내용은 PostgreSQL 로그에도 동일 했습니다.

SIGINT를 받은 PostgreSQL 로그
Shell
1
2
3
2015-09-22 15:11:37 KST [20142-6] postgres@postgres LOG:  duration: 1.750 ms  statement: select pg_cancel_backend(20197);
2015-09-22 15:11:37 KST [20197-3] test@testdb ERROR:  canceling statement due to user request
2015-09-22 15:11:37 KST [20197-4] test@testdb STATEMENT:  SELECT pg_sleep(200);

결론

SIGINT 은 SIGTERM과 중요한 차이점을 보여주고 있습니다. SIGTERM은 connection 자체를 차단하게 합니다. 따라서 쿼리를 하기위해서는 반드시 PostgreSQL로 재 접속이 이루어져야 합니다. 반면에, SIGINT은 쿼리문(statement) 자체를 취소(cancel)하게 함으로써 connection 자체에는 아무런 영향을 주지 않습니다.

PostgreSQL 프로세스를 Kill 하면 발생하는 일

PostgreSQL 은 프로세스 복제 모델로 동작합니다. 접속을 하면 Postmaster 라 불리는 부모프로세스를 복제해서 접속을 하고 쿼리를 처리하도록 합니다. 그런데 데이터베이스를 운영하다보면 불필요한 쿼리를 없앤다고 운영체제에서 kill 명령어를 이용하는 경우가 있습니다.

결론부터 말하면 절대로 해서는 안되는 일 입니다. 이렇게하게되면 PostgreSQL은 다음과같이 모든 프로세스를 죽이고 서버를 재시작합니다.

Shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2015-09-22 11:01:35 KST [6129-2] LOG:  server process (PID 6345) was terminated by signal 9: Killed
2015-09-22 11:01:35 KST [6129-3] LOG:  terminating any other active server processes
2015-09-22 11:01:35 KST [6135-2] WARNING:  terminating connection because of crash of another server process
2015-09-22 11:01:35 KST [6135-3] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2015-09-22 11:01:35 KST [6135-4] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2015-09-22 11:01:35 KST [6129-4] LOG:  all server processes terminated; reinitializing
2015-09-22 11:01:35 KST [6536-1] LOG:  database system was interrupted; last known up at 2015-09-22 10:57:06 KST
2015-09-22 11:01:35 KST [6536-2] DEBUG:  checkpoint record is at 0/176D910
2015-09-22 11:01:35 KST [6536-3] DEBUG:  redo record is at 0/176D910; shutdown TRUE
2015-09-22 11:01:35 KST [6536-4] DEBUG:  next transaction ID: 0/725; next OID: 16387
2015-09-22 11:01:35 KST [6536-5] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2015-09-22 11:01:35 KST [6536-6] DEBUG:  oldest unfrozen transaction ID: 711, in database 1
2015-09-22 11:01:35 KST [6536-7] DEBUG:  transaction ID wrap limit is 2147484358, limited by database with OID 1
2015-09-22 11:01:35 KST [6536-8] LOG:  database system was not properly shut down; automatic recovery in progress
2015-09-22 11:01:35 KST [6536-9] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2015-09-22 11:01:35 KST [6536-10] LOG:  record with zero length at 0/176D970
2015-09-22 11:01:35 KST [6536-11] LOG:  redo is not required
2015-09-22 11:01:35 KST [6536-12] DEBUG:  resetting unlogged relations: cleanup 0 init 1
2015-09-22 11:01:35 KST [6536-13] LOG:  checkpoint starting: end-of-recovery immediate
2015-09-22 11:01:35 KST [6536-14] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s
2015-09-22 11:01:35 KST [6540-1] LOG:  autovacuum launcher started
2015-09-22 11:01:35 KST [6129-5] LOG:  database system is ready to accept connections

클라이언트와 접속중인 프로세스라서 kill -9를 해도 상관이 없다고 하겠지만 전혀 그렇지 않습니다. 위의 경우에는 데이터가 아무것도 없어서 금방 재시작이 되었지만 아주 바쁜 서버의 경우에, 다시 말해서 트랜잭션이 많은 서버에서는 재시작하는데도 상당히 오랜시간이 걸릴 수 있습니다.

만약 꼭 쿼리를 죽여야겠다고 한다면 psql 명령어로 PostgreSQL에 접속한 후에 pg_terminate_pid 함수를 이용해서 해야 합니다. 이 함수로 인해서 프로세스가 죽더라도 서버가 재시작되는 일은 거의 없습니다.

읽기전용 PostgreSQL 튜닝.

이 문서는 읽기전용 PostgreSQL 서버를 대상으로 하는 튜닝 경험에 대해서 정리한 것입니다.

환경

테스트된 서버 환경은 다음과 같습니다.

  • CPU: Inter(R) Xeon(R) CPU E5606@2.13GHz * 2
  • RAM: 32GB
  • HDD: SAS 450GB
  • OS: Linux
  • PostgreSQL: 9.2

이 서버는 하루에 한번 덤프된 PosgreSQL 데이터를 전송받아서 Restore 하고 읽기전용, 그러니까 SELECT 쿼리만 사용 합니다.

Linux 튜닝

리눅스 튜닝에 핵심은 공유메모리, 최대 열수 있는 파일수, 메모리관련 커널 파라메터들 입니다. 이는 최대 물리 메모리에 영향을 받습니다.

공유메모리

리눅스의 최대 공유메모리는 다음과 같은 계산으로 세팅이 가능합니다. 단, 다음의 공식은 절대적인 기준은 아니며 PostgreSQL 서버에만 적용됩니다. 인터넷 상에는 다양한 공식들이존재하지만 이 공식으로 나온 값만으로도 충분히 잘 동작하였습니다.

공유메모리 계산 공식
Shell
1
2
_SHMALL=$(expr $(getconf _PHYS_PAGES) \* 30 / 100)
_SHMMAX=$(expr $(getconf PAGE_SIZE) \* $_SHMALL)

32GB 물리 메모리를 가진 서버의 경우에 다음과 같이 리눅스 커널의 최대 공유메모리를 설정할 수 있습니다.

/etc/sysctl.conf
1
2
kernel.shmall = 2470023
kernel.shmmax = 10117214208

swappiness

데이터베이스 시스템은 고속으로 동작되어야 함으로 스왑 파티션을 사용을 자제해야 합니다. 아예 사용을 하지 않도록 리눅스를 설정합니다.

/etc/sysctl.conf
Shell
1
vm.swappiness = 0

Maximum number of open files

최대 열기 파일 설정은 기본적인 튜닝입니다. 대부분 다음과 같이 설정을 하면 충분합니다.이는 메모리가 크면 클수록 높여줄 수 있습니다.

/etc/security/limits.conf
Shell
1
2
postgres        soft      nofile        65535
postgres        hard      nofile        65535

리눅스 커널은 다음과 같이 설정할 수 있습니다.

/etc/sysctl.conf
Shell
1
fs.file-max = 65535

IPCS

ipcs 명령어는 PIC(Process Internal Communication) 에 정보를 볼 수 있습니다. 여기서 중요한 것은 세마포어가 중요합니다. 세마포어는 동시 프로세스를 제어하는데 중요한 요소 입니다.

ipcs -l
Shell
1
2
3
4
5
6
------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767

기본 설정으로도 좋습니다.

kernel version

커널은 3.13 이상을 사용하는 것이 좋습니다. 왜냐하면 3.13 버전에서 아주 크게 Disk I/O 가 향상되었기 때문입니다. PostgreSQL 의 데이터도 결국에는 하드디스크에 저장이 됩니다. 더우기 SELECT 전용으로 사용한다면 데이터를 하드디스크에서 자주 읽어들일겁니다. 따라서 커널 버전을 3.13 이상을 사용해야 합니다.

 

PostgreSQL 튜닝.

PostgreSQL 튜닝의 핵심은 접속자와 메모리 입니다. 주요 핵심 파라메터는 다음과 같습니다.

namecontextshor_desc
default_statistics_targetuserSets the default statistics target.
maintenance_work_memuserSets the maximum memory to be used for maintenance operations.
constraint_exclusionuserEnables the planner to use constraints to optimize queries.
checkpoint_completion_targetsighupTime spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval
effective_cache_sizeuserSets the planner's assumption about the size of the disk cache
work_memuserSets the maximum memory to be used for query workspaces.
wal_bufferspostmasterSets the number of disk-page buffers in shared memory for WAL.
checkpoint_segmentssighupSets the maximum distance in log segments between automatic WAL checkpoints.
shared_bufferspostmasterSets the number of shared memory buffers used by the server.

context 부분을 보면 그 값을 적용하기 위해서 무엇을 해야하는지 알려줍니다. user 부분은 PostgreSQL 를 재시작할 필요가 없습니다. 값을 변경하면 바로 그 값이 적용이 되며 오직 사용자 세션에게만 적용됩니다. 다른 사용자 세션에는 적용되지 않습니다. sighup 은 PostgreSQL 서버를 reload 해주거나 HUP Kill 시그널을 주면 적용됩니다. postmaster 는 반드시 PostgreSQL 서버를 재시작해줘야 적용이 됩니다.

default_statistics_target

이것은 통계와 관련이 있습니다. PostgreSQL 쿼리 플래너는 개별 테이블에 통계를 필요로 합니다. 만약 이 값이 적다면, 쿼리 플래너의 결과가 달라집니다. 대신 이 값이 너무 높으면 PostgreSQL 는 개별테이블의 통계를 수집하는데 많은 시간을 사용하게 됩니다.

만약 ‘LIKE’ 쿼리를 자주 사용한다면, 이값을 증가시켜줄 필요가 있습니다.  하지만 기본적인  값으로도 괜찮습니다.

maintenance_work_mem

maintenance 연산은 ‘CREATE INDEX, ALTER TABLE ADD FOREIGN KEY’ 그리고 ‘VACUUM’ 와 깊이 연관이 있습니다. SELECT 전용으로만 사용한다면 VACUUM은 필요가 없습니다. 하지만 덤프 파일을 Restore 할때에 인덱스를 생성하거나 ALTER TABLE 를 많이 사용되게 됩니다.

따라서 만약, 덤프 파일을 가지고 빠른 Restore 를 하고 싶다면 이 값을 조정해줘야 합니다. 단, 이 값은 work_mem 보다 항상 높아야 합니다. 그리고 이 값은 autovacuum_max_workers 값과 관련이 높습니다.

통상적으로 이값은 전체 메모리의 5% 정도로 잡습니다. 만약 32GB 라면 이값은 1600MB 입니다.

INI
1
maintenance_work_em = 1600MB

constraint_exclusion

이 값은 파티션 테이블과 관련이 깊습니다. 만약 파티션 테이블을 사용한다면, 반드신 설정을 해줘야 합니다. 성능향상을 위해서 상속이나 파티션된 테이블을 자주 사용됩니다.

checkpoint_completion_target

0.9 가 기본값인데, 대부분 괜찮습니다.

effective_cache_size

이 크기는 리눅스 시스템에서 모든 애플리케이션을 끈 상태에서 cache 메모리 크기를 봐야 합니다. 이 크기만큼 보통 할당을 합니다. 예를들어 리눅스 캐쉬 사용량이 22GB 라고한다면 이값을 22GB 정도 주면 됩니다.

이렇게 값을 준다고해서 실제로 메모리가 할당이 되지 않습니다. 쿼리 플래너가 작업을 할때에 잠깐사용 합니다. 만약 이를 값이 적다면 리눅스는 파일 시스템을 사용하게 되어서 성능이 저하됩니다.

work_mem

이 메모리는 복잡한 sort가 많이 사용되거나 큰 sort 가 필요하다면 이 값을 증가 시켜줄 필요가 있습니다. 한가지 조심해야 할 것은 이 값은 각 사용자 세션별로 할당이 됩니다. 따라서 이 값을 정할때에 PostgreSQL 의 최대 접속자를 고려해야 합니다.

이 값을 대략 다음과 같이 계산을 합니다.

  • ( OS cache memory / connections ) * 0.5

만약 OS cache memory 사용량이 28GB 이거나 접속량이 20 이라면 다음과 같습니다.

  • (28/20)*0.5 = 700MB

shared_buffers

전체 메모리의 25% 정도가 적당합니다. 만일 32GB 메모리를 사용한다면 대략 8GB 정도면 충분합니다.

결론

정리를 한다면 다음과 같습니다.

/etc/sysctl.conf

/etc/sysctl.conf
INI
1
2
3
4
5
kernel.shmall = 2470023
kernel.shmmax = 10117214208
vm.swappiness = 0
kernel.sched_migration_cost_ns = 5000000
fs.file-max = 65535

/etc/security/limits.conf

/etc/security/limits.conf
INI
1
2
postgres        soft    nofile           65535
postgres        hard    nofile           65535

postgresql.conf

postgresql.conf
INI
1
2
3
4
5
6
7
8
9
default_statistics_target = 100
maintenance_work_mem = 4GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 16GB
work_mem = 700MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 8GB