• PostgreSQL

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

Check postgresql version

   1 cat /var/lib/pgsql/data/PG_VERSION

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

   1 #!/bin/sh
   2 #backupDBs.sh
   3 #chmod 755 backupDBs.sh 
   4 #backup database postgresql
   5 #crontab -e 
   6 #@daily /var/lib/pgsql/backupDBs.sh
   7 BACKUPFOLDER=/var/lib/pgsql/backups
   8 CURRDATE=`date -u "+%Y-%m-%dT%H:%m:%S.%Z"`
   9 FILE=$BACKUPFOLDER/backup$CURRDATE.sql
  10 DATABASE=dbx
  11 /usr/bin/pg_dump $DATABASE > $FILE
  12 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.

   1 SELECT field1, field2, field3, extract(epoch from begindate) as begindate,extract(epoch from enddate) as enddate 
   2 FROM tableX 

Garbage-collect and analyze a PostgreSQL database

Create file /var/lib/pgsql/vacuumdb.sh and chmod 755 it

   1 #!/bin/sh
   2 /usr/bin/vacuumdb --all --analyze --verbose

Create cron job to user postgres, crontab -e

@daily /var/lib/pgsql/vacuumdb.sh