Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aYLf0-00041N-3E for pgsql-admin@arkaria.postgresql.org; Tue, 23 Feb 2016 22:42:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aYLez-000487-M1 for pgsql-admin@arkaria.postgresql.org; Tue, 23 Feb 2016 22:42:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aYLey-00047y-Tb for pgsql-admin@postgresql.org; Tue, 23 Feb 2016 22:42:25 +0000 Received: from mail-bl2on0067.outbound.protection.outlook.com ([65.55.169.67] helo=na01-bl2-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aYLet-0001mF-IT for pgsql-admin@postgresql.org; Tue, 23 Feb 2016 22:42:24 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=journyx.onmicrosoft.com; s=selector1-journyx-com; h=From:To:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=w5VNPMoDslWXd9/CutNboXaWqNoUQ4uMfbzsPYscRx8=; b=hPBEUthRuCSQf0pCYMlVpkb19GnDf4Jg/7IakWSP6FxKB0QZrcUiQSH0JQNSeiUsPcmR4YTbAiqAtTQ0X3mVtVGPUvLTECawoNvHAMbQLQUkUUaSQfObzz6eg//zYTzHUSRHfxmLWoe3lIYgvw8ccFhIMiunysb91mFlGnEyhfY= Received: from BY1PR11MB0390.namprd11.prod.outlook.com (10.162.146.14) by BY1PR11MB0392.namprd11.prod.outlook.com (10.162.146.141) with Microsoft SMTP Server (TLS) id 15.1.409.15; Tue, 23 Feb 2016 22:42:14 +0000 Received: from BY1PR11MB0390.namprd11.prod.outlook.com ([10.162.146.14]) by BY1PR11MB0390.namprd11.prod.outlook.com ([10.162.146.14]) with mapi id 15.01.0409.024; Tue, 23 Feb 2016 22:42:14 +0000 From: Scott Whitney To: "David G. Johnston" , "drum.lucas@gmail.com" CC: "pgsql-admin@postgresql.org" Subject: Re: Incremental Refresh - PostgreSQL 9.2 Thread-Topic: [ADMIN] Incremental Refresh - PostgreSQL 9.2 Thread-Index: AQHRboXKnuQxIGPyVEKbzsLkuDvc3p86MhEAgAAHxP4= Date: Tue, 23 Feb 2016 22:42:14 +0000 Message-ID: References: , In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: authentication-results: gmail.com; dkim=none (message not signed) header.d=none;gmail.com; dmarc=none action=none header.from=journyx.com; x-originating-ip: [24.55.4.13] x-microsoft-exchange-diagnostics: 1;BY1PR11MB0392;5:Wg3F1lNr0IUGypCgZCIcQ7LmhLg9TYvu9vCKOKXigDOWnkdZDsyEMKOcIX7WMa5/ZMjvphp+y/qdq+sZniOlc/cxxyij/WXGrdwv/3lbM3/ahNdteky4SRIHnMP4oC4fx0J1E4SM4C90v2YTS/O4qQ==;24:uIW+SOWV7nQ6y2j9bJtDSfRMBebidDUgkEp9FU7axe3fwjzXlBcgXpVPOtHyE79/GZLX6RZSpFi9HxSEvPq8vDBpAuBT3rfyd+6qMJvb3uI= x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BY1PR11MB0392; x-ms-office365-filtering-correlation-id: b60f364e-51d2-4751-0300-08d33ca29368 x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:; x-exchange-antispam-report-cfa-test: BCL:0;PCL:0;RULEID:(601004)(2401047)(8121501046)(5005006)(10201501046)(3002001);SRVR:BY1PR11MB0392;BCL:0;PCL:0;RULEID:;SRVR:BY1PR11MB0392; x-forefront-prvs: 08617F610C x-forefront-antispam-report: SFV:NSPM;SFS:(10009020)(377454003)(24454002)(53754006)(479174004)(15974865002)(92566002)(15975445007)(6116002)(3660700001)(99286002)(3280700002)(33646002)(77096005)(95246002)(19580405001)(19580395003)(1096002)(2501003)(16236675004)(5001770100001)(40100003)(2950100001)(2900100001)(10400500002)(122556002)(189998001)(76176999)(54356999)(11100500001)(106116001)(5001960100002)(87936001)(63666004)(102836003)(50986999)(3846002)(5004730100002)(2906002)(4326007)(1220700001)(5008740100001)(586003)(5002640100001)(66066001)(86362001);DIR:OUT;SFP:1101;SCL:1;SRVR:BY1PR11MB0392;H:BY1PR11MB0390.namprd11.prod.outlook.com;FPR:;SPF:None;MLV:sfv;LANG:en; spamdiagnosticoutput: 1:23 spamdiagnosticmetadata: NSPM Content-Type: multipart/alternative; boundary="_000_e0121fj0w32l77ofwjfb14i21456267414369emailandroidcom_" MIME-Version: 1.0 X-OriginatorOrg: journyx.com X-MS-Exchange-CrossTenant-originalarrivaltime: 23 Feb 2016 22:42:14.1995 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 83db917a-cb55-44ba-82ef-25aa3e32343f X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY1PR11MB0392 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --_000_e0121fj0w32l77ofwjfb14i21456267414369emailandroidcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Are you using it for just select queries, or is this a full 100% test (inse= rt/update/delete) test environment? I have both. In my case they are geographically separate. Production server= at data center. Replicated "just in case," also at data center. At HQ, I r= eplicate that for DR purposes. INTERNALLY (gigabit backend), I have another= internal test server that I refresh FROM THE REPLICATED SERVER AT HQ on a = regular basis. I use the replicated server to query stuff (select) and dump= databases to the interaction test server regularly for full testing. -------- Original message -------- From: "David G. Johnston" Date: 02/23/2016 4:16 PM (GMT-06:00) To: drum.lucas@gmail.com Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Incremental Refresh - PostgreSQL 9.2 On Tue, Feb 23, 2016 at 2:59 PM, drum.lucas@gmail.com > wrote: Hi all, I've got a Master server and a Test Server. Master server has a 1.7 TB Test server has a full copy from master - 1.7 TB. The copy was made using pg_dump I.E: /usr/pgsql-9.2/bin/pg_dump --exclude-table-data=3D'junk.*' --format=3D= custom geoop_live > bigdump.sql Question: Now that the test server has a full copy from master, how could I just do a= incremental refreshing once a month? Instead copying all the DB (1.7GB), just copy what has been changed? Is that possible? ?You cannot accomplish incremental updates if your choice of tool is pg_dum= p. The only way to do a true incremental would be to rely upon something l= ike rsync on the data directory while the master server is offline. So, assuming that is not an option, you should probably look at the various= true replication solutions that are available and see which of those might= serve as an alternative. ?David J. Journyx, Inc. 7600 Burnet Road #300 Austin, TX 78757 www.journyx.com p 512.834.8888 f 512-834-8858 Do you receive our promotional emails? You can subscribe or unsubscribe to = those emails at http://go.journyx.com/emailPreference/e/4932/714/ --_000_e0121fj0w32l77ofwjfb14i21456267414369emailandroidcom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Are you using it for just select queries, or is this a full 100% test (inse= rt/update/delete) test environment?

I have both. In my case they are geographically separate. Production s= erver at data center. Replicated "just in case," also at data cen= ter. At HQ, I replicate that for DR purposes. INTERNALLY (gigabit backend),= I have another internal test server that I refresh FROM THE REPLICATED SERVER AT HQ on a regular basis. I use the r= eplicated server to query stuff (select) and dump databases to the interact= ion test server regularly for full testing.


-------- Original message --------
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: 02/23/2016 4:16 PM (GMT-06:00)
To: drum.lucas@gmail.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Incremental Refresh - PostgreSQL 9.2

On Tue, Feb 23, 2016 at 2:5= 9 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:
Hi all,

I've got a Master server and a Test Server.

Master server has a 1.7 TB
Test server has a full copy from master - 1.7 TB.

The copy was made using pg_dump

I.E: /usr/pgsql-9.2/bin/pg_dump --exclude-table-data=3D'junk.*' --form= at=3Dcustom geoop_live > bigdump.sql

Question:
Now that the test server has a full copy from master, how could I just= do a incremental refreshing once a month?

Instead copying all the DB (1.7GB), just copy what has been changed?
Is that possible?


​You cannot accomplish incremental updates if your choice of tool is = pg_dump.  The only way to do a true incremental would be to rely upon = something like rsync on the data directory while the master server is offli= ne.

So, assuming that is not an option, you should probably look at the various= true replication solutions that are available and see which of those might= serve as an alternative.

​David J.



Journyx, Inc.
7600 Burnet Road #300
Austin, TX 78757
www.journyx.com

p 512.834.8888 
f 512-834-8858 

Do you receive our promotional emails? You can subscribe or unsubscrib= e to those emails at http://go.journyx.com/emailPreference/e/4932/714/ = ;
--_000_e0121fj0w32l77ofwjfb14i21456267414369emailandroidcom_--