ͺ̽ PostgreSQL (2)

    ѵ ( ǥû)

     

 

1. 

    PostgreSQL 6.2 ԰, ̾ ĥĿ 6.2.1 ǥǾ.
    PostgreSQL 6.2ʹ ǥSQL ϰڴٰ Ѵ.
    PostgreSQL 6.2 κе Ǿµ, SQL Ǿ Ͽ κ CREATE TRIGGER ̺ Ư ʵ忡 NOT NULL DEFAULT , ʵ ϰ üũϴ CONSTRAINT ̴. ̷ ۾ ε Ͽ ó ־, Ǿ Ѱ ͸ Ʋ ̴. PostgreSQL 6.2.1 6.2 ġ Ͱ. Ư ߰ٴ ణ ָ ̷ ִ. ƿ﷯ PostgreSQL6.2 6.2.1 ġ ռ 6.1.1 ġ ϴ. ؾȿ ǥ SQL Ϻ ϱ⸦ 鼭 ϵ .

 

2.PostgreSQL

    PostgreSQL Ǵ . κ ڰ SQL̳ ٸ DBMS ͼϴٰ ϰ .
    PostgreSQL ϴ Ŵ 95 POSTGRES95 1.0 ϰ ִ. PostgreSQL ణ ̰ ִ. ƿ﷯ PostgreSQL Ӱ ǰų Ư¡ POSTGRES95 1.0 Ŵ󿡴 ʴ. ̷ κ ذϱ PostgreSQL Ʈ Ӱ ̿ õ Ʈ ǰ ݰ ƴ .
    PostgreSQL Ŵ ̴. ǥ SQL Ӹ ƴ϶ PostgreSQL ϴ UNIX ɾ Ÿ, ORACLE ȣȯ Լ, ̺귯 ƾ鿡 ֽ ִ. ǥ ġ ٸ /usr/local/pgsql/man θ ߰ ¶ Ȱ ִ.

      $ man l create_sequence

    PostgreSQL ϴ ҽ ϰų PostreSQLƮ 湮Ͽ ϸ Ʈ ϸ ڼ ִ.

 

3. PostgreSQL ܺ ɾ

    PostgreSQL ͺ̽ ý ܺ Ѵ.
    ̵ /usr/local/pgsql/bin , Ϲ UNIXɾó ִ. ɾ Ϸ /usr/local/pgsql.bin 翬 ˻ ο ϴ ̴. ͺ̽ ִ 밳 Ʈ, ͺ̽ , ý ִ ɼ ִ. postmaster ⺻ ϴ Ʈ 5432 , postmaster ٴ ٸ Ư ٸ Ʈ 쿡 PGPORT ȯ ̿ ° ִ, ƴϸ ࿡ -pɼ Ͽ Ʈ ־ Ѵ. ⿡ Ϲ 5432Ʈ ϴ Ѵ. ϳ 캸 .

    Initbd

    ȸ ̴. PostgreSQL ͺ̽ ý ʱ⿡ ġ ÿ ѹ ̴. ̳ʸ Ű ġѴٸ ̹ ͺ̽ ý Ǿ ̱ ʿ䰡 .

    Cleardbdir

    PostgreSQL ͺ̽ ıϴµ ȴ. õ Ϲ ڴ ʿ䵵 , ؼ ȵȴ. PostgreSQL PostgreSQLͺ̽ ý ٽ ʱȭϱ ͺ̽ Ѵ. postmaster μ ư ʵ Ͽ Ѵ.

    pg_dump,pg_dumpall

    ̵ ͺ̽ ٸ ͺ̽ ٸ ű SQL ũƮ ȴ. PostgreSQL ٸ ͺ̽ ޶. , 帣 ͺ̽ ޶ ׷̵带 Ϸ , ͺ̽ ̵ α׷ Ͽ SQL ũƮ , ο PostgreSQL Ѵ. SQLũƮ ޾Ƴ ݵ Ź pg_dump, pg_dumpallα׷ ؾ Ѵ. ޶ 밳 SQL ణ ޶ ǹǷ dump α׷ Ͽ Ͽٸ Ź PostgreSQL SQLũƮ ۵ ̴. , Ź PostgreSQL ġϱ dumpα׷ Ǯ , dumpα׷ PostgreSQLͺ̽ SQL ޾Ƴ Ѵ. 6.2 6.2.1 ̷ ʰ, ׳ ϸ ȴ.

    Pg_dump ϸ Ư ̺̳ ͺ̽ SQL ޾Ƴ ִ.
    Pg_dumpall ý ü ͺ̽ Ѳ ޾Ƴ Ѵ.
    ͺ̽ dumpϿ Ӱ ġѴٸ ϸ ȴ.

      $ pg_dumpall-o>.out
      $ psql-e templatel<.out

    ̵ dumpα׷ , κ ε, Ŵ ü ó Ѵٴ ˾Ƶ.

    Createdb, destroydb

    ״ ͺ̽ ϰ ϴ ƿƼ̴. Ư ͺ̽ ޺κп ̵ ̸ ͺ̽ ϰų Ѵ. , PostgreSQL ش ڿ ͺ̽ ο Ͽ Ѵ.

      $ createdb test
      $ destroydb test

    ͺ̽ ڰ ͺ̽ Ѵٸ Ÿ ̴.

      $ createdb test
      WARN:user "ddoch" is not allowed to create/destroy databases
      Createdb: database creation failed on test.
      $

    createuser, destroyuser

    ͺ̽ ڸ ߰ϰų ϴ ƿƼ̴. Ϸ PostgreSQL ̰ų ͺ̽ ڸ ߰ ־ Ѵ.

      $createuser linuxer
      $destroyuser linuxer

    ͺ̽ ߰ ڰ ͺ̽ ڸ ߰Ϸ Ѵٸ Ÿ ̴.

      $createuser linuxer
      createuser: ddoch cannot create users.
      $

    ݱ α׷ ũƮ̴. 캸 ˰ psql Ͽ templatel ͺ̽ , Ǿ ϰ ִ. ͺ̽ ڸ ߰ϴ ۾ SQL Ǿ ⺻ ΰ ִٴ ִ.

    pg_version

    ǿ뼺 α׷̴. Pg_version ڸ ־ ϸ ش 丮 PG_VERSION ̶ , .

    Pg_id

    õ ʴ α׷̴. ϸ ڽ شϴ uid Ѵ. /etc/passwd ° ׸ ϴ.

    Postmaster

    postmaster PostgreSQL μ̰, postgres PostgreSQL 鿣 μ̴. ̵ α׷ ũ α׷, postmaster Ű postmaster postgres Ѵ. postgres ٷ , postmaster Ѵ.
    Postmaster frontend backendμ Ѵ.
    ƿ﷯ ޸ Ǯ(test-and-set ʴ 迡)  ҴѴ. postmaster üδ ڿ ȣȭ , μ ⵿Ǿ Ѵ.

      ɾ :
      postmaster [-B n_buffers] [-D data_dir] [-S] [_a systerm]
      [-b backend_pathname] [-d[debug_level]][-n]
                          [-o backend_options][-p port][-s]

    -B n_bufferw

    n_buffers postmaster backend μ Ҵϰ ޸ ̴. ⺻ 64̴.

    -D data_dir

    ͺ̽ 丮 Ʈ Ʈ 丮 Ѵ. 丮 PGDATA ȯ Ѵ. PGDATA Ǿ ʴٸ, Ǵ 丮 $POSTGRESQLHOME/data̴. ȯ ʾҰ, ̷ ɼǵ ʾҴٸ, ð ⺻ 丮 ̴.

    -S

    postmaster μ Ͽ ⵿ÿ ħ(?) ǵ Ѵ.

    ̰ () ttyʹ ڽ μ ׷ ⵿ ȴ. ɼ ɼǰ Բ µ, ǥ° ǥؿ ؼ ޽ ҵDZ ̴.

    -a system

    frontend α׷ postmaster μ 'system' ý Ѵ. 'system' Ͽ ý ȿȭ ų ,'nosystem' Ͽ ý ִ. , ڷ Ͽ Kerberos ϵ Ϸ, '-a kerberos'ó ִ. ġ źϷ, '-a nounauth'ó ִ.

    -b backend_pathname

    backend_pathname, postmaster frontend α׷ û , ȣ PostgreSQL backend డ ü θ̴. ɼ ʾҴٸ, postmaster డ postmaster ȣ 丮 ã ̴.( postmaster ȣ θ ã´ٴ ǹѴ. θ Ҵٸ, "postgres" Ҹ డ ã PATHȯ溯 Ѵ).

    -d [debug_level]

    debug_level ɼ ڴ backend ޽ Ѵ. Debug_level 1̶, postmaster Ʈ ̸, ׿ܿ ƹ ϵ ʴ´. 2 ̻ ̶, ۾ backend μ ϰ ǰ, postmaster backend ȯ μ Ʈ Ѵ. Backend ޽ ʴ´ٸ, ̵ θ postmaster tty Ÿ ̴.

    -n , -s

    -s -n backend , postmaster ൿ ϴ ȴ. ɼ ۵ ʴ . ᰡ ߻ Ϲ , ٸ backend ˸, ޸𸮿  ٽ ʱȭ ϴ ̴. ̷ ϴ , ߸ backend ϱ , ¸ ߸ ֱ ̴.

    -s ɼ ȴٸ, postmaster ٸ backend SIGSTOPñ׳ ߵ ߰ ̴. ñ׳ μ ü Ű ̴. ̷ ϸ, ý α׷Ӵ backendκ ߻ ھ ִ.

    -n ɼ ȴٸ, postmaster ٽ ʱȭ ʴ´.

    -o backend_options

    postgres(unix) backend_options ɼ postmaster ⵿ backend μ ޵ȴ. ɼ ߰ ڰ ٸ ο ȣ Ѵ.

    -p port
    postmaster frontend α׷κ ӿû ޾Ƶ ͳ TCPƮ Ѵ. ⺻ Ʈ 5432, PGPORTȯ溯 Ͽ 󸶵 ִ. ⺻ Ʈ ٸ Ʈ Ѵٸ, ٸ frontend α׷ ڴ, psql libpq α׷ ⵿ , ݵ Ʈ (PGPORT ȯ溯 ɼ Ͽ) ؾѴ.

    Postmaster ų , ϴٸ SIGKILL ʴ .
    ̰ PostgreSQL ׷ ƴϴ. Httpd SIGKILL ؼ httpd ̸ ڽĵ Ƽ ƴٴϰ ǹǷ . SIGTERM ϵ .

      $ps-ax|grep postmaster
      3544 p1 S  0:00grep postmaster
      386? S  0:00/usr/local/pgsql/bin/postmaster-S-B 512-o-F-D/usr/l
      $kill-TERM 386

    ٽ ѹ , postmaster ̱ ؼ SIGKILL (Ǵ ñ׳ ȣ 9) ϰ Ǹ, postmaster ҴǾ ִ ޸𸮿 ý ڿ ϰ ȴ.
    postmaster 5432⺻ Ʈ ħ (?) .

      $postmaster-S

    postmaster 1234Ʈ , ޸ ۸ 4ް(8K*512) Ҵϰ, ũ ij ѱ 鿣 ɼ '-F' ϰ ִ. ͺ̽ 丮 /usr/local/pgsql/data ϰ ִ. psql Ͽ postmaster Ϸ, psql ࿡ -p 1234 ϰų, PGPORT ȯ溯 1234 Ͽ Ѵ.

      $postmaster-S p 1234-B 512-o-F-d/usr/local/pgsql/data

    postgres

      postgres [-B n_buffers] [-D data_directory] [-E] {-F}
      [-P filedes] [-Q] [-S n_buffers] [-e]
      [-d debug_level] [-o output_file] [-s] [dbname]

    postgres ɼǵ postmaster ִ.
    postmaster -o ڴ postgres ޵ȴ.  

    Psql

    ȣ ִ. psql Ʈ α׷̴.
    ߿ ɼ ̴.

    pg_passwd

    pg_passwd PostgreSQL йȣ óϴ ̴.
    $PGDATA/pg_hba.conf (Ϲδ, /usr/local/pgsql/data/pg_hba.conf) ִ. pg_hba.conf ڿ ϰ PostgreSQL ͺ̽  óϴ ̴. pg_hba.conf ߰ 뿡 ̴.

      host        all       127.0.0.1     255.255.255.255     trust

    ̰ ȣƮ ϴ ڸ ϴ ϰ, ͺ̽ Ѵٴ ̴. ڽ Ʈ ʴ ڵ ־ йȣ ɾΰ ʹٰ . ׷ٸ ̰ ٲٸ ȴ.

      host       all       127.0.0.1      255.255.255.255     password passwd

    ⿡ ټ° 'password' 127.0.0.1( ȣƮ) ϴ ڿԴ ȣ ī Ѵٴ ̸̾߱, 'passwd' $PGDATA/passwd (, /usr/local/pgsql/data/passwd) йȣ Ϸ Ѵٴ ̴. , ý ü /etc/passwd йȣ Ϸ ȴٸ, PostgrreSQL ⺻ йȣ Ϸδ $PGDATA/passwd Ѵٴ ̴. pg_passwdα׷ Ͽ PostgreSQL ִ. 丮 passwd.bk ȴ.

      $ cd/usr/local/pgsql/data
      $ pg_passwd passwd
      Username:linuxer
      New password:    (йȣ Է)
      Re-enter new password: (Է)
      $ cat passwd
      linuxer:XJYDbjVQfFbRs  ( ̵ ȣȭ йȣ)
      $

    ȣƮ ϴ linuxer ڴ йȣ Է ͺ̽ .

      $ psqlinux
      Connection to database 'linux' failed.
      Failed to authenticate client as Postgres user 'linuxer' using the default authentication type: be_recvauth:host-based authentication failed
      $

    йȣ ԷϷ psql '-u' ɼ ϸ ȴ.

      $ psql -u linux
      Username: linuxer
      Password:
      Welcome to the POSTGRESQL interactive sql monitor:
      Please read the file COPYRIGHT for copyright terms of POSTGRESQL
      Type? For help on slash commands
      Typeq to quit
      Typeg or terminate with semicolon to execute query
      You are currently connected to the database: linux
      Linux=>

    ȣƮ ̷ йȣ ɾ ʿ ̴. ŷӱ⸸ ϴ. Ư ȣƮ ϴ 쿡 Ȯ ĥ ʿ䰡 ִٸ, pg_passwd ƿƼ ϸ ϴ.

     

4. ͺ̽ : pg_hba.conf

    PostreSQL pg_hba.conf ̴. PostgreSQL ۿ ֵ صδ .

      $ Is -l pg_hba.conf
      -r--------1 postgres postgres    3776Now 11 08:47 pg_hba.conf

    pg_hba.conf Ͽ ù° 'host' ش ڵ Ÿ.
    , ȣƮ Ӱ õ ڵ ִ.
    ι° ͺ̴̽. 'all' ͺ̽ ĪѴ. ° ϴ ȣƮ IP ̴ּ. ûϴ ȣƮ ּҴ pg_hba.conf ׹° ũ Ǿ IPּҿ 񱳵ȴ. 192.168.1.1 192.168.1.2 ȣƮ Ϸ MASK 255.255.255.0, IP ּҴ 192.168.1.0 ϸ ȴ. ټ° Ѵ.
    'trust' ġ Ÿ. 'ident' RFC 1413 ident ݿ ģٴ Ѵ. Ѵ.

      #TYPE    DATABASE    IP_ADDRESS    MASK                    USERAUTH   MAP
      host        all                127.0.0.1         255.255.255.255     trust
      host        all                192.168.1.0      255.255.255.0        ident    sameuser

    ʴ ȣƮ Ϸ ޽ ´.

      $psql-h free.world.co.kr linux
      Connection to database 'linux' failed.
      Failed to authenticate client as Postgres user 'postgres' using the default
      Authentication type : be_recvauth: host-based authentication failed
      $

    psql -h ɼ ȣƮ ̸ ϴ ȴ. , ͺ̽ ý pg_hba.conf ڽ ȣƮ ֵ Ǿ ִٸ, ڽ ýۿ ͺ̽ ִ.

      [ddoch@han ddoch]$psql-h free.world.co.kr mydb
      Welcome to the POSTGRESQL interactive sql monitor:
      Please read the file COPYRIGHT for copyright terms of POSTGRESQL
      type\? For help on slash commands
      typeq to quit
      typeg or terminate with semicolon to execute query
      You are currently connected to the database: mydb
      mydb=>!hostname
      han.world.co.kr
      mydb=>

    psql ڽ ýۿ ǰ, Էϴ Ǵ ȣƮ postmaster ޵Ǿ ٽ ڽ ý ƿ ̴.

    , ̰ telnet ýۿ αϿ psql ϴ Ͱ , ٸ. ͺ̽ Ȱϸ, 溣̽ ϳ ͺ̽ ΰ, Ŭ̾Ʈ Ʈ α׷ óν ۾ ó лŰ üȭ ִ ̴.

 

5. Kerberos ý

    κ ڴ ϸ ̴. , Ȼ ֿ ͸ ٷ 쿡 ̰ε ϴ. ޵Ǵ ü ȣȭǾ ʿ伺 ִ. ̸ PostgreSQL kerberos ý ϰ ִ. PostgreSQL ü Kerberos Ű ʴ´. Kerberos ftp://authena-dist.mit.edu ְ, ڼ ǻ info-kerberos@authena.mit.edu غ ٶ. ϸƮ kerberos@athena.mit.edu ̰, kerberos-request@athena.mit.edu ȴ.
    USENET ׷ comp.protocols.kerberos ̴.
    ̱ ij ̿ܿ ִ Kerberos ȣȭ ڵ ѵǹǷ ϴ .

    ȣ ʹ ͺ̽ Ȱ鿡 ַ ̿ ø PostgreSQL ó IJ ì ϴ α׷ ߾ . ظ ٶ.




top

home...