Postgres

This is how I installed Postgres, PgAdmin4, and metabase in docker. The metabase part is optional, but I have it because it enables nice querying and reporting from the data in Postgres.

Install docker

See Docker install

Create docker-compose.yaml

Add the following to docker-compose.yaml

Should first do it without the metabase part. Create a user and database for metabase. Then add the metabase part.

 1version: '3.8'
 2services:
 3  db:
 4    image: postgres:16-alpine
 5    container_name: postgres_db
 6    restart: always
 7    environment:
 8      - POSTGRES_USER=${POSTGRES_USER}
 9      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
10    ports:
11      - ip_address:5432:5432
12      - 127.0.0.1:5432:5432
13    volumes:
14      - ./postgres-data:/var/lib/postgresql/data
15    networks:
16      - pg-network
17
18  pgadmin:
19    image: dpage/pgadmin4:latest
20    container_name: pgadmin4
21    restart: always
22    environment:
23      - PGADMIN_DEFAULT_EMAIL=${PGADMIN_EMAIL}
24      - PGADMIN_DEFAULT_PASSWORD=${PGADMIN_PASSWORD}
25    ports:
26        - ip_address:8080:80
27        - 127.0.0.1:8080:80
28    volumes:
29        - ./pgadmin-data:/var/lib/pgadmin
30    networks:
31      - pg-network
32
33  metabase:
34    image: metabase/metabase:latest
35    container_name: metabase
36    hostname: metabase
37    volumes:
38      - /dev/urandom:/dev/random:ro
39    ports:
40      - ip_address:3000:3000
41      - 127.0.0.1:3000:3000
42    environment:
43      - MB_DB_TYPE=postgres
44      - MB_DB_DBNAME=metabaseappdb
45      - MB_DB_PORT=5432
46      - MB_DB_USER=${MB_DB_USER}
47      - MB_DB_PASS=${MB_DB_PASSWORD}
48      - MB_DB_HOST=db
49    networks:
50      - pg-network
51    healthcheck:
52      test: curl --fail -I http://localhost:3000/api/health || exit 1
53      interval: 15s
54      timeout: 5s
55      retries: 5
56
57networks:
58  pg-network:

Notes:

The ports are bound explicitly to ip_address and 127.0.0.1. The former is the ip address given to the machine by tailscale. The latter is localhost. The ports are bound explicitly to these IP addresses so that the services can not be accessed from the general internet. (No binding to the IPv4 public IP).
Create folders pgadmin-data and postgres-data in the same folder where the docker-compose.yaml file is.
Run the command chown -R 5050:5050 pgadmin-data, else pgadmin4 will not work.
Create a file dot-env-template and the file .env and in those files, add the following (add the actual values to the .env file with no space around the = )

1POSTGRES_USER=
2POSTGRES_PASSWORD=
3PGADMIN_EMAIL=
4PGADMIN_PASSWORD=
5MB_DB_USER=
6MB_DB_PASSWORD=

Then docker compose up -d

Now, pgadmin4 can be accessed at http://ip_addr:8080 and metabase can be accessed at http://ip_addr:3000. For both, connecting to Postgres requires the hostname to be db (the name of the service in the docker-compose.yaml file).

Access psql command line client

docker exec -it pgcontainer psql -U sagar

docker exec -it pgcontainer /bin/sh and then in the prompt that appears

psql --username sagar