PostgreSQL extensions are installed in a random order

[justabaka]

• 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.

[FxKu]

That's because of the way we loop over database objects to sync. The order of the DB query is alphabetical, but the go loop makes it random, I believe.

Ideally we should not return an error on failure but just collect error message and continue with the next extension. This way your problem would go away automatically on subsequent syncs. We do this already for database users but not here.