What you are connected to

The layers

Layer	What it is	In our setup
Server	The running PostgreSQL program	Started by brew services
Cluster	Everything one server manages, kept in one data directory	/opt/homebrew/var/postgresql@18
Database	A separate collection of schemas and tables	postgres, shop
Schema	A namespace inside a database	public
Table	Where rows live	customers, orders, …

Two rules follow from this structure:

• A connection is to one database. Connected to postgres, you cannot see shop’s tables at all; you must connect to shop. “Relation does not exist” often just means you are connected to the wrong database.
• Roles belong to the whole cluster. A user created once can be allowed into some databases and not others.

Inside a database, tables live in schemas. If you do not name one, PostgreSQL puts new tables in a schema called public, which every new database has. That is why \dt lists public | customers. For now, public is all you need.

Describing a connection

To connect, a client needs four things:

Setting	Meaning	Default
host	Which machine the server is on	Your own machine
port	Which network port it listens on	5432
user	Which role to connect as	Your operating-system username
dbname	Which database	Same as the user name

Application code usually packs these into one connection URI:

postgresql://app_user@db.example.com:5432/shop

That reads: connect as app_user to the server at db.example.com, port 5432, database shop. Every part is optional, and anything you leave out falls back to a default: postgresql://localhost/shop connects to shop on your own machine, as your own user, on port 5432.

A URI can also carry a password, but keep it out. Connection strings end up in logs, error messages, screenshots and chat. Give the password to the client separately, for example in a password file or a secret your platform injects at runtime.

Check yourself

1. You created customers while connected to shop. Connected to postgres, SELECT * FROM customers says the relation does not exist. Why?

   Show answer

   A connection only sees the one database it connected to. customers is in the shop database; you need to connect to shop.

   I could explain this without looking

2. What is the public schema?

   Show answer

   The schema every new database contains, and where PostgreSQL puts tables when you do not name a schema.

   I could explain this without looking

3. What does postgresql://localhost/shop connect to?

   Show answer

   The shop database on the server on your own machine, on the default port 5432, as your operating-system user.

   I could explain this without looking