Add init scripts

[LamaAni]

First go (draft that needs to be checked).

What:

Allow init script to be ingested by the database when, 1.The database loads for the first time. 1.Each time the database release is created.

See issue: https://github.com/LamaAni/postgres-xl-helm/issues/29

[sstubbs]

Sorry this has taken me so long. I've had something else I've been busy with and also wanted to test it properly. It works really well most the time. I do get itermittant failures though when it says cannot start transaction on datanode.

I have added a pull request to get this working with pgbouncer. You will see in there I have added a sleep in there:

• name: wait_for_connection.sh script: |- sleep 5

Even with this as 2 seconds I don't get any errors which is great. Would be ideal if I didn't need to add this but would be happy to merge this.

[LamaAni]

Sorry this has taken me so long. I've had something else I've been busy with and also wanted to test it properly. It works really well most the time. I do get itermittant failures though when it says cannot start transaction on datanode.

I have added a pull request to get this working with pgbouncer. You will see in there I have added a sleep in there:

• name: wait_for_connection.sh script: |- sleep 5

Even with this as 2 seconds I don't get any errors which is great. Would be ideal if I didn't need to add this but would be happy to merge this.

Hi, yea, that is very odd. Can you share the yaml for that specific configuration? Also can you share the logs?

In general I vowed to avoid using sleep in any of my helm charts. That is a point of pride :)

[sstubbs]

Yes using sleep is bound to cause issues later I would also rather not use it. This happens most the time when running install.sh from within the new pgbouncer_with_vault example.

When I remove

name: wait_for_connection.sh
script: |-
sleep 5

So this works

# yaml anchor
small_resource_limit: &small_resource_limit
  limits:
    memory: "500Mi"
    cpu: "250m"

image: sstubbs/pgxl:latest

gtm:
  resources: *small_resource_limit
datanodes:
  count: 1
  resources: *small_resource_limit
coordinators:
  count: 1
  resources: *small_resource_limit
proxies:
  count: 1
  enabled: true
  resources: *small_resource_limit

security:
  passwords_secret_name: {{SECRET_NAME}}
  pg_password: {{SECRET_KEY}}
  postgres_auth_type: md5

service:
  type: ClusterIP

on_load:
  # set to 1 so if database create fails we will see
  # it right away.
  back_off_limit: 1
  enabled: true
  resources: *small_resource_limit
  init:
    - name: wait_for_connection.sh
      script: |-
        sleep 5
    - name: connection_pool_create_base_user.sh
      script: |-
        psql -c "$CONNECTION_POOL_CREATE_BASE_USER"
    - name: connection_pool_create_pgshadow_lookup.sh
      script: |-
        psql -c "$CONNECTION_POOL_CREATE_PGSHADOW_LOOKUP"
    - name: connection_pool_create_pgshadow_lookup_template1.sh
      script: |-
        psql -d template1 -c "$CONNECTION_POOL_CREATE_PGSHADOW_LOOKUP"
        psql -c "CLEAN CONNECTION TO ALL FOR template1;"

and this doesnt

# yaml anchor
small_resource_limit: &small_resource_limit
  limits:
    memory: "500Mi"
    cpu: "250m"

image: sstubbs/pgxl:latest

gtm:
  resources: *small_resource_limit
datanodes:
  count: 1
  resources: *small_resource_limit
coordinators:
  count: 1
  resources: *small_resource_limit
proxies:
  count: 1
  enabled: true
  resources: *small_resource_limit

security:
  passwords_secret_name: {{SECRET_NAME}}
  pg_password: {{SECRET_KEY}}
  postgres_auth_type: md5

service:
  type: ClusterIP

on_load:
  # set to 1 so if database create fails we will see
  # it right away.
  back_off_limit: 1
  enabled: true
  resources: *small_resource_limit
  init:
    - name: connection_pool_create_base_user.sh
      script: |-
        psql -c "$CONNECTION_POOL_CREATE_BASE_USER"
    - name: connection_pool_create_pgshadow_lookup.sh
      script: |-
        psql -c "$CONNECTION_POOL_CREATE_PGSHADOW_LOOKUP"
    - name: connection_pool_create_pgshadow_lookup_template1.sh
      script: |-
        psql -d template1 -c "$CONNECTION_POOL_CREATE_PGSHADOW_LOOKUP"
        psql -c "CLEAN CONNECTION TO ALL FOR template1;"

[sstubbs]

Will post logs shortly.

[LamaAni]

Tnx @sstubbs Will test this today or tomorrow and post results. How urgent is this PR for you?

[sstubbs]

Not that urgent I intend to start running it on our staging cluster on Friday once I have fixed the issues on the pgbouncer pr I have done and have some benchmarks to know it can reliably handle load but this doesn't stop me doing that as I can just use this branch.

[LamaAni]

Ok. In that case, and before you add that to the staging I suggest we fix the issues with the long names and do a release with the current fixes. This way the staging can use the release (either directly or through the git tag).

i.e. Using the helmfile you can do something like,

releases:
  - name: tran-with-secret
    # Use of a specific relaease. 
    chart: https://github.com/LamaAni/postgres-xl-helm/archive/0.6.0.tar.gz
    values:
      - ./values.yaml

And that way pin the release version to that specific implementation without the git clone.

This of course needs to be tested.

[LamaAni]

@sstubbs I think you accidentally merged your code into my branch, instead of master.