bluegreencity-ki

PostGIS Installation Guide

Administrator guide for deploying PostgreSQL and PostGIS with Docker on the project workstation.

Purpose

This guide documents how the database environment is set up so that it can be reproduced on a new workstation. It focuses on the infrastructure pattern and deployment sequence, not on project-specific secrets.

Prerequisites

Windows 11 or a comparable workstation operating system
Administrator access on the target machine
Docker Desktop with Docker Compose support
Optional remote-access layer such as Tailscale, WireGuard, or a managed VPN
A checked-out copy of the repository

1. Prepare the Host

If the workstation will be administered remotely, enable the required operating-system services first. For Windows, OpenSSH is sufficient for remote administration.

Add-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.0
Start-Service sshd
Set-Service -Name sshd -StartupType Automatic
Get-Service sshd

Install Docker Desktop and verify that both Docker and Docker Compose are available:

docker --version
docker compose version

2. Prepare the Project Configuration

Create a local environment file from the template and fill in the real values outside of version control.

Copy-Item .env.example .env

Recommended pattern for the local .env file:

POSTGRES_DB=<database-name>
POSTGRES_USER=<database-admin-user>
POSTGRES_PASSWORD=<strong-random-password>

PGADMIN_DEFAULT_EMAIL=<admin-email>
PGADMIN_DEFAULT_PASSWORD=<strong-random-password>

POSTGIS_HOST=<database-hostname>
POSTGIS_PORT=5432
POSTGIS_DB=<database-name>
POSTGIS_USER=<database-admin-user>
POSTGIS_PASSWORD=<strong-random-password>

DATABASE_URL=postgresql://<database-admin-user>:<url-encoded-password>@<database-hostname>:5432/<database-name>

Guidelines:

Generate secrets locally and distribute them out of band.
Never publish the populated .env file.
Prefer DNS names or VPN hostnames over hard-coded IP addresses in shared documentation.

3. Start the Containers

From the repository root:

docker compose up -d
docker compose ps

Expected result:

One PostgreSQL/PostGIS container is running.
One pgAdmin container is running if enabled in docker-compose.yml.

Optional log check:

docker compose logs -f postgis

4. Verify the Database

Run a direct check inside the container:

docker exec -it <postgis-container-name> psql -U <database-admin-user> -d <database-name> -c "SELECT PostGIS_Version();"

The result should return the installed PostGIS version.

5. Initialize the Schema

The repository initializes the database from SQL files under sql/init. Confirm that the expected schemas and core tables exist after the first startup.

docker exec -it <postgis-container-name> psql -U <database-admin-user> -d <database-name>

Then run:

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_catalog', 'information_schema');

6. Enable Controlled Remote Access

If the database must be reachable from other clients, expose it only through the intended network boundary.

Recommended pattern:

Use a VPN or private network.
Publish a stable hostname for the workstation.
Open only the required ports.
Keep pgAdmin behind the same boundary or disable it if not needed.

Example firewall rules on Windows:

New-NetFirewallRule -DisplayName "PostgreSQL Private Access" `
  -Direction Inbound `
  -LocalPort 5432 `
  -Protocol TCP `
  -Action Allow `
  -Profile Private

New-NetFirewallRule -DisplayName "pgAdmin Private Access" `
  -Direction Inbound `
  -LocalPort 5050 `
  -Protocol TCP `
  -Action Allow `
  -Profile Private

Verify from a client machine using the private hostname instead of a literal IP:

Test-NetConnection -ComputerName <database-hostname> -Port 5432
psql -h <database-hostname> -p 5432 -U <database-admin-user> -d <database-name>

7. Backup and Restore

Create a backup:

docker exec <postgis-container-name> pg_dump -U <database-admin-user> -d <database-name> -F c -f /tmp/backup.dump
docker cp <postgis-container-name>:/tmp/backup.dump ./backups/backup_$(Get-Date -Format 'yyyyMMdd').dump

Restore a backup:

docker cp ./backups/<backup-file>.dump <postgis-container-name>:/tmp/restore.dump
docker exec <postgis-container-name> pg_restore -U <database-admin-user> -d <database-name> -c /tmp/restore.dump

8. Operating Notes

Keep secrets in .env, a secret manager, or an operating-system key store.
Document hostnames, credentials, and user onboarding in internal-only material if required.
For day-to-day access, direct users to PostGIS_and_QGIS_User_Guide.md and DBeaver_Setup_Guide.md.