psql config

.areadme.md

psqlrc:

• some infos at initial connect
• predefined queries in ~/.psqlrc-include/ compiled by the _update.sh script into ~/.psqlrc-commands.sql which will be included within .psqlrc

typical worksetup:

• portforwarding from remote to local host via ssh user@host -L 5432:127.0.0.1:5432
• watching a temp file with entr ls ~/t.sql | entr -c psql -U docker -h localhost -f ~/t.sql
• editing the file, executed at save with vim ~/t.sql
• usually everything wrapped within a transaction

typical t.sql content:

-- disable pspg
\setenv PAGER cat 

begin;

  select true;

rollback;

Tools:

• ssh
• entr
• pspg ( https://github.com/okbob/pspg )

.psqlrc

\set QUIET 1

\i ~/.psqlrc-commands.sql

\timing off

\pset null '¤'
\x auto
\t
\pset border 0
\pset footer 0
\pset format unaligned
\! echo

\! test -x /usr/local/bin/lolcat && test -x /usr/bin/figlet && echo fck\\nafd | figlet | lolcat -t

SELECT ' 🐘 Time:         ' || now()::timestamp(0)::text;
SELECT ' 🐘 Version:      ' || version();
SELECT coalesce(
       ' 🐘 Server:       ' || inet_server_addr()::text || ':' || inet_server_port()::text,
       ' 🐘 Socket:       ' || current_setting('unix_socket_directories')
);
SELECT ' 🐘 Client:       ' || coalesce(inet_client_addr()::text || ':' || inet_client_port()::text, 'unix socket');
SELECT ' 🐘 Backend:      ' || pg_backend_pid();
SELECT ' 🐘 Uptime:       ' || date_trunc('second', current_timestamp - pg_postmaster_start_time())::text as uptime;
SELECT ' 🐘 Searchpath:   ' || reset_val FROM pg_settings WHERE name='search_path';
SELECT ' 🐘 Connections:  ' || (select count(*) from pg_stat_activity where state is not null) || ' / ' || current_setting('max_connections')::int;
SELECT ' 🐘 dead Tuples:  ' || (select sum(n_dead_tup) from pg_stat_all_tables);
\! echo

\pset format aligned
\t
\timing on
\pset footer on
\pset border 2
\pset linestyle unicode
\pset unicode_header_linestyle double
\encoding unicode

\set PROMPT1 '%[%33[33m%] 🐘 %`date +"%H:%M:%S"` %[%33[34;0m%]» %[%33[31;1m%]%n%[%33[33;1m%]@%[%33[36;1m%]%M:%>/%/ %[%33[0m%]%[%33[35m%]%x%# %[%33[0m%]'
\set PROMPT2 '%[%33[33m%] 🐘 %l %R %[%33[35m%]» %[%33[0m%]'

\unset quiet

\x off
\setenv PAGER 'pspg -i -s 17 --less-status-bar --no-mouse'

_update.sh

#!/bin/zsh
#
# need to write this up. It's quite awesome. Actual commands (well,
# variable definitions) are in libakg/dot/psqlrc-commands.d, symlink
# at ~/.psqlrc-commands.d is what you'd expect, using the home-dotdir
# to be friendlier to anyone who wants to copy this in (which they
# should) (because it's awesome)
#
# this part avoids doing the ridiculous stuff in build_psqlrc_commands
# when nothing's changed, mostly noticable when my system is waaay
# overloaded... so a lot.
set -e


update_psqlrc_commands()
{
    if [ -n "$(find ~/.psqlrc-include/ -maxdepth 1 -name '*.sql' -newer $HOME/.psqlrc-commands.sql -print -quit)" ];
    then
        build_psqlrc_commands >| $HOME/.psqlrc-commands.sql
    fi
}

build_psqlrc_commands()
{
    for fname in ~/.psqlrc-include/*.sql; do
        cmdname=$(basename $fname|sed -e 's/.sql$//');
        sql=$(cat $fname| sed -z -r -e 's/\n/\\n/g' -e "s/([^\\'])([']+)/\1\2\2/g" -e "s/^/'/" -e "s/(\\n|\\\n)*$/'/")
        rc_txt=$(echo -E '\set' "$cmdname" "$sql")
        echo -E "$rc_txt"
        echo
    done
}

update_psqlrc_commands

shorthands.sql

-- ==> activity.sql <==
WITH _0 AS
  (SELECT datname,
          pid,
          (clock_timestamp() - backend_start) AS bk_age,
          (clock_timestamp() - state_change) state_age,
          (clock_timestamp() - xact_start) xact_age,
          (clock_timestamp() - query_start) query_age,
          usename,
          application_name app,
          client_addr,
          STATE,
          wait_event,
          wait_event_type,
          regexp_replace(query, '[\n ]+', ' ', 'g') query1
   FROM pg_stat_activity order by xact_start desc nulls last, query_start desc)
SELECT *
FROM _0

-- ==> bloat.sql <==
SELECT tablename AS TABLE_NAME,
       ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS table_bloat,
       CASE
           WHEN relpages < otta THEN '0'
           ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint)
       END AS table_waste,
       iname AS index_name,
       ROUND(CASE WHEN iotta=0
             OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS index_bloat,
       CASE
           WHEN ipages < iotta THEN '0'
           ELSE pg_size_pretty((bs*(ipages-iotta))::bigint)
       END AS index_waste
FROM
  (SELECT schemaname,
          tablename,
          cc.reltuples,
          cc.relpages,
          bs,
          CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
          COALESCE(c2.relname,'?') AS iname,
          COALESCE(c2.reltuples,0) AS ituples,
          COALESCE(c2.relpages,0) AS ipages,
          COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta
   FROM
     (SELECT ma,
             bs,
             schemaname,
             tablename,
             (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
             (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
      FROM
        (SELECT schemaname,
                tablename,
                hdr,
                ma,
                bs,
                SUM((1-null_frac)*avg_width) AS datawidth,
                MAX(null_frac) AS maxfracsum,
                hdr+
           (SELECT 1+count(*)/8
            FROM pg_stats s2
            WHERE null_frac<>0
              AND s2.schemaname = s.schemaname
              AND s2.tablename = s.tablename) AS nullhdr
         FROM pg_stats s,

           (SELECT
              (SELECT current_setting('block_size')::numeric) AS bs,
                   CASE WHEN substring(v,12,3) IN ('8.0',
                                                   '8.1',
                                                   '8.2') THEN 27 ELSE 23 END AS hdr,
                                                                                 CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
            FROM
              (SELECT version() AS v) AS foo) AS constants
         GROUP BY 1,
                  2,
                  3,
                  4,
                  5) AS foo) AS rs
   JOIN pg_class cc ON cc.relname = rs.tablename
   JOIN pg_namespace nn ON cc.relnamespace = nn.oid
   AND nn.nspname = rs.schemaname
   AND nn.nspname <> 'information_schema'
   LEFT JOIN pg_index i ON indrelid = cc.oid
   LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) AS sml
ORDER BY CASE
             WHEN relpages < otta THEN 0
             ELSE bs*(sml.relpages-otta)::bigint
         END DESC


-- ==> blocking.sql <==
SELECT bl.pid AS blocked_pid,
       ka.query AS blocking_statement,
       now() - ka.query_start AS blocking_duration,
       kl.pid AS blocking_pid,
       a.query AS blocked_statement,
       now() - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.pid
JOIN pg_catalog.pg_locks kl
JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.pid ON bl.transactionid = kl.transactionid
AND bl.pid != kl.pid
WHERE NOT bl.granted

-- ==> cache_hits.sql <==
SELECT 'index hit rate' AS name,
       (sum(idx_blks_hit)) / sum(idx_blks_hit + idx_blks_read) AS ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT 'cache hit rate' AS name,
       sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables

-- ==> connections.sql <==
SELECT usename,
       count(*)
FROM pg_stat_activity
GROUP BY usename

-- ==> dbsize.sql <==
SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size

-- ==> index_sizes.sql <==
SELECT relname AS name,
       pg_size_pretty(sum(relpages::bigint*1024*8)) AS SIZE
FROM pg_class
WHERE reltype=0
GROUP BY relname
ORDER BY sum(relpages) DESC

-- ==> locks.sql <==
SELECT pg_stat_activity.pid,
       pg_class.relname,
       pg_locks.transactionid,
       pg_locks.granted,
       substring(pg_stat_activity.query
                 FROM '([^

]*

?){1,3}') AS query_snippet,
       age(now(),pg_stat_activity.query_start) AS "age"
FROM pg_stat_activity,
     pg_locks
LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE pg_stat_activity.query <> '<insufficient privilege>'
  AND pg_locks.pid=pg_stat_activity.pid
  AND pg_locks.mode = 'ExclusiveLock'
ORDER BY query_start

-- ==> missing_index.sql <==
SELECT relname,
       seq_scan-idx_scan AS too_much_seq,
       CASE
           WHEN seq_scan-idx_scan > 0 THEN 'Missing Index?'
           ELSE 'OK'
       END,
       pg_relation_size(relname::regclass) AS rel_size,
       seq_scan,
       idx_scan
FROM pg_stat_all_tables
WHERE schemaname='public'
  AND pg_relation_size(relname::regclass) > 80000
ORDER BY too_much_seq DESC

-- ==> namespace_size.sql <==
SELECT * from (SELECT
    nspname,
    pg_size_pretty(sum(pg_table_size(pg_class.oid))) "Schema Size",
    pg_size_pretty(sum(pg_indexes_size(pg_class.oid))) "Indexes",
    count(pg_class.oid) "Tables"
FROM pg_class
JOIN pg_namespace ON (pg_class.relnamespace=pg_namespace.oid)
WHERE relkind = 'r' or relkind = 'm'
GROUP BY nspname
ORDER BY sum(pg_total_relation_size(pg_class.oid)) DESC) _



-- ==> table_index_usage.sql <==
SELECT relname,
       CASE idx_scan
           WHEN 0 THEN 'Insufficient data'
           ELSE (100 * idx_scan / (seq_scan + idx_scan))::text
       END percent_of_times_index_used,
       n_live_tup rows_in_table
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC

-- ==> tablespaces.sql <==
SELECT
    coalesce(spcname, '[Default]') "Tablespace",
    pg_size_pretty(sum(pg_relation_size(c.oid)) FILTER (WHERE relkind = 'r' or relkind = 'm')) "Data Size",
    pg_size_pretty(sum(pg_relation_size(c.oid)) FILTER (WHERE relkind = 'i'                 )) "Index Size",
    count(c.oid) "# Tables"
FROM pg_class c
LEFT JOIN pg_tablespace spc ON (c.reltablespace=spc.oid)
WHERE relkind = 'r' or relkind = 'm' or relkind = 'i'
GROUP BY 1
/*ORDER BY sum(pg_total_relation_size(c.oid)) DESC;*/
ORDER BY 1


-- ==> trash_index.sql <==
( select s.schemaname as sch, s.relname as rel, s.indexrelname as idx, s.idx_scan as scans, pg_size_pretty(pg_relation_size(s.relid)) as ts, pg_size_pretty(pg_relation_size(s.indexrelid)) as "is" from pg_stat_user_indexes s join pg_index i on i.indexrelid=s.indexrelid left join pg_constraint c on i.indrelid=c.conrelid and array_to_string(i.indkey, ' ') = array_to_string(c.conkey, ' ') where i.indisunique is false and pg_relation_size(s.relid) > 1000000 and s.idx_scan < 100000 and c.confrelid is null order by s.idx_scan asc, pg_relation_size(s.relid) desc )

-- ==> ts.sql <==
SELECT
    c.oid,
    relkind,
    nspname AS schema,
    relname AS table,
    c.reltuples::bigint AS row_estiemate ,
    pg_size_pretty(pg_total_relation_size(reltoastrelid)) AS toast_size ,
    pg_size_pretty(pg_indexes_size(c.oid)) AS index_size ,
    pg_size_pretty(pg_table_size(c.oid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
LEFT JOIN pg_namespace n
ON n.oid = c.relnamespace
WHERE
      relkind not in ('c','v','S','i')
  and n.nspname not in ('pg_catalog','pg_toast','information_schema')
order by pg_table_size(c.oid)
;

-- ==> unused_index.sql <==
SELECT schemaname || '.' || relname AS TABLE,
       indexrelname AS INDEX,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
       idx_scan AS index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
  AND idx_scan < 50
  AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
                                                                         pg_relation_size(i.indexrelid) DESC


-- ==> uptime.sql <==
SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime


-- ==> vacuum.sql <==
WITH table_opts AS (
    SELECT
      pg_class.oid, relname, nspname, array_to_string(reloptions, '') AS relopts
    FROM
      pg_class INNER JOIN pg_namespace ns ON relnamespace = ns.oid
), storage_settings AS (
    SELECT
      oid, relname, nspname,
      CASE
        WHEN relopts LIKE '%autovacuum_vacuum_threshold%'
          THEN regexp_replace(relopts, '.*autovacuum_vacuum_threshold=([0-9.]+).*', E'\\1')::integer
          ELSE current_setting('autovacuum_vacuum_threshold')::integer
      END AS autovacuum_vacuum_threshold,
      CASE
        WHEN relopts LIKE '%autovacuum_vacuum_scale_factor%'
          THEN regexp_replace(relopts, '.*autovacuum_vacuum_scale_factor=([0-9.]+).*', E'\\1')::real
          ELSE current_setting('autovacuum_vacuum_scale_factor')::real
      END AS autovacuum_vacuum_scale_factor,
      CASE
        WHEN relopts LIKE '%autovacuum_freeze_min_age%'
          THEN regexp_replace(relopts, '.*autovacuum_freeze_min_age=([0-9.]+).*', E'\\1')::integer
          ELSE current_setting('vacuum_freeze_min_age')::integer
      END AS autovacuum_freeze_min_age,
      CASE
        WHEN relopts LIKE '%autovacuum_freeze_table_age%'
          THEN regexp_replace(relopts, '.*autovacuum_freeze_table_age=([0-9.]+).*', E'\\1')::real
          ELSE current_setting('vacuum_freeze_table_age')::real
      END AS autovacuum_freeze_table_age,
      CASE
        WHEN relopts LIKE '%autovacuum_freeze_max_age%'
          THEN regexp_replace(relopts, '.*autovacuum_freeze_max_age=([0-9.]+).*', E'\\1')::real
          ELSE current_setting('autovacuum_freeze_max_age')::real
      END AS autovacuum_freeze_max_age
    FROM
      table_opts
)
SELECT
  storage_settings.nspname AS schema,
  storage_settings.relname AS table,
  to_char(pg_class.reltuples, '9G999G999G999') AS rowcount,
  to_char(psut.n_dead_tup, '9G999G999G999') AS dead_rowcount,
  to_char(autovacuum_vacuum_threshold
          + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples), '9G999G999G999') AS autovacuum_threshold,
  CASE
    WHEN autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples) < psut.n_dead_tup
      THEN 'yes'
  END AS expect_autovacuum,
  age(relfrozenxid) as relfrozenxid_age,
  autovacuum_freeze_table_age,
  CASE
    WHEN age(relfrozenxid) > autovacuum_freeze_table_age
      THEN 'yes'
  END AS next_autovacuum_will_be_a_freeze,
  autovacuum_freeze_max_age,
  ROUND(100.0 * age(relfrozenxid) / autovacuum_freeze_max_age::numeric, 1) || '%' AS "% til forced vacuum freeze"
FROM
  pg_stat_user_tables psut INNER JOIN pg_class ON psut.relid = pg_class.oid
  INNER JOIN storage_settings ON pg_class.oid = storage_settings.oid
ORDER BY storage_settings.relname