Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 05D0B9FA4B2 for ; Thu, 21 Dec 2006 15:57:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 04100-10 for ; Thu, 21 Dec 2006 15:56:49 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 31F4B9FA48F for ; Thu, 21 Dec 2006 15:56:57 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mx.imatech.net (unknown [81.255.215.253]) by svr4.postgresql.org (Postfix) with ESMTP id 79C125AF940 for ; Thu, 21 Dec 2006 19:56:55 +0000 (GMT) X-AuthUser: olivier.boissard@cerene.fr Received: from [192.168.0.10] ([88.161.145.183]:1416) by mx.imatech.net with [XMail 1.23 ESMTP Server] id for from ; Thu, 21 Dec 2006 20:56:45 +0100 Message-ID: <458AE6FD.2010004@cerene.fr> Date: Thu, 21 Dec 2006 20:56:45 +0100 From: "olivier.boissard@cerene.fr" User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) MIME-Version: 1.0 To: arnaulist@andromeiberica.com Cc: pgsql-admin@postgresql.org Subject: Re: Suggestions needed about how to dump/restore a database References: <45881D1A.6090904@andromeiberica.com> <458AB4AA.7000902@andromeiberica.com> In-Reply-To: <458AB4AA.7000902@andromeiberica.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/291 X-Sequence-Number: 24083 Arnau a écrit : > Hi all, > >> I've got a DB in production that is bigger than 2GB that dumping it >> takes more than 12 hours. I have a new server to replace this old one >> where I have restore the DB's dump. The problem is I can't afford to >> have the server out of business for so long, so I need your advice about >> how you'd do this dump/restore. The big amount of data is placed in two >> tables (statistics data), so I was thinking in dump/restore all except >> this two tables and once the server is running again I'd dump/restore >> this data. The problem is I don't know how exactly do this. >> >> Any suggestion? >> >> Thanks > > Jeff answer made me check what were the configuration parameters. > That machine had the default ones, so I tweaked a bit them and now I > got a dump in about 2 hours. To dump the DB I'm using the following > command: > > /usr/bin/pg_dump -o -b -Fc $db > $backup_file > > And as result I got a file of 2.2GB. The improvement has been quite > big but still very far from the 10-15 minutes that Jeff says or the > Thomas'3 minutes. > > The version I'm running is a 7.4.2 and the postgresql.conf > parameters are the following: > > # - Memory - > > shared_buffers = 10000 # min 16, at least max_connections*2, > 8KB each > sort_mem = 10240 # min 64, size in KB > vacuum_mem = 81920 # min 1024, size in KB > > # - Free Space Map - > > max_fsm_pages = 40000 # min max_fsm_relations*16, 6 bytes each > max_fsm_relations = 2000 # min 100, ~50 bytes each > > > Any suggestions the even reduce more the dump period? > > Thank you very much. To reduce dump period I suggest you to use the unix pipes : pgdumps $PSOPTIONS | psql $database I make always like this as it 's a lot faster Olivier