ksj.name

PostgreSQL 설치및 기본 사용법



PostgreSQL 의 공식 사이트 http://www.postgresql.org/




최근 postgresql 의 사용이 늘어나는 추세이니 발빠른 서버관리자들은 이미 사용법을 익히고 있을 것으로 판단됩니다만
언제나 모르는 사람은 있는법. 딱 서버관리자가 알아야 할 만큼만의 내용을 써 보겠습니다.

서버관리자가 해야 하는것은 크게 나누면 설치, 관리, 튜닝, 백업, 리스토어,검색,삭제 정도라고 볼 수 있지요.
그렇기 때문에 최소한 모든 프로그램의 사용법을 그 분야의 전문가 수준은 아니더라도 어느수준이상을 알고 있어야 합니다.
언제 써먹을지도 모르지만 말이죠.

그럼 시작하겠습니다.



1)설치
PostgreSQL 을 rpm 으로 설치하는것은 그냥 설치만 하면 자동으로 다 알아서 만들어주고 처리해 주니 소스설치에 대한
부분만 설명하고 넘어가겠습니다.

공식 다운로드 배포처는 postgresql.org 입니다만 여러곳에서 링크하고 있으니 다운 받으시면 되구요.
postgresql-7.3.2.tar.gz 버전으로 설명을 하도록 하겠습니다.

첫째로 압축을 해제하구요.





[root@ora local]# tar xvfzp postgresql-7.3.2.tar.gz
postgresql-7.3.2/
postgresql-7.3.2/config/
postgresql-7.3.2/config/libtool.m4
postgresql-7.3.2/config/java.m4
postgresql-7.3.2/config/ac_func_accept_argtypes.m4


......주루륵............


postgresql-7.3.2/README
postgresql-7.3.2/configure.in
postgresql-7.3.2/configure
postgresql-7.3.2/register.txt
[root@ora local]#






풀리고 나면 압축이 풀린 폴더로 들어갑니다.



[root@ora local]# cd postgresql-7.3.2
[root@ora postgresql-7.3.2]# ls
COPYRIGHT       INSTALL   aclocal.m4  configure.in  register.txt
GNUmakefile.in  Makefile  config      contrib       src
HISTORY         README    configure   doc
[root@ora postgresql-7.3.2]#




설정파일을 시스템에 맞도록 갱신하는 작업을 합니다.
수많은 옵션이 있지만 추후에 ./configure --help 로 확인하기로 하고 우선 필요한 설치폴더 설정과
멀티바이트를 지원하도록 합니다.



[root@ora postgresql-7.3.2]# ./configure --prefix=/usr/local/pgsql --enable-multibyte
checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking which template to use... linux
checking whether to build with 64-bit integer date/time support... no
checking whether to build with recode support... no
checking whether NLS is wanted... no


.......주루륵......................


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@ora postgresql-7.3.2]#






설정이 무리없이 끝나면 make 와 make install 을 합니다.



[root@ora postgresql-7.3.2]# make ; make install
make -C doc all
make[1]: Entering directory `/usr/local/postgresql-7.3.2/doc'
gzip -d -c man.tar.gz | /bin/tar xf -
for file in man1/*.1; do
  mv $file $file.bak &&

....................

And, so that we have an idea of who is using what, please connect to the
following registration URL:

        http://www.pgsql.com/register/submit.php


Thank you for choosing PostgreSQL, the most advanced open source database
engine.


[root@ora postgresql-7.3.2]#







설치가 다 되었으면 이제 설치된 폴더로 이동합니다.



[root@ora postgresql-7.3.2]# cd /usr/local/pgsql/
[root@ora pgsql]# ls
bin  doc  include  lib  man  share
[root@ora pgsql]# cd bin
[root@ora bin]# ls
clusterdb   dropdb    initdb        pg_controldata  pg_encoding   postgres
createdb    droplang  initlocation  pg_ctl          pg_id         postmaster
createlang  dropuser  ipcclean      pg_dump         pg_resetxlog  psql
createuser  ecpg      pg_config     pg_dumpall      pg_restore    vacuumdb
[root@ora bin]#






데이터베이스를 초기화 하고 작동시키기전에 현 postgresql 을 가동할 권한자를 생성합니다.



[root@ora bin]#adduser -d /usr/local/pgsql postgres
[root@ora bin]# mkdir /usr/local/pgsql/data
[root@ora bin]# chown -R postgres.postgres /usr/local/pgsql/
[root@ora bin]#






그런다음 데이터베이스를 초기화 하기위하여 postgres 계정으로 로그인 한 후 데이터베이스를 초기화합니다.



[root@ora bin]# su - postgres
[postgres@ora pgsql]$
[postgres@ora pgsql]$ ls
bin  data  doc  include  lib  man  share
[postgres@ora pgsql]$ cd bin
[postgres@ora bin]$ ls
clusterdb   dropdb    initdb        pg_controldata  pg_encoding   postgres
createdb    droplang  initlocation  pg_ctl          pg_id         postmaster
createlang  dropuser  ipcclean      pg_dump         pg_resetxlog  psql
createuser  ecpg      pg_config     pg_dumpall      pg_restore    vacuumdb
[postgres@ora bin]$ ./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.euckr.
This locale setting will prevent the use of indexes for pattern matching
operations.  If that is a concern, rerun initdb with the collation order
set to "C".  For more information see the Administrator's Guide.

Fixing permissions on existing directory /usr/local/pgsql/data/... ok
creating directory /usr/local/pgsql/data//base... ok
creating directory /usr/local/pgsql/data//global... ok
creating directory /usr/local/pgsql/data//pg_xlog... ok


...........략................


Success. You can now start the database server using:

    ./postmaster -D /usr/local/pgsql/data/
or
    ./pg_ctl -D /usr/local/pgsql/data/ -l logfile start

[postgres@ora bin]$






라고 하고 기본데이터베이스가 생성됩니다.

데이터베이스를 가동시켜 보도록 합니다.


[postgres@ora bin]$ ./postmaster -D /usr/local/pgsql/data/ &
[1] 723
[postgres@ora bin]$ LOG:  database system was shut down at 2003-05-26 11:43:55 K
ST
LOG:  checkpoint record is at 0/8018FC
LOG:  redo record is at 0/8018FC; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 480; next oid: 16976
LOG:  database system is ready

[postgres@ora bin]$ ps ax|grep post
  351 pts/0    S      0:00 su - postgres
  723 pts/0    S      0:00 ./postmaster -D /usr/local/pgsql/data/
  724 pts/0    S      0:00 postgres: stats buffer process
  726 pts/0    S      0:00 postgres: stats collector process
  728 pts/0    S      0:00 grep post
[postgres@ora bin]$




데이터베이스가 작동된것이 확인 됩니다.

로그인 해 보죠.,




[postgres@ora bin]$ ./psql template1
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

Type:  copyright for distribution terms
       h for help with SQL commands
       ? for help on internal slash commands
       g or terminate with semicolon to execute query
       q to quit

template1=#






'template1=#' 이것이 postgresql의 명령어 대기쉘입니다.

우선은 postgres 의 패스워드를 변경하고 postgresql 을 패스워드 모드로 작동하게 하겠습니다.
postgresql 은 기본적으로 아무 사용자가 접근가능한 모드로 작동이 되더군요.이유는 모르겠습니다.
제가 잘못 안 것일 수도 있고..--a..

패스워드를 먼저 변경하도록 하지요.




template1=# alter user postgres with password '1234';
ALTER USER
template1=# select*from pg_user;
usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+-----------
postgres |        1 | t           | t        | t         | ******** |          |
(1 row)

template1=#






그리고 일단 빠져나온 후 /usr/local/pgsql/data 폴더로 이동하여 pg_hba.conf 파일을 열어서 아래의 내용을 바꿉니다.




template1=# q
[postgres@ora bin]$ cd ../data/
[postgres@ora data]$ ls
PG_VERSION  global   pg_hba.conf    pg_xlog          postmaster.opts
base        pg_clog  pg_ident.conf  postgresql.conf  postmaster.pid
[postgres@ora data]$vi pg_hba.conf








pg_hba.conf 파일의 내용중

==============================================================================
# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD

local   all         all                                             trust
host    all         all         127.0.0.1         255.255.255.255   trust
==============================================================================
를
==============================================================================
# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD

local   all         all                                             password
host    all         all         127.0.0.1         255.255.255.255   password
==============================================================================


위와같이 trust -> password 로 바꾸어 준 뒤 저장합니다.


그리고 데이터베이스를 재시동 하고 로그인 해 봅니다.


[postgres@ora data]$ cd ../bin
[postgres@ora bin]$ ./pg_ctl restart -D /usr/local/pgsql/data/
waiting for postmaster to shut down....LOG:  smart shutdown request
LOG:  shutting down
.LOG:  database system is shut down
.done
postmaster successfully shut down
postmaster successfully started
[postgres@ora bin]$ LOG:  database system was shut down at 2003-05-26 12:12:45 KST
LOG:  checkpoint record is at 0/81402C
LOG:  redo record is at 0/81402C; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 569; next oid: 16977
LOG:  database system is ready

[postgres@ora bin]$ ./psql template1
Password: ****
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

Type:  copyright for distribution terms
       h for help with SQL commands
       ? for help on internal slash commands
       g or terminate with semicolon to execute query
       q to quit

template1=#

Apache, MySQL, PostgreSQL, PHP 컴파일 옵션

CREATE FUNCTION pgl_get_rows() RETURNS SETOF RECORD AS
'
DECLARE
  row RECORD;

BEGIN
  FOR row IN EXECUTE ''select field1,field2 from pkg_test ''
  LOOP
    RETURN next row;

  END LOOP;
  RETURN;
END;
' LANGUAGE 'plpgsql' ;

super계정으로 들어가야됨..

DB 에 들어가서..

#create function plpgsql_call_handler() returns opaque as '/usr/local/pgsql/lib/plpgsql.so' language 'C';


#create trusted procedural language 'plpgsql' handler plpgsql_call_handler lancompiler 'PL/pgSQL';

1.\h를 치면 SQL 명령어에 대한 도움말.

2.\?를 치면 시스템 명령어

3.\L 를 치면 DataBase 의 리스트.

4.\DT 를 치면 접속한 Database 의 Table 정보

5.\d Table 이름을 치면 Table 의 정보

1.공유 메모리 체크
  : 현재 리눅스 시스템의 공유 메모리 체크
  #cat /proc/sys/kernel/shmmax
 
2.대부분의 리눅스 시스템은 위의 명령어로
  33554432 와 같은 값을 갖는다(32MB)
  이를 늘리자..128MB 로
 
3.늘리는 방법
  #echo 128000000 > /proc/sys/kernel/shmmax
   3번의 명령어를 해도 리부팅시에는 위의 설정이 사라진다.
   /etc/sysctl.conf 에 적용하면 좋다.
4.postgresql.conf 파일 편집

  shared_buffers = 15200
  sort_mem = 32168
  max_connections=64
  fsync=false
 
  위의 15200 은 (128000/8192 다, postgresql 은 8K segment 를 사용한다.)
 
 
실행
#/postmaster start > /var/lib/pgsql.log

 

 

실행방법

1)

$/usr/local/pgsql/bin/postmaster start > /home/pgsql/pgsql.log

 

2)

$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &

 

3)

Success. You can now start the database server using:

    ./postmaster -D /usr/local/pgsql/data
or
    ./pg_ctl -D /usr/local/pgsql/data -l logfile start