Installing & Administering PostgreSQL

postgres1-1 promised this chapter would deliver on the schema concept MySQL doesn't really have. This chapter covers that, plus the practical basics of getting a Postgres server running and administered: installation, the psql client, roles, and Postgres's own distinct authentication model.

Installation

On Debian/Ubuntu, Postgres installs via the standard package manager, same as mysql1's own MySQL installation chapter covered. A key difference shows up immediately after install: Postgres creates a dedicated postgres operating-system user and a matching superuser role of the same name, and the initial database cluster is created via initdb — the step that actually lays down the physical storage directory Postgres will use, distinct from simply having the software installed.

psql — The Interactive Client

psql is Postgres's own interactive client, roughly analogous to the mysql command-line client — but with a genuinely different design choice: rather than special SQL-like commands for administrative tasks, psql uses backslash meta-commands, keeping the SQL syntax itself as close to the pure standard as possible.

Roles vs. Users

Postgres unifies what MySQL splits into two separate concepts. In MySQL, there are USER accounts, and a separately-evolved mechanism (roles, added only in MySQL 8.0) for grouping privileges — roles are a relatively recent addition layered on top of a user-centric model. In Postgres, there has only ever been one core concept: the role. A role can behave as a login-capable user, as a pure privilege-grouping "group," or both at once — the distinction comes entirely from a single attribute.

-- A login-capable role (behaves like a "user")
CREATE ROLE alice LOGIN PASSWORD 'secret';

-- A pure group role — cannot log in directly, only organizes privileges
CREATE ROLE reporting_team;

-- Role membership: alice inherits every privilege granted to reporting_team
GRANT reporting_team TO alice;

This single unifying model means privilege organization was never bolted on after the fact — it's been the same core mechanism from the start.

The Schema Concept

This is the item postgres1-1 named as a genuine difference: a Postgres database contains one or more schemas, and each schema contains its own tables, views, and other objects — a real namespace layer sitting between "database" and "table" that MySQL simply doesn't have. In MySQL, "database" and "schema" are actually synonyms — CREATE DATABASE and CREATE SCHEMA do the literal same thing.

Every Postgres database starts with a default schema named public — a CREATE TABLE with no schema specified lands there. This becomes genuinely useful in real applications: a multi-tenant system can give each tenant its own schema within a single shared database, or a large application can separate an app schema from a reporting schema without needing entirely separate databases or connections. Postgres resolves an unqualified table name using its own search_path setting, checking each listed schema in order until a match is found.

pg_hba.conf — Host-Based Authentication

Postgres separates authentication into its own dedicated configuration file, pg_hba.conf, entirely apart from the role/privilege system itself. It controls who can attempt to connect, from where, and using what authentication method (trust, scram-sha-256, peer, ident, reject) — checked before a role's own privileges are ever evaluated at all. MySQL instead folds the authentication method directly into the user account itself (CREATE USER 'x'@'host' IDENTIFIED BY ...), rather than using a separate control file. Postgres's two-layer model — pg_hba.conf decides whether a connection attempt is even allowed, role privileges decide what it can do once connected — is a genuinely distinct architectural split.

Hands-On Exercises