Setting Up PostgreSQL

Table of Contents

1. What We Are Going to Set Up
2. Start PostgreSQL with Docker
3. Connect with psql
4. Your First Queries
5. Essential psql Meta-Commands
6. Create the shopdb Database
7. Stop, Start, and Keep Your Data
8. The shopdb Project Folder
9. Running SQL Files and Rebuilding
10. Optional: A Graphical Client
11. Common Problems
12. Try It Yourself

1. What We Are Going to Set Up

From chapter 1 you know that PostgreSQL is a server and that you talk to it with a client. In this chapter we set up both:

• The server: PostgreSQL 17, running inside a Docker container.
• The client: psql, the command-line tool that comes with Postgres. We run it inside the same container, so you install nothing else.
• A project folder called shopdb that holds your SQL files.

Why Docker? Installing Postgres directly on your computer works, but it spreads files and services around your system, and versions differ between Windows, macOS, and Linux. A container gives everyone the same Postgres 17, and removing it later leaves nothing behind. If you are new to Docker, Docker CLI Tutorial explains the ideas, but you only need the few commands below.

You need Docker Desktop (Windows or macOS) or Docker Engine (Linux) installed and running. Check it:

docker --version

You should see something like Docker version 27.x.x. Any recent version is fine.

2. Start PostgreSQL with Docker

Run this single command:

docker run --name shopdb-pg -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:17

Docker downloads the postgres:17 image the first time, which takes a minute. Let's read the command piece by piece:

The password postgres is fine for a learning database on your own computer. Never use something this simple for a database that other people can reach. We talk about proper accounts in chapter 12.

Check that it is running:

docker ps

Typical output (some columns omitted):

CONTAINER ID   IMAGE         STATUS         PORTS                    NAMES
3f2a9c1d7b44   postgres:17   Up 12 seconds  0.0.0.0:5432->5432/tcp   shopdb-pg

Up means the server is running. If the status is Exited, see Common Problems. You can also read the server's log:

docker logs shopdb-pg

The last lines should say database system is ready to accept connections.

3. Connect with psql

Now open a psql session inside the container:

docker exec -it shopdb-pg psql -U postgres

What this means: docker exec runs a command inside a running container; -it makes it interactive so you can type; psql -U postgres starts the client as user postgres. Because psql runs on the same machine as the server, it does not ask for the password here.

You will see a welcome message and a prompt:

psql (17.x)
Type "help" for help.

postgres=#

The prompt shows the name of the database you are connected to (postgres, a default database) and #, which means you are a superuser. You are now in a loop: type SQL or a command, press Enter, read the result.

4. Your First Queries

Type this and press Enter:

SELECT 1 + 1;

Output:

 ?column?
----------
        2
(1 row)

You asked a question with no table at all, and Postgres computed it. The column has no name, so it shows ?column?. Give it one with AS:

SELECT 1 + 1 AS answer;

 answer
--------
      2
(1 row)

Ask the server for the current time:

SELECT now();

Typical output (your time and zone will differ):

              now
-------------------------------
 2026-10-03 09:41:17.382914+00
(1 row)

And ask which version you are running:

SELECT version();

Typical output:

                                version
-----------------------------------------------------------------------
 PostgreSQL 17.x (Debian ...) on aarch64-unknown-linux-gnu, compiled by ...
(1 row)

Two habits to build right now:

1. End every SQL statement with ;. If you forget, psql thinks you are still typing and shows a continuation prompt postgres-#. Type ; and press Enter to finish.
2. SQL is not case sensitive for keywords. select 1+1; works too. We write keywords in uppercase for readability.

Text values go in single quotes:

SELECT 'Hello, database' AS greeting;

     greeting
-----------------
 Hello, database
(1 row)

Double quotes mean something else in SQL: they mark an identifier such as a column name. Mixing them up is a very common beginner error.

5. Essential psql Meta-Commands

Besides SQL, psql has its own commands that start with a backslash. They are not SQL, they are not sent to the server, and they do not end with a semicolon. These are the ones you will use all the time:

Try the first one:

postgres=# \l

Typical output (trimmed):

                              List of databases
   Name    |  Owner   | Encoding | ...
-----------+----------+----------+----
 postgres  | postgres | UTF8     | ...
 template0 | postgres | UTF8     | ...
 template1 | postgres | UTF8     | ...
(3 rows)

postgres is the default database. template0 and template1 are templates Postgres copies when you create a new database. Leave them alone.

\x helps when a row is too wide for your screen. With expanded output on, each row is shown as a vertical list:

postgres=# \x
Expanded display is on.
postgres=# SELECT 1 + 1 AS answer, now() AS time;
-[ RECORD 1 ]----------------------------
answer | 2
time   | 2026-10-03 09:44:02.118733+00

Type \x again to switch it off. And \timing is handy later in chapter 11:

postgres=# \timing
Timing is on.
postgres=# SELECT 1;
 ?column?
----------
        1
(1 row)

Time: 0.412 ms

Note that the \d family of commands (\dt, \d customers, and others such as \di for indexes) is how you look at your own schema. You will use \d after almost every change in the next chapters.

6. Create the shopdb Database

The default postgres database is for administration. Each project should have its own database. Create one with the first DDL statement of the series:

CREATE DATABASE shopdb;

Output:

CREATE DATABASE

Postgres replies with the name of the command it ran. Now connect to it:

postgres=# \c shopdb
You are now connected to database "shopdb" as user "postgres".
shopdb=#

Notice the prompt changed to shopdb=#. Every table you create from now on lives inside this database. Check that it is empty:

shopdb=# \dt
Did not find any relations.

In Postgres, "relation" is the general word for tables, views, and other table-like objects. No tables yet, which is what we expect.

Next time, you can jump straight into the database from your terminal:

docker exec -it shopdb-pg psql -U postgres -d shopdb

The -d shopdb option chooses the database. This is the command you will use at the start of each chapter. Leave psql with \q, or press Ctrl+D.

7. Stop, Start, and Keep Your Data

When you finish for the day, you do not need to keep the server running:

docker stop shopdb-pg

Next time, start it again with:

docker start shopdb-pg

Stopping and starting a container keeps its data. Everything you created in shopdb is still there.

But there is a catch. The data lives inside the container. If you remove the container with docker rm, the data is gone with it. That is fine for learning, and in fact we rely on it: you can wipe and rebuild at any time. If you would rather keep the data even when the container is replaced, store it in a volume, which is a folder managed by Docker outside the container. You must decide this when you first create the container. To use a volume, remove the old container and create it again with -v:

docker rm -f shopdb-pg
docker run --name shopdb-pg -e POSTGRES_PASSWORD=postgres -p 5432:5432 \
  -v shopdb-data:/var/lib/postgresql/data -d postgres:17

-v shopdb-data:/var/lib/postgresql/data means "mount a Docker volume named shopdb-data at the folder where Postgres keeps its files". The volume survives even if you delete the container. When you start a new container with the same -v, the data is back. To delete the volume too and start completely fresh:

docker rm -f shopdb-pg
docker volume rm shopdb-data

Here is a quick comparison of the commands:

For this series, either choice is fine, because we will keep all our work in SQL files that can rebuild everything.

8. The shopdb Project Folder

Here is the habit that separates beginners from professionals: do not only type SQL in the prompt. Save it in files. A statement typed at the prompt is gone when you close the session. A file in a folder can be run again, edited, shared, and put in Git.

Create a folder anywhere on your computer (not inside the container):

mkdir shopdb
cd shopdb
mkdir queries
touch schema.sql seed.sql

The structure:

shopdb/
  schema.sql      <- CREATE TABLE statements (chapter 3 and 6)
  seed.sql        <- INSERT statements with sample data (chapter 4 and 6)
  queries/        <- your own questions, one .sql file per topic

Each chapter tells you which file the code goes into:

• schema.sql holds the structure (DDL): the tables and rules.
• seed.sql holds the starting data (DML): rows to fill the tables.
• queries/ holds SELECT statements you want to keep.

If you use Git (see Git & GitHub for Beginners), run git init here and commit after each chapter. The shopdb folder becomes a real, versioned project.

Use any editor you like, such as VS Code. SQL files are plain text with the .sql extension.

9. Running SQL Files and Rebuilding

You can run a file in two ways.

From your own terminal, by feeding the file to psql inside the container. The -i flag (without -t) lets Docker read from standard input:

docker exec -i shopdb-pg psql -U postgres -d shopdb < schema.sql

The < is the shell's "read input from this file". Run it from inside the shopdb/ folder. This is the form we use throughout the series.

From inside psql, with \i, which needs a path that psql can see. Since psql runs inside the container, it cannot see files on your computer. So the first form is the practical one.

Let's make something to run. Put this in queries/hello.sql:

SELECT 'shopdb is ready' AS status, now() AS checked_at;

Run it:

docker exec -i shopdb-pg psql -U postgres -d shopdb < queries/hello.sql

Typical output:

     status      |          checked_at
-----------------+-------------------------------
 shopdb is ready | 2026-10-03 09:58:40.204118+00
(1 row)

Now think about rebuilding. During the series you will want to start over many times: after a mistake, or to see if your files really work. To wipe and recreate the database, run these two commands:

docker exec -i shopdb-pg psql -U postgres -c "DROP DATABASE IF EXISTS shopdb;"
docker exec -i shopdb-pg psql -U postgres -c "CREATE DATABASE shopdb;"

-c means "run this one SQL command and exit". Be careful with DROP DATABASE: it deletes everything in shopdb immediately and permanently, with no "are you sure?". It is safe here because the data is practice data and your files can recreate it.

Then rebuild with your files:

docker exec -i shopdb-pg psql -U postgres -d shopdb < schema.sql
docker exec -i shopdb-pg psql -U postgres -d shopdb < seed.sql

Two commands, and your whole database is back in a second. In later chapters we will use these four commands as the "rebuild recipe". You may want to save them in a small shell script called rebuild.sh inside shopdb/:

#!/bin/sh
PSQL="docker exec -i shopdb-pg psql -U postgres"
$PSQL -c "DROP DATABASE IF EXISTS shopdb;"
$PSQL -c "CREATE DATABASE shopdb;"
$PSQL -d shopdb < schema.sql
$PSQL -d shopdb < seed.sql

Run it with sh rebuild.sh. It uses the exact commands above, with the repeated part stored in a variable.

10. Optional: A Graphical Client

psql is all you need, and learning it pays off because it is on every server. But a graphical client can be nice for browsing tables. Two popular free options:

• DBeaver (free, all platforms): a general database tool with a table browser and a query editor.
• TablePlus (macOS, Windows, Linux): a clean, fast interface with a free tier.

To connect from a graphical client, you use the port we published with -p 5432:5432. The settings are:

This is also how your own applications, such as the Node.js script in chapter 12, will connect. From outside the container, the server is reachable at localhost:5432.

11. Common Problems

When something goes wrong, docker ps -a (shows stopped containers too) and docker logs shopdb-pg answer most questions.

12. Try It Yourself

1. Run the setup. Start the container, open psql, and run SELECT version();. What major version of PostgreSQL do you see?
2. Compute in SQL. Without any table, write one query that returns three columns: 7 * 6 as product, 10 / 4 as integer_division, and 10 / 4.0 as decimal_division. What does Postgres do with 10 / 4, and why do you think that is?
3. Explore with meta-commands. Use \l, then \c shopdb, then \dt. Then run \timing and SELECT now();. Notice the Time: line. Turn timing off again.
4. Quote it right. Run SELECT 'It''s ready';. What does doubling the single quote do? Then try SELECT "text"; and read the error message. What did Postgres think "text" was?
5. Build the folder. Create the shopdb/ folder with schema.sql, seed.sql, and queries/. Put your hello.sql query in queries/ and run it with the docker exec -i ... < file form.
6. Rebuild it. Write rebuild.sh, run it, and confirm with \l that shopdb exists. Then stop the container, start it again, and check that the database is still there.

Where to go next. You have a running PostgreSQL server, a working psql, an empty shopdb database, and a project folder that can rebuild it. In the next chapter, Tables, Data Types and Constraints, you will write the first CREATE TABLE statements and learn how rules keep bad data out.