Duration 2 days – 14 hrs
Overview
The PostgreSQL Database Basics Training Course is designed to introduce participants to the fundamentals of PostgreSQL, a powerful open-source relational database management system (RDBMS). This course will cover essential topics such as PostgreSQL architecture, database creation, table management, SQL querying, data manipulation, and basic performance tuning. Through hands-on exercises, participants will learn how to manage PostgreSQL databases effectively and leverage its advanced features for modern applications.
Objectives
- Understand the key features and architecture of PostgreSQL.
- Install and configure a PostgreSQL database.
- Create and manage databases, tables, and indexes.
- Perform basic SQL queries and data manipulation operations.
- Implement data integrity constraints and relationships between tables.
- Backup and restore PostgreSQL databases.
- Apply basic performance tuning techniques.
Audience
- Database Administrators (DBAs)
- Software Developers
- IT Professionals responsible for database management
- System Administrators
- Anyone interested in learning relational databases
Pre- requisites
- Basic knowledge of databases and SQL.
- Familiarity with command-line interfaces is helpful but not required.
Course Content
Day 1: Introduction to PostgreSQL and SQL Basics
- Introduction to PostgreSQL
- What is PostgreSQL? Features and Use Cases
- PostgreSQL Architecture: Server, Databases, and Clients
- Installing and Configuring PostgreSQL on Different Platforms
- Connecting to PostgreSQL using psql and GUI Tools (pgAdmin)
- Database and Table Management
- Creating and Dropping Databases
- Understanding Schemas in PostgreSQL
- Creating, Altering, and Dropping Tables
- Understanding Data Types in PostgreSQL (Text, Numeric, Date, etc.)
- SQL Basics: Querying Data
- Introduction to SQL Syntax
- SELECT Statements and Filtering Data with WHERE
- Sorting and Limiting Results
- Using Joins to Combine Data from Multiple Tables
- Hands-On Lab: Database and Table Creation
- Creating a Simple Database and Running Basic SQL Queries
Day 2: Data Manipulation, Indexing, and Backup
- Data Manipulation and Constraints
- Inserting, Updating, and Deleting Data
- Using Primary Keys, Foreign Keys, and Unique Constraints
- Implementing NOT NULL and CHECK Constraints
- Maintaining Data Integrity in PostgreSQL
- Indexing and Performance Basics
- Creating and Managing Indexes for Faster Queries
- Using EXPLAIN to Analyze Query Performance
- Basic Performance Tuning Techniques
- Backup and Restore Operations
- Understanding PostgreSQL Backup and Restore Methods
- Using pg_dump and pg_restore
- Automating Backups for PostgreSQL Databases
- Hands-On Lab: Data Manipulation and Backup
- Implementing Constraints, Indexing, and Performing Backups
