Skip to content

Update file postgres.md due to Postgres15 variable changes

Michelle Almendarez requested to merge Updating_Variables_due_to_Postgres15 into master

What does this MR do and why?

The original notes on updating the CI/CD variables were not clear you have to define them under Settings rather than just set them in the yaml.

Based on this ticket here where it was discovered as well as my customer ticket #484884

The workaround is shown here in Issue 30178

  • The solution is to define the variables in the UI first, let's say:
  • SQL_VARIABLE_DEFINED_IN_UI = somevalue (in CI/CD settings).

Then, in the .gitlab-ci.yml file you would add a global variable that takes in the variable saved in the UI, and that global variable can be used by the services job:

variables:
  - THE_ACTUAL_VARIABLE_I_WANT_TO_USE: $SQL_VARIABLE_DEFINED_IN_UI

services-job:
  - Does a thing using the $THE_ACTUAL_VARIABLE_I_WANT_TO_USE

The current documentation also does not take into account that since Postgres 15 was released they no longer accept variables that contain a quote, backslash, or dollar sign. If you do not wish to set variables in the CI/CD settings and instead wish to have them in your YAML you need to include them as a string without quotes backslashes or dollar signs.

  variables:
    POSTGRES_DB: example
    POSTGRES_USER: example_user
    POSTGRES_PASSWORD: example_pass1

This was tested in my enviroment successfully but the previous ticket has the best example if you have admin access.

My projects

  • Postgres values set in yaml in this run, you can see the password is set to $POSTGRES_PASSWORD and that value is set in CI/CD but I have set the remaining variables as strings.
variables:
  POSTGRES_DB: postgresdb
  POSTGRES_USER: postgres
  POSTGRES_PASSWORD: $POSTGRES_PASSWORD

job passes: https://gitlab.com/malmendarez_ultimate_group/postgres_var_set_in_file/-/jobs/5823792691

Example of a jobs failing if values are not set in CI/CD

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

Example below:

  1. Create a simple job calling Postgres using the following template
default:
  services:
    - postgres:15

variables:
  POSTGRES_DB: postgresdb
  POSTGRES_USER: postgres
  POSTGRES_PASSWORD: $POSTGRES_PASSWORD
  POSTGRES_HOST_AUTH_METHOD: trust


test-build:
  image: postgres
  services:
    - postgres

  script:
    #- export PGPASSWORD=$POSTGRES_PASSWORD
    - echo "The value of \$POSTGRES_USER is $POSTGRES_USER"
    - psql -h "postgres" -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "SELECT 'OK' AS status;"
  1. Play around with setting and unsetting variables in CI/CD
  2. Try setting variables with out CI/CD directly in the yaml and add single quotes. For example POSTGRES_DB: postgresdb becomes POSTGRES_DB: 'postgresdb'
  3. Watch it throw fatal error.

Merge request reports