From a0361c46e69997df8f020eb5ef6807c41e49c453 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sun, 23 Jun 2002 03:51:55 +0000 Subject: Rename command to reindexdb, for consistency. --- contrib/README | 2 +- contrib/reindex/README | 9 -- contrib/reindex/reindex | 223 ------------------------------------------ contrib/reindexdb/README | 9 ++ contrib/reindexdb/reindexdb | 223 ++++++++++++++++++++++++++++++++++++++++++ doc/src/sgml/maintenance.sgml | 4 +- 6 files changed, 235 insertions(+), 235 deletions(-) delete mode 100644 contrib/reindex/README delete mode 100644 contrib/reindex/reindex create mode 100644 contrib/reindexdb/README create mode 100644 contrib/reindexdb/reindexdb diff --git a/contrib/README b/contrib/README index 32304cad21..32a6cd53e7 100644 --- a/contrib/README +++ b/contrib/README @@ -149,7 +149,7 @@ pgcrypto - Cryptographic functions by Marko Kreen -reindex - +reindexdb - Reindexes a database by Shaun Thomas diff --git a/contrib/reindex/README b/contrib/reindex/README deleted file mode 100644 index b2a92a39f9..0000000000 --- a/contrib/reindex/README +++ /dev/null @@ -1,9 +0,0 @@ - - reindex - -Indexes are known to grow over time. Being as vacuum doesn't slow or -clean up after this growth, and there is no command to reindex all tables -in a database, it made sense to construct this utility to do it. - -Shaun Thomas - diff --git a/contrib/reindex/reindex b/contrib/reindex/reindex deleted file mode 100644 index 463ec4ffcf..0000000000 --- a/contrib/reindex/reindex +++ /dev/null @@ -1,223 +0,0 @@ -#!/bin/sh -# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # -# Package : reindexdb Version : $Revision: 1.3 $ -# Date : 05/08/2002 Author : Shaun Thomas -# Req : psql, sh, perl, sed Type : Utility -# -# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # - -# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # -# Function Definitions -# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # - -usage() -{ - echo "$CMDNAME reindexes a PostgreSQL database." - echo - echo "Usage:" - echo " $CMDNAME [options] [dbname]" - echo - echo "Options:" - echo " -h, --host=HOSTNAME Database server host" - echo " -p, --port=PORT Database server port" - echo " -U, --username=USERNAME Username to connect as" - echo " -W, --password Prompt for password" - echo " -d, --dbname=DBNAME Database to reindex" - echo " -a, --all Reindex all databases" - echo " -t, --table=TABLE Reindex specific table only" - echo " -i, --index=INDEX Reindex specific index only" - echo " -e, --echo Show the command being sent to the backend" - echo " -q, --quiet Don't write any output" - echo - echo "Read the description of the SQL command REINDEX for details." - echo - exit 0 -} - -# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # -# Program Body -# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # - -CMDNAME=`basename "$0"` -PATHNAME=`echo $0 | sed "s,$CMDNAME\$,,"` - -# Try valliantly to get the location of psql, since you can't ever -# really know where it has been placed. We'll start by trying the -# path. If that fails, we'll try the directory where this script -# resides. Then on to whereis, and finally locate. Wish us luck. - -if x=`psql -V 2>/dev/null | grep psql`; then - PSQL='psql' -elif [ -f ${PATHNAME}psql ]; then - PSQL=${PATHNAME}psql; -elif x=`whereis -b psql 2>/dev/null | sed 's/.* //'`; then - PSQL=$x -elif x=`locate -r bin/psql$ -n 1 2>/dev/null`; then - PSQL=$x -else - echo "$CMDNAME: Could not find psql to talk to postgres installation." - echo "Please make sure psql is in your path, or that this script is in" - echo "the same directory as psql was installed." - exit 1 -fi - -# Now, go through all of our command-line options and get each operation -# we said we'd accept in the usage listing. - -while [ "$#" -gt 0 ] -do - # Show help. - case "$1" in - --help|-\?) - usage - exit 0 - ;; - - # All of the following are postgres options. We can pass them on - # directly, without interpreting them in any way. We don't care. - # Anything that allows a space, we'll get the next *two* arguments - # and make sure to pass those along. - - --host|-h|-p|--port|-U|--username) - PSQLOPT="$PSQLOPT $1 $2" - shift - ;; - -h*|--host=*|-p*|--port=*|-U*|--username=*|-W|--password) - PSQLOPT="$PSQLOPT $1" - ;; - - # From this point on, we're setting options that are required for - # or only valid in This script. This includes which database(s) to - # reindex, which tables, or which indexes, and so on. - - # Echoing. We'll *not* use this in queries we use to get lists. - --echo|-e) - ECHOOPT="-e" - ;; - - # Do not echo messages. We'll direct all output to /dev/null. - --quiet|-q) - ECHOOPT="$ECHOOPT -o /dev/null" - quiet=1 - ;; - - # Reindex all databases, all tables, all applicable indexes. - --all|-a) - alldb=1 - ;; - - # Database to connect to, if not all of them. - --dbname|-d) - dbname="$2" - shift - ;; - -d*|--dbname=*) - dbname=`echo $1 | perl -pn -e 's/^--?d(bname=)?//'` - ;; - - # Reindex specific Table. Disables index reindexing. - --table|-t) - table="$2" - shift - ;; - -t*|--table=*) - table=`echo $1 | perl -pn -e 's/^--?t(able=)?//'` - ;; - - # Reindex specific index. Disables table reindexing. - --index|-i) - index="$2" - shift - ;; - -i*|--index=*) - index=`echo $1 | perl -pn -e 's/^--?i(ndex=)?//'` - ;; - - # Yeah, no options? Whine, and show usage. - -*) - echo "$CMDNAME: invalid option: $1" 1>&2 - usage; - exit 1 - ;; - - # Finally, it's possible that the database name was just the last - # unlabeled option. So, let's get that. - *) - dbname="$1" - ;; - esac - - shift # Shift off each argument as we loop. - -done - -# Get a list of all databases we'll be using. This first case is if we -# were asked to do all databases. -if [ "$alldb" ]; then - - if [ "$dbname" ] || [ "$index" ] || [ "$table" ]; then - echo "$CMDNAME: cannot reindex all databases and a specific database," 1>&2 - echo " table, or index at the same time." 1>&2 - exit 1 - fi - - # Execute a command to pull back all databases the user specified can - # connect to. That's the list we'll be using. It's also why it's - # a good idea for this to be a super-user. - dbname=`$PSQL $PSQLOPT -q -t -A -d template1 -c 'SELECT datname FROM pg_database WHERE datallowconn'` - -# Ok, if it's not all databases, make sure at least one database is -# specified before continuing. -elif [ -z "$dbname" ]; then - echo "$CMDNAME: missing required argument: database name" 1>&2 - usage; - exit 1 -fi - -# No. We can't reindex a specific index and table at the same time. -# Complain about this, and move on. -if [ "$table" ] && [ "$index" ]; then - echo "$CMDNAME: cannot reindex a specific table and a specific index" 1>&2 - echo "at the same time." 1>&2 - exit 1 -fi - -# If index was set, reindex that index. -if [ "$index" ]; then - $PSQL $PSQLOPT $ECHOOPT -c "REINDEX INDEX $index" -d $dbname - -# Ok, no index. Is there a specific table to reindex? -elif [ "$table" ]; then - $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE \"$table\"" -d $dbname - -# No specific table, no specific index, either we have a specific database, -# or were told to do all databases. Do it! -else - - sql="SELECT distinct tablename FROM pg_indexes WHERE tablename NOT LIKE 'pg_%'" - for db in $dbname; do - - # Only print which database we're currently reindexing if not in - # quiet mode, and we're doing more than one database. - [ "$alldb" ] && [ -z "$quiet" ] && echo "Reindexing $db" - - # Ok, reindex every table in the database. Use the same method - # we used to get a list of databases, and get a list of tables in this - # database that we may reindex. - tables=`$PSQL $PSQLOPT -q -t -A -d $db -c "$sql"` - for tab in $tables; do - $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE \"$tab\"" -d $db - done - - done - -fi - -# If any of the commands we've executed above failed in any way, bail -# out with an error. -if [ "$?" -ne 0 ]; then - echo "$CMDNAME: reindex $index $table $dbname failed" 1>&2 - exit 1 -fi - -exit 0 diff --git a/contrib/reindexdb/README b/contrib/reindexdb/README new file mode 100644 index 0000000000..21e6ecd7dc --- /dev/null +++ b/contrib/reindexdb/README @@ -0,0 +1,9 @@ + + reindexdb + +Indexes are known to grow over time. Being as vacuum doesn't slow or +clean up after this growth, and there is no command to reindex all tables +in a database, it made sense to construct this utility to do it. + +Shaun Thomas + diff --git a/contrib/reindexdb/reindexdb b/contrib/reindexdb/reindexdb new file mode 100644 index 0000000000..e9597e5fec --- /dev/null +++ b/contrib/reindexdb/reindexdb @@ -0,0 +1,223 @@ +#!/bin/sh +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # +# Package : reindexdb Version : $Revision: 1.1 $ +# Date : 05/08/2002 Author : Shaun Thomas +# Req : psql, sh, perl, sed Type : Utility +# +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # + +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # +# Function Definitions +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # + +usage() +{ + echo "$CMDNAME reindexes a PostgreSQL database." + echo + echo "Usage:" + echo " $CMDNAME [options] [dbname]" + echo + echo "Options:" + echo " -h, --host=HOSTNAME Database server host" + echo " -p, --port=PORT Database server port" + echo " -U, --username=USERNAME Username to connect as" + echo " -W, --password Prompt for password" + echo " -d, --dbname=DBNAME Database to reindex" + echo " -a, --all Reindex all databases" + echo " -t, --table=TABLE Reindex specific table only" + echo " -i, --index=INDEX Reindex specific index only" + echo " -e, --echo Show the command being sent to the backend" + echo " -q, --quiet Don't write any output" + echo + echo "Read the description of the SQL command REINDEX for details." + echo + exit 0 +} + +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # +# Program Body +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # + +CMDNAME=`basename "$0"` +PATHNAME=`echo $0 | sed "s,$CMDNAME\$,,"` + +# Try valliantly to get the location of psql, since you can't ever +# really know where it has been placed. We'll start by trying the +# path. If that fails, we'll try the directory where this script +# resides. Then on to whereis, and finally locate. Wish us luck. + +if x=`psql -V 2>/dev/null | grep psql`; then + PSQL='psql' +elif [ -f ${PATHNAME}psql ]; then + PSQL=${PATHNAME}psql; +elif x=`whereis -b psql 2>/dev/null | sed 's/.* //'`; then + PSQL=$x +elif x=`locate -r bin/psql$ -n 1 2>/dev/null`; then + PSQL=$x +else + echo "$CMDNAME: Could not find psql to talk to postgres installation." + echo "Please make sure psql is in your path, or that this script is in" + echo "the same directory as psql was installed." + exit 1 +fi + +# Now, go through all of our command-line options and get each operation +# we said we'd accept in the usage listing. + +while [ "$#" -gt 0 ] +do + # Show help. + case "$1" in + --help|-\?) + usage + exit 0 + ;; + + # All of the following are postgres options. We can pass them on + # directly, without interpreting them in any way. We don't care. + # Anything that allows a space, we'll get the next *two* arguments + # and make sure to pass those along. + + --host|-h|-p|--port|-U|--username) + PSQLOPT="$PSQLOPT $1 $2" + shift + ;; + -h*|--host=*|-p*|--port=*|-U*|--username=*|-W|--password) + PSQLOPT="$PSQLOPT $1" + ;; + + # From this point on, we're setting options that are required for + # or only valid in This script. This includes which database(s) to + # reindex, which tables, or which indexes, and so on. + + # Echoing. We'll *not* use this in queries we use to get lists. + --echo|-e) + ECHOOPT="-e" + ;; + + # Do not echo messages. We'll direct all output to /dev/null. + --quiet|-q) + ECHOOPT="$ECHOOPT -o /dev/null" + quiet=1 + ;; + + # Reindex all databases, all tables, all applicable indexes. + --all|-a) + alldb=1 + ;; + + # Database to connect to, if not all of them. + --dbname|-d) + dbname="$2" + shift + ;; + -d*|--dbname=*) + dbname=`echo $1 | perl -pn -e 's/^--?d(bname=)?//'` + ;; + + # Reindex specific Table. Disables index reindexing. + --table|-t) + table="$2" + shift + ;; + -t*|--table=*) + table=`echo $1 | perl -pn -e 's/^--?t(able=)?//'` + ;; + + # Reindex specific index. Disables table reindexing. + --index|-i) + index="$2" + shift + ;; + -i*|--index=*) + index=`echo $1 | perl -pn -e 's/^--?i(ndex=)?//'` + ;; + + # Yeah, no options? Whine, and show usage. + -*) + echo "$CMDNAME: invalid option: $1" 1>&2 + usage; + exit 1 + ;; + + # Finally, it's possible that the database name was just the last + # unlabeled option. So, let's get that. + *) + dbname="$1" + ;; + esac + + shift # Shift off each argument as we loop. + +done + +# Get a list of all databases we'll be using. This first case is if we +# were asked to do all databases. +if [ "$alldb" ]; then + + if [ "$dbname" ] || [ "$index" ] || [ "$table" ]; then + echo "$CMDNAME: cannot reindex all databases and a specific database," 1>&2 + echo " table, or index at the same time." 1>&2 + exit 1 + fi + + # Execute a command to pull back all databases the user specified can + # connect to. That's the list we'll be using. It's also why it's + # a good idea for this to be a super-user. + dbname=`$PSQL $PSQLOPT -q -t -A -d template1 -c 'SELECT datname FROM pg_database WHERE datallowconn'` + +# Ok, if it's not all databases, make sure at least one database is +# specified before continuing. +elif [ -z "$dbname" ]; then + echo "$CMDNAME: missing required argument: database name" 1>&2 + usage; + exit 1 +fi + +# No. We can't reindex a specific index and table at the same time. +# Complain about this, and move on. +if [ "$table" ] && [ "$index" ]; then + echo "$CMDNAME: cannot reindex a specific table and a specific index" 1>&2 + echo "at the same time." 1>&2 + exit 1 +fi + +# If index was set, reindex that index. +if [ "$index" ]; then + $PSQL $PSQLOPT $ECHOOPT -c "REINDEX INDEX $index" -d $dbname + +# Ok, no index. Is there a specific table to reindex? +elif [ "$table" ]; then + $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE \"$table\"" -d $dbname + +# No specific table, no specific index, either we have a specific database, +# or were told to do all databases. Do it! +else + + sql="SELECT distinct tablename FROM pg_indexes WHERE tablename NOT LIKE 'pg_%'" + for db in $dbname; do + + # Only print which database we're currently reindexing if not in + # quiet mode, and we're doing more than one database. + [ "$alldb" ] && [ -z "$quiet" ] && echo "Reindexing $db" + + # Ok, reindex every table in the database. Use the same method + # we used to get a list of databases, and get a list of tables in this + # database that we may reindex. + tables=`$PSQL $PSQLOPT -q -t -A -d $db -c "$sql"` + for tab in $tables; do + $PSQL $PSQLOPT $ECHOOPT -c "REINDEX TABLE \"$tab\"" -d $db + done + + done + +fi + +# If any of the commands we've executed above failed in any way, bail +# out with an error. +if [ "$?" -ne 0 ]; then + echo "$CMDNAME: reindex $index $table $dbname failed" 1>&2 + exit 1 +fi + +exit 0 diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index c0855c0fe1..9793cd9350 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -1,5 +1,5 @@ @@ -393,7 +393,7 @@ VACUUM As a solution, you can use the REINDEX command periodically to discard pages used by deleted rows. There is also - contrib/reindex which can reindex an entire database. + contrib/reindexdb which can reindex an entire database. -- cgit v1.2.1