Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id EACBF189E83A for ; Tue, 10 Apr 2012 16:11:24 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SHgTM-0006A0-2l for pgsql-admin@postgresql.org; Tue, 10 Apr 2012 19:11:24 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q3AJBAZg013763; Tue, 10 Apr 2012 15:11:10 -0400 (EDT) To: "Kevin Grittner" cc: "Scott Marlowe" , "Wells Oliver" , pgsql-admin@postgresql.org Subject: Re: Setting up streaming replication w/ a big ole database In-reply-to: <4F843A7D0200002500046D87@gw.wicourts.gov> References: <4F843A7D0200002500046D87@gw.wicourts.gov> Comments: In-reply-to "Kevin Grittner" message dated "Tue, 10 Apr 2012 13:49:49 -0500" Date: Tue, 10 Apr 2012 15:11:10 -0400 Message-ID: <13762.1334085070@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201204/46 X-Sequence-Number: 39382 "Kevin Grittner" writes: > Wells Oliver 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