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

Migrating IBM DB2 from 32 to 64-bit platform

The best way to move your database from one server to another is a backup/restore procedure. You can also use db2move utility but it’s not much of help here because it moves only the tables.

If you use a built-in compression to reduce size of your backups which is a normal thing to do then if you’ll try to restore a backup made on a 32-bit architecture to a 64-bit platform using a command like this

RESTORE DATABASE mydb FROM “D:\dbbackup” TAKEN AT 20111215030019 TO “D:” WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT ROLLING FORWARD WITHOUT PROMPTING;

then you will get an error

SQL2570N  An attempt to restore on target OS “NT-64” from a backup created on source OS “NT-32” failed due to the incompatibility of operating systems or an incorrect specification of the restore command.  Reason-code: “2”.

The reason why this happens is a compression library. Each time you make a compressed backup DB2 puts a compression library into a backup itself. When restoring on a 64-bit platform DB2 refuses to use a 32-bit library. There are two solutions to this problem. First is to make a plane uncompressed backup. But if your backup file is quite large then it can be rather painful to move it between servers. Second solution is to add COMRLIB clause into the original query

RESTORE DATABASE mydb FROM “D:\dbbackup” TAKEN AT 20111215030019 TO “D:” WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 COMPRLIB C:\SQLLIB\BIN\db2compr.dll WITHOUT ROLLING FORWARD WITHOUT PROMPTING;

If you restore to an existing database you will get SQL2539 warning. Which just means that original database files will be deleted.

Use this workaround description at IBM site as a reference: IY71307: SQL2570N when restoring a compressed backup image to another platform or wordsize.

DB2 advanced concepts

Now lets look at some advanced level terms of DB2 product. First comes Buffer Pool which is an extremely useful object for tuning DB2 performance. In fact Buffer Pool is just a region of memory where database keeps indexes and data. Initially pools are small. So it’s a rule of thumb to always tune them during database installation.

Next one is Transaction Logs. Transaction Logs are of big importance to any database. They keep track of transactions happening in database and in case of failure allow to “roll-forward” to particular point in time. In other words if you have full database backup made at T0 and transaction logs from that point and something bad happens at T1 then you can always recover your database to any point in time between T0 and T1 by recovering from full backup and then roll-forwarding.

Large Objects or LOBs are objects like video, audio, scanned documents, etc. It’s a type which is available during table creation. One special thing about LOBs is that they are logically created as a column of table which is stored in particular tablespace but are physically located in separate so called large object tablespace.

Structure of DB2 database

Now lets talk about databases which is a more interesting topic for developers. If you show up here then you apparently know that databases consist of tables. But there is another slice between them called schema. Schema is just a convenient aggregator of database objects. If your database is bigger than 10 tables then logically separating them into different schemas would probably be beneficial for you.

Next term is view. Using views you can look at your tables from different angle, so to speak. So if you have your table in place and don’t want to change it but it’s more convenient sometimes to use it with different structure from SQL-query then use views.

And a last but not least and probably a most interesting feature is index. Indexes help database to increase performance when searching for data programmer requests. Instead of comparing all fields you indicate in query to find necessary data, database generates indexes for each row in advance, then generate index from query when you run it and compare indexes to find requested data. Basic idea here is to to compare two numbers instead of strings and/or several numbers. It’s just faster.

General view of IBM DB2 architecture

No one would argue that DB2 is one of the leading (R)DBMS products in the market. As any other powerful software product it has complex architecture. Here I’d like to introduce some basic terminology of it in series of posts because sometimes this terminology can become somewhat vague.

Lets kick off by explaining some general view of DB2. Each IBM DB2 installation has following basic levels of architecture:

• Instance can be understood as a completely independent environment with it’s own security configuration, resource allocation and contains databases and partitions isolated from all other instances. Each instance has its own system processes which manage data. Instance may contain several databases.
• Database is a most familiar term. It’s a logical unit which holds your data. It has complicated structure which we will explain later on. Database can reside in one or more partitions.
• Partitions (or nodes) is a way of creating DB2 database cluster for the sake of higher performance. You can split your database onto several servers where each of them will have their own chunk of data. Since more servers means more CPU cores, memory and disk I/O it’s a natural way of scaling DB2. Particular storage resource is called Container.
• Database is then mapped onto several Tablespaces. Tablespace allows you to manage how database tables are held on your storage resources. For example you can hold your data in files as well as on raw hard drives (which is faster). In addition it’s possible to put frequently used data on SAS hard drives and rarely used on SATA by means of creating two different tablespaces. On top of that you can set up a different page size for each of your tablespaces.

Here is the basic idea of what DB2 represents from the view of systems administrator. To better understand a folding of different levels please refer to this well-known among dbtwoers picture: link.