CODEWITHSUNDEEP
postgresqlpostgresql-9.1reindex
Phoenix14830
Phoenix14830

Reputation: 380

How to reindex Postgres 9.1.3 from the command line

I have a series of deletes and updates on a few tables in a Postgres database I manage. It has been suggested to schedule a reindex after the series of deletes as a solution to the 10 minute next-step update freezing infinitely (as it randomly does.) The DOS instructions provide this:

Usage:
  reindexdb [OPTION]... [DBNAME]

Options:
  -a, --all                 reindex all databases
  -d, --dbname=DBNAME       database to reindex
  -e, --echo                show the commands being sent to the server
  -i, --index=INDEX         recreate specific index only
  -q, --quiet               don't write any messages
  -s, --system              reindex system catalogs
  -t, --table=TABLE         reindex specific table only
  --help                    show this help, then exit
  --version                 output version information, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt

We have to use version 9.1.3 as this is the corporate standard. I have tried every option I can think of but it won't take the command to reindex:

reindexdb.exe -U username=MyUserName -W MyPassword -t table=MyDatabase.MyTable

I've also tried

reindexdb.exe -U MyUserName -W MyPassword -t MyDatabase.MyTable

and 

reindexdb.exe -U MyUserName -W MyPassword -t MyTable -d MyDatabase

...but they all end with the error:

reindexdb: too many command-line arguments (first is "-t")

Does anyone have a working sample that would be able to clarify what the right syntax is?

Upvotes: 3

Views: 6488

Answers (3)

Max Sherbakov
Max Sherbakov

Reputation: 1955

Any of these can be forced by adding the keyword FORCE after the command

Recreate a single index, myindex:

REINDEX INDEX myindex

Recreate all indices in a table, mytable:

REINDEX TABLE mytable

Recreate all indices in schema public:

REINDEX SCHEMA public

Recreate all indices in database postgres:

REINDEX DATABASE postgres

Recreate all indices on system catalogs in database postgres:

REINDEX SYSTEM postgres

link

Upvotes: 2

Phoenix14830
Phoenix14830

Reputation: 380

This did it:

reindexdb.exe -d MyDatabase -U postgres -t MyTable

As @Colonel Thirty Two and @Erwin Brandstetter noted, removing the password entirely is possible through %APPDATA%\postgresql\pgpass.conf

Upvotes: 1

Colonel Thirty Two
Colonel Thirty Two

Reputation: 26569

Remove MyPassword from your arguments, and enter it in when Postgres prompts you for it.

-W simply causes Postgres to prompt for the password; it doesn't accept the password itself. You should never specify passwords on the command line, as it's usually logged.

If you need to run it non-interactively, either set the PGPASSWORD environment variable or create a pgpass file.

Upvotes: 5

Related Questions