cool psql prompts

I developed this as my standard prompt a few years back (double-click to see better):




- - - - - - - - - -

Here is the code to put in a .psqlrc file which should be created in your HOME directory:


--%M = database server
--%/ = database name
--%n = database user
--%> = port number
\echo '\n\033[33;1m-> Prompt is DBServer(LocalHost).DBName.DBServer.PortNumber [Local OS Timestamp]\033[0m\n'
\set PROMPT1 '\033[33;1m%M(from '`hostname`').%/.%n.%> [%`date`]\033[0m\n> '
\set PROMPT2 '> '

- - - - - - - - - -


You can change the colors by changing the [33 string to another number in the ASCII foreground list:

• black 30
• red 31
• green 32
• yellow 33
• blue 34
• magenta 35
• cyan 36
• white 37

- - - - - - - - - -


If you have scripts that call psql and you don't want this extra text showing up in the output, just add the -X option to the psql call to not use a .psqlrc file.

PG dup indexes

A problem that occurs in our environment with several different databases where manual changes have been made, then automated schema changes are applied is duplicate indexes.
This isn't a major problem per-se, but does incur overhead for DML and of course takes up additional disk space and requires more work when the autovacuum maintains the table.
So, here's how you can find 'em and decide which to get rid of based on usage denoted in the idx_scan column:


SELECT
ct.relname AS tablename,
ci.relname AS indexname,
s.idx_scan, s.idx_tup_read, s.idx_tup_fetch,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS indexsize,
pg_get_indexdef(i.indexrelid)
FROM pg_index i
JOIN ( SELECT COUNT(0), indrelid, indkey, indpred, indexprs
FROM pg_index
GROUP BY 2,3,4,5
HAVING COUNT(0)>1) AS x
ON (x.indrelid=i.indrelid AND x.indkey=i.indkey)
JOIN pg_class ct ON ct.oid=i.indrelid
JOIN pg_class ci ON ci.oid=i.indexrelid
JOIN pg_stat_all_indexes s ON i.indexrelid=s.indexrelid
ORDER BY ct.relname, i.indkey, s.idx_scan;

launch

Between a full-time job, being a new parent, owning/managing a rental home, why am I starting to blog?
How will I find the time? the energy?

We'll see...