OpenErp Database User Creation in postgresql

Postgresql Important Configuration File.
1)Configuration file: /var/lib/pgsql/data/postgresql.conf
2)Host Based Access file: /var/lib/pgsql/data/pg_hba.conf

1)How To Create the User.
$ createuser openerp -P
Enter password for new role: (openerp)
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
Creating user as super user only that specified user only can able to create the database.
2)Create Database with specified user.
#CREATE DATABASE mydb WITH OWNER ramesh;
Note: Complete with semicolon then only it will execute the command properly.
3) Psql.
\l :List databases
\c database-name :List databases
\d :List tables in database
\d table-name :Describe table
4)Delete User and Database.
> DROP DATABASE DBASE-NAME;
> DROP USER USER-NAME;
5)How to Backup & Restore Database.
pg_dump Database_name;
pg_restore Database_name;
NOTE: hba.conf file if we mention authentication is “PASSWORD”
then in the $HOME/.openerpserver we have to mention the password.
Or else which method we were connecting we have to give password or without password we can't connect to the server.
if authentication type as “trust”
without password we can connect the database.

we have to properly create
1)database user
2)database
3)Configured hba.conf (Local & Host)

Verification For Database & User Connectivity
Once Install and configure Database properly, we have to access from the Outside or Inside Network.
#psql -h <hostname> -d <database-name> -U <database-user-name> -W

Step By Step Trouble Shooting Method
LEVEL 1) Check whether that User & Database is properly created or not.
If it properly created then try to login with database.
# psql -d <database> -W
LEVEL 2)Then login with database and user
# psql -U openerp -d <database> -W
LEVEL 3)If it successful, For then try to login with specify the IP Address.
#psql -h [IP ADDRESS] -U [USERNAME] -d [DATABASE]
If it is not properly configured. Then we will get the following error message

Error And Solution
Error 1)
# psql -h localhost -U openerp -d openerp
psql: FATAL: Ident authentication failed for user "openerp"
Error 2)# psql -h 10.154.0.164 -U openerp -d openerp
psql: could not connect to server: Connection refused
Is the server running on host "10.154.0.164" and accepting
TCP/IP connections on port 5432?
Error 3)
[2012-04-28 07:47:17,525] INFO:objects:initialising distributed objects services
[2012-04-28 07:47:17,735] INFO:dbpool:Connecting to openerp
bzr: ERROR: Not a branch: "/usr/lib/python2.4/site-packages/openerp-server/".
[2012-04-28 07:47:18,336] ERROR:dbpool:[01]:
[2012-04-28 07:47:18,339] ERROR:dbpool:[02]: Environment Information :
[2012-04-28 07:47:18,340] ERROR:dbpool:[03]: System : Linux-2.6.18-164.el5xen-i686-with-redhat-5.4-Final
[2012-04-28 07:47:18,340] ERROR:dbpool:[04]: OS Name : posix
Reason : For the above three Error Message Is happens due to Not properly configure database and database user with specified host IP address in pg_hba.conf.
Error 4) -bash-3.2$ createuser openerp -P
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
createuser: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
Reason: Not properly started the /etc/init.d/postgresql service.
/tmp/.s.PGSQL.5432 file is not properly created.
Error 5)-bash-3.2$ createuser openerp -P
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
createuser: could not connect to database postgres: FATAL: missing or erroneous pg_hba.conf file
HINT: See server log for details.
Reason : restart the postgresql service then try it.
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all trust
host Database-Name User-Name Client-IP Client-NetMAK Auth
Error 6)# psql -d openerp -U openerp -W
Password for user openerp:
psql: FATAL: no pg_hba.conf entry for host "[local]", user "openerp", database "openerp", SSL off
psql: FATAL: no pg_hba.conf entry for host "10.154.0.164", user "openerp", database "openerp", SSL off
We have to add the proper entry in pg_hba.conf file.
In local and Host tab For then try it.

Related post