This comes up in most deployments when minimum levels of access is desired when creating any type of vCenter service accounts.  Typically the configuration is manually done, but is a good example of how useful PowerCLI can be as it is a much better answer for achieving consistency and integrity in a process. 

There is a script below that leverages a pre-built configuration including the necessary items to achieve what is currently documented as minimum access needs for Avamar to perform its normal operations.  We leverage PowerCLI and its built in basic cmdlets that allow you to easily perform the necessary operations.  See the following screenshot where we are showing the VIRole and VIPrivilege cmdlets. 

If you want to view the roles that are already created you can get there from the vCenter home screen, following the below screenshots.

Below are the requirements based on development environment and testing.  Keep in mind, that the script developed should work outside of these versions but the privileges might have been renamed depending on your versions.  There is a simple hashtable in the script that can be updated to accommodate any PrivilegeGroupId and Privilege Name. 

• Powershell v2+
• PowerCLI 5.1
• vCenter 5.1
• Download the script here

Onwards!

The script is very easy to run.   Simply download it, and execute it with the following line.  There are two expectations.

• You have connected to a vCenter server already (Connect-VIServer), (Set-ExecutionPolicy is unrestricted or bypass)
• You have created a new role (New-VIRole -name newrolename)
• ..or you want to run the script against an existing role and ensure it has no more and no less permissions as defined in the script

.\UpdateVIRoleBackupServicePrivileges.ps1 -Role (Get-VIRole -Name “Backup Service”)

That’s it!  You can see the role configured below based on the privileges listed above.

The next step is to Add Permission for this role and a specified User/Group to the correct place in the vCenter Hierarchy.  This can be at the vCenter/Datacenter/Cluster/Host level.  See below screenshots where we are adding the Role and a User/Group at the vCenter level which assigns the “test” user the “Backup Service” role privileges for all things covered under the vCenter instance.

And there you have it!

Bonus

In some cases there might be the need to be more granular with what objects the new role is assigned to.  This can cause some complication, so how would you go about reporting on, for example, what datastores a specific user has access to?

Get-Datastore | Select Name,@{n="VIPermission";e={Get-VIPermission -Principal "AWESOMESAUCE\test” -Entity $_}}

In other cases you may want to automate the addition of the permissions to the proper vCenter object.  Here you can see that we created a new permission set (Role & User specified) and applied it to the vSpecialists datacenter.

New-VIPermission -Role $role -Principal "AWESOMESAUCE\test"  -Entity (Get-Datacenter vSpecialists)

Stat collections using PowerCLI is a pretty well covered topic.  I have seen a lot of work done out there where it is demonstrated how to use the PowerCLI stats cmdlets, and how output can be customized to fit the needs of the consumer.  This post should be a bit different.  We will slightly reverse engineer how the Get-Stat/Get-StatType/Set-StatInterval cmdlets are used together, in order to provide a better understanding of how to leverage them along with new cmdlets presented here.  This should allow you to take full advantage of stats collections for VMware vSphere environments.  The methods shown actually under the covers are very similar to the methods that are used, if not the same, from a vSphere Web Services SDK (SOAP) perspective.

• PowerCLI Get-Stat – Realtime, Raw Feeds, Collection Intervals
• New Powershell vSphere Stats Module
• Timesync issues

Later in this post I present a custom module for stats collection.  The second portion does leverage the vSphere SOAP SDK via the .NET compiled SOAP assembly, which gives native access to all exposed SOAP methods/types/events.  This can serve as a pretty cool example of how to develop more natively to the API in parallel to the PowerCLI framework.

I do some really interesting stuff here and possibly open up some opportunities while solving some challenges/bugs that Get-Stat presents currently.   A brief summary of what you get once you plow through the basics =)

• Better output formatting
• Expose more vSphere object types for stats
• Show vSphere stats library
• Intervals handled properly
• Possible Timesync problems avoided

PowerCLI Get-Stat – Realtime, Raw Feeds, Collection Intervals

It is important to first put this post in perspective in regards to the basics of what is provided via PowerCLI.  There are three cmdlets that can work together to properly return stats for vSphere objects (VM, VMHost, Cluster, Resource Pool).  Lets explain the interaction of these objects because it relates to what you may or may not get returned when retrieving stats.

Get-StatInterval 

This cmdlet is used to return the appropriate intervals at which stats from the EsxHosts and vCenter are rolled up to aggregate stats.  These can take the form of (none, average, minimum, maximum, summation, and latest).  Notice how we are using a custom cmdlet (to be explained later in the post) to return all rollupTypes.

And when stats get rolled up there is a specific type associated (rate, absolute, delta).  This terminology is widely used across performance tools, so no need to explain it here.

Now comes the stat interval part.  We have established that stats get rolled up, and the Get-StatInterval command determines how often vCenter does it’s rollups (Sampling Period Secs) and for how long it keeps these stats (Storage Time Secs).

In summary sampling periods (rollups) are done for the 5-minute, 30-minute, 2-hour, and the 24-hour periods (Sampling Period Secs).  This information is held in the vCenter database for 1-day, 7-days, 30-days, and 1-year periods respectively (Storage Time Secs).  There is also a real-time option that comes from specifying a –Realtime parameter which aligns to 20-second intervals using a “raw feed” from the ESXi hosts directly.  ESXi hosts hold the 20-second samplings for 1 hour.  More to be covered here later in the post.

Get-Stat

So the combination of the rollups and the duration of storage should yield that our vCenter objects adhere to these rules.  Let’s take a look at the statistics that are returned for a virtual machine with default parameters specified.  Below you can see that we are calling (Get-VM | Get-Stat | Group MetricId).  This means that we are asking for the stats relating to a specific VM and then combining all of the rows with matching MetricIds into individual rows to help organize the results.  We do the same following when grouping by Timestamp.  With this you can see a few things.

• 568 samplings returned for the metrics
• 6 metrics returned for VMs
• Samplings every 2 hours (7200 seconds)

So this is interesting.  When executing the PowerCLI Get-Stat command without parameters we get results for rollups at the 2-hour level.  But this doesn’t quite seem right however, are there only 6 metrics that are stored in vCenter for a VM?  The answer is no.

There are actually a lot more.  Let’s look at how to maximize the output from the Get-Stat command.

Get-StatType

Using PowerCLI and Get-Stat, if we want to get all stats from a vSphere resource then we need to force Get-Stat to look at all available metrics for that resource.  Using no parameters generated what we saw above (6 rollup stats for VMs) but in reality there are a lot more to be had.  So how can we see what those are and get them?  Get-StatType is the answer.  This cmdlet returns all available metrics for a resource.  When running against the same VM and for the 7200 second interval (2-hour) you can see we return more than 6.  We actually get 19 rollup stats for VMs.

So the stats exist in vCenter’s database, now how can we get at them?  Simple, we need to pass this list of all available stats to the –stat  parameter when running Get-Stat.  Below is the command that can be run to expose all rollup stats for the VMs.  Notice something interesting, that the disk.used.latest metricId has 2840 samples whereas the rest have 568.  This is due to “instances” or multiple disks per VM (swap, delta, flat).  This metric is also tracking different disks which are counted separately.

Get-VM vmName | %{ $_ | Get-Stat -Stat ($_ | Get-StatType -Interval 7200) } | Group MetricId | Sort Name

Now what is important at this point is that we are covering rollups stored in vCenter.  What we haven’t mentioned prior is that there are is a well known capability in vCenter to set the performance logging level.  Basically what happens is that all stats are generated at the ESXi level no matter what (I believe), and only certain stats are requested by vCenter when doing its stats rollup process.  The level is assigned per metric, and even possibly per device type if a metric is shared.  So you can see below that the level 4 (highest) has 184 metrics that it might record if all vSphere resource types were rolled up at the highest level.  vCenter ships at level 1, which means only certain metrics are rolled up.

Now let’s consider where rollups come from.  The rollups are based on real-time collections at the ESXi host layer.  The real-time collections, as mentioned before, are done every 20 seconds automatically.  These 20-second collections are then stored for 60 minutes on each ESXi host and vCenter accesses these pre-built stats when collecting.  The stats that live on the ESXi hosts can also be accessed by setting the interval appropriately.    When requesting this real-time feed (raw, explained later), all counters that are requested regardless of level (explained shortly) are returned.

You can see this in the following example where we specify the -Realtime parameter to Get-StatType which then returns all available metrics from all levels.

Get-VM VMname | Get-StatType -Realtime | Sort

Let’s see what happens when we issue a real-time Get-Stat request.  You can see that we are showing the 20-second sampling period which dictates results coming from the ESXi host, but we are only seeing 9 metrics.  This sounds awfully familiar to the situation we encountered before where we needed to manually specify the –Stat parameter with Get-StatType to force the cmdlet to return everything for that resource.

So same trick as before, we are now forcing the exact stats to return and are seeing 258 instead of 8!  Sweet.

Now comes the next trick.  Say you would want to limit the stats being return to only the most recent one to create a “esxtop" style view.  This would happen by specifying the –MaxSamples 1 parameter.  Important note here, when looking at the use case for this compared to other perfomance metrics you may know. 

• vSCSIStats – Realtime, IO sizes, Micro-Bursting stats (sub ms, no averages—histograms), not recorded, storage-VMDK focused
• EsxTop – Semi-Realtime, averages, Hypervisor/VM focused, not recorded, extra collections on host
• vSphere SDK QueryPerf (Get-Stat) – Semi-Realtime, averages, all vSphere focused, recorded

Notice how we are actually sorting and issuing a select on specific columns followed by formatting in a table format. 

Now the last part for PowerCLI’s stats collection.  What are the breadth of metrics available via PowerCLI’s stats collection?  When we mention realtime/rollup we are referring to the fact some resources have real-time metrics and some don’t.  However, the breadth of available metrics is visible by forcing the use of -Realtime when using Get-StatType.

114 for VMs (realtime)VMs – 114 metrics

VMHosts (realtime) – 170 metrics

Cluster (only rollups) – 30 metrics

Resource Pool (only rollups) – 6 metrics

New Powershell vSphere Stats Module

Now we can begin to transition into the 2nd part of this post focused on a new Powershell module I created for vSphere stats.  This module represents two things.  1) Simplifying the use of what was previously presented for Get-Stat/Get-StatType cmdlets 2) A re-write of Get-Stat as Get-StatAdv that leverages the vSphere SDK to simulate the functionality of but provide more stats than Get-Stat currently does.  So let’s continue!  Here is a summary of what the module provides.

• Connect-VIServerVim, Get-StatRealtime, Get-StatRollup, Get-StatAdv, Get-StatTypeAdv, Get-StatCompositeAdv, Get-PerfCounter
• QueryPerf/QueryPerfComposite
• Exposes Datastore, Datastore Cluster, Datacenter, Folder Stats
• Leverage Get-View as well as standard PowerCLI get cmdlets
• Composite stats
• Filterstat
• Framework for vSphere Web Services SDK Soap

So first thing is first.  You must first follow the following criteria.

• Developed in Powershell v3, PowerCLI 5.1 Release 1 (no guarantees for lower version)
• Download the module here.
• Unzip, and then load the module (dir *.psm1 | Import-Module)

Connect-VIServerVIM

This cmdlet does all of the fancy work and is based on previous work in this module but cleaned up dramatically since there was an actual use case for it. =)

Connect-VIServerVIM -username user -password ‘pass’ -viserver vCenter

or

Connect-VIServerVIM -credential (get-credential) -viserver vCener

Get-StatRealtime

Here is the first stat cmdlet to discuss.  The Get-StatRealtime cmdlet issues the same query (against Get-Stat) that you saw in the previous screenshot, so you’ll notice the output is the same and we are still leveraging PowerCLI’s stats cmdlets.

Get-StatRollup

As well for the rollups, I provide the Get-StatRollup cmdlet which issues the same query (against Get-Stat) and neatly formats output from rolled up data similar to above.  Here you see we issue with the –Interval 300 parameter and then group it by timestamp.

So in comes one of the first problems that the module is addressing.  Notice how in prior examples when looking at rollups we only saw 2-hour samplings.  Well, here you can see that we are issuing another call using the –Interval 300 parameter which should return us the 5-minute data stored on the vCenter server.  Problem!  We are getting the 2-hour samplings back.  How can we return 5-minute results from the PowerCLI Get-Stat cmdlet?  Use the –Start parameter (see end of post).  But overall, this seems to be a bug that I address with my custom module.

You see this as well across all four intervals where only 2-hour and 1-day intervals are returned.

Get-PerfCounter

So how many vSphere stats are there for vSphere 5.1 through the web services SDK?  The Get-PerfCounter cmdlet can answer that question.  We are formatting and selecting columns that are returned, but you can see the jist of it here.  There are 459 in vSphere 5.1 in my setup.

Get-StatAdv

This cmdlet is based on what the Get-Stat cmdlet returns and leverages the same SOAP methods via the vSphere SDK that the Get-Stat cmdlet uses.  However, through this method we provide native use of the QueryPerf SOAP method to return the performance stats.  By doing this we have complete control of what the methods receives for inputs and what the output looks like.  So in this case let’s give it a shot and issue the same command as prior with the –Interval 300 parameter against Get-StatAdv.  Notice how we are returning the same 23 rolled up stats as prior, but are now returning them every 5 minutes.

Get-VM VMname | Get-StatAdv -Interval 300 | Group Timestamp | Select -First 1

This is just the beginning!

Now what other types of resources can we pull for using Get-Stat or Get-StatRealtime? Clusters, Hosts, Virtual Machines, Resource Pools based on the error we get when trying to return stats for a Datacenter.

Since we are native to the QueryPerf method we can give it a shot and pass these objects in!  Coolness!  We now have Datacenter stats!  Note that we are specifying the –Interval 300 parameter below because these objects do not allow Realtime lookup from the Esxhosts.  Makes sense right?

And Folder stats!

Like Datacenters, Datastore views of performance are aggregations from Esxhosts and Folders/Datacenters are logical objects that aren’t collected via Esxhosts.

And a couple of Datastore Cluster stats!

If you’re familiar with vCenter Operations, what you’re now seeing may start to align a bit more with what is presented!

Timesync

I mention Timesync since it can be a problem when using the Get-Stat PowerCLI cmdlets.  The –start and –finish parameters allow you to specify time ranges during queries.  However, be aware that the DateTime objects are being sent (with proper timezone changes) to SDK.  This means that if the minutes/seconds is off between the client and server, the DateTime specified may not return expected results.  You can get around this with the PowerCLI cmdlets by using a command like the following.  Notice ($global:DefaultVIServer.ExtensionData.CurrentTime().ToLocalTime()).AddMinutes(-5)).  This command gets the current vCenter time, converts it to local time (timezone applied) and then removes 5 minutes from it.  This way you are always submitting the DateTime to vCenter without TimeSync issues.  In the custom module, we compute time differences and make up for them, so you can use ((Get-Date).AddMinutes(-5)) for example and it will be fine.

Get-VM VMName | Get-Stat -Start ($global:DefaultVIServer.ExtensionData.CurrentTime().ToLocalTime()).AddMinutes(-5) | ft * -autosize

Composite

Last but not lease there is a Get-StatCompositeAdv cmdlet.  This cmdlet leverages the QueryPerfComposite method which is meant for quicker queries and reduced network traffic.  The downside to using the composite method is that it seems to not return instances such as virtual disks.  Do your own scale testing to see!

Custom cmdlets can be tweaked to use CSV, and keep in native format which would save on client-side overhead for processing large amounts of stats. 

Links

Please review the vSphere Management SDK docs for Web Services and performance.

http://pubs.vmware.com/vsphere-51/index.jsp?topic=%2Fcom.vmware.wssdk.pg.doc%2FPG_Performance.18.4.html

Breadcrumbs

For those wanting to develop against the SDK similar to what I did here.  Notice the $vimClient.VimService type is SoapHttpClientProtocol.  This is where all of the .NET methods/events exist.  Issue a Get-Member against it and check what you now have access to!  All of this is documented with C#/Java examples of how to leverage.  You can look at what I did in this module to help transpose what you might need to do.

Part 1 of this series can be seen here where we discuss the nuts and bolts behind how to protect vPostgres databases by VM image level or guest level protection.  Here we will simplify it a bit and focus only on VM image level protection.  This assumes you are leveraging VDP or Avamar. 

All of that seems manageable I’m sure, but what if you want to press the easy button and have PowerCLI do it all for you?  This post contains work that is experimental.  It will allow you to protect VMware Server Appliances (VCSA, vPostgres, etc) that are published by VMware.  The automation may not be sanctioned directly by VMware, but the steps (if available) were taken from their website so the end-state should be supported. 

Warning: This is highly experimental!

Pre-Requisites

• Powershell v3
• PowerCLI 5.1 Release 1
• Download module from here
• Unzip, and run (dir *.psm1 | import-module -force)

Image Level Protection

This was the easier method in which we simply schedule a Cron Job that executes a pg_dump/all command on the vPostgres database.  This generates a file locally which is then backed up if you have the VM on an image level backup schedule with Avamar or VDP (VDP does not protect its own VCSA). 

By the time of this post I have tested this with the vPostgres vApp (9.1) along with the vCenter Server Appliance (5.1.0.5100).

Image Level – VCSA

The vCenter Server Appliance contains instructions on the VMware website, on how to do a Postgres backup.  I followed these directions to a T in creating our cron job. 

Get-VM VCSAname | New-VCSABackupCronJob -GuestUser root -GuestPassword vmware

If you take a look at the /etc/cron.daily/backupvPostgres.bin file you can see the results of this command. 

Feel free to execute this command to give it a test (/etc/cron.daily/backupvPostgres.bin).  You can then take a look at the backup (ls –laF /storage/db/vpostgres).  You can see we have a 400MB backup file of the VCDB instance.  Keep in mind that we are backing up to the same partition, so make sure to manage your space to ensure there is space for the DB along with its backup!

Image Level – vPostgres

Now the image level protection that we demonstrated with the vCenter Server Appliance basically leverages the vPostgres Cron Job cmdlets under the covers in a more specific way since VMware had specific details.  This can be done very easily for any Server Appliance that has the same level of details.  If it is the vPostgres Server Appliance, then you can always protect the whole Postgres instance with the following command.  Notice the New-vPostgresBackupCronJob cmdlet instead of the VCSA one.  The screenshot below shows the command being run.  Take note that we discovered the vPostgres path automatically.

Important Note:  With the version of vPostgres I was using, 9.1, the VM Tools Daemon on the vPostgres vApp was not configured for PAM authentication.  If you want Invoke-VMScript to work, you need to fix this.

Edit /etc/pam.d/vmtoolsd (and paste the next lines, and save the file)

#%PAM-1.0
auth       sufficient       pam_unix2.so nullok
auth       sufficient       pam_unix.so shadow nullok
auth       required         pam_unix_auth.so shadow nullok
account    sufficient       pam_unix2.so
account    sufficient       pam_unix.so
account    required         pam_unix_acct.so

Ok.. continue on!

Get-VM VMname | New-vPostgresBackupCronJob -GuestUser root -GuestPassword password 

Taking a look at the Cron file for this one, you can see it is more generic.  We are leveraging the pg_dumpall command here and putting the backup in the root of the vPostgres instance path.

Feel free to run the backup (/etc/cron.daily/backupvPostgres.bin).  You can look at the bak file as well to ensure that it ran.

The Cmdlets

New-VCSABackupCronJob – Instantiates a specific vCenter Server Appliance Cron Job creation

New-vPostgresBackupCronJob – Instantiates a general vPostgres Cron Job creation

Get-vPostgresPath – If a path is not specified we leverage this to try and determine what the vPostgres instance path is

New-VMOSCronJobvPostgres – We specify a generic backup script here, replace necessary items, and launcht he New-VMOsCronJob cmdlet

New-VMOSCronJob – This issues the New-VMOSScript command followed by a reset of the Cron service

New-VMOSScript – This takes a script text, creates a temporary file of it, copies it to the VM, moves it into place, and then makes it executable.

Restoring?  As always, make sure to follow application guidelines.  We are simply showing you how to create a backed up file which should then be used as part of a larger restore process that should be laid out by your application vendor.

You tell me? Is this useful?

In the case of VMware Virtual Machine protection, this most likely isn’t necessary since you probably are using VM Image level protection right?  I hope so!  But there are cases when a more granular backup is desired.  This can useful if you want to target backups in the operating system for specific files, or specific applications.  In this case you will need to install an Avamar agent in the guest VM in order to do the guest OS operations and source-based dedupe.  The following post will give you the capability to completely automate, if desired, the install process of the Avamar guest agent as well as application agents.  This is meant for installs, but can be used for upgrades as well. 

So how can we accomplish this?  In PowerCLI there are a couple of commonly used cmdlets called Copy-VMGuestFile and Invoke-VMScript which allow you to transfer files and execute commands from a specified user account without the VM having a network connection.  The communication happens between the hypervisor and the VM.  The operation of the module is simple, you can either leverage the remote install capability in a on-demand or fully planned manner (CSV file).

As always, please do your own testing prior to use anywhere outside a lab to ensure you understand how this operates!

Pre-Requisites (as developed)

• PowerCLI 5.1 Release 1 and Powershell 3 (should work on prior versions as well)
• Avamar 6.1 (should work on any version)
• Will work with any Linux version that allows Invoke-VMScript/Copy-VMGuestFile
• Avamar Installed
• Download module here
• Unzip module, run (Dir *.psm1 | Import-Module) in Powershell
• Logged into vCenter with PowerCLI, possibly vCloud Director as well (use Get-CIVApp name | Get-CIVM | %{ $_.ToVirtualMachine() })
• Passwords specified as command line parameters with special characters have single quotes surrounding them!  (chorn)
• VMware Tools are installed on the VM and scripts can be executed, and files transferred

Currently this only does Linux Agents that use RPM files.  Soon to be expanded..  We do not leverage MCCLI, or any programmatic interface to Avamar during the install, so it is up to you to verify that all was installed correctly.  This is solely meant to make the deployment of agents as easy as possible for VMware VMs that require guest agents.

On-Demand Install

Download the Avamar Agent to the PowerCLI System

Let’s consider this the beginning of this post and I will continue to mature and update it.  For now it is limited to Linux.  Very simple, the first step is to download an Avamar Agent installer file.  This is available by going to the Avamar web interface and pressing the link at the bottom, Documents and Downloads.  Now, find the agent that you want to install (hopefully a Linux one until I do others soon). 

Another important note here is that you could leverage a simply where clause when getting VMs to find ones that match specific operating OS types and then specify the agent required to fulfill these types.

Get-VM | where {$_.ExtensionData.Guest.GuestId -eq “sles11_64Guest”}

Test Invoke-VMScript

Now we want to test to ensure you can use the credentials specified and do a simple command like “echo”.  If your output looks like the screenshot, then you’re set.  Another way to do the following would be to use the Get-VMScriptEcho cmdlet.

Get-VM VMname | Invoke-VMScript -GuestUser guestUser-GuestPassword guestPassword -ScriptType bash -ScriptText "echo"

Copy, Install, and Configure the Agent

Now we move on to the actual Install-AvamarAgent cmdlet.  With this cmdlet we will copy the agent, as well as install and configure the agent using the specified information.  The expected output is in the following screenshot.  Notice that the last few lines are outputting a powershell object that contains the vm name, whether the install was successful and the result if there was an error.  You can capture this in a variable such by pre-pending $tmpRun = Get-VM…

Notice how wildcards are permitted in the file name!  We add a [0] at the end to ensure that we only select the first file if the wildcard matches multiple agents!

Get-VM VMname | Install-AvamarAgent -agentFile (Get-Item AvamarClient*sles11-x86_64*.rpm)[0] -GuestUser guestUser -GuestPassword guestPassword -AvamarServer avamar_ip_or_dns

How about a Avamar guest application agent? A very similar command will give similar results.

Get-VM VMname | Install-AvamarAppAgent -agentFile (Get-Item AvamarRMAN*sles11-x86_64*.rpm)[0] -GuestUser guestUser -GuestPassword guestPassword -AvamarServer avamar_name_or_ip

As you can see in the previous step we manually are specifying which agentFile.  What if you want this to be determined automatically?  The following command will attempt to match the proper agent file to the guest along with generating useful information which is for the most part leveraged in a planned install which is shown in the next section.

Get-VM bsg67235 | Export-VMGuestToolsInfo

Notice also from above that there are empty fields with AgentBinary_.  These are leveraged in the next planned install section, but can also be leveraged here.  You can fill these in on demand here if needed.  Here we will replace a column, but you could also add any column as AgentBinary_name.

Get-VM name | Export-VMGuestToolsInfo | Select *,@{n=”AgentBinary_RMAN”;e={“AvamarRMAN*.rpm”}} -ExcludeProperty AgentBinary_RMAN

You can then run Get-VmGuestAvamarAgentTargets as an extra command, to ensure you have downloaded the correct guest OS agent executable.   If the file is not found, you would receive a warning.  The application agent file is not checked here however, so make sure it exists or else it will get skipped on install.  As you can see below we are good, we have a VM, and and agent binary without warnings displayed.

Get-VM name | Export-VMGuestToolsInfo  | Get-VmGuestAvamarAgentTargets

So if you want to actually install based on these results, you can call then use the Install-AvamarAgentTargeted cmdlet against your Export-VMGuestToolsInfo output.

Get-VM name | Export-VMGuestToolsInfo | Install-AvamarAgentTargeted -GuestUser username -GuestPassword password -AvamarServer avamar_dns_or_ip

Great! So what if you don’t want to specify credentials in plain text?  You can leverage the next method, or you can leverage the Get-Credential cmdlet as follows.

$cred = Get-Credential

After this, you can replace the following parameters with parameters from the $cred variable when executing.

-GuestUser $cred.UserName –GuestPassword $cred.password

A Planned and Automated Multi-VM, Multi-Agent Install

• Generate CSV report with assigned Agent binaries
• Modify CSV report to include only VMs necessary for install and proper agent
• Import CSV and target VMs
• Install agents automatically
• Reporting

Now that we have the basics done and have seen it work, it is now time to step into a bit more advanced functionality.  Wouldn’t it be nice if I could generate a report of the VMs that are registered and automatically choose a proper Avamar agent based on the guest ID? How about doing VMs at the same time?  Let’s show you exactly how to do this.

Generate CSV Report

The following command can be run to get all of the virtual machines, and export specific metadata information that will be useful later on.

Get-Vm | Export-VMGuestToolsInfo | Export-Csv VmGuests.csv -encoding ASCII -NoTypeInformation

Modify CSV Report

Once this command is done, feel free to open the CSV file in an editor of your choice, most likely Excel.  Your results should look something similar to below, notice the AgentBinary column that is auto-populated if we detected a Guest OS that is compatible with Avamar.  This might not be 100% accurate, so feel free to replace the file if necessary.  Remove unnecessary VMs that you don’t want targets for Agent installs.  Make sure to download the agents now for any files listed that you intend to install once you save the CSV in its edited form.  This CSV could be split up into multiple parts and each ran in their own PowerCLI window to parallelize the process.  The whole process should take under 60 seconds, where the majority of the time can be spent transferring the agent thus relying on network bandwidth to determine total time for all VMs.  You can expect under 60 seconds per VM if single threaded.

The latest version includes more columns, for guest credentials and application agent binaries.  You will see the actual schema when you run the cmdlet for the first time.  Application agent binaries are not linked up with appropriate versions, but if needed you can reference the displayed client binary per VM to lead you to the correct application binary.  These should be downloaded individually and the name should be pasted into the appropriate columns.  Each column named AgentBinary_appname that is filled with a file name will be attempted to be found and copied/installed during the process.

Import CSV and Target VMs

The next step will leverage this information to verify that the agent exists (so take the time to download them now!), guest is running, and guest id is currently detected.  If all of these criteria are met then it is populated in the list below the warnings.  Notice how the VMs that have agents with warnings do not show up in the list.  Get-VmGuestAvamarAgentTargets acts as a filter to ensure successful install of agents.  Currently, the warnings will only show for OS agents, warnings for missing application agents will be displayed during install.

Install Agents Automatically (authentication)

Ok, so the next step is to take the output from the previous step and use it to do a backup.  If you are happy with what you saw above, then you can proceed with the next command.

The next thing to consider when running in batch mode is the authentication per VM.  There are lots of options.  Overall, keep in mind that if a guestUser is specified for a VM, then what is specified as a secure password in the CSV file takes priority (guestPasswordSecure), unsecure password next priority (guestPassword) per VM.  The next section explains some of the options, other than per VM in the CSV file, you can specify a blanket default username in plain text or securely as runtime along with securely as each VM is encountered that doesn’t have previously mentioned credentials specified.

Save Usernames and Passwords in the CSV file

The Install-AvamarAgentTargeted will respect columns that are named GuestUser and GuestPassword.  If these columns are filled for VMs then the credentials will be used.  For this reason, the following command can be used without specifying credentials.

Import-Csv VmGuests.csv | Install-AvamarAgentTargeted -AvamarServer avamar_name_or_ip

Save Usernames and Passwords (as secure string) in CSV file

There is a column named guestPasswordSecure, which can be populated with a long secure string that is generated by New-SecurePasswordString. 

New-SecurePasswordString ‘p$ssword’ | Out-File temppw.txt

Open this file with notepad, and copy the contents.  The string can be pasted into the CSV file column of guestPasswordSecure for any VM.  This string is only valid for the host that generated the secure string.

Specify credentials in plain text (same creds for all VMs)

Import-Csv VmGuests.csv | Install-AvamarAgentTargeted -AvamarServer avamar_name_or_ip -GuestUser guestUser -GuestPassword guestPassword

Specify credentials securely (same creds for all VMs)

Import-Csv VmGuests.csv | Install-AvamarAgentTargeted -AvamarServer avamar_name_or_ip -GuestCredentials (Get-Credential)

Prompt for credentials securely for every VM

Notice here that we are using “{“ and “}” around Get-Credential instead of parenthesis.  This delays the operation from runtime, further into the script logic.

Import-Csv VmGuests.csv | Install-AvamarAgentTargeted -AvamarServer avamar_name_or_ip -GuestCredentials {Get-Credential}

So plenty of options for authentication!  My suggested route would be to enter the credentials securely into the CSV file (generate the proper secure strings) and then leverage the “Prompt for credentials securely for every VM” as the default in case a VM doesn’t not have credentials specified. 

Reporting

If you run Install-AvamarAgentTargeted, then you will probably want to have results stored somewhere.  For this we keep a variable, $global:result filled as VMs complete.  At any time, if the script stops you can access this variable to see the history.  I am excluding a specific column for cleaner viewing, but this one is probably important to you to see why the “Installed” column would be False.

$global:Results | select * –excludeproperty errorResult

You could also generate an HTML report with the following command.

$global:Results | ConvertTo-Html | Out-File Results.html

.\Results.html

The Cmdlets

These can be used individually for the most part with the correct parameters.  Examples of how to run each cmdlet if not found in the above post, can be found in the module above the cmdlet. 

Export-VMGuestToolsInfo – Generates a CSV file containing relevant guest information and an automatic mapping of guest OS type to respective Avamar binary

New-SecurePasswordString – This generates a secures string that can be pasted into the guestPasswordSecure column of a CSV file

Get-VmGuestAvamarAgentTargets – Used as a filter to ensure the Agent binary is available, the VM is powered on, and the guest state is running.

Install-AvamarAgentTargeted – Consumes the Get-VmGuestAvamarAgentTargets filter cmdlet output and instantiates install of OS and application agents

Install-AvamarAgent – Copies, Installs. and Registers the Avamar Guest OS agent

Install-AvamarAppAgent – Copies, and Installs the Avamar Guest Application agent

Get-VMScriptEcho – Tests that remote commands can be executed on a VM

Copy-AvamarAgent – Copies the specified file to the VM

New-AvamarAgentInstall – Begins the install process of the agent on the VM

Register-AvamarAgent – Registers the Avamar Guest OS agent to Avamar

Restart-AvamarAgent – Restarts the Avamar Guest OS agent

That should be it, of course there are more things that could be configured during the registration but not covered here.  The next step is to verify the install under /clients, open your Avamar console and take a look!

What could be coming?  Msft Server of course..

In my last post I discussed how to leverage vPostgres from Powershell.  Overall the Postgres (I use interchangeably with vPostgres as a term) database is an excellent choice for it’s functionality in replacement of other enterprise database platforms.  If you start to look through the VMware portfolio, it is seems to be becoming clear that a convergence for standard platforms (Postgres included) is taking place.  This means that there is more of a need to properly protect the Postgres as a whole to ensure the proper SLAs of your infrastructure. 

This post will focus on the techniques that can be used to protect Postgres databases.  In following posts, there will be more specifics to the virtual appliances being supplied by VMware to show how we can improve on generic protection steps.  With that side, this work can be abstracted to provide protection to just about anything by Avamar.

Important Notice

• This backup method does not leverage a specific Postgres agent, don’t expect robust error handling
• The recovery point object (RPO) for this method is up to how often you run the backup, for large databases however it is likely once every 24 hours
• Please test the recovery thoroughly to ensure you are familiar with the process and the integrity of the backups

Let’s start by differentiating between Avamar and VMware’s Data Protection appliance.  In a few words, what’s different?  VDP is specifically targeted towards doing VMware image level backups currently.  This means that extra functionality for using in-guest agents for scripting or application quiescing is not possible.  So the first major break point in this discussion is around which method you choose to use to protect Postgres.

The protection method questions can come down to two main things.  Will you be leveraging VMware snapshots and image based protection (Avamar or VDP) or are you going to use in guest agents (Avamar)?  Avamar in this case can be either Avamar Virtual Edition (a VM) or the Avamar hardware appliance.

In the Postgres case, for in-guest agents we can leverage the Linux agent which allows for extra steps and targeted backups of specific files.  This means that we are able to treat vPostgres agnostically of hypervisor, guest hardware, and anything other than the operating system.  It also means when we deduplicate at the guest operating system, we are solely looking at a specific file and not the whole operating system.

VM Image Level Backups of vPostgres (Option 1)

If looking at VMware image based backups we are operating with the notion that a backup would entail the whole virtual machine and all of it’s disks.  This overweight process with use of CBT (changed block tracking, similar to array snapshot blockmaps) where we only backup changed blocks between backups, makes for a slim manageable process even if we are looking far beyond just the targeted database backup file. 

So you may ask at this point, well if VMware image level backups are good then I’m all set for the super-easy button right?  Sort of.   It depends on the level of protection you want for your Postgres databases.  I want to put you to this article, re. Postgres 9.1 Backups.  We are not covering Point-In-Time recovery here, this is for another post and mostly relevant for larger DBs or frequent backups.

Let’s focus on file system level backups of Postgres.  If simply doing a VM image level protection then you are at the mercy of a database thinking it is recovering from a crash, no guarantees based on unflushed buffers for the DB and possibly pages for the OS. How do you overcome this for the Image method?  Invoking a cron job locally in the guest that kicks off a backup job and saves the file locally would work.  This means you get a dump file every time the cron job launches that can be used for a full recovery of the DB if needed. 

Problem?  If there is no alignment to the VM image backup process (running asynchronous to each other, with no central control), then you run the risk during the VM image backup of two main things.  1) That you don’t get a complete backup 2) The backup occurs during a VM snapshot which then would cause a lot of extra IO to consolidate the changed blocks into the original VM.  Now this may only be valid for larger databases, but overall this method for reliability requires that you first backup the database locally, followed by a VM image level backup.  In all cases here there is a lack of cohesion between backing up and shipping it off.  A target based dedupe platform as an NFS mount could help (Data Domain) but overall the process still can lack central control and tends to be a bit legacy for this discussion.

Preparing vPostgres for VM Image Level Protection

Download the files here that are shown below as examples. 

This is easy! Let’s just create a simple cron job.

SSH to your vPostgres system.  Use your favorite editor to create the /etc/cron.daily/backupvPostgres.bin file.  Make the file look at the following:

#!/bin/sh

/opt/vmware/vpostgres/9.1/bin/pg_dumpall -U postgres -c > /var/vmware/vpostgres/9.1/vpostgres.bak

Make sure to run “chmod u+x /etc/cron.daily/backupvPostgres.bin” to set execution permissions.  Run “service cron restart”.  That’s it, you should be able to run /etc/cron.daily/backupvPostgres.bin manually and see that a new file appears under /var/vmware/vpostgres/9.1/vpostgres.bak each time.  This means that a VM snaphot and a VM image level protection will protect the VM and the stateful backup of the Postgres database.

Everything mentioned above is editable.  If you want to change the schedule of backup, simply use another cron directory, ie. cron.hourly, or set your own cron configuration.  If you want to dump specific databases instead of all at once you can use the pg_dump command.  Do you have a specific mount point to put the backup?  Then change where the BAK file goes.

Guest Level Backups of vPostgres (Option 2)

Now, getting into the good stuff.  Avamar can be a central controller of the backup process for Postgres.  The process that I am laying out is to leverage the Linux FS plugin to coordinate a Postgres backup locally, followed by a targeted backup of that file.  This allows you to keep the control and reporting of the process in Avamar’s wheel house, and guarantees a proper consistent backup.

So does that mean we shouldn’t protect the VM with VM image level backup? No! Both would make sense in this case.  This means you have the best of all worlds.  Since Avamar has source-based deduplication, there is no waste of capacity.

Preparing vPostgres for Guest Level Protection

Download the files here that are shown below as examples. 

Ok, so now comes for the fun part.  The following is considering you have Avamar installed already.  Since we aren’t doing VMware integration for guest-level protection then simply installed is what is needed.

Download the Avamar Linux Guest Agent

The Avamar appliance allows the download of the agents via SSL without authentication.  Open the Avamar appliance up, via https://avamarip_or_dns/ with your web browser.  At the bottom you will see a link that says, “Documents and Downloads”.  Click this link and look for the appropriate Linux version (probably 64-bit Linux, if recent VMware vApp then likely SLES11).  Look for the AvamarClient-linux-sles11-x86-version.rpm file.  This should be transferred to your vApp via SCP, other copy method of choice, or using wget targeted at the Avamar appliance.  Here is a wget commmand that should work with modification for Avamar version.

cd /tmp; wget –no-check-certificate https://avamar_ip_or_dns/DPNInstalls/downloads/SLES11/AvamarClient-linux-sles11-x86_64-version.rpm

If you have the client downloaded, then only a few easy steps to get it up and registered to Avamar.

rpm -ivvh AvamarClient-linux-sles11-x86_64-version.rpm

cd /usr/local/avamar/bin

./avagent.bin –mcsaddr=avamar_ip_or_dns –register –daemon=false       (these are double dashes)

service avagent restart

That’s it!  If it shows as succeeded in the log then you’re good to go.  However, still a couple of more steps to perform in the guest.

Option A – Synchronously run pg_dumpall to local disk, followed by push to Avamar

This option is semi self-explanatory.  In this case the downside is sort of obvious, we are storing a backup locally on the database VM.  This adds capacity and IO requirements along with increases in backup times.  For smaller databases, this might make sense to have a copy locally (and is required under the VMware image protection method) but for larger databases this may not be the case.

Use your editor of choice and create a file called /usr/local/avamar/etc/scripts/backupvPostgres.bin.  This file should contain something similar to what you see below where anything is replaceable if you want to change database being backed up, destination of backup, version, etc.  Make sure to issue a chmod u+x on the file to ensure executable access.

#!/bin/sh
/opt/vmware/vpostgres/9.1/bin/pg_dumpall -U postgres -c > /var/vmware/vpostgres/9.1/vpostgres.bak

One more file to edit in the same way, /usr/local/avamar/var/vpostgres.cmd.  This file should contain one line as follows, which references the file specified after the “>” above.  This file tells Avamar which file to target when performing the backup.  Remember to also set executable permissions on this file (chmod u+x).  Now there is plenty more that can be done with these combination of files, but for now let’s keep it simple with a single file that gets overwritten.  Important NOTE:  If you did the image level backup process with a cron job, this should be removed if using the guest method!  You want Avamar to now issue the command to backup the database, and not a cron job.

/var/vmware/vpostgres/9.1/vpostgres.bak

Option B – Asynchronously run pg_dump to a named pipes per database, with immediate push to Avamar as data is dumped

This option leverages named pipes in Linux.  This basically means that a named pipe file (via mkfifo) is created and the pg_dump command leverages this file as output.  The interesting thing about this process however is that the file keeps the dump from Postgres and the push to Avamar in sync.  So as data is dumped from Postgres, Avamar receives and the named pipe acknowledges and accepts more data.  A backup is thus done without using local disks.  We cannot use pg_dumpall in this scenario since pg_dumpall closes the backup file per database which causes Avamar to prematurely think the backup is done.

The method here is similar to what you saw in A, with the caveat of /tmp as the path, one extra file “end_backupvPostgres.bin”, and naming of the database per file.  The end file kills all running pg_dump requests in case the Avamar job gets canceled.  This is important since a risk of using this method is that we basically let the pg_dump command run on its own without control (background) and it won’t exit until Avamar gets all of the data that is sent.  If the Avamar agent happens to exit early, the pg_dumpall command would never exit.  There other means to achieve what the end_ script is doing, but beyond this post for now.

/usr/local/avamar/etc/scripts/backupvPostgres-databasename.bin

#!/bin/sh
rm /tmp/databasename.bak
mkfifo /tmp/databasename.bak
/opt/vmware/vpostgres/9.1/bin/pg_dumpall -U postgres -c -f /tmp/databasename.bak databasename &>/dev/null &
disown
exit $?

/usr/local/avamar/etc/scripts/end_backupvPostgres.bin (end_backupvPostgtgres.bin needs to be added to the post-script section in the dataset under options and advanced)

#!/bin/sh
killall pg_dump &> /dev/null
exit 0

/usr/local/avamar/var/vpostgres-databasename.cmd (two dashes before label)

–label=databasename

/tmp/databasename.bak

Remember to (chmod +x) all of these files!  If you run the backup bin file individually, it will not work without a reciprocal avtar command to consume the backup file so you must kill this manually (ps auxw | grep pg_dumpall) and (kill pid).

Another thing to call out here is that each database requires two of three of the files above named uniquely.

Ok, that’s it for the guest itself.  Now let’s open the Avamar console.  If you haven’t installed this yet, it is available under the same Documents and Downloads location specified prior.  This time you look under the Windows for x86 link for AvamarConsoleMultiple-windows-x86-version.exe.  If installed, this is what you should see in your start bar.  What is done under the GUI is scriptable, via MCCLI but not shown in this post.  Most of what is shown below is only done once, where new vPostgres instances require guest level interaction versus anything else.

Create a Dataset

Open Tools –> Manage Datasets.

The Avamar Dataset is used to identify certain backup parameters that are specific to types of plugins and other options.  The datasets can be used in different places, but we will be using them by applying them to a vPostgres backup group.

New –> Enter the Name, possibly “vPostres Dataset”

Leave the defaults alone on this screen.  Select the Options Tab.  From the drop down, select Linux File System, and select the “Show Advanced Option” checkbox.  Under the Pre-script section look for “Run user-defined script at the beginning of backup” and enter “backupvPostgres.bin”.  As well look at the Advanced Option section for the “Client-side flag file” and enter “vpostgres.cmd” (case sensitive).  These two files should reference the two files created above.

Press Ok twice to return to the Avamar Administrator menu.

Create a Backup Group

The next step is to create a backup group to be used for any vPostgres instances that pop up in the future with new Linux agents.  Open Navigation –> Policy.

Now Right Click Clients (or anywhere you wish) and click New Group.

Let’s name it “vPostgres Databases”.  Unselect Disabled if you wish to use the group right away.

Now Select Next, and then choose the /vPostgres Dataset from the dropdown.

The next screen allow you to choose a schedule, retention, and which clients to include.  We selected defaults, and the client by name that we registered followed by Finish.  The next step is to press “Back Up Group Now”.  This will then kick off a job in the background to backup the database.

Now let’s take a look at the status or activity of the job.  Navigation –> Activity.

As you can see in the screenshot below of the activity window there are two backups.  I actually started one, and then did a subsequent one to show the deduplication that occurred at the source and how many new bytes were sent even with a new fresh database dump.  The backup of my small Postgres database occurred in 3 seconds each time, with 30% of the first backup sent to, while 2.4% sent on the subsequent backup.

And that concludes, the guest protection section when configuring these in a manual fashion for vPostgres.

Restoring

Please refer to the Postgres documentation for Backups where it refers to Restoring the Dump.  This is a very simple process where you stop the database, and issue a specific command “psql and <” to recover specific entities.  As well, please refer to VMware or application vendor that leverages the Postgres database before doing a DB recovery.  They may have specific steps or caveats to consider! 

Let’s look at the backups however in Avamar.  Open Navigation –> Backup and Restore.

Find the registered client in the list on the left, followed by a selection of the date from the calendar.  You should see listed on the right the backups of the vPostgres databases.

If you select a database, then you can then select from the dropdown below the navigateable tree on the Linux file system leading to the backup file.

That’s it!

First things, first.  I mention vPostgres (VMware’s Postgres), but in reality what I am going to demonstrate here is capable via any version of Postgres.  Also, there are older methods that have been used where the Postgres standard ODBC driver is used, but the downfall here is the requirement on a 32-bit Powershell instance to take advantage of it. 

In this post we are using the .NET Npgsql data provider which gives us some advanced postgres specific functionality while leverage whichever version of .NET/postgres that the provider versions support.

I will demonstrate how to get the Powershell vPostgres module working, create databases, tables, insert rows, and select from these rows.

This module was developed in Powershell 3, and wasn’t tested backwards to version 2.  If you’re not on version 3 yet, get there =) This should work under v2 as well however.

Setting Up vPostgres

Deploy vPostgres/Postgres

If using vPostgres vApp (assuming you have deployed the vApp), then the next step is to set the database password.  You can see from the following console screenshot of the vPostgres VM, that the /opt/aurora/sbin/set_password can be used to set the password once logged in.  The default password to login would be shown on the console if not set.

Ensure network access to Postrgres is open (optional)

There is a file in vPostgres vApp, pg_hba.conf, which is standard for postgres.  It is available at /var/vmware/vpostgres/9.1/pgdata/pg_hba.conf and can be edited to modify the type of network connectivity available to the postgres database.  In this case, since powershell is initiating DB requests, the powershell system would need network access.  An example of a wide open statement would be something as follows.  The default for vpostgres, should be open for md5 connections, but the below line could be added if you are having problems.

host all all 10.0.0.0/8 trust

Download Approriate Npgsl Version and Copy DLLs

The DLLs are available from the following link here.  If using Powershell 3 (hopefully) you will need the .NET 4 64-bit version.  There are two DLLs that are necessary Npgsql.dll and Mono.Security.Dll.  Once the data provider is installed, these should be located and copied into a folder that will contain the vpostgres.psm1 powershell module.  The next step is to unblock these DLLs (security feature in Pshell3).  This can be done by right clicking each dll and selecting properties, followed by Unblock at the bottom.  As well, you can use the Unblock-File cmdlet (Get-Item file | Unblock-File).

Download Unofficial vPostgres Powershell module

This module is available at the following link here.

Running

If all is good on the previous install steps then you should have these three files together in a directory. 

Load the module

From Powershell — Dir vpostgres.psm1 | Import-Module

This command should load the vPostgres module.  If there are no errors, then you are good to go.  You can run (get-command -module vpostgres) to see the commands available.

Connecting to vPostgres

Awesome, so now it’s time to connect to a vPostgres database!

The following command will connect to the database without specifying a database or port.  I am assuming here, that you don’t have one created yet to use. 

Connect-vPostgres -server ip/name -username postgres -password password  -database ” -port ”

If this works without error, then you’re good to go.  If there is an error it is most likely an authentication, connectivity, or pg_hba issue for network access.  Once connected I already issue a “select 1=2” to verify that we can talk with the database.

Notice that we also leverage the $vPostgresConnection global variable as a hash table to provide the ability to connect to multiple vPostgres instances as the same time.  We then issue $vPostgresConnection.”name” to retrieve said connection for use.

Creating a Database (test)

I am assuming that you need to create a database to use here.  If not then feel free to skip this part.

Get-vPostgresDataSet -connection $vPostgresConnection."ip/name" -query "CREATE DATABASE test WITH ENCODING=’UTF8′ OWNER=postgres;"

The command above will create the database, if nothing is returned then you’re good to go.  Notice how we use the connection parameter, this isn’t needed if we want to specify the connection details to this cmdlet.  See the module parameters.

Creating a table on test DB

Since we created the database, let’s reconnect to the vPostgres instance while specifying the database so that all of our following queries have the appropriate context set.

Connect-vPostgres -server ip/name -username postgres -password password -database test -port ”

If creating a new database, then we can create a table of name log with this command.   This table has two columns, a serial column which increments per entry, and a text field for hold an unlimited of text information.  This post isn’t meant to teach DBA basics, so google it if you’re scratching your head here =)

Get-vPostgresDataSet -connection $vPostgresConnection."ip/name" -query "CREATE TABLE log (id bigserial PRIMARY KEY,text TEXT)”

If there is no output, then all is good.  You can also issue a “SELECT * FROM log” in a following statement which should yield no results as well since the table is blank.

Create an Index (optional)

How about an index to speed up queries that leverage certain columns?  Again no DB expert, but you can look this up as well in plenty of places.

Get-vPostgresDataSet -connection $vPostgresConnection."ip/name" -query "CREATE INDEX ix_log_id ON log (id)”

Insert a Row

Now that we have a table and an index created, the next step would be to populate our table with a row.  There are three commands that I am running below.

In order to leverage a insert method that limits sql injection capabilities we can use handy sql parameters.  This means that we specify a PsObject with name=value’s which then get replaced after I submit the insert statement.

Here we create a PsObject with our name as the column name, and value as what should be inserted.

$SqlParam = New-Object -type PsObject -property @{text="testing"}

Now we do the insert, notice how the TEXT column name is used as (text) for the destination column, and the 1st position of (@text) with a @ symbol which then gets replaced by what’s in my PsObject “text” value field.  You can always forego the sqlParam and create an exact SQL statement where (@text) would be (‘testing’) instead.

Get-vPostgresDataSet -connection $vPostgresConnection."ip/name" -query "INSERT INTO log (text) VALUES (@text)" -sqlParam $sqlParam

If all is good, then let’s do a SELECT to see if the row is there!

Get-vPostgresDataSet -connection $vPostgresConnection."ip/name" -query "SELECT * FROM log" -sqlParam $sqlParam

Ok, so if the results look like what is in the screenshot from above then you’re good to go.  Your ID should be 1 and not 2 as I show.  I issued a “TRUNCATE TABLE LOG” prior to clear out a prior row.

Well that’s it for the basics!  If you’ve gotten this far, then Powershell is jiving with Postgres!

A Bit More Advanced

Bulk Copy Between Postgres Instances

So I mentioned earlier advanced functionality right?  Well here is something that is rather cool.  Say I have a bulk copy that needs to be performed between two distinct postgres databases (data pump).  Typically you might buy a commercial product to perform this operation.  However, since we are using the .NET data providers for Postgres we can use the advanced capabilities here.

There is a cmdlet called Copy-PostgresDataset which would be leveraged once you have established connectivity to both source and destination Postgres instances.  It would then initiate a binary copy with the Powershell system being the middle man to pump the binary data from one Postgres system to another.  The requirement here of course is that you have a table created already that has a proper schema that matches the initial table.

Copy-PostgresDataset -srcConnection $srcserver -dstConnection $dstserver -srcCommand "COPY (SELECT * FROM srctable) TO STDOUT" -dstCommand "COPY dstTable FROM STDIN"

Seems complex right?  Yeah, remember I am talking about between Postgres instances.  Another option here would be to consume each row into a PsObject and then do individual inserts per PsObject into the target database.  This option however is exponentially slower than a binary copy method.

Postgres Heroku HStore

This one is really cool!  A Hstore if you haven’t heard of it is a module in Postgres that allows for simple key->value lookups.  You can leverage this by enabling the hstore module, and then turning any column in a table into a Hstore column.  This then allows you to have that column have any number of key->value pairs which allow for schema-less insert’s and following lookups.  This is the ultimate lazy-dba tool!  Possibly no schema definition ahead of time!

There is a cmdlet in the module, Get-PostgresHstore, which can be used but let’s save this for another post!

Anyways, plenty more you can do with this.  Take a look at the data provider docs, and have fun!

In continuing with the recent theme, this post will focus on leveraging previous work in order to backup vCD configuration.  The configuration I am referencing here is simply the XML that is retrievable from VMware vCloud Director’s REST interface per object type.  For example, a VApp has a configuration XML file that represents the capabilities, virtual machines, and other configurable items.  It is this file that can be used as reference at a later time in case a recovery is needed. 

Previous posts have showed how to export the XML of a vCD object, and in this post we will take it a step further and allow you do to all objects, single, or multiple types of objects from one simple cmdlet.

We will then take the output of these objects and store them in a way that is similar the structure of the API calls themselves.  This is important as it later allows us to use this as a historical view that is similar to the native API calls but cached for later use.

The following screenshot shows that we have a structure built within the “101512” directory that beings with API.  As you drill through each level you can see we have expanded externalnet, networkpool, and providervdc.  On the top right you can see we have selected the providervdc directory and are showing the xml file that was saved for a specific provider with this GUID.  Along with that if the provider had links, we have downloaded those as well and included separate directories.  In this case you can see the “availableStorageProfiles” as another sub-element.

What vCD queries does this currently work with?  This list below is based on the Query cmdlet that I use, Search-Cloud. 

AdminCatalog, AdminCatalogItem, AdminGroup, AdminOrgNetwork, AdminOrgVdc, AdminUser, AdminVApp, AdminVAppNetwork, AdminVAppTemplate, AdminVM, Catalog, CatalogItem, Datastore, ExternalNetwork, Group, Host, NetworkPool, Organization, OrgNetwork, OrgVdc, Portgroup, ProviderVdc, Right, Role, User, VApp, VAppNetwork, VAppTemplate, VirtualCenter, Vm

How can I run this?  First check with the previous posts.  Look at the pre-requisites, download the script, and test it out!

Rolling Back Objects in vCloud Director

What Changed- vCloud Director Integrity Scripts Unleashed

Backing Up All vCD vApp Configurations

Powershell Module available http://velemental.com/wp-content/uploads/2012/10/custom_vcd_cmdlets2_101612.zipHere.

Once you are good there, you can continue to actually running the new Backup-vCD cmdlet.

Backup-vCD

This cmdlet receives a couple of inputs.  The first being the QueryType which allows you to be more granular per QueryType (listed above) or simply enter “Any” to receive objects from all queries.  The second important parameter is the “OutDirectory” parameter which defaultly uses “.” but can leverage any directory you specify to store the root of the output “api” and everything forward.  This can allow for a form of archiving each run of the cmdlet.

Backup-vCD -QueryType All

Backup-vCD -QueryType NetworkPool -Verbose

If you’re curious about what is happening during the run you can always use the -verbose parameter.

Backup-vCD -QueryType NetworkPool,VirtualCenter -Verbose

What are the downsides here?  The process is single threaded so if you have a massive amount of information, you may need to multi-thread the process by selecting some of the bigger download items (vapp,vm) as separate cmdlet runs on different systems or on the same system.  Also keep in mind that we do not issue a call to vCD to pause configuration changes, so as we download the information from the API it is possible certain things (VMs in vApp for example) do not reflect each other since a VM configuration may have changed during backup time. 

This is going to be a quick post showing how you might backup your vApp configurations per organization.

See the following posts that describe some of what we are using here.

Permanent Link to Rolling Back Objects in vCloud Director

Permanent Link to What Changed- vCloud Director Integrity Scripts Unleashed

This is actually extremely simple!  The idea would be to backup the VMs using a standard vSphere backup method and then to use a dedicated Windows Host and backup the vCD configuration Xml files as well.  This gives you not only the VMs, but how they were configured in the case you need to do a recovery.  See the following simple example.

Backing up all vApps in an Org and saving them locally

Get-Org BRS-vLab-Eng | Get-CIVApp |  %{ $VApp=$_; $VApp | Export-CIXml | Out-File "$($VApp.name).$($VApp.org).vCD.xml" }

Now let’s take a look at one of those files!

(

(gc .\vApp1.BRS-vLab-Eng.vCD.xml)).VApp

As you can see, the whole vApp structure can be navigated.  So if you have any questions about how something was configured, what id a VM was, or any number of things you can find it easily!  You can also take those files, zip them up, email them or any number of different things since they live on a Windows host. 

This stuff is truly the tip of the ice berg, enjoy!