Skip to main content

PostgreSQL Administration Deep Dive

Course

Intro Video

Photo of Landon Fowler

Landon Fowler

Database Training Architect II

Landon currently resides in Forest, VA but is originally from Alabama (Roll Tide!). Outside of work he enjoys spending time with his family and volunteering at church. Landon is passionate about sharing his love for technology with others. He enjoys sharing his knowledge through blogging, volunteering locally, and hopes to one day retire and teach free tech skills classes for children.

Length

05:12:31

Difficulty

Intermediate

Videos

33

Hands-on Labs

5

Course Details

PostgreSQL is the world's most advanced open source database. With a focus on being open source, standards compliant, and extensible it has become a popular choice across a wide variety of use cases.

In this course we cover how to install, configure, use, and troubleshoot PostgreSQL. Whether you've worked with other databases before or are wanting to use PostgreSQL as your first, this course has something for everyone.

Syllabus

Course Introduction

Getting Started

Course Introduction

00:04:18

Lesson Description:

Welcome to the PostgreSQL Administration Deep Dive course! I'm excited to go on this learning journey with you. In this video we discuss the topics that will be covered, the resources and tools available to you, and prerequisites. Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! Class GitHub Repo https://github.com/linuxacademy/content-postgresql-deepdive/

About the Training Architect

00:00:36

Lesson Description:

Hi! I'd like to introduce myself. In this video, I give a very quick synopsis of who I am and my background. It's nice to meet you!Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

PostgreSQL Overview

Concepts Overview

Database and RDBMS Introduction

00:06:16

Lesson Description:

Before launching into the technical details of PostgreSQL, let's take a moment to lay a foundation. In this video we will take a high-level look at what a relational database is, what an RDBMS is, and how PostgreSQL fits into that.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

PostgreSQL History

00:03:57

Lesson Description:

In order to understand what PostgreSQL is today, let's take a look at where it came from. In this video we briefly go over its history and how it became a community favorite.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!PostgreSQL Documentation: https://www.postgresql.org/docs/current/history.html

Technical Overview

PostgreSQL Architecture

00:10:06

Lesson Description:

In this video we move past the generic concepts of how an RDBMS is structured and look specifically at the architecture of PostgreSQL. This will help you understand what the various components of the database server are, and how they work together to fulfill queries.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Clustering Discussion

00:09:43

Lesson Description:

Creating a plan for high availability (HA) is an essential part of any DBA's job. In this video we discuss the basic strategies for clustering in PostgreSQL and introduce what open source and proprietary offerings are available.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!PostgreSQL Wiki: https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

Clients

00:04:05

Lesson Description:

There are a number of options available for connecting to and interacting with PostgreSQL. In this video we go over some of the clients that you can use to create and execute queries against the system.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Psql

00:13:01

Lesson Description:

The command line utility included in a PostgreSQL installation, psql, will become a fundamental part of your role in administering PostgreSQL. This video will give you a jumpstart on using this valuable utility by providing information about its most common flags and meta-commands.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/app-psql.html

Installation and Setup

Installation

CentOS Installation

00:09:10

Lesson Description:

In this video we demonstrate how to install specific versions of PostgreSQL on CentOS. The commands used are provided below and are also available in the study guide or as a separate download.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! Commands Install the PostgreSQL SoftwareOpen the distribution .repo file in vi:

sudo vi /etc/yum.repos.d/CentOS-Base.repo
Exclude PostgreSQL by appending the following line to both the base and updates secitons.
exclude=postgresql*
Download and install the PGDG file from postgresql.org:
sudo yum -y install    https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Install PostgreSQL 12:
sudo yum install -y postgresql12-server postgresql12-contrib
Initialize the DatabaseInitialize the database:
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
Start and enable the postgresql-12 service:
sudo systemctl start postgresql-12
sudo systemctl enable postgresql-12
Connect as the Default UserChange to the postgres user and launch psql:
sudo su - postgres
psql
Secure the postgres role:
password postgres

Ubuntu Installation

00:05:22

Lesson Description:

In this video we demonstrate how to install the latest version of PostgreSQL on Ubuntu. The commands used are provided below and are also available in the study guide or as a separate download.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! Commands Install the PostgreSQL SoftwareAdd the repository:

sudo vi /etc/apt/sources.list.d/pgdg.list

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main
Import the GPG key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Update the package list:
sudo apt-get update
Install PostgreSQL:
sudo apt install postgresql postgresql-contrib
Verify the service:
service postgresql status
Connect as the Default UserChange to the postgres user and launch psql:
sudo su - postgres
psql
Secure the postgres role:
password postgres

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

00:15:00

Configuration

Server Configuration

00:15:54

Lesson Description:

In almost every implementation, there will be a need to vary from the base PostgreSQL configuration. In this video we discuss how to change server-level settings and demonstrate carrying out such changes.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!PostgreSQL Documentation: https://www.postgresql.org/docs/current/config-setting.html

Authentication and Authorization

00:06:38

Lesson Description:

In this video, we discuss the various authentication methods available in PostgreSQL, how to configure pg_hba.conf, and how the concept of users and roles is implemented.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!PostgreSQL Documentation: https://www.postgresql.org/docs/current/client-authentication.html

Assigning Permissions

00:07:12

Lesson Description:

Now that you have an understanding of how authentication and authorization works within PostgreSQL, let's take a hands-on look at how you assign permissions to users. In this video we demonstrate how to create users and roles, and how to use them to grant permissions.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-createrole.html

Database Configuration

00:05:13

Lesson Description:

In this video, we discuss using ALTER DATABASE to configure database-level settings.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-alterdatabase.html

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

00:30:00

Maintenance

Routine Maintenance

Vacuuming

00:07:19

Lesson Description:

One of the primary maintenance tasks in a PostgreSQL system is that of the VACUUM process. In this video, we explore what VACUUM does, how that affects your instance, and considerations for scheduling it.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/routine-vacuuming.html

Indexes

00:03:10

Lesson Description:

Indexes are a fundamental part of any RDBMS system, and keeping them maintained is a critical part of your role as a PostgreSQL DBA. In this video, we explore using the REINDEX command to do just that.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-reindex.html

Backup and Recovery

00:06:05

Lesson Description:

Being able to backup and recover your data is essential to having a well-maintained RDBMS system. In this video, we explore the options available for backup and recovery in PostgreSQL, and compare their advantages and disadvantages.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!PostgreSQL Documentation: https://www.postgresql.org/docs/current/backup.html

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

00:30:00

Troubleshooting

System Catalogs

00:04:12

Lesson Description:

One method available to us for gathering information on a PostgreSQL instance is using the System Catalogs. In this video, we explain what these are and how to utilize them to gain useful insights concerning your system.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!PostgreSQL Documentation: https://www.postgresql.org/docs/current/catalogs.html

Using pgAdmin

00:03:45

Lesson Description:

In this video we explore how GUI tools can be an asset when troubleshooting PostgreSQL systems. This is demonstrated using pgAdmin as we discover the real-time information it can expose for us.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!Documentation: https://www.pgadmin.org

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

00:15:00

Working with Data

Creating Objects

Database

00:03:34

Lesson Description:

In this video we discuss how to use the CREATE DATABASE command and PostgreSQL binaries to create a database.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-createdatabase.html

Schema

00:02:29

Lesson Description:

In this video we discuss using CREATE SCHEMA to better organize the objects within your database.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-createschema.html

Table

00:04:36

Lesson Description:

A fundamental piece of database design is using the CREATE TABLE statement to build out the structure of our data. In this video we introduce considerations around table creation, and how to use this command to start laying out the structure of our PostgreSQL database.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-createtable.html

View

00:04:25

Lesson Description:

Views are a great way to access pre-configured queries in a simple way. In this video we cover what views are and how to create them.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-createview.html

Index

00:05:16

Lesson Description:

An index can be a valuable mechanism for speeding up your queries. In this video we discuss what they are, how to create them, and some strategies for their optimal use.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-createindex.html

Changing Data

Insert

00:05:58

Lesson Description:

In this video we discuss the primary way of getting data into our tables, the INSERT statement.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-insert.html

Update

00:02:28

Lesson Description:

In this video we discuss how to alter the data in our tables using the UPDATE command.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-update.html

Delete

00:02:07

Lesson Description:

In this video we discuss using the DELETE statement to remove data from tables.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-delete.html

Selecting Data

Select

00:11:45

Lesson Description:

Our primary method for retrieving data and pulling useful insights from it is the SELECT statement. In this video we introduce this powerful statement and demonstrate some of the ways it can be used.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-select.html

Joins

00:06:22

Lesson Description:

In this video we take a high-level look at methods you can use to bring together data from different tables in your queries. This includes joins, subqueries, and unions.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)!

Programming

Stored Procedures

00:04:19

Lesson Description:

Stored procedures give you the ability to store and execute SQL code directly on the database system. In this video we describe describe what they are, how to create them, and how to utilize them.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-createprocedure.html

Triggers

00:05:01

Lesson Description:

In this video we discuss what triggers are, how to create them, and how they can be used to respond to actions on your tables.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/sql-createtrigger.html

Extending SQL

00:04:54

Lesson Description:

One of the features that makes PostgreSQL stand out from the crowd is its extensibility. In this video we discuss how it is architected to be extensible, the ways you can take advantage of that, and the benefits that come from taking this approach.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/extend.html

Hands-on Labs are real live environments that put you in a real scenario to practice what you have learned without any other extra charge or account to manage.

00:30:00

Conclusion

Final Steps

What's Next

00:02:59

Lesson Description:

Congratulations on finishing the course! In this video we do a quick recap of what has been learned and discuss where you might go from here on your learning journey.Feel free to reach out with any questions on the Linux Academy forums (@lfowler), community Slack (@Landon Fowler), or Twitter (lfowler_LA)! PostgreSQL Documentation: https://www.postgresql.org/docs/current/index.html

Take this course and learn a new skill today.

Transform your learning with our all access plan.

Start 7-Day Free Trial