How to connect to an existing external PostgreSQL instance?

[sreeramachandramurthy]

I have already a docker instance of postgres:16-alpine running. I want to use this instance instead of creating another instance of PostgreSQL bundled in the docker-compose.yml file.

Is there a guide or steps to follow?

I noticed we can update the environmental variable DATABASE_URL. But it does not mention if the database user needs a special role or owner permissions.

[azat44]

[sreeramachandramurthy]

I am getting the following error after making the suggested changes

docker logs healthlog

HealthLog: Waiting for database...
HealthLog: Database is reachable.
HealthLog: Running database migrations...
◇ injected env (0) from .env // tip: ◈ secrets for agents [www.dotenvx.com]
◇ injected env (0) from .env.local // tip: ⌘ multiple files { path: ['.env.local', '.env'] }
Loaded Prisma config from prisma.config.ts.

Prisma schema loaded from prisma/schema.prisma.
Datasource "db": PostgreSQL database "postgres", schema "public" at "10.0.0.83:5432"

308 migrations found in prisma/migrations

Error: ERROR: permission denied for schema public
   0: sql_schema_connector::sql_migration_persistence::initialize
           with namespaces=None filters=SchemaFilter { external_tables: [], external_enums: [] }
             at schema-engine/connectors/sql-schema-connector/src/sql_migration_persistence.rs:17
   1: schema_core::state::ApplyMigrations
             at schema-engine/core/src/state.rs:255

I tried granting permissions again using

-- Grant permissions on all existing tables and sequences
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO healthlog;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO healthlog;

-- Ensure future tables automatically grant permissions to this user
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO healthlog;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO healthlog;

Current Configuration

.env

# Database 
DATABASE_STATEMENT_TIMEOUT_MS="60000"
POSTGRES_USER="healthlog"
POSTGRES_PASSWORD="<redacted>"
POSTGRES_HOST="10.0.0.83"
POSTGRES_PORT="5432"

docker-compose.yml snippet

x-healthlog-env: &healthlog_env
  DATABASE_URL: "postgresql://${POSTGRES_USER:-healthlog}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT:-5432/healthlog?schema=public&connection_limit=${DB_CONNECTION_LIMIT:-20}&pool_timeout=${DB_POOL_TIMEOUT:-20}"

[MBombeck]

@sreeramachandramurthy thanks for asking this one; it turned out nobody had written it down. There is a page for it now: https://docs.healthlog.dev/self-hosting/external-postgres/

The short version: the role needs no superuser and no extensions. Make it the owner of the database and you are done. The app creates its tables in public plus a _prisma_migrations table, and the job queue creates its own pgboss schema; ownership covers all of that (on Postgres 15 and later the database owner also owns public). Migrations run on every container start, so there is no separate step to run.

CREATE ROLE healthlog LOGIN PASSWORD '...';
CREATE DATABASE healthlog OWNER healthlog;

On the compose side, put DATABASE_URL into a docker-compose.override.yml and reset depends_on; docker compose up -d app then starts the app alone and leaves the bundled db out. The page has the exact snippet, plus pool sizing (20 connections per container by default, 2 of them for the queue) and the sslmode values as the driver actually treats them, because require verifies the certificate fully. Your postgres:16-alpine is the same major the compose file bundles, so that part is fine.

If anything on that page does not match what you see, say so here and I will fix the page.

[sreeramachandramurthy]

Thank You @MBombeck

This worked perfectly.