トップ «前の日(04-25) 最新 次の日(04-27)» 追記

Masa's blog

検索キーワード:

2013年04月26日 memo for PostgreSQL on CentOS

_ Environment

  • OS : CentOS 6.4 i386
  • PostgreSQL 8.4.13
  • administrator for PostgreSQL : postgres (password: 1qaz2wsx)
  • user for PostgreSQL : m-ito (password: 3edc4rfv)

_ Initialize database

# service postgresql initdb --encoding=UNICODE --no-locale

or if you install postgresql from source

# initdb --encoding=UNICODE --no-locale

_ Start PostgreSQL

# service postgresql start

or if you install postgresql from source

# pg_ctl start -D /usr/local/pgsql/data

_ Stop PostgreSQL

# service postgresql stop

or if you install postgresql from source

# pg_ctl stop -D /usr/local/pgsql/data

_ Set init script to start

# chkconfig --level 2345 postgresql on

or if you install postgresql from source

#
# /etc/rc.d/rc.local
#
if [ -x /usr/local/pgsql/bin/postgres ]
then
        su - postgres -c "/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data &"
        echo "Starting postgres."
fi

_ Set password for postgres

# psql -U postgres
postgres=# alter role postgres with password '1qaz2wsx';
ALTER ROLE
postgres=# \q
#
# vi /var/lib/pgsql/data/pg_hba.conf
#TYPE DATABASE USER IP-ADDRESS   METHOD
local all      all               password
host  all      all  127.0.0.1/32 password
host  all      all  ::1/128      password
# service postgresql restart

_ Create user

# createuser -U postgres m-ito
新しいロールをスーパーユーザとしますか?  (y/n)n
新しいロールにデータベース作成権限を与えますか?  (y/n)y
新しいロールにロールを作成する権限を与えますか?  (y/n)y
パスワード: 1qaz2wsx

_ Set password for m-ito

# psql -U postgres
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# alter role "m-ito" with password '3edc4rfv';
ALTER ROLE

_ Create user (another way)

# psql -U postgres
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# create user "m-ito" with createdb createrole password '3edc4rfv';

_ Set another permissions

# psql -U postgres
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# alter role "m-ito" with SUPERUSER;
postgres=# alter role "m-ito" with CREATEDB;
postgres=# alter role "m-ito" with CREATEROLE;
postgres=# alter role "m-ito" with CREATEUSER;
postgres=# alter role "m-ito" with REPLICATION;

_ Create database

# createdb -U postgres -E UNICODE testdb
パスワード : 1qaz2wsx

_ Create database (another way)

# psql -U postgres
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# create database testdb with owner=postgres encoding='UNICODE';

_ Add privilege

# psql -U postgres testdb
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# grant all on TABLE to "m-ito";
GRANT

_ Del privilege

# psql -U postgres testdb
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# revoke all on TABLE from "m-ito";
REVOKE

_ Show database list

# psql -U posgres -l
ユーザ postgres のパスワード: 1qaz2wsx

_ Show database list(another way)

# psql -U postgres
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# \l

_ Show table list

# psql -U postgres testdb
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# \d

_ Copy table from file(tab separated value)

# psql -U postgres testdb
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# copy TABLE from '/absolute-path/input.txt';

_ Copy table to file(tab separated value)

# psql -U postgres testdb
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# copy TABLE to '/absolute-path/output.txt';

_ Del user

# psql -U postgres testdb
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# revoke all on TABLE from "m-ito";
REVOKE
postgres=# drop user "m-ito";
DROP ROLE
postgres=# \q

_ Del user(another way)

# dropuser -U postgres m-ito
パスワード: 1qaz2wsx

_ Del database

# dropdb -U postgres testdb
パスワード: 1qaz2wsx

_ Del database (another way)

# psql -U postgres
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# drop database testdb;

_ User list

# psql -U postgres
ユーザ postgres のパスワード: 1qaz2wsx
postgres=# select * from pg_shadow;

_ Set default password for psql

# PGPASSWORD=1qaz2wsx; export PGPASSWORD
# psql -U postgres
postgres=#

_ Specify field separator for select from psql

# TAB=`echo -e -n "\t"`
# echo "select * from TABLE;" | psql -t -A -F"${TAB}" testdb

_ Specify field separator for select from psql(another way)

# psql -U postgres testdb
testdb=# \a
出力フォーマットは "unaligned" です。
testdb=# \t
タプルのみを表示しています。
testdb=# \pset fieldsep '\t'
フィールド区切り文字は "	" です。
testdb=# select * from TABLE;

_ Online backup (all of testdb)

# PGPASSWORD=1qaz2wsx pg_dump -U postgres testdb >db.sql

_ Online backup (only "TABLE" of testdb)

# PGPASSWORD=1qaz2wsx pg_dump -U postgres -t TABLE testdb >db_TABLE.sql

_ Restore into "newdb" from "db.sql"

# PGPASSWORD=1qaz2wsx psql -U postgres -d newdb -f db.sql

_ Online backup whole database

# PGPASSWORD=1qaz2wsx pg_dumpall -U postgres >wholedb.sql

_ Restore from "wholedb.sql"

First, You have to re-install PostgreSQL from scratch :P and next...

# PGPASSWORD=1qaz2wsx psql -U postgres -f wholedb.sql

_ PITR(Point In Time Recovery)

Setup

Create directories which WAL backup and FULL backup should be taken into.

# mkdir /mnt/server
# mkdir /mnt/server/archivedir
# mkdir /mnt/server/basebackup
# chown -R root.postgres /mnt/server
# chmod -R 770 /mnt/server

It's strongly recommended to locate /mnt/server on another harddisk from harddisk which /var/lib/pgsql is located on.

Backup WAL

WAL is backuped into /mnt/server/archivedir/ every 300 sec.

# vi /var/lib/pgsql/data/postgresql.conf
... snip ...
archive_mode = on
archive_command = 'cp -i %p /mnt/server/archivedir/%f </dev/null'
archive_timeout = 300
... snip ...

Make change effective.

# service postgresql restart
Switch WAL manually
# echo "select pg_switch_xlog();" | PGPASSWORD=1qaz2wsx psql -U postgres

Full backup

Full backup is taken into /mnt/server/basebackup/.

# echo "select pg_start_backup('/mnt/server/basebackup/');" | PGPASSWORD=1qaz2wsx psql -U postgres
# cd /var/lib/ && rsync -a --delete -v pgsql /mnt/server/basebackup
# echo "select pg_stop_backup();" | PGPASSWORD=1qaz2wsx psql -U postgres

After Full backup, you can delete (too) old WAL. (but it's a good idea to keep a little bit old WAL ;P)

# find /mnt/server/archivedir -mtime +2 -exec \{\} \;

Restore

Stop postgresql.

# service postgresql stop

If possible then backup newest WAL into /mnt/server/pg_xlog/. But if you have enough media space and time for backup, it's more safe idea to do Full backup into unordinary place for Full backup.

# cd /var/lib/pgsql/data/ && rsync -a --delete -v pg_xlog /mnt/server

Restore from Full backup.

# cd /var/lib/ && rm -fr pgsql/
# cd /mnt/server/basebackup/ && rsync -a --delete -v pgsql /var/lib

If possible then restore newest WAL from /mnt/server/pg_xlog/.

# cd /var/lib/pgsql/data/ && rm -fr pg_xlog/
# cd /mnt/server/ && rsync -a --delete -v pg_xlog /var/lib/pgsql/data

Create configuration file for recovery.

# cd /var/lib/pgsql/data/
# vi recovery.conf
restore_command = 'cp /mnt/server/archivedir/%f "%p"'
recovery_target_time = '2013-04-30 13:05:00 JST'

Restart postgresql with recovering. After recovering, recovery.conf is renamed to 'recovery.done'.

# service postgresql start

Recovering log (/var/lib/pgsql/data/pg_log/postgresqli-Tue.log).

LOG:  アーカイブリカバリを開始しています
LOG:  restore_command = 'cp /mnt/server/archivedir/%f "%p"'
LOG:  recovery_target_time = '2013-04-30 13:05:00+09'
cp: cannot stat `/mnt/server/archivedir/00000001.history': そのようなファイルやディレクトリはありません
LOG:  ログファイル"000000010000000000000001.00000020.backup"をアーカイブからリストアしました
LOG:  ログファイル"000000010000000000000001"をアーカイブからリストアしました
LOG:  自動リカバリを行っています
LOG:  0/1000064 で REDO が開始されました。0/1000080 で同期します。
LOG:  リカバリ状態の整合が取れました
LOG:  ログファイル"000000010000000000000002"をアーカイブからリストアしました
LOG:  ログファイル"000000010000000000000003"をアーカイブからリストアしました
LOG:  リカバリがトランザクション695のコミット、時刻2013-04-30 13:09:42.644071+09の前に停止しました
LOG:  0/30006D4のREDOが終わりました
LOG:  最後に完了したトランザクションはログ時刻2013-04-30 13:04:31.532162+09でした
cp: cannot stat `/mnt/server/archivedir/00000002.history': そのようなファイルやディレクトリはありません
LOG:  選択された新しいタイムラインID: 2
cp: cannot stat `/mnt/server/archivedir/00000001.history': そのようなファイルやディレクトリはありません
LOG:  アーカイブリカバリが完了しました

_ Vacuum

Vacuum make "deleted record" free to shrink database area.

# PGPASSWORD=1qaz2wsx; export PGPASSWORD
# psql -U postgres
postgres=# vacuum;
postgres=# vacuum full;
postgres=# vacuum TABLE;

_ JDBC

/etc/sysconfig/iptables

# vi /etc/sysconfig/iptables
...snip...
-A INPUT -m state -state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
...snip...
# service iptables restart

/var/lib/pgsql/data/postgresql.conf

# vi /var/lib/pgsql/data/postgresql.conf
...snip...
listen_addresses = '*'
...snip...

/var/lib/pgsql/data/pg_hba.conf

Clients are accessing to PostgreSQL(IP=192.168.0.123) from 192.168.0.0/24.

# vi /var/lib/pgsql/data/pg_hba.conf
...snip...
host all all 192.168.0.0/24 password
...snip...
# service restart postgresql

JDBC Driver

postgresql-8.4-703.jdbc4.jar

# cp postgresql-8.4-703.jdbc4.jar /usr/local/javaclass/
# export CLASSPATH="${CLASSPATH}:/usr/local/javaclass/postgresql-8.4-703.jdbc4.jar"

JdbcPostgreSQL.java

//
// 「jdbc PostgreSQL」テスト
//
// compile : javac -encoding utf-8 JdbcPostgreSQL.java
// run     : java JdbcPostgreSQL
//
import java.sql.*;

public class JdbcPostgreSQL {

	static Connection conn = null;
	static Statement stmt = null;
	static PreparedStatement pstmt = null;
	static ResultSet rs = null;
	static ResultSetMetaData rsmd = null;

	public static void main(String[] args) {
		try {
//
// JDBC for PostgreSQLのロード
//
			Class.forName("org.postgresql.Driver").newInstance();
			System.out.println("JDBCドライバをロードしました。");
//
// データベースへの接続
//
			String url = "jdbc:postgresql://192.168.0.123/testdb";
			conn = DriverManager.getConnection(url, "m-ito", "3edc4rfv");
			System.out.println("jdbc:postgresql://192.168.0.123/testdbに接続しました。");
//
// 自動コミット有効
//
			conn.setAutoCommit(true);
			System.out.println("自動コミットをONにしました。");
//
// SQLステートメントオブジェクト生成
//
			stmt = conn.createStatement();
//
// テーブルの削除
//
			try {
				pstmt = conn.prepareStatement("drop table member");
				pstmt.executeUpdate();
				System.out.println("テーブル member を削除しました。");
			} catch(Exception e){
				System.out.println("テーブル member を削除できませんでした(" + e.getMessage() + ")");
			}
//
// テーブルの作成
//
			pstmt = conn.prepareStatement(
				"create table member (" +
					"id INT PRIMARY KEY," +
					"name VARCHAR(50)," +
					"job VARCHAR(50)" +
				")"
			);
			pstmt.executeUpdate();
			System.out.println("テーブル member を作成しました。");
//
// わざと2重にテーブル作成してみる
//
			try {
				stmt.executeUpdate(
					"create table member (" +
						"id INT PRIMARY KEY," +
						"name VARCHAR(50)," +
						"job VARCHAR(50)" +
					")"
				);
				System.out.println("テーブル member を作成しました(2)。");
			} catch(Exception e){
				System.out.println("テーブル member を作成できませんでした(2)(" + e.getMessage() + ")");
			}
//
// 2次インデックス作成
//
			stmt.executeUpdate("create index i_member_name on member(name)");
			System.out.println("インデックスを作成しました。");
//
// 自動コミット無効
//
			conn.setAutoCommit(false);
			System.out.println("自動コミットをOFFにしました(トランザクション開始)。");
//
// タプルの追加(失敗するパターン)
//
			try {
				pstmt = conn.prepareStatement("insert into member (id, name, job) values (?, ?, ?)");
//
// 1件目
//
				pstmt.setInt(1, 1);
				pstmt.setString(2, "伊藤 太郎");
				pstmt.setString(3, "サラリーマン");
				pstmt.executeUpdate();
				System.out.println("インサートしました。");
//
// わざともう一度追加してみる
//
				pstmt.setInt(1, 1);
				pstmt.setString(2, "伊藤 太郎");
				pstmt.setString(3, "サラリーマン");
				pstmt.executeUpdate();
				System.out.println("インサートしました。");
			} catch (Exception e) {
				System.out.println("インサートできませんでした(" + e.getMessage() + ")");
//
// ロールバック(トランザクションの破棄)
//
				try {
					conn.rollback();
					System.out.println("ロールバックしました。");
				} catch(Exception e2){
					System.out.println(e2.getMessage());
				}
			}
//
// タプルの追加(成功するパターン)
//
			try {
				pstmt = conn.prepareStatement("insert into member (id, name, job) values (?, ?, ?)");
//
// 1件目
//
				pstmt.setInt(1, 1);
				pstmt.setString(2, "伊藤 太郎");
				pstmt.setString(3, "サラリーマン");
				pstmt.executeUpdate();
				System.out.println("インサートしました。");
//
// 2件目
//
				pstmt.setInt(1, 2);
				pstmt.setString(2, "山田 太郎");
				pstmt.setString(3, "野球選手");
				pstmt.executeUpdate();
				System.out.println("インサートしました。");
//
// 3件目
//
				pstmt.setInt(1, 3);
				pstmt.setString(2, "山田 花子");
				pstmt.setString(3, "タレント");
				pstmt.executeUpdate();
				System.out.println("インサートしました。");
			} catch (Exception e) {
				System.out.println("インサートできませんでした(" + e.getMessage() + ")");
			}
//
// タプルの検索
//
			try {
//
// クエリの実行
//
				pstmt = conn.prepareStatement("select * from member where id >= ?");
				pstmt.setInt(1, 1);
				rs = pstmt.executeQuery();
//
// 1件目の取り出し
//
				rs.next();
				System.out.println("セレクトしました(id=" +
					rs.getInt("id") + ")(name=" +
					rs.getString("name") + ")(job=" +
					rs.getString("job") + ")。");
//
// 2件目の取り出し
//
				rs.next();
				System.out.println("セレクトしました(id=" +
					rs.getInt("id") + ")(name=" +
					rs.getString("name") + ")(job=" +
					rs.getString("job") + ")。");
//
// 3件目の取り出し
//
				rs.next();
				System.out.println("セレクトしました(id=" +
					rs.getInt("id") + ")(name=" +
					rs.getString("name") + ")(job=" +
					rs.getString("job") + ")。");
//
// わざと存在しない4件目を取り出してみる
//
				rs.next();
				System.out.println("セレクトしました(id=" +
					rs.getInt("id") + ")(name=" +
					rs.getString("name") + ")(job=" +
					rs.getString("job") + ")。");
			} catch (Exception e) {
				System.out.println("セレクトできませんでした(" + e.getMessage() + ")");
			}
//
// タプルの更新
//
			try {
//
// 更新ステートメント実行
//
				pstmt = conn.prepareStatement("update member set job = ? where name = ?");
				pstmt.setString(1, "裁判官");
				pstmt.setString(2, "山田 太郎");
				pstmt.executeUpdate();
				System.out.println("アップデートしました。");
//
// 更新結果を取り出すクエリの実行
//
				pstmt = conn.prepareStatement("select * from member where name = ?");
				pstmt.setString(1, "山田 太郎");
				rs = pstmt.executeQuery();
//
// クエリ結果の取り出し
//
				rs.next();
				System.out.println("アップデート結果(id=" +
					rs.getInt("id") + ")(name=" +
					rs.getString("name") + ")(job=" +
					rs.getString("job") + ")。");
			} catch (Exception e) {
				System.out.println("アップデートできませんでした(" + e.getMessage() + ")");
			}
//
// タプルの削除
//
			try {
//
// 削除ステートメント実行
//
				pstmt = conn.prepareStatement("delete from member where name like ?");
				pstmt.setString(1, "山田%");
				pstmt.executeUpdate();
				System.out.println("デリートしました(" + pstmt.getUpdateCount() + "件)。");
//
// 削除後の状態の検索クエリ実行
//
				pstmt = conn.prepareStatement("select * from member");
				rs = pstmt.executeQuery();
//
// クエリ結果の取り出し
//
				while (rs.next()){
					System.out.println("デリート結果(id=" +
						rs.getInt("id") + ")(name=" +
						rs.getString("name") + ")(job=" +
						rs.getString("job") + ")。");
				}
			} catch (Exception e) {
				System.out.println("デリートできませんでした(" + e.getMessage() + ")");
			}
//
// メタデータの取得
//
			try {
//
// メタデータの取得
//
				rsmd = rs.getMetaData();
//
// カラム数の取得
//
				int numberOfColumns = rsmd.getColumnCount();
				System.out.print("メタデータを取得しました(");
				for (int i = 1; i <= numberOfColumns; i++) {
//
// カラム名称の取得
//
					String name = rsmd.getColumnName (i);
					System.out.print(name + "\t");
				}
				System.out.println(")。");
			} catch (Exception e) {
				System.out.println("メタデータを取得できませんでした(" + e.getMessage() + ")");
			}
//
// コミット(トランザクションの反映)
//
			try {
				conn.commit();
				System.out.println("コミットしました。");
			} catch (Exception e) {
				System.out.println("コミットできませんでした(" + e.getMessage() + ")");
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
//
// ロールバック(トランザクションの破棄)
//
			try {
				conn.rollback();
			} catch(Exception e2){
				System.out.println(e2.getMessage());
			}
		} catch(Exception e){
			System.out.println(e.getMessage());
//
// ロールバック(トランザクションの破棄)
//
			try {
				conn.rollback();
			} catch(Exception e2){
				System.out.println(e2.getMessage());
			}
		}

		try {
//
// 検索クエリの結果セットオブジェクトを破棄
//
			rs.close();
//
// SQLステートメントオブジェクトを破棄
//
			stmt.close();
			pstmt.close();
//
// データベースからの切断
//
			conn.close();
		} catch(Exception e){
			System.out.println(e.getMessage());
		}
		System.out.println("PostgreSQLから切断しました。");

	}
}

Result from running JdbcPostgreSQL

JDBCドライバをロードしました。
jdbc:postgresql://192.168.0.123/testdbに接続しました。
自動コミットをONにしました。
テーブル member を削除しました。
テーブル member を作成しました。
テーブル member を作成できませんでした(2)(ERROR: リレーション"member"はすでに存在します)
インデックスを作成しました。
自動コミットをOFFにしました(トランザクション開始)。
インサートしました。
インサートできませんでした(ERROR: 重複キーは一意性制約"member_pkey"に違反しています)
ロールバックしました。
インサートしました。
インサートしました。
インサートしました。
セレクトしました(id=1)(name=伊藤 太郎)(job=サラリーマン)。
セレクトしました(id=2)(name=山田 太郎)(job=野球選手)。
セレクトしました(id=3)(name=山田 花子)(job=タレント)。
セレクトできませんでした(ResultSetは適切に配置されませんでした、たぶん、次に呼ぶ必要があります。)
アップデートしました。
アップデート結果(id=2)(name=山田 太郎)(job=裁判官)。
デリートしました(2件)。
デリート結果(id=1)(name=伊藤 太郎)(job=サラリーマン)。
メタデータを取得しました(id	name	job	)。
コミットしました。
PostgreSQLから切断しました。

_ ruby + dbd + dbi

dbd-pg-0.3.9.gem, pg-0.15.1.gem

# gem install dbd-pg-0.3.9.gem

Next install may not be needed, because it may be installed automatically in above step.

# gem install pg-0.15.1.gem

dbi-0.4.5.gem

# gem install dbi-0.4.5.gem

Bug(?) fix dbi-0.4.5 for ruby-1.9.x

When I called dbi from ruby-1.9.3, I got following error message :(

/...snip.../gems/dbi-0.4.5/lib/dbi/columninfo.rb:49:in `[]=': can't add a new key into hash during iteration (RuntimeError)

Next patch fix it :)

*** dbi-0.4.5/lib/dbi/columninfo.rb.ORG	2013-05-02 09:33:01.510999929 +0900
--- dbi-0.4.5/lib/dbi/columninfo.rb	2013-05-02 09:38:35.285999986 +0900
***************
*** 39,45 ****
              @hash ||= Hash.new

              # coerce all strings to symbols
!             @hash.each_key do |x|
                  if x.kind_of? String
                      sym = x.to_sym
                      if @hash.has_key? sym
--- 39,46 ----
              @hash ||= Hash.new

              # coerce all strings to symbols
!             hash_tmp = @hash.clone
!             hash_tmp.each_key do |x|
                  if x.kind_of? String
                      sym = x.to_sym
                      if @hash.has_key? sym

Sample ruby script

#! /usr/bin/ruby
# coding: utf-8
#
require 'rubygems'
require 'dbi'
#
# データベースへの接続
#
dbh = DBI.connect('DBI:Pg:testdb:192.168.0.123', 'm-ito', '3edc4rfv')
puts("データベース(testdb)に接続しました。")
#
# 自動コミットの停止
#
##dbh.do('SET AutoCommit = 0')
dbh['AutoCommit'] = false
puts("自動コミット停止しました(トランザクションの開始)。")
#
# トランザクション(1)
#
dbh.transaction {|dbh|
#
# テーブルの削除
#
        begin
                sql = <<SQL
                        drop table member
SQL
                dbh.do(sql)
                puts "テーブルの削除に成功しました。"
        rescue => e
                puts "テーブルの削除に失敗しました(#{e.to_s})。"
        end
#
# テーブルの作成
#
        begin
                sql = <<SQL
                        create table member (
                                id INT PRIMARY KEY,
                                name VARCHAR(50),
                                job VARCHAR(50)
                        )
SQL
                dbh.do(sql)
                puts "テーブルの作成に成功しました。"
                dbh.commit
                puts "コミットしました。"
        rescue => e
                puts "テーブルの作成に失敗しました(#{e.to_s})。"
                dbh.rollback
                puts "ロールバックしました。"
        end
#
# わざと2重にテーブルを作成してみる
#
        begin
                sql = <<SQL
                        create table member (
                                id INT PRIMARY KEY,
                                name VARCHAR(50),
                                job VARCHAR(50)
                        )
SQL
                dbh.do(sql)
                puts "テーブルの作成に成功しました(2)。"
                dbh.commit
                puts "コミットしました。"
        rescue => e
                puts "テーブルの作成に失敗しました(2)(#{e.to_s})。"
                dbh.rollback
                puts "ロールバックしました。"
        end
#
# インデックスの作成
#
        sql = <<SQL
                create index i_member_name on member(name)
SQL
        dbh.do(sql)
        puts "インデックスの作成に成功しました。"
}
#
# トランザクション(2)
#
dbh.transaction {|dbh|
#
# データの追加
#
        sql = <<SQL
                insert into member (id, name, job) values (?, ?, ?)
SQL
        sth = dbh.prepare(sql)
        sth.execute(1, '伊藤 太郎', 'サラリーマン')
        puts "インサートに成功しました。"
        sth.execute(2, '山田 太郎', '野球選手')
        puts "インサートに成功しました。"
        sth.execute(3, '山田 花子', 'タレント')
        puts "インサートに成功しました。"
        dbh.commit
        puts "コミットしました。"
#
# わざと2重キーで登録してみる
#
        begin
                sth.execute(3, '山田 花子', 'タレント')
                puts "インサートに成功しました。"
                dbh.commit
                puts "コミットしました。"
        rescue => e
                puts "インサートに失敗しました(#{e.to_s})。"
                dbh.rollback
                puts "ロールバックしました。"
        end
        sth.finish
}
#
# データの検索
#
sql = <<SQL
        select * from member where id >= ?
SQL
sth = dbh.prepare(sql)
sth.execute(1)
while (row = sth.fetch)
        puts "セレクトに成功しました(id=#{row['id']})(name=#{row['name']})(job=#{row['job']})"
end
sth.finish
#
# トランザクション(3)
#
dbh.transaction {|dbh|
#
# データの更新
#
        sql = <<SQL
                update member set job = ? where name = ?
SQL
        sth = dbh.prepare(sql)
        sth.execute('裁判官', '山田 太郎')
        puts ("アップデートに成功しました。")
        sth.finish
}
#
# 更新結果の確認
#
sql = <<SQL
        select * from member where name = ?
SQL
sth = dbh.prepare(sql)
sth.execute('山田 太郎')
while (row = sth.fetch)
        puts "アップデートの確認(id=#{row['id']})(name=#{row['name']})(job=#{row['job']})"
end
sth.finish
#
# トランザクション(4)
#
dbh.transaction {|dbh|
#
# データの削除
#
        sql = <<SQL
                delete from member where name like ?
SQL
        sth = dbh.prepare(sql)
        sth.execute('山田%')
        puts ("デリートに成功しました。")
        sth.finish
}
#
# 削除の確認
#
sql = <<SQL
        select * from member
SQL
sth = dbh.prepare(sql)
sth.execute()
while (row = sth.fetch)
        puts "デリートの確認(id=#{row['id']})(name=#{row['name']})(job=#{row['job']})"
end
sth.finish
#
# メタデータの取得
#
sql = <<SQL
        select * from member
SQL
sth = dbh.prepare(sql)
sth.execute()
puts "メタデータ カラム数=#{sth.column_names.size}"
print "メタデータ カラム名="
sth.column_names.each {|name|
        print "#{name}\t"
}
puts ""
#
sth.column_info.each_with_index {|info, i|
        print "項番 = #{i} / "
        print "カラム名 = #{info.name} / "
        print "精度 = #{info.precision} / "
        print "スケール = #{info.scale}\n"
}
sth.finish
#
# (手動で)のコミット(またはロールバック)の実行
#
#dbh.rollback
#puts "ロールバックしました。"
dbh.commit
puts "コミットしました。"
#
# データベースからの切断
#
dbh.disconnect
puts("データベース(dbitestdb)から切断しました。")

Result from running sample ruby script

NOTICE:  CREATE TABLE / PRIMARY KEYはテーブル"member"に暗黙的なインデックス"member_pkey"を作成します
データベース(testdb)に接続しました。
自動コミット停止しました(トランザクションの開始)。
テーブルの削除に成功しました。
テーブルの作成に成功しました。
コミットしました。
テーブルの作成に失敗しました(2)(ERROR:  リレーション"member"はすでに存在します
)。
ロールバックしました。
インデックスの作成に成功しました。
インサートに成功しました。
インサートに成功しました。
インサートに成功しました。
コミットしました。
インサートに失敗しました(ERROR:  重複キーは一意性制約"member_pkey"に違反しています
)。
ロールバックしました。
セレクトに成功しました(id=1)(name=伊藤 太郎)(job=サラリーマン)
セレクトに成功しました(id=2)(name=山田 太郎)(job=野球選手)
セレクトに成功しました(id=3)(name=山田 花子)(job=タレント)
アップデートに成功しました。
アップデートの確認(id=2)(name=山田 太郎)(job=裁判官)
デリートに成功しました。
デリートの確認(id=1)(name=伊藤 太郎)(job=サラリーマン)
メタデータ カラム数=3
メタデータ カラム名=id	name	job
項番 = 0 / カラム名 = id / 精度 =  / スケール =
項番 = 1 / カラム名 = name / 精度 = 50 / スケール =
項番 = 2 / カラム名 = job / 精度 = 50 / スケール =
コミットしました。
データベース(dbitestdb)から切断しました。