Skip site navigation (1) Skip section navigation (2)

Re: Setting up streaming replication w/ a big ole database

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>, "Wells Oliver" <wellsoliver(at)gmail(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Setting up streaming replication w/ a big ole database
Date: 2012-04-10 19:11:10
Message-ID: 13762.1334085070@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Wells Oliver <wellsoliver(at)gmail(dot)com> wrote:
>> I admit to being scared as crap of rsync'ing a live database to
>> another server. Like chills are running down my spine even typing
>> it. Is this an approved, safe thing?
 
> It is fine, as long as you're doing it between the pg_start_backup()
> and pg_stop_backup() calls.  We do it on 100 production databases
> every week.  It is approved, documented, and safe.

Right.  It is true that the rsync alone will not give you a consistent
copy if the database is actively being modified.  The secret sauce is
in having a copy of all the WAL that was generated while the rsync
happened.  Replaying that against the copied data will apply (or
reapply) any changes that occurred meanwhile, thus fixing the
inconsistencies.

(I find that people are much more likely to believe that this works
once they grasp the principle.)

			regards, tom lane

In response to

pgsql-admin by date

Next:From: Kevin KeithDate: 2012-04-10 22:12:53
Subject:
Previous:From: Robert BurgholzerDate: 2012-04-10 18:56:50
Subject: Re: Setting up streaming replication w/ a big ole database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group