Fedora Core 2: PostgreSQL

Updated: 2004.10.22

以下の記述の中で、OS上での実際の操作を書く場合があるが、次の規則に 基づくものとする。

    rootとして権限で操作する場合
      # コマンド ...
    postgresユーザの権限で操作する場合
      % コマンド ...

  1. デフォルトではpostgresqlはインストールされていない。
        # rpm -q -a | grep ostgre
        ← 何も表示されない。
    
  2. postgres関係で、利用できるパッケージを調べる。
    # yum list | grep ostgre
    freeradius-postgresql               i386   0.9.3-4                  base
    gnucash-backend-postgres            i386   1.8.9-1                  base
    postgresql                          i386   7.4.2-1                  base
    postgresql-contrib                  i386   7.4.2-1                  base
    postgresql-devel                    i386   7.4.2-1                  base
    postgresql-docs                     i386   7.4.2-1                  base
    postgresql-jdbc                     i386   7.4.2-1                  base
    postgresql-libs                     i386   7.4.2-1                  base
    postgresql-odbc                     i386   7.3-6.1                  base
    postgresql-pl                       i386   7.4.2-1                  base
    postgresql-python                   i386   7.4.2-1                  base
    postgresql-server                   i386   7.4.2-1                  base
    postgresql-tcl                      i386   7.4.2-1                  base
    postgresql-test                     i386   7.4.2-1                  base
    
  3. yumを使ってpostgresql をインストールする。
        # yum install postgresql 
    
  4. これでインストールされるのは、2個のパッケージ。
    [root@nd root]# rpm -q -a | grep postgre
    postgresql-7.4.2-1
    postgresql-libs-7.4.2-1
    
  5. 必要なファイルが足りない。たとえばinitdbがない
  6. 開発環境、その他をインストールする。
        # yum install postgresql-devel 
        # yum install postgresql-server 
        # yum install postgresql-jdbc 
        # yum install postgresql-docs 
        # yum install postgresql-contrib 
    
  7. postgresql-server パッケージの中に、 /etc/rc.d/init.d/postgresql や /usr/bin/initdb などがあった。 また、postgres というユーザが /etc/passwd に追加された。 /usr/lib/pgsql/ と /var/lib/pgsql/ が出来たが、 データは /var/lib/pgsql/data/ に置くようだ。
  8. /etc/rc.d/init.d/postgresqlを変更する。
     /etc/rc.d/init.d/postgresql の変更点
    *** postgresql.org	Fri Oct 22 23:39:43 2004
    --- postgresql	Fri Oct 22 23:51:33 2004
    ***************
    *** 2,8 ****
      # postgresql	This is the init script for starting up the PostgreSQL
      #		server
      #
    ! # chkconfig: - 85 15
      # description: Starts and stops the PostgreSQL backend daemon that handles \
      #	       all database requests.
      # processname: postmaster
    --- 2,8 ----
      # postgresql	This is the init script for starting up the PostgreSQL
      #		server
      #
    ! # chkconfig: 2345 85 15
      # description: Starts and stops the PostgreSQL backend daemon that handles \
      #	       all database requests.
      # processname: postmaster
    
    
    自動起動するようにシンボリックリンクを張っておく。 すでに停止するリンクが /etc/rc.d/rc?.d/ の下にあるので、 一旦これらを削除しなくてはいけないことに注意すること。
        # chkconfig --del postgresql
        # chkconfig --add postgresql
    
  9. postgresql サーバを動作させるユーザは postgres で、 これはパッケージをインストールした時点で作成されている。 データベースの操作は postgres に su して行う。
        rootでloginした状態で
        # su postgres
        bash-2.05b$ initdb -D /var/lib/pgsql/data
    文字コードを明示的に指定した方がいいかも ?
        bash-2.05b$ initdb -E UNICODE -D /var/lib/pgsql/data
    
  10. サーバの起動は/etc/rc.d/init.d/postgresql を使って(root権限で)起動する。
        # /etc/rc.d/init.d/postgresql start
    
    もし、直接コマンドを指定してpostgresqlサーバを立ち上げたい ときは以下のようにすればいいらしい。
        /usr/bin/postmaster -D /var/lib/pgsql/data
    or
        /usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
    
    が、これらのコマンド (postmaster, pg_ctl)を直接指定して 起動する機会は(あまり)無いと思われる。
  11. データベースを見ると、文字コードはきちんとUNICODEになっている。 データベースの操作は postgres というユーザの権限で行うことに注意すること。
    [root@nd data]# su postgres
    bash-2.05b$ psql -l
            List of databases
       Name    |  Owner   | Encoding
    -----------+----------+----------
     template0 | postgres | UNICODE
     template1 | postgres | UNICODE
    (2 rows)
    
    
    
    
    
  12. postgresにパスワードを設定しておく。 root権限で以下の操作を行うこと。
        # passwd postgres
        NEW UNIX password: ←入力する(エコーバックされない)
        Retype new UNIX password: ←入力する(エコーバックされない)
    
  13. TCPアクセスを許可する。 postmasterの起動時に -i オプションがつくようにすべき。

    /etc/rc.d/init.d/postgresql を動作させると、いつのまにか /var/lib/pgsql/data/postmaster.opts が存在していた。 これに -i をつけるべし。

    駄目だ。postgresqlを再起動するたびに postmaster.optsは 新しく生成されている。したがって、これを作る元のファイルを 変更する必要がある。 /etc/rc.d/init.d/postgresql の中を見ると、bourne shell script が /etc/sysconfig/pgsql/postgresql を実行して、PGOPTSを 参照している。したkがって、/etc/sysconfig/pgsql/postgresqlの中で と記述すればいいようだ。→その通りだった。確認した。

     /etc/sysconfig/pgsql/postgresql
    PGOPTS='-i'
    
    

     /var/lib/pgsql/data/postmaster.opts の変更点
    *** postmaster.opts.org	Tue Oct 26 10:22:11 2004
    --- postmaster.opts	Tue Oct 26 10:22:11 2004
    ***************
    *** 1 ****
    ! /usr/bin/postmaster '-p' '5432' '-D' '/var/lib/pgsql/data'
    --- 1 ----
    ! /usr/bin/postmaster '-i' '-p' '5432' '-D' '/var/lib/pgsql/data'
    
    
    postgresqlを再起動して置く。
        # /etc/rc.d/init.d/postgresql restart
    
  14. 外部のマシンからアクセスするので、ファイアフォールが5432番ポートへの 接続を通すように設定を変更する。
     /etc/sysconfig/iptables の変更点
    *** iptables.org	Tue Oct 26 10:39:33 2004
    --- iptables	Tue Oct 26 10:39:33 2004
    ***************
    *** 12,17 ****
    --- 12,18 ----
      -A RH-Firewall-1-INPUT -p 50 -j ACCEPT
      -A RH-Firewall-1-INPUT -p 51 -j ACCEPT
      -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
    + -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
      -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 443 -j ACCEPT
      -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 25 -j ACCEPT
      -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
    
    
    設定を変更した後は、iptables を起動しなおしておく。
        # /etc/rc.d/init.d/iptables restart
    
  15. データベースクラスタの中にデータベース wiki を作成する。
    % createdb データベース名
    [実際の操作]
    % createdb wiki  ←作成
    % psql -l   ← 確認
           List of databases
       Name    |  Owner   | Encoding
    -----------+----------+----------
     template0 | postgres | UNICODE
     template1 | postgres | UNICODE
     wiki      | postgres | UNICODE
    (3 rows)
    

    ちなみにデータベースを削除する場合は以下のコマンドを使う。

        % dropdb データベース名
    
  16. データベースwiki内にtableを作る。
        % psql wiki
        wiki=#  create table word (id serial,
    	name text,
    	type text,
    	owner text,
    	subject text,
    	ctime timestamp,
    	wtime timestamp,
    	data text,
    	bdata bytea);
    
    -bash-2.05b$ psql wiki
    wiki=# create table word (id serial,
    wiki(# name text, type text, owner text, subject text, ctime timestamp,
    wiki(# wtime timestamp, data text, bdata bytea);
    NOTICE:  CREATE TABLE will create implicit sequence "word_id_seq" for "serial" column "word.id"
    CREATE TABLE
    
    wiki=# \d
                 List of relations
     Schema |    Name     |   Type   |  Owner
    --------+-------------+----------+----------
     public | word        | table    | postgres
     public | word_id_seq | sequence | postgres
    (2 rows)
    
    wiki=# \dt
            List of relations
     Schema | Name | Type  |  Owner
    --------+------+-------+----------
     public | word | table | postgres
    (1 row)
    
    wiki=# \q
    
  17. データベースユーザを作成する。
    1. データベースユーザとは
    2. どの「データベースクラスタ」も、「データベースユーザ」の集合を含む。 この「データベースユーザ」というのは、「OS (linux)におけるユーザ」 とは別物であることに注意。

      「データベースユーザ」が「データベースオブジェクト」(たとえばtable) を所有しており、どのユーザがどのオブジェクトにアクセスできるか制御できる。

      初期化されたばかりのデータベースシステムでは 「最初から定義済みのユーザ」が1人いる。 その名前は「そのデータベースクラスタを初期化したOSのユーザ名と同じ」 であり、つまり postgres というデータベースユーザである。

      データベースのクライアントプログラム(たとえば psqlなど)は、 特別に指定しない限り、「そのコマンドを起動したOSのユーザ名」 を「データベースユーザ名」として仮定する習慣がある。 そのため、どちらも postgres という名前であると便利なのだ。

    3. データベースユーザの作成
    4. データベースユーザを作成したり、削除したりするには、postgresの権限で コマンドラインから次のコマンドを使う。

       
          % createuser ユーザ名
          % dropuser ユーザ名
      [実際の操作]
      % createuser wiki
      Shall the new user be allowed to create databases? (y/n) n
      Shall the new user be allowed to create more new users? (y/n) n
      CREATE USER
      
    5. データベースユーザにパスワードを設定する。
    6. コマンドラインから「パスワード付きでユーザを生成する」方法がわからない。 SQLにおいては以下のようにすればいいが。

          % psql データベース名
          psql> CREATE USER 名前 WITH ENCRYPTED PASSWORD パスワード;
      [実際の操作]
          % psql wiki
          psql> CREATE USER wiki WITH ENCRYPTED PASSWORD XXX;
      

      パスワードを設定または変更する場合は sql のALTER USERコマンドを使う。

          % psql データベース名
          psql> ALTER USER ユーザ名 ENCRYPTED PASSWORD 'パスワード'
      [実際の操作]
          % psql wiki
          psql> ALTER USER wiki ENCRYPTED PASSWORD 'XXX'
      
      パスワードは /var/lib/pgsql/global/pg_pwdに暗号化されて記憶される。

    7. データベースユーザにテーブルを操作する権限を与える。
    8.     GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE テーブル名 TO ユーザ名;
      [実際の操作]
      % psql wiki
      wiki=# GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE word TO wiki;
      GRANT
      wiki=# \z word      ←アクセス権限の確認
                       Access privileges for database "wiki"
       Schema | Table |                   Access privileges
      --------+-------+-------------------------------------------------------
       public | word  | {postgres=a*r*w*d*R*x*t*/postgres,wiki=arwd/postgres}
      (1 row)
      
  18. wikiというデータベースにネットワーク越しにアクセスできるように 「クライアント認証」の設定を行う。 「データベースユーザ」 wiki が パスワード XXX (内緒) を用いて アクセスできるように /var/lib/pgsql/data/pg_hba.confファイルを変更する。
     /var/lib/pgsql/data/pg_hba.conf の変更点
    
    
    postmasterプロセスにSIGHUP を送っておく。
        % kill -s SIGHUP `head -1 /var/lib/pgsql/postmaster.pid`
    または
        % pg_ctl reload
    
  19. javaのプログラムを使って、他のマシンからアクセスしてみる。
    1. http://jdbc.postgresql.org から JAR ファイル をダウンロードする。 いろいろなバージョンが用意されているが、Java1.4.Xを使っているので JDBC3を、 PostgreSQLが 7.4.X なので pg74.215.jdbc3.jar を撰ぶ。
    2. $(JAVA)/jre/lib/extの下に置く。
          Windowsの場合: C:/j2sdk1.4.2_02/jre/lib/ext
          Linuxの場合:   /usr/local/j2sdk1.4.2_05/jre/lib/ext
      
    3. サンプルプログラム TestSQL.java を動作させて、正しく通信できることを確認する。
    4.     % javac Database.java
          % java Database
          sql> select * from word;
          ...
          sql> quit
      
  20. 管理ツールをインストールしておくと便利らしい。