Re: Replication - Mailing list pgsql-general

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