UPDATE:
I still get hit on this blog post – So just for the record this blog post is obsolete! If you are running vCops I suggest you upgrade to vRops asap and then take a look at my blog post around the vRops API – vRops API consumed with Powershell
I posted this on twitter and postbin earlier this year, but now that I’ve got the site up and running, I thought I would write it here also. This started as a wish to get some of the data out of vCops in order to do a centralized capacity management. I started out looking for a solution and quickly found Clint Kitsons blog post on “Unofficial VMware vCenter Operations Powershell Module” I played around with it for sometime, but it wasn’t working for me – After playing around with the script I finally found out why it wasn’t working for me, basically the script executes a sql statement and it simply timed out. I’m guessing there is to much info in the vCops db. So from here on I started looking for a different solution and ended up with opening up for direct access to the vCops db.
If you like I can’t get data out of the powershell module (It seems so cool – would love for it to work), you can always open up for remote db access like I did.
SSH to the Analytics VM and do the following
First we need to setup some security, to limit the security risk of exposing data to unwanted ip scopes. By default there is not open for remote access at all.
vi /data/pgsql/data/pg_hba.conf
Add line:
host all all 192.168.0.0/24 md5
Then you need to open the port used in the firewall
vi /usr/lib/vmware-vcops/user/conf/install/vcopsfirewall.conf
Add lines:
# postgres
TCPPORTS="$TCPPORTS 5432"
Restart the firewall to get the rule loaded
service vcopsfirewall restart
Finally restart the vCops db
sudo -u postgres /opt/vmware/vpostgres/9.0/bin/pg_ctl -D /data/pgsql/data -m immediate restart
That’s all 🙂
Well here’s a few other tricks that might come in handy
List db users
su postgres
psql
\du
Get the password for the alive user(Default vCops user)
su admin
vcops-admin get_db_password
Create db user and assign “read only” rights
su postgres
psql alivevm
CREATE USER USERNAME WITH PASSWORD 'PASSWORD';
GRANT CONNECT ON DATABASE alivevm TO USERNAME;
GRANT SELECT ON ALL TABLES IN SCHEMA public to USERNAME;
Remember when doing upgrades of vCops all of these settings might be overwritten, so you will probably have to apply them again.
2 thoughts on “vCops remote DB access”
Hi,
Thanks for this article 🙂
You have to add this to /data/pgsql/data/postgres.conf file to allow postgres listening on its address:
listen_addresses = ‘localhost, 172.20.20.2,’
Have a nice day
Erratum :
listen_addresses = ‘localhost, 172.20.20.2,public_address’