How to self-host Postgres in PostgreSQL

Why should I self-host Postgres?

Self-hosting removes SaaS fees, allows custom configuration, and fits tighter security policies. Teams can tune memory, CPU, and storage precisely to workload needs.

What hardware is required?

For small ecommerce setups, 2 vCPU, 4 GB RAM, and SSD storage handle <1 M rows. Scale vertically first, then add read replicas if analytical traffic grows.

How do I launch Postgres instantly with Docker?

Use the official image. One command pulls, configures, and starts Postgres in seconds.

docker run -d --name pg14 \
-e POSTGRES_USER=ecom_admin \
-e POSTGRES_PASSWORD=secret123 \
-e POSTGRES_DB=ecommerce \
-v $PWD/pgdata:/var/lib/postgresql/data \
-p 5432:5432 postgres:14


How do I initialise the schema?

Connect with psql or any SQL editor and create tables.

psql postgresql://ecom_admin:secret123@localhost:5432/ecommerce -f schema.sql

schema.sql

CREATE TABLE Customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE Orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES Customers(id),
order_date DATE NOT NULL,
total_amount NUMERIC(10,2)
);
CREATE TABLE Products (
id SERIAL PRIMARY KEY,
name TEXT,
price NUMERIC(10,2),
stock INT
);
CREATE TABLE OrderItems (
id SERIAL PRIMARY KEY,
order_id INT REFERENCES Orders(id),
product_id INT REFERENCES Products(id),
quantity INT
);


How do I expose Postgres securely?

Bind to localhost or a private subnet, use SSL, rotate passwords, and add fail2ban. For Kubernetes, expose via ClusterIP and use Secrets for credentials.

Which backups keep me safe?

Enable WAL archiving and run nightly pg_dump. Store dumps in object storage (e.g., S3). Test restores monthly with pg_restore.

What monitoring should I enable?

Use pg_stat_statements, Prometheus exporters, and alerts on replication lag, disk usage, and query duration.

How do I upgrade versions?

Use pg_upgrade for major releases or spin up a new container, replicate data with logical replication, then switch connections.