PostgreSQL extensions are installed in a random order

• registry.opensource.zalan.do/acid/postgres-operator:v1.8.2
• AWS EKS
• Production: no
• Feature request

There are certain extensions that have other extensions as dependencies, e.g. I want to install postgis, postgis_raster and postgis_topology extensions. The postgis_topology requires postgis but it is not always installed first. Yes, sometimes it seems to be happening in a correct way, but the whole process feels completely random and may take a lot of attempts and time.

postgresql cluster crd:

  preparedDatabases:
    test:
      extensions:
        postgis: public
        postgis_raster: public
        postgis_topology: topology

postgres-operator log messages:

time="2023-01-31T14:45:26Z" level=info msg="creating extension \"postgis_topology\" schema \"topology\"" cluster-name=default/test pkg=cluster worker=0
time="2023-01-31T14:45:26Z" level=error msg="could not sync prepared databases: could not execute create extension: pq: required extension \"postgis\" is not installed" cluster-name=default/test pkg=cluster worker=0

In order to make it work all the time from the first try there should be a strict order of installation.

Start by tracing preparedDatabases extension synchronization from the PostgreSQL cluster CRD to the logged create-extension operation. Use the postgis, postgis_raster, and postgis_topology example to check how dependencies are represented and ordered; done means required extensions install deterministically on the first attempt.