Friday, January 27, 2017

Perl: epoch(now)

perl -MTime::Local -e 'print timegm(gmtime)'

PostgreSQL: size of a row

Source: Measure the size of a PostgreSQL table row
  SELECT l.what, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / x.ct END          AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   radius_db.radacct t  -- provide table name *once*
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl))
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
    , (true , 'indexes_size'                     , pg_indexes_size(tbl))
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
    , (true , 'live_rows_in_text_representation' , txt_len)
    , (false, '------------------------------'   , NULL)
    , (false, 'row_count'                        , ct)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, what, nr);
 

Thursday, January 19, 2017

mod_perl: how to use environment variable

In httpd.conf:
PerlSetEnv PG_FLG 1
In mod_perl code:
sub is_pure_pg {
    return exists $ENV{PG_FLG};
}

Friday, January 13, 2017

PostgreSQL: rotate pgbouncer logs

Simplest method: switch to syslog
vi /etc/pgbouncer/pgbouncer.ini
; logfile = /var/log/pgbouncer.log
syslog = 1
psql -p6432 -Upostgres -d pgbouncer -c reload