PostgreSQL

É um Sistema Gerenciador de Banco de Dados (SGBD) relacional, open source e robusto. Ele se destaca por justamente conseguir "romper" os limites tradicionais de SGBDs relacionais tradicionais ao oferecer suporte a tipos avançados de dados que normalmente são esperados em NoSQL (ou orientados a objetos) databases.

• JSON / JSONB:
• Arrays:
• mini-JSON:
• ENUM:
• Arrays: Além dessa grande vantagem, o PostgreSQL é muito conhecido por apresentar:
• Uma comunidade grande
• Altamente extensível (por ser opensource e ter uma grande comunidade, existem várias extensões como o PostGIS (geodados), pg_cron (para agendar cron jobs dentro do SGBD) ou pg_mooncake (fornece views/queries uteis para monitoramento do banco de dados)).
• Alta concorrência e suporte completo a ACID.

Armazenamento dos Dados

O postgres armazena os dados em arquivos binários, otimizados. Os dados geralmente ficam em /var/lib/postgresql/16/main/. E, mais especificamente, os dados de cada banco de dados fica armazenado em um subdir de /var/lib/postgresql/16/main/base/.

Command Line Tools

Conjunto de CLIs para interagir com o PostgreSQL. Abaixo, segue as mais utilizadas.

psql

É o CLI interativa do PostgreSQL. Nele é possível se conectar com banco de dados, escrever e executar queries SQL e gerenciar bancos, usuários e permissões.

Conectar a um db no psql:

psql -U seu_usuario -d nome_do_banco # local
psql -h <endereco_do_host> -U <nome_do_usuario> -d <nome_do_banco> # remoto

Nota: No Linux o PostgreSQL usar como default o método peer authentication para o usuário postgres (configuração do arquivo /etc/postgresql/17/main/pg_hba.conf, hba = Host-Based Authentication. Essa abordagem de peer auth não requer senha pois utiliza o próprio usuário do sistema (whoami) para dizer se o usuário postgres está correto (diferentemente de usar a auth MD5 que exige senha (e a armazena usando a hash MD5). Então, para se conectar ao usuário root -U postgres é preciso ou alterar o método de auth dele ou então executar como usuário linux postgres. (Por usar peer auth, as instalações do PostgreSQL não pedem para definir uma senha default para o admin user )

cut -d: -f1 /etc/passwd # lsitar os usuer do OS (vai aparecer o postgres)
sudo -u postgres psql # sudo -u para mudar o usuário do sistema
sudo -u postgres psql -U postgres -d frutally # exemplo para se conectar ao banco de dados frutally do usuário postgres

As aplicações externas (JDBC, Spring, etc) se comunicam com o PostgreSQL via rede (não estão "na máquina"/ Unix socket/ socket local (um conceito de socket diferente do Socket de rede)). E, por esse motivo a peer auth não se aplica. E, por esse motivo, é comum criar um usuário externo em que sua auth não seja peer (ou seja, precisa de password). Exemplo:

SHOW password_encryption; # mostra o método default de cript do pg
CREATE USER nome_do_usuario WITH PASSWORD 'senha_do_usuario';
GRANT ALL PRIVILEGES ON DATABASE nome_do_banco TO nome_do_usuario; # da privilégio ao user para utilizar o db. Pode criar objetos, mas não é dono, não pode alterar permissões dos outros e etc. IMPORTANTE: lembrar que esse comando não da acesso ao usuário de criar tabelas em schemas já existentes. (tipo o schema public default).
ALTER DATABASE nome_do_banco OWNER TO nome_do_usuario; # muda o owner. Concede controle total ao db (inclusive alterar permissoes)
\dg # mostra os users/roles e seus privilegios
ALTER USER nome_do_usuario WITH PASSWORD 'nova_senha'; # mudar a senha do user

Copiar dados para o servidor:

\copy tabela_de_clientes (CPF, NOME, ENDERECO_1, ENDERECO_2, BAIRRO, CIDADE, ESTADO, CEP, DATA_DE_NASCIMENTO, IDADE, SEXO, LIMITE_DE_CREDITO, VOLUME_DE_COMPRA, PRIMEIRA_COMPRA)
FROM '/home/thom/Downloads/pg-alura/tabela_de_clientes.csv'
WITH (FORMAT csv, HEADER false, DELIMITER ';', QUOTE '"');

Comandos úteis

• \list. (ou \l). Listar os bancos de dados criados naquele servidor.
• CREATE DATABASE my_db;
• \list. (ou \l). Listar os bancos de dados criados naquele servidor.
• \list. (ou \l). Listar os bancos de dados criados naquele servidor.
• \list. (ou \l). Listar os bancos de dados criados naquele servidor.

pg_dump

Fazer backup (exportar) de um banco de dados

pg_restore

Restaura um banco de dados a partir de um dump feito com pg_dump

pg_ctl

pgAdmin

É a GUI do PostgreSQL (tipo o psql só que com interface gráfica).

Um atalho legal para visualizar uma table rápido é clicando nela e apertando Alt + Shift + V

Logging

Os logs ficam armazenados onde está configurado o parâmetro logging_directory dentro do arquivo de configurações básicas do PostgreSQL (que fica em /etc/..., separado do dir de onde ficam os dados). Para visualizar o qual é o diretório configurado, siga o comando abaixo:

cat /etc/postgresql/16/main/postgresql.conf | grep log_directory

Sintaxe SQL

Segue abaixo peculiaridades do SQL implementado pelo pgSQL.

Operadores de JSON/JSONB

SELECT dados->>'nome' FROM usuarios; -- extrai como text
SELECT dados->'enderecos'->0 FROM usuarios; -- navega aninhado
SELECT dados #>> '{enderecos,0,cidade}' FROM usuarios; -- path como text
SELECT dados ? 'telefone' FROM usuarios; -- chave existe?
SELECT dados @> '{"idade": 25}' FROM usuarios; -- contém?

Referências

• YT - I replaced my entire tech stack with PostgreSQL...
• Alura - PostgreSQL: administração e monitoramento
• Alura - Administração com PostgreSQL: backup e recuperação

==## Pesquisas Futuras==

• tablespaces
• pooling de conexões
• pgBouncer e pgBench