pg_dump - rebuilding indexes - Mailing list pgsql-hackers

Hi,

A few time in the past, my indexes have become corrupted somehow
and a vacuum analyze will cause the backend to dump core.  I've
seen other people post similiar problems on hackers and admin.  All
of the suggestions seem to be dumping the database and reloading it 
to have the indexes rebuilt or vacuuming the tables one by one until 
the system crashes and then drop the indexes for that table and 
recreate them from your DDL scripts.  Well, this happened to me again,
so I searched the list looking for any way to automatically rebuild the
indexes but didn't manage to find any.  It got me thinking, pg_dump 
already dumps index creates, all we need to do is modify it to dump
index drops and prevent all of the other schema and data from being
dumped.

I got into the code and quickly learned about the -c option (creates 
drops statements) that wasn't talked about on my outdated manpage. 
Doh! So now the problem is even easier -- I only have to suppress the 
dumping of everying except indexes and turn on the -c flag.  I added
an option called -r (rebuild indexes).  It turns on the dropSchema,
schemaOnly and dataOnly flags which in essence causes pg_dump to 
do nothing.  An extra snippet of code checks to see if indexes should
be rebuilt and dumps the index creates.  It's a real hack, but it 
suites my needs.  


Now, whenever I want to rebuild my indexes I can just type:
pg_dump -r mydatabase | psql mydatabase

If something else already exists -- oops maybe we could add it to the 
faq.  I actually would have liked to implement the code differently, but 
the current code isn't very condusive to a more elegant solution and I din't
want to put much time into something that might be rejected by the source
code maintainers.  If this is rejected, I only wasted 10 minutes.  Ideally,
what I would like is a flag that allows you to specify the types to be 
dumped.  This way, it would be flexible enough to allow you to dump any 
combination of types.  If you only wanted to dump trigger schema you could.  
If you wanted sequences and indexes, no problem.  Something like:pg_dump --dump-types "type trigger aggregate"

Anyway, 

diff -u ./pg_dump.c ../../../../postgresql-6.5.3/src/bin/pg_dump/pg_dump.c
--- ./pg_dump.c    Sun Nov 14 01:41:05 1999
+++ ../../../../postgresql-6.5.3/src/bin/pg_dump/pg_dump.c    Thu Sep 23 14:13:49 1999
@@ -112,7 +112,6 @@PGconn       *g_conn;                /* the database connection */bool        force_quotes;
/*User wants to suppress double-quotes */
 
-bool        rebuildIndexes;        /* dump DDL for index rebuilds   */bool        dumpData;            /* dump data
usingproper insert strings */bool        attrNames;            /* put attr names into insert strings */bool
schemaOnly;
@@ -543,7 +542,6 @@    g_verbose = false;    force_quotes = true;
-    rebuildIndexes = false;    dropSchema = false;    strcpy(g_comment_start, "-- ");
@@ -554,9 +552,7 @@    progname = *argv;
-        /* Get the arguments for the command line via getopts cycle through
-           each option, setting the appropriate flags as necessary */
-    while ((c = getopt(argc, argv, "acdDf:h:nNop:rst:uvxz")) != EOF)
+    while ((c = getopt(argc, argv, "acdDf:h:nNop:st:uvxz")) != EOF)    {        switch (c)        {
@@ -594,17 +590,6 @@            case 'p':            /* server port */                pgport = optarg;
break;
-            case 'r':            /* rebuild indexes */
-                rebuildIndexes = true;  /* forces only indexes to be dumped        */
-                dropSchema = true;      /* causes drop statements to be created    */
-
-                /* Setting data only to true, causes the dumpSchema() to dump 
-                   schema to a NULL file handle, and setting schemaOnly to true
-                   prevents dumpClasses() from dumping the data -- it's a HACK */
-                schemaOnly = true;      
-                dataOnly = true;      
-
-                break;            case 's':            /* dump schema only */                schemaOnly = true;
       break;
 
@@ -765,13 +750,8 @@    if (!schemaOnly)        dumpClasses(tblinfo, numTables, g_fout, tablename, oids);
-
-    /* dump indexes and triggers at the end for performance */
-    if (rebuildIndexes)
-    {
-        dumpSchemaIdx(g_fout, tablename, tblinfo, numTables);
-    }
-    else if (!dataOnly)                
+    if (!dataOnly)                /* dump indexes and triggers at the end
+                                 * for performance */    {        dumpSchemaIdx(g_fout, tablename, tblinfo,
numTables);       dumpTriggers(g_fout, tablename, tblinfo, numTables);
 

-- 
The world's most ambitious and comprehensive PC game database project.
                     http://www.mobygames.com