Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yy1n6-00006R-6x for pgsql-performance@arkaria.postgresql.org; Thu, 28 May 2015 17:40:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yy1n5-0002ov-M5 for pgsql-performance@arkaria.postgresql.org; Thu, 28 May 2015 17:40:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yy1n4-0002oN-Ry for pgsql-performance@postgresql.org; Thu, 28 May 2015 17:40:22 +0000 Received: from mail-by2on0080.outbound.protection.outlook.com ([207.46.100.80] helo=na01-by2-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 1Yy1n0-0007H8-FO for pgsql-performance@postgresql.org; Thu, 28 May 2015 17:40:21 +0000 Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.attlocal.net (2602:306:ce8b:3460:5ca5:7306:9586:94e2) by BY2PR11MB0677.namprd11.prod.outlook.com (25.163.111.19) with Microsoft SMTP Server (TLS) id 15.1.172.22; Thu, 28 May 2015 17:40:12 +0000 Message-ID: <556752FF.3070402@BlueTreble.com> Date: Thu, 28 May 2015 12:40:15 -0500 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: Steve Atkins , "pgsql-performance@postgresql.org" CC: "Wes Vaske (wvaske)" Subject: Re: Fastest Backup & Restore for perf testing References: <31701C89A44B714FB8D242F806E7286C5AEC68FE@NTXBOIMBX01.micron.com> <60ED9885-1F66-4BD0-9DE7-8CCCB7D0D75E@blighty.com> In-Reply-To: <60ED9885-1F66-4BD0-9DE7-8CCCB7D0D75E@blighty.com> Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 8bit X-Originating-IP: [2602:306:ce8b:3460:5ca5:7306:9586:94e2] X-ClientProxiedBy: CY1PR0101CA0032.prod.exchangelabs.com (25.162.170.42) To BY2PR11MB0677.namprd11.prod.outlook.com (25.163.111.19) X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:BY2PR11MB0677; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(520003)(5005006)(3002001); SRVR:BY2PR11MB0677; BCL:0; PCL:0; RULEID:; SRVR:BY2PR11MB0677; X-Forefront-PRVS: 0590BBCCBC X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(6009001)(377454003)(51704005)(479174004)(189002)(24454002)(199003)(87976001)(80316001)(122386002)(2950100001)(69596002)(36756003)(92566002)(15975445007)(97736004)(101416001)(106356001)(2501003)(19580395003)(83506001)(4001540100001)(19580405001)(5001960100002)(5001770100001)(40100003)(5001860100001)(64706001)(50466002)(46102003)(23676002)(65956001)(47776003)(189998001)(81156007)(68736005)(42186005)(65806001)(33656002)(64126003)(62966003)(77156002)(105586002)(59896002)(4001350100001)(5001830100001)(53416004)(76176999)(54356999)(65816999)(99136001)(50986999)(87266999)(86362001)(15123001)(3826002)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:BY2PR11MB0677; H:decina.attlocal.net; FPR:; SPF:None; PTR:InfoNoRecords; MX:1; A:1; LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 28 May 2015 17:40:12.9688 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY2PR11MB0677 X-Pg-Spam-Score: -3.0 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 5/27/15 3:39 PM, Steve Atkins wrote: > >> On May 27, 2015, at 1:24 PM, Wes Vaske (wvaske) wrote: >> >> Hi, >> >> I’m running performance tests against a PostgreSQL database (9.4) with various hardware configurations and a couple different benchmarks (TPC-C & TPC-H). >> >> I’m currently using pg_dump and pg_restore to refresh my dataset between runs but this process seems slower than it could be. >> >> Is it possible to do a tar/untar of the entire /var/lib/pgsql tree as a backup & restore method? >> >> If not, is there another way to restore a dataset more quickly? The database is dedicated to the test dataset so trashing & rebuilding the entire application/OS/anything is no issue for me—there’s no data for me to lose. >> > > Dropping the database and recreating it from a template database with "create database foo template foo_template" is about as fast as a file copy, much faster than pg_restore tends to be. Another possibility is filesystem snapshots, which could be even faster than createdb --template. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance