Why Self-Host PostgreSQL?

PostgreSQL is one of the most powerful open-source relational databases in existence. Whether you’re building a web app, running a homelab, or just want a reliable local database for development, self-hosting Postgres gives you full control over your data — no cloud bills, no vendor lock-in, no surprising egress charges.

And when you pair it with pgAdmin, the most popular PostgreSQL management tool, you get a full graphical interface for managing your databases, running queries, inspecting schemas, and monitoring performance — all from your browser.

In this tutorial, we’ll set up both PostgreSQL 16 and pgAdmin 4 using Docker Compose, with persistent storage, proper networking, and a few production-ready touches.

Prerequisites

• A Linux server (Ubuntu 22.04+ or Debian 12+) — this guide works on any distro
• Docker and Docker Compose installed
• Basic comfort with the terminal
• Port 5432 (Postgres) and 5050 (pgAdmin) available

Not sure if Docker Compose is installed? Check with:

docker compose version

If you get a version number, you’re good. If not, install it:

sudo apt update && sudo apt install docker-compose-plugin

Project Structure

Let’s keep things tidy:

~/pg-docker/
├── docker-compose.yml
├── .env
├── postgres-data/    (created automatically)
└── pgadmin-data/     (created automatically)

Step 1: Create the Project Directory

mkdir -p ~/pg-docker && cd ~/pg-docker

Step 2: Create the Environment File

The .env file keeps your secrets out of the compose file. Create it:

cat <<'ENVEOF' > .env
POSTGRES_USER=myadmin\nPOSTGRES_PASSWORD=ChangeThisToSomethingSecure123!\nPOSTGRES_DB=myapp\[email protected]\nPGADMIN_DEFAULT_PASSWORD=ChangeThisToo456!\nPOSTGRES_DATA=./postgres-data\nPGADMIN_DATA=./pgadmin-data
ENVEOF

⚠️ Important: Replace those passwords with something actually strong. Use a password manager or run:

openssl rand -base64 24

Step 3: Create the Docker Compose File

cat <<'COMPOSEOF' > docker-compose.yml
services:\n  postgres:\n    image: postgres:16-alpine\n    container_name: postgres\n    restart: unless-stopped\n    environment:\n      POSTGRES_USER: ${POSTGRES_USER}\n      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}\n      POSTGRES_DB: ${POSTGRES_DB}\n    ports:\n      - "5432:5432"\n    volumes:\n      - ${POSTGRES_DATA:-./postgres-data}:/var/lib/postgresql/data\n    healthcheck:\n      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER}"]\n      interval: 10s\n      timeout: 5s\n      retries: 5\n    networks:\n      - pg-network\n\n  pgadmin:\n    image: dpage/pgadmin4:latest\n    container_name: pgadmin\n    restart: unless-stopped\n    environment:\n      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL}\n      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD}\n    ports:\n      - "5050:80"\n    volumes:\n      - ${PGADMIN_DATA:-./pgadmin-data}:/var/lib/pgadmin\n    depends_on:\n      postgres:\n        condition: service_healthy\n    networks:\n      - pg-network\n\nnetworks:\n  pg-network:\n    driver: bridge
COMPOSEOF

Let’s break down the key decisions in this compose file:

• postgres:16-alpine — The Alpine variant is smaller (~80MB vs ~400MB) and faster to pull, with no meaningful trade-offs for self-hosting.
• restart: unless-stopped — Your database comes back up after reboots and container crashes, but respects manual stops.
• healthcheck — pgAdmin waits for Postgres to be actually ready before starting, preventing connection errors on boot.
• depends_on with condition: service_healthy — This is the proper way to express dependency order in modern Compose.
• Named bridge network — Containers talk to each other by service name (postgres, pgadmin) instead of IP addresses.

Step 4: Fire It Up

docker compose up -d

You should see something like:

[+] Running 3/3
 ✔ Network pg-docker_pg-network  Created
 ✔ Container postgres            Healthy
 ✔ Container pgadmin             Started

It may take 15–30 seconds for the healthcheck to pass and pgAdmin to start. Be patient — the healthcheck is doing its job.

Step 5: Verify the Database Is Working

Option A: Test from the host

Install the PostgreSQL client if you don’t have it:

sudo apt install postgresql-client

Then connect:

PGPASSWORD='***' psql -h localhost -U myadmin -d myapp -c "SELECT version();"

Option B: Use pgAdmin

1. Open your browser to http://your-server-ip:5050
2. Log in with the email and password from your .env file
3. Click Add New Server
4. In the General tab, give it a name (e.g., “My Postgres”)
5. In the Connection tab:
   • Host name/address: postgres (yes, the service name — Docker’s internal DNS handles the rest)
   • Port: 5432
   • Database: myapp
   • Username: myadmin
   • Password: your POSTGRES_PASSWORD
6. Check Save password (pgAdmin stores it server-side)
7. Click Save

If all goes well, you’ll see your database structure in the sidebar. Congratulations — you’re running PostgreSQL with a GUI!

Step 6: Create a Test Table (Sanity Check)

Let’s make sure everything works end to end. In pgAdmin, right-click your database → Query Tool, then run:

CREATE TABLE visitors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    visited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO visitors (name) VALUES ('First Visitor');

SELECT * FROM visitors;

Run it with F5 or the play button. You should see one row with your visitor. If you do, everything is wired up correctly.

Common Pitfalls & Troubleshooting

“Role does not exist” when connecting

This usually means the .env variables weren’t loaded. Double-check:

cat .env | grep POSTGRES

Make sure there are no spaces around the = sign. POSTGRES_USER = myadmin (with spaces) will not work.

Permission denied on data directories

If you see permission errors in docker compose logs postgres, the container’s postgres user (UID 999) can’t write to the host directory. Fix it:

sudo chown -R 999:999 ./postgres-data
sudo chown -R 5050:5050 ./pgadmin-data

pgAdmin can’t connect to Postgres

Remember: inside Docker’s network, use the service name (postgres), not localhost. localhost inside the pgAdmin container refers to the pgAdmin container itself, not your host machine.

Port 5432 already in use

You might already have a local PostgreSQL instance running. Check with:

sudo ss -tlnp | grep 5432

Either stop the existing service or change the host port in the compose file (e.g., "5433:5432").

Data disappears after docker compose down

Using docker compose down -v removes volumes. Just docker compose down preserves them. If you accidentally nuked your data, you’ll need to recreate it — another reason to set up backups.

Bonus: Add Automated Backups

A database without backups is a disaster waiting to happen. Let’s add a simple backup service to our compose file:

  pg-backup:\n    image: prodrigestivill/postgres-backup-local\n    container_name: pg-backup\n    restart: unless-stopped\n    environment:\n      POSTGRES_HOST: postgres\n      POSTGRES_DB: ${POSTGRES_DB}\n      POSTGRES_USER: ${POSTGRES_USER}\n      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}\n      SCHEDULE: "@daily"\n      BACKUP_KEEP_DAYS: 7\n      BACKUP_KEEP_WEEKS: 4\n      BACKUP_KEEP_MONTHS: 3\n    volumes:\n      - ./backups:/backups\n    depends_on:\n      postgres:\n        condition: service_healthy\n    networks:\n      - pg-network

Add this under services: in your existing compose file, create the backups directory, then restart:

mkdir -p ~/pg-docker/backups
docker compose up -d

Your database will now be backed up daily, with 7 daily, 4 weekly, and 3 monthly backups retained. You can verify backups are working:

ls -la ~/pg-docker/backups/

Wrapping Up

You now have a fully self-hosted PostgreSQL instance with a web-based management interface and automated backups — all running in Docker containers that survive reboots. This setup is solid enough for small production workloads, development environments, and homelab projects.

The beauty of this approach is its portability. Zip up your ~/pg-docker directory, drop it on any server with Docker, run docker compose up -d, and you’re back in business.

Next steps you might consider:

• Add SSL/TLS to your PostgreSQL connection for encrypted client connections
• Put Nginx in front of pgAdmin with a free Let’s Encrypt certificate for HTTPS access
• Set up monitoring with Prometheus and Grafana (we covered that in a previous tutorial)
• Configure point-in-time recovery with WAL archiving for production-grade backup strategy

Happy self-hosting! 🐘