PostgreSQL Installation and Password Reset Guide

Install PostgreSQL and recover access safely on Ubuntu, Rocky Linux, and Windows.

Back to Guides · Home

1. Prerequisites

1. Administrator access on the host.
2. At least 1 GB RAM for small dev setups (more for production).
3. Open firewall only when remote access is truly needed.

2. Install PostgreSQL on Ubuntu / Debian

# Update package index
sudo apt update

# Install PostgreSQL
sudo apt install -y postgresql postgresql-contrib

# Check service
sudo systemctl status postgresql --no-pager

# Basic version check
psql --version

3. Install PostgreSQL on Rocky / AlmaLinux / CentOS (PGDG)

# Install PGDG repo (EL9 example)
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable built-in PostgreSQL module
sudo dnf -qy module disable postgresql

# Install PostgreSQL 16
sudo dnf install -y postgresql16-server postgresql16

# Initialize DB cluster
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

# Enable and start service
sudo systemctl enable --now postgresql-16
sudo systemctl status postgresql-16 --no-pager

4. Install PostgreSQL on Windows

1. Download PostgreSQL installer from EnterpriseDB.
2. Choose PostgreSQL version and set a strong password for postgres during setup.
3. Keep default port 5432 unless it conflicts with existing software.
4. Install pgAdmin if you want a GUI administration tool.

5. First Login and Basic Setup

# Linux: switch to postgres OS account
sudo -u postgres psql

# Inside psql: create application DB/user example
CREATE USER app_user WITH ENCRYPTED PASSWORD 'StrongPass!123';
CREATE DATABASE app_db OWNER app_user;
\q

6. Reset Forgotten postgres Password (Linux)

Most Linux setups let you reset by logging in as OS user postgres:

sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'NewStrongPassword!123';
\q

If local authentication rules block access, temporarily adjust pg_hba.conf:

# 1) Locate pg_hba.conf
sudo -u postgres psql -c "SHOW hba_file;"

# 2) Temporarily set local method to trust for postgres only
# Example line:
# local   all   postgres   trust

# 3) Reload config
sudo systemctl reload postgresql
# or for PGDG on EL:
sudo systemctl reload postgresql-16

# 4) Reset password, then revert trust back to scram-sha-256 or md5

7. Reset Forgotten Password on Windows

1. Open pg_hba.conf and temporarily change local auth method to trust.
2. Restart PostgreSQL service.
3. Connect with psql -U postgres, reset password, then restore pg_hba.conf to secure auth and restart again.

8. Common Troubleshooting