Installing PostgreSQL
1. Installing on Linux
| Distro | Command | Notes |
|---|
| Debian/Ubuntu | sudo apt install postgresql-17 postgresql-contrib-17 | Add PGDG repo for latest |
| RHEL/Fedora | sudo dnf install postgresql17-server postgresql17-contrib | Requires PGDG yum repo |
| Arch | sudo pacman -S postgresql | Latest stable in core |
| PGDG Repo | sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' | Official upstream packages |
2. Installing on macOS
| Method | Command |
|---|
| Homebrew | brew install postgresql@17 |
| Start service | brew services start postgresql@17 |
| Postgres.app | Download GUI installer from postgresapp.com |
| EDB installer | Graphical installer from enterprisedb.com |
3. Installing on Windows
| Step | Action |
|---|
| Download | EDB installer from enterprisedb.com/downloads |
| Components | Server, pgAdmin 4, Stack Builder, Command Line Tools |
| Data dir | Default: C:\Program Files\PostgreSQL\17\data |
| Port | Default 5432 |
| Locale | Match OS or set explicitly (e.g. en_US.UTF-8) |
| Service | Auto-registered as postgresql-x64-17 |
4. Verifying Installation
| Check | Command |
|---|
| Server version | postgres --version |
| Client version | psql --version |
| Service status | systemctl status postgresql |
| Connect test | sudo -u postgres psql -c "SELECT version();" |
5. Understanding PostgreSQL Components
| Component | Role |
|---|
| postgres | Server daemon (postmaster) |
| psql | Interactive terminal client |
| initdb | Creates a new database cluster |
| pg_ctl | Start/stop/restart server, init cluster |
| pg_dump / pg_restore | Logical backup/restore |
| pg_basebackup | Physical base backup for replication/PITR |
| pg_upgrade | In-place major version upgrade |
| pgAdmin | Web-based admin GUI |
| Package | Purpose |
|---|
| postgresql-server-dev-17 | Headers for building extensions |
| libpq-dev | Client library headers (libpq) |
| postgresql-contrib | Extra extensions (pg_stat_statements, etc.) |
| pgxnclient | Install community extensions |
7. Setting Up System User
sudo useradd -m -s /bin/bash postgres
sudo passwd postgres
sudo -iu postgres # become postgres user
psql # open psql as superuser
| Detail | Value |
|---|
| OS user | postgres (created by package) |
| DB superuser | postgres (peer-mapped) |
| Home dir | /var/lib/postgresql |
8. Configuring System Service
| Action | systemd Command |
|---|
| Enable on boot | sudo systemctl enable postgresql |
| Start | sudo systemctl start postgresql |
| Restart | sudo systemctl restart postgresql |
| Status | sudo systemctl status postgresql |
| Logs | journalctl -u postgresql -f |
9. Installing Contrib Modules
| Extension | Purpose |
|---|
| pg_stat_statements | Query performance tracking |
| pgcrypto | Cryptographic functions |
| uuid-ossp | UUID generation |
| hstore | Key/value column type |
| pg_trgm | Trigram fuzzy text search |
| postgres_fdw | Foreign data wrapper for remote PG |
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
10. Upgrading PostgreSQL Versions
| Tool | Use Case |
|---|
| pg_upgrade | Fast in-place major-version upgrade (hard links) |
| pg_dumpall + restore | Cross-platform / risky upgrades |
| Logical replication | Zero-downtime version upgrade |
| Minor versions | apt/dnf install + restart (no schema change) |
pg_upgrade \
-b /usr/lib/postgresql/16/bin \
-B /usr/lib/postgresql/17/bin \
-d /var/lib/postgresql/16/main \
-D /var/lib/postgresql/17/main \
--link --check # dry run first, drop --check to run
Warning: Always take a full backup before pg_upgrade --link; old cluster becomes unusable.