SOLVED Rescue Apps (vaultwarden) lost postgres database

[phradr]

Hi forum,

I hope someone can help me here. I don't know why and couldn't figure out aufterwards why this happened.

I have set up the vaultwarden app from truecharts. That ran without issues for months. Even the updates didn't change anything on this. Then I had to restart TrueNAS and the story begins.

For normal vaultwarden scales up three pods (afaik, and not in this specific order):
1. postgresql database
2. pgbnouncer
3. web interface / app

All of a sudden (from my view) after the restart, the PVC and trigger to start the postgres database pod disappeared. I absolutely have no clue where I should start from now.

The good thing (hopefully) is, that I take snapshots from the whole pool my apps are stored. But they are only kept for 2 weeks (until now - need to change this to 4 to win some more time for investigation).

Now what I hope to find here:
How can I recreate the database POD?
When I start the App via GUI I get this:

Code:

sudo k3s kubectl get pods -n ix-vaultwarden

NAME                                       READY   STATUS             RESTARTS         AGE
vaultwarden-cnpg-main-rw-67cb97bb5-mcjgb   1/1     Running            0                22h
vaultwarden-cnpg-main-rw-67cb97bb5-jtq9z   1/1     Running            0                22h
vaultwarden-59b4d6ffd6-qcd7h               0/1     CrashLoopBackOff   32 (2m12s ago)   148m

vaultwarden-cnpg-main-rw-67cb97bb5-mcjgb is the pgbouncer pod appears twice, because I set it to 2 replications.

But how it should look like is:

Code:

sudo k3s kubectl get pods -n ix-vaultwarden2

NAME                            READY   STATUS    RESTARTS   AGE
vaultwarden2-cnpg-main-1        1/1     Running   0          22h
vaultwarden2-cnpg-main-2        1/1     Running   0          22h
vaultwarden2-67d9d75557-vwt9v   1/1     Running   0          17h

This vaultwarden2 app I set up yesterday to find diffs in config or anywhere else - but I had no luck until now.

So for now the pgbouncer of the original installation cannot connect to the postgresql database as it is not loading anymore. From what I see it should have two PODs with this naming schema: "vaultwarden-cnpg-main-[1,2]". In these PODs I should be able to open the shell within it with the postgres user. Within the "vaultwarden-cnpg-main-rw" PODs I can only open the shell with the so called pgbouncer user:

If I now want to get into psql, I get this:

\
what seems logical to me, as there is no psql instance started for this app.
I also cannot connect to it with via pgadmin with the stored connection string:

Code:

sudo ~/tcdbinfo.sh
Application     Username       Password    Address                                                        Port
vaultwarden2    vaultwarden    X1...dsA    vaultwarden2-cnpg-main-rw.ix-vaultwarden2.svc.cluster.local    5432
vaultwarden     vaultwarden    XT...zBe    vaultwarden-cnpg-main-rw.ix-vaultwarden.svc.cluster.local      5432

While the one for the new installed vaultwarden2 works like a charm.

I now hop, I could just restore an older configuration that still worked, as I have the snapshots and the original pvc config:

Code:

sudo k3s kubectl get pvc -n ix-vaultwarden
NAME                          STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS                   AGE
vaultwarden-cnpg-main-2       Bound    pvc-3e041ee9-33e4-461f-808b-76430b4817a2   256Gi      RWO            ix-storage-class-vaultwarden   110d
vaultwarden-cnpg-main-2-wal   Bound    pvc-ff17e601-abea-42e4-a512-b55844733e36   256Gi      RWO            ix-storage-class-vaultwarden   110d
vaultwarden-cnpg-main-3-wal   Bound    pvc-411c0fd5-b260-43fd-9fce-186b5c87550a   256Gi      RWO            ix-storage-class-vaultwarden   5d
vaultwarden-cnpg-main-3       Bound    pvc-a7924aab-952a-4bf3-ad19-2745c8cebeb0   256Gi      RWO            ix-storage-class-vaultwarden   5d
vaultwarden-data              Bound    pvc-7b598f15-d5d0-4cfd-8a53-31b0020e6570   256Gi      RWO            ix-storage-class-vaultwarden   110d

Both entries "vaultwarden-cnpg-main-3" and "vaultwarden-cnpg-main-3-wal" are new because I started to change configurations in kind of a small panic attack (shame on me!).

With zfs I still find snapshots:

Code:

zfs list -t snapshot | grep pvc-3e041ee9-33e4-461f-808b-76430b4817a2
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@ix-applications-backup-system-update--2023-06-21_15:55:03     2.30M      -     9.73M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202307310600-auto                                              984K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202308070600-auto                                              624K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202308140600-auto                                              636K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202308210600-auto                                             1.07M      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202308280600-auto                                              304K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202308300000-auto                                              112K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202308310000-auto                                              136K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309010000-auto                                              736K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309020000-auto                                              136K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309030000-auto                                              120K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309040000-auto                                              236K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309040600-auto                                              208K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309050000-auto                                              672K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309060000-auto                                                0B      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@ix-applications-backup-system-update--2023-09-06_11:38:45        0B      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309070000-auto                                                0B      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309080000-auto                                                0B      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309090000-auto                                              128K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309100000-auto                                              124K      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309110000-auto                                                0B      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309110600-auto                                                0B      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309120000-auto                                                0B      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@93                                                               0B      -     10.1M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-3e041ee9-33e4-461f-808b-76430b4817a2@202309130000-auto                                                0B      -     10.1M  -

Code:

zfs list -t snapshot | grep pvc-ff17e601-abea-42e4-a512-b55844733e36                                            
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@ix-applications-backup-system-update--2023-06-21_15:55:03      708K      -      740K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202307310600-auto                                             1.01M      -     1.04M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202308070600-auto                                              836K      -      868K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202308140600-auto                                              880K      -      912K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202308210600-auto                                              780K      -      812K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202308280600-auto                                              824K      -      856K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202308300000-auto                                              584K      -      868K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202308310000-auto                                              368K      -      820K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309010000-auto                                              588K      -      788K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309020000-auto                                              260K      -      936K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309030000-auto                                              160K      -      928K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309040000-auto                                              128K      -      836K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309040600-auto                                              128K      -      844K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309050000-auto                                              688K      -      880K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309060000-auto                                                0B      -     1.09M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@ix-applications-backup-system-update--2023-09-06_11:38:45        0B      -     1.09M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309070000-auto                                                0B      -     1.06M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309080000-auto                                                0B      -     1.06M  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309090000-auto                                              192K      -      912K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309100000-auto                                              112K      -      816K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309110000-auto                                                0B      -      804K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309110600-auto                                                0B      -      804K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309120000-auto                                                0B      -      804K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@93                                                               0B      -      804K  -
vm-apps-ssd/ix-applications/releases/vaultwarden/volumes/pvc-ff17e601-abea-42e4-a512-b55844733e36@202309130000-auto                                                0B      -      804K  -

So please, could someone give me some advice how to proceed and regain access to the database? That alone would be more than enough, as I would then upgrade the app to an external cloudnative-pg app in the same step. A psql dump would be all I need.

 

[phradr]

As I already invested a lot of time and didn't want to flush the results down the toilet, I kept digging.

What I was interpreting wrong was, that I would find all I was searching for with 'browsing the zfs snapshot itself'.

Means, if you ever are in a similar situation, don't repeat my mistake. Therefore I will not post this approach but the one that worked so far:

1. create a new folder, I created /mnt/snapshot
2. mount the snapshot with sudo mount -t snapshot/path/from/list/output@timestamp /mnt/snapshot (beware of a possibly used encryption - I had none) now you can browse the data from the snapshot

Here I finally found the whole psql data I was missing since nearly a week.
Now I needed to figure out where the the missing pgsql pod configuration is/qas stored. Therefore I take a look at the new vaultwarden2 configmaps and compare it to the current vaultwarden one

k3s kubectl describe configmaps -n ix-vaultwarden2

But that didn't show the significant diffs I was looking for.
So I compared the deployments:

Code:

k3s kubectl get deployment -n ix-vaultwarden         
NAME                       READY   UP-TO-DATE   AVAILABLE   AGE
vaultwarden-cnpg-main-rw   2/2     2            2           111d
vaultwarden                0/1     1            0           111d
k3s kubectl get deployment -n ix-vaultwarden2            
NAME                        READY   UP-TO-DATE   AVAILABLE   AGE
vaultwarden2-cnpg-main-rw   0/0     0            0           46h
vaultwarden2                1/1     1            1           46h

As I couldn't make any significant approach here, I changed my strategy.

I had access to the psql data dir. So I installed postgresql@15 (via brew), copied the pgdata dir to the local pgsql@15 root dir and the wal dir too. Now I had some errors starting the psql@15, so I had to change minor settings in the postgres.conf. It now starts and I can connect to the database (with the origin vaultwarden user and pass), extract/dump data, and inject it to a new set up instance.

A cleaner approach would be to repair the config of the image / pods to a state where everything ran fine, but I couldn't make it to find some helpful documentation on k3s and kubernetes configuration in truenas. Or I simply know not enough about kubernetes etc. to handle that.
Anyway, I hope this helps someone one day in similar situations.

@admins: maybe you find better headings for this thread. Feel free to change.
@All: maybe you could enrich this thread with the things I don't know and was messing around with for days...

 

[Bear Tan]

Hi Phradr
Thanks for your information. I think I hit the same issue as yours.
Now I am trying your way to get my PGSQL data back for my nextcloud app, but I am fully new to PGSQL.
Could you help to guide me how many files/folders I need to replace into a fresh PGSQL installation, is it all files/folders under 'pgdata' including those *.conf and *.opts files? What is the change in the postgres.conf? Is there any document I can refer to?

Thanks in advance.

 

[phradr]

Hi @Bear Tan ,
do you have access to your psql data dir?
If so just follow my steps as described:

1. copy psql data dir to your local machine
2. install psql on your local machine
3. install psql web admin on your local machine
4. start both once just to be sure it successfully starts and ui can connect db
5. stop psql again
6. copy (not move, as you might repeat this a couple of times) your data to the local psql data dir
7. start psql again
8. export data
9. import data into your origin psql instance nextcloud connects to

Good luck!

 

[Bear Tan]

Hi @Bear Tan ,
do you have access to your psql data dir?
If so just follow my steps as described:

1. copy psql data dir to your local machine
2. install psql on your local machine
3. install psql web admin on your local machine
4. start both once just to be sure it successfully starts and ui can connect db
5. stop psql again
6. copy (not move, as you might repeat this a couple of times) your data to the local psql data dir
7. start psql again
8. export data
9. import data into your origin psql instance nextcloud connects to

Good luck!

Hi Phradr

I tried and I found out the service can start up base on folder.
I followed your steps by copying the whole folder into a windows psql instance then start up the instance.
I got something wrong when start up, because the *.conf file is for Debian. I overwrite the postgresql.conf by using the fresh one and modify the pg_hba.conf with 'trust' access. Now I get the data.

Thanks a lot.

 

[phradr]

@Bear Tan You’re welcome, I‘m very glad to hear that!