Rajaneeshs

  SELECT blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

Rajaneeshs

# See the 10 SELECT queries which touch the most number of rows.
# These queries may benefit from adding indexes to reduce the number
# of rows retrieved/affected.
SELECT
  rows, # rows is the number of rows retrieved/affected by the query
  query
FROM pg_stat_statements
WHERE query iLIKE '%SELECT%'
ORDER BY rows DESC
LIMIT 10;

Rajaneeshs

# See the 10 slowest queries with over a 1000 calls
SELECT query, calls, (total_time/calls)::integer AS avg_time_ms 
FROM pg_stat_statements
WHERE calls > 1000
ORDER BY avg_time_ms DESC
LIMIT 10;

#  query     |  calls  | avg_time_ms
#  ----------+---------+-------------
#  INSERT .. |   52323 |          12

Rajaneeshs

SELECT 
  (heap_blks_hit::decimal / (heap_blks_hit + heap_blks_read)) as cache_hit_ratio
FROM 
  pg_statio_user_tables
WHERE relname = 'large_table';

Rajaneeshs

WITH table_scans as (
    SELECT relid,
        tables.idx_scan + tables.seq_scan as all_scans,
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                pg_relation_size(relid) as table_size
        FROM pg_stat_user_tables as tables
),
all_writes as (
    SELECT sum(writes) as total_writes
    FROM table_scans

Rajaneeshs

How to generate a self-signed SAN SSL/TLS certificate using openssl

Generating a self-signed certificate is a common taks and the command to generate one with openssl is well known and well documented. Generating a certificate that includes subjectAltName is not so straght forward however. The following example demonstrates how to generate a SAN certificate without making a permanent change to the openssl configuration.

Generate a list of all required DNS names, (Note: CN will be discarded).

$ export SAN="DNS:www.domain.localdomain,DNS:domain.localdomain"

Rajaneeshs

tmux shortcuts & cheatsheet

start new:

tmux

start new with session name:

tmux new -s myname

Rajaneeshs

Create Root CA (Done once)

Create Root Key

Attention: this is the key used to sign the certificate requests, anyone holding this can sign certificates on your behalf. So keep it in a safe place!

openssl genrsa -des3 -out rootCA.key 4096

Rajaneeshs

#! /usr/bin/env python
# -*- coding: utf-8 -*-
"""This module's docstring summary line.

This is a multi-line docstring. Paragraphs are separated with blank lines.
Lines conform to 79-column limit.

Module and packages names should be short, lower_case_with_underscores.
Notice that this in not PEP8-cheatsheet.py

Rajaneeshs

import re
import boto3
import csv
from botocore.exceptions import ClientError

ec2 = boto3.client('ec2')

def get_snapshots():
    return ec2.describe_snapshots(OwnerIds=['self'])['Snapshots']