psql - PostgreSQL: Up and Running - page 50

You can even save commonly used queries as strings in your psqlrc file, as we did for
qstats91 and qstats92 . We recommend using lowercase for your shortcuts to avoid
conflict with psql environment variables, which are uppercase.
Retrieving Prior Commands
As with many command-line tools, you can use the up arrows in psql to recall com‐
mands. The HISTSIZE variable determines the number of previous commands that you
can recall. For example, \set HISTSIZE 10 lets you recover the past 10 commands and
no more.
If you spent time building and testing a difficult query or performing a series of im‐
portant updates, you may want to have the history of commands piped into separate
files for perusal later:
\set HISTFILE ~/.psql_history- :HOST - :DBNAME
Windows does not store command history unless you're running a
Unix environment like Cygwin.
psql Gems
In this section, we cover helpful featurettes buried inside the psql documentation.
Executing Shell Commands
In psql, you can call out to the OS shell with the \! command. Let's say you're on
Windows and need a directory listing. Instead of exiting psql, you can just directly type
\! dir at the prompt.
Watching Statements
The \watch command is a new feature in psql since PostgreSQL 9.3. Use it to repeatedly
run an SQL statement at fixed intervals so you can watch the output. For example,
suppose you want to monitor the queries that have not completed. You can run a query
such as the one shown in Example 3-3 .
Example 3-3. Watching other connection traffic every 10 seconds
SELECT datname, waiting, query
FROM pg_stat_activity
WHERE state = 'active' AND pid != pg_backend_pid(); \watch 10