Migrate staging next version database to a kubernetes service managed by a postgresql operator

marked this issue as related to #5260

changed title from Migrate the staging database to a kubernetes service managed by a postgresql operator (poc/validation/...) to Migrate staging database to a kubernetes service managed by a postgresql operator

Determine postgres operator to test

Documentation on the various postgresql operator.

According to the following comparison matrix, 2 contenders are left cloudnative's and zalando's. The documentation site feels more exhaustive for the former (cloudnative) though so let's try it.

Resulting comparison matrix (source code [2]):

operator	Helm	Postgresql	Kube	HA	Disaster	Rolling	Monitoring	Bare	minikube	License	Issues	PR	star	fork	homepage	doc	Annex
	support	versions	versions		Recovery	Upgrade		metal
crunchy-data	X	>= 13	1.25-1.28	X	X	X	X	?	?	Apache-2	114	10	3.7k	573	https://github.com/CrunchyData/postgres-operator	https://access.crunchydata.com/documentation/postgres-operator/latest
zalando	X	>= 11	1.12-1.28	X	X	X	X	X	X	MIT	532	66	4k	939	https://github.com/zalando/postgres-operator	https://postgres-operator.readthedocs.io/en/latest/
cloudnative	X	>=12	1.27-1.28	X	X	X	X	X	X	Apache-2	207	64	3.4k	237	https://github.com/cloudnative-pg/cloudnative-pg	https://cloudnative-pg.io/	https://cloudnative-pg.io/documentation/current/#main-features
stackgress	X	>= 12	1.18 & 1.19?	X	X	?	X	?	X	AGPL-3	X	1	827	49	https://github.com/ongres/stackgres/	https://stackgres.io/
percona	X	>= 12	?	X	X	?	X	?	X	Apache-2	9	22	244	47	https://github.com/percona/percona-postgresql-operator	https://docs.percona.com/percona-operator-for-postgresql/2.0/	comparison matrix:
																	https://docs.percona.com/percona-operator-for-postgresql/2.0/compare.html#backups

[2] source of the table (org-mode table then export to markdown):

|--------------+---------+------------+--------------+----+----------+---------+------------+-------+----------+----------+--------+----+------+------+--------------------------------------------------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------------------|
| operator     | Helm    | Postgresql | Kube         | HA | Disaster | Rolling | Monitoring | Bare  | minikube | License  | Issues | PR | star | fork | homepage                                               | doc                                                                   | Annex                                                                             |
|              | support | versions   | versions     |    | Recovery | Upgrade |            | metal |          |          |        |    |      |      |                                                        |                                                                       |                                                                                   |
|--------------+---------+------------+--------------+----+----------+---------+------------+-------+----------+----------+--------+----+------+------+--------------------------------------------------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------------------|
| crunchy-data | X       | >= 13      | 1.25-1.28    | X  | X        | X       | X          | ?     | ?        | Apache-2 |    114 | 10 | 3.7k |  573 | https://github.com/CrunchyData/postgres-operator       | https://access.crunchydata.com/documentation/postgres-operator/latest |                                                                                   |
|--------------+---------+------------+--------------+----+----------+---------+------------+-------+----------+----------+--------+----+------+------+--------------------------------------------------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------------------|
| zalando      | X       | >= 11      | 1.12-1.28    | X  | X        | X       | X          | X     | X        | MIT      |    532 | 66 | 4k   |  939 | https://github.com/zalando/postgres-operator           | https://postgres-operator.readthedocs.io/en/latest/                   |                                                                                   |
|--------------+---------+------------+--------------+----+----------+---------+------------+-------+----------+----------+--------+----+------+------+--------------------------------------------------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------------------|
| cloudnative  | X       | >=12       | 1.27-1.28    | X  | X        | X       | X          | X     | X        | Apache-2 |    207 | 64 | 3.4k |  237 | https://github.com/cloudnative-pg/cloudnative-pg       | https://cloudnative-pg.io/                                            | https://cloudnative-pg.io/documentation/current/#main-features                    |
|--------------+---------+------------+--------------+----+----------+---------+------------+-------+----------+----------+--------+----+------+------+--------------------------------------------------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------------------|
| stackgress   | X       | >= 12      | 1.18 & 1.19? | X  | X        | ?       | X          | ?     | X        | AGPL-3   |      X |  1 | 827  |   49 | https://github.com/ongres/stackgres/                   | https://stackgres.io/                                                 |                                                                                   |
|--------------+---------+------------+--------------+----+----------+---------+------------+-------+----------+----------+--------+----+------+------+--------------------------------------------------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------------------|
| percona      | X       | >= 12      | ?            | X  | X        | ?       | X          | ?     | X        | Apache-2 |      9 | 22 | 244  |   47 | https://github.com/percona/percona-postgresql-operator | https://docs.percona.com/percona-operator-for-postgresql/2.0/         | comparison matrix:                                                                |
|              |         |            |              |    |          |         |            |       |          |          |        |    |      |      |                                                        |                                                                       | https://docs.percona.com/percona-operator-for-postgresql/2.0/compare.html#backups |
|--------------+---------+------------+--------------+----+----------+---------+------------+-------+----------+----------+--------+----+------+------+--------------------------------------------------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------------------|

marked the checklist item Determine which postgresql operator to test as completed

changed the description

mentioned in issue #5260

mentioned in commit swh/devel/snippets@6b567984

Steps done successfully so far:

Install of a bare postgresql cluster
Adding a pooler (pbbouncer) in front of it
Import databases from an existing db (through initdb)
External access to the instance

Remaining tests (supported from the documentation):

backups
restore backups
replication

Requirements:

for import, use of the 'postgres' user

Limitations encountered so far:

import: use a direct connection to the postgresql db (passing through pgbouncer will fail the import with non-explicit messages)
access to database:
- load-balancer requires an "externalTrafficPolicy" set to "Cluster" (and other cluster's load-balancers must have that same policy [1] [2]) if we want it to have an external ip set
- or we must have another dedicated external IP (metallb's IP address pool) for it (to avoid configuration change in the other cluster's load-balancer)
beware the network policies in the namespace where the db pods are installed, they can prevent the secondary replicas to be spawned [3]

[1] some have the "Local" policy which prevents the load-balancer to have an external ip (so not accessible).

[2] "Local" policy means we keep the source ip in the logs, otherwise, it's changed with internal cluster ip.

[3] https://cloudnative-pg.io/documentation/1.20/troubleshooting/#networking-is-impaired-by-installed-network-policies