PostgreSQL 9.4 Administration

Course

December 31st, 2017

Intro Video

Photo of Terrence Cox

Terrence Cox

Vice President of Content in Content

A veteran of twenty years in Information Technology in a variety of roles. He has worked in development, security and infrastructure well before they merged into what we now call DevOps. He provides training in Linux, VMWare, DevOps (Ansible, Jenkins, etc) as well as containers and AWS topics. He now leads the Training Architects to produce the courses and content we all know and love!

Length

04:39:05

Difficulty

Intermediate

Course Details

Understanding open source RDBMS systems is an important skill for a successful system administrator or engineer.

This course will help you gain a deep understanding of how to install and coonfigure PostgreSQL v9.4. Once you have the server itself, we will take a look at creating users that can access our RDBMS and setting up a basic schema, as well as how to create basic replication and do simple backup/restore.

Finally, we will show you how to build and work with that schema in order to understand data types, inserting, updating and deleting records.

Syllabus

Introduction

Course Syllabus

00:08:44

Lesson Description:

This video will go through everything that will be covered throughout the course.

Introduction to Databases

00:07:26

Lesson Description:

Now we start our journey! Let's step back and introduce databases and the DBMS as a concept.

Data Normalization

00:09:10

Lesson Description:

Data Normalization is an important and complex topic as it pertains to databases. We will talk about what normalization is as well as explain the levels of normalization typical to most systems.

Indexes, Relationships and Keys

00:07:01

Lesson Description:

The primary terms that are needed to be familiar with in any database system are covered at a high level in this video, database indexes, relationships and keys.

Introduction to RDBMS

00:06:57

Lesson Description:

Now that we understand the basics of Databases and Database Management Systems, we will explore the most common one - Relational Database Management Systems. We will talk about how it is the same as a DBMS as well as what the key differentiators are.

Introduction to PostgreSQL 9.4

00:06:09

Lesson Description:

In this video we begin the specific journey towards managing and using PostgreSQL 9.4. We will talk about where it fits in the DBMS landscape, what it works on, and the key features of the product in general.

Installation and Configuration

Downloading PostgreSQL (Enterprise vs. Open Source)

00:05:51

Lesson Description:

In this video we will talk about the two places to obtain PostgreSQL from. We will explore the Open Source vs. Enterprise DB versions, features and advantages and talk about the places you could use each one.

Installation and Service Configuration

00:08:38

Lesson Description:

We are ready to install our PostgreSQL database engine. We will talk about where to obtain the latest copy and configure our system for installing it. After installation, we will walk through the initialization of the engine and then how to set it for starting on boot.

Using the Command Line Client

00:04:49

Lesson Description:

Now that our system is installed, configured and initialized, let's take a moment to verify by connecting. We will talk about the command line client, how to connect, how to secure our Postgres user and how to enter and exit the command line shell.

Exercise: Install and Configure PostgreSQL 9.x: RHEL/CentOS

00:30:00

Exercise: Install and Configure PostgreSQL 9.x: Debian/Ubuntu

00:30:00

Administration and Security

Creating Databases

00:12:01

Lesson Description:

This video will show you how to create PostgreSQL databases from the shell as well as using the PSQL client.

Creating Tables

00:09:42

Lesson Description:

Now that we have created our databases, we can now look to create the schema, or table structures that will contain our data.

Roles - Creating and Deleting Users

00:09:18

Lesson Description:

With the creation of our database and some basic schema objects (tables) that can house our data, we have to know how to create users and give them permissions to do certain activities within our databases.

Roles - Assigning Permissions

00:08:39

Lesson Description:

We have databases, we have tables and schema, we have users with no permissions! Let's add the ability for our users to see, access and "do some stuff" within those databases.

Installing and Configuring myPgAdmin

00:12:24

Lesson Description:

In this video we will show where to pull down and how to install phpPgAdmin (similar to phpMyAdmin for MySQL/Mariadb).

Accepting External Connections

00:10:52

Lesson Description:

Our new web-based PostgreSQL management tool is installed and available, but we cannot connect to it remotely. Let's walk through the configuration files and permission types we need to grant to allow external communication with our database instance.

Inserting, Viewing and Deleting Data

00:07:51

Lesson Description:

We are ready to go! Let's dive in and start creating some data.

Backing Up and Restoring Databases

00:11:20

Lesson Description:

Now that we have some data that we may actually care about, let's step back and look at how to backup and restore our databases both at the command line and using phpPgAdmin.

Replication - Master Server Configuration

00:11:11

Lesson Description:

We will talk about basic clustering, specifically master/slave replication. In this video we will set up one node as the master database to replicate to a slave node.

Replication - Slave Server Configuration

00:10:36

Lesson Description:

Now that we have our master configuration done, we need to configure the slave, restore the databases from the master and start replication.

Discussion - Database Clustering

00:09:21

Lesson Description:

Database clustering is a topic not unique to PostgreSQL. We will explore it at a high level and then talk about how those types of clusters can be applied in the PostgreSQL space.

Exercise: Creating and Deleting Databases and Adding Users

00:30:00

Exercise: Create a Table and Inserting Data

00:30:00

Exercise: Configure the Database for External Connections

00:30:00

Exercise: Database Backup and Restore

00:30:00

Datatypes

Introduction to Datatypes

00:08:06

Lesson Description:

We are going to take a step back from the details and talk about Data Types. These are integral to understand as they absolutely will affect our ability to allocate appropriate resources, manage and optimize our database system.

Character Datatype

00:11:53

Lesson Description:

Now that we know what data types are in general and how the abstract primitive type allows us to inherit common characteristics, we will take a look at two of the most common data types in our database instance. Once we know what they are and when to use them, we will create a "better" version of our cities table to show how they are used and what errors can be generated when importing data that does not match the type or size defined.

Numeric Datatype

00:11:20

Lesson Description:

Now that we know what data types are in general and how the abstract primitive type allows us to inherit common characteristics, we will take a look at another set of common data types in our database instance. Once we know what they are and when to use them, we will create a table to show how they are used and what errors can be generated when importing data that does not match the type or size defined.

Serial Datatype

00:11:08

Lesson Description:

Now that we know what data types are in general and how the abstract primitive type allows us to inherit common characteristics, we will take a look at another set of common data types in our database instance. Once we know what they are and when to use them, we will create a table to show how they are used and what errors can be generated when importing data that does not match the type or size defined.

Boolean Datatype

00:06:59

Lesson Description:

Now that we know what data types are in general and how the abstract primitive type allows us to inherit common characteristics, we will take a look at another set of common data types in our database instance. Once we know what they are and when to use them, we will create a table to show how they are used and what errors can be generated when importing data that does not match the type or size defined.

Special Datatype - NULL

00:06:00

Lesson Description:

Now that we know what data types are in general and how the abstract primitive type allows us to inherit common characteristics, we will take a look at another set of common data types in our database instance. Once we know what they are and when to use them, we will create a table to show how they are used and what errors can be generated when importing data that does not match the type or size defined.

Date Datatype

00:08:24

Lesson Description:

Now that we know what data types are in general and how the abstract primitive type allows us to inherit common characteristics, we will take a look at another set of common data types in our database instance. Once we know what they are and when to use them, we will create a table to show how they are used and what errors can be generated when importing data that does not match the type or size defined.

Time Datatype

00:08:18

Lesson Description:

Now that we know what data types are in general and how the abstract primitive type allows us to inherit common characteristics, we will take a look at another set of common data types in our database instance. Once we know what they are and when to use them, we will create a table to show how they are used and what errors can be generated when importing data that does not match the type or size defined.

Timestamps

00:14:13

Lesson Description:

Now that we know what data types are in general and how the abstract primitive type allows us to inherit common characteristics, we will take a look at another set of common data types in our database instance. Once we know what they are and when to use them, we will create a table to show how they are used and what errors can be generated when importing data that does not match the type or size defined.

Conclusion

NoSQL and PostgreSQL

00:06:05

Lesson Description:

Although PostgreSQL is typically considered an RDBMS, it is one of the few that supports NoSQL and unstructured data fully within the same engine. Get an overview of NoSQL and how PostgreSQL can offer the best of both worlds in the same product.

Big Data Processing

00:08:22

Lesson Description:

Big data is a general term but Postgresql has features and advantages for its use vs. some of its more specialized competition. We will discuss what those features are and how Big Data and Postgresql go hand in hand.