Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 8B15B9FC112 for ; Fri, 23 Feb 2007 02:42:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 59618-10 for ; Fri, 23 Feb 2007 02:42:22 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web50311.mail.yahoo.com (web50311.mail.yahoo.com [206.190.39.202]) by postgresql.org (Postfix) with SMTP id 9E0629FC0FA for ; Fri, 23 Feb 2007 02:42:23 -0400 (AST) Received: (qmail 52023 invoked by uid 60001); 23 Feb 2007 06:42:21 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:Cc:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=tP7hmaexFOx69FUJ8bZFQA/S6yEtTjwx1cah+0KX7KSK5pgzhfl5WflU96jb0ap7bBDcZxuUruQoWCScJUROnSMoeNX/i8K9mOzFHmmuvQ7I34ldU84ZAOQi7PcTPFD8LZbRZ7cnxv7kj8LR1i2UUu2lgPoOo8ITqYxKE0Wqbq4=; X-YMail-OSG: o_5ZWyQVM1ljcAkTRgj7usaA3dos6H1deWUqGTBylrYt__J6gd9CS67XW9xT2pI0coIACiF8xHTLZjtqWO2OcHQGdB8ZYHEXufHa7DekIsyLzIm3NwuvszGWnMOFPUYqHSESWfMZ.bSmtlY- Received: from [58.62.84.154] by web50311.mail.yahoo.com via HTTP; Thu, 22 Feb 2007 22:42:20 PST X-Mailer: YahooMailRC/368.8 YahooMailWebService/0.6.132.8 Date: Thu, 22 Feb 2007 22:42:20 -0800 (PST) From: ogjunk-pgjedan@yahoo.com Subject: Re: Fastest DB restore options Cc: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Message-ID: <516257.51828.qm@web50311.mail.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.576 tagged_above=0 required=5 tests=BAYES_20, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, NO_REAL_NAME X-Spam-Level: *** X-Archive-Number: 200702/301 X-Sequence-Number: 24766 Hi,=0A=0AYes, In remember discussions about (f)sync config. Can anyone com= ment on whether turning fsync off for a restore into 8.2.3:=0A1) is advisab= le=0A2) will make the restore faster=0A=0AIf the OS and FS matter, this is = on a Fedora Core3 Linux with kernel 2.6.9 and the ext3 journaling FS.=0A=0A= Thanks,=0AOtis=0A=0A----- Original Message ----=0AFrom: Marco Bizzarri =0ACc: pgsql-admin@postgresql.org=0ASent: Thursday, F= ebruary 22, 2007 3:47:37 PM=0ASubject: Re: [ADMIN] Fastest DB restore optio= ns=0A=0AOn 2/22/07, ogjunk-pgjedan@yahoo.com wro= te:=0A> Hello,=0A>=0A> I have a fairly large DB to dump and restore as fast= as possible. I'm moving from 8.0.3 to 8.2.3! :)=0A>=0A> I normally dump w= ith these options:=0A>=0A> -d MyDB --clean --inserts --column-inserts --f= ormat=3DP=0A>=0A> But the last time I tried that, the restore took foreeeee= eeeeeeeeever. So I'm looking for the fastest way to import data from the o= ld DB to the new one. Judging from pg_dump man page the following should b= e the fastest dump & restore:=0A>=0A> -d MyDB --format=3Dc --ignore-versi= on=0A>=0A> Is there anything else I can do to make the restore as fast as p= ossible?=0A>=0A> Thanks,=0A> Otis=0A=0A=0AI'm not sure it is advisable, or = it is even faster in current=0Aimplementation. In older ones, if you config= ure postgresql not to sync=0Aafter each write, you could end in a faster re= store. Since this is a=0Arestore, after all, if lights goes out, you can al= ways throw all away=0Aand start from scratch...=0A=0ARegards=0AMarco=0A=0A= =0A