= Install Postgresql on CentOS 6.4 =

These installation steps should also apply to other distros based on RedHat source RPMs.

Logged in as root on the system run the following commands:
 * yum install postgresql
 * yum install postgresql-devel
 * yum install postgresql-server
 * service postgresql initdb
 * chkconfig postgresql on
 * service postgresql start

Edit the file ''' /var/lib/pgsql/data/pg_hba.conf  '''
 * local all all ident
 * host all all 0.0.0.0/0 password

To restart the postgresl DB run the command: 
 * service postgresql restart

== Create postgresql test user and database ==
 * su postgres
 * psql
 * create user appuser with login password '12345678';
 * create database app;
 * grant all privileges on database app to appuser;
 * \q

== Configure postgresql to accept all TCP connections ==
Edit the file ''' /var/lib/pgsql/data(postgresql.conf '''
 * listen-address="*"
 * port = 5432
 * max_connections = 100

Restart again the postgresl DB: 
 * service postgresql restart

== Test the connection using the user and password ==
psql -U appuser -h 127.0.0.1 -W -d a++

== Backup and restore with pg_dump ==
 * su postgres
 * pg_dump databasex > /var/lib/pgsql/backDatabasex.sql
 * psql databasex < /var/lib/pgsql/backDatabasex.sql # restore

== Daily backup script ==
{{{#!highlight bash
#!/bin/sh
#backupDBs.sh
#chmod 755 backupDBs.sh 
#backup database postgresql
#crontab -e 
#@daily /var/lib/pgsql/backupDBs.sh
BACKUPFOLDER=/var/lib/pgsql/backups
CURRDATE=`date -u "+%Y-%m-%dT%H:%m:%S.%Z"`
FILE=$BACKUPFOLDER/backup$CURRDATE.sql
DATABASE=dbx
/usr/bin/pg_dump $DATABASE > $FILE
gzip $FILE
}}}

== Show table structure ==
 * su postgres
 * \c databasex
 * \dt
 * \d+ tablex

== Drop a table column ==
 * su postgres
 * \c databasex
 * alter table tablex drop column columnx;

== Epoch from date field ==
Number of seconds since 1st January 1970 UTC.

{{{#!highlight sql
SELECT field1, field2, field3, extract(epoch from begindate) as begindate,extract(epoch from enddate) as enddate 
FROM tableX 
}}}

== Garbage-collect and analyze a PostgreSQL database ==
Create file /var/lib/pgsql/vacuumdb.sh and chmod 755 it
{{{
vacuumdb --all --analyze --verbose
}}}

Create cron job, crontab -e
{{{
@daily /var/lib/pgsql/vacuumdb.sh
}}}