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 AF48C3A558C; Wed, 1 Dec 2004 12:45:19 +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 94358-09; Wed, 1 Dec 2004 12:45:12 +0000 (GMT) Received: from nitrogenio.carvalhaes.net (oxigenio.carvalhaes.net [200.102.144.130]) by svr1.postgresql.org (Postfix) with ESMTP id 871D03A496D; Wed, 1 Dec 2004 12:45:10 +0000 (GMT) Received: from [192.168.0.10] ([200.215.220.2]) (authenticated bits=0) by nitrogenio.carvalhaes.net (8.12.11/8.12.11) with ESMTP id iB1B9aBQ030154; Wed, 1 Dec 2004 09:09:38 -0200 Message-ID: <41ADA82A.7070405@carvalhaes.net> Date: Wed, 01 Dec 2004 09:16:58 -0200 From: Rodrigo Carvalhaes Reply-To: grupos@carvalhaes.net User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-general@postgresql.org, pgsql-performance@postgresql.org Subject: pg_restore taking 4 hours! Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Carvalhaes-MailScanner-Information: Favor contactar o suporte técnico para maiores informações suporte@triade.com X-Carvalhaes-MailScanner: Found to be clean X-MailScanner-From: grupos@carvalhaes.net X-Virus-Scanned: by amavisd-new at hub.org X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char E9 hex) in message header 'X-Carvalhaes-MailScanner-Information': X-Carvalhaes-MailScanner-Information: Favor contactar o suporte t\351cnico para maio... X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/45 X-Sequence-Number: 69657 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!!! 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