We have a few tables with a pretty large number of entries that sometimes need to be re-imported. Only some tables are concerned, so we don't use restore but a command similar to this:
heroku pg:psql --app ourapp HEROKU_POSTGRESQL_WHITE < data.sql
This takes roughly 30min, mainly due to data upload (about 1GB).
Until now we've put the app in maintenance mode to import the new data, but we'd like to avoid the long downtime in the future.
What would be the best way to achieve this in Heroku?
Our first thought to reduce downtime was to find a way to run the command from a server that will have much better upload speed, but it's still not perfect.
We've thought of using followers but some other tables need to be written to when users are interacting with the app, and we're not sure if the app can be told to fall back on followers even if the master db doesn't have issues.
We've also thought of entirely caching all relevant tables while we're uploading new data, and then clearing that cache, but Heroku doesn't seem to give enough control on the cache to achieve that.
Import into a temporary second table, and then drop first table, and rename second one in a transaction.