PostgreSQL Installation & Management Basics

PostgreSQL is a free, open-source, ACID-compliant relational database. To install and manage it in 2026, add the official PostgreSQL Global Development Group (PGDG) repository so you get a current major version (PostgreSQL 16 or 17), install the server package, start and enable the service, then connect as the postgres superuser with sudo -u postgres psql on the default port 5432. From there you create roles and databases, control who can connect in pg_hba.conf, and keep the cluster healthy with backups, VACUUM/ANALYZE, and monitoring. This guide walks through each step on Ubuntu/Debian and RHEL/Alma/Rocky.

Key takeaways

• Install from the PGDG apt/yum repository, not the distro default, to get a current PostgreSQL 16 or 17 (PGDG also carries newer majors as they ship).
• A cluster (created by initdb) is one data directory served by a postgres process on port 5432; a database lives inside it.
• Connect locally as the OS postgres superuser with sudo -u postgres psql; learn the \l \c \dt \du \d \conninfo \q meta-commands.
• Access is controlled by pg_hba.conf — prefer scram-sha-256 (the modern default since PostgreSQL 14), not legacy md5.
• After editing pg_hba.conf or most parameters, reload (SELECT pg_reload_conf();); only a few settings (listen_addresses, shared_buffers) need a restart.
• Back up with pg_dump/pg_restore (logical) or pg_basebackup + WAL archiving (physical/PITR) — and always test the restore.
• Managed Postgres (Amazon RDS/Aurora, Cloud SQL, Supabase, Neon) removes most of this operational work.

Which PostgreSQL version should you install?

Most Linux distributions ship an older PostgreSQL by default. To run a current, fully supported release, add the PGDG repository maintained by the PostgreSQL project. PostgreSQL 16 and 17 are both excellent choices in 2026 — each major version receives bug and security fixes for five years after release. Pin a specific major (for example postgresql-17) so an unattended upgrade never jumps you across a major boundary by surprise.

Install PostgreSQL on Ubuntu and Debian (PGDG apt repo)

Add the signed PGDG apt repository for your release codename, then install the server, client, and libpq-dev (needed to build drivers like psycopg).

# Ubuntu/Debian: add the official PGDG apt repository
sudo apt update
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Register the repo for your distro codename (e.g. noble, jammy, bookworm)
. /etc/os-release
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list

# Install a specific major version (17 here) + client libraries
sudo apt update
sudo apt install -y postgresql-17 postgresql-client-17 libpq-dev

On Debian/Ubuntu the package automatically runs initdb, creates a main cluster, and starts the service. Confirm it is enabled (so it survives reboots) and running:

sudo systemctl enable --now postgresql
sudo systemctl status postgresql
psql --version   # confirm the installed version

Install PostgreSQL on RHEL, AlmaLinux, and Rocky (PGDG yum repo)

On the RHEL family, install the PGDG repo RPM, disable the built-in postgresql module so the PGDG packages win, then install and manually initialize the cluster — unlike Debian, the RPM does not auto-run initdb.

# RHEL/Alma/Rocky 9: install the PGDG repository RPM
sudo dnf install -y \
  https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the distro's built-in PostgreSQL module
sudo dnf -qy module disable postgresql

# Install server + contrib for PostgreSQL 17
sudo dnf install -y postgresql17-server postgresql17-contrib

# Initialize the cluster (required on RHEL family) and start it
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
sudo systemctl enable --now postgresql-17

Initialize the cluster and connect

A PostgreSQL cluster is a single data directory — the PGDATA, e.g. /var/lib/postgresql/17/main on Debian or /var/lib/pgsql/17/data on RHEL — managed by one server process listening on port 5432. initdb creates that directory (the package did it for you above). Every new cluster ships with one superuser role named postgres plus a matching OS user.

Connect locally as that superuser. No password is needed thanks to peer authentication for local OS users:

# Connect as the postgres superuser over the Unix socket (peer auth)
sudo -u postgres psql

# You are now at the interactive prompt:
#   postgres=#

# Set a password for the postgres role (needed for password logins later)
ALTER ROLE postgres WITH PASSWORD 'a-strong-password';

Essential psql meta-commands

psql is PostgreSQL's interactive terminal. Backslash meta-commands let you inspect and navigate the database without writing SQL:

Command	What it does
\l	List all databases
\c dbname	Connect to a database
\dt	List tables in the current schema
\d tablename	Describe a table (columns, indexes, constraints)
\du	List roles (users) and their attributes
\dn	List schemas
\conninfo	Show the current connection (user, db, host, port)
\x	Toggle expanded (vertical) row output
\timing	Toggle query execution timing
\?	Help for meta-commands
\h CREATE TABLE	SQL syntax help for a statement
\q	Quit psql

Tip: always end SQL statements with a semicolon — without it, psql keeps waiting for more input.

Create roles and databases (least privilege)

In PostgreSQL a role is both a user and a group; a role with the LOGIN attribute is effectively a user (CREATE USER is just shorthand for CREATE ROLE ... LOGIN). Never run application traffic as postgres. Instead create a dedicated role and a database it owns, granting only what each role needs:

-- Create a login role (user) with a password
CREATE ROLE app_user WITH LOGIN PASSWORD 'change-me-strong';

-- Create a database owned by that role
CREATE DATABASE app_db OWNER app_user;

-- Connect to the new database
\c app_db

-- Least privilege: allow the role to use and build in the public schema
GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE, CREATE ON SCHEMA public TO app_user;

-- A read-only role for reporting / BI tools
CREATE ROLE reporter WITH LOGIN PASSWORD 'change-me-too';
GRANT CONNECT ON DATABASE app_db TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporter;

Note: since PostgreSQL 15 the public schema no longer grants CREATE to everyone, which is why the explicit GRANT ... ON SCHEMA public lines above are required.

Configure authentication and remote access

Two files decide who can connect and how. Find their exact paths from psql with SHOW hba_file; and SHOW config_file; (Debian keeps them under /etc/postgresql/17/main/; RHEL keeps them in the data directory).

pg_hba.conf (host-based authentication) is read top-to-bottom and the first matching line wins. Use scram-sha-256 — the secure, salted password method and the default since PostgreSQL 14 — rather than legacy md5:

# /etc/postgresql/17/main/pg_hba.conf
# TYPE  DATABASE  USER      ADDRESS         METHOD

# Local OS-user connections over the Unix socket (peer maps OS user -> role)
local   all       postgres                  peer

# Password logins over the loopback interface
host    all       all       127.0.0.1/32    scram-sha-256
host    all       all       ::1/128         scram-sha-256

# Allow your app subnet to connect with a password (tighten the CIDR!)
host    app_db    app_user  10.0.0.0/24     scram-sha-256

To accept connections from other machines you must also tell the server to listen beyond localhost. Edit postgresql.conf:

# /etc/postgresql/17/main/postgresql.conf
listen_addresses = 'localhost'      # default; use '*' or specific IPs for remote
port = 5432
password_encryption = scram-sha-256

Reload vs. restart

Most changes — including every pg_hba.conf edit and runtime parameters like work_mem — take effect with a reload that keeps existing connections alive. Only a few parameters (those marked postmaster in the pg_settings view), such as listen_addresses, port, shared_buffers, and max_connections, require a full restart.

# Reload (no downtime) — picks up pg_hba.conf and most settings
sudo systemctl reload postgresql
#   ...or from inside psql:
#   SELECT pg_reload_conf();

# Restart (drops connections) — needed for listen_addresses, shared_buffers, etc.
sudo systemctl restart postgresql

Back up and restore

Choose a backup method based on how much data you have and how fast you must recover. PostgreSQL ships three native approaches:

Method	Type	Best for	Point-in-time recovery
pg_dump / pg_restore	Logical (SQL/archive)	Single databases, migrations, major-version upgrades	No
pg_basebackup + WAL archiving	Physical (file-level)	Whole-cluster backups, large data, PITR	Yes
Logical replication	Streaming changes	Near-zero-downtime upgrades, selective replication	Continuous

For most apps, schedule a nightly pg_dump in the custom format (compressed and parallel-restorable) and add WAL archiving once you need point-in-time recovery. For a multi-engine walkthrough, see our guide to backing up and restoring MySQL, PostgreSQL, and MongoDB.

# Logical backup of one database (custom format = compressed, selective restore)
pg_dump -U postgres -F c -f app_db.dump app_db

# Restore into a fresh database with 4 parallel jobs
createdb -U postgres app_db_restored
pg_restore -U postgres -d app_db_restored -j 4 app_db.dump

# Plain-SQL dump (human-readable; restore with psql)
pg_dump -U postgres app_db > app_db.sql
psql -U postgres -d app_db_restored -f app_db.sql

# Physical base backup of the whole cluster (the foundation for PITR)
pg_basebackup -U postgres -D /backups/base -F tar -z -P

Routine maintenance: VACUUM, ANALYZE, autovacuum

PostgreSQL uses MVCC, so every UPDATE/DELETE leaves behind dead row versions. VACUUM reclaims that space and ANALYZE refreshes the planner's statistics. The background autovacuum daemon (enabled by default) normally handles both — keep it on and tune its thresholds rather than disabling it. Run a manual vacuum after a large bulk load or before a maintenance window:

-- Refresh planner statistics for one table
ANALYZE orders;

-- Reclaim dead tuples and refresh stats (verbose output)
VACUUM (VERBOSE, ANALYZE) orders;

-- Heavy, locking reclaim that returns disk to the OS -- use sparingly
VACUUM FULL orders;

-- Confirm autovacuum is on and review per-table activity
SHOW autovacuum;
SELECT relname, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;

Basic performance tuning

The defaults are deliberately conservative. A handful of parameters deliver most of the win — set them in postgresql.conf, then benchmark against your real workload (tools like PGTune give sensible starting points):

• shared_buffers — PostgreSQL's own cache; ~25% of RAM is a common starting point (restart required).
• work_mem — memory per sort/hash operation; raise carefully because it is allocated per operation, per connection.
• effective_cache_size — planner hint for total OS + DB cache, often ~50-75% of RAM.
• maintenance_work_mem — speeds up VACUUM, index builds, and restores.
• Connections — PostgreSQL forks a process per connection, so cap max_connections and put a pooler like PgBouncer in front for high-concurrency apps.

If the bottleneck is your application's query patterns rather than the server, see optimizing Django database access and writing raw SQL queries in Django.

# postgresql.conf -- example starting points for a 16 GB server
shared_buffers = 4GB              # ~25% of RAM (restart required)
effective_cache_size = 12GB       # planner hint, ~75% of RAM
work_mem = 32MB                   # per sort/hash op -- keep modest
maintenance_work_mem = 1GB        # faster VACUUM / index builds
max_connections = 100            # pool with PgBouncer instead of raising this

Monitor a running server

The pg_stat_activity view shows every current session — what it is running, for how long, and whether it is waiting on a lock. Pair it with the pg_stat_statements extension for aggregate query statistics:

-- Current activity: longest-running, non-idle queries first
SELECT pid, usename, state, wait_event_type,
       now() - query_start AS runtime, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY runtime DESC;

-- Politely cancel a runaway query (pg_terminate_backend kills the session)
SELECT pg_cancel_backend(12345);

-- Aggregate query stats (add to shared_preload_libraries, then restart)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Self-managed vs. managed PostgreSQL

Running your own PostgreSQL gives full control and the lowest hosting overhead, but you own patching, backups, failover, and tuning. Managed services take that operational layer off your plate:

• Amazon RDS / Aurora PostgreSQL, Google Cloud SQL, Azure Database for PostgreSQL — managed instances with automated backups, minor-version patching, and high availability.
• Supabase and Neon — developer-focused Postgres platforms (Neon adds serverless, branchable storage).

Managed Postgres is usually the right call unless strict data residency, deep customization, or scale economics push you to self-host. When you need to move between engines or harden a self-managed cluster, MicroPyramid's database migration services cover schema moves, zero-downtime cutovers, and post-migration tuning.

Frequently Asked Questions

Which PostgreSQL version should I install in 2026?

Install a current major release — PostgreSQL 16 or 17 — from the official PGDG repository rather than your distribution's bundled default, which is often several versions behind. PGDG also carries the newest major as soon as it is released. Each major version receives bug and security fixes for five years, so 16 and 17 are both well supported past 2026. Only stay on an older major if a critical extension has not been ported yet.

Where is the PostgreSQL configuration file (postgresql.conf)?

Run SHOW config_file; and SHOW hba_file; inside psql to print the exact paths. On Debian/Ubuntu they live under /etc/postgresql/<version>/main/; on RHEL/Alma/Rocky they sit in the data directory, typically /var/lib/pgsql/<version>/data/. The server listens on TCP port 5432 by default.

What is the difference between a role and a user in PostgreSQL?

They are the same object. A role is PostgreSQL's unified concept for users and groups; a role with the LOGIN attribute can authenticate, which is what most people call a user. CREATE USER is simply shorthand for CREATE ROLE ... WITH LOGIN. Grant one role membership in another to model groups and shared permissions.

Should I use scram-sha-256 or md5 for passwords?

Use scram-sha-256. It is the secure, salted password method and the default since PostgreSQL 14, whereas md5 is legacy and weaker. Set password_encryption = scram-sha-256 in postgresql.conf, choose that method in pg_hba.conf, then have users reset their passwords so the stronger hashes are stored.

How do I let PostgreSQL accept remote connections?

Two steps. First, set listen_addresses in postgresql.conf to '*' (or specific IPs) and restart the server. Second, add a host line to pg_hba.conf for the client's IP range using scram-sha-256, then reload. Also open port 5432 in your firewall or security group, and prefer a private network or VPN over exposing PostgreSQL to the public internet.

Do I need to run VACUUM manually?

Usually not. The autovacuum daemon runs by default and handles dead-tuple cleanup and statistics. Keep it enabled and tune its thresholds for large or write-heavy tables. Run VACUUM (VERBOSE, ANALYZE) manually after big bulk loads, and reserve VACUUM FULL for rare, planned maintenance because it takes an exclusive lock and rewrites the entire table.