Hatena::Grouplinux2

Going My Linux Way このページをアンテナに追加 RSSフィード

2010年11月05日(金)

PostgreSQL のメモ

| 00:32 |  PostgreSQL のメモ - Going My Linux Way を含むブックマーク はてなブックマーク -  PostgreSQL のメモ - Going My Linux Way  PostgreSQL のメモ - Going My Linux Way のブックマークコメント

古いメモです。昔 Vine を使っていたころのものです。

データベースバックアップ方法例

(バックアップを取る)

$ /usr/bin/pg_dump dbname >backup.dump

(リストアする)

$ psql [dbname] <backup.dump

(ラージオブジェクトも含めてバックアップ)

$ /usr/bin/pg_dump -b -Fc dbname >output.dump

PL/pgSQL を使用可能にする

$ su -
# su - postgres
(postgres)$ createlang [ -d dbname ] plpgsql

$ createlang [ -d dbname ] -l      (確認)

syslog への出力を有効にする

/var/lib/pgsql/data/postgresql.conf に以下を追加してサーバ再起動する。(元々、コメントアウトされている箇所がある)

syslog = 2    # range 0-2; 0=stdout; 1=both; 2=syslog
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

TCP/IP でのアクセスを 127.0.0.1 のみに限定する

NIC に割り当てられているアドレスからアクセスできないようにするには、/var/lib/pgsql/data/postgresql.conf に以下を追加してサーバ再起動する。(元々、コメントアウトされている箇所がある)

virtual_host = '127.0.0.1'

ユーザにパスワードを付ける

以下の URI が参考になる。

デフォルトパスワードmd5 で格納されるようにするには、 /var/lib/pgsql/data/postgresql.conf に以下を追加してサーバ再起動する。(元々、コメントアウトされている箇所がある)

password_encryption = true

ユーザにパスワードを付けるには以下の SQL文を実行する

ALTER USER username WITH [ENCRYPTED] PASSWORD 'password' (実際は 1行)

パスワード認証を要求するには /var/lib/pgsql/data/pg_hba.conf を以下のように変更する。

#host  all   all    127.0.0.1   255.255.255.255   trust
host  all   all    127.0.0.1   255.255.255.255   md5

パスワード認証するユーザを限定するには、ユーザ名を 1 行づつに記述したテキストファイルを用意し(ここでは user.list とする) /var/lib/pgsql/data/pg_hba.conf を以下のように変更する。

#host  all   all    127.0.0.1   255.255.255.255   md5
host  all   all    127.0.0.1   255.255.255.255   md5 user.list

SSL を使用する

鍵ファイルを /var/lib/pgsql/data/server.key (暗号化は解除する)、証明書を /var/lib/pgsql/data/server.crt として作成して /var/lib/pgsql/data/postgresql.conf に以下を追加してサーバ再起動する。(元々、コメントアウトされている箇所がある)

ssl = true

SSL 接続だけに限定するには /var/lib/pgsql/data/pg_hba.conf を以下のように変更する。

#host  all   all    127.0.0.1   255.255.255.255   md5 user.list
hostssl  all   all    127.0.0.1   255.255.255.255   md5 user.list

ラージ・オブジェクト

psql で以下のようにラージ・オブジェクトが使える。スーパーユーザのみ \lo_import の 2番目の引数に説明を付けられるようだ。また、実効ユーザ postgres でインポート/エクスポートが行なわれるので権限に注意すること。

(インポートする)

psql=> \lo_import '/some/where/image.jpg'
lo_import 88644

(リストを見る)

psql=> \lo_list
  ラージオブジェクト
  ID   |     説明
-------+---------------
 88644 |
(1 行)

(エクスポートする)

psql=> \lo_export 88644 '/some/where/another.jpg'
lo_export

(削除する)

psql=> \lo_unlink 88644
lo_unlink 88644

ラージ・オブジェクトを扱う関数 lo_import、lo_export はスーパーユーザのみ使えるようである。スーパーユーザで SECURITY DEFINER オプションを付けてラップ関数を作り、それを使うことで一般ユーザでも関数でラージ・オブジェクトが扱えるようになる。(そのような運用が適切か否かは別)

スーパーユーザ(postgres)で対象データベースpsqlログインする。

$ su -
# su - postgres
# psql dbname

psql 上で関数を作成する。

CREATE FUNCTION import_lo(text)
RETURNS oid
RETURNS NULL ON NULL INPUT
SECURITY DEFINER
AS 'SELECT lo_import($1);'
LANGUAGE 'sql';

CREATE FUNCTION export_lo(oid, text)
RETURNS integer
RETURNS NULL ON NULL INPUT
SECURITY DEFINER
AS 'SELECT lo_export($1,$2);'
LANGUAGE 'sql';

一般ユーザでラージ・オブジェクトインポート/エクスポート関数で行えるようになる。

psql=> SELECT import_lo('/some/where/image.jpg');
 import_lo
-----------
     88647
(1 行)

psql=> SELECT import_lo(88647, '/tmp/another.jpg');
 export_lo
-----------
         1
(1 行)

シーケンスとトリガを使用した例

(定義)

CREATE SEQUENCE member_id;

CREATE TABLE member (
  id    int   NOT NULL DEFAULT nextval('member_id'),
  name  text  NOT NULL,
  photo oid   NOT NULL,

  PRIMARY KEY (id)
);

CREATE FUNCTION insert_member(text, text)
RETURNS integer
RETURNS NULL ON NULL INPUT
AS '
DECLARE
    v_name  ALIAS FOR $1;
    v_photo ALIAS FOR $2;
BEGIN
    INSERT INTO member (name, photo)
        VALUES (v_name, import_lo(v_photo));

    RETURN 1;
END'
LANGUAGE 'plpgsql';

CREATE FUNCTION delete_member_photo()
RETURNS trigger AS '
BEGIN
    PERFORM lo_unlink(OLD.photo);
    RETURN OLD;
END'
LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_delete_member
AFTER DELETE
ON member FOR EACH ROW
EXECUTE PROCEDURE delete_member_photo();

ソースからビルドする場合

Vine Linux のパッケージを使用せずに、ソースからビルドする場合にすること。(このメモはもう古いかも)

ビルドインストールクラスタの作成

$ tar zxvf postgresql-7.x.x.tar.gz
$ cd postgresql-7.x.x
$ ./configure
$ make all
$ make check
$ su -
# make install
# groupadd postgres
# useradd -g postgres postgres
# mkdir -p /var/lib/pgsql/data
# chown postgres.postgres /var/lib/pgsql/data
# su - postgres
$ initdb --encoding=EUC_JP --no-locale -D /var/lib/pgsql/data

root になり、/etc/ld.so.conf に以下の行を追加して ldconfig を実行する。

/usr/local/pgsql/lib

PostgreSQL の起動・停止は ユーザ postgres になって以下を行う。

(起動)

$pg_ctl start -D /var/lib/pgsql/data

(停止)

$pg_ctl stop  -D /var/lib/pgsql/data

ユーザ環境(postgres も含む)では以下を行なっておく。

export PG_HOME=/usr/local/pgsql
export PATH=$PG_HOME/bin:$PATH
export MANPATH=$PG_HOME/man:$MANPATH
export LD_LIBRAY_PATH=$PG_HOME/lib:$LD_LIBRAY_PATH

contrib プログラムビルドは以下のようにする。

$ cd <postgresql-source-dir>/contrib
$ make
$ su
# make install

JDBCビルドは以下のようにする。(J2SDK, Ant が必要 )

$ cd <postgresql-source-dir>/src/interface/jdbc
$ ant

(jars/postgresql.jar が作られる)

JDBC ドライバ

パッケージ popostgresql-jdbc-7.4.8-0vl1 に /usr/share/pgsql/postgresql745.jar が収納されている。この jar ファイルをクラスパスに入れるなどして使用する。

TestJDBC.java (JDBC 設定作業の検証用)

import java.sql.*;

public class TestJDBC {
    public static void main(String[] args) throws Exception
    {
        final String USAGE =    "usage: java TestJDBC <databaseID> <userID> <password>\n" +
                                "  ex.) java TestJDBC testdb username password";
        final String DRIVER = "org.postgresql.Driver";
        final String PROTOCOL = "jdbc:postgresql:";

        String uri, user, password;

        if (args.length == 3) {
            uri = PROTOCOL + args[0];
            user = args[1];
            password = args[2];
        } else {
            throw new IllegalArgumentException("\n" + USAGE);
        }

        Class.forName(DRIVER);

        System.out.println("connect to '" + uri + "' as " + user);
        Connection db = DriverManager.getConnection(uri, user, password);

        db.close();
        System.out.println("O.K.");

        System.exit(0);
    }
}

JDBC の検証(例)

$ su -
# ln -s /usr/share/pgsql/postgresql745.jar $JAVA_HOME/jre/lib/ext/
# exit
$ javac TestJDBC.java

(PostgreSQL はソケット利用可能で稼働中であること)

$ java TestJDBC dbname username password
connect to 'jdbc:postgresql:dbname' as username
O.K.
トラックバック - http://linux2.g.hatena.ne.jp/lnznt/20101105