Your First Connection

Connect to PostgreSQL, verify your identity, exit gracefully

Imagine handing someone a set of keys with no label. Which door? Which lock? PostgreSQL is the same: before it accepts a single query, it needs to know who you are, where you're connecting from, and which database you want.\n\nIn this lab you'll present your credentials, verify the connection landed where you expected, and learn to read the psql prompt like a status bar — five minutes of setup that prevents hours of confusion later.

Why PostgreSQL Needs More Than Just "Open It"

Unlike a local SQLite file you double-click, PostgreSQL is a server process — always running, always listening, always checking who's allowed in.

Every connection carries three pieces of identity:

<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 620 80" width="620" role="img" aria-labelledby="connection-diagram-title">
  <title id="connection-diagram-title">The psql client connects to the PostgreSQL server using a host, role, and database, then accesses beer_db.</title>
  <g fill="#fff" stroke="#333" stroke-width="1.5">
    <rect x="5" y="35" width="120" height="36" />
    <rect x="325" y="35" width="155" height="36" />
    <rect x="525" y="35" width="90" height="36" />
  </g>
  <g fill="none" stroke="#333" stroke-width="1.5">
    <path d="M125 53 H315 M480 53 H515" />
  </g>
  <g fill="#333">
    <path d="M315 48 L325 53 L315 58 Z" />
    <path d="M515 48 L525 53 L515 58 Z" />
  </g>
  <g fill="#333" font-family="sans-serif" font-size="13" text-anchor="middle">
    <text x="225" y="25">host + role + database</text>
    <text x="65" y="58">psql client</text>
    <text x="402.5" y="58">PostgreSQL server</text>
    <text x="570" y="58">beer_db</text>
  </g>
</svg>

  HOST    — which machine runs the Postgres server
  USER    — which database role (account) you're authenticating as
  DATABASE — which of the server's databases you want to land in

Miss any one of these and psql either guesses (and guesses wrong) or refuses to connect.

The psql Connection Flags

  psql -h localhost -U student -d beer_db

  -h  host       → the server's hostname or IP (default: UNIX socket on localhost)
  -U  username   → the role you want to connect as
  -d  database   → which database to open (PGDATABASE overrides the role-name default)

Reading the Prompt

Once connected, the prompt tells you everything:

  postgres=#    ← you are a superuser (# = superuser privilege)
  mydb=>        ← you are a regular user (> = normal role)
  mydb=*#       ← you're inside an open transaction (the * means "uncommitted")

The database name, the = sign, and the prompt character (#, >, !) are a live status bar. You never need to guess where you are — the prompt tells you.

\conninfo: Your Session Receipt

After connecting, \conninfo prints a connection details for the active session:

  You are connected to database "mydb" as user "student"
  on host "localhost" (address "127.0.0.1") at port "5432".

It's the \conninfo answer to "wait, which database am I in?"

psql

The official PostgreSQL interactive terminal. Accepts SQL statements, returns results, and offers meta-commands (backslash commands like \dt, \l) for navigating the server without writing SQL.

Connection parameters

The four required coordinates for any PostgreSQL session: host, port (default 5432), username, and database name. If any are wrong or missing, the connection fails or lands in the wrong place.

🩺 Check the Server Is Running

Before connecting, confirm the PostgreSQL server is alive and listening. Run pg_isready -h localhost — it returns a one-line health check.

pg_isready -h localhost

localhost:5432 - accepting connections

🔌 Make the Connection

Connect to the pre-installed beer_db database as the student role. Your Linux account and PostgreSQL role are separate identities; this lab provides both. PGUSER=student is the default, so omitting -U also selects student. Use the essential flag: -d.

psql -h localhost -U student -d beer_db

psql (18.x) Type "help" for help. beer_db=>

🪪 Verify Your Session with \conninfo

Run \conninfo to get the details of your active connection. This is your receipt — confirm the database, user, host, and port are all correct.

\conninfo

🔍 Ask the Server Its Version

Run SELECT version(); — the first real SQL query of the course. It asks the server to introduce itself: which version of PostgreSQL is running and on which platform.

SELECT version();

version --------------------------------------------------------------------------------------------- PostgreSQL 18.4 on riscv64-alpine-linux-musl, compiled by cc (Alpine 15.2.0) 15.2.0, 64-bit (1 row)

👤 Confirm Your Identity Inside the Session

Run SELECT current_user; to see which database role the server sees you as. Confirm it says student. The prompt shows the database name and whether the role is a superuser; it does not show the role name.

SELECT current_user;

current_user ────────────── student (1 row)

🚪 Exit Cleanly

Type \q to exit psql gracefully. Unlike Ctrl-C (which aborts mid-query and can leave transactions open), \q closes the session properly.

\q

(returns to the shell prompt)

Lab complete! That connection card is now filled in:\n\n\n HOST : localhost\n USER : student\n DB : beer_db\n STATUS : 🟢 CONNECTED\n\n\nYou know how to connect, how to verify where you landed, and how to exit cleanly.

Enable JavaScript to run the live terminal and track your progress.