PostgreSQL Database Administration on WindowsLinux Part 1

PostgreSQL Database Administration on WindowsLinux Part 1

What you’ll learn

Understand PostgreSQL Process and Memory Architecture.
Install PostgreSQL v12 on Windows and Linux.
How to Setup Environment Variable on Windows and Linux.
PostgreSQL Directory Structure.
PostgreSQL Configuration Files.
How to use PSQL command line interface to connect to PostgreSQL.
How to Create Database/User/Schema from utility and Psql commands.
Learn how to use pg system catalogs.
CRUD operations on PostgreSQL.
Table Inheritance in PostgreSQL.
Partitioning in PostgreSQL using Table Inheritance.
Copy table from/to PostgreSQL.
How to Create/Move/Drop Tablespace.
Logical (pg dump, pgdumpall), Physical backup (Offline, Online).
Continuous Archiving and PITR.
What is Explain plan and how to calculate cost of query.
Maintenance (Updating planner statistics, Vacuum, Routine Reindex, Cluster).
What is Transaction Id Wraparound Failure and Vacuum Freeze.
How to use Pgadmin 4 Interface.
Uninstall PostgreSQL on Windows and Linux.
Bonus Module
Course Extra Documents
Journey into Postgresql v13
Postgresql v13 – New Features
Let’s Explore Postgresql v15
Postgresql v15- New Features

Requirements

Basic Knowledge of Database Concepts
Internet access is required to download PostgreSQL Server
Working Knowledge on Windows and Linux

Description

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 ArchitectureInstallation of PostgreSQL v12 – Windows and Linux Setup PostgreSQL v12 Environment Variables – Windows and LinuxPostgreSQL Page LayoutDatabase Directory LayoutPostgreSQL Configuration FilesCluster in PostgreSQL (Initdb,Start/Stop,Reload/Restart Cluster)Createdb/Dropdb, Createuser/Dropuser UtilityCreate Schema and Schema Search Path Psql Interface CommandsPg System CatalogsWorking with PostgreSQL ObjectsCRUD OperationsTable Inheritance Table PartitioningTablespace in PostgreSQLBackup, Continuous Archiving and PITR (Point-in-Time-Recovery)Explain plan and Query Execution CostMaintenance (Updating Planner Statistics, Vacuum, Routine Reindex & Cluster)Data FragmentationTransaction ID wraparound, Vacuum freeze, Auto_Vacuum DemonPgadmin 4 Uninstall PostgreSQL v12 on Windows and LinuxBonus ModuleJourney into Postgresql v13Postgresql v13 – New FeaturesLet’s Explore Postgresql v15Postgresql v15 – New Features

Overview

Section 1: Introduction

Lecture 1 PostgreSQL Introduction

Lecture 2 PostgreSQL Page Setup

Section 2: Installing PostgreSQL v12 on Windows and Linux

Lecture 3 PostgreSQL System Requirement

Lecture 4 Installing PostgreSQL v12 on Windows

Lecture 5 Setup PostgreSQL Environment Variable on Windows

Lecture 6 Installing PostgreSQL v12 on Linux

Lecture 7 Setup PostgreSQL Environment Variable on Linux

Section 3: PostgreSQL Architecture

Lecture 8 Process and Memory Architecture

Lecture 9 Postmaster Process

Lecture 10 Utility Processes

Lecture 11 Memory Segments

Lecture 12 Physical Files in PostgreSQL

Section 4: Database Cluster

Lecture 13 PostgreSQL Cluster (Initdb, Start/Stop/Restart/Reload)

Lecture 14 PostgreSQL Cluster Demo on Windows

Lecture 15 PostgreSQL Cluster Demo on Linux

Section 5: PostgreSQL Directory Layout

Lecture 16 Database Directory Layout

Lecture 17 Base Directory

Section 6: Configuration Files

Lecture 18 PostgreSQL.conf

Lecture 19 PostgreSQL.Auto.conf

Lecture 20 Pg_Ident.conf

Lecture 21 Pg_Hba.conf

Section 7: Create Objects(Database/User/Schema) and Privileges

Lecture 22 Create/Drop Database

Lecture 23 Create/Drop User

Lecture 24 Create/Drop Schema and Search Schema Path

Lecture 25 Grant/Revoke Privileges on Database Objects

Section 8: Using Psql Command Line Interface

Lecture 26 Psql Commands

Section 9: Working with Pg System Catalog

Lecture 27 Pg_Catalog & Time zones in PostgreSQL

Section 10: CRUD Operations in PostgreSQL

Lecture 28 CRUD Introduction

Lecture 29 Create Table, Data Types, Constraints, Functions and Operators

Lecture 30 Retrieve Data in PostgreSQL

Lecture 31 Update Records in a Table

Lecture 32 Delete Records in a Table

Section 11: Table Inheritance, Table Partitioning and Copy Table in PostgreSQL

Lecture 33 Table Inheritance

Lecture 34 Table Partitioning using Inheritance

Lecture 35 Copy Table

Section 12: Working with Tablespace

Lecture 36 Tablespace Introduction

Lecture 37 Create Tablespace on Windows and Linux

Lecture 38 Move Objects from Tablespace and Drop a Tablespace

Lecture 39 Temporary Tablespace in PostgreSQL

Section 13: Backup and Restore

Lecture 40 Introduction to PostgreSQL Backups

Lecture 41 Logical Backups on Windows and Linux

Lecture 42 How to Compress and Split Dump Files

Lecture 43 Restore Database using Logical Backups through Psql Interface

Lecture 44 Restore Database using Logical Backups through Pg_Restore Utility

Lecture 45 Offline Backups

Lecture 46 Introduction to Online Backups

Lecture 47 Setup Continuous Archiving In Windows

Lecture 48 Setup Continuous Archiving In Linux

Lecture 49 Online Low Level API Backup

Lecture 50 Pg_Basebackup Utility

Lecture 51 Point-In-Time-Recovery(PITR)

Lecture 52 Point-In-Time-Recovery(PITR) – Demo

Section 14: Maintenance in PostgreSQL

Lecture 53 Introduction to Maintenance

Lecture 54 How to Calculate Query Cost and Explain Plan

Lecture 55 Updating Planner Statistics\Analyze

Lecture 56 Vacuum Freeze

Lecture 57 Data Fragmentation

Lecture 58 Introduction to Vacuum and Vacuum Full

Lecture 59 Transaction ID Wrap Around Failure

Lecture 60 How to Vacuum a Database and Remove Data Fragmentation

Lecture 61 Routine Re-Indexing

Lecture 62 How to Cluster a Table

Lecture 63 Auto-Vacuum in PostgreSQL

Section 15: Introduction to Pgadmin 4 Interface

Lecture 64 Pgadmin 4 – Demo

Section 16: Uninstall PostgreSQL v12 From Windows and Linux

Lecture 65 Uninstall PostgreSQL v12 From Windows – Demo

Lecture 66 Uninstall PostgreSQL v12 From Linux – Demo

Section 17: Bonus Module

Lecture 67 Introduction to PostgreSQL Upgrade

Lecture 68 PostgreSQL Upgrade using PG_Dumpall Utility.

Lecture 69 Introduction to PG_Upgrade Utility

Lecture 70 PostgreSQL Upgrade using PG_Upgrade Utility

Section 18: Journey into Postgresql- v13

Lecture 71 Introduction to Postgresql v13

Lecture 72 Postgresql v13 – New Features Part 1

Lecture 73 Postgresql v13 – New Features Part 2

Lecture 74 Postgresql v13 – New Features Part 3

Section 19: Let’s Explore Postgresql -v15

Lecture 75 Introduction to Postgresql v15

Lecture 76 Postgresql v15 – New Features 1

Lecture 77 Postgresql v15 – New Features 2

Lecture 78 Postgresql v15 – New Features 3

Database Administrators,Accidental DBA’s,Developers,Architects and Analyst,Students,Anyone who is curious about PostgreSQL

Course Information:

Udemy | English | 12h 28m | 3.80 GB
Created by: Sadiq Basha

You Can See More Courses in the Developer >> Greetings from CourseDown.com