We may earn an affiliate commission when you visit our partners.
Course image
Sadiq Basha

PostgreSQL is an advanced, enterprise class open source Object-Relational Database Management System (ORDBMS). Its is a relational database that supports both SQL (relational) and JSON (non-relational) querying.

Read more

PostgreSQL is an advanced, enterprise class open source Object-Relational Database Management System (ORDBMS). Its is a relational database that supports both SQL (relational) and JSON (non-relational) querying.

PostgreSQL possesses robust feature sets including Multi-Version Concurrency Control (MVCC), point in time recovery, granular access controls, tablespaces, asynchronous replication, nested transactions, online/hot backups, a refined query planner/optimizer, and write ahead logging. It supports international character sets, multi-byte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. PostgreSQL is highly scalable both in the quantity of data it can manage and in the number of concurrent users it can accommodate.

The course provides the learner with an extensive knowledge of PostgreSQL Database Administration with real time examples.

Topics covered are:

  • PostgreSQL Process and Memory Architecture

  • Installation of PostgreSQL v12 - Windows and Linux

  • Setup PostgreSQL v12 Environment Variables - Windows and Linux

  • PostgreSQL Page Layout

  • Database Directory Layout

  • PostgreSQL Configuration Files

  • Cluster in PostgreSQL (Initdb,Start/Stop,Reload/Restart Cluster)

  • Createdb/Dropdb, Createuser/Dropuser Utility

  • Create Schema and Schema Search Path

  • Psql Interface Commands

  • Pg System Catalogs

  • Working with PostgreSQL Objects

  • CRUD Operations

  • Table Inheritance

  • Table Partitioning

  • Tablespace in PostgreSQL

  • Backup, Continuous Archiving and PITR (Point-in-Time-Recovery)

  • Explain plan and Query Execution Cost

  • Maintenance (Updating Planner Statistics, Vacuum, Routine Reindex & Cluster)

  • Data Fragmentation

  • Transaction ID wraparound, Vacuum freeze, Auto_Vacuum Demon

  • Pgadmin 4

  • Uninstall PostgreSQL v12 on Windows and Linux

  • Bonus Module

Enroll now

Good to know

Know what's good
, what to watch for
, and possible dealbreakers
Covers advanced database concepts such as Multi-Version Concurrency Control (MVCC) and point in time recovery, making it suitable for experienced database administrators
Focuses on practical real-world examples and hands-on exercises, providing learners with practical skills
Taught by instructors who are experienced PostgreSQL database administrators, ensuring the accuracy and relevance of the course content
Suitable for intermediate to advanced database administrators who seek to enhance their skills and gain expertise in PostgreSQL
Covers advanced topics such as tablespace management, backup and recovery, and performance tuning, making it suitable for experienced database professionals
Involves the use of Pgadmin 4, a popular PostgreSQL administration tool, providing hands-on experience with a widely used software

Save this course

Save PostgreSQL v12 Database Administration on Windows and Linux to your list so you can find it easily later:
Save

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in PostgreSQL v12 Database Administration on Windows and Linux with these activities:
Review PostgreSQL Architecture
Refresh your understanding of PostgreSQL architecture to build a stronger foundation for this course.
Browse courses on Database Architecture
Show steps
  • Review the course syllabus and identify the key concepts related to PostgreSQL architecture.
  • Go through your notes or textbooks from previous courses or self-study on PostgreSQL.
  • Take practice quizzes or tests to assess your understanding of PostgreSQL architecture.
SQL Refresher for PostgreSQL
Revisit and strengthen foundational SQL skills to enhance understanding of PostgreSQL syntax and data manipulation techniques.
Browse courses on SQL
Show steps
  • Review basic SQL concepts, such as data types, operators, and statements.
  • Practice writing SQL queries to perform data retrieval, modification, and aggregation.
  • Set up a PostgreSQL environment for practice.
  • Attempt simple SQL queries in PostgreSQL to apply your refreshed skills.
  • Consult with an instructor or mentor if encountering difficulties.
PostgreSQL Course Materials Review
Review and organize course notes, assignments, and other materials to enhance understanding and retention.
Browse courses on PostgreSQL
Show steps
  • Gather all course materials, including notes, presentations, and assignments.
  • Organize the materials into a logical structure, such as by topic or module.
  • Review the materials, focusing on key concepts and important details.
  • Summarize or create flashcards to reinforce key points.
  • Test your understanding by answering practice questions or creating your own quiz.
11 other activities
Expand to see all activities and additional details
Show all 14 activities
Join a PostgreSQL Study Group
Collaborate with peers, discuss concepts, and reinforce your understanding of PostgreSQL.
Show steps
  • Find or create a PostgreSQL study group.
  • Attend study sessions regularly.
  • Engage in discussions, share knowledge, and work together on practice problems.
Follow PostgreSQL Video Tutorials
Supplement your learning with visual demonstrations and explanations from experienced PostgreSQL users.
Show steps
  • Search for high-quality video tutorials on PostgreSQL.
  • Choose tutorials that cover specific topics you want to strengthen.
  • Take notes and practice the concepts demonstrated in the tutorials.
PostgreSQL Performance Tuning Tutorials
Follow guided tutorials to improve understanding of PostgreSQL performance tuning techniques and best practices.
Browse courses on PostgreSQL
Show steps
  • Identify online tutorials or resources on PostgreSQL performance tuning.
  • Set up a PostgreSQL environment for experimentation.
  • Follow the tutorials and implement the recommended techniques.
  • Monitor the database performance before and after applying the techniques.
  • Analyze and compare the performance improvements.
Read 'PostgreSQL: Up and Running'
Gain a deeper understanding of PostgreSQL concepts and practices by reading this foundational book.
Show steps
  • Purchase or borrow a copy of 'PostgreSQL: Up and Running'.
  • Read the first few chapters to get an overview of PostgreSQL.
  • As you progress through the course, refer back to the book for further clarification and examples.
SQL Practice Problems
Practice SQL queries to solidify understanding of PostgreSQL syntax and data manipulation techniques.
Browse courses on PostgreSQL
Show steps
  • Gather practice problems from online resources or textbooks.
  • Set up a PostgreSQL environment for practice.
  • Attempt to solve the practice problems using SQL queries.
  • Check your solutions against expected results or consult with an instructor or mentor for guidance.
  • Review incorrect answers and identify areas for improvement.
Attend a PostgreSQL Workshop
Enhance your PostgreSQL skills and knowledge by participating in a structured workshop.
Show steps
  • Search for PostgreSQL workshops in your area or online.
  • Select a workshop that aligns with your learning goals.
  • Actively participate in the workshop, ask questions, and take notes.
Solve PostgreSQL Practice Problems
Reinforce your understanding of PostgreSQL concepts through repetitive practice.
Show steps
  • Find online resources or textbooks with PostgreSQL practice problems.
  • Set aside dedicated time each week to solve a certain number of problems.
  • Review your solutions and identify areas where you need improvement.
Write a Blog Post on PostgreSQL Optimization
Deepen your understanding of PostgreSQL by researching and writing about optimization techniques.
Show steps
  • Research PostgreSQL optimization techniques.
  • Organize your findings into a coherent outline.
  • Write a blog post that explains the optimization techniques in a clear and concise manner.
PostgreSQL Database Design Project
Design and create a PostgreSQL database for a real-world scenario, applying principles learned in the course.
Browse courses on PostgreSQL
Show steps
  • Identify a project scenario and define the data requirements.
  • Design the database schema, including tables, relationships, and data types.
  • Create the PostgreSQL database and implement the designed schema.
  • Populate the database with sample data.
  • Test the database functionality and optimize queries for performance.
Design a PostgreSQL Database Schema
Apply your PostgreSQL knowledge to a practical scenario by designing a database schema.
Show steps
  • Identify the requirements for your database schema.
  • Create an entity-relationship diagram (ERD) to model the relationships between entities.
  • Translate the ERD into a PostgreSQL database schema using appropriate data types and constraints.
  • Validate your schema by creating sample data and querying it.
Build a Simple PostgreSQL Application
Solidify your PostgreSQL skills by building a functional application.
Show steps
  • Define the scope of your application and its functionality.
  • Design a database schema and create the necessary tables.
  • Write SQL queries to interact with the database.
  • Develop a user interface to interact with the application.
  • Test and deploy your application.

Career center

Learners who complete PostgreSQL v12 Database Administration on Windows and Linux will develop knowledge and skills that may be useful to these careers:
PostgreSQL Database Administrator
As a PostgreSQL Database Administrator, you will be responsible for the installation, configuration, maintenance, and performance tuning of PostgreSQL databases. This course will provide you with the knowledge and skills you need to succeed in this role, including how to manage database users and permissions, create and manage databases and tables, troubleshoot database issues, and perform backups and restores. Additionally, this course will cover the latest PostgreSQL features, such as logical replication and online index rebuilds, which are essential for maintaining high-performance, scalable databases.
Database Architect
As a Database Architect, you will be responsible for designing and implementing database systems that meet the needs of an organization. This course will provide you with the knowledge and skills you need to succeed in this role, including how to model data, design database schemas, and optimize database performance. Additionally, this course will cover the latest PostgreSQL features, such as JSONB support and materialized views, which are essential for building modern, scalable database systems.
Data Analyst
As a Data Analyst, you will be responsible for collecting, cleaning, and analyzing data to identify trends and patterns. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL to perform data analysis tasks, such as creating reports, building data visualizations, and developing machine learning models. Additionally, this course will cover the latest PostgreSQL features, such as time series support and geospatial data types, which are essential for working with large and complex datasets.
DevOps Engineer
As a DevOps Engineer, you will be responsible for bridging the gap between development and operations teams. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL to automate database tasks, such as provisioning, configuration, and monitoring. Additionally, this course will cover the latest PostgreSQL features, such as container support and Kubernetes integration, which are essential for building and managing modern, cloud-native applications.
Software Engineer
As a Software Engineer, you will be responsible for designing, developing, and maintaining software applications. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL as a database for your applications. Additionally, this course will cover the latest PostgreSQL features, such as stored procedures and triggers, which are essential for building high-performance, scalable applications.
Cloud Architect
As a Cloud Architect, you will be responsible for designing and implementing cloud-based solutions. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL as a database in the cloud. Additionally, this course will cover the latest PostgreSQL features, such as Amazon RDS and Azure Cosmos DB, which are essential for building and managing cloud-based databases.
Business Analyst
As a Business Analyst, you will be responsible for gathering and analyzing data to identify trends and patterns. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL to perform data analysis tasks, such as creating reports and building data visualizations. Additionally, this course will cover the latest PostgreSQL features, such as time series support and geospatial data types, which are essential for working with large and complex datasets.
Data Scientist
As a Data Scientist, you will be responsible for building and deploying machine learning models. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL to store and manage your data. Additionally, this course will cover the latest PostgreSQL features, such as support for JSONB and geospatial data types, which are essential for working with large and complex datasets.
Quality Assurance Tester
As a Quality Assurance Tester, you will be responsible for testing software applications to ensure that they meet the requirements. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL to store and manage test data. Additionally, this course will cover the latest PostgreSQL features, such as support for JSONB and geospatial data types, which are essential for working with large and complex datasets.
Technical Writer
As a Technical Writer, you will be responsible for creating and maintaining technical documentation. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL to store and manage your documentation. Additionally, this course will cover the latest PostgreSQL features, such as support for Markdown and AsciiDoc, which are essential for writing and publishing technical documentation.
Project Manager
As a Project Manager, you will be responsible for planning, executing, and delivering projects. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL to track project progress and manage project data. Additionally, this course will cover the latest PostgreSQL features, such as support for JSONB and geospatial data types, which are essential for working with large and complex datasets.
Data Engineer
As a Data Engineer, you will be responsible for building and maintaining data pipelines. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL to store and manage your data. Additionally, this course will cover the latest PostgreSQL features, such as support for Apache Kafka and Apache Spark, which are essential for building and managing modern data pipelines.
System Administrator
As a System Administrator, you will be responsible for managing and maintaining computer systems. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL to store and manage system data. Additionally, this course will cover the latest PostgreSQL features, such as support for Docker and Kubernetes, which are essential for building and managing modern, cloud-native systems.
Cloud Developer
As a Cloud Developer, you will be responsible for designing, developing, and deploying cloud-based applications. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL as a database in the cloud. Additionally, this course will cover the latest PostgreSQL features, such as support for Amazon RDS and Azure Cosmos DB, which are essential for building and managing cloud-based databases.
Database Developer
As a Database Developer, you will be responsible for designing, developing, and maintaining databases. This course will provide you with the knowledge and skills you need to succeed in this role, including how to use PostgreSQL to create and manage databases. Additionally, this course will cover the latest PostgreSQL features, such as support for JSONB and geospatial data types, which are essential for building and managing modern, scalable databases.

Reading list

We've selected seven books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in PostgreSQL v12 Database Administration on Windows and Linux.
Comprehensive resource for PostgreSQL users. It covers all aspects of PostgreSQL, from installation and configuration to advanced topics such as replication and clustering.
Provides a comprehensive overview of PostgreSQL, covering topics from installation and configuration to database design and optimization. It great resource for both beginners and experienced users.
Valuable resource for PostgreSQL administrators. It provides practical recipes for common administrative tasks, such as backup and recovery, performance tuning, and security.
Provides a comprehensive guide to PostgreSQL performance tuning. It covers a wide range of topics, including hardware and software configuration, query optimization, and replication. The book is written by a PostgreSQL performance expert, so you can be sure that the information is accurate and up-to-date.
Is an ideal entry-level guide for those new to PostgreSQL. It covers the basics of PostgreSQL, including installation, configuration, and administration. It also provides a comprehensive overview of PostgreSQL features, such as data types, operators, and functions.
Provides a comprehensive guide to the internal workings of PostgreSQL. It covers a wide range of topics, including storage, indexing, and query processing. The book is written by a PostgreSQL developer, so you can be sure that the information is accurate and up-to-date.

Share

Help others find this course page by sharing it with your friends and followers:

Similar courses

Here are nine courses similar to PostgreSQL v12 Database Administration on Windows and Linux.
Our mission

OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.

Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.

Find this site helpful? Tell a friend about us.

Affiliate disclosure

We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.

Your purchases help us maintain our catalog and keep our servers humming without ads.

Thank you for supporting OpenCourser.

© 2016 - 2024 OpenCourser