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
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;
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;