Posgres Server Configuration

See also:

• Working with PostgreSQL
• Haas PhD Server Configuration

Note that the server is now on 128.32.204.203 (having been moved to the new colo)

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

su -c '/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data -l >/usr/local/pgsql/data/serverlog' postgres

Start postgres with one the following commands as the postgres user (if you've fixed the path then abbrev the first part)

/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
 (runs the server with output on the terminal)
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >/usr/local/pgsql/data/logfile 2>&1 &
 (runs the server with output in the logfile - note the path is needed for the logfile)
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/logfile start &
 (same as above but using the pg_ctl wrapper)

Check postgres is listening on 5432:

netstat -tulpn

Test

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

psql -h 128.32.252.201 -U ed_egan test

Adding Users

Adding Root Accounts to the box

Assuming that you have root, you can create user accounts on the box and give them root too. This isn't necessary for regular users - they just need a Postgres user account (see below). To add users to the box, the process is:

First great the users group, checking the last group number (5xx is the next one):

cat /etc/group
/usr/sbin/groupadd -g 5xx username 

Then add the user (it doesn't matter what you put for -p, it is going to be overwritten):

/usr/sbin/useradd -g username -G root -s /bin/bash -p xxxx -d /home/username -m username
      where g is the primary group, G is other groups, p sets a password, 
      d declares a home directory and m makes the directory

Change the user's password:

passwd username

And add the user to the sudoers file

echo 'username ALL=(ALL) ALL' >> /etc/sudoers

Deleting a user

To delete a user:

/usr/sbin/userdel -r roger 
where r removes the home directory

And to remove their group

/usr/sbin/groupdel username

And remove their entry from the sudoers file too if they had root.

Adding Postgres Accounts

Log on to the box as root then:

su postgres

To work as the postgres account.

Now add the user using:

/usr/local/pgsql/bin/createuser username
(Answer y or n to whether you want the new role to be superuser - generally y)

If the user doesn't have an account on the box, then you'll need to add a database for them (use DBname of 'firstname_data' as a default):

/usr/local/pgsql/bin/createdb -O username DBname

Size, Backup & Restore

Find the sizes of databases on our postgres server

• Connect to the dbase server (poss. with an admin account)
• psql postgres
• run the following query:

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
   LIMIT 20;

To see the disk space use on all drives use:

df -h

Backing up a dbase

pg_dump dbase > dbase.dump
or 
pg_dump -Fc dbase > dbase_fc.dump (which uses compression)

Note: Use top or ps -aux to see memory/CPU usage.

We can also manually compress/decompress using:

gzip filename 
gzip -d filename.gz

When done, we can drop the database:

dropdb dbase

Restoring a dbase

pg_restore -d newdb dbase.dump