Connecting to PostgreSQL Database Backing VMware Products

Most of the VMware products these days are standardised on PostgreSQL. Yes, you can still deploy vCenter for Windows, for instance, and use MS SQL or Oracle as a back-end database, but it’s now deprecated and vSphere 6.7 is the last release where it’s supported. Other products, like vRealize Automation are moving in the same direction.

VCSA, vRA, vRO are all distributed as appliances and shouldn’t be modified in any way by the end user. But I’ve had times before when I needed to directly connect to the PostgreSQL database to better understand certain parts of the product. One of the recent examples was encryption in vRO. I needed to ensure that the passwords I save in SecureString attributes (the ones shown as asterisks) in my workflows are not kept as plain text in vRO. So let’s see how I validated this assumption by looking at the vRO database.

vRO Database

I first SSH’ed into the appliance and connected to the database using PostgreSQL interactive terminal:

# psql vmware postgres

I then listed all database table names:

> SELECT * FROM pg_catalog.pg_tables;

When I found the table I was looking for, I listed its contents:

> SELECT * FROM vmo_workflowcontent;

And simply searched for my attribute name in the output, which was encrypted indeed.

Exporting the Database

You won’t always know what table you’re looking for, so the easiest way to go about it is to simply export the whole database in plain text and use search in a text file:

# su -m -c “/opt/vmware/vpostgres/current/bin/pg_dump -Fp vmware > /tmp/vmware.sql” postgres

“-Fp” here is for plain text (default is custom format, which is compressed), “vmware” is the database and “postgres” is the user.

VCSA and vRA Databases

You will find that database names aren’t the same for different products, for instance vCenter’s database name is “VCDB” (capital letters) and vRA is “vcac” (username is also “vcac”). So if you need to connect to VCSA database you will use the following syntax:

# psql VCDB postgres

For vRA it will look like this:

# psql vcac vcac

Then you can use the same approach demonstrated for vRO to read table data or simply export the whole database.

Conclusion

I hope it helps you with your tinkering adventures. Just make sure to use this only for research and not change anything in the database, unless specifically advised by GSS.

Advertisement

Unable to Delete vCenter Endpoint in vRealize Automation

vRealize Automation Error

More than once in my experience I’ve had a need to delete an endpoint in vRealize Automation. Maybe configuration has changed or you simply made a typo in vCenter hostname or credentials. Once you’ve specified vCenter address and saved the endpoint you can no longer change it (only delete and re-add).

But even when you try to delete it, you will get an error something along the lines of:

You cannot delete this endpoint because 1 compute resources and 0 storage paths use it.

CloudClient Error

There is a KB article that walks you through the process of how to do that using a special tool called CloudClient: Error “This endpoint is being used by # compute resources and # storage paths and cannot be deleted” when you attempt to delete an endpoint in vRA 7.x (2150548)

But even that approach not always work. When you run this command from the KB article “vra computeresource inactive list” you may get the following error:

Error: Something went wrong while processing your request. Please check the application logs for details.

Solution

There is almost no mention of this second error on the Internet and I can see how someone can keep banging his head trying to solve it, so I thought I’d share a solution here. And it’s simple – open a GSS ticket. They can delete the endpoint for you. If you see this error, there’s no other way that I know of to solve this problem without involving GSS.

Clean-up

You can see an error similar to the following in vRA logs if you didn’t stop proxy agents before deleting the endpoint:

Error processing ping response
Error occurred while executing stored proc usp_InsertUpdateHost The INSERT statement conflicted with the FOREIGN KEY constraint “FK_ManagementEndpoint_Host”. The conflict occurred in database “vRa_IaaS”, table “dbo.ManagementEndpoints”, column ‘ManagementEndpointID’.
The statement has been terminated.
Inner Exception: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_ManagementEndpoint_Host”. The conflict occurred in database “vRa_IaaS”, table “dbo.ManagementEndpoints”, column ‘ManagementEndpointID’.
The statement has been terminated.

All you need to do to get rid of it is restart your proxy agents.

Conclusion

Hope this post saves someone the hassle of hours searching for the answer in blogs and forums.

ESXi Host Maintenance with Zerto

Zerto replication is quite easy to configure. Once you have a Zerto Virtual Manager (ZVM) and Virtual Replication Adaptors (VRA) up and running at both sites, you can start adding your virtual machines to replication. There is, however, one question which comes up a lot from the operations point of view. What if you have replication going between the sites and you need to put one of your ESXi hosts into maintenance mode, would that break the replication? The answer is as always – it depends.

Source Site

In Zerto you typically have VRAs installed on each of the hosts at both sites and traffic going one way – from Production data centre to DR. Now, if you want to do maintenance on one of the hosts where VMs are being replicated FROM (Production site) then all you need to do is vMotion VMs to the remaining hosts. Zerto fully supports vMotion and the process is seamless. When VMs are moved to other hosts, VRAs on these hosts automatically pick them up and replication continues without user’s intervention.

Destination Site

If you want to do maintenance on one of the hosts where VMs are being replicated TO (DR site), then this is where you need to be more careful. VMs replicated by Zerto are not shown in vCenter inventory and obviously can’t be moved using conventional vMotion method. This is done from ZVM’s GUI.

In ZVM find the host you want to put into maintenance mode on the Setup tab and in the More drop-down menu select Change VM Recovery VRA. Select the replacement host where you want to redirect VM replication to and click Save. What this option does in Zerto is somewhat similar to what vMotion does in vSphere – it migrates VMs between VRAs.

Once you hit the button, VMs’ RPO will start to grow until the migration is finished. In my case for 12 VMs the process took about 5 minutes to complete. If you have dozens of protected VMs on each of the VRAs, it may take significantly longer. If it’s a concern, you may want to allocate a maintenance windows for this activity.

You will also get a warning that the migration will result in a bitmap-sync. Bitmap Sync tracks the changed blocks on a VM when replication to the destination VRA is interrupted. The amount of changed data over a 5 minute period should be reasonably small. And in my experience VMs get back in sync after a migration very quickly.

When all replicated VMs are moved to another recovery host, you can vMotion out any VMs you may have running on the host, shut down the VRA and put the host into maintenance mode to carry out the maintenance activities.

Once that’s finished, just do the reverse. Disable maintenance mode on the host, boot up the VRA and move back the migrated VMs. In the Change VM Recovery VRA dialogue you can select a completely different set of VMs to move back. As long as you keep them balanced between all VRAs in your cluster you should be good.

Zerto Overview

Zerto is a VM replication product which works on a hypervisor level. In contrast to array level replication, which SRM has been using for a long time, it eliminates storage array from the equation and all the complexities which used to come along with it (SRAs, splitting the LUNs for replicated and non-replicated VMs, potential incompatibilities between the orchestrated components, etc).

Basic Operation

Zerto consists of two components: ZVM (Zerto Virtual Manger) and VRA (Virtual Replication Appliance). VRAs are VMs that need to be installed on each ESXi host within the vCenter environment (performed in automated fashion from within ZVM console). ZVM manages VRAs and all the replication settings and is installed one per vCenter. VRA mirrors protected VMs I/O operations to the recovery site. VMs are grouped in VPGs (Virtual Protection Groups), which can be used as a consistency group or just a container.

Protected VMs can be preseeded  to DR site. But what Zerto essentially does is it replicates VM disks to any datastore on recovery site where you point it to and then tracks changes in what is called a journal volume. Journal is created for each VM and is kept as a VMDK within the “ZeRTO volumes” folder on a target datastore. Every few seconds Zerto creates checkpoints on a journal, which serve as crash consistent recovery points. So you can recover to any point in time, with a few seconds granularity. You can set the journal length in hours, depending on how far you potentially would want to go back. It can be anywhere between 1 and 120 hours.

VMs are kept unregistered from vCenter on DR site and VM configuration data is kept in Zerto repository. Which essentially means that if an outage happens and something goes really wrong and Zerto fails to bring up VMs on DR site you will need to recreate VMs manually. But since VMDKs themselves are kept in original format you will still be able to attach them to VMs and power them on.

Failover Scenarios

There are four failover scenarios within Zerto:

• Move Operation – VMs are shut down on production site, unregistered from inventory, powered on at DR site and protection is reversed if you decide to do so. If you choose not to reverse protection, VMs are completely removed from production site and VPG is marked as “Needs Configuration”. This scenario can be seen as a planned migration of VMs between the sites and needs both sites to be healthy and operational.
• Failover Operation – is used in disaster scenario when production site might be unavailable. In this case Zerto brings up protected VMs on DR site, but it does not try to remove VMs from production site inventory and leave them as is. If production site is still accessible you can optionally select to shutdown VMs. You cannot automatically reverse protection in this scenario, VPG is marked as “Needs Configuration” and can be activated later. And when it is activated, Zerto does all the clean up operations on the former production site: shuts down VMs (if they haven’t been already), unregister them from inventory and move to VRA folder on the datastore.
• Failover Test Operation – this is for failover testing and brings up VMs on DR site in a configured bubble network which is normally not uplinked to any physical network. VMs continue to run on both sites. Note that VMs disk files in this scenario are not moved to VMs folders (as in two previous scenarios) and are just connected from VRA VM folder. You would also notice that Zerto created second journal volume which is called “scratch” journal. Changes to the VM that is running on DR site are saved to this journal while it’s being tested.
• Clone Operation – VMs are cloned on DR site and connected to network. VMs are not automatically powered on to prevent potential network conflicts. This can be used for instance in DR site testing, when you want to check actual networking connectivity, instead of connecting VMs to an isolated network. Or for implementing backups, cloned environment for applications testing, etc.

Zerto Journal Sizing

By default journal history is configured as 4 hours and journal size is unlimited. Depending on data change rate within the VM journal can be smaller or larger. 15GB is approximately enough storage to support a virtual machine with 1TB of storage, assuming a 10% change rate per day with four hours of journal history saved. Zerto has a Journal Sizing Tool which helps to size journals. You can create a separate journal datastore as well.

Zerto compared to VMware Replication and SRM

There are several replication products in the market from VMware. Standalone VMware replication, VMware replication + SRM orchestraion and SRM array-based replication. If you want to know more on how they compare to Zerto, you can read the articles mentioned in references below. One apparent Zerto advantage, which I want to mention here, is integration with vCloud Director, which is essential for cloud providers who offer DRaaS solutions. SRM has no vCloud Director support.

References

• vSphere Replication and Zerto – What’s the Difference?
• 5 Differences Between Zerto and SRM
• A Comparison of Hypervisor-based Replication vs. Current and Legacy BC/DR Technologies