koushik_vmware
Enthusiast
Enthusiast

Connection failure during "PostgreSQL" DB connection from vRO

Jump to solution

Hi All,

I am getting connection error during connecting to PostgreSQL db from my vRO "add databse" work flow (located under library\SQL).

But I can connect to that PostgreSQL db from vRA appliance through SSH connection. There I am using the database 'vcac' and the user 'postgres'.

Please see below output when connecting via SSH to that vRA apliance.

pastedImage_2.png

   Below is the connection parameter passing to the WF input.

PostGreSQL_Connection_Settings.png

below is the error log I am getting in wf console:

Is there more configuration/settings are required to connect the vRA appliance PostgreSQL DB ?  please let me know.

PostGreSQL_Connection_Error_Log.png

Thnx,

0 Kudos
1 Solution

Accepted Solutions
drummoau
Enthusiast
Enthusiast

You can add the vcac database by following these steps

1.Getting the vRO DB password:
ogo to Appliance Management Console, "Admin" tab and check: "SSH service enabled" and "Administrator SSH login enabled"
olog into vRO with "root"
oexecute: xpath /etc/vcac/server.xml "//@password" 2>&1 | grep password | cut -d'"' -f2

               or

(tr '\n' ' ' < /etc/vcac/server.xml | grep -Eo "<\s*Resource.*name\s*=\s*(\"|')jdbc/cafe(\"|')[^<]*/?\s*>" | sed -nr 's:^.*\s+password\s*=\s*:fakecolumn :p' | sed -nr 's:/?\s*>::p' | cut -d' ' -f2 | cut -d'"' -f2 | cut -d"'" -f2

oexecute: /usr/sbin/vcac-config prop-util -d --p "${VCAC_DB_ENCRYPTED_PASSWORD}"

change ${VCAC_DB_ENCRYPTED_PASSWORD} with the output of the first command

ocopy the password
2.Enable none-SSL DB logging
olog into vRO as root
ogo to /storage/db/pgdata
o

edit pg_hba.conf by un-commenting line: host all all 127.0.0.1/32 md5

(note, if you're using clustered vRO you will need to add all nodes (e.g., vracafe01/32))

orestart PostgreSQL service
3.Run the workflow "Add a database"
oDatabase type is: PostgreSQL
oConnection URL is : jdbc:postgresql://127.0.0.1:5433/vcac
oSession mode: Shared Session
oUser name: vcac
oPassword: use the one from point 1

View solution in original post

0 Kudos
2 Replies
drummoau
Enthusiast
Enthusiast

You can add the vcac database by following these steps

1.Getting the vRO DB password:
ogo to Appliance Management Console, "Admin" tab and check: "SSH service enabled" and "Administrator SSH login enabled"
olog into vRO with "root"
oexecute: xpath /etc/vcac/server.xml "//@password" 2>&1 | grep password | cut -d'"' -f2

               or

(tr '\n' ' ' < /etc/vcac/server.xml | grep -Eo "<\s*Resource.*name\s*=\s*(\"|')jdbc/cafe(\"|')[^<]*/?\s*>" | sed -nr 's:^.*\s+password\s*=\s*:fakecolumn :p' | sed -nr 's:/?\s*>::p' | cut -d' ' -f2 | cut -d'"' -f2 | cut -d"'" -f2

oexecute: /usr/sbin/vcac-config prop-util -d --p "${VCAC_DB_ENCRYPTED_PASSWORD}"

change ${VCAC_DB_ENCRYPTED_PASSWORD} with the output of the first command

ocopy the password
2.Enable none-SSL DB logging
olog into vRO as root
ogo to /storage/db/pgdata
o

edit pg_hba.conf by un-commenting line: host all all 127.0.0.1/32 md5

(note, if you're using clustered vRO you will need to add all nodes (e.g., vracafe01/32))

orestart PostgreSQL service
3.Run the workflow "Add a database"
oDatabase type is: PostgreSQL
oConnection URL is : jdbc:postgresql://127.0.0.1:5433/vcac
oSession mode: Shared Session
oUser name: vcac
oPassword: use the one from point 1
0 Kudos
koushik_vmware
Enthusiast
Enthusiast

Thanks for your reply !!!. it should be working fine.

Thanks,

KM

0 Kudos