The best way to expand database hard drive?

My server has a 1T HDD, it has used over 700G, I want to add a 2T SSD and import the current data into a new database.
To reduce downtime, should I first build the database on the SSD, modify the database connection of the ignition, and finally import the data on the HDD into the new database? I don’t know databases and have never done a data import. But 700G data is quite a lot, I am not sure whether such an operation is feasible and safe, is there any relevant example?
Thanks for anything.

If you are running Linux, and the DB’s tablespace is on its own LVM logical volume, you can add the new drive, add the drive to LVM, and pvmove your tablespace volume onto it. Once present on the larger driver, the LV and its filesystem can be grown to use more space. If you have a hot-swap bay, all of the above can be done with zero downtime.

If you aren’t using Linux with LVM, your choices are more limited, and downtime is likely to be substantial. Setting up a completely new DB server might be the best choice, and migrate the data in bulk to get them close to identical before taking any downtime. If you do set up a new server, you should consider using Linux with LVM on a platform with hot-swap bays so you can avoid this problem in the future.

Unfortunately, I’m not using Linux, and I can’t use Linux as an OS for company reasons. Maybe next time I should deploy a Linux virtual machine.

thanks.

The techniques described are much less useful in VMs. Linux as the hypervisor is the ideal for storage flexibility without downtime. Your company’s ban on Linux seems unwise to me.

2 Likes

Assuming its windows, I done something similar, 500gb’s worth, took a little while. May not be the best way to go about it but it worked for me. Also, my system isn’t a production environment and I use mysql.

I installed the new hard drive, installed the mysql software, disabled the current DB in the gateway so nothing was reading or writing to it, took a backup of the existing DB. I use navicat to manage my DB’s but Mysql has a migrate tool in the workbench so depends what database your using. Once you have the backup, restore that to the new database/hard drive. Set up a new connection in ignition config and away you go (you may need to rename the old database so you can name the new one the same). You could also use one of the various HD clone tools out there and clone the entire HD to the new one. Either way you should disable the current DB so nothing is reading/writing to it. Ignitions store and forward system may kick in when the existing HD gets disabled.

Plus this way, your not touching your current DB, so all goes belly up you can re-enable the connection and carry on.

This also depends where you have the database software installed, as it will be looking at a certain location for the tables. IE, software on C drive but the tables on D drive for example. As above, may not be the best way to go about it, but worked for me.

Edited, your also going to need somewhere to store all this data during the transaction (dump or clone), unless your database software has a migrate tool where it can copy directly.

1 Like