PostgreSQL Installation and Fundamentals

1. PostgreSQL Installation (macOS, Ubuntu, Windows)
2. Starting and Stopping the Service
3. Connecting via the psql CLI
4. User (Role) and Password Management
5. Databases, Tables, and Data Types
6. CRUD and Relational Structures
7. JOIN, INDEX, Backup & Restore
8. Visual Management with pgAdmin

What is PostgreSQL?

PostgreSQL is a relational database management system. It is open source, free, and provides high performance and data integrity even under heavy workloads. It stores data in tables, establishes relationships between them, and uses the SQL language.

In short, it is a tool used to store data in a structured way, query it, and define relationships between records. Most modern applications (web, mobile, API) rely on a database like this in the background.

Why PostgreSQL?

• Open source and free
• ACID compliant — low risk of data loss or inconsistency
• Supports advanced types like JSON, arrays, and geographic data
• Large community and rich documentation

PostgreSQL Installation

Below are installation steps for three operating systems. Apply the section that matches your system. After installation, PostgreSQL will run in the background as a service.

macOS Installation (Homebrew)

The easiest way on macOS is to use the Homebrew package manager. If you don't have it installed, get it from brew.sh.

brew update
brew install postgresql@16
brew services start postgresql@16

After installation, PostgreSQL runs as a background service. To verify the version:

postgres --version

Ubuntu / Debian Installation

On Ubuntu the safest path is to install the latest version from the official APT repository.

sudo apt update
sudo apt install -y postgresql postgresql-contrib

PostgreSQL starts automatically after installation. Check the service status with:

sudo systemctl status postgresql

Windows Installation

On Windows the easiest method is the official installer. Download the EDB installer from postgresql.org/download/windows and follow these steps:

1. Run the downloaded .exe file as administrator
2. Confirm the installation directory and the data directory
3. Set a password for the postgres user (this password is critical, write it down)
4. Use the default port 5432
5. After installation, also check the pgAdmin option

After installation you can connect via the SQL Shell (psql) application, or manage everything through the pgAdmin graphical interface.

Starting and Stopping the Service

PostgreSQL is a service; you can't connect to the database unless it is running. Service commands differ per operating system.

macOS (Homebrew)

brew services start postgresql@16
brew services stop postgresql@16
brew services restart postgresql@16

Ubuntu / Debian

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql
sudo systemctl enable postgresql

The last command makes the service start automatically on system boot.

Windows

On Windows, manage the service through Services.msc or via the command line:

net start postgresql-x64-16
net stop postgresql-x64-16

Connecting via the psql CLI

psql is the terminal tool used to interact with PostgreSQL. You can run SQL commands directly here and inspect the database.

First Connection

A superuser called postgres is created during installation. For the first connection:

sudo -u postgres psql
# or
psql -U postgres

When connected, the prompt changes to postgres=#. From now on you can type SQL commands.

Common Meta Commands

Inside psql, commands starting with \are "meta commands". They are not SQL; they are shortcuts specific to the psql tool.

• \l — list all databases
• \c database_name — switch to the given database
• \dt — list tables in the current database
• \d table_name — show the structure of a table
• \du — list all users (roles)
• \q — quit psql

User (Role) and Password Management

In PostgreSQL, "user" and "role" refer to the same thing; the system treats both as a role. In production it is good practice to create a separate user with limited privileges instead of using the postgres superuser.

Creating a New User

CREATE USER ahmet WITH PASSWORD 'strong_password';

This command creates a user named ahmet with the given password. By default the user has no write permission on any database.

Changing the Password

ALTER USER ahmet WITH PASSWORD 'new_password';

Granting Superuser Rights

For a user that requires full system-wide access (use carefully):

ALTER USER ahmet WITH SUPERUSER;

Deleting a User

DROP USER ahmet;

Creating and Dropping a Database

Creating a separate database for each application is the standard approach in PostgreSQL. It keeps data isolated and makes permission management easier.

Creating a Database

CREATE DATABASE blog_app;

To create one with a specific user as the owner:

CREATE DATABASE blog_app OWNER ahmet;

Switching to the Database

\c blog_app

When successful, the prompt becomes blog_app=#. From now on commands run against this database.

Dropping a Database

Warning: This action is irreversible; data is lost permanently. Always consider taking a backup first.

DROP DATABASE blog_app;

Creating Tables and Data Types

Tables are the structures where data is stored. Each column has a data type that defines what values are allowed in it.

Common Data Types

• SERIAL — auto-incrementing integer (typically used for IDs)
• INTEGER — whole number
• NUMERIC(10, 2) — 10-digit number with 2 decimals (great for prices)
• VARCHAR(255) — variable-length text
• TEXT — text without length limit
• BOOLEAN — true / false
• DATE — date only (2026-04-28)
• TIMESTAMP — date + time
• JSONB — JSON data (indexable)

Example Table: users

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT NOW()
);

• PRIMARY KEY — uniquely identifies a record
• UNIQUE — value can appear only once in this column
• NOT NULL — cannot be left empty
• DEFAULT — default is used when no value is provided

Dropping a Table

DROP TABLE users;

Inserting, Reading, Updating, and Deleting Data (CRUD)

CRUD stands for Create, Read, Update, Delete. It refers to the four basic operations you can perform on a database.

INSERT — Adding Data

INSERT INTO users (email, name)
VALUES ('ahmet@example.com', 'Ahmet Yilmaz');

To insert multiple records at once:

INSERT INTO users (email, name) VALUES
  ('ali@example.com', 'Ali'),
  ('ayse@example.com', 'Ayse'),
  ('mehmet@example.com', 'Mehmet');

SELECT — Reading Data

SELECT * FROM users;

To fetch only specific columns:

SELECT id, email FROM users;

UPDATE — Changing Data

Warning: Without WHERE, all rows are updated. This is rarely what you want.

UPDATE users
SET name = 'Ahmet Demir'
WHERE id = 1;

DELETE — Removing Data

Without WHERE, all rows are deleted.

DELETE FROM users WHERE id = 1;

Filtering and Sorting

You usually want to see only certain records out of thousands. WHERE, ORDER BY, LIMIT, and OFFSET are used for this.

WHERE — Conditional Filtering

SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM users WHERE created_at > '2026-01-01';

ORDER BY — Sorting

SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY name ASC;

LIMIT and OFFSET — Pagination

Commonly used in web apps to show 10 records per page and move to the next page.

SELECT * FROM users
ORDER BY id ASC
LIMIT 10 OFFSET 20;

This query returns 10 records starting from the 21st (i.e., the third page).

PRIMARY KEY and FOREIGN KEY

The power of relational databases comes from establishing relationships between tables. PRIMARY KEY uniquely identifies a record, while FOREIGN KEY references the primary key of another table.

Example: users + posts

Every post has an author (user). That means the user_id column in the posts table is bound to the id column of the users table.

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  body TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

• REFERENCES users(id) — this column is bound to the id column of the users table
• ON DELETE CASCADE — when a user is deleted, all of their posts are automatically deleted as well

JOIN Operations

A JOIN combines two or more tables on a common column and returns a single result set. The most common types are INNER JOIN, LEFT JOIN, and RIGHT JOIN.

INNER JOIN — Records Present in Both Tables

Only rows that have a match in both tables are returned.

SELECT users.name, posts.title
FROM users
INNER JOIN posts ON posts.user_id = users.id;

LEFT JOIN — All Records From the Left Table

All users are returned; the post columns are NULL for users that have no posts.

SELECT users.name, posts.title
FROM users
LEFT JOIN posts ON posts.user_id = users.id;

RIGHT JOIN — All Records From the Right Table

SELECT users.name, posts.title
FROM users
RIGHT JOIN posts ON posts.user_id = users.id;

FULL OUTER JOIN — All Records

All records from both tables are returned; non-matching ones get NULL on the other side.

SELECT users.name, posts.title
FROM users
FULL OUTER JOIN posts ON posts.user_id = users.id;

Indexes (INDEX)

An index is an extra data structure that allows fast lookup of values in a column. Adding an index to frequently searched columns makes SELECT queries much faster. However, every index slightly slows down INSERT and UPDATEand consumes disk space; that's why you should add them only where needed.

Creating an Index

CREATE INDEX idx_users_email ON users(email);

Composite Index

CREATE INDEX idx_posts_user_created
ON posts(user_id, created_at DESC);

Dropping an Index

DROP INDEX idx_users_email;

When to Add an Index

• Columns frequently used in WHERE filters
• Columns used in JOIN conditions (foreign keys)
• Columns used in ORDER BY

Backup and Restore

Taking regular backups of a production database is critical. PostgreSQL ships with the pg_dump and pg_restore tools for this purpose.

Taking a Backup (pg_dump)

pg_dump -U postgres blog_app > blog_app_backup.sql

The command above writes a full copy of the blog_app database as a SQL file to disk.

Restoring (psql)

First create the target database, then import the SQL file:

createdb -U postgres blog_app_new
psql -U postgres blog_app_new < blog_app_backup.sql

Custom Format Backup

The custom (binary) format is preferred for smaller file size and selective restore.

pg_dump -U postgres -F c blog_app > blog_app.dump
pg_restore -U postgres -d blog_app_new blog_app.dump

Visual Management with pgAdmin

pgAdmin is the official graphical interface tool for PostgreSQL. If you're not used to the terminal or you want to inspect tables visually, it's ideal.

Installation

• macOS: brew install --cask pgadmin4
• Ubuntu: follow the steps at pgadmin.org/download/pgadmin-4-apt
• Windows: bundled with the PostgreSQL installer

First Connection

1. Open pgAdmin and set a master password
2. Right-click on Servers in the left menu → Register → Server
3. Name: Local
4. Connection tab:
   • Host: localhost
   • Port: 5432
   • Username: postgres
   • Password: the password you set during installation
5. Click Save. You can now see your databases in the left panel and edit data by clicking on any table.

By this point you can install PostgreSQL, connect to it, create databases, build tables, and define relationships between them. The next step is practicing on a real application of your own.