5 coisas que todo desenvolvedor deveria saber sobre PostgreSQL

- Instalação mínima em Linux
- Uso do psql (client em modo texto)
- Tipos de dados em funções
- Índices
- Monitoramento com pg_stat_activity e pg_stat_statements

Transcript

1. 5 coisas que todo desenvolvedor deveria saber sobre PostgreSQL por Fábio Telles Rodriguez
2. • Instalação mínima em Linux • Uso do psql • Tipos de dados e funções • Índices • pg_stat_activity • pg_stat_statements Agenda
3. Instalação em Linux (RPM) RESUMO: • Localizar distribuição em https://yum.postgresql.org • Adicionar repositório c/ YUM ou DNF • Instalar pacotes (client, server, contrib) • Criar o cluster ou instância • Subir o servidor
4. Instalação em Linux (RPM) Remover pacotes antigos • Verificar pacotes disponíveis nativamente na sua distribuição • yum search postgres • Verificar pacotes instalados • rpm -qa | grep postgres • Remover pacotes instalados • yum remove postgresxxxx
5. Instalação em Linux (RPM) Instalar o novo repositório https://wiki.postgresql.org/wiki/YUM_Installation • Verificar qual a sua distro: • cat /etc/os-release • Copiar link referente a sua distribuição a partir do site https://yum.postgresql.org • Colar o link em • yum install xxxx EX: yum install https://download.postgresql.org/pub/repos/yum/ reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.n oarch.rpm
8. Instalação em Linux (RPM) Instalar o novo repositório
9. Instalação em Linux (RPM) Instalar pacotes novos do repositório novo • Nomes de pacotes: postgresql12-server.x86_64 • 12: Versão do PostgreSQL • server: tipo de pacote • x86_64: arquitetura do servidor • Pacotes importantes a serem instalados: • postgresqlxx, postgresqlxx-server, postgresqlxx-contrib • Ex: yum install postgresql12 postgresql12-server postgresql12-contrib
10. Instalação em Linux (RPM) Instalar pacotes novos do repositório novo (usando grupos) • Listar grupos disponíveis: yum grouplist • Instalar grupo desejado: yum groupinstall 'PostgreSQL Database Server 12 PGDG'
11. Instalação em Linux (RPM) Criar instância • Fazer login com o usuário postgres do SO: su - postgres • Criar instância no local padrão: /usr/pgsql-12/bin/initdb
12. Instalação em Linux (RPM) Criar instância
13. Instalação em Linux (RPM) Qual é o local padrão? • Verifique a variável de ambiente $PGDATA em .bash_profile cat ~/.bash_profile
14. Instalação em Linux (RPM) Opções do initdb https://www.postgresql.org/docs/current/app-initdb.html • -D, --pgdata Local para criação do cluster • -E, --encoding Codificação de caracteres padrão • --locale Localização
15. Instalação em Linux (RPM) Colocando o banco de dados no ar https://www.postgresql.org/docs/current/app-pg-ctl.html • Subir o banco: /usr/pgsql-12/bin/pg_ctl start • Baixar o banco: /usr/pgsql-12/bin/pg_ctl start • Reiniciar: /usr/pgsql-12/bin/pg_ctl restart
16. Instalação em Linux (RPM) Usando o banco de dados como um serviço (COMO ROOT) • Habilitar o serviço: systemctl enable postgresql-12.service • Subir o banco: systemctl start postgresql-12.service • Baixar o banco: systemctl stop postgresql-12.service
17. psql Porquê utilizar o psql? • Padrão em toda instalação • Leve, rápido e confiável • Disponível mesmo que você não tenha uma interface gráfica • Flexível, fácil de usar e ótimo para automatizar tarefas
18. psql Opções mais comuns para chamar o psql https://www.postgresql.org/docs/current/app-psql.html • -U usuário (padrão é o usuário do SO) • -h ip remoto (padrão é conexão local) • -p porta (padrão é a 5432) • nome_base (padrão é o nome do usuário) • -f nome_arq.sql executa o arquivo e sai • -c ‘SELECT now()’; executa o comando e sai • -l lista bases existentes no cluster e sai
19. psql Exemplos: • Chama o psql com no servidor local, usando o mesmo usuário do SO e base com mesmo nome do usuário psql • Chama o psql no servidor 192.168.0.1, com usuário app, na base meu_banco psql -h 192.168.0.1 -U app meu_banco
20. psql Modo interativo • Use o auto complete para completar comandos e nomes de objetos; • Use setas para voltar comandos anteriores; • Use o ‘;’ como terminador de comandos SQL; • Use o \ para comandos específicos do psql;
21. psql Comandos \ • \? lista todas as opções de \ disponíveis • \l lista bancos de dados instalados • \p lista privilégios em objetos • \x alterna modo de saída em coluna e linha • \h COMANDO lista sintaxe do comando SQL • \c base_x conecta na base_x • \i arquivo.sql executa arquivo.sql • \o saida.txt grava saída de comandos em saida.txt
22. psql Comandos \d • \d lista propriedades de objetos • \dt lista todas tabelas • \di lista todos índices • \df lista todas funções • \dv lista todas visões • \ds lista todas sequencias • \du lista usuários e roles • \dx lista todas extensões instaladas
23. psql Comandos \d com modificadores • Lista todas as tabelas: \dt • Lista uma tabela específica \dt minha_tabela • Lista tabelas com nome que iniciam com user_ \dt user_* • Lista tabelas do esquema financeiro: \dt financeiro.* • Lista propriedades adicionais das tabelas \dt+
24. psql Outros comandos client importantes: https://www.postgresql.org/docs/current/reference-client.html • pg_dumpall (cria backup lógico de todas bases em modo texto) • pg_dump (cria backup lógico de uma base em vários formatos • pg_restore (restaura backup lógico nos formatos custom, tar e directory) • pg_basebackup (cria backup físico do cluster) • createdb (cria bases novas) • createuser (cria usuários novos) • vacuumdb (executa rotinas de manutenção vacuum e analyze)
25. Tipos de dados e funções SQL é uma linguagem fortemente tipada • O armazenamento eficiente depende do tipo de dados correto • Tipos de dados limitam a chance de entrada incorreta de dados • Tipos de dados permitem indexação inteligente dos dados • Tipos de dados permitem o uso de funções específicas para cada tipo
26. Tipos de dados e funções Tipos de dados no PostgreSQL https://www.postgresql.org/docs/current/datatype.html • Numéricos: smallint, integer, bigint, numeric, real, double precision • Texto: varchar, char, text • binário: bytea, oid (exige o uso de função específica) • data/hora: timestamp, timestamp with time zone, date, time, interval
27. Tipos de dados e funções Tipos de dados no PostgreSQL • Boleano: boolean • Enumerados: enum • Geométricos: point, line, lseg, box, path, polygon, circle • Rede: cidr, inet, macaddr, macaddr8 • mapa de bits: bit, bit varing • Busca textual: tsvector, tsquery • JSON: json, jsonb • Intervalos: int4range, int8range, numrange, tsrange, tstzrange, daterange • UUID • XML • Arrays • Tipos compostos • Domínios • Tipos internos
28. Tipos de dados e funções Funções https://www.postgresql.org/docs/current/functions.html • Operadores lógicos: AND, OR, NOT • Comparação: <, >, <=, >=, =, <>, != • Operadores matemáticos: +, -, *, /, %, |/, ||/, !, ||, @ • Bits: &, |, #, ~, <<, >> • Funções matemáticas: abs, cbrt, ceil, ceiling, degrees, div, exp, floor, ln, log, log10, mod, pi, power, radians, round, scale, sign, sqrt, trunc, width_bucket • Números aleatórios: random, setseed • Trigonometria: acos, asin, atan, atan2, cos, cot, sin, tan • Hiperbólicos: sinh, cosh, tanh, asinh, acosh, atanh
29. Tipos de dados e funções Funções • Caracteres: ||, bit_length, char_length, lower, upper, octet_length, overlay, position, substring, trim, ascii, btrim, chr, concat, convert, convert_from, decode, encode, format, initcap, left, length, lpad, ltrim, rtrim, md5, parse_ident, quote_ident, quote_literal, quote_nullable, repeat, replace, reverse, right, rpad, split_part, strpos, starts_with, to_ascii, to_hex, translate • Expressão regular: regexp_match, regexp_matches, regexp_replace, regexp_split_to_array, regexp_split_to_table • Busca: LIKE, ILIKE, SIMILAR, ~, ~*, !~, !~* • Formatação: format, to_char, to_date, to_number, to_timestamp • Data/hora: +, -, *, /, extract, date_part, date_trunc, at, age, clock_timestamp, current_date, current_timestamp, current_time, isinfinite, justify_days, justify_hours, justify_interval, localtime, localtimestamp, make_date, make_interval, make_time, make_timestamp, make_timestamptz, now(), statement_timestamp, timeofday, transaction_timestamp
30. Tipos de dados e funções Funções • Operadores geométricos: +, -, *, / #, @-@, @@, ##, <->, &&, <<, >>, &<, &>, <<|, |>>, &<|, |&>, <^, >^, ?#, ?-, ?|, ?-|, ?-||, @>, <@, ~= • Funções geométricas: area, center, diameter, height, isclosed, isopen, length, npoint, pclose, popen, radius, with • Operadores de rede: <, <=, = >=, >, <>, <<, <<=, >>, >>=, &&, ~, &, |, +, - • Funções de rede: abbrev, broadcast, family, host, hostmask, masklen, netmask, network, set_masklen, text, inet_same_family, inet_merge, trunc • Operadores JSON: ->, ->>, #>, #>> • Operadores JSONB: @>, <@, ?, ?|, ?&, ||, -, #-, @?, @@ • Funções p/ criação JSON: to_json, array_to_json, row_to_json, json_build_array, json_build_object, json_obsect • Funções p/ processar JSON: json_array_lenght, json_each, json_each_text, json_extract_path, json_extract_path_text, json_object_keys, json_populate_record, json_populate_recordset, json_array_elements, json_array_elements_text, json_typeof, json_to_record, json_to_recordset, json_strip_nulls, jsonb_set, jsonb_insert, jsonb_pretty, json_path_exists, jsonb_path_match, jsonb_path_query, jsonb_path_query_array, jsonb_path_query_first • Operadores ARRAY: =, <>, <, >, <=, >=, @>, <@, &&, || • Funções ARRAY: array_append, array_cat, array_ndims, array_dims, array_fill, array_length, array_lower, array_position, array_prepend, array_remove, array_replace, array_to_string, array_upper, cardinality, string_to_array, unnest
31. Índices Tipos de índices no PostgreSQL https://www.postgresql.org/docs/current/indexes.html • B-trees: <, <=, =, >=, >, LIKE ‘%...’, ~ ‘^...’ • Hash: = • GiST: <<, &<, &>, >>, <<, <<|, &<|, |&>, |>>, @>, <@, ~=, && • SPGist: <<, >>, ~=, <@, <^, >^ • GIN: <@, @>, =, && • BRIN: <, <=, =, >=, >
32. Índices Propriedades de índices • Índices compostos (múltiplas colunas) • NULL FIRST, NULL LAST • UNIQUE • Funções em índices • Índices parciais (WHERE …) • INDEX ONLY + INCLUDE • opclass (text_pattern_ops, varchar_pattern_ops, bpchar_pattern_ops, etc) • COLLATE
33. Índices Verificando o uso: • Atualizar estatísticas: ANALYZE … • pg_stat_user_indexes • pg_statio_user_indexes
34. pg_stat_activity Colunas mais comuns: https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW • pid • datname: nome da base • usename: usuário • application_name: identificador de cliente • client_addr: IP do cliente • backend_start: início da conexão • xact_start: início da última transação • query_start: início da última consulta • state_change: início data de operação dentro de uma consulta
35. pg_stat_activity Colunas mais comuns: https://www.postgresql.org/docs/current/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW • wait_event_type: tipo evento de espera (quando houver) • wait_event: nome do evento de espera • state: active, idle, idle_in_transaction • query: última consulta executada, ou consulta atual • backend_type: tipo de processo (‘client backend’ e outros processos server)
36. pg_stat_statements Histórico das consultas que mais consomem recursos https://www.postgresql.org/docs/current/pgstatstatements.html • query • calls • total_time • min_time • max_time • mean_time • rows