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