From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200509200010.j8K0As802875@candle.pha.pa.us>
Subject: Re: Replication
In-Reply-To: <1126629787.3026.77.camel@coppola.muc.ecircle.de>
To: Csaba Nagy <nagy@ecircle-ag.com>
Date: Mon, 19 Sep 2005 20:10:53 -0400 (EDT)
Cc: Russ Brown <pickscrape@gmail.com>,
	Postgres general mailing list <pgsql-general@postgresql.org>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


Added to TODO:

	* Allow WAL traffic to be steamed to another server for stand-by
	  replication

---------------------------------------------------------------------------

Csaba Nagy wrote:
> Well, AFAICT this kind of replication in postgres is not be named "out
> of the box". Setting up the whole thing to work, and test it to really
> work reliably is not exactly trivial, and you could have a number of
> surprises (like when there's little activity, the last WAL stays at the
> server and so the stand-by can be off by a considerable amount of time,
> even if in terms of data quantity the difference is not that big). You
> must master well scripting to write reliable scripts to handle the
> process, though I think there are some examples around, but you still
> need to customize them for sure.
> 
> What is missing is an easy way to set up the hot stand-by, without
> requiring the user to do too much side-work. 
> 
> I wonder if there is a way to set up a direct link between the standby
> and the server and instead of copying files just send directly the WAL
> data over by some background process ? The overhead of sending the data
> directly should be similar with copying the files. Then the easiest way
> to set up a standby would be to start up the stand-by server with some
> standby options and giving the server's connection params, and then the
> standby should make a special connection to the server requesting a data
> dump + WAL from the data dump start on. Would this be reasonably simple
> to implement ? I'm not at all familiar with postgres code or the C
> language, so this is just wishful thinking.
> 
> Cheers,
> Csaba.
> 
> 
> 
> On Tue, 2005-09-13 at 17:45, Russ Brown wrote:
> > Simon Riggs wrote:
> > > Barry,
> > > 
> > > You can use PITR to archive transaction logs to a second server that is
> > > kept in standby mode.
> > > 
> > > This will cope with any number of tables and cope with dynamic changes
> > > to tables.
> > > 
> > > This is fairly straightforward and very low overhead.
> > > Set archive_command to a program that transfers xlog files to second
> > > server.
> > > Then set restore_command on the second server to a program that loops
> > > until the next file is available.
> > > Switchover time is low.
> > > 
> > 
> > Apologies for going slighly off topic, but isn't this basically how 
> > MySQL does replication? I ask because one of the arguments against 
> > moving to PostgreSQL in my organisation is 'no replication out of the 
> > box'. But if the above is true it seems that all that is required are a 
> > couple of scripts to handle log transfer and you have a form of 
> > replication out of the box right there.
> > 
> > Or am I missing something?
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073