In this blog post, we are going to discuss causes, reasons, and solutions related to the following error “Unable to Create a PostgreSQL Instance”. Here are the error messages you can receive:
Invalid PostgreSQL User Name and Password.
or
Connection to PostgreSQL server failed.
All the PostgreSQL Server error messages we are going to review in this article you can face during the PostgreSQL instance creation. Below you can find 4 causes and solutions on how to fix this or that error message. So let’s start!
Unable to Create a PostgreSQL Instance – Cause 1
7991 21550700 10/19 04:12:25 ### libpqInterface :: ConnectDb() - Connection Info string: port = '5432' dbname = 'postgresql' user = 'admin' connect_timeout = '' password = ******* 7991 21550700 10/19 04:12:25 ### libpqInterface :: ValidateConnection() - eRRoR -- PQreset failed to reset the connection. Error:FATAL: no pg_hba.conf entry for host "[local]", user "admin", database "postgresql", SSL off FATAL: no pg_hba.conf entry for host "[local]", user "admin", database "postgresql", SSL off
“[local]” here is related to a UNIX socket connection. So, there is no record for pg_hba.conf file to enable the socket connection from cvd that generates psql -u postgres [username] from working. This can be tested from the client as root.
Solution 1
Make sure that pg_hba.conf file has an applicable line to match the user. With the help of the default line below all users can connect to the client via the UNIX socket, as part of a default PostgreSQL install. It may be hashed out.
local all all md5
As the alternative, you can use the following variation to make it secure:
local all [username_entered_in_gui_for_instance_creation] md5
With the help of this users can connect to all databases locally via the UNIX socket.
Unable to Create a PostgreSQL Instance – Cause 2
18041 1184f700 04/13 05:23:14 ### libpqInterface :: libpqInterface() - eRRoR -- Exception in [libpqInterface :: libpqInterface] Type:[APPLICATION_EXCEPTION] Msg:[Lib dir is not present] 18041 1184f700 04/13 05:23:14 ### ExecutePostgre::init() - eRRoR -- Exception in [libpqInterface :: libpqInterface] Type:[APPLICATION_EXCEPTION] Msg:[Lib dir is not present] 18041 1184f700 04/13 05:23:14 ### PostgresBrowseAgent::verifyUserInfo() - connection failed 18041 1184f700 04/13 05:23:14 ### PostgresBrowseAgent::verifyUserInfo() - exepg.postgre_exe failed
Solution 2
Make sure the correct PostgreSQL lib directory is specified in the instance creation dialog. If it contains libpq.so library file and other related library files that everything is OK. The libpq.so library is used to execute queries and to connect to the database.
- Execute the following command to locate the lib directory:
bash-3.2# ./pg_config --libdir
Here is the output example:
/PostgreSQL/9.2/lib
- If you have noticed that the lib path contains libpq.so.x.x instead of libpq.so, make a symlink libpq.so to libpq.so.x.x just before attempting instance creation.
bash-3.2# ln -s /usr/lib64/libpq.so.x.x /usr/lib64/libpq.so
Try again to create the instance, now you should be able to do it successfully.
Unable to Create a PostgreSQL Instance – Cause 3
24576 1026b700 11/12 08:14:42 ### PostgresBrowseAgent::verifyUserInfo() - m_PostgresServer->GetConfigFilePath() = Simpana 24576 1026b700 11/12 08:14:42 ### libpqInterface :: ConnectDb() - Connection Info string: port = '5432' dbname = 'database' user = 'admin' connection_timeout = '' password = ******** 24576 1026b700 11/12 08:14:42 ### libpqInterface :: IsSuperUser() - eRRoR - Exception in [libpqInterface :: IsSuperUser] Type:[APPLCATION_EXCEPTION] Msg:[The user is not a super user] 24576 1026b700 11/12 08:14:42 ### libpqInterface :: libpqInterface() - eRRoR - Exception in [libpqInterface :: IsSuperUser] Type:[APPLCATION_EXCEPTION] Msg:[The user is not a super user] 24576 1026b700 11/12 08:14:42 ###ExecutePostgre::init() - eRRoR - Exception in [libpqInterface :: IsSuperUser] ] Type:[APPLCATION_EXCEPTION] Msg:[The user is not a super user] 24576 1026b700 11/12 08:14:42 ### PostgresBrowseeAgent::verifyUseerInfo() - connection failed 24576 1026b700 11/12 08:14:42 ###PostgresBrowseeAgent::verifyUseerInfo() - exepg.postgre.exe failed 24576 1026b700 11/12 08:14:42 ### PostgresBrowseeAgent::BrowseDBs() - verifyUser failed (err=) 24576 1026b700 11/12 08:14:42 ### PostgresBrowseeAgent::DoBrowse() - BrowseDBs failed. M_errorNum = 1577058305 24576 1026b700 11/12 08:14:42 ### PostgresBrowseeAgent::SendBrowseResponse() - Sending mstype = POSTGRESQL_BROWSE_FAILED 24576 1026b700 11/12 08:14:42 ### Main() - DoBrowse() failed
There is a missing special attribute usesuper or superuser in the instance creation dialog box.
Solution 3
Here are the simple steps how you can add the superuser attribute to the user:
- Connect to PostgreSQL and check if the usesuper attribute is incorrectly set using the following query to:
select * from pg_user;
- After you have connected to PostgreSQL as a superuser, run the query below to add the attribute:
PGSQL> ALTER USER username WITH superuser;
- Check all using the following command:
select * from pg_user;
Unable to Create a PostgreSQL Instance – Cause 4
25925 ae3d6740 09/10 06:12:57 ### libpqInterface :: ConnectDb() - Connection Info string: port = '5433' dbname = 'postgresql' user = 'admin' connect_timeout = ' password = ******* 25925 ae3d6740 09/10 06:12:57 ### libpqInterface :: ValidateConnection() - eRRoR -- PQreset failed to reset the connection. Error:FATAL: Peer authentication failed for user "postgres" FATAL: Peer authentication failed for user "postgres" 25925 ae3d6740 09/10 06:12:57 ### libpqInterface :: ValidateConnection() - eRRoR -- Exception in [libpqInterface :: ValidateConnection] Type:[POSTGRE_EXCEPTION] Msg:[PQreset failed to reset the connection] 25925 ae3d6740 09/10 06:12:57 ### libpqInterface :: libpqInterface() - eRRoR -- Exception in [libpqInterface :: ValidateConnection] Type:[POSTGRE_EXCEPTION] Msg:[PQreset failed to reset the connection] 25925 ae3d6740 09/10 06:12:57 ### ExecutePostgre::init() - eRRoR -- Exception in [libpqInterface :: ValidateConnection] Type:[POSTGRE_EXCEPTION] Msg:[PQreset failed to reset the connection] 25925 ae3d6740 09/10 06:12:57 ### PostgresBrowseAgent::verifyUserInfo() - connection failed 25925 ae3d6740 09/10 06:12:57 ### PostgresBrowseAgent::verifyUserInfo() - exepg.postgre_exe failed 25925 ae3d6740 09/10 06:12:57 ### PostgresBrowseAgent::BrowseDBs() - verifyUser failed (err=)
Solution 4
The software tries to connect to PostgreSQL as a root user while instance creating. Add the following mappings pg_hba.conf and the pg_ident.conf files to make authentication successful.
- You need to add the corresponding map name configured for the PostGres DB user in the pg_hba.conf file. Look at the example below.
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres peer map=testmap
- Now add the mapping between the PostGres DB user and the system user ‘root’ in the pg_ident.conf file. Look at the following example.
# Put your actual configuration here # ---------------------------------- # MAPNAME SYSTEM-USERNAME PG-USERNAME testmap postgres postgres testmap root postgres
With the help of the next command reload PostgreSQL Server:
./pg_ctl -D /opt/PostgreSQL/9.1/data reload