yi

function string.fromhex(str)
    return (str:gsub('..', function (cc)
        return string.char(tonumber(cc, 16))
    end))
end

function string.tohex(str)
    return (str:gsub('.', function (c)
        return string.format('%02X', string.byte(c))
    end))

lfittl

# Copyright (c) 2014 Lukas Fittl <lukas@pganalyze.com>
#
# Released in the public domain - fork as you wish.

require 'rubygems'
require 'mixlib/cli'
require 'pg'
require 'pg_query'
require 'curses'

pyk

Kartones

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

• -E: will describe the underlaying queries of the \ commands (cool for learning!)
• -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

ekho

http://www.ekho.name/2012/03/pgagent-debianubuntu.html

jfrost

-- Completely unused indexes:
SELECT relid::regclass as table, indexrelid::regclass as index
     , pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes
  JOIN pg_index
 USING (indexrelid)
 WHERE idx_scan = 0
   AND indisunique IS FALSE order by pg_relation_size(indexrelid);

rgreenjr

-- show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 

Randommood

postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# \dx+
      Objects in extension "plpgsql"
            Object Description