Slide 1

Slide 1 text

5 coisas que todo desenvolvedor deveria saber sobre PostgreSQL por Fábio Telles Rodriguez

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

No content

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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'

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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;

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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+

Slide 24

Slide 24 text

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)

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

Í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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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)

Slide 36

Slide 36 text

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