How to reindex Postgres 9.1.3 from the command line

Question

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?

Use the password file, on Windows: %APPDATA%\postgresql\pgpass.conf. Details: stackoverflow.com/a/15593100/939860 — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 1, 2015 at 15:57

Colonel Thirty Two · Accepted Answer · 2015-07-01 14:59:54Z

5

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.

edited Jul 1, 2015 at 14:59

answered Jul 1, 2015 at 14:42

Colonel Thirty Two

26.9k9 gold badges52 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phoenix14830 Over a year ago

If this is in a scheduled task batch file, how would I account for entering the password in a non-interactive environment?

Max Sherbakov · Accepted Answer · 2018-03-21 14:47:19Z

2

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

answered Mar 21, 2018 at 14:47

Max Sherbakov

1,97517 silver badges21 bronze badges

Comments

Phoenix14830 · Accepted Answer · 2016-01-27 14:39:47Z

1

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

edited Jan 27, 2016 at 14:39

answered Jul 1, 2015 at 17:47

Phoenix14830

3801 gold badge8 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to reindex Postgres 9.1.3 from the command line

3 Answers 3

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Linked

Related