I have a Postgres database running a small website. Most of the tables in the database are calculated values derived from various input data. This input data is updated approximately monthly. The tables in question are not otherwise altered.
What I need to do each month is:
- acquire the new input data (mostly downloading from a few sources)
- use the new data to compute new calculated values for the db tables. Only a smallish fraction (< 5%) of the records will actually change with each update
- replace the existing tables in my production db with the newly-calculated tables
Although it would be convenient, I don't feel it is prudent to do step 2 on the actual production server (the calculations are a bit processor heavy, and I don't want to bog it down). That seems to leave me with carrying out steps 1 & 2 elsewhere and then somehow transferring the new data to the production server. Right now that "elsewhere" is simply on my local machine that has its own Postgres db, but it might end up being something like an EC2 instance.
Is there a smart way to carry out step 3 without resorting to full data dumps? At this point my db is <2GB and a data dump is feasible, but the data calculated may expand significantly, and transferring only the changed records will likely become more important.
I'm hoping there is a generally-accepted answer to this question, although I can see that it has the potential for subjectivity and, thus, might not be the ideal fit for this forum. I'm sorry if that's the case - I'm hoping to learn from the folks here and am not sure where else to ask.