Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VQxgA-0008Ag-0A for pgsql-admin@arkaria.postgresql.org; Tue, 01 Oct 2013 10:59:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VQxg9-0002v9-4m for pgsql-admin@arkaria.postgresql.org; Tue, 01 Oct 2013 10:59:45 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VQxg8-0002v3-Aa for pgsql-admin@postgresql.org; Tue, 01 Oct 2013 10:59:44 +0000 Received: from mail01.pnet.xcon.it ([62.48.53.71]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VQxg5-0007Il-By for pgsql-admin@postgresql.org; Tue, 01 Oct 2013 10:59:43 +0000 Received: from localhost (av.pnet.xcon.it [10.68.1.18]) by mail01.pnet.xcon.it (Postfix) with ESMTP id 8F739301C92DB for ; Tue, 1 Oct 2013 12:59:40 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at av Received: from mail01.pnet.xcon.it ([10.68.1.19]) by localhost (av.pnet.xcon.it [10.68.1.18]) (amavisd-new, port 10024) with LMTP id FpZ44Ws4TvPI for ; Tue, 1 Oct 2013 12:58:54 +0200 (CEST) Received: from bnc1.pnet.xcon.it (bnc1.pnet.xcon.it [10.68.1.23]) by mail01.pnet.xcon.it (Postfix) with ESMTP id 6E18A301C92DA for ; Tue, 1 Oct 2013 12:59:40 +0200 (CEST) Received: from localhost (av.pnet.xcon.it [10.68.1.18]) by bnc1.pnet.xcon.it (Postfix) with ESMTP id 6568C1022D8E for ; Tue, 1 Oct 2013 12:59:40 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at av Received: from bnc1.pnet.xcon.it ([10.68.1.23]) by localhost (av.pnet.xcon.it [10.68.1.18]) (amavisd-new, port 10024) with LMTP id 5yOyN0-5loo9 for ; Tue, 1 Oct 2013 12:58:53 +0200 (CEST) Received: from [192.168.0.144] (unknown [83.149.163.202]) (Authenticated sender: giuseppe.broccolo@2ndquadrant.it) by bnc1.pnet.xcon.it (Postfix) with ESMTPSA id EE1201022E35 for ; Tue, 1 Oct 2013 12:59:39 +0200 (CEST) Message-ID: <524AAB93.7070308@2ndquadrant.it> Date: Tue, 01 Oct 2013 13:01:39 +0200 From: Giuseppe Broccolo User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.0 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: PostgreSQL 9.2 - pg_dump out of memory when backuping a database with 300000000 large objects References: <524A90D1.5000109@iqbuzz.ru> In-Reply-To: <524A90D1.5000109@iqbuzz.ru> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -0.5 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org Maybe you can performe your database changing some parameters properly: > > PostgreSQL configuration: > > listen_addresses = '*' # what IP address(es) to listen on; > port = 5432 # (change requires restart) > max_connections = 500 # (change requires restart) Set it to 100, the highest value supported by PostgreSQL > shared_buffers = 16GB # min 128kB This value should not be higher than 8GB > temp_buffers = 64MB # min 800kB > work_mem = 512MB # min 64kB > maintenance_work_mem = 30000MB # min 1MB Given RAM 96GB, you could set it up to 4800MB > checkpoint_segments = 70 # in logfile segments, min 1, > 16MB each > effective_cache_size = 50000MB Given RAM 96GB, you could set it up to 80GB > Hope it can help. Giuseppe. -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin