Aggressive postgres instantiation ctx timeout causes intermittent failures in Datastore initialisation

[anoopsundial]

What platforms are affected?

linux

What architectures are affected?

others

What SpiceDB version are you using?

v1.31.0

Steps to Reproduce

1. Start Spice DB locally with a remote cloud postgres which can take more than 5 second to respond.
2. Before the initialisation steps can complete the ctx times out very frequently eventhough intermittently

Expected Result

The initialisation should be able to work with slow or remote postgres instances.
It should either be made configurable. Or increased to higher number like 20/30 . As this is the initialisation flow.

The exact line were this is set is below:

spicedb/internal/datastore/postgres/postgres.go

Line 227 in 4262bfd

initializationContext, cancelInit := context.WithTimeout(context.Background(), 5*time.Second)

Actual Result

The context times out very frequently . An example log below

2024-10-18 15:53:02 {"level":"trace","preshared-keys-count":1,"time":"2024-10-18T10:23:02Z","message":"using gRPC auth with preshared key(s)"} 2024-10-18 15:53:02 {"level":"trace","preshared-key-1-length":13,"time":"2024-10-18T10:23:02Z","message":"preshared key configured"} 2024-10-18 15:53:02 {"level":"info","time":"2024-10-18T10:23:02Z","message":"using postgres datastore engine"} 2024-10-18 15:53:02 {"level":"warn","details-url":"https://spicedb.dev/d/force-custom-plan","plan_cache_mode":"force_custom_plan","time":"2024-10-18T10:23:02Z","message":"defaulting value in Postgres DB URI"} 2024-10-18 15:53:07 {"level":"debug","pgx":{"args":[],"err":"timeout: context deadline exceeded","pid":23643,"sql":"SHOW track_commit_timestamp;","time":645.80925},"time":"2024-10-18T10:23:07Z","message":"Query"} 2024-10-18 15:53:07 {"level":"error","error":"failed to create datastore: timeout: context deadline exceeded","time":"2024-10-18T10:23:07Z","message":"terminated with errors"}

[anoopsundial]

Have added a PR to address the issue. Please do consider. If it needs to be fixed differently please do let me know.
#2095

[anoopsundial]

Thanks for reviewing and merging the fix.

Also the workaround of spicedb datastore repair for the pg_dump approach is something we missed when we tried that. Thanks for that information.