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:

Edit the file /var/lib/pgsql/data/pg_hba.conf

To restart the postgresl DB run the command:

Check postgresql version

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

Create postgresql test user and database

Configure postgresql to accept all TCP connections

Edit the file /var/lib/pgsql/data(postgresql.conf

Restart again the postgresl DB:

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

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

Drop a table column

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

PostgreSQL (last edited 2013-12-28 23:37:23 by 89)