Tagged: postgresql

PostgreSQL 설치.

pgsql-logo

PostgreSQL 은 인기있는 오픈소스 데이터베이스 시스템 입니다. PostgreSQL 9.0 으로 넘어오면서 ‘Hot Stand by Replication’ 기능을 제공하게 되는등 많은 변화가 있었습니다.

이 문서는 CentOS 7 배포판에서 PostgreSQL 설치에 대한 것입니다.

 

1. 준비

다음의 패키지가 설치되어 있어야 합니다.

PostgreSQL 설치를 위한 의존성 패키지.
Shell
1
yum install gcc.x86_64 gcc-c++.x86_64 wget.x86_64 bzip2-devel.x86_64 pkgconfig.x86_64 openssl-devel.x86_64 make.x86_64 man.x86_64 nasm.x86_64 gmp.x86_64 gdbm-devel.x86_64 readline-devel.x86_64 compat-readline43.x86_64 ncurses-devel.x86_64 db4-devel.x86_64 automake* autoconf* pcre-devel.x86_64 -y

2. Download and Unpack

Download and Unpack
Shell
1
2
3
4
cd /usr/src
wget http://ftp.postgresql.org/pub/source/v9.4beta2/postgresql-9.4beta2.tar.bz2
tar xjf postgresql-9.4beta2.tar.bz2
cd postgresql-9.4beta2

3. Configure and Compile and Install

필요한 기능이 무엇인지를 고민해 봅시다. 많은 기능들을 제공하지만 대략적으로 다음과 같은 기능을 제공하는 것이 좋습니다.

  • plpython => python-devel.x86_64
  • pltcl => tcl-devel.x86_64
  • plperf => perl-devel.x86_64
  • ssl => openssl-devel.x86_64
  • pam => pam-devel.x86_64
  • kerberos => krb5-devel.x86_64 e2fsprogs-devel.x86_64
  • nls => gettext-devel.x86_64
  • xml => libxml2-devel.x86_64 libxslt-devel.x86_64
  • ldap => openldap-devel.x86_64

PostgreSQL 9.x 를 설치하는데 있어서 필요한 기능이 무엇인지를 먼저 생각해야 합니다. 각 기능에 대한 것은 각각 홈페이지를 참고하세요.

필요한 패키지를 설치 합니다.

필요한 패키지 설치.
Shell
1
yum install -y zlib-devel.x86_64 bison.x86_64 flex.x86_64

그리고 기능을 위한 패키지를 설치 합니다.

기능을 위한 패키지 설치.
Shell
1
yum install -y tcl-devel.x86_64 perl-devel.x86_64 openssl-devel.x86_64 pam-devel.x86_64 krb5-devel.x86_64 e2fsprogs-devel.x86_64 gettext-devel.x86_64 libxml2-devel.x86_64 libxslt-devel.x86_64 perl-ExtUtils-Embed.noarch python-devel.x86_64

Compile option 을 다음과 같이 지정합니다.

Compile Option
Shell
1
2
3
4
5
export CPPFLAGS=' -I/usr/include/et'
export CFLAGS='-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=k8 -I/usr/include/et -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE'
export CXXFLAGS='-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=k8 -I/usr/include/et -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE'
export LIBNAME=lib64
export PYTHON=/usr/bin/python

mtune 을 자신의 CPU 에 맞게끔 수정해야 합니다. 자세한 사항은 여기(GCC Optimization)를 참고 하세요.

Configure를 다음과 같이 실행하고 컴파일을 합니다.

Configure
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
26
27
28
./configure \
--disable-rpath \
--prefix=/usr/local/pgsql9.4 \
--with-perl \
--with-python \
--with-tcl \
--with-tclconfig=/usr/lib64 \
--with-openssl \
--with-pam \
--with-gssapi \
--with-includes=/usr/include \
--with-libraries=/usr/lib64 \
--enable-nls --enable-profiling \
--disable-thread-safety \
--with-libxml \
--with-libxslt \
--with-system-tzdata=/usr/share/zoneinfo \
--sysconfdir=/etc/sysconfig/pgsql
 
make world
#tutorial compile
sed "s|C=`pwd`;|C=/usr/local/pgsql9.4/lib/tutorial;|" < src/tutorial/Makefile > src/tutorial/GNUmakefile
make -C src/tutorial NO_PGXS=1 all
 
make install
make -C contrib install
mkdir /usr/local/pgsql9.4/share/man
mv doc/src/sgml/man1 doc/src/sgml/man3 doc/src/sgml/man7 /usr/local/pgsql9.4/share/man/

4. 후속작업

PostgreSQL 9.x 를 시스템에 이식시키기 위해서 다음과 같은 과정을 진행해 줍니다.

PostgreSQL 시스템 계정 추가

PostgreSQL 시스템 계정 추가.
Shell
1
2
3
4
export PGPREFIX=/usr/local/pgsql9.4
groupadd -g 26 -o -r postgres
useradd -M -n -g postgres -o -r -d $PGPREFIX -s /bin/bash -c "PostgreSQL Server" -u 26 postgres
chown postgres $PGPREFIX

initdb 실행

initdb 실행
Shell
1
2
export PGDATA=/usr/local/pgsql9.4/data
su -l postgres -c "/usr/local/pgsql9.4/bin/initdb -E UTF-8 --locale=ko_KR.UTF-8 --lc-collate=ko_KR.UTF-8 --pgdata='$PGDATA' "

Pam 설정

Pam 설정.
Vim
1
2
3
4
]# vi /etc/pam.d/postgresql
#%PAM-1.0
auth            include         system-auth
account         include         system-auth

ldconfig 라이브러리 추가

ldconfig 라이브러리 추가
Shell
1
2
echo "/usr/local/pgsql9.4/lib" > /etc/ld.so.conf.d/postgresql.conf
ldconfig

lib64 심볼릭 링크

php와 같은 프로그램들은 설치시에 ‘/usr/lib64’ 와 같은 라이브러리 디렉토리를 검색합니다. 따라서 pgsql9.4의 lib 디렉토리를 lib64 심볼릭 링크를 만들어 줍니다.

lib64 심볼릭 링크
Shell
1
2
cd /usr/local/pgsql9.4
ln -s lib lib64

Systemd 설정

CentOS 7 부터는 initscript 가 아닌 systemd 를 이용해 데몬 서비스들을 관리 합니다. 먼저 systemd 에 등록을 위한 파일을 다음과 같이 작성 합니다.

postgresql.service 파일

postgresql.service 파일.
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
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
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/postgresql.service",
# containing
#       .include /lib/systemd/system/postgresql.service
#       ...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F
 
# For example, if you want to change the server's port number to 5433,
# create a file named "/etc/systemd/system/postgresql.service" containing:
#       .include /lib/systemd/system/postgresql.service
#       [Service]
#       Environment=PGPORT=5433
# This will override the setting appearing below.
 
# Note: changing PGPORT or PGDATA will typically require adjusting SELinux
# configuration as well; see /usr/share/doc/postgresql-*/README.rpm-dist.
 
# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-setup.
 
# Note: in F-17 and beyond, /usr/lib/... is recommended in the .include line
# though /lib/... will still work.
 
[Unit]
Description=PostgreSQL database server
After=network.target
 
[Service]
Type=forking
 
User=postgres
Group=postgres
 
# Port number for server to listen on
Environment=PGPORT=5432
 
# Location of database directory
Environment=PGDATA=/usr/local/pgsql9.4/data
 
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
 
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
 
ExecStartPre=/usr/local/pgsql9.4/bin/postgresql-check-db-dir ${PGDATA}
ExecStart=/usr/local/pgsql9.4/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/usr/local/pgsql9.4/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/pgsql9.4/bin/pg_ctl reload -D ${PGDATA} -s
 
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
 
[Install]
WantedBy=multi-user.target

systemd 에 등록할 파일을 위와같이 작성합니다. 작성한 후에 /usr/lib/systemd/system 디렉토리에 복사주고 활성화 해줍니다.

postgresql.service 등록.
Shell
1
2
3
mv postgresql.service /usr/lib/systemd/system
 
systemctl enable postgresql.service

그리고 파일 내용에 postgresql-check-db-dir 파일을 호출하는데 이는 다음과 같습니다.

postgresql-check-db-dir 파일.

postgresql check db directory 스크립트.
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
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
#!/bin/sh
 
# This script verifies that the postgresql data directory has been correctly
# initialized.  We do not want to automatically initdb it, because that has
# a risk of catastrophic failure (ie, overwriting a valuable database) in
# corner cases, such as a remotely mounted database on a volume that's a
# bit slow to mount.  But we can at least emit a message advising newbies
# what to do.
 
PGDATA="$1"
 
if [ -z "$PGDATA" ]
then
    echo "Usage: $0 database-path"
    exit 1
fi
 
# PGVERSION is the full package version, e.g., 9.1.2
# Note: the specfile inserts the correct value during package build
PGVERSION=9.4
# PGMAJORVERSION is major version, e.g., 9.1 (this should match PG_VERSION)
PGMAJORVERSION=`echo "$PGVERSION" | sed 's/^\([0-9]*\.[0-9]*\).*$/\1/'`
# PREVMAJORVERSION is the previous major version, e.g., 8.4, for upgrades
# Note: the specfile inserts the correct value during package build
PREVMAJORVERSION=9.4
 
# Check for the PGDATA structure
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base" ]
then
        if [ x`cat "$PGDATA/PG_VERSION"` = x"$PGMAJORVERSION" ]
        then
                : A-OK
        elif [ x`cat "$PGDATA/PG_VERSION"` = x"$PREVMAJORVERSION" ]
        then
                echo $"An old version of the database format was found."
                echo $"You may upgrade to version $PGMAJORVERSION."
                exit 1
        else
                echo $"An old version of the database format was found."
                echo $"You need to dump and reload before using PostgreSQL $PGMAJORVERSION."
                echo $"You may upgrade to version $PGMAJORVERSION."
                exit 1
        fi
else
    # No existing PGDATA! Warn the user to initdb it.
    echo $"\"$PGDATA\" is missing or empty."
    echo $"Use \"initdb\" to initialize the database cluster."
    exit 1
fi
 
exit 0