Should i update my selfhosted database? MariaDB
reddit / selfhosted

----------------------------------------------------------------------
Altair12311 · 2023-10-18 12:00

Hi! im a bit confused because i have a MariaDB instance on my mini-pc
so i can control things like NextCloud but some people told me i
should not update my docker image of MariaDB because it can break? and
other people said update it... Im unsure what to do... Some advices?

----------------------------------------------------------------------
Murky-Sector · 2023-10-18 12:15

back everything up and do your update

simple

----------------------------------------------------------------------
Altair12311 · 2023-10-18 12:19

Thanks for the advice

----------------------------------------------------------------------
thekrautboy · 2023-10-18 12:30

Database version upgrades can be tricky. Always make a proper backup
before! Ideally you would make a db dump plus a copy of the db
container data.

Typically smaller version upgrades are not a problem, for example
updating from 10.9.4 to 10.9.5 shouldnt be a issue.

But major upgrades, like going from 9.6 to 10.2 can cause more
problems and you should always pay attention to the release notes of a
new version and also make sure that the software that makes use of
that db is also still compatible with that new db version. For example
it could be that Nextcloud says to stay on 10.9 and 11.0 is not yet
supported.

Since databases are usually not configured so that anyone can connect
to it, let alone the open internet, it is not too bad when you lag
behind a few versions. Ideally when using Docker networking for
example you would have a closed network that only connects the db with
the actual app that needs the app, and nothing else can connect to
that db.

----------------------------------------------------------------------
Altair12311 · 2023-10-18 13:11

Thanks for take the time and explain it well to me,i really appreciate
it, thanks you so much

----------------------------------------------------------------------
edwinmiracle · 2023-10-18 13:54

I tried upgrade postgres from v15 to 16 on nextcloud 27 unfortunately
not working (docker). After redeployed latest 15 the nextcloud back to
normal (docker volume mount saves me). As non expert i would recommend
you stay on same version, or backup if you jump to big version.

----------------------------------------------------------------------
rezzorix · 2023-10-18 14:29

You could:

Backup your database.
Run a second docker container with new version database.
Restore/import backed up database from old version into the new
version.

And then try out if all works without errors.

If errors, you can just revert back to your initial docker container
with the old version.

----------------------------------------------------------------------
Altair12311 · 2023-10-18 15:25

Thats my fear right now, thanks for share your experience

----------------------------------------------------------------------
Altair12311 · 2023-10-18 15:29

I will give a shot! im watching some tutorials about how to do
backups, thanks!

----------------------------------------------------------------------
droans · 2023-10-18 17:00

Postgres requires a migration.

And if you're using Docker, it'll fail if you haven't migrated.

And the migration tool is only on v16.

Only solutions I've seen so far are to create a new container and run
the migration from there or to use a special third-party migration
container.

----------------------------------------------------------------------
adamshand · 2023-10-18 17:27

If you are using bind mounts with your container, there is a really
easy way to backup before an update.

- stop the database container
- cp -a bind_mount_dir/ bind_mount_dir.20231019

Then pull the latest database image and restart database container. If
it works, yay,  you're done.

If it doesn't work, reverse the steps:

- stop the container
- mv bind_mount_dir/ bind_mount_dir.broken
- mv bind_mount_dir.20231019 bind_mount_dir

Restart container and you're back to exactly where you were before the
upgrade.

----------------------------------------------------------------------
Altair12311 · 2023-10-18 17:52

Okay thanks you so much,it worked, i could update without problems

----------------------------------------------------------------------
astutesnoot · 2023-10-18 21:14

I just had a failed upgrade from MariaDB 10.6 to 11.1 and it was only
my backup that saved me. I have a service that runs mariadb-dump every
day and tars the resulting .sql file with a dated name (keeping only
the previous 10 backups), so I was able to just start fresh with an
empty 11.1 db and restore from yesterday's dump.

Here's my backup setup:

```
# /etc/systemd/system/backupdb.service
[Unit]
Description=auto backup mariadb daily

[Service]
Type=simple
ExecStart=/usr/bin/docker exec db /config/backupdb
```

```
# /etc/systemd/system/backupdb.timer
# enable with 'systemctl enable --now backupdb.timer'
[Unit]
Description=Periodically runs the backupdb service

[Timer]
OnCalendar=Daily
Persistent=true

[Install]
WantedBy=timers.target
```

```
#!/bin/bash

# /pool/config/db/backupdb
# runs within the context of the container
# /pool/config/db/ is bind mounted into /config in the container
# backs up to /config/backup within container
# requires files /config/db_user and /config/db_pw to exist and be
populated

mkdir -p /config/backup
cd /config/backup
BASENAME=db.backup.$(date "+%Y.%m.%d-%H.%M.%S")
/usr/bin/mariadb-dump -u $(cat /config/db_user) -p$(cat /config/db_pw)
-x -A > $BASENAME.sql
/usr/bin/tar -czvf $BASENAME.tar.gz $BASENAME.sql
rm $BASENAME.sql
while (($(ls | wc -l) > 10))
do
rm $(ls -t | tail -1)
done
```

And this was how I restored with that setup.
```
cd /pool/config/mariadb/backup
tar xvf db.backup.2023.09.22-00.00.02.tar.gz
docker exec -it db bash
root@db:/# mariadb -u root -p <
/config/backup/db.backup.2023.09.22-00.00.02.sql
```

----------------------------------------------------------------------
Altair12311 · 2023-10-18 21:39

Okay you are a life saver,i was getting confuse about how to do the
backup process, big thanks for share all the commands needed
(including the one for restore just in case) big THANKS <3

----------------------------------------------------------------------
adamshand · 2023-10-18 22:20

Wheew! :-)

Being able to backup like this is one of the reasons I love bind
mounts.

----------------------------------------------------------------------
Spuxilet · 2023-10-19 22:09

I did same thing upgraded nextclouds postgres from 15 to 16. No
problems. I upgrade it to new container and try to run nextcloud with
it. If all works i just remove old container and old postgres image.