PostgreSQL Basic Commands with Practical Examples

This guide gives you a repeatable, low-risk workflow for everyday PostgreSQL operations. You will learn how to inventory your environment, connect safely, inspect objects, run read-only queries, make controlled changes inside transactions, manage roles with least privilege, back up and restore logically, analyze query performance, and recover from common failures. Every example uses constructed names and IDs that you replace with your own values, so you can adapt each pattern directly to your runbooks.

Environment Inventory and Safe Connection

Before you run any command, capture the basics. This prevents surprises when environments differ and gives you a reliable reference during incidents.

Prerequisites

Access to a PostgreSQL server and the psql client installed locally or on a bastion host.
A role that can connect and read schema metadata. For changes, a role with explicit privileges on the target objects.
Network reachability to the host and port (default 5432), including any firewall rules or security groups.

Version and topology checks

psql --version
# Example output: psql (PostgreSQL) 15.4

psql -h db.example.internal -U app_read -d appdb -c "SELECT version();"
# Example output: PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc ...

List databases visible to your role and confirm the target database exists:

psql -h db.example.internal -U app_read -d postgres -c "\l"

Inside psql, verify your session context:

SELECT current_user, session_user, current_database();
SHOW search_path;          -- Commonly: "$user", public
SHOW statement_timeout;    -- Example: 60s

Record the following in your runbook:

Single instance or primary with standbys?
Which hostnames map to primary versus read replicas?
Which roles are read-only versus read-write?

Recommended psql settings for interactive work

\timing on          -- Show execution time for every statement
\pset pager off     -- Disable paging for short outputs
\set ON_ERROR_STOP on  -- Stop script execution on first error

Read-Only Discovery and Targeted Reads

Start every session with read-only commands. They carry zero risk of data modification and build confidence before you attempt changes.

List objects in the current database

\conninfo           -- Confirm host, port, user, database
\dn                 -- List schemas
\dt                 -- List tables in search_path
\du                 -- List roles
\d+ customers       -- Describe table with storage, size, and constraints
SELECT count(*) FROM customers;  -- Quick cardinality check

Filter and limit for safe inspection

-- Inspect recent customers (constructed example)
SELECT id, email, created_at
FROM customers
WHERE created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 20;

Expected result: zero to twenty rows, giving you a fast view of recent activity without scanning the entire table.

Controlled Changes with Transactions

Never run a data modification without a transaction wrapper. This pattern lets you verify the exact effect before committing.

Template for safe updates

BEGIN;
UPDATE customers
SET is_active = false
WHERE id = 12345;  -- Replace with a real primary key value

-- Verify the change affected exactly one row
SELECT id, is_active FROM customers WHERE id = 12345;

-- If verification passes:
COMMIT;
-- If verification fails (e.g., row count != 1):
ROLLBACK;

If you expect exactly one row and see more, issue ROLLBACK immediately and refine your WHERE clause. For bulk changes, process in batches of a few thousand rows and run ANALYZE afterward to refresh planner statistics.

Role Management and Least Privilege

Application roles should never be superusers. Grant only the privileges required for the workload.

Create a read-only role with default privileges for future tables

-- Run as a superuser or security admin role
BEGIN;
CREATE ROLE app_read LOGIN PASSWORD 'REDACTED';
GRANT CONNECT ON DATABASE appdb TO app_read;
GRANT USAGE ON SCHEMA public TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_read;
COMMIT;

Verify grants

\dp public.*   -- Shows privileges on tables in the public schema

Diagnose role capabilities

SELECT rolname, rolsuper, rolreplication, rolcanlogin
FROM pg_roles
WHERE rolname IN ('app_read', 'app_write');

Expected: application roles are not superusers and have only the privileges they need.

Schema Changes and Performance Checks

Create objects safely and validate query plans before running heavy statements.

Create a table with modern syntax

BEGIN;
CREATE TABLE IF NOT EXISTS customers (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);
COMMIT;

\d+ customers  -- Confirm columns, constraints, indexes, and size

Explain plans before executing

EXPLAIN SELECT * FROM customers WHERE email = '[email protected]';

Look for an Index Scan on customers_email_idx. If you see a Sequential Scan on a large table, add an index:

BEGIN;
CREATE INDEX CONCURRENTLY IF NOT EXISTS customers_email_idx ON customers (email);
COMMIT;

EXPLAIN SELECT * FROM customers WHERE email = '[email protected]';

CREATE INDEX CONCURRENTLY avoids long exclusive locks but takes longer to build. Run it during low-traffic periods.

Logical Backup and Restore Validation

Logical dumps with pg_dump are portable and scriptable. Always validate by restoring to a scratch database first.

Compressed dump of a single database

pg_dump -h db.example.internal -U backup_user -d appdb -F c -f appdb_2024-10-01.dump

Exit status must be 0. Record the timestamp and dump size in your runbook.

Restore to a scratch database and verify

createdb -h db.example.internal -U admin appdb_restore_scratch
pg_restore -h db.example.internal -U admin -d appdb_restore_scratch appdb_2024-10-01.dump
psql -h db.example.internal -U admin -d appdb_restore_scratch -c "\dt"
psql -h db.example.internal -U admin -d appdb_restore_scratch -c "SELECT COUNT(*) FROM customers;"

Compare the table list and key table counts against a recent snapshot from the source database. Only after validation should you consider a production restore.

Schema-only dump for faster validation

pg_dump -h db.example.internal -U backup_user -n public -F c -f appdb_public_2024-10-01.dump appdb

Query Performance Diagnosis

When a query feels slow, use EXPLAIN ANALYZE during non-peak hours to see actual execution metrics.

\timing on
EXPLAIN ANALYZE SELECT * FROM customers WHERE email = '[email protected]';

Interpret the output:

Sequential Scan on large tables: Add or adjust an index.
High actual time vs. estimated cost: Statistics are stale; run ANALYZE on the affected tables.
Rows Removed by Filter is high: Consider a partial index or rewrite the filter to be more selective.

After large data modifications, always run:

ANALYZE customers;

Lock Monitoring and Blocking Resolution

Long-running transactions can block other sessions. Identify and resolve blockers quickly.

Show active non-idle sessions

SELECT pid, usename, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start ASC;

Find blocking PIDs

SELECT pg_blocking_pids(pid) AS blockers, pid, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Cancel or terminate the offending backend (coordinate with the owner first)

SELECT pg_cancel_backend(<pid>);   -- Gentle: cancels current query
SELECT pg_terminate_backend(<pid>); -- Last resort: drops the connection

Common Failure Modes and Corrective Actions

Symptom	Likely Cause	Corrective Action
Connection refused	Host/port mismatch, firewall, service down	Verify host, port, service status, and network rules
FATAL: password authentication failed	Wrong password or role name	Confirm role exists; reset password if needed
permission denied for relation	Insufficient privileges	GRANT SELECT/UPDATE on target objects; ensure schema USAGE
canceling statement due to statement timeout	Query too slow, timeout too low	Optimize query, add index, raise `statement_timeout` for session
could not obtain lock / deadlock detected	Concurrent transactions conflict	ROLLBACK one transaction; retry with narrower scope or consistent ordering
out of shared memory / max_locks_per_transaction	Excessive locks in one transaction	Break changes into smaller batches; adjust config during maintenance
No space left on device during pg_dump	Disk full	Write dumps to a larger volume; compress and rotate old dumps

Rollback and Recovery Procedures

Abort a harmful long-running query

SELECT pg_cancel_backend(<pid>);   -- Try first
SELECT pg_terminate_backend(<pid>); -- Last resort

Revert incorrect privilege changes

BEGIN;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
COMMIT;

Recover from a logical backup after data corruption or accidental drops

Create a fresh recovery database:

   createdb -h db.example.internal -U admin appdb_recover_2024_10_01

Restore the latest valid dump:

   pg_restore -h db.example.internal -U admin -d appdb_recover_2024_10_01 appdb_2024-10-01.dump

Validate schema and key counts:

   \dt
   SELECT COUNT(*) FROM customers;  -- Compare with pre-change snapshot

If only specific tables are affected, restore them selectively into the production database:

   pg_restore -h db.example.internal -U admin -d appdb --table=public.customers appdb_2024-10-01.dump

Batch Safety Patterns for Large Modifications

Always test the WHERE clause with a SELECT first; verify the exact row count.
Use primary keys or narrow indexes in the WHERE clause.
Process large updates in batches of 1,000–5,000 rows:

  BEGIN;
  WITH c AS (
    SELECT id FROM customers WHERE is_active = false LIMIT 5000
  )
  UPDATE customers SET is_active = true WHERE id IN (SELECT id FROM c);
  COMMIT;

Run ANALYZE after each batch to keep planner statistics current.

Operations Checklist

Customize this checklist for your environment and attach it to every runbook.

Inventory and setup

[ ] Record server and client versions: psql --version, SELECT version();
[ ] Confirm host, database, role: \conninfo, SELECT current_database(), current_user;
[ ] Enable helpful psql settings: \timing on, \pset pager off, \set ON_ERROR_STOP on

Discovery (read-only)

[ ] List objects: \dn, \dt, \d+ table_name
[ ] Check counts and recent activity with limited SELECT statements

Change management

[ ] Start a transaction: BEGIN;
[ ] Verify target rows with SELECT before modifying
[ ] Apply change with a precise WHERE clause; expect exact row counts
[ ] Verify with a targeted SELECT; COMMIT or ROLLBACK accordingly

Performance and locks

[ ] Use EXPLAIN (and EXPLAIN ANALYZE off-peak) on heavy queries
[ ] Inspect pg_stat_activity for blockers; cancel with pg_cancel_backend when necessary

Backup and restore

[ ] Run pg_dump for scoped backups; record exit status and dump size
[ ] Restore to a scratch database; validate schema and key table counts

Security

[ ] Review roles: \du and pg_roles; ensure no application role is a superuser
[ ] Check privileges with \dp and apply least-privilege grants

Post-change validation

[ ] Re-check table sizes and counts if impacted
[ ] Run ANALYZE on updated tables
[ ] Record a short change note with verification results

Practical End-to-End Examples

Example 1: Safely toggle a customer flag

-- 1) Verify target row exists
SELECT id, is_active FROM customers WHERE id = 12345;  -- Expect 1 row

-- 2) Change inside a transaction
BEGIN;
UPDATE customers SET is_active = NOT is_active WHERE id = 12345;  -- Expect UPDATE 1
SELECT id, is_active FROM customers WHERE id = 12345;  -- Verify new value
COMMIT;  -- or ROLLBACK if unexpected

Example 2: Add an index after confirming a sequential scan

EXPLAIN SELECT * FROM customers WHERE email = '[email protected]';
-- If Seq Scan appears and table is large:
BEGIN;
CREATE INDEX CONCURRENTLY IF NOT EXISTS customers_email_idx ON customers (email);
COMMIT;

-- Re-check the plan
EXPLAIN SELECT * FROM customers WHERE email = '[email protected]';

Example 3: Minimal logical backup of a single schema

pg_dump -h db.example.internal -U backup_user -n public -F c -f appdb_public_2024-10-01.dump appdb

# Validate with a restore into a scratch database
createdb -h db.example.internal -U admin appdb_scratch
pg_restore -h db.example.internal -U admin -d appdb_scratch appdb_public_2024-10-01.dump
psql -h db.example.internal -U admin -d appdb_scratch -c "\dt"

Conclusion

You now have a pragmatic, repeatable set of PostgreSQL commands and patterns to operate safely in production. Start every session with an environment inventory and intentional connection. Use read-only discovery before any change. Enclose all writes in transactions and verify row counts and results before committing. Use EXPLAIN to avoid performance surprises, and EXPLAIN ANALYZE off-peak to diagnose slow queries. Back up logically with pg_dump, validate every restore in a scratch database, and document the outcome. Prepare for failures with clear rollback steps, blocking-session resolution, and a tested recovery procedure from logical dumps. Adopt the checklist in your runbooks, pilot it on a non-critical environment, capture verification outputs, and expand once the approach proves reliable.