pgAdmin Kurulumu ve PostgreSQL Veritabanı Yönetimi

yazar

PostgreSQL kurulumunu tamamladıktan sonra çoğu sysadmin’in aklına gelen ilk soru şu oluyor: “Bunu komut satırından mı yönetirim, yoksa görsel bir araç mı kullansam?” Cevap tabii ki ikisi de, ama özellikle ekip ortamlarında, müşteriye sunum yaparken veya karmaşık sorguları debug ederken pgAdmin hayat kurtarıcı oluyor. Bu yazıda pgAdmin’i sıfırdan kurarak production ortamına hazır hale getireceğiz ve gerçek dünyada kullanacağınız senaryoları ele alacağız.

pgAdmin Nedir ve Neden Kullanmalısınız?

pgAdmin, PostgreSQL için açık kaynaklı, web tabanlı bir yönetim arayüzüdür. Eski versiyonlarda masaüstü uygulaması olarak gelirken pgAdmin 4 ile birlikte tamamen web tabanlı bir mimiriye geçti. Bu geçiş başlangıçta eleştiri aldı ama şu an gelinen noktada oldukça stabil ve güçlü bir araç haline geldi.

Neden kullanmalısınız? Birkaç somut senaryo düşünelim:

  • Yeni bir developer ekibe katıldı, psql komutlarına alışkın değil ama veritabanını incelemesi gerekiyor
  • Bir tabloda 50 kolon var ve hangi kolonun hangi tipi tuttuğunu hızlıca görmek istiyorsunuz
  • Slow query analizi yapıyorsunuz ve EXPLAIN planını görsel olarak incelemek zaman kazandırıyor
  • Backup/restore işlemini otomatize etmek ve sonuçları takip etmek istiyorsunuz

Bunların hepsini psql ile de yapabilirsiniz, ama pgAdmin bu işleri çok daha az mental yorgunlukla tamamlatıyor.

Kurulum Yöntemleri

pgAdmin kurulumu için birkaç farklı yol var. Biz production ortamına en uygun olan iki yöntemi ele alacağız: paket deposu ile doğrudan kurulum ve Docker ile kurulum.

Paket Deposu ile Kurulum (Ubuntu/Debian)

Bu yöntem, pgAdmin’i bir sistem servisi olarak çalıştırmak istediğinizde idealdir.

# pgAdmin paket deposu imzalama anahtarını ekle
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | 
  sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

# Depoyu sources listesine ekle
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] 
  https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" 
  > /etc/apt/sources.list.d/pgadmin4.list'

# Paket listesini güncelle ve kur
sudo apt update
sudo apt install pgadmin4-web -y

Kurulumdan sonra pgAdmin’i web modu için yapılandırmanız gerekiyor:

# pgAdmin web kurulum scriptini çalıştır
sudo /usr/pgadmin4/bin/setup-web.sh

Bu script size birkaç soru soracak:

  • Email adresi: pgAdmin’e giriş için kullanacağınız admin e-posta adresi
  • Şifre: Admin hesabı için güçlü bir şifre

Script ayrıca Apache veya Nginx’i otomatik olarak yapılandırma seçeneği sunuyor. Eğer zaten çalışan bir web sunucunuz varsa “no” deyip manuel yapılandırma yapmak daha güvenli.

Nginx ile Manuel Yapılandırma

# Nginx için pgAdmin konfigürasyon dosyası oluştur
sudo nano /etc/nginx/sites-available/pgadmin4
server {
    listen 80;
    server_name pgadmin.sirketiniz.com;

    # HTTP'yi HTTPS'e yönlendir
    return 301 https://$server_name$request_uri;
}

server {
    listen 443 ssl;
    server_name pgadmin.sirketiniz.com;

    ssl_certificate /etc/ssl/certs/pgadmin.crt;
    ssl_certificate_key /etc/ssl/private/pgadmin.key;

    location /pgadmin4/ {
        include proxy_params;
        proxy_pass http://unix:/tmp/pgadmin4.sock;
        proxy_set_header X-Script-Name /pgadmin4;
    }
}
# Site'ı aktif et ve Nginx'i yeniden yükle
sudo ln -s /etc/nginx/sites-available/pgadmin4 /etc/nginx/sites-enabled/
sudo nginx -t && sudo systemctl reload nginx

Docker ile Kurulum

Eğer container ortamı kullanıyorsanız pgAdmin’in resmi Docker imajı oldukça kullanışlı. Özellikle test ortamları veya izole bir yönetim arayüzü istediğinizde Docker tercih edilebilir.

# pgAdmin container'ı ayağa kaldır
docker run -d 
  --name pgadmin4 
  -p 5050:80 
  -e [email protected] 
  -e PGADMIN_DEFAULT_PASSWORD=GucluSifre123! 
  -v pgadmin_data:/var/lib/pgadmin 
  --restart unless-stopped 
  dpage/pgadmin4:latest

Docker Compose kullanıyorsanız PostgreSQL ile birlikte şöyle bir yapı kurabilirsiniz:

version: '3.8'

services:
  postgres:
    image: postgres:16
    container_name: postgres_db
    environment:
      POSTGRES_DB: proddb
      POSTGRES_USER: dbadmin
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    volumes:
      - postgres_data:/var/lib/postgresql/data
    networks:
      - db_network
    restart: unless-stopped

  pgadmin:
    image: dpage/pgadmin4:latest
    container_name: pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD}
      PGADMIN_CONFIG_SERVER_MODE: 'True'
      PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED: 'True'
    ports:
      - "5050:80"
    volumes:
      - pgadmin_data:/var/lib/pgadmin
    networks:
      - db_network
    depends_on:
      - postgres
    restart: unless-stopped

volumes:
  postgres_data:
  pgadmin_data:

networks:
  db_network:
    driver: bridge

Bu yapıda .env dosyası kullanarak şifreleri git’e commit etmeden yönetebilirsiniz. .env dosyasını her zaman .gitignore‘a eklemeyi unutmayın.

İlk Bağlantıyı Kurma

pgAdmin’e tarayıcıdan erişip giriş yaptıktan sonra ilk yapmanız gereken bir PostgreSQL sunucusu eklemek. Sol panelde “Servers”a sağ tıklayıp “Register > Server” seçeneğini kullanabilirsiniz.

General sekmesi:

  • Name: Sunucunuzu tanımlayıcı bir isim verin, örneğin “Production-DB-01”

Connection sekmesi:

  • Host name/address: PostgreSQL sunucusunun IP adresi veya hostname’i
  • Port: Varsayılan 5432
  • Maintenance database: postgres
  • Username: Bağlanacağınız kullanıcı
  • Password: Kullanıcı şifresi
  • Save password: Evet veya hayır, ortama göre karar verin

SSH Tunnel sekmesi (eğer doğrudan bağlanamıyorsanız):

  • Use SSH tunneling: Aktif edin
  • Tunnel host: SSH sunucusunun adresi
  • Username: SSH kullanıcı adı
  • Authentication: Password veya Identity file

Production ortamlarında PostgreSQL sunucunuzu doğrudan internete açmak yerine SSH tunnel kullanmak çok daha güvenli bir yaklaşım.

Temel Yönetim İşlemleri

Veritabanı Oluşturma ve Yönetme

pgAdmin üzerinden yeni bir veritabanı oluşturmak birkaç tıklamayla oluyor, ama arka planda ne olduğunu anlamak önemli. Bir veritabanı oluştururken dikkat edilmesi gereken başlıca parametreler:

  • Owner: Veritabanının sahibi olan rol
  • Encoding: Karakter seti, Türkçe için UTF8 şart
  • Collation: Sıralama kuralları, tr_TR.UTF-8 tercih edilebilir
  • Character type: Karakter sınıflandırması

pgAdmin’in SQL editörünü açıp şunu da çalıştırabilirsiniz:

-- Yeni bir uygulama veritabanı oluştur
CREATE DATABASE uygulama_db
    WITH 
    OWNER = app_user
    ENCODING = 'UTF8'
    LC_COLLATE = 'tr_TR.UTF-8'
    LC_CTYPE = 'tr_TR.UTF-8'
    TEMPLATE = template0
    CONNECTION LIMIT = 100;

-- Veritabanına yorum ekle (dokümantasyon için faydalı)
COMMENT ON DATABASE uygulama_db IS 'Ana uygulama veritabanı - v2.0';

Kullanıcı ve Rol Yönetimi

pgAdmin’deki “Login/Group Roles” bölümü, kullanıcı yönetimini oldukça kolaylaştırıyor. Bir uygulama için gerekli minimum yetkili kullanıcı oluşturmak iyi pratik:

-- Salt okunur kullanıcı oluştur (raporlama için)
CREATE ROLE rapor_user WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOINHERIT
    NOREPLICATION
    CONNECTION LIMIT 10
    PASSWORD 'RaporSifresi456!';

-- Belirli veritabanına bağlanma yetkisi ver
GRANT CONNECT ON DATABASE uygulama_db TO rapor_user;

-- Şemadaki tüm tablolara okuma yetkisi ver
GRANT USAGE ON SCHEMA public TO rapor_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rapor_user;

-- Gelecekte oluşturulacak tablolara da otomatik yetki ver
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO rapor_user;

Bu yaklaşım, özellikle BI araçları veya raporlama sistemleri için veritabanına bağlantı kurulurken minimum yetki prensibini uygulamanızı sağlar.

Query Tool ile Çalışmak

pgAdmin’in en çok kullandığım özelliği Query Tool. Bir veritabanına sağ tıklayıp “Query Tool” seçtiğinizde tam özellikli bir SQL editörü açılıyor.

EXPLAIN ANALYZE Kullanımı

Yavaş çalışan sorgularla uğraşırken EXPLAIN ANALYZE çıktısını pgAdmin’de görsel olarak inceleyebilirsiniz. “Explain” butonuna bastığınızda sorgu planı hem metin hem de görsel ağaç formatında geliyor.

-- Yavaş sorgu örneği ve analizi
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT 
    u.id,
    u.email,
    COUNT(o.id) as siparis_sayisi,
    SUM(o.toplam_tutar) as toplam_harcama
FROM kullanicilar u
LEFT JOIN siparisler o ON o.kullanici_id = u.id
WHERE o.olusturma_tarihi >= NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email
HAVING COUNT(o.id) > 5
ORDER BY toplam_harcama DESC;

Bu sorgunun planını incelediğinizde Sequential Scan gördüğünüzde, ilgili kolona index eklemeniz gerektiğini anlarsınız:

-- Performans için gerekli index'leri ekle
CREATE INDEX CONCURRENTLY idx_siparisler_kullanici_tarih
ON siparisler(kullanici_id, olusturma_tarihi DESC);

CREATE INDEX CONCURRENTLY idx_siparisler_toplam_tutar
ON siparisler(toplam_tutar) WHERE toplam_tutar > 0;

CONCURRENTLY kullanımı production ortamında kritik, çünkü bu seçenek olmadan index oluşturma işlemi tabloyu kilitliyor.

Backup ve Restore İşlemleri

pgAdmin’in backup arayüzü, pg_dump ve pg_restore komutlarını görsel hale getiriyor. Ama gerçek production ortamlarında bu işlemleri script ile otomatize etmek gerekiyor.

pgAdmin Üzerinden Backup Almak

Bir veritabanına sağ tıklayıp “Backup” seçtiğinizde birkaç önemli seçenek karşınıza çıkıyor:

  • Format: Custom (önerilen), Directory, Tar, Plain
  • Compression ratio: 0-9 arası, 6 genellikle iyi denge sağlar
  • Encoding: UTF8
  • Number of jobs: Paralel backup için işçi sayısı

Custom format restore sırasında en fazla esnekliği sağladığı için tercih edilmeli. Plain SQL formatı ise backup dosyasını metin editörde incelemeniz gerektiğinde işe yarıyor.

Otomatik Backup Script

pgAdmin güzel ama backup işini script ile otomatize etmek çok daha güvenilir. Bunu systemd timer veya cron ile kullanabilirsiniz:

#!/bin/bash
# /usr/local/bin/pg_backup.sh

# Değişkenler
DB_HOST="localhost"
DB_PORT="5432"
DB_USER="backup_user"
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/pg_backup.log"

# Log fonksiyonu
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# Backup dizinini oluştur
mkdir -p "$BACKUP_DIR"

# Tüm veritabanlarını listele ve yedekle
log "Backup başlatılıyor..."

for DB in $(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -t -c 
  "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres';"); do
    
    BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.dump"
    
    pg_dump 
        -h "$DB_HOST" 
        -p "$DB_PORT" 
        -U "$DB_USER" 
        -F c 
        -Z 6 
        -f "$BACKUP_FILE" 
        "$DB"
    
    if [ $? -eq 0 ]; then
        log "Basarili: $DB -> $BACKUP_FILE ($(du -sh $BACKUP_FILE | cut -f1))"
    else
        log "HATA: $DB backup alinamadi!"
    fi
done

# Eski yedekleri temizle
find "$BACKUP_DIR" -name "*.dump" -mtime +$RETENTION_DAYS -delete
log "7 gunden eski yedekler temizlendi."
log "Backup tamamlandi."

Bu scripti cron’a eklemek için:

# /etc/cron.d/pg_backup dosyası oluştur
echo "0 2 * * * postgres /usr/local/bin/pg_backup.sh" | 
  sudo tee /etc/cron.d/pg_backup

# Script'e çalıştırma yetkisi ver
sudo chmod +x /usr/local/bin/pg_backup.sh

pgAdmin Güvenlik Ayarları

Production ortamında pgAdmin’i çalıştırırken güvenlik ciddi bir konu. Birkaç kritik ayar:

Erişim Kısıtlamaları

pgAdmin konfigürasyon dosyasına eklenebilecek güvenlik ayarları:

# /etc/pgadmin4/config_local.py dosyasını düzenle
sudo nano /etc/pgadmin4/config_local.py
# Sadece belirli IP bloklarından erişime izin ver
# (Bu pgAdmin seviyesinde değil, firewall/nginx seviyesinde yapılmalı)

# Oturum zaman aşımı süresi (saniye)
SESSION_EXPIRATION_TIME = 3600  # 1 saat

# MFA zorunluluğu
MFA_ENABLED = True
MFA_FORCE_REGISTRATION = True

# Şifre politikası
PASSWORD_LENGTH_MIN = 12

# Maksimum başarısız giriş denemesi
MAX_LOGIN_ATTEMPTS = 5

# HTTPS zorunluluğu (reverse proxy arkasında)
PREFERRED_URL_SCHEME = 'https'

# Log seviyesi
LOG_LEVEL = 20  # INFO

Firewall Kuralları

pgAdmin’e sadece yetkili IP’lerden erişim sağlamak için:

# UFW ile sadece VPN veya ofis IP'sine izin ver
sudo ufw allow from 10.10.0.0/24 to any port 443
sudo ufw allow from 203.0.113.50 to any port 443

# PostgreSQL portunu sadece uygulama sunucusuna aç
sudo ufw allow from 10.10.0.10 to any port 5432
sudo ufw deny 5432

Monitoring ve İstatistikler

pgAdmin’in “Dashboard” sekmesi, bağlı olduğunuz sunucunun anlık durumunu gösteriyor. Aktif bağlantılar, transaction sayıları, blok okuma/yazma istatistikleri burada görünüyor.

Ama daha detaylı izleme için pgAdmin’in Query Tool’unu kullanarak pg_stat_activity ve diğer sistem görünümlerini sorgulamak daha faydalı:

-- Anlık aktif sorgular ve bekleme durumları
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    LEFT(query, 100) as sorgu_ozeti,
    NOW() - query_start as sure
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY sure DESC;

Uzun süren sorguları tespit edip öldürmek de pgAdmin üzerinden yapılabiliyor, ama bu işlemi script ile de yapabilirsiniz:

-- 5 dakikadan uzun süren sorguları tespit et ve sonlandır
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '5 minutes'
  AND usename != 'postgres'  -- Süper kullanıcı sorgularına dokunma
  AND pid != pg_backend_pid();

Bu sorguyu dikkatli kullanın. Production’da önce sadece SELECT ile listeleyip hangi sorguları sonlandıracağınızı görün, sonra pg_terminate_backend çağrısını ekleyin.

Gerçek Dünya Senaryosu: Veritabanı Boyut Analizi

Bir müşteri “veritabanı çok büyüdü, disk dolmak üzere” diye aradığında ilk yapacağınız şey hangi tablo ve index’lerin yer kapladığını anlamak. pgAdmin’deki istatistik paneli bunun için yetersiz kalıyor, doğrudan sorgu çalıştırmak daha iyi:

-- En çok yer kaplayan tablolar
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as toplam_boyut,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as tablo_boyutu,
    pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as index_boyutu,
    pg_stat_user_tables.n_live_tup as canli_satir,
    pg_stat_user_tables.n_dead_tup as olmus_satir
FROM information_schema.tables
JOIN pg_stat_user_tables 
    ON tables.table_name = pg_stat_user_tables.relname
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

Bu sorgu size hangi tablonun ne kadar yer kapladığını, kaç canlı ve ölü satır içerdiğini gösteriyor. Ölü satır sayısı yüksekse VACUUM çalıştırmanız gerekiyor demektir.

-- Manuel VACUUM ANALYZE çalıştır
VACUUM ANALYZE buyuk_tablo;

-- Daha agresif temizlik için (kısa süre kilit alır)
VACUUM FULL ANALYZE buyuk_tablo;

VACUUM FULL production’da kullanırken dikkatli olun, tablo üzerine exclusive kilit alıyor ve işlem süresince tablo erişilemez oluyor.

pgAdmin’de Özelleştirmeler ve İpuçları

Günlük kullanımda işinizi kolaylaştıracak birkaç pgAdmin özelliği:

  • Saved Queries: Sık kullandığınız sorguları kaydedin. Query Tool’da “Save” ile kaydedip sonra “Load” ile açabilirsiniz.
  • ERD Tool: Veritabanı şemanızı görsel olarak görmek için kullanışlı. Özellikle yeni bir projeye dahil olduğunuzda tabloların ilişkilerini anlamak için.
  • Schema Diff: İki veritabanı şemasını karşılaştırmanıza yarıyor. Test ve production arasındaki farkları bulmak için mükemmel.
  • Search Object: Ctrl+Shift+F ile tüm veritabanı nesnelerinde arama yapabilirsiniz.
  • Auto-complete: Query Tool’da Tab ile otomatik tamamlama çalışıyor, özellikle tablo ve kolon isimlerini yazarken zaman kazandırıyor.

Bir de şunu söyleyeyim: pgAdmin bazen yavaş kalabiliyor, özellikle çok sayıda nesne olan büyük veritabanlarında. Bu durumda pgAdmin’in preferences bölümünden “Miscellaneous > Themes” altında bazı görsel öğeleri kapatmak ve bağlantı sayısını kısıtlamak yardımcı oluyor.

Sonuç

pgAdmin, PostgreSQL yönetiminde komut satırının yerini almıyor ama onu güçlü biçimde tamamlıyor. Kurulumu için paket deposu yöntemini production ortamlarında, Docker yöntemini ise geliştirme veya izole kurulumlar için öneririm. Güvenlik tarafında her zaman reverse proxy arkasına alın, HTTPS zorunlu yapın ve erişimi firewall ile kısıtlayın.

Günlük iş akışınızda Query Tool ile sorgu geliştirip optimize etmek, Dashboard ile anlık durumu takip etmek ve periyodik backup işlemlerini script ile otomatize ederek pgAdmin üzerinden doğrulamak size sağlam bir yönetim rutini oluşturuyor. En sık kullandığım özellikler arasında EXPLAIN görsel analizi ve Schema Diff öne çıkıyor, bu ikisini mutlaka deneyin.

pgAdmin kurulumunuzda sorun yaşarsanız veya farklı bir senaryo hakkında yazmamı isterseniz yorumlarda belirtin.

Yorum yapın