PostgreSQL Setup Concepts: Server, psql, and Databases

PostgreSQL is a client-server relational database system. A single running process, the PostgreSQL server (postgres), listens on a port (5432 by default) and manages one or more databases, handling every connection, query, and transaction. To work with it, you use a client — most commonly psql, the official interactive command-line client that ships with every PostgreSQL installation.

Before writing a single query, you need to understand the connection model: a client connects to a specific database on a specific server using a host, port, username, and database name. Everything you do afterward — creating tables, running queries, managing permissions — happens inside the context of that one database.

Why it matters

PostgreSQL is the most widely recommended open-source database for new projects, and psql is the tool you will have on every server, whatever graphical client you prefer. A handful of its backslash commands are enough to find your way around any database you are handed.

Connecting with psql

You typically connect with a command like psql -h localhost -p 5432 -U postgres -d mydb, or simply psql mydb if defaults are configured. Once connected, psql gives you an interactive prompt (mydb=#) where you can type SQL statements ending in a semicolon, or special "meta-commands" that start with a backslash and are handled by psql itself rather than sent to the server.

Essential Meta-Commands

Meta-commands are how you explore a database's structure quickly without writing SQL against system catalogs by hand.

• \l — list all databases on the server.
• \c dbname — connect to a different database.
• \dt — list all tables in the current schema.
• \d tablename — describe a table: columns, types, indexes, constraints, and foreign keys.
• \dn — list schemas in the current database.
• \du — list roles (users/groups) and their attributes.
• \q — quit psql.

Databases Are Isolated

A PostgreSQL server can host many databases, but a single connection can only query one database at a time — there is no cross-database JOIN like there is cross-schema. Every fresh install includes a default postgres database (used for administrative connections) and typically a template1 database that new databases are cloned from when you run CREATE DATABASE.

Example

-- From the terminal:
-- psql -U postgres

CREATE DATABASE bookstore;
\c bookstore

CREATE TABLE authors (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

\dt
\d authors

CREATE DATABASE
You are now connected to database "bookstore" as user "postgres".
CREATE TABLE

         List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | authors | table | postgres

                                     Table "public.authors"
 Column |  Type   | Collation | Nullable |              Default
--------+---------+-----------+----------+------------------------------------
 id     | integer |           | not null | nextval('authors_id_seq'::regclass)
 name   | text    |           | not null |
Indexes:
    "authors_pkey" PRIMARY KEY, btree (id)

Exercise

This exercise uses the psql client, so it needs a PostgreSQL installation and cannot run in the playground. Write the commands, in order, to: create a database named shop; connect to it; create a table products with an integer primary key id and a text name; list the tables; describe the products table; and quit.

Expected result: the list of tables shows products, and the description shows its two columns and the primary key index.

-- 1. create the database shop

-- 2. connect to it

-- 3. create the products table

-- 4. list the tables

-- 5. describe products

-- 6. quit

-- 1. create the database shop
CREATE DATABASE shop;

-- 2. connect to it
\c shop

-- 3. create the products table
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT
);

-- 4. list the tables
\dt

-- 5. describe products
\d products

-- 6. quit
\q

Quick quiz

Interview questions