We have a postgres database hosted on Azure, that we would like to refresh from a "on premise" postgres database.
I'm not sure, what would be the best way to do that?
Through an incremental backup then an incremental restore or through a Slony-I replication although I'm quite sure this feature do not exist for Azure?
Thanks
In general most DB as a service providers make it difficult to do replication to/from them to your own box so replication, at least native postgres replication, likely won't work.
That leaves you with going through replication solutions that are not so tighly integrated such as Slony, Bucardo, pgpool.
My advice would be to first describe your needs and then use that information to decide which approach would be easiest to maintain, which in my opinion is far more important than easy to install.
Do you need to have the data up to date at all times? Then you want some means of replication.
Can the data be behind hours or a day? You can use a process to get the data from the primary to the Azure machine without replication.
Does the data gets updates or is it insert only? Insert only tables are far easier to replicate.
Do you have, or can setup, insert_date and last_update dates/timestamp fields? If you have a way to know what is new and/or updated since your last migration you can do your own process with the advantage that it will work regardless of what provider you use. One, potentially big, disadvantage is that you have to manage this yourself for every single table. If your environment is mostly mature and you are not adding new tables often, this is one potential method.
Is the data for some, or all, of the tables coming from some external source? In a case like that you can double load, were each source gets loaded on both places. Biggest disadvantage is that you have to replicate your processes for the load and monitoring as well as coming up with a way to make sure there are no discrepancies.
Usually the deciding factor for this type of designs are how far behind the data can be and everything, for the most part, usually is based on that.
The other important factor is size. If you are talking a handful of MBs then the process is trivial, no matter which way you go. If you are talking Terabytes then incremental or replication is an absolute must.
