Try DrDroid: AI Agent for Debugging

Connection

psql -h hostname -p port -U username -d database
Connect to PostgreSQL server

psql -U username database
Connect to local PostgreSQL server

\c database
Connect to a different database

\conninfo
Display connection information

Database Operations

\l
List all databases

CREATE DATABASE dbname;
Create a new database

DROP DATABASE dbname;
Delete a database

\d
List all tables, views, and sequences

\d tablename
Describe a table structure

Table Operations

SELECT * FROM tablename;
Select all records from a table

SELECT * FROM tablename LIMIT 10;
Select first 10 records from a table

INSERT INTO tablename (column1, column2) VALUES (value1, value2);
Insert a new record

UPDATE tablename SET column = value WHERE condition;
Update records

DELETE FROM tablename WHERE condition;
Delete records

TRUNCATE TABLE tablename;
Remove all records from a table

Monitoring & Statistics

SELECT pg_size_pretty(pg_database_size('dbname'));
Get database size

SELECT pg_size_pretty(pg_relation_size('tablename'));
Get table size

SELECT * FROM pg_stat_activity;
Show current activity

SELECT pid, query, state FROM pg_stat_activity;
Show running queries

SELECT * FROM pg_locks;
Show locks

SELECT pg_cancel_backend(pid);
Cancel a specific query

SELECT pg_terminate_backend(pid);
Terminate a specific connection

Performance

EXPLAIN SELECT * FROM tablename;
Show query execution plan

EXPLAIN ANALYZE SELECT * FROM tablename;
Show and execute query plan with timing

CREATE INDEX ON tablename (column);
Create an index

VACUUM tablename;
Reclaim storage occupied by dead tuples

VACUUM ANALYZE tablename;
Update statistics for query optimizer

Metadata Queries

\dt
List all tables

\di
List all indexes

\dv
List all views

\df
List all functions

\du
List all users and roles

Backup & Restore

pg_dump dbname > outfile
Backup a database

pg_dump -t tablename dbname > outfile
Backup a specific table

psql dbname < infile
Restore from a backup file

pg_dumpall > outfile
Backup all databases

pg_restore -d dbname backupfile
Restore from a pg_dump backup