Postgres replication: dump/restore, PITR, Slony,...?

From: Shaul Dar <shauldar(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Postgres replication: dump/restore, PITR, Slony,...?
Date: 2009-06-11 13:12:32
Message-ID: 234efe30906110612p46bfe848x2b790489c1999d55@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

Our configuration is as follows:

1. A staging server, which receives new data and updates the DB
2. Two web servers that have copies of the DB (essentially read-only) and
answer user queries (with load balancer)

Currently we use dump (to SQL file, i.e. pg_dump with no args) + file copy
to replicate the DB daily between the staging and web servers, and then
restore (via psql) the servers one at the time. In our application we expect
that average daily change is only to 3% of the records. My question is what
would be the best way to do this replication?

I read about continuous archiving and
PITR<http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html>.
My understanding however (e.g. from
this<http://archives.postgresql.org/pgsql-admin/2006-07/msg00279.php>)
is that I cannot do a base backup once and then e.g. apply WAL files on a
daily basis, starting from yesterday's DB, but must instead redo the full
base backup before starting recovery?

Then there are Slony-I, Buchardo, Mamoth Replicator from CMO, simple
replication in Postgres 8.4 and other projects...

Suggestions?
Thanks,

-- Shaul

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2009-06-11 13:27:36 Re: GiST index performance
Previous Message Matthew Wakeling 2009-06-11 12:37:54 Re: GiST index performance