On 05/20/2016 08:11 AM, Jon Hancock wrote: > Hi list, > I need a read only replica of my postgres server (assume > current/recent stable release). > The master is in a reliable data center in California. The read-only > replica would be in an office in New Zealand. Bandwidth can be > challenging from our office. It works but bandwidth is highly > variable. > > The reason for the read-only replica is we have an old reports system > in our office that connects directly to the db via ODBC. Simply too > much work to replace this system for now. Its not an option to do > nightly dumps/download/imports as we need closer to real time replica. > A few minutes delay is ok. > > Any thoughts would be appreciated on how to have a stable/reliable method. In a previous job I worked with servers running Postgres in different locations, including New Zealand (have they upgraded the modem used to connect to the rest of the world yet? ;) ) - if you can live with occasional lag, streaming replication works pretty well. However it does of course depend on how much data you'll be receiving - any idea how many WAL files your master generates in a given period? One issue to look out for with intercontinental connections is occasional problems with routing, some router goes down between you and the data centre and there's nothing you can do but wait until it's fixed and the route gets updated. To work around that you could set up WAL archiving using something like Barman [*] which ships WAL to a different datacentre and set up your replica to pull files from there if streaming replication is not available. [*] http://www.pgbarman.org/ Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
