X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2AA353A4835 for ; Wed, 1 Dec 2004 15:19:26 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46988-10 for ; Wed, 1 Dec 2004 15:19:14 +0000 (GMT) Received: from web13921.mail.yahoo.com (web13921.mail.yahoo.com [66.163.176.46]) by svr1.postgresql.org (Postfix) with SMTP id 26DDA3A461A for ; Wed, 1 Dec 2004 15:19:17 +0000 (GMT) Received: (qmail 17370 invoked by uid 60001); 1 Dec 2004 15:19:17 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=TQHaUDTa8F3P4JUJ/06n0vVPVOvsqwk/00abyclmg7ffGdjYOZfxWk74sE594Er/I5liDnqWNP6HzL5zJlyAbLqK4adK7K4PMvBmLFJS+7Y8uK6Is52wG4Ju4BBcppt3skr3AkvK47e3CLfPmyE0iKTUKqcyMRc5H30BlLa50tg= ; Message-ID: <20041201151917.17368.qmail@web13921.mail.yahoo.com> Received: from [80.67.100.162] by web13921.mail.yahoo.com via HTTP; Wed, 01 Dec 2004 07:19:17 PST Date: Wed, 1 Dec 2004 07:19:17 -0800 (PST) From: "Riccardo G. Facchini" Reply-To: abief_ag_-postgresql@yahoo.com Subject: Re: [PERFORM] pg_restore taking 4 hours! To: Shridhar Daithankar , grupos@carvalhaes.net Cc: pgsql-general@postgresql.org, pgsql-performance@postgresql.org In-Reply-To: <200412011955.23543.ghodechhap@ghodechhap.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.374 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200412/52 X-Sequence-Number: 69664 --- Shridhar Daithankar <__> wrote: > On Wednesday 01 Dec 2004 4:46 pm, Rodrigo Carvalhaes wrote: > > I need to find a solution for this because I am convincing > customers > > that are using SQL Server, DB2 and Oracle to change to PostgreSQL > but > > this customers have databases of 5GB!!! I am thinking that even > with a > > better server, the restore will take 2 days! > > > > My data: > > Conectiva Linux 10 , Kernel 2.6.8 > > PostgreSQL 7.4.6. > > > > postgresql.conf modified parameters (the other parameters are the > default) > > tcpip_socket = true > > max_connections = 30 > > shared_buffers = 30000 > > sort_mem = 4096 > > vacuum_mem = 8192 > > max_fsm_pages = 20000 > > max_fsm_relations = 1000 > > Can you try bumping sort mem lot higher(basically whatever the > machine can > afford) so that index creation is faster? > > Just try setting sort mem for the restore session and see if it > helps.. > > Shridhar > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > Yes, indexing is probably the issue. You can always ask them to report how long does it take to restore their M$-SQL, DB2 and Oracle from a scripting dump. I've been restoring DB2 for a looong time (on different architectures) and the main problem comes from indexing. As an index is basically a dynamic structure that is created on the physical data (the data stored on the table), what is normally saved is the index DEFINITION, not the index itself, so this is recreated at restore time. Some DB2 architectures (and M$-SQL, and Oracle, and Sybase, and others. others) may have a backup tool that is capable of saving the index data, but is almost never used, as the index space itself can grow well over the data size. I'll give one example: we have one DB2 on iSeries that runs around the 70Gb of Data and Indexes. We do a full backup that occupies only 45Gb of Data and we do that in a little more than 1 hour because we only save the index definitions. We know for sure that this full backup takes something between 5 and 7 hours because of the reindexing. I had this written down in the Restore Procedure Manual, so the user can't complain (they know that the procedure will eventually restore the data and the full functionality). So, make sure that your client knows of their restore times. One small trick that can help you: FIRST restore the tables. THEN restore the foreingn keys, the constraints and the triggers and procedures. LAST restore the indexes and views. LATEST restore the security. This way, if you have complicated views and indexes with a lot of info, the procedure <<>> be shorter. regards, R.