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==

   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