Teslamate: Backup database in cron results in 0 byte backup

[mrwolf78]

I am aware that this is possibly a cron/docker or Raspberry Pi issue, but I'm hoping you can help me out.

I've made a database backup-script that runs fine when I execute it with the pi or root user. However, when I schedule the backup in cron, a zero byte file is generated. Cron log doesn't say anything, and I can't find a solution.

Script (teslamate_backup.sh):
#!/bin/sh PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin cd /home/pi/teslamate /usr/local/bin/docker-compose exec db pg_dump -U teslamate teslamate > /home/pi/backups/teslamate.bck
Cron-job:
sudo sh /home/pi/scripts/backup_teslamate.sh

Any help is appreciated (and can maybe added to the documentation)

[Arceuid731]

Hey,

docker-compose won't work through a cron because it needs a tty.

You should use this instead which will work like a charm through a cron :
docker exec teslamate_database_1 pg_dump -U teslamate teslamate > mybackup.bck

[DrMichael]

Well, not really, -T prevents that:

docker-compose exec -T database pg_dump -U teslamate teslamate > teslamate.bck

[mrwolf78]

Unfortunately, still a 0 byte file :(

[tucopup]

This command works on my synology as a scheduled user defined script. Three differences; I cd to the folder 1st (don't know if it matters), then issue the backup command; I write the output file to my user folder that I can see from my desktop (possible write permission problem?); and my database is db from the original setup:

docker-compose exec -T db pg_dump -U teslamate teslamate > /volume1/Tom/Teslamate/teslamate.bck

[DrMichael]

My complete crontab command is:

(cd /home//teslamate; mv teslamate.bck teslamate.bck~; docker-compose exec -T database pg_dump -U teslamate teslamate > teslamate.bck)

[mrwolf78]

I really don't get it; I run exactly your command (I only have to replace database with db for some reason).
I run it in Putty: all fine, 19MB backup file
I run it in cron: 0 bytes

[tucopup]

my script runs as root, if that helps.

[mrwolf78]

It did, I added a sudo in the script et voila! Thanks!
For future reference, this is the line that works for me:

cd /home/pi/teslamate; mv teslamate.bck teslamate.bck~; sudo docker-compose exec -T db pg_dump -U teslamate teslamate > teslamate.bck

[DrMichael]

Perhaps better to have all your files in teslamate be owned by you?

Am Sa., 28. März 2020 um 18:05 Uhr schrieb mrwolf78 <
[email protected]>:

It did, I added a sudo in the script et voila! Thanks!
For future reference, this is the line that works for me:

cd /home/pi/teslamate; mv teslamate.bck teslamate.bck~; sudo
docker-compose exec -T db pg_dump -U teslamate teslamate > teslamate.bck

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/adriankumpf/teslamate/issues/453#issuecomment-605489510,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AB5WRAUNMQAM3IEBNY6KX4TRJYU5XANCNFSM4KVZKXCA
.

--
Dr. Michael Schmidt
Höllsteinstraße 30
61350 Bad Homburg

Telefon [phone]
Mobil 0171 1257249
Mail [email protected]

[bahree]

So question on when running the cron job - do you shutdown Teslamate container and then run the backup? If not won't there be a possibility of corruption - if it has a lock when trying to commit. Or does pg_dump internally handle it?

I also want to add a date-time stamp to the backup file and then copy it over to my NAS - anyone else done that?

Thanks.

[mrwolf78]

I don't know about locks or corruption, I run the backup at a time the car is most likely asleep (3am), and I use one script to backup several docker containers to my NAS:

    #!/bin/bash

    ## LOCAL/FTP/SCP/MAIL PARAMETERS
    SERVER="192.168.XX.XX"         # IP of Network disk, used for: ftp mail scp
    USERNAME="XXXX"         # FTP username of Network disk used for: ftp mail scp
    PASSWORD="XXXX"               # FTP password of Network disk used for: ftp mail scp
    DESTDIR="/opt/backup"      # used for: local
    ## END OF USER CONFIGURABLE PARAMETERS

    TIMESTAMP=`/bin/date +%Y-%m-%d-%H%M`
    BACKUPFILEDIR="docker_backup_$TIMESTAMP.tar.gz" # Change the xxx to yours

   ### Create backup file from docker ###
PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin
cd /home/pi/teslamate
sudo /usr/local/bin/docker-compose exec -T db pg_dump -U teslamate teslamate > /home/pi/backups/teslamate.bck

    ### Create backup and ZIP it
    tar -zcvf /tmp/$BACKUPFILEDIR /home/pi/backups/   # Change the xxx to yours   

    ### Send to Network disk through FTP
    curl -s --disable-epsv -v -T"/tmp/$BACKUPFILEDIR" -u"$USERNAME:$PASSWORD" "ftp://$SERVER/backup/docker_pi/"  # Change the ftp to yours !!!

    ### Remove temp backup file
    /bin/rm /tmp/$BACKUPFILEDIR

    ### Done!

[tucopup]

I modified my script in my Synology to append the date-time. The file is saved in a user area, so is included in backups. One file per day at 1AM. It will eventually add up...

cd /volume1/@docker
docker-compose exec -T db pg_dump -U teslamate teslamate > /volume1/Tom/Teslamate/teslamate.bck_date +%d%b%Y%H%M%S

[jmiverson]

I initially had the full date appended as well. Instead, I’ve decided to go with a “%A”, which outputs the day of the week. This way, I’ll have a running one week’s worth of backups and they overwrite after 7 days. I figure out of the 7 days, one of the backups will hopefully work.

[tucopup]

I see I lost the back quote character, my post above, before the date and after the %S in the paste.

I quite like the %A suggestion.

[natrlhy]

Curious if anyone has tested restoring yet? :)

[DrMichael]

Just did it today...

[natrlhy]

Just did it today...

Nice. Following the documentation seems straightforward. I guess a pre-requisite would be to get a base teslamate config setup then do the restore portion

[DrMichael]

That is what I did... Clean installation, then restore.

[DrMichael]

Now I have an issue: restoring just does not insert data into the tables.

I ran docker-compose exec -T database psql -U teslamate -d teslamate < backups/teslamate-Montag.bck, no messages (what is strange) and the tables are empty. The backup file looks good.

Any ideas?

OS: Ubuntu 18.04.4
docker-compose: 1.17.1

[DrMichael]

Very strange. I copied the backup into the container and read it in there. That worked.

[bahree]

My backup and restore was successful; as context this was running on a pi 4 to synology NAS - originally was running on the NAS and sometime back they had borked an update and since then it never worked and I never had time to get around to fully debug this.

I also used the opportunity to upgrade the db from v11 to v12.

[natrlhy]

Any full list of steps we could add to the documentation for backup and restore?

[DrMichael]

@natrlhy what are you missing?

[natrlhy]

So I got around to doing some test restores today finally...
Here's what I did:
I already had VMware Workstation on my Windows 10 PC.

Download the lastest Alpine Linux ISO and get it installed in a VM, etc
https://alpinelinux.org/downloads/
https://wiki.alpinelinux.org/wiki/Docker#Installation

Login to the VM:
root (no password on intial login to Alpine)
setup-alpine

Get it all setup:
    us keyboard
    us-alt-intl
    set timezone, etc
    save to sda
    sys
    ...
reboot

Login as root

adduser teslamate
echo "http://dl-cdn.alpinelinux.org/alpine/latest-stable/community" >> /etc/apk/repositories
apk update
apk add alpine-sdk docker py3-pip python3-dev libffi-dev openssl-dev gcc libc-dev make
rc-update add docker boot
service docker start
service docker status
pip3 install --upgrade pip
pip3 install docker-compose

Edit sudoers file:

vi /etc/sudoers

Add this line:
teslamate ALL=(ALL) ALL

exit
sudo -i
mkdir teslamate;cd teslamate

Create docker-compose.yml file:
https://docs.teslamate.org/docs/installation/docker

docker-compose up -d

Now when I went back to my "prod" install of teslamate, I did a no-no! I ran a docker-compose down. Yeah that deleted the containers :)
Of course I took a backup during my testing so whew I got to do a real restoration back on my Synology NAS server :D

I did set the postgres:12 on my Alpine VM during the test restore and things came up just fine. When I did my restore on my NAS, I tried using v12 but it wouldn't start postgres. Changed topostgres:11, restored, and all is back to normal!

In the end on the backup/restore doc maybe mention following the in the Restore section:
Configure your docker environment using the Simple or Advanced installation and proceed with the Restore section below

Excellent documentation though. Nice to see the docs back and looking good.

[natrlhy]

Is it normal to have a smaller DB dump when going from v11 to v12?

-rw-r--r-- 1 root root 66571688 Apr 11 16:02 teslamate.bck_04112020_031313
-rw-r--r-- 1 root root 41396224 Apr 11 16:05 teslamate_db.bck_6_Saturday_Apr

1st file is the dump from my NAS. 2nd one is from a new docker setup running v12 after importing it into the new setup

[cleanev]

I guess there is something up since I tried With -T as below
docker-compose exec _-T_ database pg_dump -U teslamate teslamate > teslamate.bck

Now even above command without -T throws this error
ERROR: No container found for database_1

Command from documentation without -T worked this morning. I even rebooted Pi3B+ and still no luck.

[DrMichael]

Now even above command without -T throws this error
ERROR: No container found for database_1

Make sure, that the database container ist runninig...

[cleanev]

Hi @DrMichael - i should have provided that detail about running containers, apologies for missing that information.
I just checked to reconfirm and here is the output for docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES b75e74b38eec portainer/portainer "/portainer" 12 hours ago Up 12 hours 0.0.0.0:8000->8000/tcp, 0.0.0.0:9000->9000/tcp portainer 7245be2bcac2 teslamate/teslamate:latest "/sbin/tini -- /bin/…" 3 weeks ago Up 12 hours 0.0.0.0:4000->4000/tcp docker_teslamate_1 51c7e848c2c9 eclipse-mosquitto:1.6 "/docker-entrypoint.…" 3 weeks ago Up 12 hours 0.0.0.0:1883->1883/tcp docker_mosquitto_1 18ada8dc7d53 postgres:12 "docker-entrypoint.s…" 3 weeks ago Up 12 hours 5432/tcp docker_database_1 52887f556fa0 teslamate/grafana:latest "/run.sh" 3 weeks ago Up 12 hours 0.0.0.0:3000->3000/tcp docker_grafana_1

[DrMichael]

You are in the directory with the docker-compose.yml and that file defines a container named "Database"?

[cleanev]

That’s affirmative. Below copied from docker-compose.yml file that is replica of documentation

[Edit] Everything works normally, as in I can open URL’s and graphs, except that backup will not take place anymore

```Version: "3"

services:
teslamate:
image: teslamate/teslamate:latest
restart: always
environment:
- DATABASE_USER=teslamate
- DATABASE_PASS=secret
- DATABASE_NAME=teslamate
- DATABASE_HOST=database
- MQTT_HOST=mosquitto
ports:
- 4000:4000
volumes:
- ./import:/opt/app/import
cap_drop:
- all

database:
image: postgres:12
restart: always
environment:
- POSTGRES_USER=teslamate
- POSTGRES_PASSWORD=secret
- POSTGRES_DB=teslamate
volumes:
- teslamate-db:/var/lib/postgresql/data

grafana:
image: teslamate/grafana:latest
restart: always
environment:
- DATABASE_USER=teslamate
- DATABASE_PASS=secret
- DATABASE_NAME=teslamate
- DATABASE_HOST=database
ports:
- 3000:3000
volumes:
- teslamate-grafana-data:/var/lib/grafana

mosquitto:
image: eclipse-mosquitto:1.6
restart: always
ports:
- 1883:1883
volumes:
- mosquitto-conf:/mosquitto/config
- mosquitto-data:/mosquitto/data

volumes:
teslamate-db:
teslamate-grafana-data:
mosquitto-conf:
mosquitto-data:
```

[DrMichael]

Out of ideas. The above command works fine on my P3B+ ...

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.