Enhancement: Improve `/_mgmt/ping` endpoint to check if pgstac is ready

[emmanuelmathot]

Description

Currently, the /_mgmt/ping endpoint in the STAC FastAPI pgstac implementation always returns a positive message ({"message": "PONG"}) without actually checking if all services are available, especially the PgSTAC database.

Problem

This creates a misleading health/readiness check, as the API might respond positively even when the underlying database connection is not functional. In production environments, this can lead to incorrect assumptions about service availability.

Proposed Solution

Enhance the /_mgmt/ping endpoint to perform an actual health check against the PostgreSQL database with PgSTAC before responding positively. The endpoint should:

1. Attempt to establish a connection to the database
2. Execute a simple query to verify PgSTAC tables/functions are accessible
3. Return a success message only if both checks pass
4. Return an appropriate error status code and message if any check fails

Benefits

• More accurate health/readiness checks in containerized/cloud environments
• Better integration with container orchestration systems like Kubernetes
• Faster detection of database connectivity issues

[vincentsarago]

I've opened a PR in stac-utils/stac-fastapi#825

The idea is to add a new /_mgmt/health endpoint which use a user provided health_check method.

def health_check(request: Request):
    return {
        "status": "UP",
        "database": {
            "status": "UP",
            "version": "0.1.0",
        },
    }

app = StacApi(
    ...
    health_check=health_check,
)

Once merged I'll make a new stac-fastapi release (5.2.0), we could update stac-fastapi-pgstac and create a custom health_check method

[vincentsarago]

I've just release stac-fastapi 5.2.0 with ☝

https://github.com/stac-utils/stac-fastapi/blob/main/CHANGES.md#520---2025-04-18