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 2BB6E11F6237 for ; Mon, 10 Apr 2006 13:09:37 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.121 X-Spam-Level: X-Spam-Status: No, score=0.121 required=5 tests=[AWL=0.120, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id ZtRX6bO97Hg9 for ; Mon, 10 Apr 2006 13:08:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id 141F411F68E8 for ; Mon, 10 Apr 2006 13:08:50 -0300 (ADT) Received: by nproxy.gmail.com with SMTP id o25so688334nfa for ; Mon, 10 Apr 2006 09:08:48 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=eA4SJIg9SefjCoMbePZvdiPzDW/VPhXxH4KcKu5OKfttgRfamjhEt3E7U/zyhwhAMpcdnwrgHI8gZZQw4fd+LN+RkJUewZB5oYZYcdAOsTQI9nspYREaaclYb2BBgkzyoGtEfLXyeTp6fQnOZVPtWGia7H69dnr23QlDHmcn9gQ= Received: by 10.49.8.9 with SMTP id l9mr3917728nfi; Mon, 10 Apr 2006 09:08:48 -0700 (PDT) Received: by 10.49.27.16 with HTTP; Mon, 10 Apr 2006 09:08:48 -0700 (PDT) Message-ID: Date: Mon, 10 Apr 2006 21:38:48 +0530 From: "Rajesh Kumar Mallah" To: "Jesper Krogh" Subject: Re: Restore performance? Cc: pgsql-performance@postgresql.org In-Reply-To: <443A7FC7.2000001@krogh.cc> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_24814_7308244.1144685328838" References: <443A7FC7.2000001@krogh.cc> X-Archive-Number: 200604/209 X-Sequence-Number: 18293 ------=_Part_24814_7308244.1144685328838 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 4. fsync can also be turned off while loading huge dataset , but seek others comments too (as study docs) as i am not sure about th= e reliability. i think it can make a lot of difference. On 4/10/06, Jesper Krogh wrote: > > Rajesh Kumar Mallah wrote: > >> I'd run pg_dump | gzip > sqldump.gz on the old system. That took abou= t > >> 30 hours and gave me an 90GB zipped file. Running > >> cat sqldump.gz | gunzip | psql > >> into the 8.1 database seems to take about the same time. Are there > >> any tricks I can use to speed this dump+restore process up? > > > > > > was the last restore successfull ? > > if so why do you want to repeat ? > > "about the same time" =3D=3D Estimated guess from restoring a few tables > I was running a testrun, without disabling updates to the production > database, the real run is scheduled for easter where there hopefully is > no users on the system. So I need to repeat, I'm just trying to get a > feelingabout how long time I need to allocate for the operation. > > > 1. run new version of postgres in a different port and pipe pg_dump to > psql > > this may save the CPU time of compression , there is no need for a > temporary > > dump file. > > > > pg_dump | /path/to/psql813 -p 54XX newdb > > I'll do that. It is a completely different machine anyway. > > > 2. use new version of pg_dump to dump the old database as new version > > is supposed to be wiser. > > Check. > > > 3. make sure you are trapping the restore errors properly > > psql newdb 2>&1 | cat | tee err works for me. > > Thats noted. > > -- > Jesper Krogh, jesper@krogh.cc > > ------=_Part_24814_7308244.1144685328838 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
4. fsync can also be turned off while loading huge dataset ,
 &= nbsp;  but seek others comments  too (as study docs) as i am not = sure about the
    reliability. i think it can make a lot= of difference.



On 4/10/06, Jespe= r Krogh <jesper@krogh.cc> = wrote:
Rajesh Kumar Mallah wrote:
>> I'd run pg_dump | gzip > sqldump.= gz  on the old system. That took about
>> 30 hours and g= ave me an 90GB zipped file. Running
>> cat sqldump.gz | gunzip | p= sql
>> into the 8.1 database seems to take about the same time. Are there=
>> any tricks I can use to speed this dump+restore process up?>
>
> was the last restore successfull ?
> if so why = do you want to repeat ?

"about the same time" =3D=3D Estimated guess from restori= ng a few tables
I was running a testrun, without disabling updates to th= e production
database, the real run is scheduled for easter where there = hopefully is
no users on the system. So I need to repeat, I'm just trying to get afeelingabout how long time I need to allocate for the operation.

&= gt; 1. run new version of postgres in a different port and pipe pg_dump to = psql
> this may save the CPU time of compression , there is no need for a= temporary
> dump file.
>
> pg_dump | /path/to/psql813&nb= sp; -p 54XX newdb

I'll do that. It is a completely different ma= chine anyway.

> 2. use new version of pg_dump to dump the old database as new = version
>     is supposed to be wiser.

Che= ck.

> 3. make sure you are trapping the restore errors properly> psql newdb 2>&1 | cat | tee err works for me.

Thats noted.

--
Jesper Krogh, jesper@krogh.cc


------=_Part_24814_7308244.1144685328838--