Watching your PostgreSQL database

Many PostgreSQL users are running their favorite database engine on Linux or some other UNIX system. While Windows is definitely an important factor in the database world, many people like the flexibility of a UNIX-style command line. One feature used by many UNIX people is “watch”. watch runs commands repeatedly, displays their output and errors and allows you to watch the program output change over time. It is therefore a perfect tool to track down errors.

In the PostgreSQL world I have seen people running watch from the commandline. Here is an example:


watch -n 5 "psql -c 'SELECT now()' test"

Every five seconds the output of the SQL function will be displayed.
While this is easy, there is a better way: You can achive the same thing without having to leave psql at all.

Watching – here is how it works:


[hs@zenbook ~]$ psql test
psql (9.5.3)
Type "help" for help.

test=# SELECT now();
now
------------------------------
2016-06-02 19:20:42.93694+02
(1 row)

test=# \watch 5
Watch every 5s Thu Jun 2 19:20:48 2016

now
-------------------------------
2016-06-02 19:20:48.430408+02
(1 row)

Watch every 5s Thu Jun 2 19:20:53 2016

now
-------------------------------
2016-06-02 19:20:53.435332+02
(1 row)

The first thing to do is to run the command you want to execute repeatedly. Then just do “\watch seconds”. In my example the query will be executed every 5 seconds until watch is terminated (ctrl + c).

psql offers a nice and simple way to see, what a database is doing right now.

Hans-Juergen Schoenig
Hans-Jürgen Schönig has 15 years of experience with PostgreSQL. He is consultant and CEO of the company „Cybertec Schönig & Schönig GmbH“ (www.cybertec.at, www.postgresql-support.de), which has served countless customers around the globe.