PostgreSQL 16 Administration: Basic Course

Course ID : PS-DBA1

Duration In-class (в days) : 3 days

Duration Online : 3 days

Сurriculum : Virtual Instructor-Led Training - ONLINE

Overview

This course covers the fundamental topics required by any database administrator.

Students will learn how to install, manage, and maintain a server, as well as user and access rights management.

Audience for this course

Database administrators

Prerequisites for this course

  • Introduction to Relational Databases
  • Introduction to SQL
  • Introduction to Unix/Linux Operating Systems

Outcomes

By completing this course, students will have acquired the skills to:

      • Understand the PostgreSQL architecture
      • Install, perform basic configuration, and manage the server
      • Organize data at the logical and physical levels
      • Perform basic administration tasks
      • Manage users and access
      • Understand backup and replication

Outline

Module 1. “Basic Tools”

  • Install and Manage the server
  • Use psql
  • Configure

 Lab

  • Manage the PostgreSQL server, work with checksums in a database cluster
  • Sett up and work with the psql utility
  • Work with server configuration parameters

Module 2. “Architecture”

  • General structure of PostgreSQL
  • Isolation and multiversion concurrency
  • Clean up
  • Buffer cache and log

Lab 

  • Learn transaction isolation levels
  • Learn how to cleanup process works and its results
  • Compare different options for shutting down a DBMS Instance, gett to know the server message log

 Module 3. “Data Organization”

  • Databases and schemas
  • System catalog
  • Tablespaces
  • Low Level

Lab 

  • Create and work with a database and schemas
  • Work with the system catalog, gett to know the Psql commands for working with the system catalog 
  • Work with tablespaces and various database objects within them
  • Study methods for storing table data at the physical level

Module 4. “Administration Tasks”

  • Monitore

Lab

  • Work with table usage statistics, studying transaction deadlock resolution

Module 5. “Access Management”

  • Roles and Attributes
  • Privileges
  • Row Security Policies
  • Connection and Authentication

Lab 

  • Create Roles, work with roles and their attributes
  • Configure role privileges for accessing tables
  • Assign table row security policies
  • Configure user connections using different authentication methods

Module 6. “Backup”

  • Overview

Lab

  • Create and restore a logical database backup
  • Create and restore a physicalcluster backup

Module 7. “Replication”

  • Overview

 Lab 

  • Configure physical synchronous server replication
  • Configure logical table replication

 

Ask question