How to configure postgres in rhel 6.3 / centos 6.3

-->

[root@station1 ~]# yum install postgresql*

Initializing the data first

root@station1 ~]# /etc/init.d/postgresql initdb

Initializing database: [ OK ]

Set PostgreSQL Environment

[root@station1 ~]# vim /var/lib/pgsql/.bash_profile

1 [ -f /etc/profile ] && source /etc/profile

2

3 PGDATA=/var/lib/pgsql/data

4 export PGDATA

5 PATH=$PATH:$HOME/bin:/usr/bin

6 export PATH

[root@station1 ~]# service postgresql start

Starting postgresql service: [ OK ]

Set postgres Password

[root@station1 ~]# su - postgres

-bash-4.1$ psql postgres postgres

psql (8.4.11)

Type "help" for help.

postgres=# alter user postgres with password 'postgres';

ALTER ROLE

postgres=#

Configure PostgreSQL pg_hba.conf File

[root@station1 ~]# vim /var/lib/pgsql/data/pg_hba.conf

70 local all all md5

71 # IPv4 local connections:

72 host all all 127.0.0.1/32 md5

73 # IPv6 local connections:

74 host all all ::1/128 md5

In order for the change to take effect, reload the pg_hba.conf file.

As with any command, there are several ways you can reload the pg_hba.conf file.

Method 1: From the shell using pg_ctl reload:

[root@station1 ~]# su - postgres

-bash-4.1$ pg_ctl reload

server signaled

-bash-4.1$

Method 2: From psql using pg_reload_conf();

[root@station1 ~]# su - postgres

-bash-4.1$ psql postgres postgres

Password for user postgres:

psql (8.4.11)

Type "help" for help.

postgres=# SELECT pg_reload_conf();

pg_reload_conf

----------------

t

(1 row)

postgres=#

Method 3: From the shell using -c switch to run select pg_reload_conf();

[root@station1 ~]# su - postgres

-bash-4.1$ psql postgres postgres -c "select pg_reload_conf();"

Password for user postgres:

pg_reload_conf

----------------

t

(1 row)

-bash-4.1$

Configure Remote Access for PostgreSQL

[root@station1 ~]# vim /var/lib/pgsql/data/postgresql.conf

59 listen_addresses = '*'

63 port = 5432

You can also set the listen_address limit to a specific IP (or IPs using a comma separated list).

Note: For security, it is also a good idea to change the default port. To do this, uncomment port and set to a new port value.

If you change the port, you will need to restart the service.

Restart the postgresql service:

[root@station1 ~]# service postgresql restart

Stopping postgresql service: [ OK ]

Starting postgresql service: [ OK ]

Verify the changes to listen_address and port (if changed):

[root@station1 ~]# su postgres

bash-4.1$ psql

could not change directory to "/root"

Password:

psql (8.4.11)

Type "help" for help.

postgres=# show listen_addresses;

listen_addresses

------------------

*

(1 row)

postgres=# show port;

port

------

5432

(1 row)

postgres=#

Create User and Database for PostgreSQL

[root@station1 ~]# psql postgres postgres

Password for user postgres:

psql (8.4.11)

Type "help" for help.

postgres=# CREATE user ranjith with password 'ranjihat';

CREATE ROLE

postgres=# CREATE DATABASE mytestdb owner=ranjith;

CREATE DATABASE

postgres=# \c mytestdb ranjith

Password for user ranjith:

psql (8.4.11)

You are now connected to database "mytestdb" as user "ranjith".

mytestdb=> CREATE TABLE testtable (col1 varchar);

CREATE TABLE

mytestdb=> insert into testtable values('hello');

INSERT 0 1

mytestdb=> select * from testtable;

col1

-------

hello

(1 row)

mytestdb=> \dt

List of relations

Schema | Name | Type | Owner

--------+-----------+-------+---------

public | testtable | table | ranjith

(1 row)

mytestdb=>

Note that by default the schema used is Public. You should create a specific schema for your users

Configure PostgreSQL Service to Start at Boot

[root@station1 ~]# chkconfig --list postgresql

postgresql 0:off 1:off 2:off 3:off 4:off 5:off 6:off

[root@station1 ~]# chkconfig --level 234 postgresql on

[root@station1 ~]# chkconfig --list postgresql

postgresql 0:off 1:off 2:on 3:on 4:on 5:off 6:off