Install PostgreSQL and Use psql

Installing PostgreSQL gives you two separate programs that work together: the database server, usually named postgres, and client tools such as psql. The outcome of this lesson is practical: you should be able to install a local PostgreSQL instance, understand what was created on disk and in the operating system, connect with psql, run SQL, inspect connection state, and recover from the most common first-day failures.

This foundation matters for the rest of a PostgreSQL course because every later topic depends on knowing which layer you are touching. A failed query, a failed login, and a stopped server can all look like "PostgreSQL is broken" from the application side, but they have different causes and fixes.

What Installation Actually Creates

A PostgreSQL installation has three important parts. The binaries are executable programs such as postgres, initdb, createdb, and psql. The data directory is a cluster of files that stores databases, transaction logs, configuration, and internal catalogs. The service definition is how your operating system starts, stops, and supervises the server process.

The word cluster in PostgreSQL does not mean a group of machines. It means one initialized data directory managed by one server process. A cluster can contain multiple databases, and every database in that cluster shares cluster-wide objects such as roles and tablespaces. Installation packages often create a default cluster for you; source builds and container images may expect you to initialize one explicitly.

The server listens for client connections either on a TCP address such as localhost:5432 or on a Unix-domain socket path. Authentication is controlled by pg_hba.conf, while basic server settings such as port, listen addresses, memory settings, and logging are controlled by postgresql.conf. The psql program is only a client. It does not store tables and it does not start the server unless a wrapper script from your platform does that separately.

psql Connection Anatomy

A psql connection needs a host or socket, a port, a database name, and a database role. If you omit values, libpq, PostgreSQL’s client library, fills them from environment variables and defaults. A common surprise is that psql without arguments tries to connect to a database with the same name as your operating-system user, using a database role with the same name.

The prompt shows useful state. A prompt ending in =# usually means the current role is a superuser; => means it is not. SQL commands end with a semicolon. Backslash commands, called meta-commands, are interpreted by psql itself and do not need a semicolon. For example, \conninfo displays the current connection, \l lists databases, \dt lists tables in the current schema search path, and \q exits.

Example 1: Confirm the Server and Client

Start by checking whether the client exists and whether a server accepts connections. The exact service name varies by platform, so the first command is intentionally about the client binary and the second uses PostgreSQL’s own readiness probe.

psql --version
pg_isready -h localhost -p 5432

Expected behavior is a psql version line followed by a readiness message such as localhost:5432 - accepting connections. If psql is missing, the client package is not installed or not on PATH. If pg_isready reports no response, the server is stopped, listening on a different port, or blocked by local configuration.

Example 2: Create a Database and Inspect It

The next step separates cluster-wide identity from database-local objects. Create a database, connect to it, and ask PostgreSQL what database and role are active.

createdb course_lab
psql -d course_lab -c "SELECT current_database(), current_user;"

The deterministic part of the output is that current_database returns course_lab. The current_user column returns the database role used for the connection, often your operating-system user on a local peer-authenticated install. This example proves you are not merely running a client; you are connected to a specific database inside a specific cluster.

Example 3: Use psql Meta-Commands with SQL

Once connected, combine SQL with meta-commands. SQL changes or reads database state. Meta-commands help you inspect that state from the client.

CREATE TABLE notes (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    body text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO notes (body) VALUES ('first psql row');
SELECT id, body FROM notes;

The CREATE TABLE statement registers a relation in the system catalogs. The identity column asks PostgreSQL to generate a numeric key. The insert writes one row, and the final query should return one row with id equal to 1 in a newly created table and body equal to first psql row. In the aligned table output, the row appears under the headers id and body; the important deterministic values are 1 and first psql row.

Inside an interactive psql session, \dt should then show the notes table. \d notes shows columns, defaults, indexes, and constraints. This is often faster and less error-prone than querying catalog tables manually while you are learning the shape of a database.

Example 4: Connection Strings and Noninteractive Use

Applications normally use connection strings, and automation normally runs psql noninteractively. The URI form makes every connection field visible.

psql "postgresql://localhost:5432/course_lab" -c "SELECT count(*) FROM notes;"

For a fresh run after the previous example, the expected count is 1. This pattern is useful in scripts because the command exits with a nonzero status when the connection or SQL command fails. For repeatable scripts, add -v ON_ERROR_STOP=1 so psql stops at the first SQL error instead of continuing through later commands.

Design Choices and Trade-offs

Package-manager installs are convenient because they integrate with the operating system’s service manager, log locations, users, and upgrade path. They are usually the best choice for a workstation or a single training server. Containerized PostgreSQL is convenient for disposable labs and CI, but the data directory must be mounted deliberately or it disappears with the container. Source builds provide maximum control, but they make you responsible for service files, upgrades, paths, and dependencies.

Local authentication also involves trade-offs. Peer authentication is convenient on Unix-like systems because the operating-system user maps to a database role without a password. Password authentication is closer to what application deployments use, but it requires safe password storage and pg_hba.conf rules that do not accidentally allow more hosts than intended. For learning, a local-only server with a dedicated lab role is safer than using a superuser for every command.

Failure Modes and Troubleshooting

Symptom: psql: command not found. Cause: the client package is not installed or its binary directory is not on PATH. Diagnose: run which psql and inspect your package manager’s installed files. Correct: install the PostgreSQL client tools or add the correct binary directory to PATH.

Symptom: connection refused on port 5432. Cause: the server is not running, is using a different port, or is listening only on a socket. Diagnose: run pg_isready, check the service status, and inspect postgresql.conf for port and listen_addresses. Correct: start the service or connect with the actual host, socket directory, and port.

Symptom: FATAL: database "name" does not exist. Cause: omitted -d made psql default to a database named after the current user. Diagnose: run psql -l or connect to the maintenance database with psql -d postgres. Correct: create the intended database or pass -d explicitly.

Symptom: FATAL: role "name" does not exist or password authentication fails. Cause: the database role is missing, the wrong role was inferred, or pg_hba.conf selects a password method. Diagnose: connect as an administrative role and run \du; check the matching authentication rule. Correct: create the role, pass -U, set a password if needed, or adjust the local authentication rule narrowly.

Security, Performance, and Reliability

Do not use a superuser for routine work. Create roles with only the privileges needed for the database and schema they use. Keep the server bound to localhost for a local lab unless you deliberately need remote access. If remote access is required, combine narrow listen_addresses, restrictive pg_hba.conf CIDR ranges, password or certificate authentication, and network firewall rules.

Performance starts with connection discipline. Opening many short-lived psql sessions is fine for administration but is not an application architecture. Later lessons will cover pooling and query plans; for now, notice that every connection consumes server memory and appears in pg_stat_activity. Reliability starts with knowing where the data directory is and how it is backed up. A database created in a temporary container without a persistent volume is a temporary database.

Hands-On Lab

Prerequisites: a machine where you can install PostgreSQL packages or run a PostgreSQL container, permission to create a local database, and a shell with psql available. Keep the server local to your machine for this lab.

  1. Install PostgreSQL using your platform’s package manager or a local container image. Confirm the client with psql --version.
  2. Start the server and run pg_isready -h localhost -p 5432. Continue only when it reports that connections are accepted.
  3. Create a lab database with createdb course_lab. If that fails because the database exists, keep using it or drop and recreate it only if it contains no needed work.
  4. Connect with psql -d course_lab. Run \conninfo, SELECT current_database(), current_user;, and \l.
  5. Create the notes table from Example 3, insert one row, and run \d notes.
  6. Verify with SELECT count(*) FROM notes;. The expected result after one insert is 1.
  7. Test failure handling by running psql -d does_not_exist. Read the error and identify whether it is a database, role, server, or password problem.
  8. Cleanup with DROP TABLE notes; inside course_lab. If the database was created only for this lab, exit psql and run dropdb course_lab.

Assessment Exercises

  1. You run psql with no arguments and receive FATAL: database "alex" does not exist. Explain which default was applied and give two commands that would connect successfully if the postgres database exists.
  2. A teammate says psql is the database. Correct the statement by naming the server process, the data directory or cluster, and the client role of psql.
  3. Design a local lab setup for a class where students should not use superuser privileges. Which role and database would you create, and which privileges would you grant?
  4. A script runs three SQL files through psql, the first file fails, and the script still runs the next two. What option changes this behavior, and why is that important for migrations?
  5. You can connect through a Unix socket but not through localhost:5432. List the configuration values and diagnostics you would check before changing firewall rules.

Summary

Installing PostgreSQL means creating or using a server, an initialized cluster, configuration files, and client tools. psql is the primary client for learning because it exposes both SQL execution and database inspection through meta-commands. Be explicit about database, role, host, and port; read connection errors literally; and practice cleanup so labs stay repeatable. Those habits make later work with schemas, constraints, transactions, indexes, and administration much easier to reason about.