Connection string URL

Doc: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING-URIS

Utilidades

Instalando Mac

Brew

brew install postgresql@15

Reiniciando

brew services start postgresql@15

Instalando Linux

APT

Instalar e habilitar:

sudo apt install postgresql postgresql-contrib -y
 
# Enable postgres service
sudo systemctl enable --now postgresql

Verificar status:

sudo systemctl status postgresql

Reiniciar banco

Caso usando systemctl, basta:

sudo systemctl restart postgresql

Criação de usuários e database

CREATE DATABASE mydatabase;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
 
-- Se precisar trocar a senha
ALTER ROLE myuser WITH PASSWORD 'mynewpassword';
 
-- Dá permissão sobre o banco, mas não sobre os schemas e tabelas dentro dele.
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Para dar permissão para o schema public, por exemplo, ao usuário:

-- Garanta que está no banco correto
\c nome_do_banco
 
GRANT USAGE ON SCHEMA public TO myuser;
GRANT CREATE ON SCHEMA public TO myuser;
 
-- Ou para tudo:
GRANT ALL ON SCHEMA public TO myuser;

Se precisar de permissão completa:

ALTER SCHEMA public OWNER TO myuser;

Backup

A Complete Guide to PostgreSQL Backup & Recovery

https://www.enterprisedb.com/postgresql-database-backup-recovery-what-works-wal-pitr#section-7

Backup manual

To back up a PostgreSQL database, you can use the pg_dump utility. Here’s a simple way to do it:

1. Backup a Single Database

pg_dump -U your_user -W -F c -b -v -f your_backup_file.backup your_database

• -U your_user → Specifies the PostgreSQL user.

• -W → Prompts for the password.

• -F c → Uses the custom format (recommended for restoring with pg_restore).

• -b → Includes large objects (blobs).

• -v → Enables verbose mode.

• -f your_backup_file.backup → Specifies the output file.

• your_database → Replace with the database name.

2. Backup All Databases (Full Backup)

pg_dumpall -U your_user -W -f all_databases_backup.sql

• This creates a SQL file with all databases, roles, and permissions.

3. Restore a Database Backup

For a single database backup (custom format .backup file):

pg_restore -U your_user -W -d your_database -v your_backup_file.backup

For a full backup (.sql file):

psql -U your_user -W -f all_databases_backup.sql postgres

4. Automate Backups (Daily)

Add a cron job to automate backups. Open crontab:

crontab -e

Then add a line to run the backup every day at 2 AM:

0 2 * * * pg_dump -U your_user -F c -b -f /path/to/backup/your_database_$(date +\%Y\%m\%d).backup your_database

This will save backups with a timestamp (e.g., your_database_20250318.backup).

This method ensures your data is safe and restorable when needed! 🚀

Comandos básicos

Conexão inicial no Ubuntu

sudo -u postgres psql

Conexão e navegação (psql)

psql -U usuario -d nome_banco -h host    # conectar ao banco
\l                                        # listar bancos de dados
\c nome_banco                             # trocar de banco
\dt                                       # listar tabelas
\d nome_tabela                            # descrever estrutura da tabela
\du                                       # listar usuários/roles
\dn                                       # listar schemas
\q                                        # sair

Bancos de dados

CREATE DATABASE nome_banco;
DROP DATABASE nome_banco;

Tabelas

CREATE TABLE clientes (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    criado_em TIMESTAMP DEFAULT NOW()
);
 
ALTER TABLE clientes ADD COLUMN telefone VARCHAR(20);
ALTER TABLE clientes DROP COLUMN telefone;
ALTER TABLE clientes RENAME COLUMN nome TO nome_completo;
DROP TABLE clientes;
TRUNCATE TABLE clientes;              -- apaga todos os dados, mantém a estrutura

CRUD (dados)

-- Inserir
INSERT INTO clientes (nome, email) VALUES ('Alisson', 'alisson@email.com');
 
-- Consultar
SELECT * FROM clientes;
SELECT nome, email FROM clientes WHERE id = 1;
SELECT * FROM clientes ORDER BY criado_em DESC LIMIT 10;
 
-- Atualizar
UPDATE clientes SET email = 'novo@email.com' WHERE id = 1;
 
-- Deletar
DELETE FROM clientes WHERE id = 1;

Filtros e agregações

SELECT * FROM clientes WHERE nome LIKE '%Silva%';
SELECT * FROM clientes WHERE criado_em BETWEEN '2026-01-01' AND '2026-12-31';
SELECT COUNT(*) FROM clientes;
SELECT status, COUNT(*) FROM pedidos GROUP BY status;
SELECT * FROM pedidos ORDER BY valor DESC;

Joins

SELECT c.nome, p.valor
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id;
 
-- LEFT JOIN, RIGHT JOIN, FULL JOIN funcionam de forma parecida

Índices

CREATE INDEX idx_clientes_email ON clientes(email);
DROP INDEX idx_clientes_email;

Transações

BEGIN;
UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
UPDATE contas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;      -- ou ROLLBACK; para desfazer

Backup e restore (linha de comando)

pg_dump -U usuario nome_banco > backup.sql
psql -U usuario nome_banco < backup.sql
pg_dump -U usuario -Fc nome_banco > backup.dump   # formato custom (mais flexível)
pg_restore -U usuario -d nome_banco backup.dump

Usuários e permissões

CREATE USER novo_usuario WITH PASSWORD 'senha123';
GRANT ALL PRIVILEGES ON DATABASE nome_banco TO novo_usuario;
REVOKE ALL PRIVILEGES ON DATABASE nome_banco FROM novo_usuario;

Exibição expandida de registros (colunas em linhas)

No psql, o comando \x (expanded display) transforma cada coluna em uma linha, em vez de mostrar tudo lado a lado:

\x
SELECT * FROM clientes WHERE id = 1;

Resultado:

-[ RECORD 1 ]--------+--------------------------
id                    | 1
nome                  | Alisson
email                 | alisson@email.com
criado_em             | 2026-01-15 10:30:00

Variações:

\x on      -- liga explicitamente
\x off     -- desliga
\x auto    -- modo automático: só ativa o expandido se a linha não couber na tela

Dica: colocar \x auto no arquivo ~/.psqlrc para já vir configurado.

Alisson's Notes

Explorador

Postgres