ByteRef — PostgreSQL Reference

🐘

PostgreSQL banco de dados avançado

sqldatabasepostgres

🚀 Conexão & Setup

Conectar ao banco	`psql -h localhost -U postgres -d mydb`
Criar banco de dados	`createdb -U postgres mydb`
Listar bancos	`psql -U postgres -l`
Criar usuário	`createuser -U postgres --createdb myuser`
Alterar senha	`psql -U postgres -c "ALTER USER myuser PASSWORD 'newpass';"`
Ver versão	`psql --version`
Iniciar serviço	`sudo systemctl start postgresql`
Status do serviço	`sudo systemctl status postgresql`

📋 DDL - Estrutura

Criar tabela	`CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE, created_at TIMESTAMP DEFAULT NOW() );`
Adicionar coluna	`ALTER TABLE users ADD COLUMN age INTEGER;`
Criar índice	`CREATE INDEX idx_users_email ON users(email);`
Criar foreign key	`ALTER TABLE posts ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);`
Criar view	`CREATE VIEW active_users AS SELECT * FROM users WHERE active = true;`
Sequence customizada	`CREATE SEQUENCE user_id_seq START 1000 INCREMENT 1;`
Enum type	`CREATE TYPE status AS ENUM ('active', 'inactive', 'banned');`

🔍 Consultas Avançadas

JOIN com agregação	`SELECT u.name, COUNT(p.id) as posts FROM users u LEFT JOIN posts p ON u.id = p.user_id GROUP BY u.id, u.name HAVING COUNT(p.id) > 0;`
Window functions	`SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rank FROM employees;`
Common Table Expression	`WITH monthly_sales AS ( SELECT DATE_TRUNC('month', created_at) as month, SUM(amount) as total FROM orders GROUP BY 1 ) SELECT * FROM monthly_sales;`
Full-text search	`SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & query');`
JSON operations	`SELECT data->>'name' as name, data->'address'->>'city' as city FROM users WHERE data @> '{"active": true}';`
Array operations	`SELECT * FROM posts WHERE tags && ARRAY['python', 'flask'];`

⚡ Performance & Otimização

EXPLAIN ANALYZE	`EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';`
Ver índices	`SELECT * FROM pg_indexes WHERE tablename = 'users';`
Vacuum analyze	`VACUUM ANALYZE users;`
Ver locks	`SELECT * FROM pg_locks WHERE NOT granted;`
Ver conexões ativas	`SELECT * FROM pg_stat_activity;`
Cache hit ratio	`SELECT sum(blks_hit)*100/sum(blks_hit+blks_read) as cache_hit_ratio FROM pg_stat_database;`
Tamanho das tabelas	`SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname\|\|'.'\|\|tablename)) FROM pg_tables ORDER BY pg_total_relation_size(schemaname\|\|'.'\|\|tablename) DESC;`

🔄 Backup & Restore

Backup completo	`pg_dump -U postgres -h localhost mydb > backup.sql`
Backup comprimido	`pg_dump -U postgres -Fc mydb > backup.dump`
Restore de SQL	`psql -U postgres -d mydb < backup.sql`
Restore de dump	`pg_restore -U postgres -d mydb backup.dump`
Backup remoto	`pg_dump -h remote.host -U user -d mydb \| gzip > backup.sql.gz`
pg_basebackup	`pg_basebackup -U postgres -D /backup/basebackup -Ft -z`

🔧 Administração

Ver configurações	`SHOW ALL;`
Alterar configuração	`ALTER SYSTEM SET shared_buffers = '256MB';`
Reload config	`SELECT pg_reload_conf();`
Ver logs	`tail -f /var/log/postgresql/postgresql-13-main.log`
Ver tamanho do banco	`SELECT pg_size_pretty(pg_database_size('mydb'));`
Listar extensões	`SELECT * FROM pg_extension;`
Instalar extensão	`CREATE EXTENSION IF NOT EXISTS "uuid-ossp";`