On Wed, 2008-02-06 at 15:13 +0000, Simon Riggs wrote: > The -s option creates the table, as well as creating constraints and > indexes. These objects need to be dropped prior to loading, if we are to > follow the performance recommendations in the docs. But the only way to > do that is to manually edit the script to produce a cut down script. > > So it would be good if we could dump objects in 3 groups > 1. all commands required to re-create table > 2. data > 3. all commands required to complete table after data load > > My proposal is to provide two additional modes: > --schema-pre-load corresponding to (1) above > --schema-post-load corresponding to (3) above
Another thought: We could also break step #3 into two steps: those objects required for correctness (e.g. unique indexes, other constraints, etc); and those objects that are merely for performance (i.e. non-constraining indexes). Let's call these steps #3A and #3B. After reading the thread here: http://archives.postgresql.org/pgsql-performance/2008-02/msg00211.php it's clear that building indexes can take a long time, and they aren't strictly required for correct database operation. Often, it's perfectly reasonable to operate the database without a few of the indexes, so long as they don't imply a constraint. Step #3B could be done with "CONCURRENTLY" to allow uninterrupted operation. We could even allow pg_restore to run step #3B in multiple sessions building different indexes to use multiple processor cores. This is just a thought. I can see that it's getting fairly complex, and it may be better to just leave these things up to the DBA. Regards, Jeff Davis ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
