Why PostgreSQL?

So you have got a project — maybe a web app, a personal dashboard, or a side hustle that is starting to get real. You need a database. You could use SQLite for simple stuff, but once you have multiple services writing data concurrently, or you need proper user management, backups, and reliability, it is time for a real database server.

PostgreSQL is one of the most respected relational database systems in the world. It powers everything from small personal projects to massive enterprise systems. It is open source, battle-tested, and has an incredible community. And the best part? You can have it running on your own server in about five minutes with Docker Compose.

In this tutorial, we will set up a production-ready PostgreSQL instance with Docker Compose, configure it properly, and connect to it from another container. No prior database experience required.

Prerequisites

• A Linux server (or any machine with Docker installed)
• Docker and Docker Compose installed
• Basic comfort with the command line
• About 10 minutes of your time

Not sure if Docker is installed? Run docker --version and docker compose version. If you get version numbers back, you are good. If not, check out Docker installation docs first.

Step 1: Create Your Project Directory

Let us start by creating a clean directory for our database setup. Keeping things organized from the start will save you headaches later.

mkdir -p ~/postgres-docker
cd ~/postgres-docker

Simple enough. Everything we do from here lives in this directory.

Step 2: Write the Docker Compose File

Create a file called docker-compose.yml with the following content:

version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    container_name: my_postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: supersecretpassword
      POSTGRES_DB: myapp
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myuser -d myapp"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres_data:
    driver: local

Let us break down what each part does, because understanding this file is more important than just copying it:

• image: postgres:16-alpine — We are using PostgreSQL 16 on Alpine Linux. Alpine images are tiny (about 50MB) and secure because they include almost nothing extra. Perfect for containers.
• restart: unless-stopped — If the container crashes or the server reboots, PostgreSQL comes back automatically. You only stay stopped if you explicitly stop it.
• POSTGRES_USER / POSTGRES_PASSWORD / POSTGRES_DB — These environment variables tell the container to create a user, set their password, and create an initial database on first startup. This is a Docker-specific convenience — it only works on the first run when the data volume is empty.
• ports: “5432:5432” — PostgreSQL listens on port 5432 by default. This maps the container port to your host machine so other applications can connect.
• volumes — The postgres_data volume is critical. Without it, your data disappears every time the container restarts. This named volume is managed by Docker and persists across container restarts and recreations. The second volume mounts an optional initialization script.
• healthcheck — This tells Docker how to verify PostgreSQL is actually ready to accept connections. Other services can depend on this check.

Step 3: (Optional) Add an Initialization Script

If you want to create additional tables, extensions, or seed data when the database is first created, create an init.sql file in the same directory:

-- init.sql
-- This runs automatically on first startup when the data volume is empty

CREATE TABLE IF NOT EXISTS notes (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Enable the pg_trgm extension for fuzzy text search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Insert some seed data
INSERT INTO notes (title, body) VALUES
    ('Hello, PostgreSQL!', 'This is my first note in a self-hosted database.'),
    ('Docker is great', 'Containers make database management so much easier.');

This script runs only once — the very first time the container starts with an empty data volume. If you already have data, it will not run again. That is by design.

Step 4: Start the Database

Now for the exciting part. Fire it up:

docker compose up -d

The -d flag runs it in detached mode, meaning it runs in the background. You should see something like:

[+] Running 2/2
 Network postgres-docker_default  Created
 Container my_postgres            Started

Let us verify it is actually running and healthy:

docker compose ps

You should see the container status as healthy. If it says starting, give it a few seconds and check again. If it has been more than 30 seconds, something is wrong — check the logs:

docker compose logs postgres

Step 5: Connect to Your Database

Let us connect and make sure everything works. We will use psql, PostgreSQL command-line client, from inside the container:

docker exec -it my_postgres psql -U myuser -d myapp

You should see the myapp=# prompt. Try a few commands:

-- List all tables
\dt

-- View the seed data
SELECT * FROM notes;

-- Check PostgreSQL version
SELECT version();

-- Quit
\q

If you see your notes table with the seed data, congratulations — you have a working PostgreSQL database!

Step 6: Connect from Another Container

In real setups, you will usually have other services that need to talk to your database. Here is how to add an app service to your docker-compose.yml that connects to PostgreSQL:

version: '3.8'

services:
  postgres:
    image: postgres:16-alpine
    container_name: my_postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: supersecretpassword
      POSTGRES_DB: myapp
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myuser -d myapp"]
      interval: 10s
      timeout: 5s
      retries: 5

  app:
    image: your-app-image:latest
    container_name: my_app
    depends_on:
      postgres:
        condition: service_healthy
    environment:
      DATABASE_URL: "postgresql://myuser:YOUR_PASSWORD@postgres:5432/myapp"

volumes:
  postgres_data:
    driver: local

Key things to notice:

• depends_on with condition: service_healthy — Your app will not start until PostgreSQL is actually ready. Without this, your app might try to connect before the database is initialized, causing crashes.
• Database URL uses @postgres — Not localhost! Inside Docker network, containers reach each other by service name. The postgres service is reachable at hostname postgres from any container in the same Compose project.
• Port 5432 is only exposed to the host — Containers talk to each other on Docker internal network. The ports mapping is only needed if you want to connect from outside Docker (like from your laptop).

Step 7: Backing Up Your Data

You have a database running. Great. Now let us make sure you do not lose your data. Create a simple backup script:

#!/bin/bash
# save as ~/postgres-docker/backup.sh

BACKUP_DIR="/home/noodly/postgres-backups"
TIMESTAMP=20260619_212854
BACKUP_FILE="/myapp_.sql"

mkdir -p ""

docker exec my_postgres pg_dump -U myuser -d myapp > ""

echo "Backup saved to "

# Keep only the last 7 backups
ls -t ""/myapp_*.sql | tail -n +8 | xargs rm -f

Make it executable and test it:

chmod +x backup.sh
./backup.sh

To automate this, add a cron job:

# Edit your crontab
crontab -e

# Add this line to back up daily at 3 AM
0 3 * * * /home/youruser/postgres-docker/backup.sh >> /var/log/pg_backup.log 2>&1

To restore from a backup:

docker exec -i my_postgres psql -U myuser -d myapp < /path/to/backup.sql

Common Pitfalls and Troubleshooting

“Connection refused” errors: Make sure the container is running (docker compose ps) and healthy. If you are connecting from another container, use the service name (postgres) as the hostname, not localhost.

Password authentication failed: Double-check your POSTGRES_PASSWORD matches what you are using to connect. If you changed the password in the Compose file after the first startup, it will not take effect — the password is set only on first initialization. You would need to either delete the volume (docker compose down -v — warning: this deletes all data) or change the password manually inside PostgreSQL.

Data disappears after docker compose down: You forgot the named volume! Without volumes: postgres_data in your Compose file, data lives inside the container and gets deleted when the container is removed. Always use a named volume or bind mount for data you care about.

Port 5432 already in use: You might already have PostgreSQL running on your host machine. Either stop the host service (sudo systemctl stop postgresql) or change the port mapping to something like "5433:5432".

Initialization script did not run: The docker-entrypoint-initdb.d scripts only run when the data directory is completely empty. If you have already started the container once, the volume has data and the script is skipped. Run docker compose down -v to wipe the volume and start fresh (again, this deletes data).

Security Considerations

This setup is great for getting started, but if you are exposing this to the internet or running in production, consider these hardening steps:

• Do not expose port 5432 to the public internet. Only map the port if you need external access. For container-to-container communication, the port mapping is not needed at all.
• Use a strong password. supersecretpassword is fine for local testing, but use a real password for anything serious. Consider using Docker secrets or a .env file (and add it to .gitignore).
• Keep the image updated. Run docker compose pull periodically to get security patches. The postgres:16-alpine tag will get you minor version updates (16.x), but you will need to manually bump to postgres:17-alpine for major versions.
• Limit resource usage. Add deploy.resources.limits to prevent PostgreSQL from consuming all your server memory.

What is Next?

Now that you have PostgreSQL running, here are some things you can do with it:

• Install pgAdmin or Adminer in another container for a web-based database management UI
• Set up automated backups to a remote server or cloud storage
• Connect your favorite web framework — Django, Rails, Laravel, Next.js — to your self-hosted database
• Explore PostgreSQL advanced features: full-text search, JSON columns, and logical replication

Databases can feel intimidating at first, but once you have one running, you will wonder how you ever lived without it. PostgreSQL is one of those tools that quietly does its job, year after year, without complaining. It is the boring infrastructure that makes everything else possible.

And honestly? That is the best kind of infrastructure.