Thread: advice on Replication for a Specific Scenario

advice on Replication for a Specific Scenario

From
"Donald Kerr"
Date:
Hopefully this question is posted in the correct list. Apologies if that's not the case.
 
It's quite a simple question but I am not sure of the answer.
 
The scenario is a server (master database - PostgreSQL/PostGIS) containing gis information (lots of it - Maybe up to 80Gb in about 25 tables). This serves maps to clients across an internal intranet. There are also 200 clients (maybe up to 400 in the future) that are mobile, i.e. in vehicles, each with there own readonly copy of PostgreSQL/PostGIS serving maps locally on the vehicles. If I update tables with fresh information on the master, on a quarterly basis, I would like these updates to be trickled out to the copies that exist on the vehicles. The vehicles are on wireless lan for about 80% of the time but are out and about for the other 20%.
 
Any hints and tips would be appreciated.
 
Regards,
 
Donald
 
 

Re: advice on Replication for a Specific Scenario

From
Greg Sabino Mullane
Date:
On Mon, Feb 27, 2012 at 09:38:50PM -0000, Donald Kerr wrote:
> Hopefully this question is posted in the correct list. Apologies if that's
> not the case.

This is a fairly inactive list. You might have better responses on
pgsql-general@postgresql.org, but I will take a swing at things.

> The scenario is a server (master database - PostgreSQL/PostGIS) containing
> gis information (lots of it - Maybe up to 80Gb in about 25 tables). This
> serves maps to clients across an internal intranet. There are also 200
> clients (maybe up to 400 in the future) that are mobile, i.e. in vehicles,
> each with there own readonly copy of PostgreSQL/PostGIS serving maps locally
> on the vehicles. If I update tables with fresh information on the master, on
> a quarterly basis, I would like these updates to be trickled out to the
> copies that exist on the vehicles. The vehicles are on wireless lan for
> about 80% of the time but are out and about for the other 20%.

You are only applying changes to the master every quarter? The easiest solution
is to apply those changes on the readonly databases as well, e.g. with a
single file full of SQL commands. However, if you need something more automatic,
your best bet for partial replication is Slony or Bucardo. If by "trickle"
you mean that the client may get disconnected before it can completely sync
with the master, then Slony is your best bet, as Bucardo will want to make
all changes at once while Slony will attempt to "replay" the actual changes.

It's hard to say more however, without more details on your situation and
requirement. Feel free to ask here or -general. I suspect that everyone
on this list reads -general anyway. :)

--
Greg Sabino Mullane greg@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8

Attachment

Re: advice on Replication for a Specific Scenario

From
"Donald Kerr"
Date:
Thank you Greg.

I will have a look at Slony in the first instance. I have a great deal of
preparatory work to do before I look at it in detail.

Thanks again.

Regards,

Donald


-----Original Message-----
From: pgsql-cluster-hackers-owner@postgresql.org
[mailto:pgsql-cluster-hackers-owner@postgresql.org] On Behalf Of Greg Sabino
Mullane
Sent: 10 March 2012 03:11
To: Donald Kerr
Cc: pgsql-cluster-hackers@postgresql.org
Subject: Re: [pgsql-cluster-hackers] advice on Replication for a Specific
Scenario


On Mon, Feb 27, 2012 at 09:38:50PM -0000, Donald Kerr wrote:
> Hopefully this question is posted in the correct list. Apologies if
> that's not the case.

This is a fairly inactive list. You might have better responses on
pgsql-general@postgresql.org, but I will take a swing at things.

> The scenario is a server (master database - PostgreSQL/PostGIS)
> containing gis information (lots of it - Maybe up to 80Gb in about 25
> tables). This serves maps to clients across an internal intranet.
> There are also 200 clients (maybe up to 400 in the future) that are
> mobile, i.e. in vehicles, each with there own readonly copy of
> PostgreSQL/PostGIS serving maps locally on the vehicles. If I update
> tables with fresh information on the master, on a quarterly basis, I
> would like these updates to be trickled out to the copies that exist
> on the vehicles. The vehicles are on wireless lan for about 80% of the
> time but are out and about for the other 20%.

You are only applying changes to the master every quarter? The easiest
solution
is to apply those changes on the readonly databases as well, e.g. with a
single file full of SQL commands. However, if you need something more
automatic,
your best bet for partial replication is Slony or Bucardo. If by "trickle"
you mean that the client may get disconnected before it can completely sync
with the master, then Slony is your best bet, as Bucardo will want to make
all changes at once while Slony will attempt to "replay" the actual changes.

It's hard to say more however, without more details on your situation and
requirement. Feel free to ask here or -general. I suspect that everyone
on this list reads -general anyway. :)

--
Greg Sabino Mullane greg@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8