Mastering PostgreSQL Databases: From Basics to Advanced

Learn about PostgreSQL, from simple queries to advanced functions like transaction control, security, replication, and performance tuning. Explore complex data types, backups, upgrades, and migration.

Beginner

56 Lessons

11h

Certificate of Completion

AI-POWERED

Explanations

AI-POWERED

Explanations

This course includes

2 Projects

1 Assessment

172 Playgrounds

8 Quizzes

This course includes

2 Projects

1 Assessment

172 Playgrounds

8 Quizzes

Course Overview

PostgreSQL is a popular relational database management system. This course offers many features—from fundamental commands to advanced functionalities within PostgreSQL. You will learn simple queries, data definition, and data manipulation through SQL. You’ll be taught how to work with complex data types, regular expressions, views, indexing, and stored procedures. You’ll also learn about transaction control, administration, security, replication, and performance tuning. Following that, you’ll gain insight...Show More

TAKEAWAY SKILLS

Sql

Database

What You'll Learn

Hands-on experience of database administration, replication, backup, and recovery

An understanding of SQL queries to retrieve and manipulate data

A working knowledge of performance tuning, security, and migration in PostgreSQL

An understanding of query performance and improvement through indexing

Familiarity with advanced features like JSON data types and GIS functionality

The ability to perform upgrade and patching of PostgreSQL databases

What You'll Learn

Hands-on experience of database administration, replication, backup, and recovery

Course Content

Introduction to Database Management System

Get familiar with PostgreSQL, relational databases, SQL data types, and their key features.

Introduction to the Course Overview of Databases Types of Databases Relational Databases What is PostgreSQL?Quiz on Introduction to DBMS

SQL Basics

Unpack the core of SQL queries, data definition, manipulation commands, and query performance tuning.

Overview of SQL

Basic Data Definition Commands Advanced Data Definition Commands to Alter Tables Advanced Data Definition Commands to Alter Columns Data Types in SQL Retrieving Data from Tables Arithmetic and Comparison Operators in SQL Logical Operators in SQL Data Manipulation Commands Data Presentation Using Indexes to Improve Query Performance Quiz on SQL Basics

Personal Expense Tracker Database

Project

Advanced SQL

Examine advanced SQL with complex data types, regex, views, procedures, triggers, sequences, and transactions.

Working with Complex Data Types Regular Expressions in SQL Working with Views in PostgreSQL Stored Procedures and Functions Triggers Sequences Transactions, Locks, and Deadlocks Quiz on Advanced SQL

Administration

Grasp the fundamentals of PostgreSQL architecture, server configuration, and routine maintenance.

Architecture of PostgreSQL Configuring the PostgreSQL Server Routine Database Maintenance in PostgreSQL Quiz on PostgreSQL Administration

Security

Secure PostgreSQL databases with robust authentication, role-based access control, encryption, and privilege management.

Overview of PostgreSQL Database Security Securing PostgreSQL Installation Granting and Revoking Privileges Using Data Encryption Quiz on PostgreSQL Security

Replication

5 Lessons

Follow the process of PostgreSQL replication to enhance reliability, scalability, and high availability.

Performance Tuning

6 Lessons

Master the steps to enhance PostgreSQL performance through tuning, query optimization, and monitoring.

Designing an Online Retail Store

Project

Backup and Recovery

5 Lessons

Step through vital backup and recovery strategies for PostgreSQL database management.

Upgrade and Migration

3 Lessons

Solve challenges with upgrading and migrating PostgreSQL databases effectively and smoothly.

PostgreSQL Assessment

Assessment

10.

Appendix

2 Lessons

Work your way through PostgreSQL installation and essential commands for seamless database management.

Create a GUI Application for PostgreSQL Database

Project

Course Author

Trusted by 1.4 million developers working at companies

Anthony Walker

@_webarchitect_

Emma Bostian 🐞

@EmmaBostian

Evan Dunbar

ML Engineer

Carlos Matias La Borde

Software Developer

Souvik Kundu

Front-end Developer

Vinay Krishnaiah

Software Developer

Eric Downs

Musician/Entrepeneur

Kenan Eyvazov

DevOps Engineer

Anthony Walker

@_webarchitect_

Emma Bostian 🐞

@EmmaBostian

Hands-on Learning Powered by AI

See how Educative uses AI to make your learning more immersive than ever before.

Instant Code Feedback

Evaluate and debug your code with the click of a button. Get real-time feedback on test cases, including time and space complexity of your solutions.

AI-Powered Mock Interviews

Put your skills to the test in a simulated interview setting. Receive personalized feedback based on your performance. Available in Premium & Premium Plus plans.

Adaptive Learning

At various checkpoints throughout Educative courses, you will be prompted to take a quick assessment. Receive a condensed curriculum tailored to your strengths and skill gaps.

Explain with AI

Select any text within any Educative course, and get an instant explanation — without ever leaving your browser.

AI Code Mentor

AI Code Mentor helps you quickly identify errors in your code, learn from your mistakes, and nudge you in the right direction — just like a 1:1 tutor!