PostgreSQL Veritabanı ve Şema Oluşturma: CREATE DATABASE, CREATE SCHEMA

Küme, veritabanı, şema: hangisi ne zaman?

Kılavuza göre rol, veritabanı ve tablespace adları gibi az sayıda nesne küme düzeyinde tanımlıdır; bu yüzden aynı kümedeki iki veritabanında aynı adlı iki farklı rol olamaz. Veritabanları birbirinden yalıtılmıştır: bir bağlantı isteğinde veritabanı adı belirtilir ve aynı bağlantıyla ikinci bir veritabanına erişilemez. Şemalar ise bu kadar katı ayrılmaz; yetkisi olan bir kullanıcı aynı veritabanındaki bütün şemalardaki nesneleri tek sorguda birleştirebilir.

Kılavuzun önerisi şöyle özetlenebilir: birbirinden habersiz olması gereken ilgisiz projeleri veya kullanıcıları ayrı veritabanlarına koyun; birbirinin verisini kullanması gereken ilişkili projeleri tek veritabanında ama ayrı şemalarda tutun. Belge ayrıca bir kümede çok sayıda veritabanı açmanın yedekleme ve kurtarma açısından dezavantajlarına dikkat çeker; bütün veritabanları ortak WAL'ı paylaştığı için kullanıcı gözünden yalıtılmış olsalar da yönetici gözünden birbirine bağlıdırlar.

CREATE DATABASE aslında bir kopyalamadır

Yeni bir veritabanı oluşturmak için sunucunun çalışıyor olması, sizin de süper kullanıcı olmanız ya da CREATEDB yetkisine sahip olmanız gerekir. Komutu çalıştıran rol yeni veritabanının sahibi olur; sahip daha sonra veritabanını, içindeki nesneler başka rollere ait olsa bile silebilir. İlk veritabanı her zaman initdb tarafından oluşturulan postgres'tir; ilk normal veritabanınızı açmak için ona bağlanırsınız.

Belgenin vurguladığı ayrıntı şudur: CREATE DATABASE varsayılan olarak template1 veritabanını kopyalar. template1'e eklediğiniz her nesne bundan sonra açılan her veritabanına geçer. template0 ise template1'in ilk hâlinin temiz bir kopyasıdır ve hiç değiştirilmemesi gerekir. Bunu deneme kümemizde gösterdik; template1'e bir tablo ekledik, sonra iki veritabanı açtık:

\c template1
CREATE TABLE ortak_ayar (anahtar text PRIMARY KEY, deger text);
INSERT INTO ortak_ayar VALUES ('para_birimi', 'TRY');
\c postgres
CREATE DATABASE ornek_vt;
CREATE DATABASE ornek_vt_temiz TEMPLATE template0;
\c ornek_vt
SELECT * FROM ortak_ayar;
\c ornek_vt_temiz
SELECT * FROM ortak_ayar;

   anahtar   | deger 
-------------+-------
 para_birimi | TRY
(1 row)

You are now connected to database "ornek_vt_temiz" as user "postgres".
psql:vt1.sql:13: ERROR:  relation "ortak_ayar" does not exist
LINE 1: SELECT * FROM ortak_ayar;
                      ^

ornek_vt tabloyu ve içindeki satırı devraldı, template0'dan açılan veritabanı ise temiz kaldı. Denemeden sonra tabloyu template1'den sildik; şablona istemeden nesne eklemek yaygın bir hatadır ve sonradan açılan bütün veritabanlarını etkiler. Belge, template1'i kirletirseniz onu silip template0'dan yeniden oluşturabileceğinizi de söyler; silmek için önce datistemplate bayrağının false yapılması gerekir. Kopyalamaya giren ve girmeyen şeyler de belgede açıktır: nesneler ve veriler kopyalanır, ama veritabanı düzeyindeki GRANT yetkileri ve ALTER DATABASE ... SET ile verilen ayarlar kopyalanmaz.

Farklı locale veya encoding için template0 gerekir

Yeni veritabanının kodlaması ve locale ayarları şablonla aynı olmak zorundadır; tek istisna şablon olarak template0 kullanmaktır. Bunun nedeni, template1'de farklı kodlamaya uymayan veri ya da sıralama düzeni değişince bozulacak indeksler bulunabilmesidir. Deneme kümemiz builtin sağlayıcıyla oluşturulmuştu; ICU tabanlı Türkçe bir veritabanı açmayı denedik:

CREATE DATABASE ornek_tr LOCALE_PROVIDER icu ICU_LOCALE 'tr-TR';
CREATE DATABASE ornek_tr LOCALE_PROVIDER icu ICU_LOCALE 'tr-TR' TEMPLATE template0;

psql:vt1.sql:18: ERROR:  new locale provider (icu) does not match locale provider of the template database (builtin)
HINT:  Use the same locale provider as in the template database, or use template0 as template.
CREATE DATABASE

İpucu satırı çözümü zaten söylüyor. Veritabanlarının sağlayıcısını, locale'ini ve şablon bayraklarını pg_database katalogundan görebilirsiniz:

SELECT datname, datlocprovider, datlocale, datistemplate, datallowconn
FROM pg_database ORDER BY datname;

    datname     | datlocprovider | datlocale | datistemplate | datallowconn 
----------------+----------------+-----------+---------------+--------------
 ornek_psql     | b              | C.UTF-8   | f             | t
 ornek_tr       | i              | tr-TR     | f             | t
 ornek_vt       | b              | C.UTF-8   | f             | t
 ornek_vt_temiz | b              | C.UTF-8   | f             | t
 postgres       | b              | C.UTF-8   | f             | t
 template0      | b              | C.UTF-8   | t             | f
 template1      | b              | C.UTF-8   | t             | t
(7 rows)

template0 satırındaki datallowconn = f değeri bu veritabanına bağlanılmasını, dolayısıyla değiştirilmesini engeller. datistemplate = t ise CREATEDB yetkisi olan herkesin o veritabanını kopyalayabileceği anlamına gelir; bayrak kapalıysa yalnızca süper kullanıcılar ve sahibi kopyalayabilir. Türkçe sıralama ve büyük/küçük harf dönüşümünün hangi sağlayıcıda nasıl çalıştığını collation ve Türkçe sıralama yazısında ayrıntılı gösteriyoruz.

CREATE DATABASE seçenekleri ve kısıtları

Komutun özetindeki seçeneklerin hepsi isteğe bağlıdır ve istenen sırada yazılabilir. En sık kullanılanlar OWNER (sahip rol), TEMPLATE, ENCODING, LOCALE ve sağlayıcıya özel ICU_LOCALE, BUILTIN_LOCALE, LOCALE_PROVIDER, ayrıca CONNECTION LIMIT ve ALLOW_CONNECTIONS seçenekleridir. STRATEGY seçeneği kopyalamanın nasıl yapılacağını belirler: varsayılan WAL_LOG küçük şablonlar için en verimli yoldur, eski FILE_COPY yöntemi ise WAL hacmini azaltır ama öncesinde ve sonrasında checkpoint yapılmasını gerektirir. Belge, bağlantı sınırının yaklaşık olarak uygulandığını ve süper kullanıcılara uygulanmadığını da not eder.

İki kısıtı mutlaka bilin. Birincisi, CREATE DATABASE bir transaction bloğu içinde çalışamaz. İkincisi, kopyalanan şablona o anda başka bir oturum bağlıysa komut başarısız olur; belge bu yüzden başka veritabanlarını şablon olarak kullanmanın genel amaçlı bir kopyalama aracı olmadığını vurgular:

BEGIN;
CREATE DATABASE olmaz;
ROLLBACK;
CREATE DATABASE kopya TEMPLATE ornek_vt_temiz;  -- başka bir oturum ornek_vt_temiz'e bağlıyken

BEGIN
psql:vt1.sql:24: ERROR:  CREATE DATABASE cannot run inside a transaction block
ROLLBACK
psql:vt2.sql:2: ERROR:  source database "ornek_vt_temiz" is being accessed by other users
DETAIL:  There is 1 other session using the database.

createdb ve dropdb araçları

Kabuktan veritabanı açmak için createdb vardır. Belge bu aracın sihirli bir yanı olmadığını, postgres veritabanına bağlanıp aynı CREATE DATABASE komutunu gönderdiğini söyler; argümansız çalıştırılırsa işletim sistemi kullanıcı adıyla bir veritabanı açar. -e seçeneği gönderilen SQL'i gösterir, ikinci argüman da veritabanına açıklama ekler:

createdb -h 127.0.0.1 -p 46410 -U postgres -e -T template0 -E UTF8 magaza "Magaza uygulamasi"
dropdb -h 127.0.0.1 -p 46410 -U postgres -e --if-exists magaza

SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE magaza ENCODING 'UTF8' TEMPLATE template0;
COMMENT ON DATABASE magaza IS 'Magaza uygulamasi';
SELECT pg_catalog.set_config('search_path', '', false);
DROP DATABASE IF EXISTS magaza;

Çıktı, iki aracın da işe önce arama yolunu boşaltarak başladığını gösteriyor; bu, güvenli olmayan bir şema düzeninde başka bir kullanıcının nesnelerinin araya girmesini önleyen bir önlemdir. Açıklamayı ASCII yazdık; Windows komut satırında Türkçe karakterli bir açıklama denediğimizde veritabanı oluştu ama açıklama invalid byte sequence for encoding "UTF8" hatasıyla eklenemedi. dropdb, createdb'den farklı olarak argümansız çalıştırıldığında kullanıcı adıyla aynı veritabanını silmeye kalkmaz; silinecek adı her zaman açıkça vermeniz gerekir.

DROP DATABASE: geri dönüşü yok

DROP DATABASE veritabanının katalog kayıtlarını ve veri dizinini siler, içindeki bütün nesneler gider ve işlem geri alınamaz. Yalnızca sahibi veya süper kullanıcı çalıştırabilir, transaction bloğu içinde çalışmaz ve hedef veritabanına bağlıyken çalıştırılamaz; bu yüzden postgres veya başka bir veritabanına bağlanıp komutu oradan verirsiniz. Başka oturumlar hedef veritabanına bağlıysa komut başarısız olur; WITH (FORCE) seçeneği bu oturumları sonlandırmayı dener:

\c ornek_tr
DROP DATABASE ornek_tr;
\c postgres
DROP DATABASE ornek_vt_temiz;               -- başka bir oturum bağlıyken
DROP DATABASE ornek_vt_temiz WITH (FORCE);

ERROR:  cannot drop the currently open database
psql:vt2.sql:1: ERROR:  database "ornek_vt_temiz" is being accessed by other users
DETAIL:  There is 1 other session using the database.
DROP DATABASE

Bağlı oturum tarafında ise şu mesajlar göründü:

FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
connection to server was lost

Denemede ikinci komut hatayı hemen değil, birkaç saniye bekledikten sonra verdi. FORCE seçeneği hazırlanmış transaction'lar, etkin mantıksal replikasyon slotları veya abonelikler varsa bağlantıları sonlandırmaz; ayrıca yalnızca sizin pg_terminate_backend ile sonlandırma yetkiniz olan bağlantıları kapatır. Bir veritabanını silmeden önce yedeğinin alındığından emin olun; pg_dump ve pg_restore yazısı bunun uygulamalı yolunu gösteriyor.

Veritabanına özel ayarlar: ALTER DATABASE ... SET

Sunucu parametrelerinin çoğu için veritabanına özel varsayılan değer tanımlanabilir. Belgeye göre ALTER DATABASE ad SET parametre TO değer ayarı hemen uygulamaz; o veritabanına sonradan açılan her oturumun başında SET çalıştırılmış gibi davranır ve kullanıcı oturum içinde bunu yine değiştirebilir. Geri almak için RESET kullanılır. Uygulamaya özel bir arama yolu tanımlamak tipik bir örnektir:

ALTER DATABASE ornek_vt SET search_path TO uygulama, public;
\c ornek_vt
SHOW search_path;
\c postgres
\drds * ornek_vt

ALTER DATABASE
You are now connected to database "ornek_vt" as user "postgres".
   search_path    
------------------
 uygulama, public
(1 row)

You are now connected to database "postgres" as user "postgres".
                List of settings
 Role | Database |           Settings           
------+----------+------------------------------
      | ornek_vt | search_path=uygulama, public
(1 row)

Şemalar: aynı veritabanında ayrı ad alanları

Şema, tabloların, view'ların, fonksiyonların ve veri tiplerinin içinde yaşadığı bir ad alanıdır; belge onu işletim sistemindeki dizinlere benzetir, tek farkla: şemalar iç içe konamaz. Farklı şemalarda aynı adlı tablolar çakışmadan bulunabilir. Bir nesneye şemasıyla birlikte erişmek için nitelikli ad (şema.tablo) yazılır. Aşağıdaki örnekte iki şemada fatura adlı iki ayrı tablo oluşturduk:

CREATE SCHEMA muhasebe;
CREATE SCHEMA depo;
CREATE TABLE muhasebe.fatura (no integer PRIMARY KEY, tutar numeric(12,2));
CREATE TABLE depo.fatura (no integer PRIMARY KEY, palet integer);
INSERT INTO muhasebe.fatura VALUES (1, 1250.00);
INSERT INTO depo.fatura VALUES (1, 4);
SHOW search_path;
SELECT current_schema();
SELECT * FROM fatura;

   search_path   
-----------------
 "$user", public
(1 row)

 current_schema 
----------------
 public
(1 row)

psql:vt3.sql:10: ERROR:  relation "fatura" does not exist
LINE 1: SELECT * FROM fatura;
                      ^

Arama yolunda yalnızca kullanıcı adıyla aynı adlı şema (yok olduğu için atlanır) ve public olduğundan nitelikli olmayan fatura bulunamadı. Belgeye göre başka şemalarda aynı adlı tablo olsa bile, arama yolunda eşleşme yoksa hata verilir.

search_path: ilk eşleşme kazanır

Arama yolu, nitelikli olmayan adların sırayla arandığı şema listesidir ve ilk eşleşme kullanılır. Listedeki ilk mevcut şemaya geçerli şema (current schema) denir; şema belirtmeden oluşturulan yeni nesneler oraya yazılır. pg_catalog şeması listede yazılmasa bile örtük olarak en önce aranır; böylece yerleşik tipler ve fonksiyonlar her zaman bulunur. Bu yüzden pg_class gibi bir katalog tablosu public.pg_class olarak bulunamaz ama nitelikli olmayan adla her zaman bulunur:

SET search_path TO depo, muhasebe, public;
SELECT * FROM fatura;
SELECT current_schema();
CREATE TABLE sevkiyat (id integer);
\dt

 no | palet 
----+-------
  1 |     4
(1 row)

 current_schema 
----------------
 depo
(1 row)

CREATE TABLE
            List of tables
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 depo   | fatura   | table | postgres
 depo   | sevkiyat | table | postgres
(2 rows)

İki ilginç sonuç var. Birincisi, sevkiyat tablosu şema belirtmediğimiz hâlde depo şemasına yazıldı, çünkü yoldaki ilk şema oydu. İkincisi, \dt çıktısında muhasebe.fatura görünmedi: aynı adlı depo.fatura yolda daha önce geldiği için muhasebe şemasındaki tablo bu adla görünür değil. psql'in görünürlük kuralını psql komutları yazısında anlattık. Yol yalnızca muhasebe yapıldığında aynı sorgu diğer tabloyu döndürdü:

SET search_path TO muhasebe;
SELECT * FROM fatura;
SELECT count(*) > 0 AS katalog_bulundu FROM pg_class;
RESET search_path;

 no |  tutar  
----+---------
  1 | 1250.00
(1 row)

 katalog_bulundu 
-----------------
 t
(1 row)

Arama yolu tablolar dışında veri tipi, fonksiyon ve operatör adları için de aynı biçimde çalışır. Belge, bir şemayı arama yoluna eklemenin, o şemada CREATE yetkisi olan herkese güvenmek anlamına geldiğini vurgular: kötü niyetli bir kullanıcı yolunuzdaki bir şemaya sizin kullandığınız bir fonksiyonla aynı adlı nesne koyarak sorgularınızın davranışını değiştirebilir. Güvenlik açısından hassas kodlarda nitelikli ad yazmak ya da arama yolunu fonksiyon tanımında sabitlemek bu yüzden önerilir; fonksiyonlar için bunun nasıl yapıldığı CREATE FUNCTION yazısında.

Şema oluşturma, silme ve kurallar

CREATE SCHEMA ad AUTHORIZATION rol şemayı başka bir role ait olarak oluşturur; şema adı verilmezse rol adı kullanılır. IF NOT EXISTS varsa yalnızca bir bildirim verilir. Şemayla birlikte içindeki nesneleri de tek komutta oluşturabilirsiniz; belgeye göre bu alt komutlarda yalnızca CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER ve GRANT kabul edilir ve alt komutlar noktalı virgülle bitmez. pg_ ile başlayan şema adları sisteme ayrılmıştır. Boş olmayan bir şemayı silmek için CASCADE gerekir:

CREATE SCHEMA pg_benim;
DROP SCHEMA depo;
DROP SCHEMA depo CASCADE;
CREATE SCHEMA rapor
    CREATE TABLE gunluk (gun date, ciro numeric)
    CREATE VIEW son_gun AS SELECT * FROM gunluk ORDER BY gun DESC LIMIT 1;

psql:vt3.sql:24: ERROR:  unacceptable schema name "pg_benim"
DETAIL:  The prefix "pg_" is reserved for system schemas.
psql:vt3.sql:26: ERROR:  cannot drop schema depo because other objects depend on it
DETAIL:  table depo.fatura depends on schema depo
table depo.sevkiyat depends on schema depo
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
psql:vt3.sql:27: NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table depo.fatura
drop cascades to table depo.sevkiyat
DROP SCHEMA
CREATE SCHEMA

Belge, sistem tablolarının adlarının hep pg_ ile başlayacağını, bu yüzden kendi tablolarınıza da bu öneki vermemenizi önerir; aksi hâlde ileride aynı adda bir sistem tablosu eklenirse nitelikli olmayan adınız sistem tablosuna çözülebilir.

public şeması ve PostgreSQL 15 sonrası güvenli düzen

Her yeni veritabanında public adlı bir şema bulunur ve şema belirtmeden oluşturulan nesneler varsayılan arama yolu sayesinde oraya gider. Belgeye göre PostgreSQL 14 ve öncesinden yükseltilmiş veritabanlarında herkesin public şemasında nesne oluşturma (CREATE) yetkisi vardır; PostgreSQL 15 ve sonrasının varsayılan yapılandırması ise bunu vermez, herkes yalnızca USAGE yetkisine sahiptir. Bunu sıradan bir rolle denedik:

CREATE ROLE ali LOGIN;
\dn+ public
SET ROLE ali;
CREATE TABLE notlar (metin text);
RESET ROLE;
CREATE SCHEMA AUTHORIZATION ali;
SET ROLE ali;
CREATE TABLE notlar (metin text);
SELECT current_schema();

                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   | 
(1 row)

SET
psql:vt4.sql:4: ERROR:  permission denied for schema public
LINE 1: CREATE TABLE notlar (metin text);
                     ^
RESET
CREATE SCHEMA
SET
CREATE TABLE
 current_schema 
----------------
 ali
(1 row)

Yetki satırındaki =U/pg_database_owner herkesin (PUBLIC) yalnızca U yani USAGE hakkı olduğunu gösteriyor; UC ise veritabanı sahibinin hem kullanma hem oluşturma hakkı. ali için kendi adında bir şema oluşturduğumuzda aynı komut başarılı oldu, çünkü arama yolunun ilk öğesi olan "$user" artık var olan bir şemaya çözüldü ve geçerli şema ali oldu. Belgenin önerdiği güvenli düzenlerden biri tam olarak budur: hiçbir şemada herkese açık CREATE yetkisi bırakmamak ve nesne oluşturması gereken her kullanıcıya kendi adında bir şema açmak.

Eski bir sürümden yükseltilmiş bir veritabanında aynı düzeni kurmak için belge REVOKE CREATE ON SCHEMA public FROM PUBLIC; komutunu verir. Burada ilk public şemanın adı, ikinci PUBLIC ise herkesi temsil eden anahtar kelimedir. Belge, birbirine güvenen az sayıda kullanıcının olduğu durumlar dışında herkese public şemasında oluşturma yetkisi vermenin hiçbir zaman güvenli bir düzen olmadığını da açıkça belirtir. Şema ve tablo yetkilerinin tamamı GRANT ve REVOKE, rollerin kendisi kullanıcı ve rol yönetimi yazısında.

Sık yapılan hatalar

• template1'e yanlışlıkla nesne eklemek: Sonradan açılan her veritabanı o nesneyi taşır; şablona yalnızca bilerek ve her veritabanında olmasını istediğiniz şeyleri ekleyin.
• Farklı locale ile template1 kullanmak: Kodlama veya locale şablondan farklıysa TEMPLATE template0 yazmanız gerekir.
• Bir dökümü kirli bir veritabanına yüklemek: Belge, pg_dump çıktısının template0'dan açılmış temiz bir veritabanına yüklenmesini önerir; aksi hâlde template1'e eklenmiş nesnelerle çakışma olabilir.
• Tablonun hangi şemada oluştuğunu bilmemek: Şema belirtmeden yazılan CREATE TABLE arama yolundaki ilk mevcut şemaya gider; emin olmak için SELECT current_schema(); çalıştırın veya nitelikli ad yazın.
• Uygulama kullanıcısına public üzerinde CREATE vermek: Bu, arama yolunu paylaşan diğer kullanıcıları riske atar; uygulamaya ayrı bir şema açın.
• Veritabanı yerine şemayı yalıtım sınırı sanmak: Şemalar yetkiyle ayrılır ama aynı bağlantıdan erişilebilir; birbirini hiç görmemesi gereken müşteriler için ayrı veritabanı veya row level security düşünün.

Sık Sorulan Sorular

Bir veritabanını başka bir adla nasıl kopyalarım?

Kaynak veritabanına kimse bağlı değilse CREATE DATABASE yeni TEMPLATE kaynak; çalışır, ama belge bunun genel amaçlı bir kopyalama aracı olmadığını belirtir. Çalışan bir sistemde kopya almak için pg_dump ve pg_restore kullanmak daha güvenlidir.

MySQL'deki database kavramı PostgreSQL'de şemaya mı karşılık gelir?

Kullanım amacına göre değişir. PostgreSQL'de tek bağlantıyla birden çok veritabanına erişilemez, birden çok şemaya ise erişilebilir; tabloları sorgularda birleştirmeniz gerekiyorsa şema, tamamen ayrı tutmanız gerekiyorsa veritabanı daha yakın karşılıktır. İki sistemin genel karşılaştırması için MySQL ve PostgreSQL yazısına bakın.

Arama yolunu kalıcı olarak nasıl değiştiririm?

Bir veritabanı için ALTER DATABASE ad SET search_path TO ..., bir rol için ALTER ROLE ad SET search_path TO ... kullanılır; sunucu geneli için postgresql.conf dosyasındaki search_path parametresi değiştirilir. SET search_path yalnızca o oturumu etkiler.

Bir veritabanını silmeden önce kimlerin bağlı olduğunu nasıl görürüm?

SELECT datname, usename, state FROM pg_stat_activity WHERE datname = 'ad'; sorgusu bağlı oturumları listeler. Oturumları tek tek sonlandırmayı ve izlemeyi pg_stat_activity yazısında anlatıyoruz.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: PostgreSQL öğrenme rehberi · PostgreSQL kurulumu · CREATE TABLE ve ALTER TABLE · GRANT ve REVOKE · Kullanıcı ve rol yönetimi · Collation ve Türkçe sıralama