Posgres Server Configuration

Mount Bear

Mounting Bear makes data transfer for the build easier...

mkdir /mnt/ed
mount -t cifs //bear/ed_egan/ /mnt/ed -o user=haas\\ed_egan

Check the spec

Run some basic commands to check the spec of the box

uname -a
 Linux PhD-postgres2 2.6.18-274.12.1.el5 #1 SMP Tue Nov 29 13:37:46 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

cat /etc/issue
 CentOS release 5.7 (Final)

cat /proc/version
 Linux version 2.6.18-274.12.1.el5 

gmake --version
 GNU Make 3.81 (need >3.8)

perl -V
 check for: usemultiplicity=define

python -v
 (ctrl-D) to get out if it works

Build Postgres

Download a copy of Postgres 9.1.2 and put it in /home/ed/ (not on the mount - have it local) Then:

gunzip postgresql-9.1.2.tar.gz
tar xf postgresql-9.1.2.tar
cd postgresql-9.1.2

Update missing packages needed for the build

yum install gcc gcc-c++ autoconf automake
yum install readline-devel zlib-devel python-devel

Now do the actual install (Official Instructions):

./configure --with-perl --with-python --with-segsize=16 --with-blocksize=32

gmake
 All of PostgreSQL is successfully made. Ready to install.

gmake world
 PostgreSQL, contrib and HTML documentation successfully made. Ready to install.

As root edit /etc/profile to include (before 'export PATH'):

PATH=/usr/local/pgsql/bin:$PATH

Configure the server

Add the postgres user and get her running:

adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

Make a database user:

CREATE USER ed_egan WITH PASSWORD 'whatever';

Edit /etc/sysconfig/iptables to include:

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 128.32.66.0/24 --dport 5432 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 128.32.67.0/24 --dport 5432 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 128.32.74.0/24 --dport 5432 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 10.136.0.0/23 --dport 5432 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp -s 136.152.208.0/22 --dport 5432 -j ACCEPT

/etc/init.d/iptables restart

Change postgres.conf:

listen_addresses = '*'
port = 5432		
max_connections = 100	
shared_buffers = 4GB	
work_mem = 512MB	
maintenance_work_mem = 512MB
effective_cache_size = 14GB 

Add access permissions to pg_hba.conf

host    all         all         128.32.74.0/24        trust
host    all         all         128.32.66.0/24        trust
host    all         all         128.32.67.0/24        trust
host    all         all         10.136.0.0/23         trust

Add to /etc/rc.local

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

Start postgres with (if you've fixed the path then abbrev the first part)

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

Note that the second command may fail silently if there is something wrong with the config, and the first uses the terminal unless you add the &.

Check postgres is listening on 5432:

netstat -tulpn

Test

Test by connecting remotely using a psql client on your desktop. Then enjoy!