Please enable JavaScript.
Coggle requires JavaScript to display documents.
Introdução ao banco de dados PostgreSQL - Coggle Diagram
Introdução ao banco de dados PostgreSQL
fundamentos de banco de dados
valores brutos, observações documentadas, registros soltos, que são recolhidos e armazenados sem sofrer qualquer tipo de alteração
informações: conjunto de dados relacionados entre si que geram valor, que criam sentidos aos dados
modelo relacional: modelo mais comum que classifica as informações em tabelas com linhas e colunas. as linhas, ou tuplas, são os dados organizacionais, são os valores das tabelas, e as colunas são os atributos destes dados
modelo relacional
tabelas
coisas tangíveis: elementos físicos (carro, produto, animal)
funções: perfis de usuário, status de compra
eventos ou ocorrências: produtos de um pedido, histórico de dados
colunas importantes
chave primária - primary key - pk: conjunto de um ou mais campos que nunca se repetem. identidade da tabela. utilizadas como índice de referência de relacionamento entre as tabelas
chave estrangeira - foreign key - fk: valor de referência a uma pk de outra tabela ou da mesma tabela para criar um relacionamento
sistemas de gerenciamento de banco de dados - sistemas de gestão de banco de dados (SGBD): conjunto de programas ou softwares responsáveis pelo gerenciamento de um banco de dados. programas que facilitam a administração de um banco de dados
PostgreSQL
SGBD opensource
iniciado no departamento de ciência da computação na universidade da califórnia em berkeley em 1986
arquitetura multiprocessos
postmaster: processo principal
childs: cópias do processo principal que gerenciam as conexões que entram e saem do bd
gestão de memória
shared_buffers
wal_buffers
clog_buffers
lock space
processos de escrita em disco
storage
documentação:
http://postgresql.org/docs/manuals
postgresql.conf
definições de todas as configurações do servidor postgresql.
algumas alterações só podem ser feitas com uma reinicialização do banco de dados
configurações de conexão
listen_addresses: tcp/ip das interfaces que serão liberadas para o servidor PostgreSQL
port: porta tcp que o servidor PostgreSQL vai ouvir (5432 é o padrão)
max_connections: número máximo de conexões simultâneas no servidor PostgreSQL
quantidade muito grande de conexões exigirá muitos recursos do banco de dados e do sistema operacional, o ideal é saber a quantidade realmente necessária de conexões
superuser_reserverd_connections: quantidade de conexões (slots) reservadas para conexões ao banco de dados de super usuários
configurações de autenticação
password_encryption: algoritmo de criptografia das senhas dos novos usuários criados no banco de dados
secure socket layer (ssl): cria um canal criptografado entre um servidor web e um navegador (browser) para garantir que todos os dados transmitidos sejam sigilosos e seguros, para habilitá-lo no postgre, é necessário que o mesmo seja compilado com suporte ao ssl
authentication_timeout: tempo limite que um cliente tem para conseguir se conectar ao banco, caso excedido, surgirá um erro de timeout
configurações de memória
shared_buffers: tamanho da memória para operações administrativas (vacuum, index, alter table)
work_mem: tamanho de memória para operações de agrupamento (order by, distinct, merge joins)
maintenance_work_mem: tamanho da memória compartilhada do servidor postgresql para cache/buffer de tabelas, índices e demais relações
os dados que necessitam de um acesso mais rápido o postgre os armazena nesses buffers
recomendado pela documentação do postgre, que o valor padrão desse parâmetro seja 25% de sua memória
pg_hba.conf
controle das autenticações dos usuários no banco de dados (gestão de usuários)
métodos de autenticação
métodos de autenticação (formas de se conectar ao db)
trust: conexção sem requisição de senha
reject: rejeita conexões
md5: criptografia md5
password: senha sem criptografia
gss: generic security service application program interface
sspi: security support provider interface - somente para windows
krb5: kerberos v5
ident: utiliza o usuário do sistema operacional do cliente via ident server
peer: utiliza o usuário do sistema operacional do cliente
ldap: idap server
radius: radius server
cert: autenticação via certificado ssl do cliente
pam: pluggable authentication modules, o usuário precisa estar no banco
exemplo:
https://prnt.sc/tktjwa
pg_ident.conf
mapeia os usuários do sistema operacional com os usuários do banco de dados (a opção ident deve ser utilizada no arquivo pg_hba.conf)
exemplo:
https://prnt.sc/tktnt1
comandos administrativos
menu windows > serviços > postgresql-x64
binários do postgresql:
https://prnt.sc/tkuazs
cluster: coleção de banco de dados que compartilham as mesmas configurações (arquivos de configurações) do postgresql e do sistema operacional (porta, listen_addresses, etc)
importante para conexão
liberar acesso ao cluster em postgresql.conf
liberar acesso ao cluster para o usuário do banco de dados em pg_hba.conf
criar/editar usuários
roles: papéis ou funções
users: usuários em um bd que possuem permissões em comum ou específicas
create role:
https://prnt.sc/tleg1h
https://prnt.sc/tlfpgv
administrando acessos (grant)
privilégios de acesso aos objetos do banco de dados
https://prnt.sc/tlmf4b
codando:
https://prnt.sc/tlmfqi
removendo privilégios
https://prnt.sc/tlmibq
https://prnt.sc/tlmivd
simplificado
https://prnt.sc/tlmiyr
database, schema e objetos
database
banco de dados
grupo de schemas e seus objetos, como tabelas, types, views, funções etc.
seus schemas e objetos não podem ser compartilhados entre si, apenas usuários/roles e configurações do cluster postgresql
comandos básicos:
https://prnt.sc/tm7re1
schemas
grupo de objetos
comandos básicos:
https://prnt.sc/tm7sl4
informações:
select * from information_schema.columns where table_name = 'banco';
objetos
tabelas, views, funções, types, sequences, entre outros, pertencentes aos schemas
tipos de dados:
https://prnt.sc/tm8gxa
truncate:
https://prnt.sc/tmmbyb
idempotência
propriedade que algumas ações/operações possuem possibilitando-as de serem executadas diversas vezes sem alterar o resultado após a aplicação inicial
não geram erros ao serem executados mais de uma vez
insert:
https://prnt.sc/tmm60z
common table expressions - CTE
forma auxiliar de organizar 'statements', ou seja, blocos de códigos, para consultas muito grandes, gerando tabelas temporárias e criando relacionamenos entre elas (dentro dos statements podem ter SELECTs, INSERTs, UPDATEs ou DELETEs
exemplo:
https://prnt.sc/tmvmmv
views
create or replace (criar ou modificar - idempotência)
temporary (temporária, desaparecerá quando a janela ativa for finalizada)
recursive (
https://prnt.sc/tmw3lz
)
i
https://prnt.sc/tmvtbg
exemplo create:
https://prnt.sc/tmvulf
transações
caso haja algum erro durante a transação (erros manuais ou não), o resultado final será um rollback, e então todas as alterações serão desfeitas, caso contrário será um commit (a menos que o rollback seja forçado no código)
exemplo:
https://prnt.sc/tmwpf8
forçando rollback:
https://prnt.sc/tmwpm1
savepoint: caso haja algum erro, não terá perdido todas as transações, apenas as que foram feitas depois do savepoint
exemplo:
https://prnt.sc/tmwqbm
funções
query language funcions (funções escritas em sql)
procedural language functions (funções escritas em pl/pgsql ou pl/py)
internal functions
funções integradas no postgresql avg, sum, min, max, count
c-language functions
user defined functions
funções criadas pelo próprio usuário
podem ser criadas em sql ou em linguagens procedurais
estrutura:
https://prnt.sc/tmx7t3
idempotência:
https://prnt.sc/tmx814
segurança
invoker: permite que a função seja executada com as permissões do usuário que está executando
definer: permite que a função seja executada com as permissões de quem criou a função
boas práticas
called on null input: default, se algum parâmetro for nulo, a função será executada
returns null on null input: se qualquer dos parâmetros for nulo, a função retornará null
comportamento
immutable: não pode alterar o banco de dados (dml), evita selects, pois tabelas podem sofrer alterações
stable: parecido com immutable, porém aceita selects
volatille: comportamento default, aceita todos os cenários
recursos
cost: custo de rows em unidade de cpu
rows: número estimado de linhas que será analisada pelo planner (planejador de consultas)
sql functions
não é possível utilizar transações
exemplo:
https://prnt.sc/tmyj6v
https://prnt.sc/tmyk0m
exemplo plpgsql:
https://prnt.sc/tmykl3
coalesce: se o parâmetro for nulo, retorna o primeiro valor não nulo que for passado