search

tiredofit / docker-db-backup

Backup multiple database types on a scheduled basis with many customizable options
MIT License
852 stars 125 forks source link

backup routine finished but no backup #350

Open mlause opened 4 months ago

mlause commented 4 months ago

Hi All,

I'm trying to backup postgresql14, and I get this message in the logs. I'm on Unraid 6.12.9 with db-backup as a docker.

2024-05-25.14:34:35 [STARTING] [scheduling] [1] Starting cron 2024-05-25.14:34:36 [INFO] [01-x.x.x.xALL] Next Backup at 2024-05-25 14:34:36 CDT 2024-05-25.14:34:36 [INFO] ** [01-1x.x.x.xALL] Backup 01 routines finish time: 2024-05-25 14:34:36 CDT with exit code 0 2024-05-25.14:34:36 [NOTICE] [01-x.x.x.x__ALL] Backup 01 routines time taken: Hours: 0 Minutes: 00 Seconds: 00 2024-05-25.14:34:36 [NOTICE] [01-x.x.x.x__ALL] Sleeping for another 86400 seconds. Waking up at 2024-05-26 14:34:36 CDT

There is no backup files in the backup destination. Any idea on why?

ToshY commented 4 months ago

It might be easier to spot why if you could provide logs with DEBUG_MODE=true.

mlause commented 4 months ago

I have another thread on here that may be related. I posted the debug output code in comment #349

mlause commented 4 months ago

I'm still having this issue. Postgresql14 is not being backed up. I have Postgresql14 setup as DB01. Mysql is setup as DB02 and backing up fine. I have 2 host databases. One is Postgresql and the other mysql. Is it possible to support multiple hosts on 1 instance, or do I need to have 2 instances running?

Some additional troubleshooting. I setup another instance of db-backup to troubleshoot postgres not backing up. I'm using my super username as DB01_USER which has access to all databases. I get this error as my superuser name is auth.

psql: error: connection to server at "10.10.62.20", port 5432 failed: FATAL: database "auth" does not exist.

I've tried postgres as the username being the default database and get nothing. Do I need to create another user for the postgres default as a super user to get access to all the databases?

ToshY commented 4 months ago

I'm still having this issue. Postgresql14 is not being backed up. I have Postgresql14 setup as DB01. Mysql is setup as DB02 and backing up fine. I have 2 host databases. One is Postgresql and the other mysql. Is it possible to support multiple hosts on 1 instance, or do I need to have 2 instances running?

If I'm not mistaken, yes, if you provide let's say environment variables DB01_ for MySQL and DB02_ for Postgres. You could also use multiple instances if you want but that's more a matter of preference.

Some additional troubleshooting. I setup another instance of db-backup to troubleshoot postgres not backing up. I'm using my super username as DB01_USER which has access to all databases. I get this error as my superuser name is auth.

psql: error: connection to server at "10.10.62.20", port 5432 failed: FATAL: database "auth" does not exist.

You say your superuser name is "auth", but the error that is thrown shows that it is trying to look for a database called "auth". Did you accidently mix up some of the environment variables, maybe DB0X_USER and DB0X_NAME?

tiredofit commented 4 months ago

Have a peek at the https://github.com/tiredofit/docker-db-backup#postgresql DEFAULT_AUTH option which may give you opportunities to specify a specific auth db.

mlause commented 4 months ago

@tiredofit DEFAULT_AUTH worked with the postgresql only instance. Thank you. The default database created is called postgres, so that is what I used.

@ToshY "auth" is my DB01_USER. For DB01_NAME I have it set to ALL.

Ideally, I would love to have one instance of db-backup running. I have postgres and mariadb with at least 4 databases in each. Is DEFAULT_AUTH tied to all database hosts? if not, I should be good. If so, how can I set DEFAULT_AUTH to only be used on DB01/postresql?

tiredofit commented 4 months ago

You should just be able to use DBXX_AUTH (01/02/03) for that specific set of backup configuration. The introduction of multiple databases being able to be backed up brought in some murkiness in terms of configuration, this is one of them that isn't entirely clear from the outset.

mlause commented 4 months ago

DB01_AUTH worked! Thank you for your help.

JW-CH commented 3 months ago

Hi, I have the same issue in my setup...

My Setup: DB01 -> MariaDB DB02 -> Postgres

DEFAULT_SPLIT_DB=true
(I have some more DEFAULT_XX variables but only for time, log level etc, shoudl not matter here)
DB01_NAME=ALL
DB01_TYPE=mysql
DB01_HOST=192.168.0.10
DB01_USER=root
DB01_PASS=xxx

DB02_NAME=ALL
DB02_TYPE=pgsql
DB02_HOST=192.168.0.10
DB02_USER=backup_worker
DB02_PASS=xxx

Logs:

# backup02-now
2024-06-10.10:06:21 [INFO] ** [db-backup02] Starting Manual Backup for db-backup02
psql: error: connection to server at "192.168.0.10", port 5432 failed: FATAL:  database "backup_worker" does not exist
2024-06-10.10:06:21 [INFO] ** [02-192.168.0.10__ALL] Backup 02 routines finish time: 2024-06-10 10:06:21 CEST with exit code 0
2024-06-10.10:06:21 [NOTICE] ** [02-192.168.0.10__ALL] Backup 02 routines time taken: Hours: 0 Minutes: 00 Seconds: 00

Do I need to set DBXX_AUTH? If yes, to what value?

The setup works, if I change the DB02_NAME to an actual DB.

Iceman248 commented 2 months ago

Hi, I have the same issue in my setup...

My Setup: DB01 -> MariaDB DB02 -> Postgres

DEFAULT_SPLIT_DB=true
(I have some more DEFAULT_XX variables but only for time, log level etc, shoudl not matter here)
DB01_NAME=ALL
DB01_TYPE=mysql
DB01_HOST=192.168.0.10
DB01_USER=root
DB01_PASS=xxx

DB02_NAME=ALL
DB02_TYPE=pgsql
DB02_HOST=192.168.0.10
DB02_USER=backup_worker
DB02_PASS=xxx

Logs:

# backup02-now
2024-06-10.10:06:21 [INFO] ** [db-backup02] Starting Manual Backup for db-backup02
psql: error: connection to server at "192.168.0.10", port 5432 failed: FATAL:  database "backup_worker" does not exist
2024-06-10.10:06:21 [INFO] ** [02-192.168.0.10__ALL] Backup 02 routines finish time: 2024-06-10 10:06:21 CEST with exit code 0
2024-06-10.10:06:21 [NOTICE] ** [02-192.168.0.10__ALL] Backup 02 routines time taken: Hours: 0 Minutes: 00 Seconds: 00

Do I need to set DBXX_AUTH? If yes, to what value?

The setup works, if I change the DB02_NAME to an actual DB.

I had to do the same thing. It thinks DB02_USER is a database name if I set it to backup all databases, but resolved when I list all the databases instead of saying ALL.

JanRossler commented 3 weeks ago

I confirm that the ALL databases option is broken. On MariaDB, the query to list database names gets malformed as visible here in debug log:

2024-08-27.09:40:03 [DEBUG] /etc/services.available/dbbackup-01/run ** [01-mariadb__ALL] Preparing to back up everything except for information_schema and _* prefixes
+ output_on
+ '[' true = true ']'
+ case "$(basename "$0")" in
++ basename /var/run/s6/legacy-services/dbbackup-01/run
+ case "$PWD" in
+ set -x
+ var_true ''
+ '[' '' = true ']'
+ '[' '' = yes ']'
++ run_as_user mysql -h mariadb -P 3306 -uroot --batch -e 'SHOW DATABASES;'
++ grep -v Database
++ s6-setuidgid dbbackup mysql -h mariadb -P 3306 -uroot --batch -e SHOW 'DATABASES;'
++ grep -v schema
ERROR 1049 (42000): Unknown database 'DATABASES;'
+ db_names=

For the time being I've worked around it by downgrading back to v3, which works correctly.

tiredofit commented 3 weeks ago

docker.io/tiredofit/db-backup:develop has been sent up to the registry which may help rejoin that SHOW DAATABASES command. Can you let me know if that is the case and I'll issue a new a release containing the fix?