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