Correct way of backing postgresql based apps

[AFellmett]

Hello everyone,

I'm new to Truenas in general and started my journey with Truenas Scale Cobia as homelab setup.
I'm hosting a few Apps like Pihole, Unifi, Vaultwarden and NextCloud all from the official Truenas repository.

From my understanding, the concept of hostpath is used to persist the configuration of Apps. Back it up and recover in case something goes horrible wrong.
Using Pihole and Unifi, it works as expected. Deleting the corresponding app and recreating it, pointing to the same hostpath directory everything spins up and is running like a charm.

Starting with apps using postgresql, I don't really understand the concept of how to make backups and restore them.
I understand, that the postgresql files on the hostpath directory is not a valid database backup and should not be used as such - no further explanation needed.
For me it looks like: I could point the database folder to a hostpath. Backup all data uninstall the app and start over again, pointing the app to the same hostpaths as previous installation.
But that's not the case - at least on my system.
The app is setting up a complete new database with new credentials and that's totally fine.
I like the seeing the database related connection information in the Notes.
Using the pgAdmin app I can use the connection information and connect to the database and make my backup.
But is there no automatic way to make my backups? I had a look at pgagent, but didn't get it to work.

Currently I'm using the cron jobs to execute a shell script, which `exec` into my postgres service creating a backup within the pgData directory move it afterwords to a known dataset location and keep track of the number of files.
It works. Using the same method I can also recover after installing the Apps, but something about it feels unintuitive and very hacky. I don't even know if I get notified if my cron job fails.

What am I missing here?

 

[Patrick M. Hausen]

HeavyScript performs daily database dumps for me.

 

[AFellmett]

Thanks, I appreciate the general idea of using heavyscript.
Maybe I'm too picky, but I don't see this as solution, it's just another workaround.
The Warning on restoring backups has a clear message (Regardless if it's working or not):

Code:

WARNING:
This is NOT guranteed to work
This is ONLY supposed to be used as a LAST RESORT
Consider rolling back your applications instead if possible

I even don't get it to work properly. Tested it with vaultwarden, cause I have a working backup strategy in place, but cannot get it to work with heavyscript.
A short look at the script seems to reference the cnpg quite often, which is not available on my system, cause I use the TrueNAS app instead of the TrueCharts app.

 

[Patrick M. Hausen]

I am not suggesting backing up and restoring entire applications. HeavyScript offers a convenient way to perform daily or hourly consistent database dumps. Look at the instructions on Github.

These can for a clean restore be read in via e.g. pgadmin once an application has been newly installed as a disaster recovery measure.

Yes, you need to use the cloud native postgresql app for that to work. It's part of TrueCharts. I run all my apps from TrueCharts.

 

[AFellmett]

Thanks for the further explanation and clarification.
Sounds like good and valuable strategy overall.

Following up, means, that there is nothing comparable without using TrueCharts?