Thursday, November 10, 2011

~/.psqlrc file for DBA's


In our regular DBA monitoring, we will be using so many combination of pg_catalog queries to reteive information like <IDLE> in transaction , waiting queries,  No. of connections, etc. Most of the DBA's, create views to cut short big combination queries and keep handy for later use per requirement.

PostgreSQL, provides a startup file(.psqlrc) which executes before connecting to the database when using with psql utility. Using .psqlrc file you can place all your important queries with one word alias by '\set' command and execute it in psql terminal instead of typing big queries. If you wont see .psqlrc file in 'postgres' user home directory, you can create it explicitly. I tried it and found very helpful.

Points on .psqlrc:
  • .psqlrc is a startup file, executes when connecting to the cluster.
  • .psqlrc file will reside in 'postgres' user home directory.
  •  psql options -X or -c, do not read the .psqlrc file.
  • .psqlrc file is for complete session-level not database level.
My terminal Screenshot:


Lets see how to implement this.

Syntax:
\set <alias-variable-name>  'query'
Note: if your query has single or double quotes then use \' or \" in the query.

Sample Queries to put in .psqlrc file with alias:
vi ~/.psqlrc

\set PAGER OFF

\set waits 'SELECT pg_stat_activity.procpid, pg_stat_activity.current_query, pg_stat_activity.waiting, now() - pg_stat_activity.query_start  as "totaltime", pg_stat_activity.backend_start FROM pg_stat_activity WHERE pg_stat_activity.current_query !~ \'%IDLE%\'::text AND pg_stat_activity.waiting = true;;'

\set locks 'select pid,mode,current_query from pg_locks,pg_stat_activity where granted=false and locktype=\'transactionid\' and pid=procpid order by pid,granted;;'

:wq!

Usage:
postgres=# :waits
 procpid |         current_query         | waiting |    totaltime    |          backend_start
---------+-------------------------------+---------+-----------------+----------------------------------
    9223 | insert into locks VALUES (1); | t       | 00:00:18.901773 | 2011-10-08 00:29:10.065186+05:30
(1 row)

postgres=# :locks
 pid  |   mode    |         current_query
------+-----------+-------------------------------
 9223 | ShareLock | insert into locks VALUES (1);
(1 row)


Was it not helpful. Enjoy... :). Will be back with some more stuff.

--Raghav



7 comments :

Anonymous said...

It works..!Thanks

Pavani said...

Really helps Great ..!!!

Anonymous said...

Wonderful, many thx!!

Anonymous said...

psql 8.4.11, in .psqlrc must be
\pset pager
(\set PAGER OFF not work)

Raghavendra said...

Very True. Its my typo... It wont work in any version. \set is for variable declaration. Thanks for corrections and visiting my blog. Good catch though :)

Anonymous said...

Any way to use if command in .psqlrc? I would like to use only one .psqlrc but this should define different prompts for different systems.

Anonymous said...

Maybe you can try something like the following:
\set PROMPT1 '%[%033[1;32;40m%]%m port=%> %n@%/%[%033[0m%] %# '
This will show on what system, port, user and database you are connected to. No need for if statements!

Post a Comment

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License