Make PostgreSQL Client Selection Predictable with pg_wrapper

You will finish with a deliberate way to choose the PostgreSQL cluster used by psql and related client commands, plus a clear method for setting defaults without guessing which configuration won. This guide uses the Debian postgresql-client-common package, version 257build1.1 on the tested machine, with PostgreSQL 16.15 installed.

Allow about 15 minutes. You need a shell and a PostgreSQL client package. The checks are read-only. The optional system-wide configuration step changes where users connect by default, so it needs care and elevated privileges.

1. Confirm that the client is using the wrapper

pg_wrapper is normally not run by typing its name. Client commands such as psql, createdb and dropuser are links to it. The wrapper chooses a suitable version and cluster, then runs the requested client.

$ command -v psql
/usr/bin/psql
$ readlink -f "$(command -v psql)"
/usr/share/postgresql-common/pg_wrapper
$ psql --version
psql (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

Your version string may differ. The useful checkpoint is that the resolved path ends in pg_wrapper. The wrapper's selection rules apply to other PostgreSQL client links in the same way, although some commands have their own version-selection rules.

2. Select a cluster for one command

Use --cluster when the command must target a particular local cluster. Its local form is version/cluster. The cluster name is not the database name.

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main ...
$ psql --cluster 16/main --version
psql (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

pg_lsclusters is a convenient local inventory command when it is installed. The final command only asks for the client version, so it does not require a database login. For a real connection, add the database or another ordinary psql option, for example:

$ psql --cluster 16/main --dbname=reporting --command='select current_database(), current_user;'
 current_database | current_user
------------------+--------------
 reporting        | andy

That query changes no data. Replace reporting with a database you are authorised to access. If the command could write data, review it as an ordinary PostgreSQL operation before running it. The --cluster selector itself does not grant database permissions.

3. Understand which selector wins

The wrapper checks selectors in a fixed order. The first applicable choice wins:

1. --host selects a host, so normal local cluster selection stops.
2. --cluster selects a local cluster or a remote host:port.
3. PGHOST selects a host. The default version and port then come from the command line, PGPORT, or port 5432.
4. PGCLUSTER supplies a version/cluster selection.
5. A supplied port selects the local cluster using that port when no host is supplied.
6. ~/.postgresqlrc, then /etc/postgresql-common/user_clusters, supplies a configured default.
7. If no rule matches, one local cluster is selected; with several, the cluster on port 5432 is preferred.

The common distraction is PGHOST. Once it is set, your ~/.postgresqlrc cluster choice is not consulted. Inspect the environment before debugging a surprising connection:

printf 'PGHOST=%s\nPGPORT=%s\nPGCLUSTER=%s\n' \
    "${PGHOST-}" "${PGPORT-}" "${PGCLUSTER-}"
env | grep -E '^(PGHOST|PGPORT|PGCLUSTER)=' || true

Checkpoint: for a single command, prefer an explicit selector and record it in the command or script. For a shell session, clear an accidental host with unset PGHOST only if that is genuinely the intended connection.

4. Use a temporary environment default

PGCLUSTER is useful when several commands in one operation should use the same local cluster:

$ PGCLUSTER=16/main psql --dbname=reporting --command='select current_database();'
 current_database
------------------
 reporting

This assignment applies only to that command. For a short shell session, export it and remove it afterwards:

export PGCLUSTER=16/main
psql --dbname=reporting --command='select current_database();'
unset PGCLUSTER

Do not leave a broad export in a service environment until you have checked every client command that inherits it. A wrong cluster can be a quiet operational error, especially when database names exist on more than one cluster.

5. Add a per-user default

Use ~/.postgresqlrc when one Unix user needs a stable default and other users should remain unaffected. Its first uncommented, non-blank line has three whitespace-separated fields: PostgreSQL major version, cluster name, and database. A database of * means the database named after the Unix login.

# ~/.postgresqlrc
16 main reporting

Before creating it, preserve any existing file:

if test -e "$HOME/.postgresqlrc"; then
    cp -- "$HOME/.postgresqlrc" "$HOME/.postgresqlrc.bak"
fi
editor "$HOME/.postgresqlrc"
chmod 600 "$HOME/.postgresqlrc"
psql --version

Use your editor command in place of editor. The wrapper reads only the first active line, so put comments and blank lines wherever you like, but do not expect a later active line to act as a fallback. To undo this change, restore ~/.postgresqlrc.bak if one existed, or remove the new file after checking that it is the intended file.

6. Set a system-wide mapping only when needed

Administrators can map Unix users and groups in /etc/postgresql-common/user_clusters. Each active line is USER GROUP VERSION CLUSTER DATABASE. The first matching line wins, so specific rules must come before a wildcard default:

# /etc/postgresql-common/user_clusters
andy analysts 16 main reporting
* * 16 main *

The final wildcard line is a fallback for users and groups not matched earlier. This file does not override a user's ~/.postgresqlrc. Editing it needs elevated privileges and can change the default target for several people:

$ sudo cp -- /etc/postgresql-common/user_clusters /etc/postgresql-common/user_clusters.bak
$ sudoedit /etc/postgresql-common/user_clusters

There is no database service restart for this file. Validate the ordering by testing as the affected user, using --cluster for a comparison. To recover, restore the backup with sudo cp, then remove it once the change has been confirmed and your normal backup policy has captured the result.

7. Investigate a failed connection

If no selection rule matches, the wrapper may leave connection variables unset. A client will then report a connection failure, commonly involving the local socket or port 5432. First list clusters and inspect the relevant environment. Do not jump straight to changing a service or firewall.

$ pg_lsclusters
$ env | grep -E '^(PGHOST|PGPORT|PGCLUSTER|PGDATABASE|PGUSER)=' || true
$ psql --cluster 16/main --dbname=reporting --command='select 1;'
 ?column?
----------
        1

For a remote cluster, --cluster can use host:port, such as 16/db.example.test:5433. An empty port after the host means 5432. PostgreSQL authentication, network access and database permissions remain separate checks.