---
title: 'PostgreSQL: Setup, Performance and Management for IT Pros'
url: 'http://itlad.uk/blog/postgresql-setup-management-guide'
markdown: 'http://itlad.uk/blog/postgresql-setup-management-guide.md'
date: '2026-06-06'
description: 'PostgreSQL setup and management guide for IT professionals — installation, configuration tuning, pgAdmin, useful extensions and Docker development setup.'
taxonomy:
  category:
    - databases
  tag:
    - postgresql
    - databases
    - linux
    - open-source
    - performance
---

[ Home ](http://itlad.uk/)     [ Blog ](http://itlad.uk/blog)      PostgreSQL: Setup, Performance and Management for IT Pros    

# [PostgreSQL: Setup, Performance and Management for IT Pros](http://itlad.uk/blog/postgresql-setup-management-guide)

 By [IT Lad](http://itlad.uk/about) 6 Jun 2026 

  [postgresql](http://itlad.uk/blog/tag:postgresql#body-wrapper) [databases](http://itlad.uk/blog/tag:databases#body-wrapper) [linux](http://itlad.uk/blog/tag:linux#body-wrapper) [open-source](http://itlad.uk/blog/tag:open-source#body-wrapper) [performance](http://itlad.uk/blog/tag:performance#body-wrapper)  

PostgreSQL is the database I reach for when I have a choice. Free, ACID-compliant, extensible, runs everywhere, and has a track record that goes back to 1996. If you've only worked with SQL Server or MySQL, the switch takes a weekend to get comfortable with.

## Installation

**Ubuntu/Debian:**

```bash
sudo apt install postgresql postgresql-contrib
sudo systemctl enable --now postgresql
sudo -u postgres psql  # connect as superuser
```

**Docker (development):**

```bash
docker run -d --name postgres \
  -e POSTGRES_PASSWORD=yourpassword \
  -e POSTGRES_USER=admin \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16
```

## postgresql.conf Tuning

The default `postgresql.conf` is tuned conservatively to run on anything. Adjust these for a dedicated database server:

```ini
# Memory (for a server with 16GB RAM)
shared_buffers = 4GB                  # 25% of RAM
effective_cache_size = 12GB           # 75% of RAM
work_mem = 64MB                       # per sort/hash operation
maintenance_work_mem = 1GB            # for VACUUM, CREATE INDEX

# WAL / Write Performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# Parallelism
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Connection pooling (use PgBouncer instead of raising this)
max_connections = 100
```

Use [PgTune](https://pgtune.leopard.in.ua) to generate a baseline config for your hardware. It's faster than calculating values manually.

## Management Tools

**pgAdmin 4** — the official GUI. Runs in a browser, connects to multiple servers, query editor with execution plans. I already run it in Docker alongside my PostgreSQL container (you probably do too — see your `docker ps`).

**DBeaver** — cross-platform SQL client that handles PostgreSQL, MySQL, SQL Server, Oracle and 50 others from one tool. Better for writing complex queries than pgAdmin.

**psql** — the command-line client. Fast for quick checks and scripting. Learn enough of it to not need a GUI when you're SSH'd into a server.

```bash
# Useful psql one-liners
\l                      # list databases
\dt                     # list tables
\d tablename            # describe table structure
\timing                 # show query execution time
\copy t FROM 'file.csv' CSV HEADER   # bulk import CSV
```

## Extensions Worth Installing

```sql
-- Vector similarity search (AI/ML use cases)
CREATE EXTENSION vector;

-- Time-series data (Timescale)
CREATE EXTENSION timescaledb;

-- Geospatial data
CREATE EXTENSION postgis;

-- Full-text search improvements
CREATE EXTENSION pg_trgm;

-- Statistics on query plans
CREATE EXTENSION pg_stat_statements;
```

`pg_stat_statements` is the one to enable first — it tracks query performance over time, equivalent to SQL Server's `sys.dm_exec_query_stats`.

## Backup

```bash
# Single database backup
pg_dump -U admin mydb > mydb_backup.sql

# All databases
pg_dumpall -U postgres > all_databases.sql

# Binary format (faster restore for large DBs)
pg_dump -U admin -Fc mydb > mydb.dump
pg_restore -U admin -d mydb mydb.dump
```

Automate with a cron job and ship the output to your NAS. For continuous backup, `pgBackRest` handles incremental WAL archiving properly.

## PostgreSQL vs SQL Server

Both are production-grade. The practical differences:

|  | PostgreSQL | SQL Server |
|---|---|---|
| Cost | Free | Expensive at scale |
| Platform | Linux-first | Windows-first |
| Extensions | Rich ecosystem | Fewer open-source |
| Tooling | psql, pgAdmin | SSMS, Azure Data Studio |
| Windows Auth | No | Yes |
| JSON support | Excellent (JSONB) | Good |

For new Linux-based infrastructure, PostgreSQL. For anything tied to Active Directory, .NET or Windows auth, SQL Server. For everything else, PostgreSQL runs cheaper and has a broader open-source extension ecosystem.

 [ Previous Post](http://itlad.uk/blog/sql-server-setup-tuning-guide) [Next Post ](http://itlad.uk/blog/my-laptop-specs-daily-driver)

#### Syndicate

 [ Atom 1.0](http://itlad.uk/blog.atom) [ RSS](http://itlad.uk/blog.rss)

---

## Navigation

- Parent: [Blog](http://itlad.uk/blog.md)
- Previous: [My Daily Driver: i7-8750H Laptop Running 64GB, Dual 2TB NVMe and a GTX 1060](http://itlad.uk/blog/my-laptop-specs-daily-driver.md)
- Next: [SQL Server Setup and Tuning for IT Professionals](http://itlad.uk/blog/sql-server-setup-tuning-guide.md)