irvan.or.id

Tulisan kali ini hanya sekedar dokumentasi dari pekerjaan saya di kantor, yang mana saya ditugaskan untuk mencoba server HP Proliant DL 785 G5 dengan spesifikasi menggunakan 4 x Quad Core AMD Opteron 8354 2.2 GHz dan 8 GB RAM. Mesin tersebut rencananya akan digunakan sebagai mesin database utama di kantor saya, database yang digunakan adalah PostgreSQL yang berjalan di atas sistem operasi GNU/Linux.

Untuk saat ini tahapan ujicoba yang saya lakukan adalah menggunakan Distro Linux CentOS 5.2 64bit dengan kernel default, dan menggunakan PostgreSQL 8.3.7 vanilla source yang langsung di ambil dari mirror terdekat PostgreSQL. Sengaja saya menggunakan PostgreSQL 8.3.7 source bukan menggunakan PostgreSQL binary yang telah di bundle dalam paket distro CentOS 5.2, karena selain versi 8.3.7 belum tersedia di dalam distro CentOS, saya ingin mengoptimalkan PostgreSQL sesuai dengan mesin yang digunakan. Untuk itu saya memerlukan source dari PostgreSQL 8.3.7 agar dapat dicompile pada mesin tersebut.

Baik lah, tak perlu berpanjang lebar lagi, karena tujuan dari tulisan ini hanyalah sebagai dokumentasi dari apa yang telah saya kerjakan.

Pertama-tama buat kita buat group dan user dari postgresql

#groupadd -g 26 postgres > /dev/null 2>&1 || :
#useradd -u 26 -g 26 -s /bin/bash -M -r -d /var/lib/pgsql postgres > /dev/null 2>&1 || :

Kemudian download source tarball PostgreSQL 8.3.7 dari mirror terdekat:

#wget http://kambing.ui.edu/postgresql/latest/postgresql-8.3.7.tar.bz2

Lalu ekstrak paket source tarball tersebut dan pindah ke dalam direktori hasil ekstrak

#tar -jxvf postgresql-8.3.7.tar.bz2
#cd postgresql-8.3.7

Setelah itu export variable CFLAGS dan CXXFLAGS yang sesuai dengan arsitektur mesin kita. Hal ini dimaksudkan agar compiler membentuk binari hasil compile khusus untuk arsitektur mesin yang kita miliki. Kemudian jalankan perintah ./configure dengan opsi yang sesuai dengan struktur direktori CentOS 5.2. Ketik baris berikut pada shell

CFLAGS=”-O2 -march=opteron -funroll-loops” \
CXXFLAGS=”-O2 -march=opteron -funroll-loops -felide-constructors -fnoexceptions -fno-rtti” \
./configure \
–prefix=/usr \
–libdir=/usr/lib64 \
–includedir=/usr/include \
–localstatedir=/var \
–sysconfdir=/etc \
–mandir=/usr/share/man \
–infodir=/usr/share/info \
–with-libs=/usr/lib64 \
–with-includes=/usr/include \
–with-docdir=/usr/share/doc \
–enable-shared \
–disable-debug \
–enable-integer-datetimes \
–with-perl \
–with-python \
–with-openssl \
–with-pam \
–with-ldap \
–with-krb5 \
–with-libxml \
–with-libxslt

Setelah itu compile paket tersebut dan install

#make all
#make install

Selanjutnya kita membuat init script PostgreSQL yang akan digunakan untuk star|stop|restart service PostgreSQL, karena paket PostgreSQL yang kita install ini bukan berasal dari CentOS maka init script tersebut harus dibuat manual. Namun tak perlu repot, cukup copy paste init script bawaan CentOS seperti di bawah ini ke dalam file /etc/rc.d/init.d/postgresql

#!/bin/sh
# postgresql This is the init script for starting up the PostgreSQL
# server
#
# chkconfig: – 64 36
# description: Starts and stops the PostgreSQL backend daemon that handles \
# all database requests.
# processname: postmaster
# pidfile: /var/run/postmaster.pid

# Version 6.5.3-2 Lamar Owen
# Added code to determine if PGDATA exists, whether it is current version
# or not, and initdb if no PGDATA (initdb will not overwrite a database).

# Version 7.0 Lamar Owen
# Added logging code
# Changed PGDATA.

# Version 7.0.2 Trond Eivind Glomsrd
# use functions, add conditional restart

# Version 7.0.3 Lamar Owen
# Check for the existence of functions before blindly using them
# in particular — check for success () and failure () before using.
# More Cross-distribution support — PGVERSION variable, and docdir checks.

# Version 7.1 Release Candidate Lamar Owen
# initdb parameters have changed.

# Version 7.1.2 Trond Eivind Glomsrd
# Specify shell for su
# Handle stop better – kill unwanted output, make it wait until the database is ready
# Handle locales slightly differently – always using “C” isn’t a valid option
# Kill output from database initialization
# Mark messages for translation

# Version 7.1.2-2.PGDG Lamar Owen
# sync up.
# Karl’s fixes for some quoting issues.

# Version 7.2b2 Lamar Owen
# version change.

# Version 7.2 final. Lamar Owen
# reload from Peter E.
# Eliminate the pidof postmaster test in stop — we’re using pg_ctl so we don’t need pidof.
# Tested the $? return for the stop script — it does in fact propagate.
# TODO: multiple postmasters.

# Version 7.3 Lamar Owen
# Multiple postmasters, courtesy Karl DeBisschop

# Version 7.4 Lamar Owen.

# Version 7.4.3 Tom Lane
# Support condstop for uninstall
# Minor other changes suggested by Fernando Nasser.

# Version 7.4.5 Tom Lane
# Rewrite to start postmaster directly, rather than via pg_ctl; this avoids
# fooling the postmaster’s stale-lockfile check by having too many
# postgres-owned processes laying about.

# PGVERSION is the full package version, e.g., 7.4.7
# Note: the specfile ordinarily updates this during install
PGVERSION=8.3.7
# PGMAJORVERSION is major version, e.g., 7.4 (this should match PG_VERSION)
PGMAJORVERSION=`echo “$PGVERSION” | sed ’s/^\([0-9]*\.[0-9]*\).*$/\1/’`

# Source function library.
. /etc/rc.d/init.d/functions

# Get function listing for cross-distribution logic.
TYPESET=`typeset -f|grep “declare”`

# Get config.
. /etc/sysconfig/network

# Find the name of the script
NAME=`basename $0`
if [ ${NAME:0:1} = "S" -o ${NAME:0:1} = "K" ]
then
NAME=${NAME:3}
fi

# For SELinux we need to use ‘runuser’ not ’su’
if [ -x /sbin/runuser ]
then
SU=runuser
else
SU=su
fi

# Set defaults for configuration variables
PGENGINE=/usr/bin
PGPORT=5432
PGDATA=/var/lib/pgsql
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base/template1" ]
then
echo “Using old-style directory structure”
else
PGDATA=/var/lib/pgsql/data
fi
PGLOG=/var/lib/pgsql/pgstartup.log

# Override defaults from /etc/sysconfig/pgsql if file is present
[ -f /etc/sysconfig/pgsql/${NAME} ] && . /etc/sysconfig/pgsql/${NAME}

export PGDATA
export PGPORT

# Check that networking is up.
# Pretty much need it for postmaster.
[ "${NETWORKING}" = "no" ] && exit 1

[ -f "$PGENGINE/postmaster" ] || exit 1

script_result=0

start(){
PSQL_START=$”Starting ${NAME} service: ”

# Make sure startup-time log file is valid

if [ ! -e "$PGLOG" -a ! -h "$PGLOG" ]
then
touch “$PGLOG” || exit 1
chown postgres:postgres “$PGLOG”
chmod go-rwx “$PGLOG”
[ -x /usr/bin/chcon ] && /usr/bin/chcon -u system_u -r object_r -t postgresql_log_t “$PGLOG” 2>/dev/null
fi

# Check for the PGDATA structure
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base" ]
then
# Check version of existing PGDATA

if [ x`cat "$PGDATA/PG_VERSION"` != x"$PGMAJORVERSION" ]
then
SYSDOCDIR=”(Your System’s documentation directory)”
if [ -d "/usr/doc/postgresql-$PGVERSION" ]
then
SYSDOCDIR=/usr/doc
fi
if [ -d "/usr/share/doc/postgresql-$PGVERSION" ]
then
SYSDOCDIR=/usr/share/doc
fi
if [ -d "/usr/doc/packages/postgresql-$PGVERSION" ]
then
SYSDOCDIR=/usr/doc/packages
fi
if [ -d "/usr/share/doc/packages/postgresql-$PGVERSION" ]
then
SYSDOCDIR=/usr/share/doc/packages
fi
echo
echo $”An old version of the database format was found.”
echo $”You need to upgrade the data format before using PostgreSQL.”
echo $”See $SYSDOCDIR/postgresql-$PGVERSION/README.rpm-dist for more information.”
exit 1
fi

# No existing PGDATA! Initdb it.

else
echo -n $”Initializing database: ”
if [ ! -e "$PGDATA" -a ! -h "$PGDATA" ]
then
mkdir -p “$PGDATA” || exit 1
chown postgres:postgres “$PGDATA”
chmod go-rwx “$PGDATA”
fi
# Clean up SELinux tagging for PGDATA
[ -x /sbin/restorecon ] && /sbin/restorecon “$PGDATA”
# Initialize the database
$SU -l postgres -c “$PGENGINE/initdb –pgdata=’$PGDATA’ –auth=’ident sameuser’” >> “$PGLOG” 2>&1 < /dev/null
# Create directory for postmaster log
mkdir “$PGDATA/pg_log”
chown postgres:postgres “$PGDATA/pg_log”
chmod go-rwx “$PGDATA/pg_log”

[ -f "$PGDATA/PG_VERSION" ] && echo_success
[ ! -f "$PGDATA/PG_VERSION" ] && echo_failure
echo
fi

echo -n “$PSQL_START”
$SU -l postgres -c “$PGENGINE/postmaster -p ‘$PGPORT’ -D ‘$PGDATA’ ${PGOPTS} &” >> “$PGLOG” 2>&1 < /dev/null
sleep 2
pid=`pidof -s “$PGENGINE/postmaster”`
if [ $pid ] && [ -f "$PGDATA/postmaster.pid" ]
then
success “$PSQL_START”
touch /var/lock/subsys/${NAME}
head -n 1 “$PGDATA/postmaster.pid” > “/var/run/postmaster.${PGPORT}.pid”
echo
else
failure “$PSQL_START”
echo
script_result=1
fi
}

stop(){
echo -n $”Stopping ${NAME} service: ”

$SU -l postgres -c “$PGENGINE/pg_ctl stop -D ‘$PGDATA’ -s -m fast” > /dev/null 2>&1 < /dev/null
ret=$?
if [ $ret -eq 0 ]
then
echo_success
else
echo_failure
script_result=1
fi
echo
rm -f “/var/run/postmaster.${PGPORT}.pid”
rm -f “/var/lock/subsys/${NAME}”
}

restart(){
stop
start
}

condrestart(){
[ -e /var/lock/subsys/${NAME} ] && restart
}

condstop(){
[ -e /var/lock/subsys/${NAME} ] && stop
}

reload(){
$SU -l postgres -c “$PGENGINE/pg_ctl reload -D ‘$PGDATA’ -s” > /dev/null 2>&1 < /dev/null
}

# This script is slightly unusual in that the name of the daemon (postmaster)
# is not the same as the name of the subsystem (postgresql)

# See how we were called.
case “$1″ in
start)
start
;;
stop)
stop
;;
status)
status postmaster
script_result=$?
;;
restart)
restart
;;
condrestart)
condrestart
;;
condstop)
condstop
;;
reload|force-reload)
reload
;;
*)
echo $”Usage: $0 {start|stop|status|restart|condrestart|condstop|reload|force-reload}”
exit 1
esac

exit $script_result

Langkah berikutnya adalah menjalankan PostgreSQL dan mengaktifkan PostgreSQL tiap kali mesin dihidupkan

#chkconfig –add postgresql
#chkconfig –level 345 postgresql on
#/etc/init.d/postgresql start

Yak, sampai di situ saja dokumentasi dari apa yang telah saya kerjakan kali ini. Semoga bisa bermanfaat untuk anda..

How-To, Linux, Tutorial 64bit, CentOS, Linux, Optimizing, PostgreSQL