pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rodrigo Carvalhaes <grupos@carvalhaes.net>
To: pgsql-general@postgresql.org
To: pgsql-performance@postgresql.org
Subject: pg_restore taking 4 hours!
Date: Wed, 01 Dec 2004 09:16:58 -0200
Message-ID: <41ADA82A.7070405@carvalhaes.net> (raw)

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



view thread (15+ messages)  latest in thread

Message-ID: <41ADA82A.7070405@carvalhaes.net>
Permalink:  ../41ADA82A.7070405@carvalhaes.net/
Also on:    postgresql.org/message-id/41ADA82A.7070405@carvalhaes.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: grupos@carvalhaes.net, pgsql-general@postgresql.org
  Subject: Re: pg_restore taking 4 hours!
  In-Reply-To: <41ADA82A.7070405@carvalhaes.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox