pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedread-only remote replica
3+ messages / 3 participants
[nested] [flat]
* read-only remote replica
@ 2016-05-19 23:11 Jon Hancock <jhancock@shellshadow.com>
2016-05-20 01:30 ` Re: read-only remote replica Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2016-05-20 01:51 ` Re: read-only remote replica Ian Barwick <ian@2ndquadrant.com>
0 siblings, 2 replies; 3+ messages in thread
From: Jon Hancock @ 2016-05-19 23:11 UTC (permalink / raw)
To: pgsql-admin
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.
thanks, Jon
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: read-only remote replica
2016-05-19 23:11 read-only remote replica Jon Hancock <jhancock@shellshadow.com>
@ 2016-05-20 01:30 ` Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
1 sibling, 0 replies; 3+ messages in thread
From: Alvaro Aguayo Garcia-Rada @ 2016-05-20 01:30 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, Jon Hancock <jhancock@shellshadow.com>
Hi, Jon.
What postgres version are you using on your master database server? If you use(or can upgrade to) 9.4/9.5, you can consider using pglogical, which works by sending WAL from the master to the read-only slave. It's really efficient, as, unlikely the trigger based replicators, it does not double writes the replicated information. Here's he website: http://2ndquadrant.com/en/resources/pglogical/
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
Sent from my Sony Xperia™ smartphone
---- 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.
thanks, Jon
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: read-only remote replica
2016-05-19 23:11 read-only remote replica Jon Hancock <jhancock@shellshadow.com>
@ 2016-05-20 01:51 ` Ian Barwick <ian@2ndquadrant.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ian Barwick @ 2016-05-20 01:51 UTC (permalink / raw)
To: Jon Hancock <jhancock@shellshadow.com>; pgsql-admin
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-05-20 01:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-05-19 23:11 read-only remote replica Jon Hancock <jhancock@shellshadow.com>
2016-05-20 01:30 ` Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2016-05-20 01:51 ` Ian Barwick <ian@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox