Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 08BBF2E3606 for ; Tue, 18 Dec 2007 16:54:13 -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 02412-02 for ; Tue, 18 Dec 2007 16:54:09 -0400 (AST) Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.229]) by postgresql.org (Postfix) with ESMTP id 335BB2E3616 for ; Tue, 18 Dec 2007 16:54:09 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id l8so1275155nzf.28 for ; Tue, 18 Dec 2007 12:54:08 -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:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=YPJ9b6SCl+PWFdk8bU/szK+zv7BvgWo2htwz3vcfww0=; b=Kv6QXUAsKpUMVToc2up47TEUH8qdlvnUHsX6vvqA/YFMidjdfvAO7WKDlSPOtJFaS1aggusKN3hiLakEOGtWbOQZR8vHRpyEB+NEgSfd5yPcf28B3jffGelURhQoGkaMNd/VPAxdG2lej1CValXxDn78TrotK6F08qOQiGo6FG8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Wu1688KGdSNBPGBKPGdd4I6UltuTB4livoYumaQYpL11O/68gaCD9vMsqlfD25BnOTBtQBs9b5EuUjGllVopCbcUauX+sxRy8/wzx+BFGyWlv00Pv1KRqMSyUOaCNK6ba7L8CNUgNtS4k/F4JunaKBWViCKgcyMsWK8W3NmuHDc= Received: by 10.114.112.1 with SMTP id k1mr5162267wac.24.1198011247632; Tue, 18 Dec 2007 12:54:07 -0800 (PST) Received: by 10.114.131.4 with HTTP; Tue, 18 Dec 2007 12:54:07 -0800 (PST) Message-ID: Date: Tue, 18 Dec 2007 14:54:07 -0600 From: "Scott Marlowe" To: Deron Subject: Re: Set maintenance work mem for pg_restore Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/171 X-Sequence-Number: 27688 On Dec 18, 2007 2:30 PM, Deron wrote: > 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', and is > useful in some cases. > > I know I can set this for my current session dynamically "set > maintenance_work_mem = xxxxx", but it seems that the only way I can do this > for pg_restore is to update the configuration, reload and then change it > back when the script completes and reload again. If you use psql to restore, you can just add the set maintenance_work_mem = xxx at the top of the file. Also you can do it by creating a "special" superuser and altering that user to inherit this new setting: create user bubba superuser; alter user bubba set maintenance_work_mem TO 512000; then just connect as that user to run your restores or what not. You can also set it for a non-super user, or a database as well.