clusterdb takes an ACCESS EXCLUSIVE lock on every table it touches, so running it against production can block traffic for the whole window. This guide builds a repeatable way to run it against PostgreSQL 16.15, select one or more tables when needed, and check connection failures without guessing. The command reclusters tables that already have a remembered clustering index; it does not choose an index for a table that has never been clustered.
sudo; use an elevated account only if your normal account cannot read the client configuration or reach the server socket.Check the executable and version before copying an example into a script. This is read-only and does not need elevated privileges:
$ command -v clusterdb
/usr/bin/clusterdb
$ clusterdb --version
clusterdb (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)The local installation is PostgreSQL 16.15 on Ubuntu 24.04. Option names and connection defaults below are taken from that installed manpage. If your version differs, run clusterdb --help on that host before adapting a script.
Checkpoint: the version is known, and the command you will run resolves to the expected PostgreSQL installation.
clusterdb has no separate preview mode. A deliberately invalid database name is a safe way to confirm that the client can reach the server and that errors are visible, but it does not test permissions on your real target:
$ clusterdb --no-password --dbname=does-not-exist
clusterdb: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "does-not-exist" does not existThe command exits non-zero. That result is expected for this check and confirms that this client reached a PostgreSQL server which rejected the named database. If it instead reports that the socket or server is unavailable, check the service and connection settings with your normal PostgreSQL administration process.
For a real target, make the connection explicit while testing. Replace every placeholder, and do not put a password in the command line:
$ clusterdb --no-password \
--host=DB_HOST \
--port=5432 \
--username=DB_USER \
--dbname=APP_DATABASE \
--verbose--no-password is useful for automation because it fails rather than hanging for input. For an interactive run, omit it to allow the normal password prompt, or use --password to request the prompt before the connection attempt. Keep credentials in PostgreSQL's supported mechanisms, such as a correctly protected .pgpass file, rather than exposing them in shell history.
With one database name and no --table, clusterdb asks PostgreSQL to recluster every table in that database which has previously been clustered. Tables without a remembered clustering index are left alone. This is maintenance, not an index-selection command.
Do not treat a successful exit as a harmless check. Clustering physically rewrites table data. PostgreSQL takes an ACCESS EXCLUSIVE lock on each table while it works, blocking reads and writes for that table. It can also need temporary disk space comparable to the table and its indexes, and sometimes more depending on the chosen method.
Warning: run the actual command in a maintenance window appropriate for the workload. Check free space and the expected table sizes first. A busy production database can experience blocked application traffic while clustering is in progress.
When the maintenance window is open, run the database-level operation with a specific connection:
$ clusterdb --host=DB_HOST --port=5432 \
--username=DB_USER --dbname=APP_DATABASE --verbose
The --verbose option asks the utility to print detailed processing information. Exact messages depend on the server and the tables it finds. A zero exit status means the utility completed successfully; preserve the output in an operations log if you need an audit trail.
Recovery: there is no undo command that restores the former physical row order. The operation changes storage layout, not the logical contents of the tables. If you must stop a long-running operation, use your established PostgreSQL cancellation procedure and check the server logs afterwards. Do not kill the database service as a first response.
Use repeated --table options when only a small, known set needs maintenance. The table name can be schema-qualified:
$ clusterdb --host=DB_HOST --username=DB_USER --dbname=APP_DATABASE \
--table=public.orders \
--table=public.order_items \
--verboseEach selected table must already have a clustering index recorded by PostgreSQL. If a name contains characters meaningful to the shell, quote the complete argument. Do not paste an untrusted table name into a privileged maintenance script without validating it first.
Checkpoint: review the final database, schema-qualified table names, connection identity and maintenance window before pressing Enter.
The --all option visits all databases. It first connects to a maintenance database to obtain the list, using postgres by default or template1 if postgres is absent:
$ clusterdb --all --maintenance-db=postgres \
--host=DB_HOST --port=5432 --username=DB_USER --verboseThis can create a much larger maintenance event than the single-database form. Use it only after checking the database list, permissions, available space and expected application impact. If the maintenance database is remote or needs different connection details, provide a suitable connection string according to your local PostgreSQL policy.
PGHOST, PGPORT, PGUSER or PGDATABASE for repeatable defaults, or keep the corresponding options explicit in automation. An omitted database name uses PGDATABASE, then the connection user name.sudo. Check the selected role, authentication rules and password source. In a batch job, use --no-password so the failure is immediate and visible.CLUSTER command first, then use clusterdb for later reclustering. That separate operation needs its own review because it determines the physical ordering to maintain.