Postgresql Database Installation on Linux Environment

POSTGRESQL  COMMANDS Purpose Command Login to postgresql            : psql -d dbname -U usernameConnect to other DB: /connect dbname Describe table Structure: \d + tablename Show all tables: \d Show all databases            : \l Create new database: create database dbname Show any table rows: select  * from table name; Change user password: ALTER USER un WITH PASSWORD 'mypass' ; Create new user: create user usrnm with password 'password'; Version: select version() Delete database: DROP DATABASE databasename; Help for any command: \?

Postgres Installtion Steps:

step1-

yum install postgresql postgresql-server

step2-

# initialize the database first.....!!

[root@localhost lib]# service postgresql initdb

Initializing database:                                     [  OK  ]

step3-

On the services with chkconfig command

[root@localhost lib]# chkconfig postgresql on

[root@localhost lib]#

step4-

Start the database---

[root@localhost lib]# service postgresql start

Starting postgresql service:                               [  OK  ]

step5-

Create the admindb like below

su - postgres

createdb admindb

and now alter the postgres user with password  like below

# psql -U postgres (it will directly connect to database)

> ALTER USER postgres WITH PASSWORD '<user_password>';

step6-

To connect to DB from linux terminal,  We need to made the below changes in the file.

>cd /var/lib/pgsql/9.2/data/;ll

>vi pg_hba.conf

Changes the below entries in the file
local   all             all                                     ident ## Change to md5

# IPv4 local connections:

host    all             all             127.0.0.1/32            ident ## Change to md5

# IPv6 local connections:

host    all             all             ::1/128                 ident ## Change to md5

step7-

Restart the postgres

> service postgresql restart

Now connect to DB from linux console directly

[root@localhost data]# psql -U postgres

Password for user postgres: # Give password as password

Errors while installing postgresql Database :

---->

[root@localhost data]# su - postgres

-bash: TMOUT: readonly variable

-bash-4.1$ createdb admindb

createdb: could not connect to database template1: FATAL:  could not open relation mapping file "global/pg_filenode.map": No such file or directory

sol:

To rectify the above error, check for postmaster file ( ps -ef grep post )

kill the pid of the process ( postmaster file) and then restart the postgresql DB

---->

If any Fatal error get then kill all the java sessions and postgres sessions and restart the postgres DB then it will work: :)