Save to directly rsync dbase?

[shaderecker]

@deHakkelaar brought this up in Discourse

I noticed you do a one on one rsync of the gravity dbase file:

ansible-pihole/roles/sync/templates/pihole_sync.j2

Lines 5 to 12 in d6b2f53

	if [[ $(ip a | grep {{ pihole_vip_ipv4.split('/')[0] }}) ]]; then
	RSYNC_GRAVITY=$(rsync -a --info=name -e "ssh -i {{ ssh_key.filename }}" {{ ansible_user_dir }}/pihole/pihole/gravity.db {{ ansible_user }}@{{ sync_target }}:{{ ansible_user_dir }})
	if [ $? -eq 0 ]; then
	if [ -n "$RSYNC_GRAVITY" ]; then
	ssh -i {{ ssh_key.filename }} {{ ansible_user }}@{{ sync_target }} cp {{ ansible_user_dir }}/gravity.db {{ ansible_user_dir }}/pihole/pihole
	DOCKER_RESTART=1
	fi
	fi

Wouldn't that potentially also rsync the dbase file that might still be in transition performing some UPDATE query or something else ?
Isnt it safer to dump the dbase instead on the "primary" and copy the dump over to the "secondaries" eg:

sqlite3 /etc/pihole/gravity.db ".backup main gravity.db.dump"

And restore/import on the secondary hosts if checksum check (md5 or whatever) has changed with:

sudo -u pihole sqlite3 /etc/pihole/gravity.db ".restore main gravity.db.dump"

Would need to stop pihole-FTL to prevent tables being locked while importing.

[deHakkelaar]

Both rsync and the gravity pull could run at the same time which could result in tables still being in transition while the file already is being rsynced.
Am not sure if its safe to copy a dbase file with rsync this way.
I believe the recommended way is to dump and import again at the other end when concerning dbases.
Or some other way to sync via SQL.

[shaderecker]

Thanks again for the suggestions. I actually didn't think about that case yet.
I will have a look and adapt my script.

[shaderecker]

Finally came around to implement this in #7 😁