Lesson 6 - Project: Installing PostgreSQL Locally

Welcome to Your Local Postgres Setup#

So far you have practiced against a Postgres database that was already running for you. In this hands-on project you will install PostgreSQL on your own machine and connect to it from Python. Having a local server means you can experiment freely — create databases, design tables, load data, and break things safely — without any external setup.

By the end of this project, you will be able to:

• Install PostgreSQL on Windows, macOS, or Linux
• Install the psycopg2 Python library
• Connect to your local Postgres server from Python
• Create a table to confirm everything works

This is a setup project, so the goal is simply to get a working local database. Let’s install it.

Step 1: Install PostgreSQL#

During installation you will be asked to choose a username and password. Because this server only runs locally, you do not need a highly secure combination — just pick something you will remember.

You may also be asked for a port number. Even though Postgres runs on the same machine, other applications talk to it through a port as if it were remote. The default port for PostgreSQL is 5432, and you should use it.

macOS#

1. Download Postgres.app from postgresapp.com, move it to your Applications folder, and double-click to launch. It runs in the background, and it must be running for Python to connect. By default it listens on port 5432.
2. Add this line to the end of your ~/.zshrc (or ~/.bash_profile on older systems) so the psql command is on your path:

export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin

Windows#

1. Download the latest Windows installer from the EnterpriseDB downloads page.
2. Double-click the installer and follow the wizard.
3. When asked for a port number, use 5432.

Linux#

1. Follow the directions for your distribution in the official PostgreSQL documentation.
2. If asked for a port number, use 5432.

Verify the installation#

Open your command line and type:

psql

If you land in the PostgreSQL shell (a prompt that usually ends in =#), your server is installed and running. Type \q to exit.

Step 2: Install psycopg2#

To connect from Python you need the psycopg2 library. Install it with either pip or conda:

pip install psycopg2

conda install psycopg2

If pip gives you trouble building the package, the prebuilt psycopg2-binary package is a convenient alternative for local development:

pip install psycopg2-binary

Step 3: Connect from Python#

Launch your Python shell or a notebook, import psycopg2, and run the following to connect and create a small test table. Use the username you chose during installation — the examples below use the default postgres user and database:

import psycopg2

conn = psycopg2.connect(dbname="postgres", user="postgres")
cursor = conn.cursor()
cursor.execute("CREATE TABLE notes(id integer PRIMARY KEY, body text, title text)")
conn.commit()
conn.close()

If this runs without raising an error, your setup is complete. (Remember from Lesson 1 to commit() so the table is actually saved.)

If your installation set a password for the postgres user, include it in the connection:

conn = psycopg2.connect(dbname="postgres", user="postgres", password="your_password")

Step 4: Confirm It Worked#

Reconnect and query the table you just created to be sure it persisted:

import psycopg2

conn = psycopg2.connect(dbname="postgres", user="postgres")
cur = conn.cursor()
cur.execute("SELECT * FROM notes;")
print(cur.fetchall())   # an empty table prints []
conn.close()

An empty list [] is exactly what you want here — it means the notes table exists and simply has no rows yet.

If something goes wrong#

Setup issues are common and almost always solvable:

• “could not connect to server” — make sure the Postgres server is actually running (on macOS, that Postgres.app is open; on Linux, that the postgresql service is started).
• “role does not exist” — the user in your connection string must match a real Postgres user; check the username you set during installation.
• “password authentication failed” — pass the correct password keyword to psycopg2.connect().

Search the exact error message online — Stack Overflow has answers to nearly every Postgres setup problem.

Summary#

You installed PostgreSQL and psycopg2, connected to your local server from Python, and created a table to confirm the whole stack works. You now own a real database you can practice on for the rest of this course and beyond.

Key Concepts#

• Local Postgres server — a full PostgreSQL installation on your own machine, reachable on port 5432 by default.
• psycopg2 — the Python library that connects to your local server, installed with pip or conda.
• Connection test — creating and querying a small table is the quickest way to confirm your setup is correct.

Why This Matters#

Every data engineer needs a sandbox where they can install, configure, and experiment without fear of breaking production. A local Postgres install is that sandbox. With it running, you can reproduce every technique from this module — typed tables, safe inserts, bulk loads, user management — on data of your own, which is the fastest way to make these skills permanent.

Next Steps#

Continue Building Your Skills#

With Postgres running locally, you are ready for the capstone. In the next lesson you will pull together everything from this module — database creation, typed tables, bulk loading, and user groups — to build a complete crime reports database from a real CSV file. Let’s build it.