Preparing vPostgres as external Database for Horizon Workspace

Since most of blog posts around the internet speak about how to make a basic installation of Horizon Workspace i decided to start a series of articles with the intention to show a full blown Horizon Workspace setup suited for production use.

A basic installation is good for a proof-of-concept or a lab environment but you will see very fast how it won’t scale and how it won’t even be supported by VMware itself since, for example, it uses an internal DB.

Even if the internal DB is based on VMware vFabric Postgres, or vPostgres, you still need to have a separate VM with vPostgres dedicated to be the Horizon Workspace database if you want a supported configuration in your production environment.

If you want to install vPostgres you have 2 options: download RPM packages and install them on a supported platform or download the OVA appliance and import it in vCenter.

I am no DBA and i find it not fun at all to learn database stuff if i don’t have to so it was a pretty easy choice for me. I also found that in the documents section of the Horizon Workspace page in the VMTN community there is a nice guide about that specific scenario.

The following is my experience deploying the OVA and preparing vPostgres for Horizon Workspace.

Install and Setup Notes
First thing first you have to download the OVA from the VMware website. There is a trial version to use which if i’m not mistaking last 60 days before expiring. Note that we will need version 9.1 even if there is a newer version simply because this is the version supported in Horizon Workspace at the moment of writing.

Once the download is complete we can use standard tools and procedures to import the virtual appliance in your environment:

Use vSphere Client or Webclient to deploy the OVF file.

The process is pretty straightforward all the way down to the network configuration.

Now we have to log in as ‘root’ and this can be sometimes tricky because we don’t get to choose what the password is since it’s randomly generated when the OVA is imported but no need to worry, the password is shown on the login screen. It’s tricky also because the password is really long and complex and sometimes i haven’t been able to type it in correctly, no matter what (there is also a time window for typing it which doesn’t help either). If this happens to you as well just redeploy the virtual appliance and hope for a more friendly password, i’m sure you won’t need to deploy it more than twice to successfully log in.

Once you are successfully logged in as ‘root’ we need to change the password as indicated in the login screen using the following command:

/opt/aurora/sbin/set_password

Note that the same password will be set to both the ‘root’ and ‘postgres’ users; this is worth noting because by default you can SSH into the virtual appliance only with the ‘postgres’ user.

If you want to change the network configuration you can run the following command:

/opt/vmware/share/vami/vami_config_net

While logged in as ‘root’ edit the following config file:

vi /var/vmware/vpostgres/current/pgdata/postgresql.conf

Find the “max_connections” value and change it from 100 to 600:

max_connections = 600

Add a line right under the “max_connections” line:

search_path = 'saas'

Save the file and change the directory into “/opt/vmware/vpostgres/current/bin” and su to the ‘postgres’ user:

cd /opt/vmware/vpostgres/current/bin
su postgres

Verify that you are indeed in the “/opt/vmware/vpostgres/current/bin” folder:

pwd

Now we are going to create a script that will take care of all the operations needed to prepare the database for Horizon Workspace, paste the following into a text file:

CREATE ROLE horizon LOGIN
        PASSWORD '123456'
        NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

ALTER ROLE horizon;

CREATE DATABASE saas
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;
GRANT CONNECT, TEMPORARY ON DATABASE saas TO public;
GRANT ALL ON DATABASE saas TO postgres;
GRANT ALL ON DATABASE saas TO horizon;

\connect saas;
CREATE SCHEMA saas AUTHORIZATION horizon;
SET search_path = saas;
CREATE EXTENSION citext SCHEMA saas;

Change the ‘PASSWORD’ to whatever you want to use when you will make a connection to this DB with the ‘horizon’ user and save the text into a file, for example “/tmp/createdb.do”.

Now let’s execute the script:

./psql -f /tmp/createdb.do

If all goes well you should get an output similar to this:

CREATE ROLE
ALTER ROLE
CREATE DATABASE
GRANT
GRANT
GRANT
You are now connected to database "saas2" as user "postgres".
CREATE SCHEMA
CREATE EXTENSION

At this point the documentation states to reboot the appliance so go ahead.

Before installing Horizon Workspace we will use a tool called pgAdmin to check that all went as expected; pgAdmin is available for almost every platform so just pick what you prefer and install it on your management workstation.

Let’s open pgAdmin and check everything is all right:

Let’s connect to the DB with the credentials we’ve set.

Check the user ‘horizon’ attributes which should look quite familiar.

Same thing for the DB properties.

All right! We are now ready to install Horizon Workspace and point it to this newly created database.

In the coming articles i’ll show you just how to do that.

Licensing Notes
vPostgres is not part of the Horizon Workspace product or the Horizon Suite so you will have to purchase it separately.

If you want to check the status of the trial license you can do it with the following command:

cat /opt/vmware/vFabric/vf.vpg.log

If you see the following in the log file it means you are running on trial license:

[Mon Jul 22 15:32:35 2013] WARNING getComponentLicenseState: No local serial numbers, and the server hasn't yet provided a license state, using default evaluation licenses, exception: No server license state available
[Mon Jul 22 15:32:35 2013] INFO getComponentLicenseState: No local serial numbers, nothing from server, using default evaluation licenses.

If you purchased a valid license you can use it by creating the following file and pasting your licensing information in it:

vi /etc/opt/vmware/vfabric/vf.vpg-serial-numbers.txt

Here’s a sample of what a valid license information should look like:

XXXXX-XXXXX-XXXXX-XXXXX-XXXXX    [quantity=20, expiration=Permament, addon=1]

After that we need to change permissions on the file so that vPostgres can read it:

chgrp vfabric /etc/opt/vmware/vfabric/vf.vpg-serial-numbers.txt
chmod 644 /etc/opt/vmware/vfabric/vf.vpg-serial-numbers.txt

Now a restart of the service is needed and then we can check the log again:

service aurora_mon stop
service aurora_mon start
cat /opt/vmware/vFabric/vf.vpg.log

You should be able to read that vPostgres found a valid serial number:

[Mon Jul 22 15:57:05 2013] INFO getComponentLicenseState: Using local serial numbers

Sizing Notes
In the community document i mentioned earlier you will also find some useful indications about how to size the vPostgres VM and how to edit the virtual appliance settings in details to support up to 30000 users with Horizon Workspace. You can also install a second instance of vPostgres and setup an HA configuration at the database level if vSphere HA is not good enough for your availability concearns.

Here’s a brief description of sizing requirements for 30000 users:

Supported Platforms for manual RPM installation
If you want to build your own VM and manually install vPostgres using RPMs keep in mind the following list of supported operating systems:

• Red Hat Linux RHEL 6.2 (64 bit)
• SUSE Linux SLES 11 SP 1 (64 bit)

Refer to the official documents for detailed instructions on how to manually install vPostgres on supported platforms using RPMs.