Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id F180C9F9FE1 for ; Thu, 21 Dec 2006 12:22:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 49042-05 for ; Thu, 21 Dec 2006 12:21:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from fafnir.androme.com (fafnir.androme.com [62.58.96.158]) by postgresql.org (Postfix) with ESMTP id AF44E9F9DAD for ; Thu, 21 Dec 2006 12:22:04 -0400 (AST) Received: by fafnir.androme.com (Postfix, from userid 1003) id B39D7B9B5ED; Thu, 21 Dec 2006 17:22:02 +0100 (CET) Message-ID: <458AB4AA.7000902@andromeiberica.com> Date: Thu, 21 Dec 2006 17:22:02 +0100 From: Arnau Reply-To: arnaulist@andromeiberica.com User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Suggestions needed about how to dump/restore a database References: <45881D1A.6090904@andromeiberica.com> In-Reply-To: <45881D1A.6090904@andromeiberica.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/287 X-Sequence-Number: 24079 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. -- Arnau