Visão Geral

Visão geral do curso de administração do PostgreSQL

Este curso de administração do PostgreSQL cobre administração, manutenção, segurança e ajuste de desempenho dos bancos de dados PostgreSQL versão 10, 11 e 12.

Exercícios e exemplos são usados ​​ao longo do curso para fornecer experiência prática com as técnicas abordadas.

Objetivos do Curso

Este curso visa fornecer ao delegado o conhecimento para ser capaz de instalar, administrar, manter, fazer backup, recuperar e ajustar um banco de dados PostgreSQL.

Objetivo

Após a conclusão deste Curso PostgreSQL Administration você será capaz de:

• Instale PostgreSQL
• Crie um banco de dados
• Gerenciar funções e privilégios
• Gerenciar esquemas
• Tratamento de transações e simultaneidade
• Examine estatísticas e tabelas de sistema
• Gerenciar tablespaces
• Entenda o relatório e registro de erros
• Realize a manutenção periódica com autovacuum
• Faça backup e recupere bancos de dados
• Crie índices para melhorar o acesso à tabela
• Otimize consultas e índices
• Defina as configurações para aumentar o desempenho
• Preencher um novo banco de dados de forma eficiente

Publico Alvo

Este curso de administração do PostgreSQL foi desenvolvido para administradores de banco de dados profissionais que precisam obter uma compreensão dos recursos e funcionalidades que podem ser usados ​​para gerenciar bancos de dados PostgreSQL 10, 11 e 12. Este curso também é adequado para usuários de bancos de dados PostgreSQL 9.

Pre-Requisitos

Este curso pressupõe um bom conhecimento prático de SQL e, especificamente, das instruções SELECT, INSERT, UPDATE e CREATE TABLE. Este conhecimento pode ser obtido através da frequência do curso de pré-requisitos SQL para PostgreSQL .

Este curso é executado em um sistema operacional Linux, portanto, é necessário um bom conhecimento prático de UNIX / Linux. 

Informações Gerais

• Carga horaria, 32h
• Se noturno o curso e ministrado de segunda-feira a sexta-feira, das 19h às 23h, total de 8 noites,
• Se aos sábados o curso e ministrado de 09h às 18h, total de 4 sábados

Formato de Entrega:

• On-line ao vivo em tempo real na presença de um consultor/instrutor DBA
  
• Presencial em sala de aula
• In-company

Materiais

Conteúdo Programatico

Course Introduction

1. Administration and Course Materials
2. Course Structure and Agenda
3. Delegate and Trainer Introductions

INSTALLATION AND CONFIGURATION OF POSTGRESQL

1. PostgreSQL Version Numbers
2. PostgreSQL Configuration Recommendations
3. Single Cluster and Database per Server
4. File System Layouts
5. Install PostgreSQL
6. Install PostgreSQL on Linux/UNIX
7. Post-Installation Setup
8. Create a Database Cluster
9. Basic Server Configuration
10. Account Management

POSTGRESQL ARCHITECTURE

1. Overview of PostgreSQL Architecture
2. The PostgreSQL Instance
3. PostgreSQL Processes
4. How Data is Processed by PostgreSQL
5. PostgreSQL Directory Structure
6. Manage a PostgreSQL Instance
7. Automate the Startup of PostgreSQL at Boot Time
8. The PostgreSQL Configuration Files
9. Multiversion Concurrency Control
10. The Vacuum utility
11. PostgreSQL WAL

POSTGRESQL CLIENT APPLICATIONS

1. Overview of PostgreSQL Client Applications
2. The PostgreSQL Interactive Client Terminal - psql
3. The pgAdmin Tool
4. The pgAgent Tool
5. Client and host based access control
6. Client Connection Problems
7. Authentication Failures
8. Server Startup Failures

CREATE AND CONFIGURE A DATABASE

1. Create a New Database using the CREATE DATABASE Command
2. Create a New Database using pgAdmin
3. Start and Stop the Database Server
4. Drop a Database
5. Copy a Database
6. List Databases
7. Obtain Database Object Sizes
8. Initialise a Cluster

POSTGRESQL SCHEMAS

1. Overview of the Key Concepts of PostgreSQL Schemas
2. Create and Drop a Schema
3. The Public Schema
4. The Schema Search Path
5. Schemas and Privileges
6. The System Catalog Schema
7. Overview of the Information Schema

ROLE MANAGEMENT

1. Overview of PostgreSQL Roles and Privileges
2. Create a User Defined Role
3. Role Attributes
4. Role Membership
5. Assigning Users to Roles
6. Group and User Role Inheritance
7. Removing Roles
8. Troubleshooting and Understanding Role Access

FINE GRAINED ACCESS CONTROL USING GRANT

1. Control Database Level Permissions
2. Control Schema Level Permissions
3. Grant Table Level Permissions
4. Define Access Privileges with the GRANT Command
5. Remove Access Privileges with the REVOKE Command
6. Manage Column Rights

INDEX CREATION AND MANAGEMENT

1. Index Overview
2. The CREATE INDEX Command
3. Index Types
4. Efficient Usage of PostgreSQL Indexes
5. Index creation
6. B-tree and Hash Indexes
7. Single Column Indexes
8. Multicolumn Indexes
9. Index Based Expressions
10. Partial Indexes
11. List Indexes
12. Manage and Maintain Indexes
13. When Indexes Should be Avoided

TRANSACTIONS AND CONCURRENCY

1. Overview of Transaction Processing in PostgreSQL
2. Transaction Properties
3. Locking Concepts
4. Levels of Transaction Isolation
5. Atomic Changes
6. Explicit and Implicit Table Locking
7. Transaction Control
8. Lock Management Parameters
9. Locking and Indexes

POSTGRESQL DATABASES STRUCTURE

1. The PostgreSQL Configuration Files
2. Relocate the Configuration Files
3. Physical Storage and File Layout
4. Overview of Tablespace Usage
5. Table and Row Storage
6. Column Limitations
7. Free Space Map
8. The Visability Map
9. Index Storage

MANAGE TABLESPACES

1. Overview of PostgreSQL Tablespaces
2. Default PostgreSQL Tablespaces
3. Create a Tablespace using SQL Commands and pgAdmin
4. Create a Tablespace in the UNIX Operating System
5. Alter a Tablespace
6. Delete a Tablespace
7. Session 12: POSTGRESQL LOGGING
8. The Principles and Usages of Logging
9. PostgreSQL Log Destinations
10. Logging Parameters
11. Error Reporting and Logging
12. What Should be Logged and When
13. CSV Format Log Output

POSTGRESQL EXTENSIONS

1. Install the PostgreSQL Contrib Module
2. List the Available Extensions
3. Add an Extension to the postgresql.conf File
4. Create an Extension in a Database
5. Drop an Extension in a Database

BACKUP AND RECOVERY OF DATABASES

1. Overview of Backup Methods
2. Export and Import Operations with COPY
3. Backup a Database with Operating System Commands
4. Backup a Database with pg_dump
5. Backup User Credentials
6. Backup All Databases with pg_dumpall
7. Backup Database Object Definitions
8. Backup databases with pg_Admin
9. Overview of Database Restore
10. Restore using psql
11. Restore using pg_restore
12. Restore a Database with pgAdmin
13. Filesystem Backup

POINT-IN-TIME RECOVERY (PITR)

1. Overview of PITR
2. Continuous Archiving
3. Overview of PostgreSQL Write-ahead Logs
4. List the Transaction Logs
5. Checkpoints
6. Transaction Log Optimisation
7. Setup PITR
8. Creat an Initial Base Backup
9. Exclusive and Non-Exclusive Low Level Backups
10. Test Transaction Log Archiving
11. Replay Transaction Logs
12. Archive Recovery Settings
13. Timelines
14. Archive Target Settings Parameters
15. Locating the Correct Timestamp
16. Clean up the Transaction Logs

THE POSTGRESQL QUERY OPTIMIZER

1. Query Optimization
2. Optimization Operations
3. Optimization Decisions
4. Join Methods
5. Statement Transformation
6. Use EXPLAIN to optimize Queries and Indexes
7. Execution Plans
8. The Statistics used by the Planner and the ANALYZE Command
9. Query Planner Statistics
10. Control the Planner with Explicit Join Clauses
11. Detect Slow Queries
12. Parameters Affecting Optimization

POPULATE A DATABASE EFFICIENTLY

1. DISABLE autocommit
2. Configure Variables for Increased PerformanceUse the COPY Command to Bulk Load Data
3. Drop Indexes and Foreign Keys Temporarily
4. Use the COPY command to Bulk Load Data
5. Temporarily drop indexes and Foreign Key Constraints before a Bulk Load
6. Configure Variables for Increased Performance
7. Temporarily Disable WAL Archival and Streaming Replication
8. Use pg_dump Efficiently

SERVER PERFORMANCE TUNING AND MONITORING

1. Optimize Storage and Manage Cleanup with VACUUM
2. Monitor Database Activities
3. Statistics Collection
4. Set postgres.conf Cache Parameters