I am trying to implement “atomic” database swap for DB restore operation from a backup. My current design is the following:
I have an existing database
a that has been dumped into a file. When I do DB restore from a file, I create a new database
b and load data from DB dump into the database
b. When data are loaded and I have 2 consistent databases
b I am trying to do following transaction:
./cockroach sql --insecure -e "BEGIN; SAVEPOINT cockroach_restart; ALTER DATABASE a RENAME TO a_old ; ALTER DATABASE b RENAME TO a; RELEASE SAVEPOINT cockroach_restart; COMMIT;"
I’d like to confirm that this is the right way to achieve “atomic” database swap in a single transaction that doesn’t leave a room for inconsistent state.