The following bug has been logged online:
Bug reference: 1543
Logged by: Ales Vojacek
Email address: ALESV@FBL.CZ
PostgreSQL version: 8
Operating system: W2000
Description: Problem with restore DB
Details:
We try to come from MSSQL. We heva no trigger or stored procedures. When I
just create tables, then transfer data using copy command to fill tables,
then create primary keys, indexes, foreign keys it takes on my hardware cca
4 hours.
When I backup database using pg_dump and then I try to restore DB into empty
DB. I take 12+ hours and does not end. The problem was that create FK on
some columns take a lot of time. Especially on of them takes 10+ hours it
was on tables (2 000 000 and 10 000 rows). All database is about 18 GB and I
have 1 GB RAM.
When I try to setup maintenance_work_mem from 300000 to 500000 there was
not solution only if I have 300000 there was more much I/O then 500000. If
I have set 500000 there few I/O and CPU 100%. But the tim of creafion FK
seemd to be same.
The time of creation FK was much sorter when I reindex tables which are
affected by creation of FK. After that with maintenance_work_mem set to
400000 it ends afte few minutes.
If I was talking about my problem on IRC they say to report it to you. I
hope that you can explain it to me or you can fix it.
If we try to backup and restore same database on our linux box it was done
in time which we hope cca two hours. It is on faster hw, but I thing that
restore from dump of DB cannot have a problem with creation of FK.
Thanks a excuse me for my poor english.
Ales