레드헷계열 리눅스에서 PostgreSQL의 설치

오라클을 설치한 김에 내친김에 PostgreSQL을 설치해보자.
다운받는곳 :
http://www.snowrice.com/freepds/1112
http://wwwmaster.postgresql.org/download/mirrors-ftp/source/v8.4.0/postgresql-8.4.0.tar.gz

PostgreSQL은 MySQL과 같이 오픈 RDBMS로서 안정성이 각광받고 있는 SQL입니다.
레드헷 배포판에서도 설치옵션이 들어있습니다.
안정성은 MySQL보다 뛰어나나 MySQL보다는 약간 느립니다.
그외 MySQL이 갖고 있지 않는 여러 장점의 기능들이 있지요..엔터프라이즈급 SQL에 준하는 쿼리체계와 안정성을 갖고 있는거라 하겠는데요..

그리고 표준SQL이라 할수 있는 ANSI SQL을 준수하려는 노력이 엿보이는 제품입니다.

각설하고 php,tomcat,java를 설치하기전에 Oracle , PostgreSQL , MySQL을 모두 설치해보는 포스팅을 합니다.
오라클에 이어 두번째 DB인 PostgreSQL입니다. 

다운을 받으면 아무 디렉토리에 옮겨서 압축을 풉니다.

[root@akas postgresql-8.4.0]# ./configure --prefix=/usr/local/pgsql --with-ldap --with-libxml --with-openssl --with-gnu-ld 생략 config.status: linking ./src/backend/port/tas/dummy.s to src/backend/port/tas.s config.status: linking ./src/backend/port/dynloader/linux.c to src/backend/port/dynloader.c config.status: linking ./src/backend/port/sysv_sema.c to src/backend/port/pg_sema.c config.status: linking ./src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c config.status: linking ./src/backend/port/dynloader/linux.h to src/include/dynloader.h config.status: linking ./src/include/port/linux.h to src/include/pg_config_os.h config.status: linking ./src/makefiles/Makefile.linux to src/Makefile.port [root@akas postgresql-8.4.0]#make 생략mkdir ./testtablespace make[2]: Leaving directory `/root/Desktop/postgresql-8.4.0/src/test/regress' make[1]: Leaving directory `/root/Desktop/postgresql-8.4.0/src' make -C config all make[1]: Entering directory `/root/Desktop/postgresql-8.4.0/config' make[1]: `all'를 위해 할 일이 없습니다 make[1]: Leaving directory `/root/Desktop/postgresql-8.4.0/config' All of PostgreSQL successfully made. Ready to install. [root@akas postgresql-8.4.0]# make install 생략make[2]: Leaving directory `/root/Desktop/postgresql-8.4.0/src/test/regress' make[1]: Leaving directory `/root/Desktop/postgresql-8.4.0/src' make -C config install make[1]: Entering directory `/root/Desktop/postgresql-8.4.0/config' mkdir -p -- /usr/local/pgsql/lib/pgxs/config /bin/sh ../config/install-sh -c -m 755 ./install-sh '/usr/local/pgsql/lib/pgxs/config/install-sh' /bin/sh ../config/install-sh -c -m 755 ./mkinstalldirs '/usr/local/pgsql/lib/pgxs/config/mkinstalldirs' make[1]: Leaving directory `/root/Desktop/postgresql-8.4.0/config' PostgreSQL installation complete. [root@akas postgresql-8.4.0]# ls /usr/local/ bin  etc  games  include  lib  libexec  pgsql  sbin  share  src

설치가 말끔히 끝났다.
이제 관리자 설정을 해주고 DB를 구동시켜보죠.

아래와 같이 관리자계정을 만들고 권한을 줍니다. DB관리자라서 .bash_profile옵션이 없기 때문에 프롬프트 정의가 없어집니다.

[root@akas postgresql-8.4.0]# cd /usr/local/pgsql/bin [root@akas bin]# ls clusterdb   createuser  dropuser  pg_config       pg_dump       pg_restore  psql createdb    dropdb      ecpg      pg_controldata  pg_dumpall    postgres    reindexdb createlang  droplang    initdb    pg_ctl          pg_resetxlog  postmaster  vacuumdb [root@akas bin]# adduser -d /usr/local/pgsql postgres adduser: 경고: 홈디렉토리가 이미 존재합니다. skel 디렉토리에서 파일을 복사하지 않습니다. [root@akas bin]# ls /home/ bestakas  oracle [root@akas bin]# mkdir /usr/local/pgsql/data [root@akas bin]# chown -R postgres.postgres /usr/local/pgsql [root@akas bin]#

posrgres로 접속합니다.
db를 초기화합니다.

[root@akas bin]# su postgres bash-3.2$ ls clusterdb   createuser  dropuser  pg_config       pg_dump       pg_restore  psql createdb    dropdb      ecpg      pg_controldata  pg_dumpall    postgres    reindexdb createlang  droplang    initdb    pg_ctl          pg_resetxlog  postmaster  vacuumdb bash-3.2$ pwd /usr/local/pgsql/bin bash-3.2$ ./initdb /usr/local/pgsql/data The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale ko_KR.UTF-8. The default database encoding has accordingly been set to UTF8. initdb: could not find suitable text search configuration for locale ko_KR.UTF-8 The default text search configuration will be set to "simple". fixing permissions on existing directory /usr/local/pgsql/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in /usr/local/pgsql/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... ok copying template1 to postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using:     ./postgres -D /usr/local/pgsql/data or     ./pg_ctl -D /usr/local/pgsql/data -l logfile start

PostgreSQL를 구동합니다. MySQL과 비슷한설정입니다.

bash-3.2$ ./postmaster -D /usr/local/pgsql/data & [1] 17637 bash-3.2$ LOG:  database system was shut down at 2009-07-15 03:08:57 KST LOG:  autovacuum launcher started LOG:  database system is ready to accept connections

SQL데몬이 정상적으로 가동된것을 알수있습니다.

bash-3.2$ ps ax | grep post 17494 pts/1    S      0:00 su postgres 17637 pts/1    S      0:00 ./postmaster -D /usr/local/pgsql/data 17639 ?        Ss     0:00 postgres: writer process 17640 ?        Ss     0:00 postgres: wal writer process 17641 ?        Rs     0:00 postgres: autovacuum launcher process 17642 ?        Ss     0:00 postgres: stats collector process 17678 pts/1    R+     0:00 grep post

관리자에게 DB password를 줍니다.

bash-3.2$ ./psql template1 psql (8.4.0) Type "help" for help. template1=# alter user postgres with password '1234; ALTER ROLE template1=# select * from pg_user;  usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig ----------+----------+-------------+----------+-----------+----------+----------+-----------  postgres |       10 | t           | t        | t         | ******** |          | (1 row)

/usr/local/pgsql/data/pg_hba.conf를 수정합니다. trust된 항목을 아래와 같이 password로 바꾸어줍니다.

bash-3.2$ vi pg_hba.conf # "local" is for Unix domain socket connections only local   all         all                               password # IPv4 local connections: host    all         all         127.0.0.1/32          password # IPv6 local connections: host    all         all         ::1/128               password

PostgreSQL을 재가동합니다.

bash-3.2$ pwd /usr/local/pgsql bash-3.2$ cd bin bash-3.2$ ./pg_ctl restart -D /usr/local/pgsql/data LOG:  received smart shutdown request LOG:  autovacuum launcher shutting down LOG:  shutting down waiting for server to shut down....LOG:  database system is shut down  done server stopped server starting [1]+  Done                    ./postmaster -D /usr/local/pgsql/data bash-3.2$ LOG:  database system was shut down at 2009-07-15 03:24:38 KST LOG:  autovacuum launcher started LOG:  database system is ready to accept connections

관리자로 template1 DB를 접속하니 Password를 물어봅니다.

bash-3.2$ ./psql template1 Password: psql (8.4.0) Type "help" for help. template1=# \q

모든 설정이 끝났습니다.

이제 부팅시 자동으로 로드되게 하고, 루트계정으로 가동 및 중지, 재가동이 가능한 스크립트를 작성합시다.
다시 루트로 돌아와 아래와 같은 스크립트를 만들어줍니다.

[root@localhost pgsql]# vi /etc/rc.d/init.d/postgresql #!/bin/sh   # PostgreSQL START/STOP Script   SERVER=/usr/local/pgsql/bin/postmaster PGCTL=/usr/local/pgsql/bin/pg_ctl PGDATA=/usr/local/pgsql/data OPTIONS=-i LOGFILE=/usr/local/pgsql/data/postmaster.log   case "$1" in     start)         echo -n "Starting PostgreSQL..."         su -l postgres -c "nohup $SERVER $OPTIONS -D $PGDATA >> $LOGFILE 2>&1 &"         ;;      stop)         echo -n"Stopping PostgreSQL..."         su -l postgres -c "$PGCTL -D $PGDATA stop"         ;;      *)          echo "Usage: $0 {start|stop}"         exit 1         ;;  esac exit 0

스크립트 실행권한을 만듭니다.

[root@localhost pgsql]# chmod 755 /etc/rc.d/init.d/postgresql [root@akas ~]# ls -la /etc/rc.d/init.d/postgresql -rwxr-xr-x 1 root root 566  7월 15 03:44 /etc/rc.d/init.d/postgresql

스크립트를 이용하여 stop명령과 start명령을 내려봅니다. OK입니다.

[root@akas ~]# /etc/rc.d/init.d/postgresql stop -nStopping PostgreSQL... LOG:  received smart shutdown request LOG:  autovacuum launcher shutting down LOG:  shutting down waiting for server to shut down....LOG:  database system is shut down  done server stopped [root@akas ~]# /etc/rc.d/init.d/postgresql start Starting PostgreSQL...[root@akas ~]#

오라클과 같이 /etc/rc.local에 등록하여, 부팅시 가동되게 만들어주면 됩니다.

[root@akas ~]# vi /etc/rc.local #!/bin/sh # # This script will be executed *after* all the other init scripts. # You can put your own initialization stuff in here if you don't # want to do the full Sys V style init stuff. touch /var/lock/subsys/local /etc/init.d/oracle start /etc/rc.d/init.d/postgresql start