Connecting to PostgreSQL – Basics

Here, we will take a look at the basic method of connecting to the PostgreSQL cluster running on the local server using command psql.

Connection to a PostgreSQL database can be made either remotely or locally. If you are following up on the previous exercise of ‘Install PostgreSQL’ and installed PostgreSQL on Ubuntu WSL or Ubuntu machine, then you are ready to connect to the database.

• Sudo to ‘postgres’ user from Linux terminal and enter the password chosen earlier.

$ sudo su - postgres

• If you didn’t start the PostgreSQL cluster earlier, then start now. Start might fail in certain situations. In such a case, please refer to the solution below.

$ pg_ctlcluster 13 main start

If start fails with the below error, create the directory, change the directory ownership to ‘postgres’. Then, try starting the cluster again.

install: cannot change owner and permissions of ‘/var/run/postgresql’: No such file or directory
Error: Could not create /var/run/postgresql/13-main.pg_stat_tmp: No such file or directory

$ sudo mkdir /var/run/postgresql

$ sudo chown -R postgres:postgres /var/run/postgresql

A PostgreSQL Cluster is a postmaster (main background process) and a group of auxiliary processes, all managing a shared data directory that contains one or more databases. PostgreSQL Cluster has multiple databases and all those databases are stopped and started together when the Cluster is stopped or started.

In stricter terms, a PostgreSQL Database is one of the databases of the Cluster. But, PostgreSQL Cluster and PostgreSQL Database terms can be used interchangeably sometimes.

Note that pg_ctlcluster is an Ubuntu-specific wrapper command. There are few other commands too. For example, pg_lsclusters – lists the PostgreSQL clusters running on the server which have been created using another wrapper command pg_createcluster. Similarly, the command pg_dropcluster is used to drop a PostgreSQL cluster. These commands will be covered in detail later.

• List the cluster which you started earlier.

$ pg_lsclusters

• Connect to the cluster running on default port of 5432 using psql command.

$ psql

• To connect to the cluster running on non-default port, use psql command with -p option.

$ psql -p 5433

• Check the details of your database connection using commands \c and \conninfo. When you connect to the cluster using ‘postgres‘ OS user without giving any database name, it connects to ‘postgres‘ database by default.

$ psql

# \c

# \conninfo

Here, you are able to connect to the cluster just using ‘psql’ command because the cluster is running on the localhost on default port ‘5432’ and you are on the logged into the terminal using operating system user ‘postgres’ which has a default corresponding user ‘postgres in the default database ‘postgres’ inside the PostgreSQL cluster.