Node db backup

[rmica]

Has anyone used rsync to backup a node/wallet box with live working db? Do I need to stop services or is it ok to backup live?
Thanks.

[stefan-lange]

I had no time to create a robust backup script with rolling backups and timestamp etc.

But here is what I use for live copies of the db:
sqlite3 -line /path/to/farmer/data/mainnet/db/blockchain_v1_mainnet.sqlite "VACUUM main INTO '/backup/farmer/data/mainnet db/blockchain_v1_mainnet.sqlite.bak';"

It copies all data from one db file blockchain_v1_mainnet.sqlite to another db file blockchain_v1_mainnet.sqlite.bak.

Restore:

• stop app / service
• delete (or move to another folder) the original sqlite file including -shm and -wal
• copy blockchain_v1_mainnet.sqlite.bak to blockchain_v1_mainnet.sqlite
• start app / or service

[rmica]

Unfortunately it takes db-file-size * 2 disk space to run a node (I guess that 'INTO' wants a true file and doesn't accept a fifo path, if I'm wrong that could solve the problem). Given that in many cases the db can recover from a process or computer crash, could it be an option to rsync the file to a recipient box, verify the copy on the recipient and eventually repeat the copy if corrupted and try again?
Are you asking why not a network file system? I tend to optimize network traffic, whenever possible, given that rsync and NFS have the same disk requirements for a backup.

[stefan-lange]

I personally use this command to backup the db every 4 weeks and compress it in a xz file. In case of a desaster recovery I won't need to wait days for the sync. But that's enough for the backup. In the worst worst worst case I do a full re-sync.

Space... I use the unused space on the hard disks with the plots. That works for me.

[rmica]

Ok. I'll opt for NFS. Thanks for the vacuum info, it's useful.

[hajes]

if you do not stop chia services, database/blockchain will be corrupt !!!

I have simple script that stops chia services...copy blockchain+wallet to ext disk. restart services.

Move ext disk to my workstation where compression takes about 30min (my server runs old HW where it takes hours to compress it...not to mention, chia eats too much space...no space left for copy of blockchain anymore)

[stefan-lange]

That is true, if you COPY the database, but it is wrong, if you use "VACUUM INTO" or the chia db backup command, which uses vacuum into under the hood

see https://www.sqlite.org/lang_vacuum.html

[hajes]

Rule #1 - NEVER backup life system
Rule #2 - if you do not know how it works > follow rule #1

Is it true SQL standard or Chia proprietary? I do not know, do I have lots of spare time to learn about it?

It takes <20min downtime, which costs me about 40$ with 100% certainty it will work. Why gamble? ;-)

[stefan-lange]

Ok, you have definitely not read anything. Apply your method and everything will be fine. If people don't want downtime, they can read.

[hajes]

better 20min downtime once in 2 months, than a half year downtime while server resync due to incompatible blockchain DB :-P