postgres_data_path not respected by postgres-15 causing data lost

[adpavlov]

Please confirm the following

• I agree to follow this project's code of conduct.
• I have checked the current issues for duplicates.
• I understand that the AWX Operator is open source software provided for free and that I might not receive a timely response.

Bug Summary

Since awx-postgres-15 postgres_data_path variable is not respected and default /var/lib/pgsql/ is used.

Snippet from pod:

Containers:
  postgres:
    Container ID:   docker://9ce9fed297e67772302260c7d44a44a9e75160a819d4534da42eb58c5f1bfe3d
    Image:          quay.io/sclorg/postgresql-15-c9s:latest
<...>
    Environment:
<...>
      PGDATA:                     /data/awx/postgres
<...>
    Mounts:
      /data/awx from postgres-15 (rw,path="awx")

Even PGDATA is set properly as /data/awx/postgres and directory is mounted as /data/awx from PV, it's empty:

bash-5.1$ ls -la /data/awx/
total 0
drwxrwxrwx 2 root root  6 Mar 14 15:58 .
drwxr-xr-x 3 root root 17 Mar 20 19:43 ..

In fact database files are not in persistent volume and will be lost on restart:

bash-5.1$ ls -la /var/lib/pgsql/     
total 16
drwxrwx--- 1 postgres root      93 Mar 22 17:50 .
drwxr-xr-x 1 root     root      19 Mar 13 11:58 ..
-rw------- 1 postgres postgres 472 Mar 21 10:05 .bash_history
-rw-rw-r-- 1 postgres root      85 Feb 13  2023 .bash_profile
drwxrwx--- 2 postgres root       6 Feb 13  2023 backups
drwxrwx--- 1 postgres root      22 Mar 20 19:43 data
-rw-r--r-- 1 postgres postgres 671 Mar 20 19:43 openshift-custom-postgresql.conf
-rw-r--r-- 1 postgres postgres 830 Mar 20 19:43 passwd

AWX Operator version

2.13.1

AWX version

24.0.0

Kubernetes platform

other (please specify in additional information)

Kubernetes/Platform version

k3s version v1.28.7+k3s1

Modifications

no

Steps to reproduce

Upgrade or install from scratch

Expected results

postgres to store data in PV

Actual results

postgres data is not in PV

Additional information

No response

Operator Logs

No response

[yoonmin1030]

I have the same issue.
in the main.yaml file of installer role in awx-operator, the value of postgres DB path is defined "postgres_data_path: '/var/lib/pgsql/data/pgdata'".
However, in the`` postgres-15 container image, postgres process env is defined $PGDATA=/var/lib/pgsql/data/userdata

In the postgres-15 image, the $PGDATA is hard coded, as like followed
in the file "/usr/share/container-scripts/postgresql/common.sh"
$HOME=/var/lib/pgsql
...
export PGDATA=$HOME/data/userdata
$PGDATA=/var/lib/pgsql/data/userdata
...

then the $PGDATA variable from postgres-15 pod set in postgres-15 container env. as intended (like "PGDATA=/var/lib/pgsql/data/pgdata" , BUT in the image the $PGDATA does NOT set as intended.

So,
the postgres-15 pod seems to set the variable $PGDATA as the same as the variable from image, like $PGDATA=/var/lib/pgsql/data/userdata

[kurokobo]

In short, PSQL from sclorg does not support changing data path by PGDATA environment variables.

This issue needs to be corrected, but care should be taken to ensure that users who have already changed postgres_data_path from the default do not lose data in the upgrade.

[fosterseth]

care should be taken to ensure that users who have already changed postgres_data_path from the default do not lose data in the upgrade.

@TheRealHaoLiu we probably need to come up with solution for this...

[kurokobo]

@fosterseth
For users who are using PSQL13, a simple implementation is to simply ignore postgres_data_path in PSQL15 and mount the PV in /var/lib/pgsql/data in any cases.
Since data migration is done with pgdump | pgrestore with both old and new PSQLs running, I believe we can migrate data from PSQL13 with PV with custom path, to PSQL15 with PV with fixed path.

Users using PSQL15 (Operator 2.13+) with a customized postgres_data_path outside of /var/lib/pgsql/ are already at risk of losing data if PSQL is simply restarted.
For such users, I don't know which is the correct approach that immediate announce manual procedure to revert postgres_data_path to default without any data loss, or implement some feature in the future release to move data from custom path to PV.

This is a bit out-of-topic, but I honestly disagree with the decision to keep the release cadence while inheriting a bug without any warnings that risks data loss 😞
The more versions that are released, the more patterns there are to consider, and more users who are trying to upgrade and are facing bug, which complicates the situation further.

[rooftopcellist]

@kurokobo yes, I think we should move forward with hardcoding the postgres_data_path to /var/lib/pgsql/data if we cannot find another way around it. And we should remove the custom postgres_data_path parameter, or at least deprecate it. There is a bit in the helm chart that requires it that would need to be addressed too iirc. I just opened a PR here to do that.

• Remove the ability to customize the postgres_data_dir #1798

For users that have a customized postgres_data_path already... Please see the steps in this forum post for how to remedy that situation:

• https://forum.ansible.com/t/attention-do-not-upgrade-to-awx-operator-to-2-13-0/4363

This is a truly unfortunate bug.. I will edit the 2.13.1 and 2.14.0 release text now to add a warning for users upgrading who have a custom postgres_data_dir configured.

[TheRealHaoLiu]

for people that read this in the future, we deprecated and removed postgres_data_path