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
   

   View Slide

2. ● Instalação mínima em Linux
   ● Uso do psql
   ● Tipos de dados e funções
   ● Índices
   ● pg_stat_activity
   ● pg_stat_statements
   Agenda
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
   

   View Slide

6. View Slide

7. View Slide

8. Instalação em Linux (RPM)
   Instalar o novo repositório
   

   View Slide

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
   

   View Slide

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'
    

    View Slide

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
    

    View Slide

12. Instalação em Linux (RPM)
    Criar instância
    

    View Slide

13. Instalação em Linux (RPM)
    Qual é o local padrão?
    • Verifique a variável de ambiente $PGDATA em .bash_profile
    cat ~/.bash_profile
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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;
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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+
    

    View Slide

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)
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

31. Índices
    Tipos de índices no PostgreSQL
    https://www.postgresql.org/docs/current/indexes.html
    • B-trees: <, <=, =, >=, >, LIKE ‘%...’, ~ ‘^...’
    • Hash: =
    • GiST: <<, &<, &>, >>, <<, <<|, &<|, |&>, |>>, @>, <@, ~=, &&
    • SPGist: <<, >>, ~=, <@, <^, >^
    • GIN: <@, @>, =, &&
    • BRIN: <, <=, =, >=, >
    

    View Slide

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
    

    View Slide

33. Índices
    Verificando o uso:
    • Atualizar estatísticas:
    ANALYZE …
    • pg_stat_user_indexes
    • pg_statio_user_indexes
    

    View Slide

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
    

    View Slide

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)
    

    View Slide

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
    

    View Slide