Installing and Getting Started with PostgreSQL

PostgreSQL is a mature, rock-solid open-source relational database known for its strong SQL-standard compliance, which makes it a great fit for your own server or VPS. This guide walks you through installation and the basics on Ubuntu/Debian.

Installation

On Ubuntu/Debian, PostgreSQL ships in the official repositories, so a single apt command does the job:

sudo apt update
sudo apt install -y postgresql postgresql-contrib

The service starts automatically once the install finishes. You can confirm it's running:

sudo systemctl status postgresql

The install also creates a postgres system account and a matching database superuser role.

Connecting for the first time

PostgreSQL defaults to peer authentication, meaning your system username must match the database role name. So for your first login, hop in through the postgres account:

sudo -u postgres psql

The prompt changes to postgres=# — you're now inside psql, the interactive client. Type \q to exit.

Handy meta-commands

Anything starting with a backslash in psql is a meta-command, not SQL, and needs no semicolon:

• \l — list all databases
• \du — list all roles (users)
• \c dbname — switch to a database
• \dt — list the tables in the current database
• \d tablename — inspect a table's structure
• \? — show meta-command help

Creating roles and databases

In PostgreSQL, "users" are all called roles. Below we create a role that can log in, give it a password, and hand it ownership of a new database:

CREATE ROLE appuser WITH LOGIN PASSWORD 'your_strong_password';
CREATE DATABASE appdb OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

To change the password on an existing role:

ALTER ROLE appuser WITH PASSWORD 'new_password';

You can then connect to that database as the new role:

psql -h 127.0.0.1 -U appuser -d appdb

Allowing remote access

By default PostgreSQL listens only on localhost, so nothing outside the box can reach it. Opening it up means editing two files (the 16 in the paths is the version number — adjust to match yours).

First, edit postgresql.conf so it listens on every interface:

sudo nano /etc/postgresql/16/main/postgresql.conf

listen_addresses = '*'

Second, edit pghba.conf to add a rule permitting your remote network:

sudo nano /etc/postgresql/16/main/pg_hba.conf

host    all    all    0.0.0.0/0    scram-sha-256

Restart the service for the changes to take effect:

sudo systemctl restart postgresql

In production, narrow 0.0.0.0/0 down to trusted IPs and open port 5432 in your firewall.

How it differs from MySQL

• User model: MySQL accounts are user@host; PostgreSQL uses roles, and login ability is governed by the LOGIN attribute.
• Default port: PostgreSQL is 5432, MySQL is 3306.
• Client: PostgreSQL uses psql with meta-commands like \l and \dt; MySQL uses mysql with SHOW DATABASES; and SHOW TABLES;.
• Switching databases: PostgreSQL uses \c dbname; MySQL uses USE dbname;.
• Standards: PostgreSQL tends to be stricter and more complete in its support for the SQL standard, complex queries, and transactions.

Summary

On Ubuntu/Debian, apt install postgresql gets you up and running, your first login goes through sudo -u postgres psql, and CREATE ROLE / CREATE DATABASE set up your roles and databases while \l \du \dt let you check the current state at a glance. When you need remote access, remember to edit both listenaddresses and pghba.conf and restart — then tighten the exposed surface with a firewall and a specific network range to keep the database safe.