Start a terminal running an initial command

Michael Shepanski
September 13, 2015
bash --init-file <(echo "source /home/mjs7231/.bashrc; workon pk; /usr/bin/subl")
bash --init-file <(echo "source /home/mjs7231/.bashrc; workon pk; grunt watch")
bash --init-file <(echo "source /home/mjs7231/.bashrc; workon pk; django-admin runserver")

View Postgres Current Transactions

Michael Shepanski
August 30, 2015
SELECT datid, xact_start, 
    date_trunc('second', current_timestamp - xact_start) AS xact_runtime, 
    age(date_trunc('second', current_timestamp), date_trunc('second', state_change)),
    state, query 
  FROM pg_stat_activity 
  ORDER BY xact_start;

View Postgres Vacuum Health

Michael Shepanski
August 30, 2015
  dead > av_threshold AS av_needed,
  CASE WHEN reltuples > 0 THEN round(100.0 * dead / reltuples) ELSE 0 END AS pct_dead,
  CASE WHEN av_threshold > 0 THEN round(100.0 * dead / av_threshold) ELSE 0 END AS pct_threshold
      n.nspname || '.' || c.relname AS tablename,
      pg_stat_get_tuples_inserted(c.oid) AS inserted,
      pg_stat_get_tuples_updated(c.oid) AS updated,
      pg_stat_get_tuples_deleted(c.oid) AS deleted,
      pg_stat_get_live_tuples(c.oid) AS live,
      pg_stat_get_dead_tuples(c.oid) AS dead,
      round(current_setting('autovacuum_vacuum_threshold')::INTEGER +
        current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * c.reltuples)::INTEGER AS av_threshold,
      c.reltuples::INTEGER AS reltuples,
      age(date_trunc('second', current_timestamp), date_trunc('second', greatest(ut.last_vacuum, last_autovacuum))) AS last_vacuum,
      ut.vacuum_count + ut.autovacuum_count as vacuums,
      age(date_trunc('second', current_timestamp), date_trunc('second', greatest(last_analyze, last_autoanalyze))) AS last_analyze,
      ut.analyze_count + ut.autoanalyze_count as analyzes
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_stat_user_tables ut ON ut.relname = c.relname
    WHERE C.relkind IN ('r', 't')
      AND n.nspname NOT IN ('pg_catalog', 'information_schema')
      AND n.nspname !~ '^pg_toast'
  ) AS av
  ORDER BY pct_threshold DESC;

View Postgres Indexes and Usage

Michael Shepanski
August 30, 2015
SELECT i.schemaname AS schema,
    i.relname AS tablename,
    i.indexrelname AS indexname,
    i.idx_scan AS scans,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS indexsize,
    SUBSTRING(k.indexdef FROM '\((.*?)\)') AS indexcolumns
  FROM pg_stat_user_indexes i
  JOIN pg_index j ON i.indexrelid = j.indexrelid
  JOIN pg_indexes k ON k.indexname = i.indexrelname
  WHERE j.indisunique IS FALSE
    AND i.relname = 'api_metrics_metric'
    pg_relation_size(i.indexrelid) DESC;

Dynamic DNS Using DigitalOcean's API

Michael Shepanski
August 1, 2013
Dns, Vps

Another host another dynamic DNS Script to update the IP to point to your home address. The steps are fairly easy:

  1. Create a new A record on the domain of your choice. I named mine "home".
  2. Update the script below with your DigitalOcean CLIENTID, APIKEY, DOMAIN, and RECORD.
  3. Setup a cronjob to run the script once an hour or so.