PostgreSQL 8のインストール - Vancouverで奮闘中(FreeBSD, Linux, Java覚え書き)

　PostgreSQLをインストールする。Debianだとstableは未だに7.4なのでFreeBSDに8.0.4をインストールすることにする。Debianでもソース持って来てコンパイルするなり自前でパッケージを作れば良いのだろうが、自前でパッケージ作るよりもportsでconfigureしての方が楽過ぎる。

いつものように


tmkbsd# cd /usr/ports/databases/postgresql80-server
tmkbsd# make install clean
--snip--
======================================================================
For procedural languages and postgresql functions, please note that
you might have to update them when updating the server.

If you have many tables and many clients running, consider raising
kern.maxfiles using sysctl(8), or reconfigure your kernel
appropriately.

You should vacuum and backup your database regularly. There is a
periodic script, ${LOCALBASE}/etc/periodic/daily/502.pgsql, that you
may find useful. Per default, it perfoms vacuum on all databases
nightly. See the script for instructions.

To allow many simultaneous connections to your PostgreSQL server, you
should raise the SystemV shared memory limits in your kernel. Here are
example values for allowing up to 180 clients (configurations in
postgresql.conf also needed, of course):
  options         SYSVSHM
  options         SYSVSEM
  options         SYSVMSG
  options         SHMMAXPGS=65536
  options         SEMMNI=40
  options         SEMMNS=240
  options         SEMUME=40
  options         SEMMNU=120

If you plan to access your PostgreSQL server using ODBC, please
consider running the SQL script /usr/local/share/postgresql/odbc.sql
to get the functions required for ODBC compliance.

======================================================================

To initialize the database, run

  /usr/local/etc/rc.d/010.pgsql.sh initdb

You can then start PostgreSQL by running:

  /usr/local/etc/rc.d/010.pgsql.sh start

For postmaster settings, see ~pgsql/data/postgresql.conf

NB. FreeBSD's PostgreSQL port now by default logs to syslog
    See ~pgsql/data/postgresql.conf for more info

======================================================================

To run PostgreSQL at startup, add
'postgresql_enable="YES"' to /etc/rc.conf

======================================================================
===>   Registering installation for postgresql-server-8.0.4

${LOCALBASE}/etc/periodic/daily/502.pgsqlとあるので見てみると、なんとvacuumとbackupのスクリプトが．．．．これはportsだから????

今までは、postgreSQLのML(だったと思うけど)で見た、


#/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/lib/postgresql/bin
PG=/usr/lib/postgresql
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH":$PG/lib
export PGLIB=$PG/lib
export PGDATA=/var/lib/postgres/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=/var/lib/postgres/backup
LOG=$DIR/pgdump.log
BACKUPDIR=$DIR/dbbackup
EXPIRE_DAY=3

# 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/lib/postgresql/bin/pg_dump $db_name -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

echo "Vacuum database $db_name start." `date` >>$LOG
	/usr/lib/postgresql/bin/vacuumdb $db_name -f -z 
echo "Vacuum database $db_name complete."`date` >> $LOG

を使っていました。

/etc/rc.confにpostgresql_enable="YES"を追加する。

tmkbsd# /usr/local/etc/rc.d/010.pgsql.sh initdb
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale C.

creating directory /usr/local/pgsql/data ... ok
creating directory /usr/local/pgsql/data/global ... ok
creating directory /usr/local/pgsql/data/pg_xlog ... ok
creating directory /usr/local/pgsql/data/pg_xlog/archive_status ... ok
creating directory /usr/local/pgsql/data/pg_clog ... ok
creating directory /usr/local/pgsql/data/pg_subtrans ... ok
creating directory /usr/local/pgsql/data/base ... ok
creating directory /usr/local/pgsql/data/base/1 ... ok
creating directory /usr/local/pgsql/data/pg_tblspc ... ok
selecting default max_connections ... 40
selecting default shared_buffers ... 1000
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... ok
initializing pg_shadow ... ok
enabling unlimited row size for system tables ... ok
initializing pg_depend ... ok
creating system views ... ok
loading pg_description ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/postmaster -D /usr/local/pgsql/data
or
    /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

でインストールは完了。