Step #09 : Demonstrate Google Spreadsheet to OpenERP data pump


Welcome to the final step in this series.

If you have followed through from the beginning you will have a cloud-based platform with three valuable tools:
  • a SSH terminal server accessible by HTTPS
  • a Salt Stack master
  • the Google spreadsheet to OpenERP data pump.
Our last task is to understand how to use the data pump to control OpenERP instances.

There should be no need for further documentation of it here; the web site covers the whole thing in detail.  

Here's the video:

Tasks performed

  1. Update the "creds" page of the controller spreadsheet with IP address or domain name of the target OpenERP installation
  2. On the same page, also provide a name for the database to use, and the super user password of the OpenERP installation
  3. To test connectivity, run the pump, specifying the controller spreadsheet's ID key with the -k switch.  This first run should show that a connection was achieved, but no tasks run.
  4. There are eight possible subtask executions per task row.  A pair of bit maps control which of the eight are executed. If a row has 5 action steps and only the 4th should be executed. Using binary arithmetic the settings should be: complete 23, incomplete 8, length 31. Any error in that will be seen in the "Chk" column (P).
  5. To create the new OpenERP data base, we want to run the second action step in the first row.  We'll also run the chkTask action.  It simply reports that the indicated Model Class is available and has a method called chkTask.  There are two action steps in total, so the length is 3. 
  6. Run the pump, with the same command as before.
  7. Examine OpenERP to see if the database "demoVideo" was actually created.
  8. Log in to the "demoVideo" database in OpenERP.
  9. I never found a way to set the "Technical Features" check-box for the administrator via XMLRPC.  So this is an unfortunate, one-time, manual step.  A workaround is to restore a database backup, with similar default settings already done.
  10. We repeat the preceding steps for adding some national regions and languages.
  11. The remainder of the video attempts to point out how all the various parts of the pump work together.
An overview of all the steps is available here:


Step #08 : Install and check OpenERP data pump


In the next step in this series we will Demonstrate Google Spreadsheet to OpenERP Data Pump.

First, however, we have to install and configure it.  In this video you will see how.

For deeper understanding please read the documentation for Google Spreadsheets to OpenERP Data Pump.

Success with this step depends on the previous step Install And check GSpread.  In fact, the Salt state stack for the data pump needs to be able to execute GSpread's self-test.  Most of this video is concerned with preparing for that.

Here's the video:

Tasks performed

  1. Copy the access and refresh tokens that were acquired at the end of the previous step, and apply them to the pillar init.sls for gdata_oerp_pump
  2. Open a new or unimportant spreadsheet and copy'n paste its title, URL  and key into the pillar init.sls for gdata_oerp_pump. These control GSpread's self test
  3. Run salt 'name of the target minion' state.sls gdata_oerp_pump.
  4. Verify that the install proceeded correctly and that the embedded GSpread self-test encountered no errors.

An overview of all the steps is available here:


Step #07 : Install And check GSpread


Our purpose with this video series is to demonstrate how to set up a cloud-based, permanent, yet part-time, cloud control workstation.  One we can depend on to be there when we activate it, but switched off 95% of the time: casual DevOps if you will.

Our goal with the particular use-case example is to install and use the Google spreadsheets to OpenERP data pump.  That tool needs GSpread to connect to Google.

In this video we show how to use the Salt state stack we created for installing GSpread and the GData_OpenERP_Data_Pump.

We want our connection to Google to use OAuth.  That way, our username and password do not get left lying around by accident. 

Most of the following video is about preparing our Salt "pillar" file with the first phase of OAuth credentials before we run it.  Under the covers, our Salt state patches a JSON file with those data items and deposits it in GSpread's tests directory.  For the second phase of OAuth -- obtaining access and refresh tokens -- we use the tool get_google_oauth2_creds.py from the same directory.

The tool does several useful things:

  1. Guides you through authorizing GSpread to use your GMail for sending email.
  2. Sends email to a 3rd party requesting permission for GSpread to access their spreadsheets.
  3. Generates files in several formats containing the resulting access and refresh tokens.  One of those is qiktest.py; a quick check that the connection works.
Once we have these tokens, checked with qiktest.py, we can move on to the next step

Here's the video:

Tasks performed

  1. With GateOne as our SSH entry point, we no longer need SSH terminal windows in our work station
  2. Create two SSH windows in GateOne logged into the Salt master machine and become to root in one of them with sudo -sHu root
  3. As root, re-point Salt's symbolic link /srv to point to SaltStackToolSet/gdata_oerp_pump/srv
  4. Open the APIs and Auth >> Credentials sub-menu of our project in Google's API Console
  5. Edit the pillar file init.sls, providing values for redirect_uri, client_secret and client_id obtained from the API console
  6. Run Salt state.sls against the master's own minion to install just GSpread.  (We'll do the same for gdata_oerp_pump in the next video)
  7. In the tests subdirectory of the temporary gspread build directory, run get_google_oauth2_creds.py
  8. Pick the 2nd method and provide the project Google ID and the ID of the 3rd party whose spreadsheets are to be worked on
  9. Authorize the project's GMail account to be used for SMTP by obtaining the verification code and pasting it back into get_google_oauth2_creds.py
  10. Run a quick test to be sure we are connecting correctly.
An overview of all the steps is available here:


Step #06 : Install and use GateOne; the HTTP terminal server


After this step in the series you'll be able to continue from any machine, anywhere, even a Chromebook!

We finished the previous step, Install OpenERP using a SaltStack state file, with OpenERP up and running in a Salt minion.  Inappropriately for a production-grade service, we installed it in a disposable pay-by-the-hour virtual machine.

On the other hand, for occasionally required services such as a Salt master, pay-by-the-hour VMs like iwstack.com's are a boon for several reasons: cost and security.

I have a very lightweight VM (384MB) with a minuscule virtual disk for my administrative work.  It's dead for most of its life.  When I need to work on a client's machines -- off site back up, for example -- I attach their admin virtual disk to my little admin VM, boot it up, do my housekeeping work, like saving a backup copy, and kill it again.

I used to use SSH for that work, but really regretted it one day when a client had an emergency and I was hours away from access to any copy of my SSH private key.

That's when I found GateOne.
GateOne is a terminal server with a HTTP client.

Now, my SysAdmin work day involves :
  1. Logging into iwStack.com 
  2. Booting up my admin VM
  3. Logging into the GateOne terminal of the admin VM using my Google ID
  4. Finding all my old terminal sessions up, running and logged back in
  5. Dusting the shelves and mopping the floor
  6. Killing my admin VM
  7. Logging out of iwStack.com 

Here's the video:

Tasks performed

  1. Make the Master also be it's own minion, check that both daemons are running and have the Master accept the Minion's keys
  2. Finalize the Salt state stack for GateOne, link it into Salt's path and then run it
  3. Start up GateOne
  4. Access GateOne by IP address in the browser.
  5. Fix the missing "origin" in GateOne's configuration
  6. Restart GateOne, and gain clear anonymous access to it
  7. Ensure we are already logged in to Google
  8. Switch the "auth" configuration parameter from "none" to "google"
  9. Restart GateOne, and get automatically redirected to Google's confirmation page
  10. Confirm our trust in the GateOne page
  11. Gain clear authenticated access to GateOne
  12. Use GateOne's identity manager to create an SSH key pair.
  13. Ensure SSH is intalled and that a .ssh directory exists with correct permissions
  14. Add the public key to tour authorized keys file
  15. Open an SSH session with the server, authenticate with SSH keys instead of UID/PWD

An overview of all the steps is available here:


Step #05 : Install OpenERP using a SaltStack state file


We finished the previous step, Install SaltStack master and minion, with a Salt minion accepted for communication with a Salt master.

Now, at last, we can install OpenERP using the "stack" for that purpose published here : FleetingCloud's SaltStack Tool Set on github.com.

Why SaltStack?

Salt requires a bit of conceptual understanding. So here goes...

The point of Salt is to offer a standard declarative language for preparing machines to serve their intended purpose.  As much as possible it hides differences between operating systems.

In this code fragment we see how to customize the PostgreSQL configuration file:
        - blockreplace
        - name: /etc/postgresql/9.1/main/postgresql.conf
        - marker_start: "# START SaltStack managed zone -DO-NOT-EDIT- -----"
        - marker_end: "# END SaltStack managed zone -----------------------"
        - prepend_if_not_found: True
        - backup: '.bak'
        - show_changes: True

        - filename: /etc/postgresql/9.1/main/postgresql.conf
        - name: postgresql-accum
        - text: "listen_addresses='{{ grains['ip_interfaces']['eth0'][0]}}'"
        - require_in:
            - file: postgresql.conf

The first of the two sections sets up a "managed zone" at the beginning of the file /etc/postgresql/9.1/main/postgresql.conf. The require_in phrase of the second section says that the postgresql.conf state should include the "listen_addresses" configuration item. In Salt, "grains" are named attributes of the minion. So, for example, the grain "ip_interfaces" is a list of interfaces on the minion. Listed item "eth0" has an array of attributes of which the first ("0th") is the IP address that PostgreSQL needs to be told about. 

All this may seem like a very verbose and roundabout way of patching a file, but it is advantageous because of platform independence .  Also, once you are familiar with the syntax it becomes much more readable that reams of bash script.

Finally, the main advantage is that it can all be kept on a single master server, and pushed out to hundreds or thousands of minion machines according to a wide variety of targeting and filtering rules.

The /srv Symbolic Link

While Salt's minion targeting is very powerful, it is very advanced usage.  For this video series I have placed each installation state stack in a separate named directory of SaltStackToolSet.  Each one has a single immediate subdirectory named "srv".  For each installation run of salt "*" state.highstate I simply set a symbolic link from Salt's default state stack directory named "/srv".  Not recommended for production, but clear and simple for an introductory video.

Here's the video:

Note correction:  Please refer to Update on recent changes to Video #5 for errata updates.

Tasks performed

  1. Go to GitHub to get the URL of zip'd FleetingClouds / SaltStackToolSet.
  2. Download and unzip the tools.
  3. Rename the directory to SaltStackToolSet
  4. Copy the example pillar file and edit the new one with the correct configuration parameters for OpenERP.
  5. Set up a symbolic link from Salt's default state stack directory ("/srv") to the "srv" directory of our OpenERP tool.
  6. Run salt 
  7. Restart the minion
  8. Test that OpenERP is working.
An overview of all the steps is available here:


Step #04 : Install SaltStack master and minion


In the first three steps we set ourselves up with a working environment "out in the cloud", thanks to iwStack's implmentation of Apache CloudStack.  Although, as underlined in Step #03 : Create template from VM and 2nd VM from template any two Ubuntu (and probably any Linux) machines would do just as well.

I have experimented with Chef, and a bit with Ansible.  I have used RunDeck a lot.  I've found that SaltStack is closest to what I want -- declarative provisioning with open source modularity in Python.

In this video, I take you through the steps to get a SaltStack master and minion installed and interacting.

SaltStack Bootstrap is Unix shell script that gives you a complete installation with a single command.  With switch "-P" it installs a minion; with the switches "-M -N", it installs a master.  We will do both, and then get them talking.

Here's the video:

Tasks performed

  1. Connect to the minion by SSH, change its "host name", tell it the name and IP address of the Salt master and then reboot it.
  2. Go to GitHub to pick up the latest SaltStack Bootstrap. Note that the usage instructions changed shortly after I made the video.  You should find, on that page, the bootstrap install that best suits your needs. I use this now.
  3. As root, paste the indicated text into a SSH terminal and let it run.
  4. Reboot to ensure the minion starts up as a working SaltStack minion.
  5. Check the log files to ensure it is trying to contact the master.
  6. Start up the master, and repeat the above.
  7. When the master is working, ensure that the minion has presented keys and accept them.

An overview of all the steps is available here:


Step #03 : Create template from VM and 2nd VM from template


In the first two steps we created an Ubuntu 13.10 virtual machine in an Apache CloudStack virtual environment.

Now we will see how to make a second virtual machine from a template made from the first one.

Note : Steps one through three have been done in a pay-by-the-hour CloudStack virtual machine environment.  This is completely optional  However, you will certainly want multiple machines, virtual or physical, for steps four through nine.

Here's the video:

Tasks performed

  1. Stop using the built-in console. Get the IP address and connect via SSH
  2. Make sure our virtual server is fully up-to-date using these apt-get commands
  3. Stop the server in order to use its disk.
  4. Got to the "Storage" area, find the disk and start creating a template.
  5. Create a new instance of the VM, and call it SaltMinion.
  6. Get the new machine's IP address
  7. Log in to it using SSH.

An overview of all the steps of this series is available here:


Step #02 : Install Ubuntu 13.10 in the VM (quick review, if you need it)


Here's the video:

There really isn't anything to say here.  This is just a default installation of Ubuntu 13.10. An overview of all the steps is available here:

Step #01 : Prepare Cloud Virtual Machine in iwStack.com


Please see Tasks Performed, below, to get an idea of the contents of this first video :

I use iwstack rather than Amazon or Heroku (or etc., etc.) for a number of reasons :
  • based on Apache CloudStack, there's no supplier lock-in at all.
  • for three years now, I have had truly excellent service from Prometeus, the parent company.
  • I can scale resources on demand, billed per hour of usage, with full root access, or turn everything off and pay a few cents a day for storage of my virtual appliances.
You will have not need iwstack, or even CloudStack for that matter, for videos four through nine.  I use a server in iwstack for administering the various VPS servers my customers have around the web, and shut it down when I don't need it.

Tasks performed

  1. Log in to iwstack
  2. Select the Templates menu
  3. Select the ISO files view
  4. Open Ubuntu's downloads page
  5. Get the URL of the desired version
  6. Fill in the ISO file registration sheet 
  7. Select the Instances menu
  8. Click the Add Instance button
  9. Complete the specifications in the instantiation wizard
  10. Open the terminal console to begin the Ubuntu installation procedure.
An overview of all the steps of this series is available here:

Handbook for the DevOps for OpenERP Video Playlist


Over the next week or two I plan to launch a 9 part series describing the environment I've set up to manage my customer's OpenERP sites "in the cloud".

DevOps for OpenERP is an extremely concise (under 30 mins), quick start guide to installing virtual machines in a cloud, provisioning them remotely with OpenERP instances, and then managing loading them with data from Google Spreadsheets via OpenERP's XMLRPC interface.

Tools Used

You will be introduced to the following Free and Open Source Software (FOSS) products:
Every tool used is freely available on the Internet. 

Note that I do pay iwStack.comby the hour, for hosting of Apache CloudStack, but CloudStack itself is a free download, if you already have a server on which to install it.


  1. Prepare cloud virtual machine in iwStack.com. (video)
    iwStack.com rents out an Apache CloudStack implementation. 
    CloudStack has the excellent advantage of allowing you fine grained control over root access virtual machines without any supplier lock-in at all.
    Here you will get a quick guide to creating a new virtual machine. 
  2. Install Ubuntu 13.10 in the VM (quick review, if you need it) (video)
    I've added this step in the blog just to "prove" that I use plain vanilla Ubuntu Saucy.  Since I promise to show you every you every single required step, installing Ubuntu is included.
  3. Create template from VM and 2nd VM from template.  (video)
    "Template" is Apache CloudStack's term for a ready to use VM, a.k.a virtual appliance. Here I show you how to get a second server up and running quickly from a "template" created from the first server.
  4. Install SaltStack master and minion.   (video)
    SaltStack is network provisioning for Python people.  OpenERP is written with Python as is much of Ubuntu's administrative stuff; the compatibility makes sense.
  5. Install OpenERP using a SaltStack state file. (video)
    I have created a SaltStack state file that installs PostgreSQL and OpenERP. 
    Here, I show you how to use it.
  6. Install and use GateOne; the HTTP terminal server (video)
    Normally, to access machines in your cloud you have to run an SSH client somewhere. GateOne is an SSH terminal server you access via HTTP from any browser.  You can authenticate yourself with your Google ID.  Run from Apache CloudStack, you can start up a GateOne server, do admin tasks on other servers using Salt, and then shut it down again. It's very hard to break into a server that is switched off! 
  7. Install And check GSpread (video)
    GSpread is a very easy to use, Python API for Google Spreadsheets.  I show you how to install, test and use it.
  8. Install and check OpenERP data pump (video)
    My data pump wraps GSpread and the OpenERP XMLRPC channels with a high-level data handling facility.  Typically you just need to get your data into correctly named columns in a Google Spreadsheet in order to be able to load it into OpenERP at high speeds.
  9. Demonstrate Google Spreadsheet to OpenERP data pump (video)
    I run through a few of the commands and data loading capabilities of the pump.

My quality rules 

  • I really hate tutorial videos that waste my time!  So, each video is 2 to 4 minutes: no fluff, no dead time.  You'll know in a minute or two whether the video is going to be useful to you.
  • Dark, blurry videos are stupid.  So, these are recorded as 1280x720 45fps H.264.
  • I dislike tutorial videos with audio!  So, there's no sound: you can run through at high speed, skimming text annotations and stopping where there's something you need. "Seeing is believing.", " A picture's worth a blah, blah blah"
  • I get mad if a tutorial video ends with something that doesn't work!   So, I start everything from original downloads and show you every installation step, complete. This ensures you have a good chance of getting the demonstrated results.  Be prepared to pause playback very frequently to get the full details. (If you have the right browser version you may have a playback speed control among the buttons on the bottom right.)
  • Then there're videos with a lot of typing and no where to get the text. So I provide a page with all the longer bits of text you'll need to type.

Usage Notes

  • Be prepared to pause the video frequently.  Accelerating the video is a bit like Dolby noise reduction -- boost things way up above the noise level, then rein back at the point of delivery.  You know what is noise for you.
  • You can remove any annotation that gets in your way.  Just click the little black ball at the top-right of the annotation's rectangle.
  • Annotations that have an arrow in a box on the right-hand side are links to text or URL's you'll need in order to follow along completing the associated action.