Temporal tables in PostgreSQL

Tagged postgres, slowly changing, temporal  Languages sql

To implement temporal tables in PostgreSQL, see Magnus Hagander’s “A TARDIS for your ORM” presentation: https://www.hagander.net/talks/tardis_orm.pdf

Temporal tables allow you to travel back in time and see your data as it was right after the big bang or, if needed, right before your data arrived at the event horizon.

The following code is from https://gist.github.com/iperdomo/74890685f3c76b0fc139 and implements the gist of time travel:

-- psql -U postgres -h localhost -f /path/to/tardis.sql

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

CREATE DATABASE tardis WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

\connect tardis

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;


CREATE SCHEMA history;

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;

SET search_path = history, pg_catalog;

CREATE FUNCTION log_change() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
    DECLARE
      c refcursor;
      tt tstzrange;
    BEGIN
        IF TG_OP = 'INSERT' THEN
            EXECUTE 'INSERT INTO history.' || TG_TABLE_NAME ||
              ' SELECT $1.*, tstzrange(now(), $$infinity$$, $$[)$$)' USING NEW;
            RETURN NEW;
        ELSIF TG_OP = 'UPDATE' THEN
            OPEN c FOR EXECUTE 'SELECT _validrange FROM history.' || TG_TABLE_NAME ||
              ' WHERE id = $1 ORDER BY _validrange DESC LIMIT 1 FOR UPDATE'
              USING NEW.id;
            FETCH FROM c INTO tt;

            IF isempty(tstzrange(lower(tt), now(), $$[)$$)) THEN

                -- What edge case are we missing by commenting the following lines of code?
        -- How can we get the lastxid value?

        --IF NOT lastxid = txid_current() THEN
        --    RAISE EXCEPTION 'UPDATE would have empty validity: %d!', OLD;
        --END IF;

        EXECUTE 'DELETE FROM history.' || TG_TABLE_NAME ||
          ' WHERE CURRENT OF ' || quote_ident(c::text);
            ELSE
                EXECUTE 'UPDATE history.' || TG_TABLE_NAME || ' SET _validrange = tstzrange($1, now(), $$[)$$)' ||
                  ' WHERE CURRENT OF ' || quote_ident(c::text) USING lower(tt);
            END IF;

            EXECUTE 'INSERT INTO history.' || TG_TABLE_NAME ||
              ' SELECT $1.*, tstzrange(now(), $$infinity$$, $$[)$$)' USING NEW;

            RETURN NEW;
        ELSIF TG_OP = 'DELETE' THEN

          OPEN c FOR EXECUTE 'SELECT _validrange FROM history.' || TG_TABLE_NAME ||
            ' WHERE id = $1 ORDER BY _validrange DESC LIMIT 1 FOR UPDATE' USING OLD.id;

          FETCH FROM c into tt;

          IF isempty(tstzrange(lower(tt), now(), $$[)$$)) THEN
            EXECUTE 'DELETE FROM history.' || TG_TABLE_NAME ||
              ' WHERE CURRENT OF ' || quote_ident(c::text);
            RETURN OLD;
          END IF;

          EXECUTE 'UPDATE history.' || TG_TABLE_NAME ||
            ' SET _validrange = tstzrange($1, now(), $$[)$$) WHERE CURRENT OF ' ||
            quote_ident(c::text) USING lower(tt);

          RETURN OLD;
        END IF;
        RETURN NULL;
    END;
$_$;


CREATE TABLE table1 (
    id text NOT NULL,
    name text NOT NULL,
    _validrange tstzrange
);

ALTER TABLE ONLY table1
    ADD CONSTRAINT table1_exclusion EXCLUDE
    USING gist (id WITH =, _validrange WITH &&);


SET search_path = public, pg_catalog;

-- We're using text instead of uuid because btree_gist don't support
-- uuid data type yet and we want to use it for the EXCLUDE constraint
-- See: https://commitfest.postgresql.org/7/332/

CREATE TABLE table1 (
    id text PRIMARY KEY DEFAULT (uuid_generate_v4())::text NOT NULL,
    name text NOT NULL
);

CREATE TRIGGER table1_history BEFORE
  INSERT OR DELETE OR UPDATE ON table1
  FOR EACH ROW EXECUTE PROCEDURE history.log_change();


-- Data

BEGIN;
INSERT INTO table1 (name) VALUES ('name 1');
INSERT INTO table1 (name) VALUES ('name 2');
COMMIT;

BEGIN;
UPDATE table1 SET name = 'name 1.1' WHERE name = 'name 1';
COMMIT;

BEGIN;
-- Since this is a transaction, the first UPDATE is not recorded
-- in the history.table1 (as expected)
UPDATE table1 SET name = 'name 1.2' WHERE name = 'name 1.1';
UPDATE table1 SET name = 'name 1.3' WHERE name = 'name 1.2';
COMMIT;

SELECT * FROM public.table1 WHERE name = 'name 1.3';
SELECT * FROM history.table1 ORDER BY _validrange DESC;

My Mac won't go to sleep

Tagged mac, pmset, sleep  Languages bash

If your Mac won’t go to sleep, try this:

pmset -g assertions

Check the output for details on which processes and voodo magic is keeping your iMac up all night:

Listed by owning process:
   pid 4627(cloudd)...
Idle sleep preventers: IODisplayWrangler

Things you can try:

  • Resetting the printing system (CTRL+click in the left pane of the Printers and scanners settings)
  • Unplugging external devices (USB, bluetooth, etc)
  • Voodoo

References:

https://discussions.apple.com/thread/7256162

https://www.reddit.com/r/applehelp/comments/43ks7u/macbook_wont_go_to_sleep_hidd_preventing_sleep/

https://apple.stackexchange.com/questions/307151/significance-of-iodisplaywrangler-in-power-management-assertions

How to set up a Digital Ocean Kubernetes cluster

Tagged digitalocean, kubernetes, setup  Languages bash, yaml

How to set up a Digital Ocean Kubernetes cluster

Introduction

Overview of how a request gets from a browser to a Docker container managed by Kubernetes:

Internet[Browser => DigitalOcean LoadBalancer] => Kubernetes[Ingress => Service => Deployment => Pod] => Docker[Container]

Prerequisites

  • Install kubectl
$ brew install kubectl
  1. Create cluster in DigitalOcean dashboard

Download the cluster configuration (YAML file) and put it in the ~/.kube folder.

  1. Set KUBECONFIG environment variable
$ export KUBECONFIG=/Users/christian/.kube/staging-kubeconfig.yaml
$ kubectl config current-context
$ kubectl get nodes
NAME               STATUS   ROLES    AGE   VERSION
xxx-staging-qv72   Ready    <none>   70m   v1.14.1
xxx-staging-qv7p   Ready    <none>   71m   v1.14.1
xxx-staging-qv7s   Ready    <none>   71m   v1.14.1
  1. Install ingress controller
$ kubectl apply -f https://raw.githubusercontent.com/kubernetes/ingress-nginx/master/deploy/mandatory.yaml
$ kubectl apply -f https://raw.githubusercontent.com/kubernetes/ingress-nginx/master/deploy/provider/cloud-generic.yaml

Get the EXTERNAL-IP of the load balancer:

$ kubectl get services --namespace=ingress-nginx
  1. Create ingress resource

ingress.yaml:

apiVersion: extensions/v1beta1
kind: Ingress
metadata:
  name: app-ingress
  annotations:
    ingress.kubernetes.io/rewrite-target: /
spec:
  rules:
  - http:
      paths:
        - path: /
          backend:
            serviceName: app-service
            servicePort: 80
$ kubectl apply -f ingress.yml
  1. Create deployment

deployment.yaml:

apiVersion: v1
kind: Service
metadata:
  name: app-service
spec:
  ports:
  - port: 80
    targetPort: 5678
  selector:
    app: app
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: app
spec:
  selector:
    matchLabels:
      app: app
  replicas: 1
  template:
    metadata:
      labels:
        app: app
    spec:
      containers:
      - name: app
        image: hashicorp/http-echo
        args:
        - "-text=Hello. This is your Digital Ocean k8s cluster."
        ports:
        - containerPort: 5678
$ kubectl apply -f deployment.yml
  1. Curl your app
curl https://<EXTERNAL-IP> --insecure

References

How to debug CrashLoopBackOff

Tagged crashloopbackoff, kubectl, kubernetes  Languages bash, yaml

One day, you see CrashLoopBackOff in the kubectl output:

$ kubectl get pod
NAME                               READY   STATUS             RESTARTS   AGE
app-548c9ddc46-z2fng               0/1     CrashLoopBackOff   79         6h26m

You already know that executing bash in the container is not possible because the container has crashed:

$ kubectl exec -ti app-548c9ddc46-z2fng bash
error: unable to upgrade connection: container not found ("app")

Option 1: Analyze the container logs

You can view the container’s logs with kubectl logs:

$ kubectl logs -f app-548c9ddc46-z2fng

Option 2: Modify Dockerfile’s CMD

Modifying the Dockerfile’s ‘CMD’ is not needed, as it can be done indirectly through the Pod’s YAML configuration file:

apiVersion: v1
kind: Pod
metadata:
  name: app
spec:
  containers:
    - name: app
      image: company/app
      # DEBUG env variables
      command: [ "/bin/sh", "-c", "env" ]

The modified command will print the environment variables to the logs:

command: [ "/bin/sh", "-c", "env" ]

To view the output run:

$ kubectl logs -f app-548c9ddc46-z2fng

Option 3: Start a shell and sleep (CMD)

Sleeping usually helps, and this can be done by modifying the container’s command:

apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: xxx-service
spec:
  replicas: 1
  template:
    ...
    spec:
      containers:
      - image: yyy/xxx:1.0.0
        name: xxx-service
        ...
        command:
          - "sh"
          - "-c"
          - "sleep 10000"

The container will start and run “sleep 10000” in a shell, giving you exactly 10000 seconds to debug the issue by connecting to the “sleeping” container:

$ kubectl exec -ti app-548c9ddc46-z2fng bash

How to configure Kubernetes to pull images from a private Docker registry

Tagged docker, kubernetes, private, registry  Languages bash, yml

How to configure Kubernetes to pull images from a private Docker registry:

  • First configure Docker by following the steps outlined here:

https://snippets.aktagon.com/snippets/869-configure-docker-to-use-a-private-container-registry-using-a-self-signed-certificate

  • Verify that the Docker configuration contains the authentication information
sudo cat ~/.docker/config.json
{
    "auths": {
        "<registry-server>": {
            "auth": "<hash>"
        }
    },
    "HttpHeaders": {
        "User-Agent": "Docker-Client/18.09.4 (linux)"
    }
}
  • Base64 encode the config.json file
cat ~/.docker/config.json | base64 -w0 > config.base64.json
  • Create secret.yml and add the contents of config.base64.json to dockerconfigjson
apiVersion: v1
kind: Secret
metadata:
 name: registrypullsecret
data:
 .dockerconfigjson: <config.base64.json>
type: kubernetes.io/dockerconfigjson
  • Import the secret into Kubernetes
kubectl create -f secret.yml && kubectl get secrets
  • Test that the secret was imported into Kubernetes
kubectl get secrets

Configure Docker to use a private container registry using a self-signed certificate

Tagged docker, registry, self-signed  Languages bash

How to configure Docker to use a private container registry using a self-signed certificate.

Tested on Docker version 18.09.4, build d14af54.

  • Copy self-signed certificate from the registry server to the docker server

On your laptop:

$ scp christian@registry-server://etc/ssl/certs/selfsigned.crt christian@docker-server://etc/ssl/certs/private-docker-registry.crt
  • Restart docker daemon

On the docker server:

$ sudo service docker restart
  • Login to the registry from the docker server

On the docker server:

sudo docker login -u christian registry-server
> WARNING! Your password will be stored unencrypted in /home/christian/.docker/config.json.

How to connect to a Kubernetes cluster hosted on DigitalOcean

Tagged digitalocean, kubernetes  Languages bash

Tested April, 2019.

  1. Download the Kubernetes configuration from the DigitalOcean dashboard

  2. Move the Kubernetes configuration file to ~/.kube folder
$ mkdir $HOME/.kube/projectx/
$ mv ~/Downloads/projectx-staging-kubeconfig.yaml $HOME/.kube/projectx/staging-kubeconfig.yaml
  1. List all contexts in the KUBECONFIG variable
$ export KUBECONFIG=$HOME/.kube/projectx/staging-kubeconfig.yaml:$HOME/.kube/projectx/production-kubeconfig.yaml
  1. Install kubectl
$ brew install kubectl
  1. List context and nodes
$ kubectl config get-contexts
$ kubectl get nodes
$ kubectl config use-context

How to set the session's time zone to UTC when using Golang's database/sql

Tagged database, golang, session, sql, timestamp  Languages go

Always remember to set the time zone session setting when using database/sql and timestamps, otherwise timestamps will use the database default:

db, err = sql.Open("postgres", "timezone=UTC")
if err != nil {
    log.Fatal(err)
}
defer db.Close()
var timezone string
err = db.QueryRow("SHOW timezone").Scan(&timezone)
if err != nil {
    log.Fatal(err)
}
log.Println("timezone:", timezone)

Running SET TIME ZONE ‘Africa/Casablanca’; once in a connection won’t work because a pool of connections is used.

How to check if an IP address is private (IPv6, IPv4, cidr)

Tagged ip, private, validate, cidr, ipv4, ipv6  Languages ruby
require 'resolv'
require 'ipaddr'

class PrivateIP
  # https://en.wikipedia.org/wiki/Private_network#Private_IPv4_address_spaces
  IPV4_NETWORKS = %w[0.0.0.0/8
  10.0.0.0/8
  100.64.0.0/10
  127.0.0.0/8
  169.254.0.0/16
  172.16.0.0/12
  192.0.0.0/24
  192.0.0.0/29
  192.0.0.8/32
  192.0.0.9/32
  192.0.0.170/32
  192.0.0.171/32
  192.0.2.0/24
  192.31.196.0/24
  192.52.193.0/24
  192.88.99.0/24
  192.168.0.0/16
  192.175.48.0/24
  198.18.0.0/15
  198.51.100.0/24
  203.0.113.0/24
  240.0.0.0/4
  255.255.255.255/32
  224.0.0.0/24
  239.0.0.0/8].map { |cidr| IPAddr.new(cidr) }

  # https://en.wikipedia.org/wiki/Unique_local_address
  IPV6_NETWORKS = %w[fd00::/8
  fc00::/8
  0000:0000:0000:0000:0000:0000:0000:0000/64].map { |cidr| IPAddr.new(cidr) }

  class InvalidHost < ArgumentError; end

  # Examples:
  # private?('127.0.0.1') => true
  # private?('localhost') => true
  # private?('google.com') => false
  def self.private?(ip_or_host)
    address = IPAddr.new(ip_or_host)
    if address.ipv4?
      IPV4_NETWORKS.any? { |cidr| cidr.include?(ip_or_host) }
    elsif address.ipv6?
      IPV6_NETWORKS.any? { |cidr| cidr.include?(ip_or_host) }
    else
      false
    end
  rescue IPAddr::InvalidAddressError
    private_host?(ip_or_host)
  end

  # Example: private_host?('localhost') => true
  def self.private_host?(host)
    host_ips(host).any? do |type, ips|
      ips.any? { |ip| private?(ip) }
    end
  end

  # Example: host_ips('localhost') => {ipv4: ['127.0.0.1'], ipv6: []}
  def self.host_ips(host)
    ipv4 = Resolv::DNS.new.getresources(host, Resolv::DNS::Resource::IN::A)
    ipv6 = Resolv::DNS.new.getresources(host, Resolv::DNS::Resource::IN::AAAA)
    raise InvalidHost, "unknown host: #{host}" if ipv4.empty? && ipv6.empty?
    { ipv4: ipv4.map { |r| r.address.to_s },
      ipv6: ipv6.map { |r| r.address.to_s } }
  end
end
require 'test_helper'
require 'private_ip'

class PrivateIPTest < ActiveSupport::TestCase
  test "private?" do
    assert PrivateIP.private?('localhost')
    assert PrivateIP.private?('127.0.0.1')
    assert PrivateIP.private?('0.0.0.0')
    refute PrivateIP.private?('google.com')
    refute PrivateIP.private?('209.216.230.240')
  end

  test "private? (IPV6)" do
    assert PrivateIP.private?('fd7b:5886:20a0:11a0:1111:2222:3333:4444')
    assert PrivateIP.private?('::1') # Try http://[::1]:3000 in browser
    assert PrivateIP.private?('::')
    assert PrivateIP.private?('0:0:0:0:0:0:0:1')
  end

  test "private? (CIDR)" do
    PrivateIP::IPV4_NETWORKS.each do |cidr|
      assert PrivateIP.private?(cidr.to_s)
    end
    PrivateIP::IPV6_NETWORKS.each do |cidr|
      assert PrivateIP.private?(cidr.to_s)
    end
  end

  test "private_host?" do
    assert PrivateIP.private_host?('localhost')
    refute PrivateIP.private_host?('google.com')
  end

  test "host_ips" do
    assert_equal({ ipv4: ["127.0.0.1"], ipv6: [] }, PrivateIP.host_ips('localhost'))
    assert_equal({ ipv4: ["209.216.230.240"], ipv6: []}, PrivateIP.host_ips('news.ycombinator.com'))
  end

  test "host_ips (invalid)" do
    assert_raise PrivateIP::InvalidHost do
      PrivateIP.host_ips('https://google.com')
    end
    assert_raise PrivateIP::InvalidHost do
      PrivateIP.host_ips('127.0.0.1')
    end
    assert_raise PrivateIP::InvalidHost do
      PrivateIP.host_ips('::1')
    end
    assert_raise PrivateIP::InvalidHost do
      PrivateIP.host_ips('[::1]')
    end
    assert_raise PrivateIP::InvalidHost do
      PrivateIP.host_ips('::')
    end
  end
end