Patroni dynamicConfiguration not working properly

[alepefe95]

Hello guys,

I was exploring the possibility of using your new 5.1.0 postgres-operator as the old version 4.7.5 is no longer working on a k8s v1.22.9 due to a custom resource definition error.

One of our project's requirement is to use the postgres 14 citus preloaded library.
*The citus library need to be the first of the directive shared_preload_libraries in the postgres configuration file.

In order to achieve my goal, I forked your repo postgres-operator-examples and modify you postgres.yaml resource definition.

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
...
spec:
....
  patroni:
    dynamicConfiguration:
      postgresql:
        # custom_conf : /opt/custom-postgresql.conf
        parameters:
          shared_preload_libraries: citus,pgaudit,pg_stat_statements,pgnodemx
          shared_buffers: 256MB

After applying the new configuration postgres doesnt boot as the citus extension was not the first library loaded.
The problem is that the value of
shared_preload_libraries: citus,pgaudit,pg_stat_statements,pgnodemx

what really does is concatenate pgaudit plus your custom value, the result is:

shared_preload_libraries = pgaudit, citus,pgaudit,pg_stat_statements,pgnodemx
Thats why the postgres server boot fails.

I also tried defining a custom postgres configuration custom_conf : /opt/custom-postgresql.conf but It didnt work either.

Is there any way to solve this problem?

Thank you,
Alepefe

[anthonator]

I'm running into this exact same situation. Need citus to be the first item in shared_preload_libraries.

@alepefe did you have any luck with this?

[alepefe95]

I'm running into this exact same situation. Need citus to be the first item in shared_preload_libraries.

@alepefe did you have any luck with this?

Yes, but I had to create a customized docker image for the "postgres-operator"

Its a little tricky but you can:

1. Branch the operator's repository
2. Find the code that is causing the problem (I think its a function in controller.go file)
3. Compile it
4. Create a new docker image

I can provide you with the docker image link if you want.

I'm having trouble when it comes to configuring pgbouncer's hba for citus.

When can help each other :)

[anthonator]

@andrewlecuyer could you provide any color around why pgaudit is automatically added to shared_preload_libraries? Also, is there interest in changing this?

[tjmoore4]

@alepefe Thank you for the detailed issue description. I've added this to our backlog for implementation in a future PGO release.

[pere3]

i'm kinda on the same page, but with pgnodemx - it is somehow being added to shared_preload_libraries even if i'm dropping this extension completely building customized version of crunchy-containers suite.

which results in a fatal error on bootstrapping process

2022-09-05 13:21:16.796 UTC [206] FATAL:  could not access file "pgnodemx": No such file or directory

is there any way to remove pgnodemx from requirements to run postgres in crunchy-containers?

main driving force behind this request is a need to run older postgres versions than 13 and 14 supported by Crunchy Data Developer Program

[spacepluk]

Just adding myself to the list of people interested in running postgres-operator with citus 😄

@alepefe did you figure out the pgBouncer issues?

Maybe we can put together a PR that adds new images with all the solutions so we can all benefit.
Trying to follow your notes atm :)

[tunatoksoz]

Would it be possible to get the citus PR above (3530) in?