If you're new to PostgreSQL, the basics can feel intimidating. This guide walks you through the essential tasks every beginner should know: starting and stopping PostgreSQL, finding the key configuration files, connecting with psql, and performing simple administrative commands. This applies to PostgreSQL 18 on Debian-based systems.

Managing the PostgreSQL Service

On Debian and Ubuntu, PostgreSQL runs as a systemd service. These commands handle starting, stopping, restarting, and checking the service.

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql
sudo systemctl status postgresql

Where PostgreSQL Stores Its Files

PostgreSQL 18 creates its main data directory under:

/var/lib/postgresql/18/main

This is where PostgreSQL stores everything: tables, indexes, catalogs, WAL, and internal metadata. But the files you interact with most are the configuration files.

The postgresql.conf File

This is PostgreSQL’s main configuration file. It controls memory settings, logging, replication, ports, autovacuum, and tuning parameters. On Debian, it's located at:

/etc/postgresql/18/main/postgresql.conf

Any time you make changes here, restart PostgreSQL:

sudo systemctl restart postgresql

The pg_hba.conf File

This file controls authentication and access rules. If a user can't connect or you're seeing messages about missing entries, this is where you look.

/etc/postgresql/18/main/pg_hba.conf

After modifying authentication rules, reload PostgreSQL instead of restarting it:

sudo systemctl reload postgresql

Checking PostgreSQL Clusters

Debian systems use the cluster system. This command shows which PostgreSQL versions and clusters are running:

pg_lsclusters

Connecting to PostgreSQL with psql

PostgreSQL creates a Linux user named postgres. Most admin tasks are performed using this account.

sudo -u postgres psql

To connect to a specific database:

sudo -u postgres psql -d mydatabase

Useful psql Commands

\l            -- list databases
\c mydb       -- connect to a database
\dt           -- list tables
\d table      -- describe a table
\q            -- quit

Finding Which Port PostgreSQL Uses

grep -R "port" /etc/postgresql/18/main/postgresql.conf

PostgreSQL listens on port 5432 by default.

Creating Users and Databases

sudo -u postgres createuser myuser
sudo -u postgres createdb mydb -O myuser

Conclusion

With these bare-bones basics, you’ll be able to handle the core PostgreSQL tasks you’ll use over and over again—starting and stopping the service, editing configs, and connecting with psql. Mastering these fundamentals puts you in a solid position to learn deeper topics like tuning, monitoring, backups, and advanced administration later on.