Let me break down PostgreSQL CLI usage with detailed examples.
**Connection to PostgreSQL**
```sql
-- Basic connection
psql -U username -d database_name
-- Full connection with all parameters
psql -h localhost -p 5432 -U postgres -d mydb
-- Connection URL format
psql "postgresql://username:password@localhost:5432/dbname"
-- Connect to specific database from within psql
\c database_name
```
**Database Operations**
```sql
-- Create new database
CREATE DATABASE sales_db;
-- Create database with specific encoding and owner
CREATE DATABASE sales_db
WITH ENCODING='UTF8'
OWNER=sales_admin;
-- Drop database (be careful!)
DROP DATABASE IF EXISTS sales_db;
-- Rename database
ALTER DATABASE sales_db RENAME TO new_sales_db;
```
**Table Operations**
```sql
-- Create table
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Add column
ALTER TABLE products ADD COLUMN description TEXT;
-- Drop column
ALTER TABLE products DROP COLUMN description;
-- Rename table
ALTER TABLE products RENAME TO items;
-- Add index
CREATE INDEX idx_products_name ON products(name);
```
**Data Manipulation**
```sql
-- Insert data
INSERT INTO products (name, price) VALUES ('Laptop', 999.99);
-- Bulk insert
INSERT INTO products (name, price) VALUES
('Mouse', 24.99),
('Keyboard', 59.99),
('Monitor', 299.99);
-- Update data
UPDATE products SET price = 899.99 WHERE name = 'Laptop';
-- Delete data
DELETE FROM products WHERE price < 50;
-- Truncate table (remove all data)
TRUNCATE TABLE products;
```
**Useful psql Commands**
```sql
-- List all tables
\dt
-- Describe table structure
\d products
-- List all schemas
\dn
-- List all users
\du
-- Show table size
SELECT pg_size_pretty(pg_total_relation_size('products'));
-- Show running queries
SELECT pid, query, state
FROM pg_stat_activity
WHERE state != 'idle';
-- Kill query by PID
SELECT pg_cancel_backend(pid);
```
**Backup and Restore**
```bash
# Create backup
pg_dump dbname > backup.sql
# Create compressed backup
pg_dump -Fc dbname > backup.dump
# Backup specific tables
pg_dump -t table1 -t table2 dbname > tables_backup.sql
# Restore from backup
psql dbname < backup.sql
# Restore compressed backup
pg_restore -d dbname backup.dump
```
**User Management**
```sql
-- Create user
CREATE USER john WITH PASSWORD 'secure_password123';
-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE sales_db TO john;
GRANT SELECT, INSERT ON products TO john;
-- Create role with login
CREATE ROLE sales_admin WITH LOGIN PASSWORD 'admin123';
-- Add user to role
GRANT sales_admin TO john;
-- Revoke privileges
REVOKE ALL PRIVILEGES ON products FROM john;
```
**Performance Monitoring**
```sql
-- Check table sizes
SELECT
relname as table_name,
pg_size_pretty(pg_total_relation_size(relid)) as total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
-- Find unused indexes
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_catalog.pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname NOT IN ('pg_catalog', 'pg_toast');
-- Check cache hit ratio
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;
```
**Maintenance Commands**
```sql
-- Analyze table statistics
ANALYZE products;
-- Vacuum table (recover space and update statistics)
VACUUM (VERBOSE, ANALYZE) products;
-- Reindex table
REINDEX TABLE products;
-- View locks
SELECT blocked_locks.pid AS blocked_pid,
blocking_locks.pid AS blocking_pid,
blocked_activity.usename AS blocked_user,
blocking_activity.usename AS blocking_user
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
```
10. **Useful SQL Queries for Administration**
```sql
-- Get all tables and their sizes
SELECT
table_name,
pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) as total_size
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_total_relation_size(quote_ident(table_name)) DESC;
-- Find blocking queries
SELECT pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query as blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
-- Check index usage
SELECT
t.schemaname,
t.tablename,
c.reltuples::bigint AS rows,
pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_tables t ON t.tablename = c.relname
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(c.oid) DESC;
```
These examples cover most common PostgreSQL CLI operations. Remember to:
- Always backup before major operations
- Be careful with DELETE and DROP commands
- Use EXPLAIN ANALYZE for query optimization
- Regularly maintain (VACUUM) your databases
- Monitor performance metrics
- Keep your PostgreSQL version updated