KAHIBARO
Discord Login Register ☰
☰

11.2 Installing PostgreSQL

Table of Contents

Why Installation Matters

Before you can use PostgreSQL from your backend code, you need a running PostgreSQL server and the basic tools to connect to it. In this chapter you will:

You do not need to be a system administration expert. You only need enough to get a local development database running.

Goal: By the end of this chapter you should be able to run psql, connect to a local PostgreSQL instance, and create a simple test database.

Installing PostgreSQL on Linux

On Linux, PostgreSQL is usually installed through your distribution package manager. The commands differ slightly between distributions, but the ideas are the same.

Ubuntu and Debian

On Ubuntu and Debian you can install PostgreSQL from the official PostgreSQL repository, which often has more recent versions than the default system repository.

Step 1: Add PostgreSQL repository

Open a terminal and run:

bash 
sudo apt update
sudo apt install curl ca-certificates gnupg

Add the PostgreSQL signing key and repository:

bash 
# Import the repository key
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
  | sudo gpg --dearmor -o /usr/share/keyrings/postgresql.gpg
# Add the repository (for Ubuntu 22.04 as an example)
echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] \
  http://apt.postgresql.org/pub/repos/apt jammy-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list

Change jammy to focal, bullseye, etc, depending on your distribution. You can find the correct codename on the PostgreSQL downloads page.

Step 2: Install PostgreSQL

Update and install:

bash 
sudo apt update
sudo apt install postgresql postgresql-contrib

Step 3: Check service status

PostgreSQL should start automatically. Check:

bash 
sudo systemctl status postgresql

You should see something like:

text 
Active: active (exited) or active (running)

If it is not running, start it:

bash 
sudo systemctl start postgresql

And enable on boot:

bash 
sudo systemctl enable postgresql

Step 4: Switch to the `postgres` system user

On Linux, PostgreSQL creates a special operating system user named postgres that owns the server process.

bash 
sudo -i -u postgres

Your shell prompt should change to something like:

text 
postgres@your-machine:~$

From here you can run psql without a password:

bash 
psql

To exit psql type:

sql 
\q

Then exit the postgres user:

bash 
exit

Common package commands (Ubuntu/Debian)

TaskCommand
Start PostgreSQLsudo systemctl start postgresql
Stop PostgreSQLsudo systemctl stop postgresql
Restart PostgreSQLsudo systemctl restart postgresql
Statussudo systemctl status postgresql
Version (server)psql --version or postgres -V

Fedora, CentOS, and RHEL

On Fedora and related distributions you usually use dnf or yum.

For a typical Fedora installation:

bash 
sudo dnf install postgresql-server postgresql-contrib

For CentOS / RHEL, you might also need to initialize the database:

bash 
sudo /usr/bin/postgresql-setup --initdb

Then start and enable the service:

bash 
sudo systemctl start postgresql
sudo systemctl enable postgresql

Check status:

bash 
sudo systemctl status postgresql

Usage (switch user and run psql) is similar to Ubuntu:

bash 
sudo -i -u postgres
psql

Installing PostgreSQL on macOS

On macOS you have several options. For development, two popular ones are:

Option 1: Homebrew

Homebrew is a package manager for macOS. It is a good choice if you like working in the terminal.

Step 1: Install Homebrew (if needed)

If you do not have Homebrew yet, run in Terminal:

bash 
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

Follow the instructions and then ensure brew is in your PATH as explained by the installer.

Step 2: Install PostgreSQL

bash 
brew update
brew install postgresql

Homebrew may print extra instructions at the end, read them carefully. They often include helpful tips about managing the service.

Step 3: Start the PostgreSQL service

bash 
brew services start postgresql

Check that it is running:

bash 
brew services list

You should see postgresql with status started.

Step 4: Test `psql`

PostgreSQL client tools are now available in your shell.

bash 
psql --version

To connect to the default database with your macOS username:

bash 
psql postgres

If everything works, you will see a prompt similar to:

text 
postgres=#

Type \q to exit.

Option 2: PostgreSQL.app

PostgreSQL.app is a standalone app that includes PostgreSQL and common tools, convenient if you want a more graphical and self-contained installation.

  1. Go to https://postgresapp.com
  2. Download the latest version for your macOS
  3. Drag and drop PostgreSQL.app into your Applications folder
  4. Open PostgreSQL.app. It will initialize and start a PostgreSQL server

To use psql from the terminal, PostgreSQL.app offers a menu item:

Installing PostgreSQL on Windows

On Windows, the easiest way is the official installer provided by EnterpriseDB.

Step 1: Download the installer

  1. Go to https://www.postgresql.org/download/windows/
  2. Click the link to installers provided by EnterpriseDB
  3. Download the latest stable version (for example, 16.x) for Windows

You will get an .exe installer file.

Step 2: Run the installer

Double click the installer and follow the wizard:

  1. Installation directory: choose a location, or accept the default, for example C:\Program Files\PostgreSQL\16\
  2. Components: select at least:
    • PostgreSQL Server
    • pgAdmin 4 (graphical admin tool)
    • Command Line Tools
  3. Data directory: accept the default or pick a location
  4. Password: set a strong password for the PostgreSQL superuser postgres. Write it down, you will need it later
  5. Port: default is 5432. Normally you should keep this
  6. Locale: choose your locale or accept default

Finish the installation. The installer will start the PostgreSQL service.

Step 3: Verify service status

On Windows, PostgreSQL runs as a Windows service.

If it is not running, right click the service and choose “Start”.

Step 4: Test connectivity with `psql`

You can use either:

Example using “SQL Shell (psql)”:

  1. Open the Start menu
  2. Search for “SQL Shell (psql)” and open it
  3. It will ask:
text 
   Server [localhost]:
   Database [postgres]:
   Port [5432]:
   Username [postgres]:

Press Enter for Server, Database, Port, then type:

text 
   Username: postgres

Then type the password you set in the installer.

If login is successful, you will see the postgres=# prompt.

Type:

sql 
SELECT version();

Then:

sql 
\q

to exit.

Verifying Your Installation

After installation on any platform, you should check two things:

  1. The server is running
  2. You can connect with psql

Check the server status

Linux

bash 
sudo systemctl status postgresql

You want active (running) or similar.

macOS with Homebrew

bash 
brew services list

Look for postgresql started.

Windows

Use services.msc and check that the PostgreSQL service is running.

Check `psql` version

Run this in your terminal or command prompt:

bash 
psql --version

You should see output similar to:

text 
psql (PostgreSQL) 16.0

If your system says command not found or similar, then psql is not in your PATH or not installed. For:

Basic `psql` Usage

Once PostgreSQL is installed, psql is the main command line tool you will use to:

You do not need to memorize everything now. Just learn the basic commands you will use daily.

Connecting to PostgreSQL

The connection syntax is:

bash 
psql -h HOST -p PORT -U USERNAME -d DATABASE

If you omit some options, it will use defaults:

Examples

Linux (as postgres OS user):

bash 
sudo -i -u postgres
psql

macOS with your system user:

bash 
psql postgres

Windows:

bash 
psql -U postgres -d postgres -h localhost -p 5432

When prompted for a password, type it and press Enter. psql shows nothing as you type, this is normal.

Useful `psql` meta commands

psql commands start with a backslash. They are not SQL, they are client commands.

CommandDescription
\qExit psql
\lList databases
\c dbConnect to database db
\dtList tables in current database
\duList roles (users)
\d table_nameDescribe table structure
\?Help for meta commands

Example:

sql 
\l
\c postgres
\dt
\du

Creating a Test Database and User

You now have PostgreSQL running. For backend development, you typically do not use the default superuser from your application. Instead, you create:

Rule: Never use the postgres superuser from your application. Always create a separate, limited user for each application.

Step 1: Connect as an admin

On Linux, run:

bash 
sudo -i -u postgres
psql

On macOS / Windows, connect as postgres (superuser) using psql:

bash 
psql -U postgres -d postgres -h localhost

Enter the password if prompted.

You should see the postgres=# prompt.

Step 2: Create a database

Create a new database named myapp_dev:

sql 
CREATE DATABASE myapp_dev;

Check that it exists:

sql 
\l

You should see myapp_dev in the list.

Step 3: Create a user with password

Create a login role that your backend application will use.

Example user myapp_user with password myapp_password:

sql 
CREATE USER myapp_user WITH PASSWORD 'myapp_password';

For development, this is fine. For production, you should use a stronger password and secure storage, which you will cover in later chapters.

Step 4: Grant privileges on the database

Give the new user permission to work with the new database.

First, connect to the database:

sql 
\c myapp_dev

You will see:

text 
You are now connected to database "myapp_dev"

Then grant privileges:

sql 
GRANT ALL PRIVILEGES ON DATABASE myapp_dev TO myapp_user;

At this point:

Step 5: Test login as the new user

Exit psql:

sql 
\q

Connect again using myapp_user:

bash 
psql -U myapp_user -d myapp_dev -h localhost

Enter the password myapp_password.

Now create a simple table to confirm permissions:

sql 
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO test_table (name) VALUES ('hello'), ('postgres');
SELECT * FROM test_table;

You should see:

text 
 id |  name
----+---------
  1 | hello
  2 | postgres
(2 rows)

Then type \q to exit.

Typical Connection Details for Backend Apps

Your backend application will usually need the following fields to connect to PostgreSQL:

FieldTypical development value
Hostlocalhost
Port5432
Databasemyapp_dev
Usermyapp_user
Passwordmyapp_password

In many backend frameworks, you will configure a database URL like:

text 
postgresql://myapp_user:myapp_password@localhost:5432/myapp_dev

Later chapters will show how to use this with ORMs and FastAPI.

Common Installation Problems and Fixes

You might run into some typical problems. Here are simple checks.

Port already in use

If another PostgreSQL instance or service uses port 5432, you might see errors like:

text 
could not bind IPv4 socket: Address already in use

Solutions:

bash 
  sudo lsof -i :5432

Cannot connect: “connection refused”

Check that the service is running:

If it is stopped, start it.

Authentication failed

Errors like:

text 
psql: FATAL:  password authentication failed for user "myapp_user"

Possible issues:

You can reset the password as superuser:

sql 
ALTER USER myapp_user WITH PASSWORD 'new_password';

Then try again.

Summary

You now know how to:

This setup is enough for development work. In later chapters you will learn how to design schemas, use SQL, and connect PostgreSQL to your backend code using ORMs and connection URLs.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3177 / 24h
Impressum / Imprint
Datenschutz / Data Policy