Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 03FA52E32B5 for ; Tue, 18 Dec 2007 18:40:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 34025-03 for ; Tue, 18 Dec 2007 18:40:50 -0400 (AST) Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.181]) by postgresql.org (Postfix) with ESMTP id A05672E0241 for ; Tue, 18 Dec 2007 18:40:50 -0400 (AST) Received: by py-out-1112.google.com with SMTP id u77so5639820pyb.15 for ; Tue, 18 Dec 2007 14:40:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=w7Slsp2xRq0qCCpYuU6S0oXn/c3eKvh5H3oiJlpJEkQ=; b=N7vTPWaWCvgF3F6z23XKJp4aTdPGX+EzsilWk6Pje73xoF/qn+Yy/9RCDQ78xB99Wy/cZgUgujwWD59sA1os3jWUUnuEpmsYSvEBI/vT2vqiw6NvnEIeHYZdb0K287/F5RsBN92uWfNMqzjy+xsA9LxewnXmVsB2EaX6rj0VttQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=qTYbJNwvavdgUh4OoO9G2wPMvQTqEwRuJjyvdvDjxFQBNWUwgzZb+zdWtl5lo6IhoaeHxOyQAo5AJ1lh+PJgt1XoXXOT4AGUqU+q5fo0meCCjrevnJEs7K1hKQeyp5TRn4JRAH1mPPzpVY+h2tVR1b21e8XgZJ/432Q6hDPG5e8= Received: by 10.64.181.12 with SMTP id d12mr19096929qbf.10.1198017643104; Tue, 18 Dec 2007 14:40:43 -0800 (PST) Received: by 10.64.150.15 with HTTP; Tue, 18 Dec 2007 14:40:43 -0800 (PST) Message-ID: Date: Tue, 18 Dec 2007 14:40:43 -0800 From: Deron To: pgsql-admin@postgresql.org Subject: Re: Set maintenance work mem for pg_restore In-Reply-To: <20071218204957.GE8088@alvh.no-ip.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_20634_3851366.1198017643087" References: <20071218204957.GE8088@alvh.no-ip.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/174 X-Sequence-Number: 27691 ------=_Part_20634_3851366.1198017643087 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks for this and the other suggestions. This is exactly what I was looking for. Deron On 12/18/07, Alvaro Herrera wrote: > > Deron escribi=F3: > > I see a lot of suggestions to increase maintenance work mem if running > > pg_restore. This is to help with the index creation and initial sort > from > > what I understand. A few tests I ran does show this helps. > > Does anyone know if there is a way to only "temporarily" set this > setting? > > I have some DBs that are bloated and I have a script to run off hours (= I > > don't like working late). This uses pg_dump and pg_restore to recover > the > > disk space. I found that this is usually faster than 'vacuum full', an= d > is > > useful in some cases. > > You can specify it via PGOPTIONS: > > $ PGOPTIONS=3D'-c maintenance_work_mem=3D1GB' psql > Welcome to psql 8.2.5, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > alvherre=3D# show maintenance_work_mem ; > maintenance_work_mem > ---------------------- > 1GB > (1 row) > > alvherre=3D# \q > > $ psql > Welcome to psql 8.2.5, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > alvherre=3D# show maintenance_work_mem ; > maintenance_work_mem > ---------------------- > 16MB > (1 row) > > > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > ------=_Part_20634_3851366.1198017643087 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Thanks for this and the other suggestions.  This is exactly what = I was looking for.
 
Deron


 
On 12/18/07, Alvaro Herrera <alvhe= rre@commandprompt.com> wrote:
Deron escribi=F3:
> I see = a lot of suggestions to increase maintenance work mem if running
> pg= _restore.  This is to help with the index creation and initial so= rt from
> what I understand.  A few tests I ran does show this hel= ps.
> Does anyone know if there is a way to only "temporarily&qu= ot; set this setting?
> I have some DBs that are bloated and I have a= script to run off hours (I
> don't like working late).  This uses pg_dump and pg_= restore to recover the
> disk space.  I found that this is = usually faster than 'vacuum full', and is
> useful in some ca= ses.

You can specify it via PGOPTIONS:

$ PGOPTIONS=3D'-c maintenance_work_mem=3D1GB' psql
Welco= me to psql 8.2.5, the PostgreSQL interactive terminal.

Type: &n= bsp;\copyright for distribution terms
     &nbs= p;\h for help with SQL commands
      \? f= or help with psql commands
      \g or terminate with semicolon to e= xecute query
      \q to quit

alvhe= rre=3D# show maintenance_work_mem ;
maintenance_work_mem
------------= ----------
1GB
(1 row)

alvherre=3D# \q

$ psql
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.

Type= :  \copyright for distribution terms
    &= nbsp; \h for help with SQL commands
     &= nbsp;\? for help with psql commands
      = \g or terminate with semicolon to execute query
      \q to quit

alvherre=3D# show= maintenance_work_mem ;
maintenance_work_mem
----------------------16MB
(1 row)



--
Alvaro Herrera   &nbs= p;            &= nbsp;           &nbs= p;    http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Cu= stom Development, 24x7 support

------=_Part_20634_3851366.1198017643087--