Essential PostgreSQL Commands Guide

Discover the most important PostgreSQL commands every database administrator and developer needs to know. From basic database operations to user management, system monitoring, and maintenance tasks, this guide provides a complete reference for effectively managing your PostgreSQL databases. Learn how to check database status, manage users, monitor performance, and handle backups with simple, practical commands.

Essential PostgreSQL Commands: A Comprehensive Guide for Database Administrators

Whether you're a database administrator, developer, or just getting started with PostgreSQL, having a handy reference of essential commands is crucial. This guide covers the most important PostgreSQL commands for managing databases, users, and monitoring your system.

Administrative Access

Access PostgreSQL as the superuser:

# Access PostgreSQL as postgres user
sudo -u postgres psql

Database Management

First, let's look at basic database operations and navigation commands:

# List all databases
\l
\l+    # With additional details

# Connect to specific database
\c database_name

# List all tables in current database
\dt
\dt+   # With additional details

# Show table schema
\d table_name
\d+ table_name # With additional details

User Management

Managing users and their permissions is a critical part of database administration:

# List all users/roles
\du
\du+   # With additional details

# Create new user
CREATE USER username WITH PASSWORD 'password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

# Revoke privileges
REVOKE ALL PRIVILEGES ON DATABASE database_name FROM username;

# Delete user
DROP USER username;

System Information

These commands help you monitor and understand your PostgreSQL installation:

# Show database version
SELECT version();

# Show current user
SELECT current_user;

# Show current database
SELECT current_database();

Size and Storage

Monitor your database and table sizes with these commands:

# Check database size
SELECT pg_size_pretty(pg_database_size('database_name'));

# Check table size
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

Connection Management

Monitor and manage database connections:

# List all active connections
SELECT * FROM pg_stat_activity;

# Kill a specific connection
SELECT pg_terminate_backend(pid);

Backup and Restore

Essential commands for data backup and restoration:

# Backup database
pg_dump -U username database_name > backup.sql

# Restore database
psql -U username database_name < backup.sql

System Service Management

Control your PostgreSQL service with these system-level commands:

# Start PostgreSQL service
sudo systemctl start postgresql

# Stop PostgreSQL service
sudo systemctl stop postgresql

# Restart PostgreSQL service
sudo systemctl restart postgresql

# Check status
sudo systemctl status postgresql

Log Monitoring

Keep track of your PostgreSQL logs:

# View PostgreSQL log files (location might vary)
tail -f /var/log/postgresql/postgresql-*.log

Conclusion

These commands form a solid foundation for managing your PostgreSQL databases. Keep this guide handy as a quick reference when performing database administration tasks. Remember to always be careful when executing commands that modify data or user permissions, and make sure to maintain regular backups of your important databases.