Master PostgreSQL: Installation, Configuration, and Performance Tuning Guide

Overview

PostgreSQL is a powerful open‑source object‑relational database that supports advanced SQL standards, extensions, transaction integrity, and high concurrency. This guide covers installation, basic configuration, daily usage, advanced features, performance tuning, and common troubleshooting for both development and production environments.

1. Installation

1.1 Linux (Ubuntu/Debian)

sudo apt update
sudo apt install postgresql postgresql-contrib

Start and enable the service:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Connect as the superuser:

sudo -u postgres psql

1.2 Windows

Download the appropriate installer from the PostgreSQL website.

Run the installation wizard.

Set the superuser (postgres) password.

Choose the port (default 5432) and locale.

After installation, connect with pgAdmin or the command line.

1.3 macOS (Homebrew)

brew install postgresql
brew services start postgresql

1.4 Docker (recommended for development)

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=123456 \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v /data/postgres:/var/lib/postgresql/data \
  postgres:16

2. Basic Configuration

2.1 Change password

ALTER USER postgres WITH PASSWORD 'new_password';

2.2 Create user and database

CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydb OWNER myuser;

2.3 Remote access

Edit postgresql.conf to enable remote connections: listen_addresses = '*'.

Edit pg_hba.conf to allow remote IPs, e.g. host all all 0.0.0.0/0 md5.

Restart the service: sudo systemctl restart postgresql.

3. Daily Usage

3.1 Common psql commands

\l

– list all databases \c dbname – connect to a database \dt – list tables \d tablename – show table schema \q – quit psql

3.2 Data manipulation examples

-- Create table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  age INT
);

-- Insert data
INSERT INTO users (name, age) VALUES ('Tom', 25);

-- Query data
SELECT * FROM users WHERE age > 20;

-- Update data
UPDATE users SET age = 30 WHERE name = 'Tom';

-- Delete data
DELETE FROM users WHERE id = 1;

3.3 Views and indexes

CREATE VIEW adult_users AS SELECT * FROM users WHERE age >= 18;
CREATE INDEX idx_users_age ON users(age);

3.4 Transactions

BEGIN;
UPDATE users SET age = age + 1 WHERE id = 1;
COMMIT; -- or ROLLBACK;

3.5 Backup and restore

# Export
pg_dump -U postgres -d mydb > backup.sql

# Restore
psql -U postgres -d mydb < backup.sql

4. Advanced Features

4.1 Tablespaces

CREATE TABLESPACE fastspace LOCATION '/mnt/ssd/pgdata';
CREATE DATABASE fastdb TABLESPACE fastspace;

4.2 Scheduled backups (Linux crontab)

0 2 * * * pg_dump -U postgres mydb > /backup/mydb_$(date +\%F).sql

4.3 High availability (overview)

Streaming Replication

Patroni / repmgr cluster management

Load balancing with PgBouncer or HAProxy

5. Performance Tuning Recommendations

shared_buffers : ~25% of system memory – caches data pages.

work_mem : 4 MB–64 MB – memory per query operation.

maintenance_work_mem : 64 MB–512 MB – memory for VACUUM and index builds.

max_connections : adjust according to concurrency needs.

effective_cache_size : 50%–75% of system memory – planner’s estimate of available cache.

Typical tuning commands:

EXPLAIN ANALYZE SELECT ...;
VACUUM FULL;
ANALYZE;

6. Common Issues and Solutions

6.1 Cannot connect

Check pg_hba.conf and postgresql.conf settings.

Ensure the firewall allows port 5432.

6.2 Performance degradation

Run VACUUM and ANALYZE regularly.

Inspect slow‑query logs.

6.3 Forgotten password

Temporarily set pg_hba.conf to trust, restart, then change the password.

Revert pg_hba.conf to md5 after resetting.