How to Install PostgreSQL and pgAdmin on a Linux Server (The Database Half Your Apps Want)

Tutorial 29 set up MariaDB. The other database you keep meeting in compose files — Immich, Gitea, Paperless, Umami, Vikunja, Joplin, Planka — is PostgreSQL. Each of those tutorials ran its own Postgres container, which is fine; but you may want one shared server for apps you install by hand, a web UI to look at the data, and a backup routine that doesn't involve six separate dumps. Here's Postgres as a first-class citizen.

One per stack (what the other tutorials did): each app's compose file has its own db: service. Simple, isolated, the app's docs match. Upgrade one without touching the others.

One shared server: a single Postgres on a shared Docker network (proxy-style, tutorial 101) that several apps connect to by name. Less RAM, one backup, one pgAdmin. Better for apps you install by hand or many small ones.

Both are fine. Don't share for Immich — it needs its own build with extensions.

mkdir -p ~/docker/postgres && cd ~/docker/postgres
nano compose.yaml

services:
db:
image: postgres:16
container_name: postgres
environment:
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
TZ: Europe/London
volumes:
- ./data:/var/lib/postgresql/data
- ./backups:/backups
ports:
- "127.0.0.1:5432:5432"
networks: [default, shared]
shm_size: 256mb
restart: unless-stopped

pgadmin:
image: dpage/pgadmin4:latest
container_name: pgadmin
environment:
PGADMIN_DEFAULT_EMAIL: john@example.com
PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD}
volumes:
- ./pgadmin:/var/lib/pgadmin
ports:
- "127.0.0.1:5050:80"
restart: unless-stopped

networks:
shared:
external: true

docker network create shared once; apps that join shared reach the database as postgres:5432. The host port on 127.0.0.1 is for psql from the server and SSH tunnels (62).

docker compose up -d

docker compose exec -u postgres db psql

CREATE USER vikunja WITH PASSWORD 'long-random';
CREATE DATABASE vikunja OWNER vikunja;
\l
\du
\q

In that app's compose file: host postgres, port 5432, those credentials, and networks: [default, shared]. Each app sees only its own database.

Tunnel to it (ssh -L 5050:127.0.0.1:5050 john@server) or proxy it behind Authelia (95). Log in, Add New Server: name, host postgres (or db), user postgres, the password. Browse tables, run SQL, see what Paperless or Immich actually store — and fix the odd row when an app's UI can't.

Logical dumps, never copied data files while running (tutorial 65). Nightly, into the mounted backups folder so restic picks them up:

docker compose exec -T -u postgres db pg_dumpall > /mnt/backup/postgres-$(date +%F).sql

Per-app, in the compressed custom format (faster restore, selective):

docker compose exec -T -u postgres db pg_dump -Fc vikunja > ./backups/vikunja.dump
docker compose exec -T -u postgres db pg_restore -d vikunja --clean ./backups/vikunja.dump

For the per-stack containers from other tutorials, the same pg_dump against each stack's db service.

Postgres ships tuned for a tiny machine. In the container's environment or postgresql.conf:

• shared_buffers = 1GB (a quarter of the RAM you'll give it) — the cache.
• effective_cache_size = 3GB — what the OS will cache on top.

And shm_size: 256mb in compose, or parallel queries fail with "could not resize shared memory". pgtune.leopard.in.ua generates the rest from your RAM and workload.

Postgres won't start a 16 data directory with the 17 image. Either stay on a major tag (postgres:16) and upgrade deliberately — pg_dumpall on 16, start 17 with an empty data folder, restore — or use the pgautoupgrade image that does it on start. Pin the tag (50).

"FATAL: password authentication failed". Wrong user/database pairing, or the app connects as postgres with the app's password. Check \du.

App can't resolve postgres. Not on the shared network, or you used localhost from inside a container (101).

"could not resize shared memory segment". shm_size.

Data folder permission errors after a restore. Must be owned by UID 999 (the postgres user in the image): sudo chown -R 999:999 data.

Per-stack Postgres for the apps that ship it; a shared one on a Docker network for everything else; a user and database per app; pgAdmin through a tunnel; pg_dumpall nightly; major versions pinned and upgraded on purpose. The database behind half your apps stops being a mystery box.