Here is a quick SQL to check PostgreSQL9.4 current activities, to let you know what your postgres is busy on.

 

select usename, query from pg_stat_activity where datname='chimera' and query<>'<IDLE>';

 Here is one example output:

chimera=> select usename, query from pg_stat_activity where datname='chimera' and query<>'<IDLE>';
usename | query
---------+------------------------------------------------------------------------------------------------------------
chimera | WITH RECURSIVE paths(pnfsid, path) AS ( +
| VALUES (path2inode('000000000000000000000000000000000000', '/data/fibrevillage'), '') +
| UNION +
| SELECT d.ipnfsid, p.path||'/'||d.iname +
| FROM t_dirs d, paths p +
| WHERE p.pnfsid = d.iparent AND d.iname != '.' AND d.iname != '..' +
| ) +
| SELECT p.path FROM t_inodes i, paths p +
| WHERE i.ipnfsid = p.pnfsid AND i.icrtime <= '2016-01-24T13:23:50-0800' AND i.itype = 32768 ORDER BY path ;
chimera | select usename, query from pg_stat_activity where datname='chimera' and query<>'<IDLE>';

 

For pg9.1, 9.2, 9.3, see check current postgresql activites

 

Want to kill, or terminate a query ? see How to stop a postgre query