Visão Geral PostgreSQL 12

Prévia do material em texto

Dominando
PostgreSQL 12
Visão Geral
PostgreSQL
O Que tem de Novo
Melhorias na Documentação
Rebuild Concorrente de Índices
Armazenar Resultado de Calculos
Similar ao Generated Always do
Oracle
JSONPATH
Backup e Recovery
O Que é e Por Que PostgreSQL?
Capacidade
Max DB Size Até 32 TB
Max de Colunas por Tabela 1600
Indices com Até 32 Colunas Cada
A.C.I.D
Atomicidade
Consistência
Isolamento
Durabilidade
Multiversionamento Leitores Não Bloqueiam Escritores
Suporte a Várias Linguagens
Suporte a Extensões
Origem
Ingres: 1986
Postgres 4.2 1994
Objeto Relacional
De�nindo Alguns Termos
Importantes
Cluster
Postmaster
Database
WALs
Transações e Locks O Que é?
Instalando o
PostgreSQL
O Que Instalar?
Server
Client
Contrib Package
Documentação
PL/Perl, PL/Python e PL/Tcl
Instalando o PostgreSQL no
CentOS/RH/Fedora
Vamos usar o Método RPM + yum Repositorio
2
Inicializar o DB Directory
Habilitar o PostgreSQL para Start
Automatico
Conectividade no
PostgreSQL
Firewall Porta 5432
Arquivos de Con�guração
postgresql.conf listen_addresses
Host Server IP
Restart Service
pg_hba.conf Estrutura
connection-type
local
host
hostssl
database Quais Bancos
role username role
remote-machine hostname | IP | Sub-rede
Método de Autenticação
scam-sha-256
md5 (antigo)
reject
trust
Instance-Level Security
Visibilidade do Usuário Todos os Databases
Roles e Usuários São a Mesma
Coisa
Quem Cria um banco de Dados
Por Padrão é o Owner do Banco
de Dados
Fazendo a Primeira Conexão
De Onde Aceitar as Conexões
Alterar o Modo de Encriptação de
Senhas
Alterar a Senha do Usuário
Restartar o Postgresql-12
Fazer a Conexão com PgAdmin
Conhecendo Melhor
seu Seu PostgreSQL
É Sempre Free?
Qual Minha Server Version?
Antes do V10 X.Y.Z PostgreSQL 9.4.0
Versionamento
1
Como Veri�car na Prática
Query SQL
Shell
Quanto Tempo Online?

Onde Estão Meus Arquivos
Plataforma
CentOS/RH/Fedora
/var/lib/pgsql/
/data
Debian/Ubuntu
/var/lib/postgresql/
/main
Windows
C:\Program Files\PostgreSQL\
\data
Como Consultar?
Debian/Ubuntu
pg_lsclusters

Release
Porta
Status: Online/O�ine
Diretorio de Dados
Arquivo de Log
Layout é Similar ao OFA $ORACLE_BASE+ORACLE_HOME
RedHat/Fedora/CentOS
Cuidado
Não Altere nada que não seja
*.conf ou *.cfg
Variáveis

Onde Ficam as Mensagens e
Warnings
1
Debian/Ubuntu /var/log/postgresql
CentOS/RH/Fedora /var/lib/pgsql/12/data/log
Windows
O Que São?

Parâmetros Principais
1
log_destination
csvlog
stderr
log_min_messages
log_rotation_age/log_rotation_size
log_directory
Onde se Con�gura postgresql.confQual o ID do seu Database
Server
CentOS/RH/Fedora

DEBIAN/UBUNTU

É como Acessar o Control�le Nunca Edite o Control�le
Quais os Bancos de Dados
Tenho no Meu Servidor
DatabaseServer = Cluster
Como Consultar
psql -l
select datname from
pg_database;Inicialmente Há 3 Databases
Default

Não Delete o Database
postgres

Extended output com "\x"

Quais Módulos e Extensões
eu Tenho
1
Existem Objetos Padrão
Existem os Módulos/Extensões Onde �cam
CENTOS/RH/Fedora

DEBIAN/UBUNTU
Como Consultar

Qual o Tamanho dos Meus
Bancos de Dados
Current Database

Todos Databases
Alternando entre meus
Bancos de Dados
Administrando
Usuários,  Roles &
Schemas
Criando Usuários
1
Há Basicamente 2 Formas
SQL Language
createuser script Modo Interativo

O que é? Global Rights
Pode ou Não Criar Bancos de
Dados
Se é um Super User ou Não
User Groups Equivalente a Role no Oracle
Listando Usuários

Schemas
2
O Que é
Criando Schemas Listando Schemas
Em Qual Schema eu Estou
Criando Objetos?
Mudando o Current_Schema
Equivalente ao ALTER SESSION
SET CURRENT SCHEMA
O Schema Public e a Role Public
Por que Revogar Privilégios da
Role Public?
Quero user Read Only
Não quero que ele Conecte Por
Padrão
O Conceito de Search  Path
Grant de Connect Database?
Con�gurando Meu
PostgreSQL Server
Onde Encontrar as
Con�gurações?
pg_settings
default
�le
show
show all;
show ;
Con�gurando Parâmetros
para Grupos de Usuários

Atualizando Parâmetros
Arquivos
postgresql.conf
pg_hba.conf
pg_ident.conf
Como Reler os Arquivos de
Con�guração
CentOS/RH/Fedora

Debian/Ubuntu
Checklist Básico de
Con�guração
Parâmetros no Nível de
Sessão/Transação
Planejando e Criando
Bancos de Dados
Criando Databases
1
Tablespaces
1 
Database Cluster Level
Somente Super-Users Podem
Criar
Criando Database Simples
Criando Database com Requisitos
Especí�cos
Planejamento
Storage/Armazenamento
Tipos de Filesystem e LayoutSeparação de Arquivos Por Tipo
Write-Ahead Log�les (Vulgo
Transaction Log)
Temp Files
Tablespaces Separação por Schema vs
Database
Con�gurações de Hardware
Locale e Time Zone
2
Order By
Funções de Conversão
Indexação
Identi�car Aplicações e Clientspg_hba.conf
Monitoramento
OmniDB e pgAdmin
Outras Ferramentas

Backup
Full vs Parcial
Dados ou Apenas DDL
Point-in Time Recovery?
RTO
RPO
Retenção
Regras de Negocios
Armazenamento
Periodicidade
Performance Recuperação
Armazenamento
Físico ou Lógico
HA
Replicação
2
Perda de Dados
Sincrona
Assíncrona
O que é
Tipo de Replicação
Adicionando e
Con�gurando
Módulos
Adicionando um Módulo
Externo
Usando um Módulo
Instalado
Gerenciando Extensões
Instaladas
Administrando seu
PostgreSQL Server
O que é postmaster?
Start do Database Server
Manual
Stop Seguro do Database
Server
Stop Emergencial do
Database Server
Relendo Arquivos de
Con�guração
Restart Rápido do Server
Bloqueio de Novas Conexões
Restringindo o Número de
Conexões por Usuário
Desconectando Usuários
Múltiplos Database Servers
no Mesmo Sistema
Con�gurando Pools de
Conexão
Acesso em Múltiplos Servers
na Mesma Porta e Host
Vamos Trabalhar com
Dados e Tabelas?
Criando Tabelas
1
Mostrando Estrutura de
Tabela

Listando minhas Tabelas

Adicionando e Removendo
Colunas
Requer Rebuild de Indexes
Check e Not Null  Forçam Scan
Cuidado com as Foreign Keys
Removendo Colunas
O Espaço não é Liberado
Imediatamente
Alterando o Tipo de DadosSET DATA TYPE Modi�cando o Tipo de Dados
de uma Coluna
ViewsCriando Views
Materialized View
Os Dados São Persistentes
Cuidado com Espaço em Disco
Mas é muito Mais Rápido
Movendo Objetos entre
Schemas
Os Indices e Triggers Também São
Movidos
Tipo de Dados Serial Também é
Movido
Vamos Melhorar a
Segurança do meu
Server
Backup & Recovery
Monitorando e
Operando o
PostgreSQL Server
Real Time com pgAdmin
Veri�cando se um User está
Conectado
Veri�cando se um servidor
está conectado
Executando uma consulta
Repetidas Vezes
Quais Queries Estão
Executando
Veri�cando Queries Ativas ou
Bloqueadas
Quem está bloqueando
Quem:?
Eliminando uma Sessão
Encontre Sessões Orfãs
Quem Está Usando uma
Tabela Especí�ca
Quando Foi o Ultimo Acesso
a uma Tabela
Quanto Estou usando de
Temp Space
Porque Queries Estão
Lentas?
Investigando Bugs
Analisando Performance de
Consultas em Real Time
Analisando Mensagens de
Erro
Bloqueando Usuário em
Janelas especí�cas
Arquitetura do
PostgreSQL Server
Principais Features
Portável
Escrito em C
POSIX Compliance
Con�ável
ACID
Suporta Savepoints
WAL Files Architecture
Escalável
Particionamento
Suporta Tablespaces
Concorrência e
Multiversionamento Escritores não Bloqueiam Leitores
Seguro
Acesso Baseado em Host pg_hba.conf
Permissão no Nível de Objetos
Suporta Auditoria e SSL
Disponibilidade
Replicação com Load Balance
H.A Usando Replicação
Suporte a Linguagem Procedural
PL/pgSQL
PL/Perl
PL/PHP
PL/TCL
Suporta Hot Backups
Limitações Nominais
1
Database Size Ilimitados
Table Max Size 32 TB BLOCKSZ = 8Kb
Maximum Row Size
Max Field Size
Maximo de Linhas por Table
Maximo de Colunas Por Tabela
Maximo de Indices por Tabela
Colunas por Indice 32
Database Server Architecture
Memória e Processos de
Background
1
Veri�cando Processos no S.O
ps -ef | grep postgres
Usando psql
Estrutura de Diretório
1
Porque PostgreSQL
Open Source Redução de Custos
ORDBMS Robusto e Performático
Linguagem SQL-ANSI
Mantendo a Saúde do
Seu Banco de Dados
Evitando Problemas de
Performance Bloat Tables
Implementando Proatividade
Perguntas Importantes
Recursos do Servidor
Janelas de Manutenção
Tabelas Críticas para a Aplicação
VACUUM ou AUTOVACUUM
Parâmetros
Principais
autovacuum
track_counts
Outros Parâmetros Tunáveis
2 
Como Ajustar para Tabelas
Especí�cas
Con�gurando o autovacuum

Implementando
Regras de Negócio
com Constraints
Essencialmente São Regras
Limitam ou Restringem e Até
Proibem
Check
Not Null
Unique
Pode Aplicar 1 ou Mais Colunas
Permite Mais de 1 Por tabela
Primary Keys
Valor: Não Aceita Nulos
Valores tem que ser Únicos
Pode ser Pai no Relacionamento
Pode ter mais de uma Coluna
Somente 1 Por Tabela
Especi�cam RelacionamentosForeign Keys
Pode ser Feito na Camada de
Application
Administrando
Tablespaces
Adicionando Tablespaces
I/O Load Balance
Tablespaces Separadas por banco
de Dados
Pre-Requisitos
Device
Permissões: postgres
Criar Ponto de Montagem ex:
/data/tbs1
Criar uma Partição
Formatamos com o Tipo de
Filesystem das Políticas
Dropando uma Tablespace
Movendo Objetos entre
Tablespaces
Acessando Bancos
Remotos com
DBLINKS
Pre-Reqs
Contrib Extension
Con�guração Arquivos
postgresql.conf listen_address
pg_hba.conf
Criar Extensão no Banco de Dados
Criar o Server
Criar o Mapeamento de Usuario
Exportando e
Importando Dados
Exportando dados de Tabela
pg_dump
copy
Instalando no
DEBIAN/Ubuntu
