I have a live server running on postgres 9.5.
I would like to have an offsite replication of the same database hosted on a cloud server (Let's call this the standby server). We are okay with anywhere between 10 minutes to even 1 hour of delay between the live database and the standby database.
The live server is running on intranet but has access to the internet. There is no static IP address associated with the live server.
We want to use the standby server to allow other offices outside the country to access the same data albeit with acceptable delays. The other offices only has read access to the data on the standby server.
The standby server also acts as a hot backup to the live database.
I have found this article but it looks pretty brief. https://wiki.postgresql.org/wiki/Streaming_Replication
How do I set it up so that this can be achieved?
I would also like to know the impact of the setup if the internet connection is non-existent for a period of time (say, 5 seconds to even 20 minutes) between the live server and the standby server. This can happen occasionally as the live server is in a country where the infrastructure is not the best.
UPDATE with more research
There are multiple options for replication:
The most popular documented option is Transaction Log Shipping
Within that there are 2 options for standby: warm and hot
And then you can have a choice between 2 ways to replicate or a mixture of both:
- https://wiki.postgresql.org/wiki/Streaming_Replication
- file-based log shipping sending WAL (aka write-ahead log) records https://www.postgresql.org/docs/current/static/warm-standby.html
Implementation related questions to the bigger question of how to have a replication:
Assuming Transaction Log Shipping is chosen as the method,
- how to determine whether I should go for streaming or file-based log shipping?
- under what circumstances I should choose for both?
- how to determine minimum required bandwidth for outgoing internet connection at the live server site?
