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!