Postgres databases fail to work

[NeillM]

I have found that when trying to run a postgres database using for example:

withMoodlePluginCiContainer(php: '8.0', db: 'postgres) {

I get the following error:

moodle-plugin-ci install --db-type pgsql --db-user jenkins --db-pass jenkins --db-host 127.0.0.1 --branch MOODLE_311_STABLE --plugin ./files/converter/onedrive
 0/9 [>---------------------------]   0% < 1 sec [Starting install]
 1/9 [===>------------------------]  11% < 1 sec [Cloning Moodle]
 2/9 [======>---------------------]  22% 14 secs [Moodle assets]
In ProcessHelper.php line 101:

  The command "env PGPASSWORD='jenkins' psql -c 'CREATE DATABASE "moodle";' -  
  U 'jenkins' -h '127.0.0.1'" failed.                                          

  Exit Code: 2(Misuse of shell builtins)                                       

  Working directory: /var/lib/jenkins/workspace/leconverter_onedrive_jenkinst  
  est                                                                          

  Output:                                                                      
  ================                                                             

  Error Output:                                                                
  ================                                                             
  psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:   
   database "jenkins" does not exist

When changing from postges to mysql everything works fine.

I'm sure I remember postgres working in the past, however it could be that I only ever tried it after using a mysql database

[micaherne]

Thanks for reporting that @NeillM! I've not encountered it before, although it's been a while since I ran any of our tests against PostgreSQL. I'll try a test and see if I can replicate it.

[NeillM]

I wonder if this part in the dockerfile is the issue:

RUN service postgresql start \
&& createuser -s -i -d -r -l -w jenkins \
&& psql -c "ALTER ROLE jenkins WITH PASSWORD 'jenkins';"

If I split that into 3 separate run commands the build fails (at least when running it locally)

RUN service postgresql start
RUN createuser -s -i -d -r -l -w jenkins
RUN psql -c "ALTER ROLE jenkins WITH PASSWORD 'jenkins';"

then I get

 => ERROR [26/27] RUN createuser -s -i -d -r -l -w jenkins                                                                                                                                                                                                                                                         0.4s 
------
 > [26/27] RUN createuser -s -i -d -r -l -w jenkins:
0.400 createuser: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: Connection refused
0.400   Is the server running locally and accepting connections on that socket?
------
Dockerfile:83
--------------------
  81 |     # Create jenkins user. There's probably a better way to do this.
  82 |     RUN service postgresql start
  83 | >>> RUN createuser -s -i -d -r -l -w jenkins
  84 |     RUN psql -c "ALTER ROLE jenkins WITH PASSWORD 'jenkins';"
  85 |
--------------------
ERROR: failed to solve: process "/bin/sh -c createuser -s -i -d -r -l -w jenkins" did not complete successfully: exit code: 1

I wonder if postgres may still be doing some kind of initialisation and need some additional wait before trying to create the jenkins user.

It does look as though between separate run commands the service does not stay up, however when all mashed together it does, so it looks as though this is me looking at the wrong thing.

[NeillM]

There appear to have been two issue preventing postgres working:

1. When using the psql command without specifying a database it seems that it connects to a databased with the same name as the user. This appears to have been the cause of the message I was seeing.
2. The php-pgsql package is not being installed to Ubuntu