How to configure psql PostgreSQL client on a production environment to limit the downtime resulting from human mistakes

During systems maintenance, it is often required to conduct working on dev, test, preprod and prod systems. To avoid accidental mistakes leading to downtime it is worth to give a clear warning to operators logging on and working on prods environments.

This post is short, for many of you probably obvious (hopefuly), others probably planned to take the steps described here, but there were always more urgent things to do. Therefore, below we show how to configure psql – the official PostgreSQL database client – on production environments.

At first glance, it may seem that psql is a primitive text tool with few options, so whenever possible you should replace it with something more functional. Nothing could be more wrong! Although it is a text tool, it has features (even the autocomplete function) that allow it to be used as the only database administration tool. And although we use various graphical tools in development environments (e.g. DBeaver), on server environments we use psql exclusively.

We prefer not to expose the database port to the Internet, and all operations that require maintaining the database are performed directly on the server host via the SSH protocol. So we use the psql client installed directly on the server host. This allows the client to be configured in one place and guarantees that for each of the admins, psql will be configured the same and will behave the same.

Autocommit

By default, psql is configured with autocommit enabled. It must be remembered that in PostgreSQL the transaction has a large scope and unlike e.g. the Oracle database, the transaction even covers DDL commands. This is undoubtedly an advantage! And we must not skip it! Therefore, the autocommit option must be turned off in production environments. This will allow more informed transaction approval and give you the opportunity to roll back the transaction if something goes wrong.

Prompt

The prompt line plays a key role in terminal applications. In server environments, it should shout, be red, and convey the most important information such as the username of the user who connected to the database, etc. In all other environments, even (or even more) preprod, the prompt should be completely different, so that one who logins into production environments (or switches windows in the terminal application between production and other environments) always knows when he starts touching the production environment.

Welcome message (a.k.a. banner)

The welcome message may also have a beneficial role in the context of the considerations here. Thanks to it, immediately after logging in, the user can read the most important information, e.g. to which environment he has logged in, what default settings are changed (e.g. that we have disabled autocommit) ;), etc.

All the configuration issues listed above can be defined in one file. As we have already mentioned, we want it to be global (not different for each user), so we need to check where, in our operating system, psql stores the global configuration file. In our example, we use Ubuntu distro, and the file is stored in /etc/postgresql-common/ and is named psqlrc. An exemplary configuration could look like below. To be precise this is not a config file, but a script that runs just after the user logs in.

\set AUTOCOMMIT off
\set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%[%033[1;33m%]<LIVE>%[%033[0m%]%R%#%x '

\echo You have logged into LIVE database!
\echo AUTOCOMMIT is set to :AUTOCOMMIT

The exact meaning of PROMP parameters can be found in the PostgreSQL documentation so you can tailor it to your needs.

The result of the above configuration will be as follow.

And you? Have you configured your production psql or you are still waiting for a disaster?