Re: pg_restore taking 4 hours!

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(at)commandprompt(dot)com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL