Install Postgis with ansible

My goal was to automate the installation of postgres and its postgis extension on a virtual machine, following more or less this procedure.
I was talked into trying ansible and decided to head this way.


At first I installed a vanilla Ubuntu12.04LTS on a virtual box. I've just added my ssh public key.

Then I followed the general instruction to have ansible running.


I came up with two playbooks, one to install the RDMS and its dependencies, the other to add a postgis enabled database added.

my hosts file is like :

[vms]
vm1204 ansible_ssh_host=192.168.1.64 userhome=/home/remoteuser

Install Postgres + PostGIS

---

- hosts: vms
  sudo: True
  gather_facts: False

  tasks:
  - name: ensure apt cache is up to date
    action: apt update_cache=yes
  - name: ensure packages are installed
    action: apt pkg=$item
    with_items:
        - build-essential
        - postgresql-9.1
        - postgresql-server-dev-9.1
        - libxml2-dev
        - proj
        - libjson0-dev
        - xsltproc
        - docbook-xsl
        - docbook-mathml
        - libgdal1-dev


- hosts: vms

  tasks:
  - name: create download dir
    action: file dest=${userhome}/download state=directory
    
  - name: download GEOS
    action: get_url url=http://download.osgeo.org/geos/geos-3.3.5.tar.bz2 
                    dest=${userhome}/download/geos-3.3.5.tar.bz2 mode=0440
  - name: untar GEOS
    action: command tar xjf geos-3.3.5.tar.bz2 chdir=${userhome}/download/
  - name: configure GEOS
    action: command ./configure chdir=${userhome}/download/geos-3.3.5
  - name: make GEOS
    action: command make chdir=${userhome}/download/geos-3.3.5
    
    
- hosts: vms
  sudo: True    
  
  tasks:
  - name: install GEOS
    action: command make install chdir=${userhome}/download/geos-3.3.5
    
    
- hosts: vms

  tasks:
  - name: create download dir
    action: file dest=${userhome}/download owner=isisafe state=directory
        
  - name: download PostGis
    action: get_url url=http://postgis.org/download/postgis-2.0.1.tar.gz 
                    dest=${userhome}/download/postgis-2.0.1.tar.gz mode=0440
  - name: untar PostGis
    action: command tar xzf postgis-2.0.1.tar.gz chdir=${userhome}/download/
  - name: make PostGis
    action: command make chdir=${userhome}/download/postgis-2.0.1
    
    
- hosts: vms
  sudo: True    
  
  tasks:
  - name: install PostGis
    action: command make install chdir=${userhome}/download/postgis-2.0.1
  - name: install PostGis
    action: command make comments-install chdir=${userhome}/download/postgis-2.0.1
  - name: post install ldconfig
    action: command ldconfig
   

then to launch it:

nil@home$ ansible-playbook create_postgis_db.yaml -u remoteuser -K -v

sudo password: 

The install took a very long time on the VM, I should get a better computer.

Creating a PostGIS enabled database

---

- hosts: vms
  sudo: True
  sudo_user: postgres
  gather_facts: False

  vars_prompt:
    - name: "dbuser"
      prompt: "user login"
      private: False    
    - name: "dbname"
      prompt: "database name"
      private: False       
    - name: "dbpassword"
      prompt: "user pwd"
      private: True    
    
  tasks:
      - name: ensure database is created
        action: postgresql_db db=$dbname
        notify:
          - convert database to postgis
          - convert database to postgis topology
    
      - name: ensure user has access to database
        action: postgresql_user db=$dbname user=$dbuser password=$dbpassword priv=ALL

      - name: ensure user does not have unnecessary privilege
        action: postgresql_user user=$dbuser role_attr_flags=NOSUPERUSER,NOCREATEDB

  handlers:
      - name: convert database to postgis
        action: command psql -d $dbname -c "CREATE EXTENSION postgis;"
      - name: convert database to postgis topology
        action: command psql -d $dbname -c "CREATE EXTENSION postgis_topology;"


    

Let's add a new database on the remote server

nil@home$ ansible-playbook create_postgis_db.yaml -u remoteuser -K -v
sudo password: 
user login: nil
database name: new_map
user pwd:

check on the VM that the database was created

postgres@vm1204$ psql 

postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 new_map    | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/postgres         +
            |          |          |             |             | postgres=CTc/postgres+
            |          |          |             |             | nil=CTc/postgres

postgres@vm1204$ psql new_map

new_map=# \dt
               List of relations
  Schema  |      Name       | Type  |  Owner   
----------+-----------------+-------+----------
 public   | spatial_ref_sys | table | postgres
 topology | layer           | table | postgres
 topology | topology        | table | postgres

Note : I don't clean up the source files after PG install

Customize django_tinymce widget in django admin

The lazy path using an HTMLField can be customized easily:

in settings.py

TINYMCE_DEFAULT_CONFIG = {
    'plugins': "xhtmlxtras",
    'theme': "advanced",
    "theme_advanced_buttons1" : "bold,italic,underline,separator,bullist,numlist,separator,outdent,indent,separator,undo,redo",
    "theme_advanced_buttons2" : "link,unlink,separator,removeformat,separator,sub,sup,separator,abbr",
    "theme_advanced_buttons3" : "",
    'cleanup_on_startup': True,
    'custom_undo_redo_levels': 10,
}
 

all options :

• http://www.tinymce.com/wiki.php/Configuration:Advanced_theme
• http://www.tinymce.com/wiki.php/Buttons/controls

change the size of string column using sqlalchemy migrate

Why on earth have I done a table description like

CREATE TABLE address
(
  id serial NOT NULL,
  street text,
  city character varying(255),
  province character varying(10),
...

what a scrooge, 10 chars only, you can't live in saskatchewan with that. I had to migrate.

After a while I managed to get the syntax that makes it work

def upgrade(migrate_engine):
    meta.bind = migrate_engine
    address = Table('address', meta, autoload=True)
    address.c.province.alter(type=VARCHAR(length=255))

Eclipse pydev template to generate sql alchemy migrations

How often have you committed code, just to realize your missing a column in your sqlAlchemy model?
If you're using eclipse IDE, you can leverage the templates to write a migration script in no time.
Go to windows -> preferences -> PyDev -> Editor -> Templates

then create a new template with :

• name : Module: Migration SQLA
• context: New Module
• description: SQLA migration

# -*- coding: utf-8 -*-
'''
Created on ${date}

@author: ${user}
'''

from sqlalchemy import *
from migrate import *

meta = MetaData()

def upgrade(migrate_engine):
    meta.bind = migrate_engine
    some = Table('some',  meta, autoload=True)
    if not 'somename' in some.columns:
        new_col = Column('somename', Unicode(255))
        new_col.create(some)
            ${cursor}

def downgrade(migrate_engine):
    meta.bind = migrate_engine
        some = Table('some',  meta, autoload=True)
        some.c.some_col.drop()

And that's it ! you can now go into your migrations folder and do:
New -> PyDev Module, and choose your new template

default value for text function using lxml

Say we need to parse this XML

<pack xmlns="http://ns.qubic.tv/2010/item">
        <packitem>
            <duration>520</duration>
            <max_count>14</max_count>
        </packitem>
        <packitem>
            <duration></duration>
            <max_count>23</max_count>
        </packitem>
</pack>

if you want to parse it and retrieve the values in tuples

root = etree.fromstring(xml)
namespaces = {'i':"http://ns.qubic.tv/2010/item"}
packitems_duration = root.xpath('//i:pack/i:packitem/i:duration/text()', 
    namespaces=namespaces)
packitems_max_count = root.xpath('//b:pack/i:packitem/i:max_count/text()',
    namespaces=namespaces)
packitems = zip(packitems_duration, packitems_max_count)

>>> packitems
[('520','14')]

The problem is the zip result miss a value. That's because lxml returns nothing instead of None or empty string. Let's change that.

def lxml_empty_str(context, nodes):
    for node in nodes:
        node.text = node.text or ""
    return nodes

ns = etree.FunctionNamespace('http://ns.qubic.tv/lxmlfunctions')
ns['lxml_empty_str'] = lxml_empty_str

namespaces = {'i':"http://ns.qubic.tv/2010/item",
              'f': "http://ns.qubic.tv/lxmlfunctions"}
packitems_duration = root.xpath('f:lxml_empty_str('//b:pack/i:packitem/i:duration)/text()',
    namespaces={'b':billing_ns, 'f' : 'http://ns.qubic.tv/lxmlfunctions'})
packitems_max_count = root.xpath('f:lxml_empty_str('//b:pack/i:packitem/i:max_count)/text()',
    namespaces={'b':billing_ns, 'f' : 'http://ns.qubic.tv/lxmlfunctions'})
packitems = zip(packitems_duration, packitems_max_count)

>>> packitems
[('520','14'), ('','23')]

more info on extending lxml http://lxml.de/extensions.html#xpath-extension-functions

zc.buildout in Ubuntu 10.04 LTS

you have a nice buildout in development and it's time to move to the production server

buildout bootstrap
./bin/buildout
.... 
     pkg_resources.Requirement.parse('zc.buildout')).location
AttributeError: 'NoneType' object has no attribute 'location'

ouch!

this workaround worked for me

sudo easy_install zc.buildout==1.4.4
buildout bootstrap

zc.buildout will upgrade itself (to 1.5.2 at the time of writing)

and ./bin/buildout will work again as it should

using toscawidget behind apache mod_wsgi

Turbogears Ticket #2309

The problem, when it occurred, would be visible when someone clicked the login link (going to /login ). The came_from would, sometimes, be set to "/toscawidgets/resources". When I remove the "modname" parameter from the JSLink above, the problem never occurs.

Quick fix: in the .wsgi script add those 3 lines at the end

import paste.fixture
app=paste.fixture.TestApp(application)
app.get("/") 

Full explanation (copy/paste from Diez Roggish) (http://groups.google.com/group/turbogears/browse_thread/thread/ca4e3fd12a49d44/fa8721260b68741b)

The snippet will in fact force the mod_wsgi-process to pre-load the full application controller hierarchy. As a result, all the tosca-widgets should be instantiated, and their resources registered in the resource-middleware.

The reason this needs to be done is because usually, you run mod_wsgi with several processes. Lets name these P1-P10.

Now if a request hits a freshly started apache (without the "magic lines"), it hits P1. That initial request is directed at a controller action, so the full TG2 app with it's controller hierarchy is bootstrapped. All is nice and easy.

The resulting HTML now contains several references to static resources from TW. So the browser will - in parallel - request these. Because P1 is just finished, it probably won't even getting any of these requests. Instead, the are distributed amongst P2-P10.

And these processes are not bootstrapped yet. So, TW doesn't know about any resources when asked, and the result is a 404 or 500.

And for that reason, the "magic snippet" is there.

It is unfortunate, but I don't see any other way. You could try & have some sort of finalizing method-call into the Pylons/TG2-stack that essentially does the same, but the underlying technology won't be changeable.