I am in the process of implementing and automating lvm snapshot backups and restores. New to LVM and filesystem backups for databases.

After the snapshot I am archiving each database into a separate gzip file. I need to have the option of restoring any one database at a time.

My question is about the journaling piece. We do have it enabled, so according to your docs I don’t have to lock the server. Although I am assuming I would need to grab those journal files.

On the restore:

1. If I restore one database with the journal files as well.
a. Will this roll forward transactions on a database currently on the server NOT be restoring but with the same name from where the backup came from?
b. What happens to the CURRENT transactions on the server I am restoring to? Gone right? Unless you fsync and wait before the restore?
c. Is it ok to restore to a server where transactions in the journal are for a database not on the server I am restoring to?
d. Best practice> Should we be shutting down the server for a restore or can you just lock the server and copy the files over? On the shutdown, will the transaction in the journal roll forward over the newly restored database when the service is started?

I don’t have a problem just locking the server if this would get around all these issues. On both the backup and restore? Do I have to wait 60 seconds? I am fine with this as well its a hidden secondary and if it’s always 60 seconds it’s no problem.

Thanks!