psql Fundamentals, Connection Parameters, Service Files, and Safe CLI Workflows

Learning outcomes

ServiceHub now has a running PostgreSQL 18.x lab. The next risk is client-side ambiguity: a shell may inherit PGHOST from an old project, a connection URI may expose a password in history, or a script may continue after a failed statement and apply later changes. psql is much more than a place to type SQL; it is a PostgreSQL client with its own meta-commands, variables, scripting behavior, and libpq connection rules.

This lesson teaches a safe CLI workflow: specify or centralize connection parameters, verify the destination immediately after connecting, understand where psql behavior ends and SQL begins, and make automation fail explicitly.

1. psql is a client, not the database server

psql reads commands, decides whether they are psql-specific meta-commands, and sends SQL statements to a PostgreSQL server. A line beginning with an unquoted backslash is normally a psql meta-command. The server never parses \conninfo, \dt, or \x; psql interprets them.

\conninfo\l\dn+\du\dx\dt app.*\d+ app.work_orders\x auto\timing on

SELECT current_database(), current_user, pg_backend_pid();SHOW search_path;SELECT now();

This distinction matters in scripts. A -c argument to psql must contain server-parsable SQL or a single backslash command; mixing arbitrary SQL and psql meta-commands inside one -c string is not the same as writing a script file that contains both.

2. The four core connection coordinates

A basic PostgreSQL connection needs a target database service. The most visible parameters are host, port, database name, and user/role. If you omit values, libpq/psql applies defaults; convenient defaults are useful interactively but dangerous when you are not sure which project environment variables are already set.

psql -h 127.0.0.1 -p 55432 -U postgres -d postgres -W

-W forces an interactive password prompt. It is not necessary when another secure authentication path already supplies credentials, but it makes a first lab connection explicit and avoids putting the password directly on the command line.

3. Conninfo strings and PostgreSQL URIs

libpq accepts keyword/value connection strings and URI forms. These are useful when a tool exposes one “connection string” field instead of separate flags.

psql "host=127.0.0.1 port=55432 dbname=postgres user=postgres application_name=bda_psql"

psql "postgresql://postgres@127.0.0.1:55432/postgres?application_name=bda_psql"

Do not normalize URLs containing literal passwords in shell history, screenshots, logs, or source code. URI percent-encoding also creates easy-to-miss mistakes when credentials contain reserved characters. Use interactive prompting, a password file for appropriate automation, or a real secret-management system in production.

Connection strings can include TLS parameters such as sslmode. This local loopback lab does not pretend to teach production certificate verification; Chapter 20 handles TLS and server identity rigorously.

4. Environment variables are defaults, not invisible truth

libpq recognizes environment variables such as PGHOST, PGPORT, PGDATABASE, PGUSER, PGSERVICE, PGSERVICEFILE, PGSSLMODE, and PGAPPNAME. They are useful for a short-lived terminal context, but an inherited value can silently redirect a command.

printf 'PGHOST=%s\nPGPORT=%s\nPGDATABASE=%s\nPGUSER=%s\n' \  "$PGHOST" "$PGPORT" "$PGDATABASE" "$PGUSER"export PGHOST=127.0.0.1export PGPORT=55432export PGDATABASE=postgresexport PGUSER=postgresexport PGAPPNAME=bda_psqlpsql -W

Get-ChildItem Env:PGHOST,Env:PGPORT,Env:PGDATABASE,Env:PGUSER -ErrorAction SilentlyContinue$env:PGHOST = '127.0.0.1'$env:PGPORT = '55432'$env:PGDATABASE = 'postgres'$env:PGUSER = 'postgres'$env:PGAPPNAME = 'bda_psql'psql -W

The PGPASSWORD environment variable exists, but PostgreSQL documentation discourages it for security because environment variables can be exposed by operating-system facilities on some systems. Prefer a password file or secret manager for noninteractive use.

5. Service files make connection intent reusable

A connection service file associates a service name with libpq connection parameters. This prevents every script from hard-coding the same host, port, database, TLS mode, and application name. The per-user default is ~/.pg_service.conf on Unix-like systems and %APPDATA%\postgresql\.pg_service.conf on Windows. PGSERVICEFILE can point to another file.

[servicehub-lab-admin]host=127.0.0.1port=55432dbname=postgresuser=postgresapplication_name=bda_servicehub_adminconnect_timeout=5

psql "service=servicehub-lab-admin" -W

libpq combines settings from several sources. A service-file setting overrides the corresponding environment-variable default, and a value specified directly in the connection string can override the service setting. This is useful, but it means troubleshooting should inspect the actual destination after connection rather than reasoning from one file alone.

6. Password files: useful automation with strict handling

The libpq password file lets clients find a password without placing it in the connection URI or process arguments. On Unix-like systems the default path is ~/.pgpass; on Windows it is %APPDATA%\postgresql\pgpass.conf. The format is colon-separated:

host:port:database:user:password

For the lab, a narrowly scoped record might match only 127.0.0.1:55432:postgres:postgres. Do not publish the actual last field. On Unix-like systems, libpq requires restrictive permissions; files with broadly accessible permissions are ignored.

chmod 600 ~/.pgpass

A password file is still a secret at rest. Protect backups, home directories, CI workspaces, and workstation accounts accordingly. In production, consider platform secret stores or short-lived credentials where appropriate rather than turning .pgpass into a shared team password vault.

7. Always prove where the session landed

A safe psql habit is to make the destination visible before doing DDL or DML. \conninfo summarizes the client connection. Then ask the server for identity evidence:

\conninfo

SELECT current_database() AS database_name,       session_user AS login_role,       current_user AS effective_role,       inet_server_addr() AS server_address,       inet_server_port() AS server_port,       pg_backend_pid() AS backend_pid;SELECT current_setting('server_version') AS server_version,       current_setting('data_directory') AS data_directory;

For a local Unix-domain socket, the server address/port functions may return null even though the connection is valid. \conninfo will describe the socket connection. For production workflows, also use service-specific identifiers such as DNS names, certificate verification, deployment metadata, or a configured cluster_name where appropriate; no single query is a universal environment-proof mechanism.

8. Introspection meta-commands accelerate learning

psql’s \d family reads PostgreSQL catalogs and formats the result for humans. Use it for interactive exploration; use explicit catalog/information-schema queries when an application or durable automation needs structured data.

Meta-command output can change as psql evolves. It is an operator interface, not a schema for another application to scrape.

9. Formatting and timing: make evidence readable

For wide rows, \x auto can switch to expanded display when helpful. \pset pager off avoids interactive pager behavior in captured terminal output. \timing on reports client-observed statement execution time and is useful during investigation, but it is not a rigorous benchmark methodology.

\x auto\pset pager off\timing on

Later performance chapters use EXPLAIN (ANALYZE, BUFFERS, ...), statistics views, repeated workloads, and controlled cache/concurrency conditions. One psql timing is an observation, not a universal performance claim.

10. Autocommit and explicit transactions

By default, psql operates in autocommit mode: when you send a statement outside an explicit transaction block, PostgreSQL executes it in its own transaction. If a multi-step change must succeed or fail as a unit, use an explicit transaction.

BEGIN;-- Example changes would go here.SELECT current_database(), current_user;ROLLBACK;  -- use COMMIT only after you intend to keep changes

psql also has an AUTOCOMMIT variable, but production scripts are clearer when transaction boundaries are explicit. A prompt that changes from => to a transaction-state variant can provide a hint, but never depend on prompt decoration as your only transaction-state check.

11. Scripts must stop on errors deliberately

By default, psql processing can continue after an error in a script. That is dangerous for migrations where later statements assume earlier ones succeeded. Set ON_ERROR_STOP for automation.

\set ON_ERROR_STOP on\echo 'Verifying target before work'SELECT current_database(), current_user, version();BEGIN;-- migration statements that are valid inside a transactionCOMMIT;

From the shell, -v ON_ERROR_STOP=1 provides the same intent. For a file whose statements are all transaction-safe, --single-transaction can wrap the file in one transaction:

psql "service=servicehub-lab-admin" \  -X \  -v ON_ERROR_STOP=1 \  --single-transaction \  -f migration.sql

-X tells psql not to read startup files such as .psqlrc, which improves reproducibility for automation. --single-transaction is inappropriate if the script contains commands that cannot run inside a transaction block, such as CREATE DATABASE. Know the commands before applying the wrapper.

12. A deliberately wrong workflow: hidden environment redirect

Suppose yesterday you set PGHOST=production-db.example and PGDATABASE=servicehub. Today you open a terminal and type only:

psql

If the inherited defaults are valid, psql can connect somewhere you did not intend. The wrong response is to trust the shell prompt because it “looks familiar.” The repair is to use a named service or explicit flags and verify the destination immediately with \conninfo plus server-side identity queries.

13. Build a reusable ServiceHub connection profile

Create a service entry now; Lesson 5 will change its database from postgres to servicehub_lab after the lab database exists.

[servicehub-lab-admin]host=127.0.0.1port=55432dbname=postgresuser=postgresapplication_name=bda_servicehub_adminconnect_timeout=5

Then create a tiny verification file named verify-target.sql:

\set ON_ERROR_STOP on\conninfoSELECT current_database() AS database_name,       session_user AS login_role,       current_user AS effective_role,       pg_backend_pid() AS backend_pid,       version() AS server_build;

psql "service=servicehub-lab-admin" -X -v ON_ERROR_STOP=1 -f verify-target.sql

The expected result is an explicit connection summary plus one row identifying the local lab server. If it reports a different host/database/role than expected, stop. Do not let a migration script “correct” the environment by force.

14. Hands-on lab: safe psql workflow

1. Create the service-file entry for your actual local lab port.
2. Connect by service name and enter the disposable password through a prompt or protected password file.
3. Run \conninfo and the server identity query.
4. Run \l, \dn+, \du, and \dx, then explain which are psql commands rather than SQL.
5. Enable \x auto and \timing on; run SELECT now(); and interpret timing as a local observation only.
6. Start BEGIN;, run a harmless SELECT, and ROLLBACK;.
7. Create verify-target.sql and run it with -X -v ON_ERROR_STOP=1.

Negative test: prove ON_ERROR_STOP matters

Use a disposable script with a deliberate reference to a nonexistent table, followed by an \echo. First run without ON_ERROR_STOP, then with it. Do not include any destructive statements.

SELECT 1 AS before_error;SELECT * FROM definitely_missing_table;\echo 'If you see this in a non-fail-fast run, psql continued.'SELECT 2 AS after_error;

The fail-fast run should terminate at the error and return psql’s script-error exit status rather than executing the later commands. This is a safe way to observe why migration automation should specify error behavior.

15. Summary and bridge to the repeatable lab

psql has two personalities: it sends SQL to PostgreSQL and processes its own backslash meta-commands locally. libpq connection settings can come from flags, conninfo/URIs, environment variables, service files, and password files; therefore safe operation requires target verification rather than assumptions about defaults.

For scripts, use reproducible startup behavior, explicit transaction boundaries where appropriate, and ON_ERROR_STOP. The next lesson uses these conventions to create the persistent Chapter 01 ServiceHub lab: separate owner and login roles, a dedicated database and schema, least-privilege grants, seed/reset scripts, optional approved extensions, and a backup/cleanup policy that later PostgreSQL internals labs can reuse safely.

Authoritative references

• psql — PostgreSQL interactive terminal
• libpq Database Connection Control Functions
• libpq Environment Variables
• The Password File
• The Connection Service File
• System Information Functions and Operators