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 2114D3A57EC for ; Thu, 2 Dec 2004 16:54:16 +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 15614-07 for ; Thu, 2 Dec 2004 16:53:58 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id 5CF5F3A57CE for ; Thu, 2 Dec 2004 16:53:58 +0000 (GMT) Received: from [192.168.1.20] (clbb-248.saw.net [64.146.135.248]) (authenticated bits=0) by hosting.commandprompt.com (8.12.8/8.12.8) with ESMTP id iB2GrXaW017508; Thu, 2 Dec 2004 08:53:33 -0800 Message-ID: <41AF486C.8090706@commandprompt.com> Date: Thu, 02 Dec 2004 08:53:00 -0800 From: "Joshua D. Drake" User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Thierry Missimilly Cc: grupos@carvalhaes.net, pgsql-general@postgresql.org Subject: Re: pg_restore taking 4 hours! References: <41ADA82A.7070405@carvalhaes.net> <41AF38C4.2000408@bull.net> In-Reply-To: <41AF38C4.2000408@bull.net> Content-Type: multipart/mixed; boundary="------------070108070509070802090807" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.045 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/104 X-Sequence-Number: 69716 This is a multi-part message in MIME format. --------------070108070509070802090807 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Thierry Missimilly wrote: > > 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. > There are a couple of things you can do. 1. Turn off Fsync for the restore 2. Restore in three phases: 1. Schema without constraints or indexes 2. Restore data 3. Apply rest of schema with constraints and indexes 3. Increase the number of transaction logs. Sincerely, Joshua D. Drake >> >> 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 >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL --------------070108070509070802090807 Content-Type: text/x-vcard; charset=utf-8; name="jd.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jd.vcf" begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard --------------070108070509070802090807--