Hello Postgres and REST in Crystal

Tagged crystal, postgres, rest  Languages bash, crystal, yaml

NOTE: This does not work with Crystal 0.25.2.

Install Crystal

brew install crystal-lang
mkdir projects/hello
shards init

Set up dependencies

    github: will/crystal-pg
    version: "~> 0.5"
    github: sdogruyol/kemal
    version: "~> 0.16.1"

Write a simple REST API

require "kemal"
require "json"
require "pg"

PG_URL = "postgres://postgres@localhost:5432/xxx"
DB     = PG.connect PG_URL

get "/" do |env|
  env.response.content_type = "application/json"
  users = DB.exec("SELECT * FROM users")
  users.to_hash.map do |user|
    {first_name: user["first_name"].as(String), last_name: user["last_name"].as(String)}


How to debug Ansible variables

Tagged ansible, debug, hostvars, variables  Languages bash, yaml

Print all variables for all hosts from the command line:

 $ ansible -i inventory/local -m debug -a "var=hostvars" all

Replace hostvars with any of the following to print:

  • ansible_locals
  • groups
  • group_names
  • environment
  • vars
  • ansible_sucks

Print all variables for all hosts from a playbook:

- hosts: all
    -  debug:
        var: hostvars[inventory_hostname]
        # -vvv to debug !!!!
        # verbosity: 4

Print all variables:

- name: print ansible_local
  debug: var=ansible_local

Ansible: How to find the IP address of a specific network interface when there are multiple network interfaces

Tagged ansible, ip address, network  Languages yaml

Here’s how to find the IP address of a specific network interface that matches a given CIDR:

- hosts: all
  gather_facts: false
    - set_fact:
        prod_ip_addr: "{{ item }}"
      when: "item | ipaddr('')"
      with_items: "{{ ansible_all_ipv4_addresses }}"
    - debug: var=prod_ip_addr

This is useful for example when you have a separate management network interface.

It’s almost as easy as trying to explain what the script is doing in plain English.

How to set the setgid flag with Ansible

Tagged ansible, setgid, setuid  Languages yaml

This is how to set the setgid flag with Ansible. Tested with Ansible version 2.4.

- name: Create directories and set setguid
    path: "{{item}}"
    state: directory
    owner: www-data
    group: www-data
    # NOTE: 2 = setguid flag. The prefix 0 is required by Ansible
    mode: 02770
    recurse: true
    - /var/www/
  become: true


Setting the setgid permission on a directory (“chmod g+s”) causes new files and subdirectories created within it to inherit its group ID, rather than the primary group ID of the user who created the file (the owner ID is never affected, only the group ID).

See https://en.wikipedia.org/wiki/Setuid

Detecting software version with Ansible

Tagged ansible, version  Languages yaml

With Ansible, detecting the version of, for example, Redis or Racket can be done like this:

- name: Detect Redis version
  # Input: Redis server v=3.2.1 sha=00000000:0 malloc=libc bits=64 build=62a67eec83b28403
  # Output: 3.2.1
  shell: redis-server -v | awk '{print $3}' | sed -e 's/v=//'
  changed_when: False
  register: redis_installed_version

- name: Detect racket versions
  # Input: Welcome to Racket v6.6.
  # Output: 6.6
  shell: "racket -v | rev | cut -d ' ' -f1 | rev | sed 's/.$//' | sed 's/^v//'"
  register: racket_installed_version

Example: Download src only if version does not match:

- get_url:
    url: http://download.redis.io/releases/redis-{{redis_version}}.tar.gz
    dest: /usr/local/src/
    sha256sum: "{{redis_sha256}}"
  register: get_redis_result
  when: redis_installed_version.stdout | version_compare(redis_version, '!=')

Database diff

Tagged database, diff, postgres  Languages bash, ruby, yaml

This script will connect to two databases, named old and new, and print:

  • the names of the tables that cannot be found in the new database
  • the difference in the amount of rows per table, if there is a difference in the number of rows


# Postgres database diff script. Prints the names of missing tables and the
# difference in the amount of rows per table.
require 'sequel'
require 'pg'
require 'yaml'
require 'pry'

list_tables_sql = "SELECT tablename from pg_tables where schemaname = 'public';"
OLD_DB = Sequel.connect(YAML.load(File.read('old_database.yml')))
NEW_DB = Sequel.connect(YAML.load(File.read('new_database.yml')))
OLD_TABLES = OLD_DB[list_tables_sql].map{ |x| x.fetch(:tablename) }
NEW_TABLES = NEW_DB[list_tables_sql].map{ |x| x.fetch(:tablename) }

# Compare tables
def diff_tables

# Compare row count
def diff_row_count
  OLD_TABLES.sort.reduce({}) do |hash, table|
    sql = "SELECT count(*) FROM %{table}"
    # Sequel's count method does not work.
    diff = OLD_DB[sql % {table: table}].first[:count].to_i - NEW_DB[sql % {table: table}].first[:count].to_i
    hash[table] = diff if diff != 0

puts JSON.pretty_generate(tables: diff_tables, rows: diff_row_count)


source 'https://rubygems.org'

gem 'sequel'
gem 'pg'
gem 'pry'

new_database.yml and old_database.yml:

adapter: postgres
host: localhost
port: 5432
encoding: unicode
database: x_production
username: x
password: x

To run the script:

gem install bundler
bundle exec ruby db_diff.rb

Other migration and diff tools

migra is a schema diff tool for PostgreSQL. Use it to compare database schemas or autogenerate migration scripts.

Given two database connections, it output a file that represent the difference between schemas. It means that if you run the output file into the from database, it’ll have the same schema as the to database.

Compares the PostgreSQL schema between two databases and generates SQL statements that can be run manually against the second database to make their schemas match.

How to deploy an app on a specific node/server in a kubernetes cluster using labels

Tagged kubernetes, label, node  Languages yaml

This is an example of how to deploy an app on a specific node/server in a kubernetes cluster using labels:

# # Label one node in k8s cluster
# $ kubectl label nodes country=finland name=node1
# # Deploy 1 nginx pod in k8s cluster on node1 in Finland
# $ kubectl apply -f nginx.yml
# # View deployment
# $ kubectl describe deployment nginx-app
# # View pods
# $ kubectl get pods -l app=nginx
# # Find port the service is listening on
# $ kubectl describe service nginx-app | grep NodePort
# > NodePort:                 http   31796/TCP
# > NodePort:                 https  31797/TCP
# # Find the node the pod is deployed to
# $ kubectl describe pods nginx-app | grep Node
# > Node: 
# # Access deployment using node ip and port
# http://<node ip>:<node port>
# # Export service to YAML
# $ kubectl get service nginx-app -o yaml --export
# # Delete
# $ kubectl delete deployments,services -l country=finland
apiVersion: apps/v1
kind: Deployment
  name: nginx-app
    run: nginx-app
      app: nginx
  replicas: 1 # tells deployment to run 1 pods matching the template
        app: nginx
      - name: nginx
        image: nginx:1.13.12
        - containerPort: 80
        - containerPort: 443
        country: finland
        server: node1
# Expose deployment on <node ip>:<node port>. Node and a random port is assigned by k8s.
apiVersion: v1
kind: Service
    run: nginx-app
  name: nginx-app
  - name: http
    port: 80
    protocol: TCP
    targetPort: 80
  - name: https
    port: 443
    protocol: TCP
    targetPort: 443
    app: nginx
  type: NodePort