How to do Postgres Replication and Failover in VMware vFabric Data Director 2.7?

Last week VMware released vFabric Data Director 2.7. Among the many new features for various database, I wish to give a little more insight into my favorite ones which are regarding Postgres.

One of the big feature add from a broad perspective is support of Postgres 9.2 based managed database servers along with replication.  Lets look at how it is done in brief.

First vFabric Postgres 9.2 base DBVM needs to be uploaded into Data Director system resource pool and then converted into template. Note this template is different from vFabric Postgres 9.2 Virtual appliance and is available on the same location where Data Director 2.7 download exists.  Also this DBVM template is based on the latest PostgreSQL 9.2.4 core.

Next once the template is  loaded into the system resource pool it will show up in Base DBVMs section in System->Manage and Monor-> Templates-> BaseDBVMs

Then you would then  right click on the base DBVM and select “Convert to Base DB Template”.  Here you also have a new feature to add more disks (think Tablespaces in PostgreSQL) to the template. In Data Director 2.7, disks are added at the template level.

This process take some time and then it should show up in Base DB Templates. On the Base DB Template section right click on the newly created template and select “Validate”. This one creates a test database to see if it is successful or not before it can be rolled out to the Organizations and Database groups . Once the validation is a success, right click and select “Assign it to the resource bundle” and select the resource bundle that will have access to the template or create a new resource bundle to use the template.

Note in the Resource bundle creation steps lies yet another new  way to separate out IO on separate datastores since these may need different IO characteristics

The above image shows how the various types of datastores for your OS, Backup, Data and Logs (Data can be multiple location if you need multiple tablespaces)

Now all the Orgs/Database groups using the resource bundle will see the new vPostgres 9.2 template.

In a sample demo I created a database called MyDB using few wizard questions.

Now on the DB List we do a right click on the database to create more replicas

Couple of more quick wizard questions and a slave is ready. Similarly you can create more slaves of the same master. Now actually behind the scenes it does all the plumbing right from creating a new clone virtual machine from the master and setting it up using standard PostgreSQL replication on the newly created VM but from a user point of view it it is all point , click and done.

The database dashboard also has a new portlet to show bit more information about the replication.

To make a slave as a new master and it will give an option to  move all the other slaves to the new master select as follows:

After completion of the failover, the status shown will be similar to:

Where the original slave replica is now redirected to the new Master. The original master is kept as it is for Port-Mortem or other activities. This also gives a way to use it as new use cases like separating a slave for test purpose/DR purpose or separating “mirrors” etc. Best of all it uses all Standard PostgreSQL replication.

Of course one of the thing that it does not do is re-purpose the original Master as a slave of the new Master. For more details there is a perfect opportunity to find Heikki Linnakangas from VMware at PGCon in Ottawa this week and ask him the question after his session .. WHY??? :-)

PyPgDay 2013 - HA / Replication for PostgreSQL in Virtualized Environments

My presentation at PyPgDay 2013 on HA and Replication for PostgreSQL on Virtualized Environments

Using DVDStore with PostgreSQL

We now have support for PostgreSQL in the popular DVDStore Benchmark which stresses database using an emulated DVDStore e-Commerce website. DVDStore Benchmark is maintained by Dave Jaffe (Dell) and  Todd Muirhead (VMware).  It is an open source database test kit. The beauty of the benchmark kit is it allows the same web application being deployed either as

1. Java/Tomcat  and connect to the database,
2. Web Server/PHP and connect to the database,
3. IIS/ASP.NET connect to the database or
4. Direct connect to the database and invoking the business logic as stored procedures stored on the database itself.

Currently the PostgreSQL implementation details are as follows

1. Java/Tomcat using PostgreSQL JDBC driver,
2. Web Server/PHP  using  PHP-postgres modules which uses  libpq
3. Currently there is noIIS/ASP.NET web app  implementation for PostgreSQL
4.  Direct connect to PostgreSQL database and business logic implemented in stored procedures however the driver is implemented using .NET C# and requires Npgsql 2.0.11.0

Setup instructions for the database are relatively quite easy.

1. Download ds21.tar.gz  and also ds21_postgresql.tar.gz from  http://linux.dell.com/dvdstore/
2. Unzip them on the system running PostgreSQL
3. The default data size is 10MB. If you want a different size execute 'perl Install_DVDStore.pl' in the ds2 directory. (Expects perl to be available on the system. I used the option 100, MB , PGSQL, LINUX respectively for the options.)
4. Assuming you are logged on as the DB Owner and the database is on the localhost at port 5432, execute the script pgsql_create_all.sh in the ds2/pgsqlds2 directory. It will create a database "ds2", two users "ds2/ds2" and "web/web", create tables, load tables, create indexes, update sequences and finally run analyze. (The script needs to be modified slighly if the database is already hardened and you want to control the creation of database and the users.)

Setup for the actual load driver is probably easiest on  another Windows platform as follows as it was designed for .NET platform.

1. Download and install Windows SDK v6.1 and .NET 3.5 framework  on a Windows Client machine.
2. Once installed start the CMD prompt from Programs-> Windows SDKv6.1-> CMD Prompt.
3. Verify the above CMD prompt has path setup for gacutil in windows (Try 'gacutil/l')
4. Download Npgsql 2.0.11 for msnet35 and install the dlls using the gacutil.exe (Note other versions of Npgsql may have issues.)
•  gacutil/i Npgsql.dll
•  gacutil/i Mono.security.dll
•  gacutil/i policy-2.0.Npgsql.dll

With the above setup you can use the ds2webdriver.exe in ds2/drivers or the direct ds2pgsqldriver.exe in ds2/pgsqlds2. More on running the benchmark driver itself  in another post.

How does PostgreSQL HA works in vFabric Data Director?

Databases go down due to various reasons. Some reasons are known and some unknown.
Common reasons are hardware failure, software failure, database unresponsive, etc. What is considered as a failure is actually one of the tasks. Various DBA's use a simple select statement as a test to make sure that the database is up and working. But what does one do if that simple select statement fails. I remembers years ago I worked on a module which will start paging  engineers in a sequence (and eventually their managers if the engineers failed to respond back in a certain expected way).  In this email/text age, scripts will start sending out emails and text messages.  What we are is basically in the Event->React-> Respond mode of operation.

However true HA needs to lower downtime which can  only be done by having the mode of operation as Event->Respond->React. To explain that when such an event happens, do an automated response first and then React to wake the engineers up :-)

How do you set this up in vFabric Data Director? This can be achieved by selecting the database properties, selecting the Database Configuration tab and set "High Availability" to "Enable". This is also refered as One-Click HA setting.

Of course this assumes that your virtual Data Cluster is set properly for providing the high availability services. How do you set it up properly? Well you need atleast two ESXi Hosts so if one host fails, the other can cover for it. Also vSphere HA property has been enabled in the Virtual Data Center Cluster. Note these settings are all "required" for vFabric Database setup and a "supported" setup does mandate atleast two ESXi Hosts in order for HA to work.

Now that we have gone over the setup requirements, lets go over the scenarios on how the application or user sees it.  A user is connected to the database using the connection string. Something happens and the database goes down and the connection drops. Chances are if you reconnect again immediately it may fail. However with certain time which is expected to be less than 5 minutes (which we call our Recovery Time Objective or RTO)  by default, if you try again you can connect to the database again.

So what happens in the background? Well if it was Magic, we would not tell you. But it is not really magic though it feels like that. Here is what will typically happen in the background.
For some reason the PostgreSQL fails to respond anymore it could be a "hung" situation or the PostgreSQL server has died. There is a small heartbeat monitor which figures out the status of the database. If it notices that the hung situation or no DB server process, it will try to restart the database. If the database cannot be restarted (because the whole VM appliance cannot respond anymore), it will in novice terms kill the virtual machine. The vCenter Server which has its own heartbeat on the VM appliance will see that the Virtual Machine has died (irrespective of the Database Monitor which may not be working if the whole host dies), the vCenter Server will restart the VM appliance on another server.

Since shared-storage is a requirement, the VM appliance will start on another host and it will feel like a reboot. Once the VM starts, the PostgreSQL server process will be restarted. At this point of time, the PostgreSQL server goes into recovery mode. The biggest question at this point of time typically is how long will the recovery mode take. Typically based on internal tests even with the heaviest workload on 8vCPU, the recovery time can finish within the checkpoint_timeout settings which means our Recovery Time Objective is guided by checkpoint_timeout + heartbeat latency + the time to restart the VM on another hosts.  Overall we try to fit that into our Recovery Time Objective of 5 minutes.

Great the virtual machine has restarted and the database has done its recovery and working again. Now what? Well dont forget in this cloud setup, the easiest thing is to use DHCP addresses. Unfortunately DHCP addresses are not guaranteed to be same after reboot . Plus rebooting on a different host makes it more complex to get the same IP. This IP address change can cause the Database connectivity to be lost to the actual end user.   In order to shield the end users from this complexity, we sort of implemented our own Database Name Server. However this can only work by modifying the clients which references the database using this "Virtual Hosts" format so that the clients can always find their intended database without really worrying about where it is running. A minor change in the PostgreSQL clients but a huge complexity reducer for end users to fix their IP addresses or domain names to the changed location.

Aha now this explains why vPostgres ships their own clients and libpq library which is API compatible with standard PostgreSQL libpq library.The libpq library is actually 100% compatible with standard PostgreSQL Libpq library. The only addition it has is the feature of Virtual Hosts which is critical for HA to work seemlessly without the users being concerned about the actual IP of the database. Without the change, HA will not work on the framework. Since it is 100% compatible, if an application works standard libpq it will work with vPostgres libpq. Similar changes are also done in the JDBC driver and ODBC Driver for vPostgres so HA is supported across all supported clients.

That said if you use standard libpq/psql and other standard clients and you know the IP Address of the vPostgres database and connect to it via that IP address (and not the virtual host string)  it will still work flawlessly. However if the database goes down and restarts with a new IP address then the client will have no ability to figure out the new IP address and will have to bug the Administrator to figure out the new IP address.

Though for folks familiar with vSphere terminology, HA is not FT - Fault Tolerant which is a different take on HA to further reduce downtime from minutes to seconds. More on that in future.

vFabric Data Director - All in a Box setup

vFabric Data Director 1.0 is available for download on the VMware download website. Generally the question we get a lot is on how to do a "small" setup for either a departmental setup or trial setup which is actually small enough to fit in a beefy workstation or a small server. Often time this helps people to evaluate the features of Data Director before doing the "standard" HA setup. Maybe it is only for tests/dev databases where one does not want to invest too much in the infrastructure setup itself..

So here is one such way of doing a "small" setup of vFabric Data Director for test/dev/qa databases.
(Note: This is going to be a long blog post)

What do you need?

1. A beefy Workstation or departmental Server with lots of RAM and lots of Storage and atleast two network adapter (Even though only one need be connected to the departmental network)
2. ESXi V5.0 installation CD (Software)
3. vCenter  Virtual Appliance/Virtual Machine
4. DHCP Server Virtual Machine
5. vFabric Data Director Virtual Appliance Image
6. Preferably atleast one static IP Address on your departmental network for DB Name Server or have Dynamic DNS on your departmental network
7. License Keys - Use your VMware contacts.

Step I: Preparing the Workstation
On my test setup I had 12GB of RAM with 2x Quad Core x86_64 chips with 5 disks in it. Since this is my whole setup, the more memory I get, the merrier I am with the setup. It is recommended that you have some sort of Raid controller on the setup and have ability to create multiple LUN devices that will be exposed to the ESXi server. On my setup I had about one disk dedicated to ESXi and for other disks I created a RAID-5 LUN (RAID-10 is preferred but I did not have enough space on my demo setup).  If I had more Storage available I would do two setups with RAID-10 setup and a RAID-0 setup which can be used as backup datastore. The RAID preferences depends on individual needs on what to trade off (performance , availability, capacity).

Step II: Installing ESXi 5.0 
Using the ESXi V5.0 installation CD, I installed ESXi  on the first logical device and eventually setup the RAID protected device as a datastore that ESXi can use.

Step III: Install vCenter Virtual Machine
Here one can use the vCenter Virtual Appliance available also. On my demo setup I had used a vCenter Server based on Windows Server 2003 since that was available. Installation of vCenter Server itself can be its own blog entry and I will leave that to experts. For my purpose I had setup vCenter Server setup done in a virtual machine.

Step IV: Setting up vCenter Server for our task
This is where things are bit different for this special all in a box setup. The idea of setting up is to do vFabric Data Director Appliance which includes the hardware. Hence the idea is only the management console and the databases that it deploys are exposed outside (of course the ESXi also has to be visible outside to set this up)  and all other infrastructure related things are hidden within this appliance. This is where two network adapters will come in play. Lets go first with the steps and then a bit of explanation on why do it this way.
I am assuming that the ESXi Server and  vCenter VM has network connected to the live network adapter.

• Connect to the vCenter through vSphere GUI or through the webclient.
• Create a new DataCenter. 
• Create a new Cluster
• Add the ESXi Host to this cluster
• Edit Settings for the Cluster to Enable
• vSphere HA (even though we cannot do it on a single Host)
• vSphere DRS
• "VM and Application Monitoring" in VM Monitoring
• Create a Distributed vSwitch as follows:
• Go to networking in Inventory you will see your corporate network called probably "VM Network"
• Add a new distributed vSwitch in the section where you have to add a physical Network Adapter, select the Network Adapter which is not plugged into the departmental network (Skip creating an automatic port group for the switch)
• Now for the dvSwitch created, create two port Groups "vCenter Network" and "Internal Network"
• For the ESXi host create a vmKernel port in "vCenter Network" portgroup with a static IP address 192.168.2.2
• For the vCenter VM, create a new network adapter in the "vCenter Network" port group with a static IP of 192.168.2.1
• Change the Managed IP of vCenter Server (Administration->vCenter Server Settings->Runtime Settings)  to 192.169.2.1
• Make sure vCenter server can still access the ESXi server through the new "vCenter Network" portgroup
• Setup DHCP Server Appliance such that its LAN network is on "Internal Network"
• I had setup DHCP  Server such that its own IP is 192.168.1.1 and it does DHCP on the network from range 192.168.1.5 to 192.168.1.250 (for my demo setup)

Step V: Deploy vFabric Data Director OVA Template

• Using the vSphere Client (connected to our vCenter Server) deploy the vFabric Data Director OVA template. 
• The setup wizard will ask you to select the network for vCenter and the Management console. For the vCenter Network select the "vCenter Network" portgroup and for the Management Console, select the "VM Network" which is the live departmental network
• If your setup is like mine, I do not have access to static IP adddress in the deparmental network so I just leave the next screen at its defaults to use DHCP and finish the deployment. 

Once the deployment finishes  there will be a new vApp called VMware Data Director.

• Start the vAPP.  Once the vAPP starts, expand the "+" sign and select to the Management Server VM. 
• Select the "Summary" tab of Management Server and wait till it shows an IP address.
• Enter  that IP address in a browser and you should see message "This connection is untrusted" depending on your browse type, add it to your exceptions and then it should take you to License agreement screen. 

Step VI: Setup vFabric Data Director

• Read and accept the License agreement to proceed. 
• Next create an administrator account.
• On the next screen since this is a small setup, I selected the Global User Management Mode
• Setup the Branding as required on the next screen
• Setup the SMTP server information if available (needed for user password resets) (You also need outgoing email id for a successful setup of SMTP )
• On the next screen you have to setup vCenter Network Information. Since we dont have any DHCP on our "vCenter Network" portgroup, edit Network adapter settings and select "Static" with netmask information 255.255.255.0
• Set static IP addresses 192.168.2.3 for Management server and 192.168.2.4 for DB Name Server
• On the next screen change Internal Network to select "Internal Network" portgroup and leave DB Name Server Network as the "VM Network" which is the departmental network.
• On the following screen, select the network settings of "Internal Network"  DHCP should be already selected. Also check Static and add the network mask as 255.255.255.0
• For Management Server - Internal Network adapter, select static IP address and set it to 192.168.1.3
• For DB Name Server - Internal Network adapter, select static IP address and set it to 192.168.1.4
• Warning: This next bit is where you use your one static IP address or Dynamic DNS based FQDN requirement. We still have to provide DB Name Server - DB Name Service Network Adapter. If you have any influence on your IT, get a static IP address for this one. If you get the static IP, then click the Departmental "VM Network" setup and select static IP address with the associate Gateway, netmask and DNS Server setup. Then  set the static IP address for DB Name Server - DB Name Service Network Adapter with the static IP address that you get from your IT..
• Next enter your Evaluation License keys for vFabric Data Director and vFabric Postgres
• Finally verify information on the summary page and then click Finish
• You should get a login screen if it sets up successfully

Note: The most tricky bit is getting the IP address from IT. If for some reasons you do not have a static IP address, fake a  fully qualified domain name for DHCP. Once setup and you get a login screen. Figure out the DHCP IP allocated to DB Name Server using vSphere Client (It is first IP address that shows in the Summary tab of DB Name Server VM). Enter using your administrator account credentials, go to "Administration" tab. Select Settings-> Networking setup. Select Edit Network Setup and step through the setup again and change your fakeFQDN with the DHCP IP address and press finish. Of course this is a hack and not recommended since DHCP IP addresses can change anytime if the lease is up or the system is rebooted and other network policies.

Step VII: Setting Up  an Resource Bundle in vFabric Data Director
For this we need a special Resource Pool in our Virtual Data Center

• Using vSphere Client we create a resource Pool "Resource Bundle1" in the data center
• Edit its settings such that it has reservations and limits matching for both CPU and memory
• Also "Unlimited" should not be checked for both CPU and Memory. 
• In my demo setup I set CPU reservations and limits to 4096MB and Memory reservations and limits to 4096 MB.

Enter the vFabric Management Console using your administrator credentials and go to "Manage & Monitor" tab.

• Select "Resource Bundle" and create a new resource Bundle "ResourceBundle1".
• If the setup is right the next screen should show you the CPU/Memory Resource Pool that we created "Resource Bundle1"  
• Next select the RAID protected datastore and a size chunk off it for Database Storage. Select any alternative or the same datastore for "Backup Storage" with a sizeable chunk. In my demo I selected my Raid5 based datastore and 100GB sizes for both.
• Next select the "VM Network" which is my departmental network through which uses will access the database. 
• Click Finish to setup the Resource Bundle.

Step VII: Setup an Organization in vFabric Data Director
In "Manage&Monitor" select Organizations and create "+" a new organization called "DataDirectorOrg".

• On the next screen you could select an new user or in my case I used "Choose an existing user" and used my administrator account to manage the Organization also.
• Next I selected the resource Bundle I just created (need to select "Choose an existing Resource Bundle" to see the resource bundle) 
• click Finish.

Once created there will be the new Org displayed. If you select it once, the link becomes active. If you then select the active link again it will open a New tab for the Organization for our next step.


Step VIII: Setup a Database Group
Next we have to setup a database group.

• In the org tab select "Manage&Monitor" tab to see the list of databases (which is empty). 
• Select the second tab "Database Groups"  to see the empty group list
• Create ("+" ) a new database group "DBGroup1" where I selected half of my datastore resources assigned for this group leaving the rest of the entries at default.

Step IX: Create a database

• Select and enter the database group we just created.
• Create ("+") a new database "dbtest" with owner credentials "dba" and password.
• Wait till deployment of the database succeeds and "dbtest" is running. 
• Once running highlight it , right click to see the properties and get the UUID and Name. The client also needs the IP address of the DB Name server.
  

Here is an image of the distributed vSwitch from vSphere client on my demo box.

Step X: Connect to the database from a client
Download vPostgres Clients for your platform. Then using psql from the client connect to the database similar to the following example

psql -h {dd9fce1e-db46-4a08-99a1-e9023b8239fe}.129.55.555.55 -d dbtest1 -U dba

It should prompt for the dba password and now you are connected to the database and the setup is working. Check out my previous blog entry on how to use vPostgres Clients.

Finally now the setup of vFabric Data Director all in a box setup is working and tested.