Rainlily ✨

Common PostgreSQL Commands

A quick reference guide for frequently used PostgreSQL commands on macOS.

Service Management

Start PostgreSQL Service


                brew services start postgresql@15

Stop PostgreSQL Service


                brew services stop postgresql@15

Restart PostgreSQL Service


                brew services restart postgresql@15

Check Service Status


                brew services list | grep postgresql

Connection Commands

Connect to Default Database


                psql postgres

Connect to Specific Database


                psql -d database_name

Connect as Specific User


                psql -U username -d database_name

Connect with Connection String


                psql postgresql://username:password@localhost:5432/database_name

Database Operations

List All Databases


                \l

Create Database


                CREATE DATABASE database_name;

Or from command line:


                createdb database_name

Drop Database


                DROP DATABASE database_name;

Or from command line:


                dropdb database_name

Connect to Database


                \c database_name

Show Current Database


                SELECT current_database();

User/Role Management

List All Users


                \du

Create User


                CREATE USER username WITH PASSWORD 'password';

Create User with Privileges


                CREATE USER username WITH 
    PASSWORD 'password'
    CREATEDB
    CREATEROLE;

Change User Password


                ALTER USER username WITH PASSWORD 'new_password';

Grant Privileges


                GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Revoke Privileges


                REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;

Drop User


                DROP USER username;

Table Operations

List All Tables


                \dt

Describe Table Structure


                \d table_name

List All Columns in Table


                \d+ table_name

Create Table


                CREATE TABLE table_name (
    id SERIAL PRIMARY KEY,
    column1 VARCHAR(50),
    column2 INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Drop Table


                DROP TABLE table_name;

Truncate Table (Remove All Rows)


                TRUNCATE TABLE table_name;

Rename Table


                ALTER TABLE old_name RENAME TO new_name;

Data Operations

Select Data


                SELECT * FROM table_name;
SELECT column1, column2 FROM table_name WHERE condition;

Insert Data


                INSERT INTO table_name (column1, column2) VALUES ('value1', 123);

Update Data


                UPDATE table_name SET column1 = 'new_value' WHERE condition;

Delete Data


                DELETE FROM table_name WHERE condition;

Schema Operations

List All Schemas


                \dn

Create Schema


                CREATE SCHEMA schema_name;

Set Search Path


                SET search_path TO schema_name;

Drop Schema


                DROP SCHEMA schema_name;

Index Operations

Create Index


                CREATE INDEX index_name ON table_name (column_name);

Create Unique Index


                CREATE UNIQUE INDEX index_name ON table_name (column_name);

List All Indexes


                \di

Drop Index


                DROP INDEX index_name;

Backup and Restore

Backup Database


                pg_dump database_name > backup.sql

Backup Specific Table


                pg_dump -t table_name database_name > table_backup.sql

Backup with Custom Format


                pg_dump -Fc database_name > backup.dump

Restore Database


                psql database_name < backup.sql

Restore Custom Format


                pg_restore -d database_name backup.dump

Useful psql Meta-Commands

Exit psql


                \q

Get Help


                \?

Get SQL Help


                \h COMMAND_NAME

Show Connection Info


                \conninfo

Execute SQL from File


                \i /path/to/file.sql

Timing Commands


                \timing

Show Query Execution Plan


                EXPLAIN SELECT * FROM table_name;
EXPLAIN ANALYZE SELECT * FROM table_name;

Toggle Output Format


                \x  -- Expanded display (toggle)

Show Query History


                \s

Save Query History


                \s /path/to/file.sql

Environment Variables

Set these in your ~/.zshrc or ~/.bash_profile:


                export PGHOST=localhost
export PGPORT=5432
export PGUSER=your_username
export PGPASSWORD=your_password
export PGDATABASE=your_database

Then connect with:


                psql

Performance and Monitoring

Show Active Connections


                SELECT * FROM pg_stat_activity;

Show Database Size


                SELECT pg_size_pretty(pg_database_size('database_name'));

Show Table Size


                SELECT pg_size_pretty(pg_total_relation_size('table_name'));

Show All Table Sizes


                SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Kill a Connection


                SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = <process_id>;

Quick Tips

Related Guides