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 63EC73A5731 for ; Thu, 2 Dec 2004 15:46:25 +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 83237-08 for ; Thu, 2 Dec 2004 15:46:18 +0000 (GMT) Received: from ecfrec.frec.bull.fr (ecfrec.frec.bull.fr [129.183.4.8]) by svr1.postgresql.org (Postfix) with ESMTP id 485073A4C7D for ; Thu, 2 Dec 2004 15:46:16 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by ecfrec.frec.bull.fr (Postfix) with ESMTP id 5C6C319D90A; Thu, 2 Dec 2004 16:46:13 +0100 (CET) Received: from ecfrec.frec.bull.fr ([127.0.0.1]) by localhost (ecfrec.frec.bull.fr [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01406-05; Thu, 2 Dec 2004 16:46:11 +0100 (CET) Received: from ecn002.frec.bull.fr (ecn002.frec.bull.fr [129.183.4.6]) by ecfrec.frec.bull.fr (Postfix) with ESMTP id DE65519D908; Thu, 2 Dec 2004 16:46:10 +0100 (CET) Received: from [129.183.101.115] ([129.183.101.115]) by ecn002.frec.bull.fr (Lotus Domino Release 5.0.12) with ESMTP id 2004120216532829:2840 ; Thu, 2 Dec 2004 16:53:28 +0100 Message-ID: <41AF38C4.2000408@bull.net> Date: Thu, 02 Dec 2004 16:46:12 +0100 From: Thierry Missimilly User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.7.3) Gecko/20040910 X-Accept-Language: fr, en MIME-Version: 1.0 To: grupos@carvalhaes.net Cc: pgsql-general@postgresql.org Subject: Re: pg_restore taking 4 hours! References: <41ADA82A.7070405@carvalhaes.net> In-Reply-To: <41ADA82A.7070405@carvalhaes.net> X-MIMETrack: Itemize by SMTP Server on ECN002/FR/BULL(Release 5.0.12 |February 13, 2003) at 02/12/2004 16:53:28, Serialize by Router on ECN002/FR/BULL(Release 5.0.12 |February 13, 2003) at 02/12/2004 16:53:30, Serialize complete at 02/12/2004 16:53:30 Content-Transfer-Encoding: 8bit Content-Type: text/plain; charset=ISO-8859-1; format=flowed X-Virus-Scanned: by amavisd-new at frec.bull.fr 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/91 X-Sequence-Number: 69703 Rodrigo Carvalhaes a écrit : > Hi! > > I am using PostgreSQL with a proprietary ERP software in Brazil. The > database have around 1.600 tables (each one with +/- 50 columns). > My problem now is the time that takes to restore a dump. My customer > database have arount 500mb (on the disk, not the dump file) and I am > making the dump with pg_dump -Fc, my dumped file have 30mb. To make > the dump, it's taking +/- 1,5 hours BUT to restore (using pg_restore ) > it it takes 4 - 5 hours!!! I have notice that fac and one way to improve the restore prefomances, is to avoid build indexes and checking the foreign key in the same step than the restore. So, as it is not possible to disable indexes and Foreign key, you have to drop them and recreate them once the restore step has finished. To do that you should have a script to recreate the indexes and the Foreign Key afterward. > > Our machine it's a Dell Server Power Edge 1600sc (Xeon 2,4Ghz, with > 1GB memory, 7200 RPM disk). I don't think that there is a machine > problem because it's a server dedicated for the database and the cpu > utilization during the restore is around 30%. > > Looking on the lists arquives I found some messages about this and Tom > Lane was saying that then you have a lot of convertions the dump can > delay too much. 90% of the columns on my database are char columns and > I don't have large objects on the database. The restore is delaying > too much because the conversion of the char columns ? How can I have a > better performance on this restore? > > 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 > > Regards, > > Rodrigo Carvalhaes > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >