Client installation

If you have a PostgreSQL server already installed and you need to interact with it, then you need to install the posttgresql-client software package. In order to do so, open a terminal and execute the following command:

sudo apt-get install PostgreSQL-client-10

With the installation of postgrsql-client-10, several tools are installed including the PostgreSQL interactive terminal (psql), which is a very powerful interactive frontend tool for PostgreSQL. To see the full list of installed programs, one can browse the installation directory. Note that the installation path might vary depending on the installed PostgreSQL version and also depending on the operating system:

ls /usr/lib/PostgreSQL/10/bin/
clusterdb createdb createuser dropdb dropuser pg_basebackup pg_dump pg_dumpall pg_isready pg_receiveWAL pg_recvlogical pg_restore psql reindexdb vacuumdb

In order to connect to an existing PostgreSQL server using psql, one needs to specify the connection string, which might include the host, the database, the port, and the username.

Another powerful frontend graphical user interface (GUI) tool is pgadmin4, which is used for PostgreSQL administration and development. pgAdmin is favored by beginners, while psql can be used for shell scripting.

pgadmin4 has many features, such as cross-platform, web-based, server and desktop modes, and so on. In order to install pgAdmin in desktop mode, one should run the following command:

# get virtual environment and pip 
sudo apt-get install pip 
sudo pip install --upgrade pip
sudo pip install virtualenvwrapper

# create a virtual environment and activate it 
virtualenv pgadmin && cd pgadmin && source bin/activate

# get recent version f pgadmin and install it. 
wget https://ftp.PostgreSQL.org/pub/pgadmin/pgadmin4/v2.0/pip/pgadmin4-2.0-py2.py3-none-any.whl
pip install pgadmin4-2.0-py2.py3-none-any.whl

# create a configuration based on config.py and run it 
cp ./lib/python2.7/site-packages/pgadmin4/config.py ./lib/python2.7/site-packages/pgadmin4/config-local.py
python  ./lib/python2.7/site-packages/pgadmin4/pgAdmin4.py

Another GUI tool to interact with PostgreSQL is DBeaver https://dbeaver.jkiss.org/. This tool provides a universal SQL client for many databases including PostgreSQL.