Connecting to PostgreSQL Using psql

The `psql` command-line tool is the default PostgreSQL client for interacting with databases. It allows you to execute SQL queries, manage database objects, and perform administrative tasks. In this tutorial, you’ll learn how to connect to a PostgreSQL database using `psql`.

Prerequisites

Before proceeding, ensure you have:

•  PostgreSQL installed on your system or running in a Docker container.
•  Access to a PostgreSQL database with a username and password.
•  The `psql` command-line tool installed (included with PostgreSQL).

Step 1: Open the Terminal

The psql tool is accessed via the terminal (Linux/macOS) or command prompt (Windows). Ensure PostgreSQL is running before proceeding.

To check the status of PostgreSQL (Linux/macOS):

sudo systemctl status postgresql

Output will be as shown below

ramansah@bckinfo:~$ sudo systemctl status postgresql
[sudo] password for ramansah: 
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
Active: active (exited) since Wed 2025-03-12 09:01:42 WIB; 1h 35min ago
Process: 2236 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 2236 (code=exited, status=0/SUCCESS)
CPU: 12ms

Mar 12 09:01:42 bckinfo systemd[1]: Starting postgresql.service - PostgreSQL RDBMS...
Mar 12 09:01:42 bckinfo systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.

If using Docker:

docker ps

The output will be as shown below :

ramansah@bckinfo:~$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
200cbc34497a postgres "docker-entrypoint.s…" 6 seconds ago Up 5 seconds 5432/tcp, 0.0.0.0:5444->5444/tcp, [::]:5444->5444/tcp docker_bckinfo

Step 2: Connect to PostgreSQL Locally

To connect as the default PostgreSQL user (postgres), use:

psql -U postgres

Output will be as shown below :

ramansah@bckinfo:~$ sudo su - postgres
postgres@bckinfo:~$ psql
psql (17.4 (Ubuntu 17.4-1.pgdg24.04+2))
Type "help" for help.

postgres=#

We can also specify the database:

psql -U postgres -d mydatabase

If your PostgreSQL server runs on a non-default port (e.g., 5444), specify it:

psql -U postgres -d mydatabase -p 5444

Step 3: Connect to PostgreSQL on a Remote Server

To connect to a remote PostgreSQL server, use:

psql -h remote_server_ip -U myuser -d mydatabase

For example:

psql -h 192.168.64.128 -U rentaladmin -d carrentaldb

Conclusion

The psql tool provides an efficient way to interact with PostgreSQL databases. Whether working locally or connecting remotely, mastering psql will enhance your database management skills.