4.3 Client & Server Command-Line Utilities

4.3 Client & Server Command-Line Utilities

[!NOTE] Command-Line Wrapper Philosophy: PostgreSQL ships with a suite of dedicated command-line client binaries designed for operating system shell integration. Rather than requiring administrators to launch psql and manually type DDL or maintenance statements, these executables wrap core SQL commands into standard POSIX-compliant CLI utilities with standard return codes.

Mastering these utilities—and understanding how client environment variables and credential files control their execution—is essential for database administration and a heavily tested area of the PostgreSQL Associate exam.

---

Database Management Wrappers: createdb and dropdb

The createdb and dropdb utilities are client wrapper programs that send CREATE DATABASE and DROP DATABASE SQL commands to the server over a standard client connection.

1. Creating Databases with createdb

createdb [connection-options...] [options...] dbname [description]

Key Command-Line Options

• -O OWNER (--owner=OWNER): Assigns the database owner role (defaults to the connecting user).
• -T TEMPLATE (--template=TEMPLATE): Specifies the template database to clone (defaults to template1).
• -E ENCODING (--encoding=ENCODING): Sets the database character set encoding (e.g., UTF8).
• -l LOCALE (--locale=LOCALE): Sets the default collation and character classification locale.
• -D TABLESPACE (--tablespace=TABLESPACE): Sets the default tablespace for the database.
• -e (--echo): Prints the generated SQL query sent to the server (e.g., CREATE DATABASE sales OWNER app_admin;).

# Create a UTF-8 production database owned by app_admin in tablespace fast_ssd
createdb -h localhost -p 5432 -U postgres -O app_admin -E UTF8 -D fast_ssd sales_prod

2. Dropping Databases with dropdb

dropdb [connection-options...] [options...] dbname

Dropping a database permanently deletes all catalogs, tables, indexes, and stored data associated with that database. It is completely irreversible.

Key Command-Line Options

• -i (--interactive): Prompts for interactive user confirmation (Are you sure? (y/n)) before sending the drop command.
• -f (--force): In PostgreSQL 13 and later, attempts to forcefully terminate all active client backend connections connected to the target database before dropping it. Without this option, dropdb aborts with an error if other sessions are active.
• -e (--echo): Echoes the generated DROP DATABASE command sent to the server.

---

Role and User Management Wrappers: createuser and dropuser

In PostgreSQL, user accounts and groups are unified under the concept of roles. The createuser and dropuser binaries wrap the SQL statements CREATE ROLE ... LOGIN and DROP ROLE.

1. Provisioning Roles with createuser

createuser [connection-options...] [options...] [username]

# Create a standard application user with login rights and password prompt
createuser -h db.internal -U postgres -l -P app_service

# Create a database administrator with CREATEDB and CREATEROLE permissions
createuser -h db.internal -U postgres -d -r db_manager

2. Decommissioning Roles with dropuser

dropuser [connection-options...] [options...] username

• -i (--interactive): Requests interactive confirmation before executing DROP ROLE.
• Constraint Rule: A role cannot be dropped if it owns database objects (tables, schemas, tablespaces) or has privileges granted on objects in any database. Objects must be reassigned (REASSIGN OWNED BY) or dropped (DROP OWNED BY) first.

---

Routine Maintenance Wrappers: vacuumdb and reindexdb

PostgreSQL administrators automate cluster maintenance using vacuumdb and reindexdb, which eliminate bloat and refresh planner statistics without requiring interactive SQL consoles.

1. Automated Vacuuming with vacuumdb

vacuumdb issues SQL VACUUM and ANALYZE commands across designated tables or entire clusters:

vacuumdb [connection-options...] [options...] [dbname]

Key Operational Options

• -a (--all): Vacuums all databases across the entire PostgreSQL cluster.
• -d DBNAME: Specifies an individual target database.
• -t TABLE [(COL1, COL2)]: Vacuums only the specified table (and optionally analyzes only specific columns).
• -v (--verbose): Outputs detailed vacuum statistics for every processed table.
• -z (--analyze): Calculates optimizer statistics (runs ANALYZE) alongside the vacuum operation.
• -f (--full): Executes VACUUM FULL. Rebuilds table contents to physical minimum storage and releases free space back to the OS filesystem. Caution: Acquires an exclusive lock (AccessExclusiveLock), blocking all reads and writes!
• -F (--freeze): Aggressively freezes transaction IDs (txid), advancing the table's freeze horizon.
• -j JOBS (--jobs=JOBS): Executes vacuum tasks in parallel by opening $N$ concurrent client connections, processing multiple tables simultaneously.

# Nightly cluster maintenance: vacuum and analyze all databases using 4 parallel workers
vacuumdb -h localhost -U postgres -a -z -j 4

2. Index Rebuilding with reindexdb

reindexdb rebuilds indexes to eliminate internal index bloat, repair index corruption, or apply new collation rules:

reindexdb [connection-options...] [options...] [dbname]

Key Operational Options

• -a (--all): Reindexes all databases in the cluster.
• -t TABLE: Reindexes all indexes residing on the specified table.
• -i INDEX: Reindexes only a single designated index.
• -s (--system): Reindexes the cluster's system catalog indexes in the target database.
• -c (--concurrently): Rebuilds indexes concurrently (REINDEX ... CONCURRENTLY). This allows normal SELECT, INSERT, UPDATE, and DELETE operations to continue during the index rebuild without acquiring an exclusive table write lock.

# Reindex the high-traffic orders table concurrently in production without blocking transactions
reindexdb -h db.prod -U postgres -d sales -t orders -c

---

Availability Probing with pg_isready

The pg_isready binary is a specialized, lightweight connection probe designed specifically for monitoring systems, load balancers, container orchestration health checks (Kubernetes liveness and readiness probes), and failover scripts.

pg_isready [connection-options...]

Unlike psql or custom Python scripts, pg_isready does not authenticate or run SQL queries. It performs a minimal handshake with the postmaster to determine if the server instance is listening and accepting client connections.

Command-Line Arguments

• -h HOSTNAME: Database server host or socket directory.
• -p PORT: Database listening port (default: 5432).
• -d DBNAME: Database name to probe.
• -t SECONDS (--timeout=SECONDS): Maximum seconds to wait before reporting that the server is not responding. The default is 3 seconds, and setting it to 0 disables the timeout rather than waiting indefinitely by default.
• -q (--quiet): Suppresses standard output text; execution status is determined exclusively via the process exit code.

The Four Exit Codes of pg_isready

# Kubernetes readiness probe script
pg_isready -h 127.0.0.1 -p 5432 -q
STATUS=$?

case $STATUS in
  0) echo "PostgreSQL is accepting connections." ;;
  1) echo "PostgreSQL is starting up or rejecting connections." ;;
  2) echo "PostgreSQL is down or unreachable!" ;;
  3) echo "Configuration error in pg_isready arguments." ;;
esac

---

Client Environment Variables

All PostgreSQL client utilities (psql, createdb, dropdb, vacuumdb, pg_dump, pg_isready) are built on libpq. When connection arguments are omitted from the CLI, libpq reads configuration from standard environment variables:

• PGHOST: Host name or UNIX domain socket directory path.
• PGPORT: TCP port number (default 5432).
• PGUSER: Database username to connect as.
• PGDATABASE: Target database name.
• PGPASSWORD: Database password.

  [!CAUTION] Security Risk: Setting PGPASSWORD directly in environment variables is strongly discouraged in shared multi-user environments because other users can inspect environment variables via /proc/PID/environ or process monitoring utilities.

• PGSSLMODE: Enforces client SSL behavior (disable, allow, prefer, require, verify-ca, verify-full).
• PGPASSFILE: Overrides the default location of the password file (~/.pgpass).

Precedence Hierarchy

1. Explicit CLI Flags (-h, -p, -U, -d)
2. Connection URI strings (postgresql://...)
3. Environment Variables (PGHOST, PGPORT, PGUSER, PGDATABASE)
4. Compiled System Defaults (Local socket, port 5432, OS username, DB matching username)

---

The ~/.pgpass Password File and Security Permissions

The standard, secure method for automating client authentication without interactive password prompts is the ~/.pgpass password file.

File Format

The file contains one or more lines of five colon-delimited fields:

hostname:port:database:username:password

Formatting Rules and Wildcards

1. Wildcards (*): The first four fields can contain an asterisk (*) to match any value. The fifth field (the password) is never a wildcard.
2. Escaping: If any field value contains a literal colon (:) or backslash (\), it must be escaped with a preceding backslash (\: or \\).
3. First-Match Precedence: libpq reads ~/.pgpass line by line from top to bottom and uses the password from the first line that matches the connection parameters.

# ~/.pgpass Example
# Exact match for production analytics
db-primary.internal.net:5432:analytics:etl_user:SecretK3y!#

# Wildcard match for local development databases
localhost:5432:*:dev_user:LocalDevPass99

# Catch-all fallback for a dedicated backup user across all hosts
*:5432:*:backup_admin:SecureB@ckup2026

Strict File Permission Requirements (0600)

[!CAUTION] Strict 0600 Permission Enforcement: Because ~/.pgpass contains plaintext passwords, libpq strictly enforces POSIX file permission checks on Unix/Linux systems.

The file must NOT allow any read or write access to group or world! If the file permissions are anything looser than 0600 (-rw-------)—such as 0644 (-rw-r--r--) or 0660 (-rw-rw----)—libpq completely ignores the file and either prompts for a password interactively or fails authentication.

# Create and secure the password file on Linux
touch ~/.pgpass
chmod 0600 ~/.pgpass
echo "10.0.1.25:5432:production:app_user:V3ryStr0ngP@ss" >> ~/.pgpass

On Windows systems, the file is named %APPDATA%\postgresql\pgpass.conf, and equivalent restricted access controls are enforced via Windows Access Control Lists (ACLs).

---

Exam Tips and Common Pitfalls

• Exam Trap: Permissions on ~/.pgpass: This is one of the most frequently tested concepts on PostgreSQL certifications. If an automated script fails with a password prompt despite a valid .pgpass file existing, verify the file mode: it must be strictly 0600. Anything looser causes libpq to ignore it.
• Exam Trap: pg_isready Exit Codes: Memorize the four exit codes: 0 = running & accepting; 1 = running but rejecting; 2 = no response (down); 3 = invalid CLI flags (no attempt).
• Exam Trap: pg_isready Timeout Default: The connection timeout defaults to 3 seconds, not to an unlimited wait. Passing -t 0 removes the timeout entirely — the opposite of what the value suggests.
• Exam Trap: reindexdb Blocking: Standard reindexdb locks tables against concurrent writes. To rebuild indexes online in a live production environment without blocking client DML, you must specify the -c / --concurrently flag.