Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

PostgreSQL Database Administration Essential Training

Name: PostgreSQL Database Administration Essential Training
Rating: 4.1 (140 reviews)

PostgreSQL 16 Database Administration Essential Training

Created byEmar Morrison

Last updated 1/2025

English

English [Auto],

What you'll learn

How to Install and Configure Postgres
How to Perform Backups and Restores
How to Create Roles and Grant Permissions
How To Manage Tablespaces
How To Optimize Database Backups
How To Migrate Databases

Course content

9 sections • 46 lectures • 1h 55m total length

Introduction0:57
What is postgres2:24

Getting Started with Postgres0:24
Set up the test environment by installing pgadmin, Oracle VirtualBox, Ubuntu ISO image, and Windows ISO image or OVA file to configure a Linux server and Windows Server.
Installing PG Admin1:41
Download the latest Pgadmin release from the official site, install it on Windows, and finish setup; Pgadmin is the cross-platform tool to manage and interact with databases in the course.
Installing Oracle Virtual Box0:56
Install Oracle VirtualBox by downloading the Windows host platform package, running the installer with Python core dependencies, finishing setup, and launching VirtualBox; next, download the Ubuntu desktop for this course.
Install PostgreSQL on Linux10:30
This is an updated video there the IP used will be different for other videos.
Install PostgreSQL on Windows2:29
Install PostgreSQL on Windows by downloading the Windows installer, selecting version 12, configuring the data directory on the D drive, setting the PostgreSQL superuser password, and launching PgAdmin 4.
Update Notice0:09
Understanding PostgreSQL Configuration files3:29
In version 16, the path is: /etc/postgresql/16/main.
Connecting to PostgreSQL Database4:09
Configure PostgreSQL to accept connections by backing up and editing postgresql.conf and pg_hba.conf, enabling listen_addresses, md5 authentication, restarting the server, and setting the postgres password.

Creating your first database5:50
Learn how to create a new database in Postgres by writing a database statement, assign an owner, choose a template, encoding, and tablespace, and set the connection limits.
Altering PostgreSQL database1:26
Renaming PostgreSQL Database1:05
Rename a PostgreSQL database using the alter database syntax, after connecting to a different database and terminating active connections; only super users with create database privilege may rename.
Changing Database Owner0:36
Changing The Tablespace of a Database0:51
Demo: Altering Databases5:29
Learn how to alter a PostgreSQL database by changing the connection limit, renaming databases, managing active sessions, and changing ownership using the alter database commands and backend termination tools.

Understanding tablespaces in PostgreSQL3:17
Creating a Tablespace in Postgres4:41
Create a tablespace with the create tablespace statement, set an owner and absolute path, and avoid names starting with pkg underscore; assign it to databases, tables, or indexes.
Altering Tablespaces in Postgres2:01
Use the PostgreSQL alter tablespace statement to rename a tablespace, change the owner, and set parameters, then verify changes by refreshing table spaces and reviewing properties.
Dropping Tablespaces in Postgres1:56
Learn how to safely drop a tablespace in Postgres, including using drop tablespace with if exists, verifying dependencies, ensuring the tablespace is empty, and noting owners and super users.

Backing up a Single Database2:47
Backing up all Databases3:19
Compressing Postgres Backups1:42
Compress postgres backups with gzip, either piping during backup or after, observe significant size reductions, and use verbose output and logs to monitor any errors.
Splitting Backups into Multiple Files0:53
Backing up Remote Databases1:24
Optimizing Postgres Backups2:45
Use parallelism with pg_dump to cut backup time for PostgreSQL databases, balancing jobs with CPU. In directory format, backups are compressed by default, demonstrated with the DVD rental database.
Restoring all Backup Types7:51

Maintenance in Postgres0:38
Explore how periodic vacuuming keeps Postgres databases healthy, from automated vacuum demon to manual scheduling with PPG agent or CRON, and adjust parameters for your workload.
Vacuuming Basics1:33
Syntax Parameters in PostgreSQL VACUUM Command
FULL: The FULL request selects a “full” vacuum. The request takes a longer processing time and locks the table. One vivacious is that it can free up more space. The request creates new duplicates of the table queried and do not delete the old one until the operation is finished. This approach uses more disc space, limiting its use only when a substantial quantity of table space has to be freed up.
FREEZE: The FREEZE request is for aggressive freezing of tuple. Whenever you specify FREEZE, it is equivalent to performing VACUUM with the parameters set to zero for vacuum_freeze_min_age and vacuum_freeze_table_age. The FREEZE request should only be performed whenever you need to rewrite the table — making it counterintuitive/redundant if the user states a FULL request.
VERBOSE: The VERBOSE request prints a thorough report on the vacuum action for every table.
ANALYZE: The ANALYZE request updates the facts/statistics that the planner uses to decide the best and the most efficient way to carry out a query.
Vacuuming: Recovering Disk Space2:39
Vacuuming: Updating Planner Statistics3:54
Learn how PostgreSQL uses table statistics gathered by analyze during vacuum to drive query plans, and how the art of vacuum demon keeps statistics up to date for better performance.
Vacuuming: Updating the Visibility Map1:02
Vacuuming: Preventing Transaction ID Wraparound Failures1:07
The Auto vacuum Daemon0:54
Routine Reindexing0:40
SYNTAX: REINDEX [ ( VERBOSE ) ] {SCHEMA | DATABASE | TABLE |SYSTEM | INDEX } name;
VERBOSE: This keyword is optional. The statement shows the progress report when the VERBOSE is defined while indexes being reindexed.
INDEX: Reconstruct the defined index.
TABLE: Reconstruct all indexes of the defined table.
DATABASE: Reconstruct all indexes of the defined database.
SYSTEM: Reconstruct all indexes on the system of the defined database.
name: This field defines the name of the index, table, database, or schema.

Requirements

Laptop with 4GB RAM and at least 10GB hard disk free.
Internet connection to download installation files.

Description

PostgreSQL Administration Course Overview

This course is targeted at beginners and DBAs with experience using another DBMS such as Microsoft SQL Server, MySQL, or Oracle.

Exercises and examples are used throughout the course to give practical, hands-on experience with the techniques covered.

Course Objectives

This course aims to provide the delegate with the knowledge to be able to install, administer, maintain, backup, recover, and tune a PostgreSQL database.

Who will the Course Benefit?

This PostgreSQL Administration course is designed for professional database administrators who need to gain an understanding of the features and functionality that can be used to manage PostgreSQL 10, 11, 12, 13, 14, 15 an 16 databases. This course is also suitable for users of PostgreSQL 9 databases.

Skills Gained

The delegate will learn and acquire skills as follows:

Install PostgreSQL
Create a database
Manage roles and privileges
Examine statistics and system tables
Manage tablespaces
Carry out periodic maintenance with VACUUM autovacuum
Backup and restore databases
Configure settings for increased performance

PostgreSQL is one of the most advanced open-source platforms available today. Multiple large-scale organizations such as Cisco and Apple use the tool for their web-based and mobile-based application development. It is managed by an open-source community that is dedicated to improving the platform to keep it relevant to today's time. Another focus for the community of developers is security; they keep evolving PostgreSQL and updating their security measures to keep up with the threats of today.

Who this course is for:

Database administrators
DBAs
Junior Database Administrators
I.T Professionals

PostgreSQL Database Administration Essential Training

What you'll learn

Explore related topics

Course content

Introduction2 lectures • 3min

Install & Configure Postgres8 lectures • 24min

Managing Databases6 lectures • 15min

Managing Tablespaces4 lectures • 12min

Securing Your Server5 lectures • 19min

Backup & Restore Databases7 lectures • 21min

Scheduling With pgAgent5 lectures • 9min

Routine Database Maintenance Tasks8 lectures • 12min

Congratulations1 lecture • 1min

Requirements

Description

Who this course is for: