Thread: replication

Hi

We have a 100GB database (16GB dumped) running on 8.2.

Since the bandwidth in South Africa isn't that freely available it is
difficult for us to get a new copy of out DB in our office (our fastest
link in the office is 4Mbps).

What can you recommend for me to get an up to date copy of my DB in the
office with as little transfer as possible.

A few ideas I've been toying with are rsyncing a dump of the db, or
replication.

Any ideas?

--
Adrian Moisey
Systems Administrator | CareerJunction | Your Future Starts Here.
Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za
Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842

On Tue, June 24, 2008 8:41 am, Adrian Moisey wrote:
> Hi
>
> We have a 100GB database (16GB dumped) running on 8.2.
>
> Since the bandwidth in South Africa isn't that freely available it is
> difficult for us to get a new copy of out DB in our office (our fastest
> link in the office is 4Mbps).
>
> What can you recommend for me to get an up to date copy of my DB in the
> office with as little transfer as possible.
>
> A few ideas I've been toying with are rsyncing a dump of the db, or
> replication.
>
> Any ideas?

Give Slony (http://www.slony.info/) a try - the initial replication of the
entire DB takes a while, thereafter it only replicates changes (as they
happen).

We've been using it successfully (it has it's occasional problems like all
things) for over a year now replicating across a cluster (DB is also about
100GB+).

Regards
Henry

Hi

Take a look also on Londiste from SkyTools. It is easy to set up and manage. In addition SkyTools contains other useful scripts and tools.
We use Londiste to replicate data ovwe WAN where walshipping would consume too much bandwidth.

Asko

Initially you may want to get the full dump (this applies also to the
replication option) across to your other site. You may want to bzip2 the
dump file, you may be surprised by the reduction of file size using
bzip2. Try "tar -cjf mydump.sql.tar.bz2 mydump.sql" then transfer it
using scp or place it on an ftp server in your local office, then login
remotely (ssh) to a pc/server on your destination office then issue
"wget -c -t0 http://myftpsite/path/to/mydump.sql".


Allan.

Asko Oja wrote:
> Hi
>
> Take a look also on Londiste from SkyTools. It is easy to set up and
> manage. In addition SkyTools contains other useful scripts and tools.
> We use Londiste to replicate data ovwe WAN where walshipping would
> consume too much bandwidth.
>
> Asko
>
> On Tue, Jun 24, 2008 at 9:41 AM, Adrian Moisey
> <adrian@careerjunction.co.za <mailto:adrian@careerjunction.co.za>> wrote:
>
>     Hi
>
>     We have a 100GB database (16GB dumped) running on 8.2.
>
>     Since the bandwidth in South Africa isn't that freely available it
>     is difficult for us to get a new copy of out DB in our office (our
>     fastest link in the office is 4Mbps).
>
>     What can you recommend for me to get an up to date copy of my DB
>     in the office with as little transfer as possible.
>
>     A few ideas I've been toying with are rsyncing a dump of the db,
>     or replication.
>
>     Any ideas?
>
>     --
>     Adrian Moisey
>     Systems Administrator | CareerJunction | Your Future Starts Here.
>     Web: www.careerjunction.co.za <http://www.careerjunction.co.za> |
>     Email: adrian@careerjunction.co.za
>     <mailto:adrian@careerjunction.co.za>
>     Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686
>     6842
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>

On Wed, Jun 25, 2008 at 01:52:00PM +0200, Allan Kamau wrote:
> Initially you may want to get the full dump (this applies also to the
> replication option) across to your other site.

Most of the replication systems I've seen require that _they_ send the
data to the remote site, so copying the data yourself is actually a
waste of bandwidth, since it's all going to get copied anyway.  (They
have to do this because that's how they know that they're starting
from a point in time for which they have consistency information.)

You can indeed save on bandwith, however, by tunnelling your postgres
connections through an ssh tunnel that's using compression.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/