Useful Postgres Queries

The more I work with Postgres, the more I prefer it to MySQL. Two years ago we migrated a sizable database (42g disk size) from MySQL to gain access to the Geospatial searching with GIS. We’ve replaced Sphinx with Elastic Search which handles spatial searches much faster. We no longer use the GIS extension, but the Postgres database remains. We currently run 9.1.

This post is intended to be a holder for the queries I find useful from time to time, but can’t seem to commit to memory.

Find all queries that have been running for at least two minutes:

 select procpid,datname,current_query,now() - pg_stat_activity.query_start 
   as duration 
 from pg_stat_activity 
 where pg_stat_activity.current_query <> ''::text and 
   now() - pg_stat_activity.query_start > interval '2 minutes';

To kill a long running process:

 kill -INT procpid

or, more safely

  select pg_cancel_backend(procpid#);

Adding a user to postgres and pg_bouncer:

  • Create a new user/role in the Postgres DB:
     CREATE USER username WITH PASSWORD 'password';
    
  • Add permission:
     GRANT SELECT/ALL ON ALL TABLES IN SCHEMA public TO username;
    
  • Generate pg_auth line:
     select '"'||rolname||'"'||' "'||rolpassword||'"' from pg_authid;
    

Index Usage:

 select * 
 from pg_stat_all_indexes 
 where schemaname = 'public' 
 order by relname asc, idx_scan desc;

View slow queries:

 select 
   procpid,
   usename,
   datname,
   substring(current_query,0,180),
   now() - pg_stat_activity.query_start as duration 
  from pg_stat_activity 
  where pg_stat_activity.current_query <> ''::text and 
    current_query <> '' and 
    now() - pg_stat_activity.query_start > interval '0.5 seconds' 
  order by duration desc;

How far is the slave behind the master?

 SELECT now() - pg_last_xact_replay_timestamp() AS time_lag;

This entry was posted in postgres. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *