Connecting to embedded PostgreSQL on vCSA 6.5 to unlock NSX Controller settings

In my home lab I used the vCenter Server Appliance all the times and l due to limited resources I need to change the NSX Controller memory and cpu settings. This is not possible by default because these VMs are protected. In order to remove the protection you need to delete some entries from the VCDB database. Needless to say this it’s unsupported but hey, it’s just my home lab 🙂

There are 2 methods to access the embedded PostgreSQL VDCB database:

1. Execute SQL queries from VCSA cli
2. SQL commands from pgAdmin

1) Execute SQL queries from VCSA cli

This method is widely coverered on the internet and VMware even released a KB for this, see https://kb.vmware.com/s/article/2147285

2) Using pgAdmin to access VCDB embedded on VCSA 6.5

I’d like to focus on this method because it’s not well documented so I’m going to share my experience.

1. From VCSA shell open firewall port 5432 from iptables.

   iptables -I INPUT 1 -p tcp --dport 5432 -j ACCEPT

   check the rules has been added by running

   iptables --list-rules

   

2. save iptables configuration

   /usr/sbin/iptables-save

   d

3. Allow PostgreSQL to accept external connections

   vi /storage/db/vpostgres/pg_hba.conf

   Add a line like the following for each subnet you need to allow connections from.

   host all all <subnet-cidr> trust

   Here I have allowed 3 subnets:
   

4. restart PostgreSQL service and check its status

   /usr/lib/vmware-vmon/vmon-cli -r vmware-vpostgres

   

5. install pgAdmin from https://www.pgadmin.org/download/
6. configure the server db connection as following:

   

7. get the username vc password
   cat /etc/vmware-vpx/vcdb.properties

   

8. Connect

   

   you should be able to browse the VCDB
   

Remove protection from NSX Controller VMs

A great use case to access the VCDB is to unlock the protected NSX controllers VMs.

1. start the Query tool

   

2. run the following query

   select * from vpx_disabled_methods

   

3. open another Query editor and run the following query to match the vm-<id> with the actual vm id coming from the vpx_entity table

4. delete the record that matches with the NSX controller vm, in my case here they were vm-204, vm-205 and vm-221. You can use the following query:

    delete from vpx_disabled_methods where entity_mo_id_val = 'vm-204'

5. check the records are gone using the same query at point 2)
6. restart the vCenter Server (please note that the following commands are specific on PhotonOS, as I wrote this article I was using VCSA 6.5 (as per title)

   systemctl stop vmware-vpxd.service
   systemctl start vmware-vpxd.service

7. Once vCenter Server is back you should be able to edit the NSX Controller settings to decrease memory, memory reservation and CPUs.
   
   and here I have it, 3 controllers running at 2GB and 1vCPU
   
   NSX controller settings from HTML5 client
   

Enjoy.