pg_dump - rebuilding indexes

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 using proper 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