PSQL: Syllabus

Mastering psql: The PostgreSQL Command-Line Client

psql is the interactive terminal for PostgreSQL, allowing users to interact with databases efficiently via command-line. This book provides a hands-on approach to mastering psql, covering database administration, query execution, scripting, automation, and optimization techniques.

Module 1: Introduction to psql and PostgreSQL Basics#

• What is psql? Understanding its role in PostgreSQL management
• Installing and configuring psql
• Connecting to a PostgreSQL database using psql
• Navigating the psql interface and using help commands (\?, \h)

Module 2: Managing Databases and Tables#

• Creating and deleting databases (CREATE DATABASE, DROP DATABASE)
• Switching between databases (\c)
• Creating, altering, and dropping tables
• Understanding schemas and schema management

Module 3: Querying Data with SQL in psql#

• Writing and executing SELECT queries
• Filtering data with WHERE, ORDER BY, and LIMIT
• Using aggregate functions (SUM, COUNT, AVG, MIN, MAX)
• Working with JOINs (INNER, LEFT, RIGHT, FULL)
• Grouping data with GROUP BY and HAVING

Module 4: Advanced SQL Features in psql#

• Subqueries and Common Table Expressions (CTEs)
• Window functions for advanced analytics
• Using JSONB data types and functions
• Full-text search in PostgreSQL
• Working with arrays and composite types

Module 5: Database Administration with psql#

• Managing users and roles (CREATE ROLE, GRANT, REVOKE)
• Setting permissions and access control
• Creating and restoring database backups using pg_dump and psql
• Monitoring database activity (pg_stat_activity, EXPLAIN ANALYZE)

Module 6: Performance Optimization and Indexing#

• Creating and using indexes (CREATE INDEX, GIN, BRIN)
• Understanding query execution plans (EXPLAIN and EXPLAIN ANALYZE)
• Optimizing performance with VACUUM and ANALYZE
• Managing connection pooling with pgbouncer

Module 7: Automating Tasks with psql Scripting#

• Writing and executing SQL scripts in psql
• Using variables and loops in psql
• Automating database tasks with CRON and shell scripting
• Logging query outputs and results for auditing

Module 8: Using psql for Data Import and Export#

• Importing CSV and JSON files using COPY
• Exporting query results to CSV, JSON, and other formats
• Bulk data loading and best practices for large datasets
• Handling data transformations during import/export

Hands-On Projects

Project 1: Creating and Managing a PostgreSQL Database#

• Set up a PostgreSQL database and manage users and permissions
• Create tables, insert data, and query using psql
• Implement indexing and performance tuning techniques

Project 2: Automating Data Import and Reporting#

• Write and execute SQL scripts for automated data import
• Schedule SQL queries for daily reports using CRON
• Export reports in CSV and JSON formats

Project 3: Query Optimization and Performance Tuning#

• Analyze slow queries using EXPLAIN ANALYZE
• Implement indexing strategies for high-performance queries
• Optimize large dataset queries for efficiency

Project 4: Backup and Recovery Strategies with psql#

• Perform full and incremental backups using pg_dump
• Automate backup processes with shell scripting
• Restore databases and test recovery strategies

Project 5: Building a Real-Time Analytics Dashboard with psql#

• Query and aggregate real-time data from a PostgreSQL database
• Use window functions and complex joins for analytics
• Export data to visualization tools like Grafana

References#

• PostgreSQL Official Documentation
• psql Command Reference
• PostgreSQL Performance Tuning Guide