Downloading and install Postgresql

get the latest build of postgresql
using wget -U ftp://..../postgresql-7.xx.tar.gz

$ su
# cd /usr/local/src
# tar -xvzf ~yourhomedir/postgresql-7.xx.tar.gz
# cd postgresql-7.xx
# ./configure --prefix=/usr/local/pgsql
# make
# make install
# adduser postgres
# su postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
$ exit

edit /usr/local/pgsql/data/pg_hba.conf

let everyone from the  network

add something like this
host         all            255.255.255.0       trust

or

host         all            255.255.255.0       password 
I noticed that this was required using pgadmin from windows or trust
crypt didn't work for me...

or

host         all            255.255.255.0       crypt 
This is the better choice if you're using the psql cli client
pgadmin failed to connect with this option

create start/stop script

#!/bin/sh

case "$1" in
  'start')
  su postgres -c 'exec /usr/local/pgsql/bin/pg_ctl -o -i -D /usr/local/pgsql/data start >> /usr/local/pgsql/errlog 
2>&1 &'
  ;;
 
  'stop')
  su postgres -c 'exec /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop >> /usr/local/pgsql/errlog 2>&1 &'
  ;;
 
  *)
        echo "usage: $0 start|stop"
        exit 1
  ;;
esac

save script as pgsql  place the following in /etc/rc.d/init.d/pgsql

#  chmod 755 /etc/rc.d/init.d/pgsql

add script to startup,reboot,shutdown runlevels

# ln -s /etc/rc.d/init.d/pgsql /etc/rc.d/rc3.d/S15pgsql
# ln -s /etc/rc.d/init.d/pgsql /etc/rc.d/rc0.d/K81pgsql
# ln -s /etc/rc.d/init.d/pgsql /etc/rc.d/rc6.d/K81pgsql

start database

# /etc/rc.d/init.d/pgsql start

start interacting with database

# su postgres
$ createuser <username>
$ createdb <database>

connect to database

$ psql <database>

User Administration

# create user account
template1=# create user <username>;

# add the permission to create databases and users
template1=# update pg_shadow set usecreatedb='t' where usename='<username>'; 
template1=# update pg_shadow set usesuper='t' where usename='<username>'; 

# look at change
template1=# select * from pg_shadow; 

# remove those permissions
template1=# update pg_shadow set usecreatedb='t' where usename='<username>'; 
template1=# update pg_shadow set usesuper='t' where usename='<username>'; 


Group Administration

# create group
template1=# create group <groupname>

# add users to that group
template1=# alter group <groupname> add user1,user2;

# remove user(s) from group
template1=# alter group <groupname> del user2;

Database Administration

template1=# create database <database>
template1=# \c database
mydb=# 

# run filename.pgsql sql commands
mydb=# \i fileName

# list tables in database
mydb=# \dt

# list tables/views
mydb=# \d

# grant everyone the ability to select from customers
mydb=# grant select on customers to PUBLIC

# revoke everyone the ability to update, insert on accounts
mydb=# revoke all on accounts from PUBLIC

# allow group <groupname> to update accounts
mydb=# grant update on accounts to group <groupname>


Backing the system up
01:00 each morning:

00 01 * * * /home/postgres/pgbackup > /dev/null 2>&1


#!/bin/sh
# Location of logfile.
logfile="/home/sysbackups/postgres/backup.log"
# Location to place backups.
backup_dir="/home/sysbackups/postgres"
touch $logfile
timeslot=`date +%H-%M`
databases=`/usr/local/pgsql/bin/psql -h localhost template1 -q -c "\l" |
sed -n 4,/\eof/p | grep -v rows\) \
| awk {'print $1'}`


for i in $databases; do
        timeinfo=`date '+%T %x'`
        echo "Backup and Vacuum complete at $timeinfo for time slot
$timeslot on database: $i " >> $logfile
        /usr/local/pgsql/bin/vacuumdb -z -h localhost -U postgres $i
>/dev/null 2>&1
        /usr/local/pgsql/bin/pg_dump $i -h 127.0.0.1 | gzip >
"$backup_dir/postgresql-$i-$timeslot-database.gz"
done


Postgresql SQL Ref

ABORT - Aborts the current transaction 
ALTER GROUP - Add users to a group, remove users from a group 
ALTER TABLE - Modifies table properties 
ALTER USER - Modifies user account information 
BEGIN - Begins a transaction in chained mode 
CHECKPOINT - Force transaction log checkpoint
CLOSE - Close a cursor 
CLUSTER - Gives storage clustering advice to the server 
COMMENT - Add comment to an object 
COMMIT - Commits the current transaction 
COPY - Copies data between files and tables 
CREATE AGGREGATE - Defines a new aggregate function 
CREATE CONSTRAINT TRIGGER - Create a trigger to support a constraint 
CREATE DATABASE - Creates a new database 
CREATE FUNCTION - Defines a new function 
CREATE GROUP - Creates a new group 
CREATE INDEX - Constructs a secondary index 
CREATE LANGUAGE - Defines a new language for functions 
CREATE OPERATOR - Defines a new user operator 
CREATE RULE - Defines a new rule 
CREATE SEQUENCE - Creates a new sequence number generator 
CREATE TABLE - Creates a new table 
CREATE TABLE AS - Creates a new table from the results of a SELECT 
CREATE TRIGGER - Creates a new trigger 
CREATE TYPE - Defines a new base data type 
CREATE USER - Creates a new database user 
CREATE VIEW - Constructs a virtual table 
DECLARE - Defines a cursor for table access 
DELETE - Removes rows from a table 
DROP AGGREGATE - Removes the definition of an aggregate function 
DROP DATABASE - Removes an existing database 
DROP FUNCTION - Removes a user-defined C function 
DROP GROUP - Removes a group 
DROP INDEX - Removes existing indexes from a database 
DROP LANGUAGE - Removes a user-defined procedural language 
DROP OPERATOR - Removes an operator from the database 
DROP RULE - Removes existing rules from the database 
DROP SEQUENCE - Removes existing sequences from a database 
DROP TABLE - Removes existing tables from a database 
DROP TRIGGER - Removes the definition of a trigger 
DROP TYPE - Removes user-defined types from the system catalogs 
DROP USER - Removes a user 
DROP VIEW - Removes existing views from a database 
END - Commits the current transaction 
EXPLAIN - Shows statement execution plan 
FETCH - Gets rows using a cursor 
GRANT - Grants access privilege to a user, a group or all users 
INSERT - Inserts new rows into a table 
LISTEN - Listen for a response on a notify condition 
LOAD - Dynamically loads an object file 
LOCK - Explicitly lock a table inside a transaction 
MOVE - Moves cursor position 
NOTIFY - Signals all frontends and backends listening on a notify condition 
REINDEX - Recover corrupted system indexes under stand-alone Postgres 
RESET - Restores run-time parameters to default values
REVOKE - Revokes access privilege from a user, a group or all users. 
ROLLBACK - Aborts the current transaction 
SELECT - Retrieves rows from a table or view 
SELECT INTO - Creates a new table from the results of a SELECT 
SET - Set run-time parameters
SET CONSTRAINTS - Set the constraint mode of the current SQL-transaction
SET TRANSACTION - Set the characteristics of the current SQL-transaction
SHOW - Shows run-time parameters
TRUNCATE - Empty a table 
UNLISTEN - Stop listening for notification 
UPDATE - Replaces values of columns in a table 
VACUUM - Clean and analyze a Postgres database