postgres 7.3.3 + DBI のインストール
2003-07-24 作成 福島

オフィシャルサイト: www.postgresql.org (当然英語です)

$ su
# mkdir /home/pgsql
# useradd -d /home/pgsql postgres
# chown postgres:postgres /home/pgsql
# chmod o+x /home/pgsql

# su - postgres            環境ごと
pgsql$ tar xzf postgresql-7.3.3.tar.gz
pgsql$ cd postgresql-7.3.3
postgresql-7.3.3$ ./configure --enable-multibyte=EUC_JP --prefix=/home/pgsql --enable-odbc
postgresql-7.3.3$ make
postgresql-7.3.3$ make check
postgresql-7.3.3$ make install        root にならない

postgresql-7.3.3$ cd
pgsql$ cat >> .bash_profile
PATH=/home/pgsql/bin:$PATH
export PGLIB=/home/pgsql/lib
export PGDATA=/home/pgsql/data
export MANPATH=/home/pgsql/man:$MANPATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGLIB
pgsql$ . .bash_profile pgsql$ mkdir data pgsql$ chmod 700 data pgsql$ initdb pgsql$ exit # cp -p /home/pgsql/postgresql-7.3.3/contrib/start-scripts/linux /etc/rc.d/init.d/postgres # vi /etc/rc.d/init.d/postgres
#! /bin/sh

# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS

# This is an example of a start/stop script for SysV-style init, such
# as is used on Linux systems.  You should edit some of the variables
# and maybe the 'echo' commands.
#
# Place this file at /etc/init.d/postgresql (or
# /etc/rc.d/init.d/postgresql) and make symlinks to
#   /etc/rc.d/rc0.d/K02postgresql
#   /etc/rc.d/rc1.d/K02postgresql
#   /etc/rc.d/rc2.d/K02postgresql
#   /etc/rc.d/rc3.d/S98postgresql
#   /etc/rc.d/rc4.d/S98postgresql
#   /etc/rc.d/rc5.d/S98postgresql
# Or, if you have chkconfig, simply:
# chkconfig --add postgresql
#
# Proper init scripts on Linux systems normally require setting lock
# and pid files under /var/run as well as reacting to network
# settings, so you should treat this with care.

# Original author:  Ryan Kirkpatrick 

# $Header: /cvsroot/pgsql-server/contrib/start-scripts/linux,v 1.3 2001/07/30 14:52:42 momjian Exp $

## EDIT FROM HERE

# Installation prefix
prefix=/home/pgsql

# Data directory
PGDATA="/home/pgsql/data"

# Who to run pg_ctl as, should be "postgres".
PGUSER=postgres

# Where to keep a log file
PGLOG="$PGDATA/serverlog"

## STOP EDITING HERE

# Check for echo -n vs echo \c
if echo '\c' | grep -s c >/dev/null 2>&1 ; then
    ECHO_N="echo -n"
    ECHO_C=""
else
    ECHO_N="echo"
    ECHO_C='\c'
fi

# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# What to use to start up the postmaster
DAEMON="$prefix/bin/pg_ctl"

set -e

# Only start if we can find pg_ctl.
test -f $DAEMON || exit 0

# Parse command line parameters.
case $1 in
  start)
        $ECHO_N "Starting PostgreSQL: "$ECHO_C
        su - $PGUSER -c "$DAEMON start -D '$PGDATA' -s -l $PGLOG"
        echo "ok"
        ;;
  stop)
        echo -n "Stopping PostgreSQL: "
        su - $PGUSER -c "$DAEMON stop -D '$PGDATA' -s -m fast"
        echo "ok"
        ;;
  restart)
        echo -n "Restarting PostgreSQL: "
        su - $PGUSER -c "$DAEMON restart -D '$PGDATA' -s -m fast"
        echo "ok"
        ;;
  status)
        su - $PGUSER -c "$DAEMON status -D '$PGDATA'"
        ;;
  *)
        # Print help
        echo "Usage: $0 {start|stop|restart|status}" 1>&2
        exit 1
        ;;
esac

exit 0
# chmod 754 /etc/rc.d/init.d/postgres # /sbin/chkconfig --add postgres # exit $ tar xzf DBI-1.37.tar.gz $ cd DBI-1.37 DBI-1.37$ perl Makefile.PL DBI-1.37$ make DBI-1.37$ make test DBI-1.37$ su DBI-1.37# make install DBI-1.37# exit $ tar xzf DBD-Pg-1.22 $ cd DBD-Pg-1.22 DBD-Pg-1.22$ export POSTGRES_LIB=/home/pgsql/lib DBD-Pg-1.22$ export POSTGRES_INCLUDE='/home/pgsql/postgresql-7.3.3/src/include -I/home/pgsql/postgresql-7.3.3/src/interfaces/libpq' DBD-Pg-1.22$ perl Makefile.PL DBD-Pg-1.22$ make DBD-Pg-1.22$ su DBD-Pg-1.22# su DBD-Pg-1.22# su - postgres DBD-Pg-1.22$ pg_ctl -w start DBD-Pg-1.22$ exit DBD-Pg-1.22# exit DBD-Pg-1.22$ export PGUSER=postgres DBD-Pg-1.22$ make test ERROR: DROP DATABASE: database "pgperltest" is being accessed by other users は無視して良い DBD-Pg-1.22$ su DBD-Pg-1.22# make install DBD-Pg-1.22# exit # /etc/rc.d/init.d/postgres start postmaster を起動
C 言語から利用
#include	<stdio.h>
#include	<libpq-fe.h>

main()
{
	char*	pghost   = NULL ;	// postmaster が動作しているホスト名
	char*	pgport   = NULL ;	//            〃            ポート
	char*	pgoption = NULL ;	// オプション
	char*	pgtty    = NULL ;
	char*	dbname   = "testdb" ;
	PGconn*	pgconn ;
	PGresult*	pgresult ;
	int	nField ;
	int	i,j ;


	conn = PQsetdb(pghost, pgport, pgoption, pgtty, dbname) ;	// postmaster へ接続

	if (PQstatus(conn) == CONNECTION_BAD)
		{
		// 接続失敗
		printf("%s\n", PQerrorMessage(conn)) ;
		PQfinish(conn) ;
		exit ;
		}

	res = PQexec(conn, "BEGIN") ;
	if (res == 0
	||  PQresultStatus(res) != PGRES_COMMAND_OK)
		{
		PQclear(res) ;
		PQfinish(conn) ;
		exit ;
		}
	PQclear(res) ;

	// カーソルを開く
	//
	res = PQexec(conn, "DECLARE cur CURSOR FOR select from test_table") ;
	if (res == 0
	||  PQresultStatus(res) != PGRES_COMMAND_OK)
		{
		PQclear(res) ;
		PQfinish(conn) ;
		exit ;
		}
	PQclear(res) ;

	res = PQexec(conn, "FETCH ALL in cur") ;
	if (res == 0
	||  PQresultStatus(res) != PGRES_TUPLES_OK)
		{
		PQclear(res) ;
		PQfinish(conn) ;
		exit ;
		}

	nField = PQnfields(res) ;

	for ( i = 0 ; i < nField ; i ++ )
		{
		printf("%s\n", PQfname(res,i)) ;	// 属性名を表示
		}
	printf("\n") ;

	for ( i = 0 ; i < PQntuples(res) ; i ++ )
		for ( j = 0 ; j < nField ; j ++ )
			{
			printf("%s\n", PQgetvalue(res, i, j)) ;	// インスタンスを表示
			}

	PQclear(res) ;

	// カーソルを閉じる
	//
	res = PQexec(conn, "CLOSE cur") ;
	PQclear(res) ;

	res = PQexec(conn, "COMMIT") ;	// トランザクション (conn) をコミットする

	PQfinish(conn) ;
}