merger merger

Welcome to your new course portal!

On October 15th, hotcourses.com moved to findcourses.co.uk - which means you can now find all your favourite courses right here. Don't worry - you can still do all the things that you could do before.

All you need to do is search.

Show findcourses.co.uk as: Mobile

PostgreSQL Administration - In-house Training

StayAhead Training
Course summary
5 days
Nationwide
Next available date: Contact for info - United Kingdom
Corporate Training for Teams

Course description

PostgreSQL Administration - In-house Training

This PostgreSQL Administration course covers administration, maintenance, security and performance tuning of PostgreSQL databases.

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

The delegate will gain an understanding of:

  • Install PostgreSQL
  • Create a database
  • Manage roles and privileges
  • Manage schemas
  • Transaction handling and concurrency
  • Examine statistics and system tables
  • Manage tablespaces
  • Scale PostgreSQL using table inheritance and partitioning
  • Understand error reporting and logging
  • Security features of PostgreSQL
  • Encrypt sensitive data
  • Carry out periodic maintenance with Autovacuum
  • Backup and recover databases
  • Create indexes to improve table access
  • Optimize queries and indexes
  • Configure settings for increased performance
  • Install and use phpPgAdmin for web based administration
Who will the Seminar Benefit?

This PostgreSQL Administration course is designed for professional database administrators who need to gain an understanding of and implement the features and functionality required to manage PostgreSQL databases.

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.

Requirements

This course assumes a good working knowledge of SQL and specifically of the SELECT, INSERT, UPDATE and CREATE TABLE statements. This can be obtained by attendance on the pre-requisite PostgreSQL course.

Course Notes

This course is run on a Linux operating system, a good working knowledge of Linux/UNIX is therefore required.

Pre-Requisite Courses
  • PostgreSQL
Follow-On Courses
  • Python Programming 1
  • PHP Programming
Notes:
  • Course technical content is subject to change without notice.
  • Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.
PostgreSQL Administration Training Course

Course Contents - DAY 1

Course Introduction

  • Administration and Course Materials
  • Course Structure and Agenda
  • Delegate and Trainer Introductions

Session 1: CONFIGURATION AND INSTALLATION OF POSTGRESQL

  • Install PostgreSQL
  • Install PostgreSQL on Linux/UNIX
  • Post-Installation Setup
  • The PostgreSQL Windows Installer
  • Create a New Database using the CREATE DATABASE Statement
  • Create a New Database using pgAdmin
  • Start the Database Server
  • Drop a Database
  • Copy a Database
  • List Databases and Obtain Database Object Sizes
  • Initialise a Cluster

Session 2: POSTGRESQL CLIENT APPLICATIONS

  • Overview of PostgreSQL Client Applications
  • The PostgreSQL Interactive Client Terminal - psql
  • Startup Failures and Client Connection Problems
  • Host based access control

Session 3: INDEX CREATION AND MANAGEMENT

  • Index Types
  • Efficient Usage of PostgreSQL Indexes
  • Index creation
  • B-tree and Hash Indexes
  • Multicolumn Indexes
  • Indexe Based Expressions
  • Partial Indexes
  • Manage and Maintain Indexes

Session 4: TRANSACTIONS AND CONCURRENCY

  • Overview of Transaction Processing in PostgreSQL
  • Levels of Transaction Isolation
  • Atomic Changes
  • Explicit Locking
  • Data Consistency
  • Locking and Indexes
PostgreSQL Administration Training Course

Course Contents - DAY 2

Session 5: ROLE MANAGEMENT

  • Overview of PostgreSQL Roles and Privileges
  • Create a User Defined Role
  • Role Attributes
  • Role Membership
  • Assigning Users to Roles
  • Group and User Role Inheritance
  • Removing Roles
  • Troubleshooting and Understanding Role Access

Session 6: FINE GRAINED ACCESS CONTROL USING GRANT

  • Define Access Privileges with the GRANT Command
  • Grant Access on Database Objects
  • Grant Access on Roles
  • Remove Access Privileges with the REVOKE Command
  • Alter Default Privileges

Session 7: STRUCTURE AND ORGANISATION OF POSTGRESQL DATABASES

  • Overview of PostgreSQL Architecture
  • Configuration Files
  • Physical Storage and File Layout
  • Tablespace Usage
  • PostgreSQL Log Directories
  • PostgreSQL Directory Structures
  • System Catalogs
  • Overview of PostgreSQL Clusters
  • Setup and Manage a Cluster
PostgreSQL Administration Training Course

Course Contents - DAY 3

Session 8: MANAGE TABLESPACES AND DATABASES

  • Overview of PostgreSQL Tablespaces
  • Default PostgreSQL Tablespaces
  • Create a Tablespace using pgAdmin
  • Create a Tablespace in the UNIX Operating System
  • Alter a Tablespace
  • Delete a Tablespace

Session 9: POSTGRESQL SCHEMAS

  • Overview of the Key Concepts of PostgreSQL Schemas
  • Create and Drop a Schema
  • The Public Schema
  • The Schema Search Path
  • Schemas and Privileges
  • The System Catalog Schema

Session 10: POSTGRESQL LOGGING

  • The Principles and Usages of Logging
  • Logging Parameters
  • Error Reporting and Logging
  • What Should be Logged and When
  • CSV Format Log Output

Session 11: CONTROL ACCESS TO THE SERVER

  • Security Labelling
  • Secure Access with OpenSSL
  • Secure TCP Connections with SSL
  • Create Secure SSH Tunnels
  • Client Authentication
PostgreSQL Administration Training Course

Course Contents - DAY 4

Session 12: DATA ENCRYPTION

  • Encryption Options
  • Encrypt Specific Columns with the pgcrypto Module
  • Data Partition Encryption

Session 13: BACKUP AND RECOVERY OF DATABASES

  • Overview of Backup Methods
  • Backup a Database with pg_dump
  • Backup All Databases with pg_dumpall
  • Backup Database Objects
  • Backup databases with pg_Admin
  • Restore using psql
  • Restore using pg_restore
  • Restore a Database with pgAdmin
  • Filesystem backup

Session 14: POINT-IN-TIME RECOVERY (PITR)

  • Carry out a PITR Recovery
  • Overview of PostgreSQL Write-ahead Logs
  • Carry out a PITR Backup
  • Carry out a PITR Recovery

Session 15: THE POSTGRESQL QUERY OPTIMIZER

  • Use EXPLAIN PLAN to optimize Queries and Indexes
  • The Statistics used by the Planner and the ANALYZE Command
  • Query Planner Statistics
  • Control the Planner with Explicit Join Clauses
PostgreSQL Administration Training Course

Course Contents - DAY 5

Session 16: SERVER PERFORMANCE TUNING AND MONITORING

  • Performance Tips
  • Configure Variables for Increased Performance
  • Disable Autocommit
  • Use the COPY command to Bulk Load Data
  • Temporarily drop indexes and Foreign Key Constraints before a Bulk Load

Session 17: MAINTAIN AND MONITOR A DATABASE

  • Routine Maintenance Tasks
  • Routine Vacuuming
  • Monitor Database Activities
  • Monitor Disk Usage

Session 18: CLIENT CONNECTIONS

  • The ODBC Driver for PostgreSQL
  • PostgreSQL JDBC
  • PostgreSQL PHP
  • PostgreSQL Python

Session 19: OVERVIEW OF HIGH AVAILABILITY, LOAD BALANCING AND REPLICATION TOOLS

  • Overview of Different High Availability Solutions
  • Connection Pooling and Load Balancing with pgpool
  • Asynchronous Database Replication with Slony-I

Expenses

Contact the provider for more information and pricing details of this in-house course.

Continuing Studies

Follow on courses: Python Programming 1, PHP Programming

About provider

StayAhead Training - Unix, Linux, MySQL, Java, CSS, HTML, Oracle, RedHat, Linux, Solaris, PHP, Perl, Python, Programming

StayAhead Training established 1992, is recognised as one of the leading independent IT Training specialists in the UK and the first choice provider for many of UK's largest organisations. Located in 7 training centres throughout the UK:  London, Birmingham, Manchester,...


Read more and show all courses with this provider

Customer Outreach Award

customer-outreach-badge-2019

We believe that it should be easy for you to find and compare training courses. 

Our Customer Outreach Award is presented to trusted providers who are excellent at responding to enquiries, making your search quicker, more efficient and easier, too.

Request info

Fill out your details to find out more about PostgreSQL Administration - In-house Training.

  Contact the provider

  Get more information

  Register your interest

Contact info

StayAhead Training

6 Long Lane, Barbican
EC1A 9HF London

 Show phone number
www.stayahead.com

Request Information

Find out more about this PostgreSQL Administration - In-house Training course - simply fill out your details:

Reviews

There are no reviews available yet.

U.K. L&D Report: 2019 - Benchmark Your Workplace Learning Strategy

Are you curious about the L&D strategies of some of the U.K.'s top companies?

Find out what they're up to in findcourses.co.uk's second annual U.K. L&D Report!

uk-ld-report-2019

Learn more