Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
psql config

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

image

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:

\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'
#!/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
-- ==> 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
@verfriemelt-dot-org

This comment has been minimized.

Copy link
Owner Author

@verfriemelt-dot-org verfriemelt-dot-org commented Dec 18, 2019

image

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment