hipsterdb

PostgreSQL

Hackers DataBase

Like linux

we need Ubuntu

3 projects

• REST API for postgres
• Post-industrial UI
• HA Postgres Cluster

= hipsterdb

Cluster

hipsterdb new cluster --config cluster.edn
> master node running on X.X.X.X:8650

hipsterdb new node --join X.X.X.X:8650
> take base backup
> setup streaming replication
> replica node running on X.X.X.Y:8650

hipsterdb new node --join X.X.X.X:8650, X.X.X.Y:8650

curl localhost:8650/status
> master  | X.X.X.X:8650
> replica | X.X.X.Y:8650
> replica | X.X.X.Z:8650

kill node-1

curl localhost:8650/status
> master  | X.X.X.Y:8650
> replica | X.X.X.Z:8650
> lost    | X.X.X.X:8650
          

Project

• repmgr
• zalando/patroni
• pgpool-II
• handmade

No automatic failover

Cluster Agent

• connected to cluster
• supervises pg
• manages configs
• monitoring & alerting
• REST API to cluster
• cloud aware/frendly

80/20

Cluster consensus

Paxos, Raft algorythms

Zookeeper,Consul & ETCD

Distributed DBs

• Zookeeper
• Consul
• ETCD

atomix.io

An open source software stack for distributed systems infrastructure

• Distributed variables
• Distributed collections such as maps, multi-maps, sets, and queues
• Distributed groups tools such as group membership, leader election, messaging and more
• Distributed concurrency tools such as locks

atomix.io

AtomixReplica replica = AtomixReplica
  .builder(new Address("localhost", 8700))
  .withStorage(storage)
  .withTransport(transport)
  .build();

replica.join(new Address("localhost", 8700)).join();

DistributedLock lock = replica.getLock("my-lock").join();
lock.lock().thenRun(() -> System.out.println("Acquired a lock!"));

          

failover (Master Alive)

MASTER:
  if it's_me?
    renew master_dvar, ttl; goto 0
  else
    if history_spawn?
      stop pg; exit cluster; notify
    else
      rewind to new master
REPLICA:
  if my_master?
    goto 0
  else
    rewind to new master
NEWBIE:
  follow master
          

failover (Mater Dead)

MASTER:
  if got_lock?
    set master_dvar to me; release_lock; goto 0
  else
    goto 0
REPLICA:
  if old_master_alive?
    notify inconsistency or kill it; goto 0
  else
    if i_am_latest?
      if got_lock?
        promote; set master_dvar to me; release lock
      else
        goto 0
    else
      goto 0
NEW: goto 0
          

REST API & Discovery

• Cluster status
• Master discovery
• Failover watch
• Management

Configuration management

• Good & live defaults
• Recomendations
• Versioning & rollback

REST API for postgresql

Nobackend (Generic backend)
for data driven app

• PostgREST
• Postgraphql
• OpenResty (nginx)
• C2H50H

Schema is your API (PostgREST)

hipsterdb new cluster --config cluster.edn

CREATE TABLE people (.....);

GET /people?age=gte.18&student=is.true

GET /clients?id=eq.42&select=id, name, projects{*}

POST /people
[
  { "name": "J Doe", "age": 62, "height": 70 },
  { "name": "Janus", "age": 10, "height": 55 }
] 
          

Swagger & JSON schema

swagger: "2.0"
host: "petstore.swagger.io"
basePath: "/api"
paths:
  /pets:
    get:
      description: "Returns all pets from the system that the user has access to"
      produces:
        - "application/json"
      responses:
        "200":
          description: "A list of pets."
          schema:
            type: "array"
            items:
              $ref: "#/definitions/Pet"
definitions:
  Pet:
    type: "object"
    required:
      - "id"
      - "name"
          

Demo Swagger UI

GraphQL

type Human {
  id: String
  name: String
  appearsIn: [Episode]
  homePlanet: String
}

query NestedQuery {
  hero {
    name
    friends {
        name
        appearsIn
        friends {  name }
    }
  }
}
  

Postgraphql

create table post (
  id serial primary key,
  author_id int non null references user(id),
  headline text,
  body text,
  …
);

{
  postNodes {
    nodes {
      headline
      body
      author: userByAuthorId { name}
    }
  }
}

  

Data driven queries

{:select [:f.* :b.baz :c.quux [:b.bla "bla-bla"]
             (sql/call :now) (sql/raw "@x := 10")]
:modifiers [:distinct]
:from [[:foo :f] [:baz :b]]
:join [:draq [:= :f.b :draq.x]]
:where [:or [:in :f.e [1 (sql/param :param2) 3]]
            [:between :f.e 10 20]]
:group-by [:f.a]
:having [:< 0 :f.e]
:order-by [[:b.baz :desc] :c.quux [:f.a :nulls-first]
:limit 50
:offset 10}
          

HoneySQL

Security

• Reuse postgresql security
• SET ROLE in connection
• OAUTH 2.0
• JWT
• Row Security Policies!!!

Row Security Policies

CREATE TABLE accounts
  (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);
          

Post-industrial UI

• Web
• Educatable
• Hackable
• Vizualizable

Educatable

• embeded docs
• interactive docs
• education mode

Visualize

from schema to data

• d3 world integration

Hackable

• visualizers
• advisers/analyzers
• monitorings
• plv8 integration

Demo

$> hipsterdb start

Thank you