PostgreSQL ve psql Temel Komutları

psql -U app -d appdb                         # lokal
psql -h db.example.com -p 5432 -U app -d appdb
psql "postgresql://app:secret@localhost:5432/appdb"
sudo -u postgres psql                        # süper kullanıcı olarak (Linux)
docker compose exec db psql -U app -d appdb  # container içinde

# host:port:database:user:password
localhost:5432:appdb:app:secret

psql -U app -d appdb -c "SELECT count(*) FROM users;"
psql -U app -d appdb -f migration.sql
psql -U app -d appdb -At -c "SELECT email FROM users" > emails.txt   # sade çıktı

CREATE ROLE app WITH LOGIN PASSWORD 'secret';
CREATE DATABASE appdb OWNER app;

-- Sadece okuma yetkili kullanıcı
CREATE ROLE reporter WITH LOGIN PASSWORD 'secret2';
GRANT CONNECT ON DATABASE appdb TO reporter;
GRANT USAGE ON SCHEMA public TO reporter;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporter;

ALTER ROLE app WITH PASSWORD 'new-secret';
DROP ROLE reporter;

CREATE TABLE users (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    metadata    JSONB DEFAULT '{}'::jsonb,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE users RENAME COLUMN name TO full_name;
CREATE INDEX idx_users_created_at ON users (created_at);
CREATE INDEX CONCURRENTLY idx_users_meta ON users USING gin (metadata);
TRUNCATE users RESTART IDENTITY;
DROP TABLE IF EXISTS users;

-- Upsert
INSERT INTO users (email, name) VALUES ('[email protected]', 'A')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- Eklenen/güncellenen satırı geri döndür
UPDATE users SET last_login = now() WHERE id = 42 RETURNING *;

-- JSONB sorgusu
SELECT * FROM users WHERE metadata->>'plan' = 'pro';
SELECT * FROM users WHERE metadata @> '{"beta": true}';

-- Sayfalama
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40;

-- Gruplu sayım
SELECT date_trunc('day', created_at) AS day, count(*)
FROM users GROUP BY 1 ORDER BY 1 DESC;

# Tek veritabanı, düz SQL
pg_dump -U app -d appdb > appdb.sql

# Sıkıştırılmış custom format (önerilen)
pg_dump -U app -d appdb -Fc -f appdb.dump

# Sadece şema / sadece belirli tablo
pg_dump -U app -d appdb --schema-only > schema.sql
pg_dump -U app -d appdb -t users -Fc -f users.dump

# Geri yükleme
psql -U app -d appdb < appdb.sql
pg_restore -U app -d appdb --clean --if-exists appdb.dump
pg_restore -U app -d appdb -j 4 appdb.dump        # 4 paralel iş

# Tüm cluster (roller dahil)
pg_dumpall -U postgres > all.sql

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE created_at > now() - interval '7 days';

-- Aktif sorgular
SELECT pid, usename, state, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

-- Sorguyu iptal et / bağlantıyı sonlandır
SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

-- Tablo ve index boyutları
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

SELECT pg_size_pretty(pg_database_size('appdb'));

VACUUM (VERBOSE, ANALYZE) users;   -- ölü satırları temizle, istatistikleri güncelle
ANALYZE;                           -- tüm tabloların planlayıcı istatistikleri
REINDEX INDEX CONCURRENTLY idx_users_created_at;
SHOW max_connections;
SHOW config_file;
SELECT version();