Backing up and restoring the vCenter vPostgres database

As part of the vCenter Server installation process, you are presented with two options as to where you want the VCDB (vCenter database) hosted. For small scale deployments, and perhaps even smaller budgets, the embedded PostgreSQL database will do just fine. This option is equally available for vCenter Server for Windows and vCenter Server Appliance (vCSA) 6.0, both of which, finally, enjoy identical feature sets. For complex environments, the choice lies between an Oracle or a Microsoft dedicated db solution.

The focus of this article is on how to backup and restore the PostgreSQL database using nothing but VMware supplied Python scripts. As a note of interest, I should technically be referring to the database as vPostgres, which is how you’ll find it listed in VMware literature. Essentially, VMware tuned and optimized the open source PostgreSQL database appositely for vCenter Server, hence the name.

I must also stress that an image-based backup is the only VMware supported solution when it comes to fully restoring vCenter Server, reason being that SSO and licensing data, to name a couple, are stored independently of the VCDB database.  Simply restoring the VCDB database, in the event of a complete server failure, will not suffice. Nevertheless, it pays to know how to backup and restore the VCDB database just in case you need to recover from, say, database corruption; highly unlikely but never underestimate Murphy’s Law. Given this type of scenario, a VCDB restore is, in my opinion, preferable to performing a full server restore as a first means towards a fix more so if you’re running vCenter on a physical server.

Examining the vPostgres database

In this section I’ll be writing about vCenter Server for Windows but most of it applies just as well to vCSA, the full details for which can be found here.

If you have access to a vCenter Windows instance, RDP to it, open a command prompt window and type;

set | findstr VMWARE_

The command lists the environment variables used by vCenter Server (Fig. 1), one of which is the VMWARE_DATA_DIR, the path to the vPostgres vCenter Server database (VCDB).

Figure 1 – Environment variables in use by vCenter Server

Next, switch over to the directory holding the database if you would like to examine its folder and file structure (Fig. 2). An explanation of each and every component is given here;

cd %VMWARE_DATA_DIR%\vpostgres
dir

Figure 2 – PostgreSQL database physical layout

We’ll also have a look at the ODBC connection created and used by vCenter Server to, well, establish a connection with the database. To view the ODBC connection settings, launch the ODBC Data Sources tool accessible from under Administrative Tools or the Tools menu (Fig. 3) in Server Manager.

Figure 3 – Running the ODBC Data Sources tool in Windows Server 2012

Click on ODBC Data Sources (64-bit) and hit the Configure button at the next screen. Select the System DSN tab under which you should find an ODBC connection specific to vCenter Server (Fig. 4).

Figure 4 – The ODBC Data Sources (64 bit) tool

Figure 5 – vCenter Server ODBC configuration

As can be seen in Fig.5, a number of ODBC settings and options are given. I suggest you leave everything as is. We came here just to retrieve bits of information needed later on, namely the network port on which SQL is listening (5432) and the credentials used by vCenter to access the database.

By default the vc account is used, the password for which – stored in clear text, ouch! – is written to a file called vcdb.properties under %VMWARE_CFG_DIR%\vmware-vpx.

Figure 6 – Retrieving the password used for the ODBC connection

We now have all the information required to establish a connection to the database and have a better look at its internals. To accomplish this, a PostgreSQL management tool of sorts must be installed. I installed TeamPostgreSQL which runs as a listener service on the vCenter Server. It is easily accessed both locally and remotely over port 8082 using a standard internet browser.

Alternatively you can use the command line based tool psql you’ll find under C:\Program Files\VMware\vCenter Server\vPostgres\bin on the vCenter server. To establish a connection use the following command;

psql -d VCDB -U vc

Disclaimer: For this post, I’m using a vCenter Server set up purely for testing purposes. I would never dream about installing 3^rd party applications – more so any that open up network ports – on live / production servers. I reiterate that this is just for demonstration purposes and I strongly discourage you from replicating this part on live systems.

The VCDB schema

Fire up a browser and type http://<vCenter IP or DNS>:8082/teampostgresql/.

The following steps are captured in Figure 7.

1 – Click on the “Add Database Connection”.

2 – Specify the vCenter Server IP or DNS name, user and password as gathered from the previous section.

3 – Choose the VCDB database from the drop down box. You can set an optional identifier if needed.

4 – Depending on the environment’s size, allow some time for the view to populate.

Figure 7 – Connecting to the VCDB database using TeamPostgreSQL

Once the web part has finished populating, you should be able to view the VCDB database schema as shown in Fig. 8. The items you would probably be most interested in are tables, views and functions. In Fig. 8, I’ve expanded a view called vpxv_vms which returns a subset of properties of the vms hosted on my test vCenter Server.

Figure 8 – Opening a view from the VCDB database

Backing up the database

First things first, you need to download two Python scripts you’ll find attached to this KB article specifically the archive called 2091961_windows_backup_restore.zip.

Figure 9 – Zipped Python scripts for vCSA and vCenter Server for Windows

Unzip the archive to any folder on the Windows vCenter Server. I’m using c:\vcBackup.

Figure 10 – Backup and restore Python scripts

Using a command prompt, carry out the following;

cd %VMWARE_PYTHON_HOME%
python.exe c:\vcBackup\backup_win.py -p <previously copied password from vcdb.properties> -f c:\vcBackup\vc_040816.bak

Figure 11 – Backing up the VCDB database using the backup Python script

Simulating database corruption

To simulate a corrupted database, I’ve deleted a record holding data about a specific vm from the vpx_vm table.  If I then try to power on said vm in vSphere Client, the task fails to complete and stays put at the 75% mark.  Shortly afterwards, the vSphere client starts throwing connection errors due to a crashed VMware VirtualCenter Server service. The service will fail to start unless the database issue is first fixed.

Although this is a very unlikely scenario, you cannot really rule it out even if it’s bound to happen once every blue moon whether done maliciously or not. If luck deserts you, remedial options are limited to a handful;

• Call VMware support especially if you’re paying for it.
• Revert to a previous working snapshot, if your vCenter Server is virtualized.
• Perform a full server restore from the most recent image backup.
• Perform a database restore using the python script – assuming you’ve been taking regular db backups perhaps using a scheduled task. Take a snapshot or a full backup first.

I’ve documented the db corruption simulation and ensuing behavior in the following video;

Restoring the database

To restore the database, we need to use the second Python script called restore_win.py.

1. Make sure that the VMware VirtualCenter Server (vpxd.exe) and VMware Content Library Service services are both in a stopped state.
2. From a command prompt on the vCenter Server, run the following;

cd %VMWARE_PYTHON_HOME%
python.exe c:\vcBackup\backup_win.py -p <previously copied password from vcdb.properties> -f c:\vcBackup\vc_040816.bak

Restoring the database may take some time depending on its size. In the next video, I go through the process of restoring the database, starting up the services previously stopped and making  sure that I can log in vCenter Server just to see if I can manage the vm for which I deleted information from the database. Note that the restore process is sped up in the video. In reality, it took almost 30 mins even though I must say that I’m running a nested setup on an ESXi physical host that’s heavily used.

Summary

We have seen how using the VMware supplied Python scripts one can back up, and if required, restore the embedded vPostgres database on a Windows based vCenter Server. This option should be used to recover testing / dev environments and to a lesser extent production environments provided the latter are fully backed up using an image-based backup approach, this being the only method supported by VMware for reasons explained above.

You should also keep in mind that you can only restore the vCenter database to the same instance for which the backup was taken since vCenter employs other databases to store data related to SSO configurations, licensing and permissions. Restoring the database to a freshly installed or secondary vCenter Server is definitely not supported and will in all probability fail to work. That said, I haven’t tried it yet and will make for an interesting project.

Anyway, hope you enjoyed reading this article. If you have any comments, please post them in the comments box below. I’ll try and reply to the best of my abilities.

[the_ad id=”4738″][the_ad id=”4796″]