Update postgresql to 16.11

Stefan Constantinrequested to merge
update_postgresql_16_11 into master

What does this MR do?

Postgresql version bump from 16.10 to 16.11

Related issues

Closes https://gitlab.com/gitlab-org/omnibus-gitlab/-/issues/9496

Checklist

See Definition of done.

For anything in this list which will not be completed, please provide a reason in the MR discussion.

Required

  • MR title and description are up to date, accurate, and descriptive.
  • MR targets the appropriate branch.
  • Component test plan copied into MR description.
  • Latest merged results pipeline is green.
  • When ready for review, MR is labeled workflowready for review per the Distribution MR workflow.

For GitLab team members

If you don't have permissions to trigger pipelines for this MR, the reviewer should trigger these jobs for you during the review process.

  • The manual Trigger:ee-package jobs have a green pipeline running against latest commit. To debug QA failures, refer to Investigate QA failures.
  • If config/software or config/patches directories are changed, build-package-on-all-os job within the Trigger:ee-package downstream pipeline succeeded.
  • If you are changing anything SSL related, the Trigger:package:fips manual job within the Trigger:ee-package downstream pipeline succeeded.
  • If CI/CD configuration is changed, the branch is pushed to dev.gitlab.org to confirm regular branch builds aren't broken.

Expected (please provide an explanation if not completing)

  • Test plan indicating conditions for success has been posted and passes.
  • Documentation created or updated.
  • Tests added.
  • Integration tests added to GitLab QA.
  • Equivalent MR/issue for the GitLab Chart opened.
  • Potential values for new configuration settings validated. Formats such as integer 10, duration 10s, URI scheme://user:passwd@host:port may require quotation or other special handling when rendered in a template and written to a configuration file.

Test plan

To test this upgrade I followed the instructions available at https://docs.gitlab.com/administration/postgresql/replication_and_failover to setup a gitlab install with multiple postgresql nodes.

I have installed gitlab 18.7 which comes with postgresql 16.10. I grabbed the gitlab-omnibus package for 18.7 and installed in local VMs with a few changes from the documentation above (to make the work a little easier but still do a good test):

  • 5 nodes in total
  • 1 rails + consul + redis node . It's also running haproxy as a load balancer for the pgbouncer nodes below
  • 2 pgsql nodes (master and secondary) as opposed to 1 master and 2 secondary in the notes above
  • 2 nodes with consul + pgbouncer each

After the initial install and setup , I could login to gitlab and create projects, change settings, etc.

My pgsql nodes are:

  • debian-2 - 192.168.122.20
  • debian-3 - 192.168.122.242
root@debian-2:~# gitlab-ctl patroni members
+ Cluster: postgresql-ha ----+---------+---------+----+-----------+
| Member   | Host            | Role    | State   | TL | Lag in MB |
+----------+-----------------+---------+---------+----+-----------+
| debian-2 | 192.168.122.20  | Leader  | running |  4 |           |
| debian-3 | 192.168.122.242 | Replica | running |  4 |         0 |
+----------+-----------------+---------+---------+----+-----------+

As expected, both nodes, show they are running postgresql 16.10:

gitlabhq_production=# select version();
                                          version                                           
--------------------------------------------------------------------------------------------
 PostgreSQL 16.10 on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)

On the pgbouncer nodes, I initially see both pgbouncers connected to what is then the master (output is the same for both):

pgbouncer=# show databases;
        name         |   host   | port |      database       | force_user | pool_size | min_pool_size | reserve_pool_size | server_lifetime | pool_mode | load_balance_hosts | max_connections | current_connections
 | max_client_connections | current_client_connections | paused | disabled 
---------------------+----------+------+---------------------+------------+-----------+---------------+-------------------+-----------------+-----------+--------------------+-----------------+--------------------
-+------------------------+----------------------------+--------+----------
 gitlabhq_production | debian-2 | 5432 | gitlabhq_production |            |       100 |             0 |                 5 |            3600 |           |                    |               0 |                   0
 |                      0 |                         21 |      0 |        0
 pgbouncer           |          | 6432 | pgbouncer           | pgbouncer  |         2 |             0 |                 0 |            3600 | statement |                    |               0 |                   0
 |                      0 |                          1 |      0 |        0
(2 rows)

If I shutdown one pgsql node (debian-2), pgbouncers switch and the app continues running:

pgbouncer=# show databases;
        name         |   host   | port |      database       | force_user | pool_size | min_pool_size | reserve_pool_size | server_lifetime | pool_mode | load_balance_hosts | max_connections | current_connections
 | max_client_connections | current_client_connections | paused | disabled 
---------------------+----------+------+---------------------+------------+-----------+---------------+-------------------+-----------------+-----------+--------------------+-----------------+--------------------
-+------------------------+----------------------------+--------+----------
 gitlabhq_production | debian-3 | 5432 | gitlabhq_production |            |       100 |             0 |                 5 |            3600 |           |                    |               0 |                   4
 |                      0 |                         21 |      0 |        0
 pgbouncer           |          | 6432 | pgbouncer           | pgbouncer  |         2 |             0 |                 0 |            3600 | statement |                    |               0 |                   0
 |                      0 |                          1 |      0 |        0
(2 rows)

pgbouncer=#

At this point I test the upgrade by installing a package built by the pipeline in this MR on the two pgsql nodes. After the upgrade and a

gitlab-ctl restart

Both pgsql nodes show:

root@debian-2:~# gitlab-psql
psql (16.11)
Type "help" for help.

gitlabhq_production=# select version();
                                          version                                           
--------------------------------------------------------------------------------------------
 PostgreSQL 16.11 on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)

Again, using the app, changing settings, creating projects works. Knocking the master/leader offline, correctly switches to the replica and everything continues on as before:

root@debian-3:~# gitlab-ctl patroni members
+ Cluster: postgresql-ha ----+---------+---------+----+-----------+
| Member   | Host            | Role    | State   | TL | Lag in MB |
+----------+-----------------+---------+---------+----+-----------+
| debian-2 | 192.168.122.20  | Replica | running |  6 |         0 |
| debian-3 | 192.168.122.242 | Leader  | running |  6 |           |
+----------+-----------------+---------+---------+----+-----------+

Actually I've performed these test while the two nodes:

  • were running 16.10
  • each was running a different version
  • then again both running 16.11

As expected , everything worked correctly.

I consider this test complete, let me know please if I can provide any other info.

Edited by Stefan Constantin

Merge request reports