Backup and Restore Fundamentals in PostgreSQL DB - Level 2

Name: Backup and Restore Fundamentals in PostgreSQL DB - Level 2
Rating: 3.7 (76 reviews)

Certified PostgreSQL ( Postgres ) Database Backup and Recovery Professional

Created byRajkumar V

Last updated 10/2017

English

What you'll learn

Practical exposure on backup and restore techniques in PostgreSQL Database
The best part is that every concept and techniques explained are supported by a hands on demo session
Source codes / scripts for performing backup and restoration tasks

Course content

6 sections • 19 lectures • 1h 45m total length

Section-1-1-Course-Introduction4:10
Master PostgreSQL backup and restore fundamentals by exploring physical backups, comparing them with logical backups, and applying practical recovery strategies through hands-on demos.

Section-2-1-What is backup and restore, in the context of database technology ?1:55
Define backup and restore concepts for databases, describing how backups copy structure and data for use in the same or another database, and note environment-specific strategies, such as separate disks.
Section-2-2-Importance of Backup and Restore in a database1:49
Learn why backup and restore are essential for PostgreSQL databases, as human error, failures, or disasters threaten data integrity and availability, and how a well-planned recovery strategy protects operations.
Section-2-3-Different techniques of backup and restore in PostgreSQL database1:05
Explore three techniques for backing up and restoring a PostgreSQL database, and learn when to use each technique for your environment.
Section-2-4-Backup Options in PostgreSQL3:46
Explore the two backup approaches in PostgreSQL—logical backups (sql dumps) and physical backups—covering schema and data recreation, cross-version use, and the speed and constraints of full cluster backups.
Section-2-5-Environment Setup13:37
Set up a complete demo environment with a sample database for Postgres backups and recovery, including an Ubuntu vm, Postgres 9.6.5, and tablespace and user creation.

Section-3-1-Introduction to Filesystem Level Backup and Restore3:52
PostgreSQL stores data in a data directory owned by the postgres user, and filesystem backups copy this directory to back up and restore the entire cluster.
Section-3-2-Approach-1 Copy and Paste8:51
Explore the file system level backup and restore strategy for PostgreSQL, using cp to back up the data directory and a three-step offline process, then restore and verify with psql.
Filesystem Level Backup and Restore
Section-3-3-Approach-2 Rsync19:01
Learn how to back up and restore a PostgreSQL database using rsync, including a four-step process, incremental backups with checksum, and validation through restoration.

Section-4-1-Introduction to Continuous Archival and Point in time Recovery0:53
Explore continuous archival and point-in-time recovery in PostgreSQL, including fundamentals, a demo of taking physical backups, and recovering to a specified point in time.
Section-4-2-What is Continuous Archival ?2:06
Explore write ahead log (wal) files and how PostgreSQL uses them to protect data, and learn how continuous archival copies wal files from the pg_xlog directory for safe recovery.
Section-4-3-What is PITR ?1:04
Use point-in-time recovery to restore a database to a past date and time, such as 12:05 am to 12:00 am on 22 October 2017, a feature available since PostgreSQL 8.0.
Section-4-4-How Continuous Archival is used for PITR ?1:31
Learn how continuous archiving of WAL files enables point in time recovery in PostgreSQL. Ensure you have a base backup and all WAL backups taken after it.
Section-4-5-Demo : Continuous Archival and Point in Time Recovery24:43
Learn how to set up continuous archival in PostgreSQL, create base backups, and perform point-in-time recovery using WAL archiving and recovery configurations.
Section-4-6-How PITR Works ?5:20
Explore how PITR replays archived WAL files from a base backup at the checkpoint to a defined recovery_target_time, using recovery.conf and archive_command.
Section-4-7-Most Challenging but Rewarding Approach2:13
Utilize continuous archival of all files for fast backups with incremental changes, enabling point-in-time recovery and standby system operation without requiring a perfectly consistent base backup.
Continuous Archival and PITR

Section-5-1-Pros and Cons of Physical Backups and Logical Backups5:33
Evaluate the pros and cons of physical backups and logical backups in PostgreSQL, including PITR capabilities, point-in-time restore, full database cluster restoration, and related trade-offs.
Section-5-2-When to use Physical Backups and When to use Logical Backups2:18
Choose logical backups for small databases under 100 gb with pg_dump for quick recovery; use physical backups for large databases near 1 tb to meet production load and test backups.

Requirements

Exposure to any Operating System (OS) and Installation of OS. Good to have exposure on Oracle Virtual Box Manager
You would need an operating system of any sort, such as Linux, Windows, Ubuntu or Mac.
For practicing hands on demo sessions involving multiple machines, you need to have PostgreSQL DB installed in HOST OS and Virtual Machines
Recommended but Optional to attend my earlier course on "Backup and Restore Fundamentals in PostgreSQL DB - Level 1" that details about Logical Backup and Restore techniques

Description

Learn the key terminologies, basic concepts, implementation techniques that you will need to carry out the backup and restoration tasks in PostgreSQL database. Every concepts or topics are covered with hands on demo session to provide better clarity.

Many of the key concepts learned in this course can be applied on other databases as well.

Content and Overview

Through this course, comprising of 6 Sections with multiple lectures and demo sessions, Quiz chapters, Assignments, along with additional resources, you will

Understand what, when and how to carry out backup and restoration tasks.
Gain confidence in implementing Backup and Restoration jobs in Production system
Know how to write backup and restoration scripts
Understand how to automate all the tasks related to backup and restoration tasks

You can test the knowledge gained through the sessions by attending quizzes and every use case mentioned in the course are explained with demo sessions thereby enabling you to practice the newly learned skills.

I will add more contents to this course as and when possible.

Downloadable Resources

The backup and restoration scripts used are mostly of one liner form and hence you could watch those in the videos and use the same in your project. Whereas in certain demo sessions, complex scripts are used and the same has been attached as resources for you to download.

You can download the source code files / Environment setup instructions used during the demo sessions, to practice at your end.

Learners who complete this course will gain the knowledge and confidence to implement a fully functional Backup and Restoration solutions in the projects.

Who this course is for:

Database Administrators
Database Developers
Database Testers
Anyone who loves technological aspects

Backup and Restore Fundamentals in PostgreSQL DB - Level 2

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 4min

Building the Foundation5 lectures • 22min

Filesystem Level Backup and Restore3 lectures • 32min

Continuous Archival and Point-In-Time Recovery7 lectures • 38min

Physical Backup Approach Vs Logical Backup Approach2 lectures • 8min

What Next ?1 lecture • 1min

Requirements

Description

Who this course is for: