~/.psqlrc file for DBA’s

Posted on by Raghav
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

14 Replies to “~/.psqlrc file for DBA’s”

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

  5. 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!

  6. 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.

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

  8. 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 🙂

  10. 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 🙂

Comments are closed.