Postgres

- Windows 版 PosgreSQL 8.0 リリースノートやプレスリリース、ニュース

- Windows 版 PosgreSQL 8.0 ダウンロード

- Windows 版 PosgreSQL 8.0 インストール

□ Write detailed installation log to postgresql-8.0.log in the current directory

- インストールするコンポーネントの選択

The PostgreSQL .NET Driver.
This feature requires 3244KB on your hard drive.

- Service configuration 画面での入力項目

The service account is the account that runs the PostgreSQL database server. It must NOT be a member of the local administrators group. If you have not already created an account, the installer can do so for you. Enter an account name and a password, or leave the password blank to have one auto-generated.

サービスアカウントは postgres の データベースサーバを実行するアカウントです。サービスアカウントはローカルの Administrators グループのメンバを指定してはいけません。もしアカウントを作っていないなら、インストーラはアカウントを作成することができます。アカウント名とパスワードを入力してください。パスワード欄を空欄にしておくとパスワードを自動生成します。

The password you specified appears weak. Would you like the installer to replace it with a random password?

Service name

Account name

Account domain

Account password

Verify password

User 'develop\postgres' was not found. Would you like the account to be created for you?

- Initialize database cluster データベースクラスタの初期化

□ Accept connections on all addreses, not just localhost

This is the internal database username, and not the service account. For security reasons, the password should NOT be the same as the service account.

- Select procedural languages to enable in the default database ストアドや拡張用言語の選択

Select procedural languages to enable in the default database

□ PL/pgsql
□ PL/perl
□ PL/perl (untrusted)

- Enable contrib modules その他寄贈されたモジュール

Enable contrib modules

Contrib modules provide additional, often specialised, functionality. Select those you wish to install in the default template database. All files will be installed so modules may be added later simply by executing the appropriate SQL script.

□ B-Tree GiST
□ Chkpass
□ Cube
□ DBlink
□ DBsize
□ Earth Distance
□ Fuzzy String Match
□ Integer Aggregator
□ Integer Array
□ ISBN and ISSN
□ Large Objects (lo)
□ L-Tree
□ Misc. Utilities
□ No Update
□ Trigram Matching
□ pgAdmin Support
□ Crypto. Functions
□ PGStatTuple
□ R-Tree GiST
□ SEG
□ AutoInc
□ Insert Username
□ ModDateTime
□ RefInt
□ Time Travel
□ String IO
□ Table Functions
□ TSearch2
□ User Lock

Deprecated modules:
□ Full Text Index
□ TSearch

- PostgreSQL is now ready to be installed インストール準備完了

PostgreSQL is now ready to be installed. Click Next to complete the installation.

- Installation complete! インストール完了

Installation complete!

Congratulations, PostgreSQL has been successfully installed on your system.
We recommend that you subscribe to the pgsql-announce mailinglist to receive information about new releases and bugfixes.
Click the finish button to exit from the installer.

- タスクマネージャを見るといくつか postgres 関連のプロセスが動いている

- PostgreSQL 8.0 のインストールは実に簡単

- ナチュラルキー

- サロゲートキー

- なぜサロゲートキーを使う方が良いのか

Web+DB press (Vol.21)


発売日: 2004/06


amazon で詳しく見る   bk1で詳しく見る

- システム側で振り出したキーを使えと教えられたなあ

- 参考

- SQL Server2000 の場合

時刻だけまたは日付だけを個別に格納するデータ型はありません。datetime 型または smalldatetime 型の値を設定する場合、時刻だけを指定すると、日付のデフォルト "January 1, 1900" が使用されます。日付だけを指定すると、時刻のデフォルト "12:00AM (深夜)" が使用されます。

- PostgreSQL 7.2.1 の場合。

- CREATE TABLE

CREATE TABLE time_where_test (utime datetime);

CREATE TABLE time_where_test (utime timestamp);

- INSERT 時の展開

hiroaki=> INSERT INTO time_where_test VALUES ('2004-06-18');
INSERT 93862442 1
hiroaki=> SELECT * FROM time_where_test;
        utime
---------------------
2004-06-18 00:00:00
(1 row)

- SELECT 時の展開

INSERT INTO time_where_test VALUES ('2004-08-09 02:00:00');
INSERT INTO time_where_test VALUES ('2004-08-20 03:00:00');
INSERT INTO time_where_test VALUES ('2004-08-26 00:00:00');
INSERT INTO time_where_test VALUES ('2004-08-26 04:00:00');
INSERT INTO time_where_test VALUES ('2004-08-26 23:59:59');
INSERT INTO time_where_test VALUES ('2004-08-27 00:00:00');
INSERT INTO time_where_test VALUES ('2004-08-27 09:00:00');
INSERT INTO time_where_test VALUES ('2004-08-27 23:59:59');

SELECT * FROM time_where_test WHERE utime < '2004-08-26' ORDER BY utime;

    utime
------------------------------------------------------
2004-08-09 02:00:00.000
2004-08-20 03:00:00.000

(2 件処理されました)

SELECT * FROM time_where_test WHERE utime <= '2004-08-26' ORDER BY utime;

utime
------------------------------------------------------
2004-08-09 02:00:00.000
2004-08-20 03:00:00.000
2004-08-26 00:00:00.000

(3 件処理されました)

> >>ずっと将来構想に入っている WAL からの復元機能と合わせて、期待ですね。
> >
> > これが次の7.5(8/1からベータテスト開始予定)にほぼ間違いなく入りそうです．
> >
>
> 情報ありがとうございます。
> この機能が最も導入時に問題視されているので
> 大変な貴重な情報です。
> 楽しみに待っております！
> 7.5はいつ頃リリースされそうでしょうか？

早ければ年内という話もありますが，個人的には結構今回はベータが長引くの
ではないかと思っています．というのも，PITR以外にも，

NT(Nested Transaction)
table space
Win32ネィティブ対応

といった大物が7.5には含まれており，しかもその変更がデータベースエンジ
ンの心臓部に渡っているからです．

# 後，あんまり表には出ない改良点ですが，background writer processとか，
# ARCによるバッファ管理とかも，実際にはかなりクリティカルな変更です．

- 2004/08/12 追記

標題の件ですが、TRUNCATEを実行する権限を
テーブル所有者以外に与える方法はないでしょうか。

GRANT ALL ON [テーブル名] TO [与えたいユーザー ]
ではTRUNCATE実行時に"所有権がない"旨のエラーがでます。

現在はやむを得ずDELETE文を使用していますが
ロールバックが必要になる処理ではないので、
可能であればTRUNCATEを使いたいと思っています。

PostgreSQL 7.0.3 on Vine LINUX 2.1.5

hiroaki=> TRUNCATE TABLE the_world;
ERROR:  you do not own class "the_world"

hiroaki=> DELETE FROM the_world ;
DELETE 5

PC UNIXユーザのためのPostgreSQL完全攻略ガイド―RDBMSの王道をひた走る強力フリーデータベース

石井 達夫
発売日: 2004/06


amazon で詳しく見る   bk1で詳しく見る

PostgreSQL 7.4.2対応ですので，7.4の機能についてあれこれ書いた結果，特
に3章が膨らんでしまいました．また，5章もチューニングなどで量が増えてし
まいました．そこで第4章のサンプルをダイエットしました．取り上げた言語
はPHPとPerlのみ，どちらもシンプルな共通のデータベースを使ったWenアプリ
ケーションです．PHPの方は，PHP+PEAR+Smartyという今流行のコンビ．Perlの
方は，DBI+DBD-PgにHTML::Templateを組み合わせています．この結果は，トー
タルでなんとか418ページに収まりました．

発売は6/28です．それと，なんと今回シーラカンスのフィギュアが限定でおま
けについています:-)企画的には大変だったと思いますが，ご尽力いただいた
技術評論社の関係の方々に感謝しています．

フィギュアのもらい方:-)その他詳細については，

http://www2b.biglobe.ne.jp/~caco/fourth_edition/index.html

以下から予約や購入をすると，限定シーラカンスフィギュアがもらえる特典が付いています

- Postgres 接続用ライブラリはいずこに?

- インストール

ruby extconf.rb --with-pgsql-include-dir=/usr/local/pgsql/include --with-pgsql-lib-dir=/usr/local/pgsql/lib

- ruby から接続できるかテスト

$ psql -c 'create table test1 (num int);'

$ for i in `seq 1 100`; do psql -c "INSERT INTO test1 (num) VALUES ($i);"; done

$ ruby -e 'require "postgres"; conn = PGconn.connect("localhost", 5432, "", "", "hiroaki"); res = conn.exec("select * from test1;"); p res.num_tuples;'
100

- 今回作業した環境

$ ruby -v
ruby 1.8.1 (2003-12-25) [i586-linux]

$ psql -V
psql (PostgreSQL) 7.2.1

- 実験

ERROR:  Can't serialize access due to concurrent update

select mas_no from g_master order by mas_no desc offset 0 limit 1;
Limit  (cost=0.00..3.24 rows=1 width=4)
  ->  Index Scan Backward using g_master_pkey on g_master  (cost=0.00..166442.75 rows=51427 width=4)

- COALESCE リファレンス

6.12.2. COALESCE
COALESCE(value [, ...])
COALESCE 関数は、NULL でない自身の最初の引数を返します。 データを表示の目的で取り出す際、NULL 値の代わりにデフォルト値を使う場合、時として便利なことがあります。 その例です。

SELECT COALESCE(description, short_description, '(none)') ...

- COALESCE のスペルが覚えられない

bigdbというデータベースの掃除とオブティマイザ用の解析を行なう場合には下記のコマンドを実行します。
$ vacuumdb --analyze bigdb

UPDATE pg_shadow SET usesuper = true WHERE usename = 'hiroaki';

psql -l -P format=unaligned -P fieldsep=, -P tuples_only |perl -F, -lane "if (@F[0] ne 'template0') {print @F[0]}"

#/bin/sh

# PostgreSQL database backup script
# Version 1.1 [Apr. 17, 2002] Add routine: deleting exipired files.
# Version 1.0 [Sep. 10, 2002] First release.
#  Saito Hiroaki  <example@example.com>

# * Feature
# Dump to DATABASE_NAME_YYMMDD.pgdmp.
# Delete expired pgdmp file.
# Output log.

# Setting
PATH="$PATH":/usr/local/pgsql/bin
PG=/usr/local/pgsql
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH":$PG/lib
export PGLIB=$PG/lib
export PGDATA=$PG/data

# Get all DB name
TARGET_DB=`psql -l -P format=unaligned -P fieldsep=, -P tuples_only |perl -F, -lane "if (@F[0] ne 'template0') {print @F[0]}"`

DIR=/home/postgres
LOG=$DIR/pgdump.log
BACKUPDIR=$DIR/dbbackup
EXPIRE_DAY=90

# Main routine
echo "pg_dump start." `date` >>$LOG
date=`date +%Y%m%d`

for db_name in $TARGET_DB
do
  DUMP_FILE_PATH=$BACKUPDIR/${db_name}_${date}.pgdmp
  /usr/local/pgsql/bin/pg_dump $db_name -b -o -Fc > $DUMP_FILE_PATH 2>>$LOG
  echo "Database $db_name backup complete." `date` >>$LOG
done

echo "pg_dump complete." `date` >>$LOG

echo "Delete expired pgdmp file start." `date` >>$LOG
find $BACKUPDIR -type f -daystart -mtime +$EXPIRE_DAY |xargs /bin/rm -f
echo "Delete expired pgdmp file complete." `date` >>$LOG

-U, --username=NAME      connect as specified database user
-W, --password          force password prompt (should happen automatically)

- VARCHAR(n) の文字数とは、SQL92 の定義に則れば、

- しかし、7.2 未満の Postgres は上記仕様を満たしていなかった。

もともとPostgreSQLのマルチバイト実装におい
ては，『処理系の定義する特定の文字集合』とは，データベースごとに決まる
エンコーディングです．日本のユーザならたいていEUC_JPでしょう．

- VARCHAR(n) の文字数とは直接の関係はないが、知っておくべき情報もいくつかあった。

- CHAR 型 はスペースでパディングされるために結構コストが高い。

char型の場合、たとえばCHAR(10)で確保した列に100万行を挿入す
れば、その列にどんな長さの文字列を挿入しようとも、きっちり10
バイト×行数（この場合には1000万バイト）の領域が必要になりま
す。しかし、varchar型やtext型の場合には、実際に挿入した文字列
の長さ+4バイトしか使いません。ですから、長さがまちまちである
文字列を保持するのであれば、varchar型やtext型の方がリーズナブ
ルなわけです。

- CAHR と VARCHAR の違いと文字列長超過時のエラー

SQL は 2 つの主要な文字データ型を定義しています。
character(n) と character varying(n) で、n は正の整数です。
これらのデータ型は 2 つとも長さにして n 文字分の文字列を保存できます。
超過している文字がすべてスペースの場合（この時は長さの限界で切り捨てられます）を除いて、
上限を越えた文字列をこの種の列に保存しようとするとエラーになります。
（この一風変わった例外は SQL 標準で要求されています。）
もし宣言された上限よりも文字列が短いときは character の値はスペースでパッドされ、
character varying の値は単により短く保存されます。
Note: PostgreSQL 7.2 以前では上限を越えた文字列は黙って切り捨てられエラーとはなりません。

PL/PGSQL で Perl の join 関数みたいなストアドを作りたいんだけど、
なんかもっとスマートな方法無いでしょうか?
結果レコードのカラム名を変数で指定できると嬉しいんだけど、
Postgres 7.2.1 では変数展開してくれませんでした。

-- SQL の結果レコードに指定した文字列を挟んで連結して返す
-- 引数1: 実行する SQL 文
-- 引数2: 挟み込む文字列
DROP FUNCTION join_str(TEXT, TEXT);
CREATE FUNCTION join_str(TEXT, TEXT) RETURNS TEXT AS '
  declare
    delimiter alias for $1;
    sql alias for $2;
    str text;
    rec record;
    is_first bool;
  begin
    is_first = true;
    str := '''';
    FOR rec IN EXECUTE sql LOOP
      IF is_first THEN
        str := rec.serialnum;  -- カラム名を指定
        is_first := false;
      ELSE
        str := str || delimiter || rec.serialnum; -- カラム名を指定
      END IF;
    END LOOP;
    return str;
  end;
' LANGUAGE 'plpgsql';

PG=`pwd`
export PGPORT=10732
export PGLIB=$PG/lib
export PGDATA=$PG/data
export LD_LIBRARY_PATH=$PG/lib
PATH="$PG/bin":$PATH

PC UNIXユーザのためのPostgreSQL完全攻略ガイド

石井 達夫
発売日: 2001/06


amazon で詳しく見る   bk1で詳しく見る

- 参考

- 誕生日からの経過日数を表示する。

SELECT date_trunc('day', CURRENT_TIMESTAMP - '1977-11-07');

- pg_dump するときに -b オプションを付けなければ、

- SQL の結果を出力したいとき

\pset format unaligned -- 出力形式を指定
\pset fieldsep ',' -- フィールドのセパレータにカンマ区切りを指定
\o psql_out.csv --  出力ファイルを指定
ここに SQL を書く

$ psql -c 'SELECT user_id, user_name FROM users ORDER BY user_id;' -A -F,

- テーブルをまるごと出力したいとき。

COPY system_name TO '/tmp/system_name_tabale.txt' DELIMITERS ',';

- 参考

$ psql --help
This is psql, the PostgreSQL interactive terminal.

Usage:
  psql [options] [dbname [username]]

Options:
  -a              Echo all input from script
  -A              Unaligned table output mode (-P format=unaligned)
  -c COMMAND      Run only single command (SQL or internal) and exit
  -d DBNAME      Specify database name to connect to (default: tka)
  -e              Echo commands sent to server
  -E              Display queries that internal commands generate
  -f FILENAME    Execute commands from file, then exit
  -F STRING      Set field separator (default: "|") (-P fieldsep=)
  -h HOSTNAME    Specify database server host (default: local socket)
  -H              HTML table output mode (-P format=html)
  -l              List available databases, then exit
  -n              Disable enhanced command line editing (readline)
  -o FILENAME    Send query results to file (or |pipe)
  -p PORT        Specify database server port (default: 5432)
  -P VAR[=ARG]    Set printing option 'VAR' to 'ARG' (see \pset command)
  -q              Run quietly (no messages, only query output)
  -R STRING      Set record separator (default: newline) (-P recordsep=)
  -s              Single step mode (confirm each query)
  -S              Single line mode (end of line terminates SQL command)
  -t              Print rows only (-P tuples_only)
  -T TEXT        Set HTML table tag attributes (width, border) (-P tableattr=)
  -U NAME        Specify database user name (default: tka)
  -v NAME=VALUE  Set psql variable 'NAME' to 'VALUE'
  -V              Show version information and exit
  -W              Prompt for password (should happen automatically)
  -x              Turn on expanded table output (-P expanded)
  -X              Do not read startup file (~/.psqlrc)

For more information, type "\?" (for internal commands) or "\help"
(for SQL commands) from within psql, or consult the psql section in
the PostgreSQL documentation.

Report bugs to <pgsql-bugs@postgresql.org>.

- fnumber を使ってカラム名を添字に変換する

print $result->getvalue(0, $result->fnumber('serialnum'));

- 無名ハッシュの配列にマッピングする。

my @sql_result = get_sql_result($result);
print $sql_result[0]->{'ao_cd'};

# SQL の実行結果を結果をハッシュの配列にして返す
sub get_sql_result($) {
  my $result = $_[0];
  my $record_nums = $result->ntuples;
  my $field_nums = $result->nfields;

  my @result_records = ();
  for (my $i = 0; $i < $record_nums; $i++) {
    my $buf = {};
    for (my $j = 0; $j < $field_nums; $j++) {
      my $field_name = $result->fname($j);
      $buf->{"$field_name"} = $result->getvalue($i, $j);
    }
    push(@result_records, $buf);
  }

  return @result_records;
}

# SELECT date_trunc('month', CURRENT_TIMESTAMP + '-2 month');
      date_trunc
------------------------
2002-10-01 00:00:00+09
(1 row)

# SELECT to_date(now() + '-7day', 'YYYY-MM-DD');
  to_date
------------
2002-11-26
(1 row)

> pg_dump dbname > db.out でバックアップファイルを作成
>
> db.outをviにて置き換え。
> :%s/timestamp wuthout time zone/timestamp(0) with time zone/g

参考までにもう一つ。
7.2では単にtimestampといえ（記述すれ）ばtimestamp with time zone
でしたが、7.3からはtimestamp without time zoneになります。これから
はwith/withoutを明記しないと混乱してしまうでしょうね。

> これら、PostgreSQLを利用したいという人の目的の多くはこれなんじゃないか？と
> 思ってます。
> インストール時のオプションはこれだけで良かったです。
>
> ./configure --enable-multibyte=EUC_JP

--enable-syslog も付けておけば，障害が発生したときにログが取れるように
なります，というか実運用ではログの取得は必須です．

ちなみに，7.3では --enable-multibyte --enable-syslog もデフォルトでオ
ンになっているので，

/configure

だけでOKです．ただし，デフォルトエンコーディングが SQL_ASCII になって
しまうので，initdb のときに EUC_JP を指定しておくか，createdb で
EUC_JP を指定しなければなりません．それから， --enable-locale までデフォ
ルトでオンになってしまったので，initdb のときに --no-locale を付けて
locale が効かないようにしなければならなくなりました．

- 2003-04-17 追記

- Postgres 自動バックアップスクリプトの使い方

- スクリプト

#/bin/sh

# PostgreSQL database backup script
# Version 1.0
# Sep. 10, 2002
# Saito Hiroaki

# Dump to DATABASE_NAME_YYMMDD.pgdmp.
# Output log.

# Setting
TARGET_DB='account audit' # List target database name.

DIR=/home/postgres
LOG=$DIR/pgdump.log
BACKUPDIR=$DIR/dbbackup

# Main routine
echo "pg_dump start." `date` >>$LOG
date=`date +%Y%m%d`

for db_name in $TARGET_DB
do
  DUMP_FILE_PATH=$BACKUPDIR/${db_name}_${date}.pgdmp
  /usr/local/pgsql/bin/pg_dump $db_name -b -o -Fc > $DUMP_FILE_PATH 2>>$LOG
  echo "Database $db_name backup complete." `date` >>$LOG
done

echo "pg_dump complete." `date` >>$LOG

- ユーザ postgres のホームディレクトリ

- 上記スクリプトを、夜間バッチとして cron で毎日実行することにした。

0 6 * * * nice -19 /home/postgres/pgd.sh

- pg_dump でバックアップ

$ pg_dump account -b -o -Fc > account_all_dump_data_20020806.pgdmp
$ pg_dump audit -b -o -Fc > audit_all_dump_data_20020806.pgdmp

- pg_dump コマンドのオプションの意味

- pg_restore でリストア

$ pg_restore -d account ./account_all_dump_data_20020806
$ pg_restore -d audit ./audit_all_dump_data_20020806.pgdmp

- 関連記事

- sonic で pgbench

[postgres@pro pgbench]$ pgbench postgres
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 9.607025(including connections establishing)
tps = 11.733426(excluding connections establishing)

OS: TurboLinux Server 6.5
CPU: Pentium 133MHz
Memory: 64MB
HDD: 4.3GB
NIC: Intel i82557

- pro で pgbench

[postgres@pro pgbench]$ pgbench postgres
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 20.484373(including connections establishing)
tps = 23.593809(excluding connections establishing)

OS: TurboLinux Server 6.5
CPU: MMX Pentium 200MHz
Memory: 128MB
HDD: 8.4GB
NIC: 3COM 3C905B