Study guide
4.1 Interactive Command-Line Mastery with psql
PostgreSQL Associate Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & PostgreSQL Foundation
1.1 EDB PostgreSQL Associate Certification Overview 1.2 The PostgreSQL Relational Model & Architecture Principles 1.3 Study Strategies, Lab Setup & Exam-Day Execution
2 Chapter 2: PostgreSQL Architecture & Installation
2.1 The PostgreSQL Server Process Model 2.2 Database Clusters & Cluster Initialization (initdb) 2.3 Filesystem Layout, Service Control & Startup/Shutdown 2.4 Server Configuration Parameters
3 Chapter 3: Demonstrating SQL Knowledge
3.1 Relational Data Types & Table Design 3.2 Integrity Constraints & Data Validation 3.3 Querying, Joining & Aggregating Data 3.4 DML Operations, Transaction Control & MVCC Mechanics
4 Chapter 4: PostgreSQL Administrative Tools
4.1 Interactive Command-Line Mastery with psql 4.2 GUI Administration with pgAdmin 4.3 Client & Server Command-Line Utilities 4.4 System Catalogs & Information Schema
5 Chapter 5: Core Database Administration & Security
5.1 Managing Databases, Schemas & Tablespaces 5.2 Roles, Privileges & Object Ownership 5.3 Client Authentication & pg_hba.conf Architecture
6 Chapter 6: Routine Maintenance & Data Operations
6.1 The PostgreSQL Vacuuming Engine & MVCC Space Reclamation 6.2 Analyzing Query Statistics & Reindexing 6.3 High-Performance Data Loading & Data Movement
7 Chapter 7: Backup and Recovery Strategies
7.1 Logical Backups with pg_dump & pg_dumpall 7.2 Restoring Logical Backups 7.3 Physical Base Backups with pg_basebackup 7.4 Write-Ahead Logging & Point-in-Time Recovery
8 Chapter 8: High Availability & Replication
8.1 Physical Streaming Replication Architecture 8.2 Synchronous vs. Asynchronous Replication 8.3 Replication Slots & Standby Management 8.4 Failover, Switchover & High-Availability Design
9 Chapter 9: Monitoring, Diagnostics & Performance Tuning
9.1 Cumulative Statistics & Activity Monitoring 9.2 Query Execution Plans & Index Optimization 9.3 Server Logging Configuration & Autovacuum Tuning 9.4 Server Memory Sizing & Core Performance Parameters
4.1 Interactive Command-Line Mastery with psql
Key Takeaways Explain This Topic AI
psql serves as PostgreSQL's primary interactive terminal and non-interactive script processor, accepting connection parameters via command-line flags, connection URIs, or libpq environment variables.
Batch execution can be driven non-interactively using -c for inline SQL commands and -f for script files, with -v ON_ERROR_STOP=1 recommended to halt execution immediately upon encountering any SQL error.
Meta-commands (backslash commands such as \d , \l , \c , \dn , \du , and \dt ) are interpreted client-side by psql and must never terminate with a semicolon, which would be parsed as part of the command arguments.
Expanded display mode ( \x ) pivots wide table rows into vertical key-value pairs, while \timing enables precise elapsed execution time reporting in milliseconds for every statement.
The .psqlrc startup configuration file allows administrators to customize default prompts, enable timing, configure formatting, and enforce session variables automatically upon connection.
Last updated: September 2026
4.1 Interactive Command-Line Mastery with psql
Note
The Standard Interface : While graphical interfaces provide accessible overviews, psql remains the premier, ubiquitous administrative interface for PostgreSQL. Every automated deployment script, continuous integration pipeline, troubleshooting runbook, and performance diagnostic workflow relies on psql .
Mastering psql is essential for the PostgreSQL Associate certification. The tool functions in two distinct modes: as an interactive command-line shell for administrative operations and ad-hoc queries, and as a non-interactive batch engine for automating database scripts and infrastructure provisioning.
Establishing Connections: Syntax and Options
When invoked, psql establishes a client connection to a target database cluster using the underlying libpq C application programming interface. The primary connection parameters can be specified via positional arguments, explicit command-line flags, or uniform connection strings (URIs).
Core Connection Flags
Flag Long Option Description & Default Value
-h --host=HOSTNAME Database server host name or IP address. If omitted or starting with a slash, connects to a local UNIX domain socket (default socket directory e.g., /tmp or /var/run/postgresql ).
-p --port=PORT TCP port or socket file extension on which the server listens (default: 5432 ).
-U --username=USERNAME Database role name to authenticate as (default: current operating system username).
-d --dbname=DBNAME Target database name to connect to (default: value matching the database username).
-W --password Forces psql to prompt interactively for a password before connecting.
-w --no-password Strictly forbids prompting for a password; fails immediately if authentication requires one. # Standard TCP connection using explicit flags
psql -h db.internal.net -p 5432 -U app_admin -d production
- # Connection using a PostgreSQL URI
- psql postgresql://app_admin:SecretPass123@db.internal.net:5432/production?sslmode=require
If flags are omitted, psql falls back sequentially to standard environment variables (such as PGHOST , PGPORT , PGUSER , PGDATABASE ) and then to compiled system defaults.
Non-Interactive and Batch Script Execution
In administrative automation, cron jobs, and infrastructure-as-code pipelines, psql runs in non-interactive batch mode. Several flags govern how queries are passed, executed, and formatted:
1. Single Command Execution ( -c / --command )
Passes a command string directly to the server, outputs the result, and terminates immediately: psql -U postgres -d sales -c "SELECT count(*) FROM orders;"
Multiple -c flags can be specified on a single command line. PostgreSQL executes them sequentially in the order they appear: psql -U postgres -d sales \
- c "SELECT count(*) FROM orders;" \
- c "SELECT count(*) FROM customers;"
- 2. Script File Execution ( -f / --file )
Reads and executes SQL statements and meta-commands from an external file: psql -U postgres -d sales -f /opt/migrations/v2_schema.sql
3. Fail-Fast Automation with ON_ERROR_STOP
By default, when psql encounters an error while processing a script via -f , it prints the error message to stderr and continues executing subsequent lines . In migration scripts, continuing after a failed table creation or constraint addition can cause severe data corruption.
To enforce strict transactional safety in automation, always pass -v ON_ERROR_STOP=1 (or --variable=ON_ERROR_STOP=1 ). This instructs psql to halt execution immediately upon encountering any SQL error and return an exit code of 3 to the calling shell: psql -U postgres -d sales -v ON_ERROR_STOP=1 -f deploy.sql
- if [ $? -ne 0 ]; then
- echo "Deployment failed! Aborting pipeline."
- exit 1
- fi
- 4. Headless and Parsing Flags for Shell Pipelines
When generating output for consumption by UNIX utilities like awk , sed , or Python scripts, use formatting flags:
-A ( --no-align ) : Switches to unaligned table output mode.
-t ( --tuples-only ) : Suppresses column headers, row counts, and separator footers.
-F 'DELIMITER' ( --field-separator ) : Specifies a custom column delimiter (e.g., -F ',' or -F ' ' ).
-X ( --no-psqlrc ) : Prevents reading the user's ~/.psqlrc file, ensuring user customizations do not alter expected machine output. # Extract active user emails as a raw comma-separated list
- psql -U postgres -d app_db -A -t -F ',' -c "SELECT email FROM users WHERE is_active = true;"
- The Anatomy of psql: Meta-Commands vs. SQL Statements
One of the most foundational concepts in psql is the absolute distinction between SQL statements and psql meta-commands :
Dimension SQL Statements psql Meta-Commands (Backslash Commands)
Processing Location Sent over the network to the backend server daemon. Evaluated locally on the client terminal by psql .
Statement Terminator Must terminate with a semicolon ( ; ). Must NOT terminate with a semicolon!
Multi-line Support Can span multiple lines until ; is entered. Must reside on a single logical line (unless escaped with \ ).
Command Prefix Standard SQL keywords ( SELECT , INSERT , CREATE ). Starts with a leading backslash ( \ ).
Backend Execution Parsed, planned, and executed by database engine. Translated by psql into background SQL queries against pg_catalog .
Caution
The Trailing Semicolon Trap : A universal beginner pitfall on certification exams is typing a semicolon at the end of a meta-command (e.g., \dt; ). In psql , meta-commands treat everything following the command as arguments. Entering \dt; causes psql to search for a table literally named ; , resulting in Did not find any relation named ";" !
Essential Object Inspection Meta-Commands
PostgreSQL's system catalogs can be queried directly, but psql provides streamlined shortcuts known as informational meta-commands. Adding a + to most commands displays additional details (such as physical storage size, compression attributes, and column descriptions).
Relation and Schema Discovery
\d : Lists all visible tables, views, materialized views, foreign tables, and sequences in the current search path. When given an argument ( \d tablename ), describes the table's schema, column data types, modifiers, storage constraints, foreign keys, and indexes.
\d+ tablename : Extended description showing physical column storage settings, compression methods, statistics targets, and user comments.
\dt [pattern] : Lists tables only. Use patterns like \dt sales.* to list tables within the sales schema. \dt+ adds persistence status, owner, access privileges, disk size, and comments.
\di [pattern] : Lists indexes only ( \di+ includes size and physical definition).
\dv [pattern] : Lists views only ( \dv+ includes view definition query).
\dm [pattern] : Lists materialized views only ( \dm+ includes size and refresh state).
\ds [pattern] : Lists sequences only ( \ds+ includes data type and cache values).
\df [pattern] : Lists functions and stored procedures, showing argument signatures and return types.
\dn [pattern] : Lists schemas (namespaces) and their owners ( \dn+ includes access privileges).
\du [pattern] or \dg [pattern] : Lists database roles and their cluster-wide attributes (e.g., Superuser, Create role, Create DB, Bypass RLS, Replication).
\l or \l+ : Lists all databases in the cluster, displaying database name, owner, character encoding, collation, ctype, access privileges, default tablespace, and disk size. -- Interactive psql session example
- production=> \l+
- List of databases
- Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
------------+----------+----------+-------------+-------------+-------------------+---------+------------+-------------
- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8742 kB | pg_default |
- production | app_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 142 MB | pg_default |
- (2 rows)
- production=> \dt sales.*
- List of relations
- Schema | Name | Type | Owner
- -------+---------------+-------+----------
- sales | customers | table | app_user
- sales | order_items | table | app_user
- sales | orders | table | app_user
- (3 rows)
- Session Management and Terminal Toggles
Administrative work frequently requires switching contexts, modifying display formatting, or measuring query performance.
1. Connection Switching ( \c / \connect )
Switches to a different database or role without exiting the psql process: -- Syntax: \c [dbname [username [host [port]]]]
production=> \c analytics report_user
You are now connected to database "analytics" as user "report_user".
Passing - as an argument retains the current setting (e.g., \c - postgres switches user to postgres while remaining connected to the current database).
2. Expanded Display Mode ( \x )
Pivots table rows into vertical key-value records. When querying wide tables with dozens of columns, standard tabular output wraps illegibly. The \x command toggles expanded mode:
\x on : Forces expanded vertical display.
\x off : Returns to traditional horizontal grid.
\x auto : Automatically enables expanded display whenever output width exceeds the current terminal window width. production=> \x auto
Expanded display is used automatically.
- production=> SELECT * FROM customers WHERE customer_id = 101;
- [ RECORD 1 ]---------------------------------------
- customer_id | 101
- first_name | Sarah
- last_name | Connor
- email | sconnor@cyberdyne.org
- address | 404 Resistance Way, Los Angeles, CA
- created_at | 2026-04-12 09:15:30.12455+00
- 3. Execution Timing ( \timing )
Toggles client-side execution timing. When enabled, psql measures and prints the exact elapsed real time for every executed SQL command: production=> \timing on
Timing is on.
- production=> SELECT count(*) FROM orders;
- count
- -------
- 528940
- (1 row)
- Time: 14.821 ms
- 4. External Editor Integration ( \e )
Opens the query buffer in an external text editor (governed by the EDITOR or VISUAL environment variables, such as vim or nano ):
\e : Opens the most recently executed query in the editor. Upon saving and exiting, psql immediately executes the edited query.
\e script.sql : Opens a specified file for editing within the active session.
\ef function_name : Extracts the definition of a stored function into the editor for in-place modification and recompilation.
5. Input and Output Redirection ( \i and \o )
\i filename : Executes commands from an external SQL file inside the active interactive session (equivalent to @ or source in other database clients).
\o [filename] : Redirects all subsequent query output to the specified file. Entering \o with no arguments restores output back to standard output ( stdout ).
\q : Exits the psql session cleanly.
- Variables, Client Interpolation, and .psqlrc
- Managing psql Variables ( \set )
psql provides internal client-side variables that can be defined interactively or via scripts: -- Assign a variable
\set min_threshold 500
- - Display a variable
- \echo Current threshold: :min_threshold
- - Variable substitution in SQL queries
- SELECT order_id, total_amount
- FROM orders
- WHERE total_amount > :min_threshold;
PostgreSQL supports three distinct substitution formats:
:variable : Direct text substitution (useful for numbers or column names).
:'variable' : Wraps the variable value in single quotes as a literal SQL string ( 'value' ), correctly escaping internal quotes.
:"variable" : Wraps the variable value in double quotes as a SQL identifier ( "identifier" ).
Critical Built-in Variables
AUTOCOMMIT : Defaults to on . If set to off ( \set AUTOCOMMIT off ), psql implicitly issues a BEGIN before any SQL command, requiring explicit COMMIT or ROLLBACK .
HISTSIZE : Defines the number of command history lines preserved in ~/.psql_history .
PROMPT1 / PROMPT2 : Customizes the interactive shell prompt format.
Startup Customization via .psqlrc
When starting an interactive session, psql reads initialization commands from ~/.psqlrc in the user's home directory (or /etc/postgresql-common/psqlrc system-wide). Administrators configure standard productivity defaults here: -- ~/.psqlrc: Recommended Administrator Configuration
- \set ON_ERROR_STOP on
- \timing on
- \x auto
- \set HISTSIZE 10000
- \set HISTCONTROL ignoredups
- - Formats prompt: [user@hostname dbname] #/$
- \set PROMPT1 '%[%033[1;32m%]%n@%M %[%033[1;34m%]%/%[%033[0m%]%# '
- Terminal Pagination ( PAGER )
When query results exceed terminal height, psql routes output through an external pager. The pager utility is determined by the PAGER environment variable (defaulting to more or less ). The behavior can be adjusted inside the session using \pset pager [always|auto|off] .
Exam Tips and Common Pitfalls
Exam Trap: Semicolons on Meta-Commands : Never append a semicolon to a psql backslash command. On exam questions presenting options like \du; , \dt; , or \l; , remember that the semicolon is interpreted as an argument, causing lookup failures.
Exam Trap: Script Failure Handling : In a shell script running psql -f update.sql , errors do not stop the script by default. You must specify -v ON_ERROR_STOP=1 to ensure script termination upon the first error.
Exam Trap: Table Description : Remember that \d describes an individual table's structure (columns, types, constraints, indexes), whereas \dt lists the tables residing within schemas.