Visão Geral

Este Curso PostgreSQL Administration for the Amazon RDS, ensina DBAs experientes a administrar o PostgreSQL na plataforma de nuvem Amazon RDS.

Objetivo

Após realizar este você será capaz de:

• Entenda a arquitetura PostgreSQL na plataforma Amazon RDS
• Cluster e escalabilidade do PostgreSQL no Amazon RDS
• Criar e gerenciar bancos de dados e usuários
• PostgreSQL seguro
• Configurar PostgreSQL
• Domine o psql e use-o para manipular objetos de banco de dados e copiar/importar dados
• Faça backup e restaure seus dados PostgreSQL
• Replique seus dados PostgreSQL
• Entenda os detalhes internos do PostgreSQL
• Registre, monitore, consulte e otimize o PostgreSQL usando as ferramentas certas
• Execute atualizações principais e secundárias do PostgreSQL

Pre-Requisitos

Todos os participantes do treinamento PostgreSQL devem ter experiência substancial como administrador de uma plataforma de banco de dados moderna, como Oracle, SQL Server ou DB2.

Materiais

Conteúdo Programatico

PostgreSQL Introduction & Architecture

1. Introduction and History
2. Getting support for PostgreSQL
3. The PostgreSQL community
4. Getting started with PostgreSQL
5. Major differences between self-managed PostgreSQL and RDS

The PostgreSQL Cluster

1. High-level PostgreSQL architecture
2. Definition of a cluster
3. Review PostgreSQL executables
4. Cluster configuration on RDS
5. RDS Parameter groups

Creating Databases and Users

1. Templates
2. Creating PostgreSQL Databases
3. Users, Groups, and Roles
4. PostgreSQL role inheritance

Security

1. Discussion of encryption at rest
2. Discussion of encryption in transit
3. Basic SSL client setup
4. PostgreSQL and TDE

PostgreSQL Configuration

1. The postgresql.conf file
2. Making persistent configuration changes
3. Using ALTER SYSTEM
4. RDS Parameter groups

Introduction to psql

1. Connecting the psql client
2. psql command line options
3. psql meta commands
4. psql input and output

PostgreSQL Database Objects

1. PostgreSQL data type overview
2. Schemas
3. Configuring User-Schema Relationships
4. Schema search path
5. Views and Materialized Views
6. Notes on COMMIT
7. Notes on case sensitivity

Copying and Importing Data

1. The SQL COPY utility
2. Using the \copy PSQL meta command
3. Exporting data to AWS S3
4. Transporting data between instances (pg_transport)

PostgreSQL Backup and Recovery

1. PostgreSQL logical backup
2. Using pg_dump
3. Using pg_dumpall
4. Recovery from logical backups
5. Using pg_restore

PostgreSQL Streaming Replication

1. Replication in RDS
2. Setting up RDS replicas

PostgreSQL Internals – System Catalogs and Cluster Settings

1. Overview of system catalogs
2. Overview of system views
3. Querying system catalogs and views

Logging, Monitoring and Query Performance Tools

1. System log configuration and management
2. Logging SQL queries
3. Logging security events
4. Publishing RDS logs
5. Using pg_stat_statements extension for query tuning
6. PostgreSQL stats views
7. Query analysis with EXPLAIN
8. Visualizing EXPLAIN output

Upgrading PostgreSQL

1. Minor version upgrades
2. Major version upgrades
3. Discussion of data conversion
4. Running a major upgrade in RDS