I have a 300GB database that is growing around 5GB a day. It stores time-based data, and as such, I currently have around 60 days of data. My main database server has a 450 GB NVMe, so I want to get a secondary server that has expandable cheap storage for old data.
The main server only really needs the last 15 days of data. So anything older than 15 days of data I want to move off to the secondary database which has slower but cheap storage. I think standard backup or archiving isn’t viable because I want to query the data at any given moment (maybe ~100 queries a day).
I want to kill two birds one stone here and I thought if I setup replication to the secondary server and then somehow indicate the cleanup stored procedure which delete records older than 15 days doesn’t propagate deletes to the slave.
Is this possible and recommended? If not, what is the best way to accomplish archived the data but to another server and allow querying. I suspect partitioning all of my tables but not all of my tables are time-based so I would need some way to move the time-based partitioned data over along with any FKs needed for it
PS: I won’t be relying on the secondary server as a backup but rather my main replication. I would then do offsite backups of the secondary server periodically