PostgreSQL for Administrators
Duration: 1 day | Price: € 350.00 VAT excluded
20% discount for multiple people from the same company
Prerequisites:
Basic concepts of an RDBMS
Ability to install software and use system services and command-prompt in Windows
The examples presented during the course will be executed on a Windows 10+ machine. The learner must be equipped with a Windows 10 laptop or a version of Ubuntu Linux.
Description
PostgreSQL is an advanced, enterprise-class, fully open-source ORDBMS (Object-Relational Database Management System). It is a relational database that supports both SQL (relational) queries and JSON (non-relational) queries.
PostgreSQL supports numerous features including Multi-Version Concurrency Control (MVCC), point-in-time recovery, granular access controls, tablespaces, asynchronous replication, nested transactions, online/hot backups, a refined query optimizer and planner, and write-ahead logging. PostgreSQL is highly scalable both in terms of the amount of data it can handle and the number of concurrent users it can serve.
In this course, the fundamental knowledge that a PostgreSQL administrator must have will be analyzed.
Contents
Introduction
Installation
Architectural Fundamentals
Creating and Accessing Databases with psql
Security Management
Authentication Types
The pg_hba.conf file
Creating ROLEs to manage Users and Groups
Managing user privileges
Command-line security policies
Schemas
Inheritance
Table Partitioning
Data from External Sources
Periodic Maintenance Activities
Routine Vacuuming
Routine Reindexing
Log File Maintenance
Monitoring
Performance Tips
Index Types
Multi-column indexes
Indexes and ORDER BY
Combining multiple indexes
Expression indexes
Partial indexes
Index-Only Scans and Covering Indexes
How and when is an index used?
Using and understanding EXPLAIN
Statistics used by the Planner
Transactions, MVCC, WAL, and Checkpoints
What is MVCC?
Understanding the implications of the MVCC model on PostgreSQL
MVCC Best Practices
How transactions work in PostgreSQL
Transaction isolation levels
Savepoints
Deadlock
WAL
Backup and Restore
SQL Dump/Restore
File System Level Backup
Backup, Recovery, and Point-in-Time Recovery (PITR)
High Availability
Streaming Replication with Hot Standby
Logical Replication
Beyond the Core
Installing Extensions
How to use some of the most common extensions