Arquivos, diretórios e processos

Introdução

No PostgreSQL, a organização de arquivos e diretórios, bem como a gestão de processos, são componentes fundamentais para entender o seu funcionamento. Este artigo fornecerá uma visão geral de como o PostgreSQL estrutura seus dados e controla seus processos.

Roteiro:

• Arquivos e diretórios dos dados no PostgreSQL:
  • Binários
  • Arquivos de configuração
  • Arquivos de logs
  • Subdiretórios
• Processos

Arquivos e diretórios dos dados no PostgreSQL

O PostgreSQL organiza seus dados e configurações em uma estrutura de diretórios específica. A localização desses diretórios pode variar dependendo da instalação, mas geralmente segue um padrão. O diretório principal que contém todos os dados do PostgreSQL é o Base Directory (também conhecido pela variável de ambiente $PGDATA), que é definido durante a inicialização do cluster PostgreSQL com o comando initdb. Para sistemas Linux, o endereço de PGDATA pode variar conforme a distribuição:

• Debian: /var/lib/postgresql/<versão>/<nome_do_cluster>
• Red Hat: /var/lib/pgsql/<versão>/data

Claro, você pode definir qualquer diretório/disco/partição para colocar seus dados, como demonstramos nos artigos anteriores sobre instalação. Portanto é uma boa ideia usar a variável de ambiente $PGDATA (que, a depender da distribuição, já vem setada para o usuário postgres) ou então descobrir esse diretório pelo comando no psql SHOW data_directory:

$ psql -c 'SHOW data_directory;'
       data_directory
-----------------------------
 /var/lib/postgresql/16/main
(1 row)

Nesse diretório, temos arquivos e subdiretórios importantes para o programa, que veremos a seguir.

Binários

Dentre os principais arquivos do PostgreSQL, temos os binários, que são executáveis responsáveis por iniciar e interagir com o banco de dados. Eles estão em diferentes diretórios, que mudam conforme o sistema operacional. Considerando essas três distribuições Linux, podemos encontrar os principais binários em:

• Debian:
  • /usr/share/postgresql-common: contém arquivos e scripts comuns utilizados por várias versões do PostgreSQL, por exemplo, pg_ctlcluster e pg_createcluster;
  • /usr/lib/postgresql/<sua_versão>/bin: neste diretório, você pode encontrar os binários específicos da versão do PostgreSQL, como o próprio psql, os utilitários createdb, initdb etc.
• Red Hat:
  • /usr/pgsql/<versão>/bin/: guarda binários e executáveis específicos de cada versão do PostgreSQL.
• Instalação via compilação:
  • /usr/local/pgsql/bin/

Arquivos de configuração

• postgresql.conf: principal arquivo de configuração do servidor PostgreSQL. Contém parâmetros para ajustar o desempenho, a segurança, o comportamento e outras opções do servidor. Em distribuições Debian e derivados, este arquivo está em outro local, num diretório que agrega todos os arquivos de configuração: /etc/postgresql/<versão>/<nome_do_cluster>;
• pg_hba.conf: configurações de autenticação do host. Define quem pode conectar-se ao banco de dados e como as conexões são autenticadas. Assim como antes, em distribuições Debian e derivados, este arquivo fica em outro local;
• pg_ident.conf: mapeamentos para autenticação do tipo ident no pg_hba.conf. Usado para mapear nomes de usuários do sistema operacional para nomes de usuários do PostgreSQL. Mais uma vez, em distribuições Debian e derivados, este arquivo fica em outro local, vide acima;
• postgresql.auto.conf: armazena as configurações definidas dinamicamente através do comando ALTER SYSTEM SET, bem como outras configurações definidas automaticamente pelo próprio PostgreSQL. Você não deve editar este arquivo manualmente!;
• PG_VERSION: contém a versão do PostgreSQL em uso no cluster (major version);
• postmaster.pid: contém o ID do processo (PID) do servidor PostgreSQL em execução e outras informações, como diretório de dados do cluster, data de início do processo etc.;
• postmaster.opts: arquivo que registra as opções de linha de comando usadas por último na inicialização do servidor PostgreSQL.

Arquivos de logs

Dentro do $PGDATA, temos outros diretórios importantes que guardam arquivos do PostgreSQL, como os arquivos de log. Normalmente, os logs ficam em $PGDATA/logs, mas em distribuições Debian e derivadas eles estão localizados em /var/log/postgresql, como podemos ver na saída abaixo. 

# ls -lh /var/log/postgresql/*
total 6.6M
drwxrwxr-t  2 root     postgres 4.0K May 19 00:00 .
drwxr-xr-x 10 root     root     4.0K May  1 00:00 ..
-rw-r-----  1 postgres adm      123K May 23 09:40 postgresql-16-main.log
-rw-r-----  1 postgres adm      6.0M May 19 00:00 postgresql-16-main.log.1
-rw-r-----  1 postgres adm      254K May 12 00:00 postgresql-16-main.log.2.gz
-rw-r-----  1 postgres adm       18K May  5 00:00 postgresql-16-main.log.3.gz
-rw-r-----  1 postgres adm      125K Apr 28 00:00 postgresql-16-main.log.4.gz
-rw-r-----  1 postgres adm       27K Apr 21 00:00 postgresql-16-main.log.5.gz
-rw-r-----  1 postgres adm       36K Apr 14 00:00 postgresql-16-main.log.6.gz
-rw-r-----  1 postgres adm      1.1K Apr  7 00:00 postgresql-16-main.log.7.gz
-rw-r-----  1 postgres adm      2.0K Mar 31 00:00 postgresql-16-main.log.8.gz

Na verdade, você pode escolher outros lugares arbitrários para colocar seus logs, como em um disco separado (pode ser útil em servidores com muita carga), ajustando a variável log_directory. Abaixo vemos um exemplo da localização do diretório de logs na AWS RDS:

SHOW log_directory;
    log_directory
----------------------
 /rdsdbdata/log/error

O valor do log_directory pode ser absoluto, com o path completo ou relativo ao diretório do $PGDATA.

Subdiretórios

• base/: contém um subdiretório para cada banco de dados individual no cluster;
  • Dentro desses subdiretórios de cada banco que são identificados com números (16390, por exemplo) temos com os dados de cada objeto no banco, como tabelas e índice. Estes arquivos possuem eventualmente outros com o mesmo número e um sufixo _fsm (free space map) e _vm (visibility map), que são essenciais para manutenção e otimização e controle de transações. Além disso, temos um diretório chamado pgsql_tmp com arquivos temporários como tabelas temporárias. Eventualmente é possível armazenar estes arquivos temporários em outros tablespaces, ajustando a configuração temp_tablespaces;
• global/: contém tabelas e dados globais, como informações sobre usuários e grupos, por exemplo, as tabelas pg_database e pg_control;
  • Aqui também temos os arquivos _fsm e _vm, mas a nível global, ou seja, que se aplicam a todos os bancos de dados em um cluster;
• pg_xlog/ ou pg_wal/: contém os arquivos de log de transação (WAL – Write-Ahead Logging), essenciais para a recuperação de dados;
• pg_clog/ ou pg_commit_ts/: mantém informações sobre o estado das transações atuais;
• pg_stat/: armazena dados estatísticos permanentes utilizados para otimização de consultas;
• pg_stat_tmp/: armazena dados estatísticos temporários utilizados para otimização de consultas;
• pg_tblspc/: pontos de montagem de tablespaces, que permitem a administração de locais de armazenamento adicionais.

Processos

PostgreSQL é um sistema de gerenciamento de banco de dados do tipo cliente-servidor. Diferente de outros modelos, ele utiliza uma arquitetura multiprocesso para rodar em um único host. Imagine uma equipe trabalhando em conjunto para cuidar do seu banco de dados: esse é o conceito por trás dessa arquitetura.

Essa “equipe” é composta por diversos processos, cada um com uma função específica:

• postmaster (server process): o processo principal do PostgreSQL. É responsável por iniciar, parar e supervisionar todos os processos de servidor do PostgreSQL. Ele recebe conexão de apenas uma porta (a 5432 por padrão), porém podemos ter mais de uma instância do PostgreSQL rodando no mesmo servidor, especificando uma porta para cada instância. Podemos ter, por exemplo, um PostgreSQL na versão 16 na porta 5432 e outro na versão 15 na porta 5433;
• postgres (backend process): cada conexão ao banco de dados é gerenciada por um processo postgres dedicado. Esse processo é responsável por receber e executar as consultas e comandos enviados pelos clientes, garantindo uma experiência personalizada para cada um;
• autovacuum launcher (background process): processo responsável pela limpeza automática do banco de dados, removendo registros obsoletos gerados por operações de UPDATE e DELETE;
• wal writer (background process): gerencia a gravação dos logs de transação (WAL) no disco, garantindo a durabilidade das transações, o que permite a recuperação do banco de dados;
• wal sender (background process): responsável por enviar os registros do WAL (Write-Ahead Logging) do servidor primário para um ou mais servidores de réplica (standby). Este processo é fundamental para a replicação em tempo real e a alta disponibilidade;
• wal receiver (background process): é executado em servidores de réplica e recebe os registros do WAL enviados pelo processo WAL sender do servidor primário;
• statistics collector (background process): é responsável por coletar e agrupar estatísticas sobre a operação do banco de dados, como contagem de linhas lidas, inseridas, atualizadas ou deletadas;
• logging collector (background process): coleta e armazena mensagens de log do servidor PostgreSQL em arquivos de log;
• archiver (background process): copia os arquivos do WAL para um local de arquivamento seguro após eles serem preenchidos;
• bgwriter (background process): é o “background writer”, que periodicamente escreve páginas sujas da memória para o disco, ajudando a manter a consistência dos dados;
• checkpointer (background process): periodicamente cria pontos de verificação (checkpoints) que ajudam na recuperação de falhas;
• IPC (background process): os processos do PostgreSQL se comunicam usando IPC (Inter-Process Communication), que inclui sinais (signals), memória compartilhada (shared memory) e semáforos (semaphores). Estes mecanismos permitem a coordenação entre os processos, garantindo a consistência e a integridade dos dados.

Abaixo segue o comando ps faux | grep postgres executado como root, que é um comando para mostrar todos os processos relacionados ao postgres, de todos os usuários, e sua saída:

# ps faux | grep postgres
postgres  171711  0.0  1.7 217608 17460 ?        Ss   Apr09   7:52 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf
postgres  171712  0.0  7.8 218036 77644 ?        Ss   Apr09   0:02  \_ postgres: 16/main: checkpointer
postgres  171713  0.0  0.7 217756  7468 ?        Ss   Apr09   0:41  \_ postgres: 16/main: background writer
postgres  171715  0.0  0.6 217608  6796 ?        Ss   Apr09   0:44  \_ postgres: 16/main: walwriter
postgres  171716  0.0  0.7 219208  7108 ?        Ss   Apr09   1:45  \_ postgres: 16/main: autovacuum launcher
postgres  171717  0.0  0.4 219184  4340 ?        Ss   Apr09   0:02  \_ postgres: 16/main: logical replication launcher

Como um exemplo adicional, fizemos um teste com o pg_bench (com escala 10x maior do que a padrão, 50 clientes e quatro threads, com duração de 120 segundos) e rodamos, desta vez, o comando top -c, que fornece a visualização dinâmica e interativa dos processos. Este é um trecho da saída:

# top -c
top - 18:01:38 up 57 days,  2:24,  2 users,  load average: 19.95, 5.63, 1.93
Tasks: 135 total,  32 running, 103 sleeping,   0 stopped,   0 zombie
%Cpu(s): 64.7 us, 34.3 sy,  0.0 ni,  0.0 id,  1.0 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :    961.0 total,     77.9 free,    505.3 used,    679.6 buff/cache
MiB Swap:      0.0 total,      0.0 free,      0.0 used.    455.7 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
1219997 postgres  20   0  239676   7792   6412 S  17.5   0.8   0:09.62 /usr/lib/postgresql/16/bin/pgbench -c 50 -j 4 -T 120 postgres
1220014 postgres  20   0  219816  84068  80660 R   2.3   8.5   0:00.92 postgres: 16/main: postgres postgres [local] idle in transaction
1220040 postgres  20   0  219820  82300  78888 S   2.0   8.4   0:00.90 postgres: 16/main: postgres postgres [local] UPDATE waiting
1220050 postgres  20   0  219820  83928  80516 R   2.0   8.5   0:00.90 postgres: 16/main: postgres postgres [local] UPDATE waiting
1220005 postgres  20   0  219816  80776  77376 R   1.7   8.2   0:00.90 postgres: 16/main: postgres postgres [local] COMMIT
1220007 postgres  20   0  219816  82044  78636 S   1.7   8.3   0:00.90 postgres: 16/main: postgres postgres [local] UPDATE waiting
1220009 postgres  20   0  219816  82604  79204 R   1.7   8.4   0:00.90 postgres: 16/main: postgres postgres [local] idle
1220010 postgres  20   0  219816  82204  78804 S   1.7   8.4   0:00.90 postgres: 16/main: postgres postgres [local] UPDATE waiting
1220011 postgres  20   0  219816  81988  78588 R   1.7   8.3   0:00.90 postgres: 16/main: postgres postgres [local] idle in transaction
1220012 postgres  20   0  219816  82944  79544 S   1.7   8.4   0:00.90 postgres: 16/main: postgres postgres [local] UPDATE waiting
1220013 postgres  20   0  219816  83216  79800 R   1.7   8.5   0:00.90 postgres: 16/main: postgres postgres [local] idle in transaction
1220015 postgres  20   0  219816  84092  80692 S   1.7   8.5   0:00.90 postgres: 16/main: postgres postgres [local] UPDATE waiting
1220017 postgres  20   0  219816  80868  77468 R   1.7   8.2   0:00.90 postgres: 16/main: postgres postgres [local] UPDATE waiting
1220018 postgres  20   0  219816  83108  79708 R   1.7   8.4   0:00.90 postgres: 16/main: postgres postgres [local] idle in transaction
1220021 postgres  20   0  219816  83968  80568 S   1.7   8.5   0:00.90 postgres: 16/main: postgres postgres [local] UPDATE waiting
1220022 postgres  20   0  219816  79704  76304 S   1.7   8.1   0:00.90 postgres: 16/main: postgres postgres [local] UPDATE waiting
1220023 postgres  20   0  219816  82652  79252 R   1.7   8.4   0:00.90 postgres: 16/main: postgres postgres [local] idle in transaction
1220024 postgres  20   0  219820  82452  79036 S   1.7   8.4   0:00.90 postgres: 16/main: postgres postgres [local] UPDATE waiting
1220025 postgres  20   0  219820  80704  77292 R   1.7   8.2   0:00.90 postgres: 16/main: postgres postgres [local] COMMIT
1220026 postgres  20   0  219820  79340  75928 R   1.7   8.1   0:00.90 postgres: 16/main: postgres postgres [local] idle in transaction

O primeiro processo pgbench é o que está executando o teste de benchmarking e consome 17,5% da CPU, sendo o responsável pela maior carga no sistema.

Também é possível observar vários processos em estado R (running) ou S (sleeping), o que é esperado em um ambiente de testes. Também temos os status idle in transaction e UPDATE waiting, indicando que estão aguardando a conclusão de transações ou de operações de atualização.

Conclusão

O PostgreSQL tem uma organização relativamente simples de arquivos, diretórios e processos. Para o desenvolvedor ou usuário final do banco de dados, essas informações podem ser mera curiosidade, mas para quem pretende ser um DBA, a história é bem diferente. É fundamental entender o que cada um faz e onde fica cada informação. Enquanto tudo estiver funcionando bem, você provavelmente não vai se preocupar muito com isso, mas quando problemas ocorrerem, como falhas de hardware, bugs etc., entender esses conceitos básicos será fundamental para resolver os problemas. 
Mais informações podem ser encontradas na documentação oficial, aqui.

No próximo artigo, vamos entender um pouco melhor como os objetos do banco são organizados logicamente no catálogo interno e fisicamente nos diretórios $PGDATA/base, $PGDATA/global e $PGDATA/pg_tblspace.

Utilização de SQL básico

Introdução

Já percorremos um bom caminho até aqui. Instalamos o PostgreSQL em diferentes cenários, vimos algumas ferramentas para utilização no dia a dia, mas você já deve estar um pouco cansado disso e louco para colocar a mão na massa, não é? Então, neste artigo, exploraremos os principais comandos e conceitos envolvidos na administração e manipulação de dados em um ambiente SQL, mostrando comandos que podem ser executados no psql, desde a criação e o gerenciamento de papéis/usuários (roles) até a manipulação dos principais objetos (tabelas, esquemas e bases de dados), com muitos exemplos. A ideia é dar uma visão geral, para você se sentir mais confiante e sair explorando possibilidades para seus próprios projetos independentemente dos próximos artigos na sequência. Boa diversão!

Roteiro:

• CREATE / ALTER / DROP …
  • … ROLE
  • … DATABASE
  • … SCHEMA
  • … TABLE
• INSERT
• SELECT
• UPDATE 
• DELETE

CREATE / ALTER / DROP

Os comandos CREATE, ALTER e DROP são fundamentais para a administração de bancos de dados em SQL, sendo responsáveis pela criação, alteração e exclusão de objetos, respectivamente. Em termos mais formais, esses comandos correspondem a um subconjunto da linguagem SQL conhecida como DDL: Linguagem de definição de dados (do Inglês Data Definition Language).

CREATE: como dito anteriormente, esse comando é utilizado para criar objetos dentro do banco de dados. Isso inclui a criação de tabelas, bancos de dados, esquemas, usuários, papéis (roles), entre outros. 

ALTER: esse comando é utilizado para modificar a estrutura de objetos existentes no banco de dados. Ele permite alterar tabelas, adicionar ou remover colunas, modificar tipos de dados, entre outras operações.

DROP: é utilizado para excluir objetos do banco de dados. Isso pode incluir a exclusão de tabelas, bancos de dados, esquemas, usuários, entre outros. É uma operação irreversível e deve ser usada com cuidado, pois excluir um objeto pode resultar na perda permanente de dados.

Exemplos

Aplicação em ROLE

O comando CREATE ROLE é utilizado para criar usuários dentro do banco de dados. Um grupo de usuários ou role é uma entidade que define um conjunto de permissões e privilégios na utilização desse banco. Essas permissões podem ser atribuídas a usuários específicos ou a grupos, facilitando a gestão de acesso e segurança. Ao criar uma nova role com o comando CREATE ROLE, você pode especificar diversas configurações, como permissões específicas, restrições de acesso, entre outros. Por exemplo:

CREATE ROLE meu_role LOGIN PASSWORD 'senha';

Nesse exemplo dado, foi fornecida permissão para fazer login (LOGIN) com as credenciais fornecidas (nome da role e senha, identificada como PASSWORD), portanto, pode ser considerado uma role de usuário. Caso fosse criado sem permissão de login, seria uma role para controle de privilégios, que poderia ser aplicado em diversos usuários. Além disso, é válido lembrar que também temos o comando CREATE USER para criar usuários, que por debaixo dos panos chama o CREATE ROLE com a permissão de login inclusa, tornando-o, assim, específico para usuários (note que o uso do comando CREATE USER está depreciado, por isso não recomendamos a sua utilização). Existem, ainda, muitas outras permissões possíveis, que podem ser consultadas na página oficial do comando CREATE ROLE. Algumas das principais são:

• SUPERUSER: define se a role terá privilégios de superusuário, que incluem acesso irrestrito a todos os objetos e comandos no banco de dados. É um acesso sensível pelo tanto de privilégios que tem e só deve ser usado em caso de necessidade, por exemplo, para um usuário que fará papel de administrador;
• CREATEDB: define se a role pode criar bases de dados;
• CREATEROLE: define se a role pode criar outras roles.

Para exemplificar esse e outros comandos do SQL, bem como suas permissões e níveis de acesso, criamos as seguintes roles:

• dba: será o administrador do banco de dados, criado como SUPERUSER;
• Aplicação 1:
  • app1_admin: será o administrador da aplicação1, criado com ADMIN;
  • app1_user1: será um usuário representando um módulo da aplicação 1;
  • app1_user2: será outro usuário representando mais um módulo da aplicação 1.
• Aplicação 2:
  • app2_admin: será o administrador da aplicação 1;
  • app2_user1: será um usuário representando um módulo da aplicação 2;
  • app2_user2: será outro usuário representando mais um módulo da aplicação 2.

Perceba que para as roles de administradores, vamos conceder permissões extras além de apenas o login. Os comandos utilizados foram:

CREATE ROLE dba SUPERUSER LOGIN PASSWORD 'senha';
CREATE ROLE app1_admin CREATEDB CREATEROLE LOGIN PASSWORD 'senha';
CREATE ROLE app2_admin CREATEDB CREATEROLE LOGIN PASSWORD 'senha';
CREATE ROLE app1_user1 LOGIN PASSWORD 'senha1';
CREATE ROLE app1_user2 LOGIN PASSWORD 'senha2';
CREATE ROLE app2_user1 LOGIN PASSWORD 'senha1';
CREATE ROLE app2_user2 LOGIN PASSWORD 'senha2';

Agora, para exemplificar o ALTER ROLE, podemos usar o seguinte cenário: suponha que a role app1_user2 foi criado sem permissão de login, e que isso inviabilizaria o uso do banco. Para corrigir, poderíamos rodar:

ALTER ROLE app1_user2 LOGIN;

E isso é válido para qualquer permissão, tanto para acrescentar quanto para removê-la. Claro que, ao se tratar de permissões mais sensíveis, apenas um superusuário pode editar.

Também é possível adicionar/remover um usuário de uma role com o comando ALTER ou adicionar/remover uma role de um grupo de usuários.

Para remover uma role, basta usar o comando DROP ROLE da seguinte forma:

DROP ROLE app_user3;

Aplicação em DATABASE

O comando CREATE DATABASE permite a criação de novos bancos de dados, possibilitando que diferentes conjuntos de dados sejam segregados e gerenciados de forma independente, melhorando a organização e a segurança do sistema.

Um exemplo simples de utilização do CREATE DATABASE é mostrado abaixo:

CREATE DATABASE novo_banco;

O comando CREATE DATABASE também possui diversas opções e configurações que podem ser especificadas durante a criação do banco de dados. Por exemplo, é possível definir a codificação de caracteres, o proprietário do banco de dados, as permissões de acesso e outras configurações relevantes para o ambiente de armazenamento de dados, o que pode ser conferido nesta página. 
Além disso, como citado anteriormente, não é todo usuário que consegue criar um banco novo, é preciso ter permissão para isso, por ser uma operação de maior impacto. 
Para prosseguir com nosso exemplo de banco, vamos criar a base de dados tendo nossos administradores (app1_admin e app2_admin) como proprietários, tudo feito através do usuário dba:

CREATE DATABASE aplicacao1 OWNER app1_admin;
CREATE DATABASE aplicacao2 OWNER app2_admin;

Utilizando o comando ALTER DATABASE, podemos modificar as configurações de um banco de dados existente, como alterar o proprietário:

ALTER DATABASE meu_banco OWNER TO novo_proprietario;

E o DROP DATABASE segue sua mesma função de excluir permanentemente esse objeto:

DROP DATABASE meu_banco;

Aplicação em SCHEMA

O comando CREATE SCHEMA possibilita a criação de espaços de nomes lógicos que contêm objetos relacionados, como tabelas, índices, visualizações e outros schemas. Essa funcionalidade é essencial para organizar e estruturar os objetos dentro de um banco de dados, melhorando a manutenção e a legibilidade do sistema. Um exemplo de utilização seria:

CREATE SCHEMA novo_esquema;

Assim como os outros usos do CREATE, aqui também temos diversas opções e configurações que podem ser especificadas, como definição de proprietário e permissões de acesso para usuários, que podem ser conferidas na página oficial do comando CREATE SCHEMA. Para prosseguir com nosso exemplo principal, vamos criar os schemas aplicacao1 e aplicacao2, pertencentes aos usuário app1_admin e app2_admin respectivamente:

CREATE SCHEMA aplicacao1 AUTHORIZATION app1_admin;
CREATE SCHEMA aplicacao2 AUTHORIZATION app2_admin;

Para modificar configurações de um schema existente, utilizamos o ALTER SCHEMA, conforme exemplo de alteração do proprietário:

ALTER SCHEMA meu_schema OWNER TO novo_proprietario;

E para excluí-lo é só utilizar o DROP SCHEMA:

DROP SCHEMA meu_schema;

Aplicação em TABLE

O comando CREATE TABLE é utilizado para criar novas tabelas em um banco de dados. Como exemplo:

CREATE TABLE aplicacao1.tabela1_app1 (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(150),
    idade VARCHAR(150)
);

Nesse exemplo, estamos criando uma nova tabela chamada tabela_app1, no schema aplicacao1, com três colunas: id, nome e e-mail. A coluna id é definida como uma chave primária usando o tipo de dado SERIAL, que é um tipo autoincrementável, garantindo valores únicos para cada registro. Os campos nome e e-mail são definidos como VARCHAR, que é o tipo de dado ideal para armazenar strings de tamanho variável. Além disso, a tabela foi criada no database aplicacao1, com o usuário dba, e além dela vamos criar mais duas tabelas, totalizando três tabelas em cada database:

CREATE TABLE aplicacao1.tabela2_app1 (
    id SERIAL PRIMARY KEY,
    endereco VARCHAR(150)
);

CREATE TABLE aplicacao1.tabela3_app1 (
    id SERIAL PRIMARY KEY,
    email VARCHAR(150)
);

Replicamos as mesmas tabelas para o schema aplicacao2.

O comando CREATE TABLE, assim como seus similares, também possui diversas opções e configurações que podem ser especificadas durante a criação da tabela. Por exemplo, é possível definir restrições de chave estrangeira, índices, gatilhos e outras propriedades específicas da tabela. Para mais informações é só consultar a página oficial.

Podemos também adicionar/modificar uma tabela existente utilizando o comando ALTER TABLE, por exemplo, para adicionar uma coluna nova:

ALTER TABLE minha_tabela
ADD COLUMN nova_coluna VARCHAR(100);

Finalmente, o comando DROP TABLE é utilizado para excluir uma tabela existente juntamente com todos os seus dados e objetos relacionados:

DROP TABLE minha_tabela;

Antes de executar o comando DROP TABLE, verifique se você realmente deseja excluir a tabela e se não há dados importantes dentro dela. Essa operação é irreversível e não pode ser desfeita.

INSERT

O comando INSERT serve para adicionar novos registros a uma tabela existente. Ele permite inserir dados em uma ou mais colunas específicas de uma tabela, criando, assim, novas entradas de dados que podem ser consultadas, atualizadas ou excluídas posteriormente.

Por exemplo, suponha que temos uma tabela chamada ‘usuários’ com colunas ‘id’, ‘nome’ e ‘e-mail’. Podemos inserir um novo usuário na tabela da seguinte maneira:

INSERT INTO usuarios (nome, email)
VALUES ('João', 'joao@example.com');

Nesse exemplo, estamos inserindo um novo registro na tabela ‘usuários’, fornecendo valores para as colunas ‘nome’ e ‘e-mail’. Se a coluna ‘id’ for uma chave primária autoincrementável, ela será gerada automaticamente pelo sistema.

Além disso, o comando INSERT também permite inserir múltiplas linhas de uma vez, fornecendo uma lista de valores para cada coluna. Vamos exemplificar isso inserindo dados nas nossas tabelas criadas anteriormente (replicando tudo no schema aplicacao2):

-- Inserção de dados na tabela1_app1
INSERT INTO aplicacao1.tabela1_app1 (nome, idade) VALUES ('João', '30');
INSERT INTO aplicacao1.tabela1_app1 (nome, idade) VALUES ('Maria', '25');
INSERT INTO aplicacao1.tabela1_app1 (nome, idade) VALUES ('Pedro', '40');

-- Inserção de dados na tabela2_app1
INSERT INTO aplicacao1.tabela2_app1 (endereco) VALUES ('Rua A, 123');
INSERT INTO aplicacao1.tabela2_app1 (endereco) VALUES ('Avenida B, 456');
INSERT INTO aplicacao1.tabela2_app1 (endereco) VALUES ('Praça C, 789');

-- Inserção de dados na tabela3_app1
INSERT INTO aplicacao1.tabela3_app1 (email) VALUES ('joao@example.com');
INSERT INTO aplicacao1.tabela3_app1 (email) VALUES ('maria@example.com');
INSERT INTO aplicacao1.tabela3_app1 (email) VALUES ('pedro@example.com');

SELECT

O comando SELECT permite recuperar dados de uma ou mais tabelas de um banco de dados. Com o SELECT, é possível realizar consultas complexas para obter exatamente os dados desejados, filtrar resultados, realizar cálculos e até mesmo unir informações de várias fontes diferentes.

A seguir, você pode conferir um exemplo simples de utilização do comando SELECT:

SELECT * FROM minha_tabela;

Nesse exemplo, estamos selecionando todos os registros e todas as colunas da tabela minha_tabela. O * é um operador curinga que significa “todas as colunas”. Se desejar, você também pode categorizar colunas específicas para retornar:

SELECT nome, idade FROM minha_tabela;

Além disso, o SELECT permite realizar filtragens e ordenações nos resultados, utilizando a cláusula WHERE para especificar condições de filtro e a cláusula ORDER BY para ordenar os resultados de acordo com uma ou mais colunas:

SELECT * FROM minha_tabela WHERE idade > 18 ORDER BY nome;

Com esse comando, estamos selecionando todos os registros da tabela minha_tabela, em que a idade é maior que 18, e ordenando os resultados pelo nome.

O comando SELECT também suporta funções de agregação, como SUM, COUNT, AVG, entre outras, que podem ser usadas para realizar cálculos sobre os dados selecionados:

SELECT COUNT(*) FROM minha_tabela;

Esse comando retorna o número total de registros na tabela minha_tabela.

UPDATE

O comando UPDATE é utilizado no PostgreSQL para modificar os dados existentes em uma ou mais linhas de uma tabela. Ele oferece uma maneira eficiente de realizar alterações nos registros já existentes, permitindo atualizar valores de colunas de acordo com condições específicas. Confira a seguir um exemplo simples de utilização do comando UPDATE:

UPDATE minha_tabela
SET coluna1 = novo_valor
WHERE condicao;

Nesse exemplo, estamos atualizando os valores da coluna coluna1 na tabela minha_tabela, definindo-os como novo_valor, apenas para as linhas que atendem à condição especificada após a cláusula WHERE.

Por exemplo, para aumentar o salário de todos os funcionários com mais de 10 anos de serviço, podemos fazer o seguinte:

UPDATE funcionarios
SET salario = salario * 1.1
WHERE anos_de_servico > 10;

Com esse comando, estamos multiplicando o salário de todos os funcionários com mais de 10 anos de serviço por 1.1, aumentando-o em 10%.

Além disso, o UPDATE permite atualizar múltiplas colunas em uma única consulta e utilizar subconsultas para determinar os novos valores a serem atribuídos. Por exemplo, vamos supor que temos uma tabela chamada ‘tarefas’ com as colunas ‘id’, ‘descricao’ e ‘status’. Queremos atualizar todas as tarefas que estão com o status ‘Atrasada’ para o status ‘Em Andamento’:

UPDATE tarefas
SET status = 'Em Andamento'
WHERE status = 'Atrasada';

Com esse comando, estamos atualizando o status de todas as tarefas que estão como ‘Atrasada’ para ‘Em Andamento’.

DELETE

O comando DELETE é usado para remover registros de uma tabela. Ele permite remover uma ou mais linhas de uma tabela, de acordo com as condições especificadas, proporcionando uma maneira eficaz de limpar ou alterar os dados em um banco de dados. Exemplo:

DELETE FROM minha_tabela
WHERE condicao;

Nesse exemplo, estamos removendo registros da tabela minha_tabela que atendem à condição especificada após a cláusula WHERE. Isso significa que apenas as linhas que atendem a essa condição serão excluídas da tabela. Por exemplo, para remover todas as tarefas que estão concluídas e foram marcadas como ‘finalizadas’, podemos fazer o seguinte:

DELETE FROM tarefas
WHERE status = 'finalizadas';

Com esse comando, estamos excluindo todos os registros da tabela ‘tarefas’ que têm o status ‘finalizadas’.

O comando DELETE também pode ser usado sem uma cláusula WHERE para excluir todos os registros de uma tabela:

DELETE FROM minha_tabela;

Esse comando remove todos os registros da tabela minha_tabela, tornando-a vazia. Existe, ainda, uma outra forma mais rápida e eficiente de excluir todos os registros de uma tabela, utilizando o comando TRUNCATE.

É importante ter cuidado ao usar o comando DELETE, pois ele remove os dados de forma permanente e irreversível. Certifique-se sempre de ter feito backup dos dados antes de executar operações de exclusão em larga escala ou com condições complexas.

Conclusão

Ok, agora você já viu os comandos básicos para criar seus objetos (com os comandos CREATE / ALTER / DROP), manipular seus dados (com os comandos INSERT, UPDATE e DELETE) e, por fim, como extrair os dados com o comando SELECT. 

Em tese, isso é tudo o que você precisa para começar a colocar suas ideias em prática. Com o tempo, veremos que existem inúmeras opções e alternativas para cada um dos comandos apresentados aqui. Nós iremos revisitar cada um desses comandos com mais calma e atenção mais para a frente. No entanto, recomendamos que você não espere tanto assim. Tente, erre, comece novamente. Nada pode substituir a sua experiência pessoal por mais exemplos e descrições detalhadas que possamos dar aqui. Portanto, tire um tempo para testar, brincar e, se bobear, até se divertir. Só lembre-se de fazer isso longe dos bancos de dados de produção!