Implantação com Docker

Obter a imagem oficial

sudo docker pull postgres

Executar o contêiner

Defina as variáveis de ambeinte para configurar o banco de dados:

sudo docker run --name containerdb -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=secret123 -p 5432:5432 -d postgres

sudo docker ps

Acessar o terminal do PostgreSQL

sudo docker exec -it containerdb psql -U admin -d postgres

Comandos básicos com psql

CREATE TABLE exemplo (codigo SERIAL PRIMARY KEY, descricao VARCHAR(50));

\dt

Gerneciando Bancos de Dados

Criar e Excluir Bancos

CREATE DATABASE banco_teste;

DROP DATABASE banco_teste;

Configurar Conexões

ALTER DATABASE banco_teste SET CONNECTION LIMIT 20;

Utilizar Templates

CREATE DATABASE novo_banco TEMPLATE modelo_padrao;

Extensões Úteis

• hstore: para dados chave-valor
• pg_prewarm: pré-carregar dados no cache
• pg_buffercache: monitorar buffers compartilhados
• pg_walinspect: analisar logs WAL

Instalação:

CREATE EXTENSION pg_prewarm;

SELECT * FROM pg_buffercache;

Estruturas do Banco

Schema e Tabelas

CREATE DATABASE loja;
\c loja;

CREATE SCHEMA vendas;

CREATE TABLE vendas.produtos (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    preco DECIMAL CHECK (preco > 0),
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

\d vendas.produtos

Manipulação de Dados

INSERT INTO vendas.produtos (nome, preco) VALUES ('Teclado', 150.00);

SELECT * FROM vendas.produtos;

UPDATE vendas.produtos SET preco = 140.00 WHERE nome = 'Teclado';

DELETE FROM vendas.produtos WHERE id = 1;

Índices e Visualizações

CREATE INDEX idx_nome ON vendas.produtos (nome);

CREATE VIEW produtos_baratos AS
SELECT nome, preco FROM vendas.produtos WHERE preco < 100;

Analogia: Índices são como placas de sinalização em um galpão – apontam rapidamente para a localização dos dados. Views são atalhos, similares a links simbólicos no Linux.

Sequências e Transações

Gerenciar Sequências

CREATE SEQUENCE codigo_produto START 1;

INSERT INTO vendas.produtos (id, nome, preco)
VALUES (nextval('codigo_produto'), 'Mouse', 80.00);

Corrigir conflitos:

SELECT last_value, is_called FROM codigo_produto;

SELECT setval('codigo_produto', (SELECT MAX(id) FROM vendas.produtos) + 1);

Transações

BEGIN;
INSERT INTO contas (id, saldo) VALUES (1, 2000);
UPDATE contas SET saldo = saldo - 200 WHERE id = 1;
COMMIT;

-- Em caso de erro:
ROLLBACK;

Níveis de Isolamento

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM contas WHERE id = 1;
COMMIT;

Programação PL/pgSQL

Funções e Procedimentos

CREATE OR REPLACE FUNCTION ola_mundo()
RETURNS VOID AS $$
BEGIN
    RAISE NOTICE 'Olá, Mundo!';
END;
$$ LANGUAGE plpgsql;

SELECT ola_mundo();

Tipos e Controle de Fluxo

CREATE OR REPLACE FUNCTION avaliar_nota(nota INTEGER)
RETURNS TEXT AS $$
BEGIN
    IF nota >= 90 THEN
        RETURN 'Excelente';
    ELSIF nota >= 75 THEN
        RETURN 'Bom';
    ELSE
        RETURN 'Precisa melhorar';
    END IF;
END;
$$ LANGUAGE plpgsql;

SELECT avaliar_nota(82);

Loops e Cursores

CREATE OR REPLACE FUNCTION listar_contas()
RETURNS VOID AS $$
DECLARE
    cur CURSOR FOR SELECT id, saldo FROM contas;
    id_cont INTEGER;
    saldo_cont DECIMAL;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO id_cont, saldo_cont;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Conta ID: % | Saldo: %', id_cont, saldo_cont;
    END LOOP;
    CLOSE cur;
END;
$$ LANGUAGE plpgsql;

SELECT listar_contas();

Tratamento de Exceções

CREATE OR REPLACE FUNCTION exemplo_erro()
RETURNS VOID AS $$
BEGIN
    PERFORM 1 / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Erro de divisão por zero capturado!';
END;
$$ LANGUAGE plpgsql;

SELECT exemplo_erro();

Triggers e Segurança

Triggers Convencionais

CREATE TABLE log_contas (
    log_id SERIAL PRIMARY KEY,
    id_conta INTEGER,
    acao TEXT,
    momento TIMESTAMP
);

CREATE OR REPLACE FUNCTION registrar_alteracao()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO log_contas (id_conta, acao, momento)
    VALUES (NEW.id, TG_OP, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER gatilho_contas
AFTER INSERT OR UPDATE OR DELETE ON contas
FOR EACH ROW
EXECUTE FUNCTION registrar_alteracao();

Gestão de Usuários e Permissões

CREATE ROLE operador LOGIN PASSWORD 'senhaForte';

GRANT SELECT, INSERT ON contas TO operador;

REVOKE INSERT ON contas FROM operador;

Backup e Recuperação

Backup Lógico com pg_dump

pg_dump -U admin -d loja -f backup_loja.sql

Backup de Todo o Cluster

pg_dumpall -U admin > todos_bancos.sql

Backup a nível de Arquivo

sudo systemctl stop postgresql
cp -r /var/lib/postgresql/data /backup/data
sudo systemctl start postgresql

Backup a Quente com pg_basebackup

pg_basebackup -U admin -D /backup/ -Ft -z -P

Backup Incremental com WAL

Configurar em postgresql.conf:

archive_mode = on
archive_command = 'cp %p /arq_wal/%f'

Monitoramento e Otimização

Benchmark com pgbench

pgbench -i -s 10 loja

pgbench -c 5 -j 2 -T 30 loja

Análise de Consultas Lentas

Ativar a extensão:

CREATE EXTENSION pg_stat_statements;

SELECT query, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Particionamento de Tabelas

CREATE TABLE vendas_2023 PARTITION OF vendas
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Alta Disponibilidade

Replicação com Keepalived

Configurar Keepalived:

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass SenhaVIP
    }
    virtual_ipaddress {
        192.168.1.100
    }
}

Failover Automático com pg_auto_failover

pg_autoctl create cluster --pgdata /var/lib/postgresql/data --hostname primario

pg_autoctl show state

Balanceamento com pgpool-II

Configurar em /etc/pgpool-II/pgpool.conf:

backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 1