[BUGS] Problem with restore DB
Bug reference: 1543 Logged by: Ales Vojacek Email address: [EMAIL PROTECTED] 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 30 to 50 there was not solution only if I have 30 there was more much I/O then 50. If I have set 50 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 40 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1542: pg_dump seg fault
> Hmmm. What did you do to "delete that table" exactly? The crash To tell the truth, I delete the table using Windows PgAdmin. I guess it has done the same operation, hasn't it? > suggests that there is a row in pg_rewrite that links to a nonexistent > row in pg_class. It'd be better if pg_dump emitted a more useful > failure message, of course, It would be nice if pg_dump could backup the information from other tables, not only showing failure message. > but the real question is how did your system catalogs get into such a state ... I agree. I ask people at #postgres for tools to check state of the DB, if structure is alive or corrupt, because in other cases I could only guess that something is wrong when pg_dump fails. Would you like me to send you a copy of data? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
