|
| 1 | +#!/bin/sh |
| 2 | +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # |
| 3 | +# Package : reindexdb Version : $Revision: 1.1 $ |
| 4 | +# Date : 05/08/2002 Author : Shaun Thomas |
| 5 | +# Req : psql, sh, perl, sed Type : Utility |
| 6 | +# |
| 7 | +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # |
| 8 | + |
| 9 | +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # |
| 10 | +# Function Definitions |
| 11 | +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # |
| 12 | + |
| 13 | +functionusage() |
| 14 | +{ |
| 15 | +echo"$CMDNAME reindexes a PostgreSQL database." |
| 16 | +echo |
| 17 | +echo"Usage:" |
| 18 | +echo"$CMDNAME [options] [dbname]" |
| 19 | +echo |
| 20 | +echo"Options:" |
| 21 | +echo" -h, --host=HOSTNAME Database server host" |
| 22 | +echo" -p, --port=PORT Database server port" |
| 23 | +echo" -U, --username=USERNAME Username to connect as" |
| 24 | +echo" -W, --password Prompt for password" |
| 25 | +echo" -d, --dbname=DBNAME Database to reindex" |
| 26 | +echo" -a, --all Reindex all databases" |
| 27 | +echo" -t, --table=TABLE Reindex specific table only" |
| 28 | +echo" -i, --index=INDEX Reindex specific index only" |
| 29 | +echo" -e, --echo Show the command being sent to the backend" |
| 30 | +echo" -q, --quiet Don't write any output" |
| 31 | +echo |
| 32 | +echo"Read the description of the SQL command REINDEX for details." |
| 33 | +echo |
| 34 | +exit 0 |
| 35 | +} |
| 36 | + |
| 37 | +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # |
| 38 | +# Program Body |
| 39 | +# -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- # |
| 40 | + |
| 41 | +CMDNAME=`basename"$0"` |
| 42 | +PATHNAME=`echo$0| sed"s,$CMDNAME\$,,"` |
| 43 | + |
| 44 | +# Try valliantly to get the location of psql, since you can't ever |
| 45 | +# really know where it has been placed. We'll start by trying the |
| 46 | +# path. If that fails, we'll try the directory where this script |
| 47 | +# resides. Then on to whereis, and finally locate. Wish us luck. |
| 48 | + |
| 49 | +if x=`psql -V2>/dev/null| grep psql`;then |
| 50 | + PSQL='psql' |
| 51 | +elif [-f${PATHNAME}psql ];then |
| 52 | + PSQL=${PATHNAME}psql; |
| 53 | +elif x=`whereis -b psql2>/dev/null| sed's/.* //'`;then |
| 54 | + PSQL=$x |
| 55 | +elif x=`locate -r bin/psql$ -n 12>/dev/null`;then |
| 56 | + PSQL=$x |
| 57 | +else |
| 58 | +echo"$CMDNAME: Could not find psql to talk to postgres installation." |
| 59 | +echo"Please make sure psql is in your path, or that this script is in" |
| 60 | +echo"the same directory as psql was installed." |
| 61 | +exit 1 |
| 62 | +fi |
| 63 | + |
| 64 | +# Now, go through all of our command-line options and get each operation |
| 65 | +# we said we'd accept in the usage listing. |
| 66 | + |
| 67 | +while ["$#"-gt 0 ] |
| 68 | +do |
| 69 | +# Show help. |
| 70 | +case"$1"in |
| 71 | + --help|-\?) |
| 72 | + usage |
| 73 | +exit 0 |
| 74 | + ;; |
| 75 | + |
| 76 | +# All of the following are postgres options. We can pass them on |
| 77 | +# directly, without interpreting them in any way. We don't care. |
| 78 | +# Anything that allows a space, we'll get the next *two* arguments |
| 79 | +# and make sure to pass those along. |
| 80 | + |
| 81 | + --host|-h|-p|--port|-U|--username) |
| 82 | + PSQLOPT="$PSQLOPT$1$2" |
| 83 | +shift |
| 84 | + ;; |
| 85 | + -h*|--host=*|-p*|--port=*|-U*|--username=*|-W|--password) |
| 86 | + PSQLOPT="$PSQLOPT$1" |
| 87 | + ;; |
| 88 | + |
| 89 | +# From this point on, we're setting options that are required for |
| 90 | +# or only valid in This script. This includes which database(s) to |
| 91 | +# reindex, which tables, or which indexes, and so on. |
| 92 | + |
| 93 | +# Echoing. We'll *not* use this in queries we use to get lists. |
| 94 | + --echo|-e) |
| 95 | + ECHOOPT="-e" |
| 96 | + ;; |
| 97 | + |
| 98 | +# Do not echo messages. We'll direct all output to /dev/null. |
| 99 | + --quiet|-q) |
| 100 | + ECHOOPT="$ECHOOPT -o /dev/null" |
| 101 | + quiet=1 |
| 102 | + ;; |
| 103 | + |
| 104 | +# Reindex all databases, all tables, all applicable indexes. |
| 105 | + --all|-a) |
| 106 | + alldb=1 |
| 107 | + ;; |
| 108 | + |
| 109 | +# Database to connect to, if not all of them. |
| 110 | + --dbname|-d) |
| 111 | + dbname="$2" |
| 112 | +shift |
| 113 | + ;; |
| 114 | + -d*|--dbname=*) |
| 115 | + dbname=`echo$1| perl -pn -e's/^--?d(bname=)?//'` |
| 116 | + ;; |
| 117 | + |
| 118 | +# Reindex specific Table. Disables index reindexing. |
| 119 | + --table|-t) |
| 120 | + table="$2" |
| 121 | +shift |
| 122 | + ;; |
| 123 | + -t*|--table=*) |
| 124 | + table=`echo$1| perl -pn -e's/^--?t(able=)?//'` |
| 125 | + ;; |
| 126 | + |
| 127 | +# Reindex specific index. Disables table reindexing. |
| 128 | + --index|-i) |
| 129 | + index="$2" |
| 130 | +shift |
| 131 | + ;; |
| 132 | + -i*|--index=*) |
| 133 | + index=`echo$1| perl -pn -e's/^--?i(ndex=)?//'` |
| 134 | + ;; |
| 135 | + |
| 136 | +# Yeah, no options? Whine, and show usage. |
| 137 | + -*) |
| 138 | +echo"$CMDNAME: invalid option:$1"1>&2 |
| 139 | + usage; |
| 140 | +exit 1 |
| 141 | + ;; |
| 142 | + |
| 143 | +# Finally, it's possible that the database name was just the last |
| 144 | +# unlabeled option. So, let's get that. |
| 145 | +*) |
| 146 | + dbname="$1" |
| 147 | + ;; |
| 148 | +esac |
| 149 | + |
| 150 | +shift# Shift off each argument as we loop. |
| 151 | + |
| 152 | +done |
| 153 | + |
| 154 | +# Get a list of all databases we'll be using. This first case is if we |
| 155 | +# were asked to do all databases. |
| 156 | +if ["$alldb" ];then |
| 157 | + |
| 158 | +if ["$dbname" ]|| ["$index" ]|| ["$table" ];then |
| 159 | +echo"$CMDNAME: cannot reindex all databases and a specific database,"1>&2 |
| 160 | +echo" table, or index at the same time."1>&2 |
| 161 | +exit 1 |
| 162 | +fi |
| 163 | + |
| 164 | +# Execute a command to pull back all databases the user specified can |
| 165 | +# connect to. That's the list we'll be using. It's also why it's |
| 166 | +# a good idea for this to be a super-user. |
| 167 | + dbname=`$PSQL$PSQLOPT -q -t -A -d template1 -c'SELECT datname FROM pg_database WHERE datallowconn'` |
| 168 | + |
| 169 | +# Ok, if it's not all databases, make sure at least one database is |
| 170 | +# specified before continuing. |
| 171 | +elif [-z"$dbname" ];then |
| 172 | +echo"$CMDNAME: missing required argument: database name"1>&2 |
| 173 | + usage; |
| 174 | +exit 1 |
| 175 | +fi |
| 176 | + |
| 177 | +# No. We can't reindex a specific index and table at the same time. |
| 178 | +# Complain about this, and move on. |
| 179 | +if ["$table" ]&& ["$index" ];then |
| 180 | +echo"$CMDNAME: cannot reindex a specific table and a specific index"1>&2 |
| 181 | +echo"at the same time."1>&2 |
| 182 | +exit 1 |
| 183 | +fi |
| 184 | + |
| 185 | +# If index was set, reindex that index. |
| 186 | +if ["$index" ];then |
| 187 | +$PSQL$PSQLOPT$ECHOOPT -c"REINDEX INDEX$index" -d$dbname |
| 188 | + |
| 189 | +# Ok, no index. Is there a specific table to reindex? |
| 190 | +elif ["$table" ];then |
| 191 | +$PSQL$PSQLOPT$ECHOOPT -c"REINDEX TABLE$table" -d$dbname |
| 192 | + |
| 193 | +# No specific table, no specific index, either we have a specific database, |
| 194 | +# or were told to do all databases. Do it! |
| 195 | +else |
| 196 | + |
| 197 | + sql="SELECT distinct tablename FROM pg_indexes WHERE tablename NOT LIKE 'pg_%'" |
| 198 | +fordbin$dbname;do |
| 199 | + |
| 200 | +# Only print which database we're currently reindexing if not in |
| 201 | +# quiet mode, and we're doing more than one database. |
| 202 | + ["$alldb" ]&& [-z"$quiet" ]&&echo"Reindexing$db" |
| 203 | + |
| 204 | +# Ok, reindex every table in the database. Use the same method |
| 205 | +# we used to get a list of databases, and get a list of tables in this |
| 206 | +# database that we may reindex. |
| 207 | + tables=`$PSQL$PSQLOPT -q -t -A -d$db -c"$sql"` |
| 208 | +fortabin$tables;do |
| 209 | +$PSQL$PSQLOPT$ECHOOPT -c"REINDEX TABLE$tab" -d$db |
| 210 | +done |
| 211 | + |
| 212 | +done |
| 213 | + |
| 214 | +fi |
| 215 | + |
| 216 | +# If any of the commands we've executed above failed in any way, bail |
| 217 | +# out with an error. |
| 218 | +if ["$?"-ne 0 ];then |
| 219 | +echo"$CMDNAME: reindex$index$table$dbname failed"1>&2 |
| 220 | +exit 1 |
| 221 | +fi |
| 222 | + |
| 223 | +exit 0 |