PostgreSQL is a very popular database engine used by a lot of websites, is one of the most used relational database management system available, and after installing it we can use some tools that it includes. One of these tools is a command-line tool called psql, and today we are going to learn a lot about it, and how to fix the famous ‘psql command not found’ error on your systems.
About the psql command
So as we said, the psql command is related to the PostgreSQL RDBMS. But what does this command do exactly? Let’s start by explaining that.
psql is a command-line tool that grants us the ability to interact with the PostgreSQL RDBMS using the command line o terminal of our system.
There are different ways to use or interact with an RDBMS. Some of these are graphic programs like phpMyAdmin, which is used for another RDBMS called MySQL (there is a version of it for PostgreSQL too), and there are less graphic ways like the command-line or terminal. But why would someone use a terminal if a nice software with a nice GUI can be used instead?
Well, some people are more used to the terminal, and in some cases the features available in these tools are different, so even if you do have a GUI to interact with an RDBMS you may be lacking a function that is available in the terminal, and vice versa of course.
What can I do with psql?
As we said, it is used to connect with PostgreSQL so we can then issue different queries, and of course, see the results of those queries. It includes a lot of functions, around 30 to be more precise, but some are more important than others so let’s see which of these functions or options are used the most.
- –command: this a widely used psql option and, as you may have guessed already, it is used to issue a command. This function is used a lot in bash scripts related to PostgreSQL because it will allow us to execute commands in PostgreSQL directly from the script.
- –dbname: another very important option, this one will let us connect to a particular database in PostgreSQL to work with it. This way we make sure that the queries that we run are only executed in the right database.
- –list: this is a pretty straight forward feature that is used to list all the databases available. Yeah, that is all it does, but it is very useful to check if we are missing a database for example.
- –quiet: a feature very useful if we don’t want to read much, it will automatically suppress all messages that the executed command may return on our screen. This may be useful in some situations, for example, if too much useless data is printed, but in other cases, we may not want to use it if we really need to look at the output.
- –username: with this option we can connect to a database using a username instead of the default psql session.
- –no-password: this option disables the password prompt, so we will not be able to get a password request while using the psql tool. In this case, passwords will have to be stored in a file and obtained from there. This can also be used when we need to run an automatic script that requires a password, in this case we should have the password stored in a secure file so the script can read it from there.
- –password: this is the opposite of the previous option, which means that we will be asked for a password when using it. psql will ask for a password when we try to connect to a database or run a query. In most cases, this feature is enabled by default in PostgreSQL’s config so we may not need to use it in the terminal.
How can I fix the psql command not found error on Linux?
So now that we have explained what psql is exactly and how it can be used to interact with PostgreSQL databases let’s see how can we resolve one of the most common problems associated with this tool: the not found error.
If you ever come across the psql command not found error then you may, or may not, be doing something wrong. Before explaining the solution, let’s see what is going on here.
When you get the psql not found error it means that the binary/executable file for psql wasn’t found in your user’s PATH. Depending on how PostgreSQL was installed it may or may not be placed in your PATH.
We can also get this error if we haven’t installed PostgreSQL yet, in which case the solution is to install it of course and check again after that.
But what if PostgreSQL is installed but you still get this error anyway? Well, don’t worry, it’s pretty easy to solve this problem, and this solution works for both CentOS and Ubuntu systems, and for most Linux distros actually.
First, let’s run a very useful command called ‘updatedb’, which nowadays comes included in most distros by default as a part of the ‘locate’ tool. It may run for a few minutes before finishing:
Once it finishes, the locate cache database will be updated and we can use it to find the exact location of the psql command, so let’s run this in our shell:
Once the exact path of the binary is found we only have to create a symlink. For example, if the psql binary was found in the path ‘/usr/lib/postgresql/9.5/bin/psql’ then we only need to issue the following command to create a symlink:
ln -s /usr/lib/postgresql/9.5/bin/psql /usr/bin/psql
This way the binary will be available in our PATH and we will be able to execute it quickly just by typing “psql” in our shell. This is the easiest and quickest way to solve the psql command not found error.
So today we have learned a bit more about the psql tool which is used in the shell to issue commands and queries in PostgreSQL, and it can also be used in bash scripts that connect to this RDBMS.
We also know how to fix the psql command not found error, which is one of the most common errors related to this tool. The solution is quite easy and is compatible with most Linux distros, requiring only a symlink to add the binary to the user’s PATH.