Enable SYSLOG facility for Postgresql instance

Modified values of the following parameters at postgresql.conf:

log_destination = ‘syslog’
syslog_facility = ‘LOCAL0’
silent_mode = on

Added lines at /etc/syslog.conf

LOCAL0.*              -/var/log/pgsql/pgsql.log

 Ignored PostgreSQL facility for the default log file otherwise you would log the queries twice:

*.info;...;local0.none        /var/log/messages

Restarted the SYSLOG service:

/etc/init.d/sysklogd restart

Now, we got postgresql log at /var/log/pgsql/pgsql.log 

Continue reading →

Top 20 tables acording to sequential scan in Postgresql

select c.relname as name, c.reltuples as No_of_records, c.relpages as  No_of_pages,
   pg_size_pretty(pg_relation_size(c.relname)) as size,
   t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch, t.n_tup_ins, t.n_tup_upd, t.n_tup_del,
   COALESCE(t.idx_tup_fetch,0) + COALESCE(t.seq_tup_read,0) as total_read
from pg_stat_user_tables as t inner join pg_class as c
         on (t.relid = c.oid)
where c.relkind ='r'
order by seq_scan desc
limit 20

Continue reading →