Postgresql docker image

POSTGIS and TimescaleDB (inc. toolkit for hyperfunctions) image built for amd64 and arm64 using timescale/timescaledb-ha base image with:

• OR specific ENV variables and a healthcheck added
• Easy configuration of max_connections using POSTGRES_MAX_CONNECTIONS environment variable (set to -1 to disable this setting)
• PGDATA path set to match old Alpine image (for ease of DB migration)
• POSTGRES user UID and GID changed to match old Alpine image (for ease of DB migration)
• Auto upgrade of database with PG major version changes from previous PG major version; can be disabled using OR_DISABLE_AUTO_UPGRADE=true
• Auto upgrade of timescaleDB extension when a new version is available in the container; can be disabled using OR_DISABLE_AUTO_UPGRADE=true
• OR_DISABLE_REINDEX env variable with associated scripts to determine if a REINDEX of the entire DB should be carried out at first startup with existing DB (checks whether or not $PGDATA/OR_REINDEX_COUNTER.$OR_REINDEX_COUNTER exists). This is used when a collation change has occurred (glibc version change, muslc <-> glibc) which can break the indexes; migration can either be manually handled or auto handled depending on OR_DISABLE_REINDEX env variable value. NOTE THAT A REINDEX CAN TAKE A LONG TIME DEPENDING ON THE SIZE OF THE DB! And startup will be delayed until completed. This functionality is intended to simplify migration for basic users; advanced users with large DBs should take care of this themselves.
• Slimmed images using slim toolkit to reduce image size by ~60%

Local Development

Prerequisites

• Docker must be installed and running
• slim toolkit must be installed for image optimization

Install slim toolkit via the install script:

curl -sL https://raw.githubusercontent.com/slimtoolkit/slim/master/scripts/install-slim.sh | sudo -E bash -

Or via Homebrew (macOS):

brew install docker-slim

For more installation options, see the slim toolkit documentation.

Building the Image

1. Build the Docker image (replace 17 with desired PostgreSQL major version):

docker build --build-arg PG_MAJOR=17 -t openremote/postgresql:pg17 .

2. Slim the image using the provided script:

# Usage: ./slim-image.sh <source-image> <target-image> <architecture>
# Architecture: amd64 or arm64 (auto-detected if omitted)

./slim-image.sh openremote/postgresql:pg17 openremote/postgresql:pg17-slim

3. Optionally replace the original with the slimmed version:

docker tag openremote/postgresql:pg17-slim openremote/postgresql:pg17

Upgrading

NOTE: If you change the version of container you use then make sure you have backed up your DB first as this container will try to auto upgrade your DB and/or TimescaleDB extension; this auto upgrade functionality can be disabled using OR_DISABLE_AUTO_UPGRADE=true

Automatic Upgrade

This image supports automatic upgrades from the previous PostgreSQL major version. When the container starts with an existing database from a supported older version, it will:

1. Upgrade TimescaleDB extensions on the old PostgreSQL version
2. Run pg_upgrade to migrate the database to the new PostgreSQL version
3. Upgrade TimescaleDB extensions on the new PostgreSQL version

Manual Upgrade

If automatic upgrade is not supported for your database version (e.g., skipping multiple major versions), you will need to perform a manual upgrade. Follow these steps:

1. Backup your database using pg_dump or pg_dumpall
2. Upgrade TimescaleDB first (if installed) - this must be done before PostgreSQL upgrade
3. Use pg_upgrade to migrate between PostgreSQL versions, or restore from backup to a fresh database

Useful Resources

• PostgreSQL pg_upgrade documentation
• TimescaleDB upgrade guide
• TimescaleDB major upgrade guide

Example: Manual pg_dump/restore

# On the old container, dump the database
docker exec -it <old_container> pg_dumpall -U postgres > backup.sql

# Start the new container with a fresh data directory
docker run -d --name new_postgres -v /path/to/new/data:/var/lib/postgresql/data openremote/postgresql:latest

# Restore the backup
docker exec -i new_postgres psql -U postgres < backup.sql