Upgrade to Pro — share decks privately, control downloads, hide ads and more …

5 coisas que todo desenvolvedor deveria saber sobre PostgreSQL

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

Fábio Telles Rodriguez

December 16, 2019
Tweet

More Decks by Fábio Telles Rodriguez

Other Decks in Programming

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: +, -, *, / #, @[email protected], @@, ##, <->, &&, <<, >>, &<, &>, <<|, |>>, &<|, |&>, <^, >^, ?#, ?-, ?|, ?-|,
    ?-||, @>, <@, ~=
    • 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