X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id F224A9DD785 for ; Fri, 2 Dec 2005 16:48:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20717-02 for ; Fri, 2 Dec 2005 16:48:28 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.Mi8.com (d01gw01.mi8.com [63.240.6.47]) by postgresql.org (Postfix) with ESMTP id 918AC9DD787 for ; Fri, 2 Dec 2005 16:48:28 -0400 (AST) Received: from 172.16.1.24 by mail.Mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D1)); Fri, 02 Dec 2005 15:48:18 -0500 X-Server-Uuid: 241911D6-425B-44B9-A073-E3FE0F8FC774 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.160]) by d01smtp03.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Fri, 2 Dec 2005 15:48:11 -0500 Received: from 67.103.45.218 ([67.103.45.218]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.104]) with Microsoft Exchange Server HTTP-DAV ; Fri, 2 Dec 2005 20:48:10 +0000 User-Agent: Microsoft-Entourage/11.2.1.051004 Date: Fri, 02 Dec 2005 12:48:08 -0800 Subject: Re: Database restore speed From: "Luke Lonergan" To: "Stephen Frost" cc: "David Lang" , "Steve Oualline" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] Database restore speed Thread-Index: AcX3fY1vB7inoWv+QBO0SMp+aneqZQABCXP+ In-Reply-To: <20051202201847.GE6026@ns.snowman.net> MIME-Version: 1.0 X-OriginalArrivalTime: 02 Dec 2005 20:48:11.0012 (UTC) FILETIME=[B50A4C40:01C5F781] X-WSS-ID: 6F8E6C8F4PK229376-05-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.253 required=5 tests=[RCVD_NUMERIC_HELO=1.253] X-Spam-Score: 1.253 X-Spam-Level: * X-Archive-Number: 200512/77 X-Sequence-Number: 15898 Stephen, On 12/2/05 12:18 PM, "Stephen Frost" wrote: > Just a thought, but couldn't psql be made to use the binary mode of > libpq and do at least some of the conversion on the client side? Or > does binary mode not work with copy (that wouldn't suprise me, but > perhaps copy could be made to support it)? Yes - I think this idea is implicit in what David suggested, and my response as well. The problem is that the way the client does conversions can potentially differ from the way the backend does. Some of the types in Postgres are machine intrinsic and the encoding conversions use on-machine libraries, each of which preclude the use of client conversion methods (without a lot of restructuring). We'd tackled this problem in the past and concluded that the parse / convert stage really belongs in the backend. > The other thought, of course, is that you could use PITR for your > backups instead of pgdump... Totally - great idea, if this is actually a backup / restore then PITR plus filesystem copy (tarball) is hugely faster than dump / restore. - Luke