POSTGRESQL MİMARİSİ VE TEMEL KULLANICI İŞLEMLERİ

Logical Architecture

Postgresql Database Cluster Kavramı ile Oracle daki cluster kavramı tamamen bir birinden farklıdır. Postgresql deki cluster kavramı bir makine içerisindeki tüm database nesnelerini içermektedir.

Cluster içerisindeki her bir database in bir Object ID si vardır bunada OID denir. Cluster databasleri bu OID lere göre yönetir.

$ psql
psql (13.2)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

Şu an bizim oluşturduğumuz bir database yok dolayısıyla postgres ve template databaseler sistemde mevcut fakat bu template lere dokunmamak gerek çünkü yeni bir database oluşturulacağı zaman bu tamplate ler üzerinden yeni database oluşturulacaktır. Şimdi bir database oluşturalım sonra o database OID sine bakalım;

postgres=# create role rolename with password '123' superuser createdb login;
CREATE ROLE
postgres=# create database test123 owner = rolename;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test123   | rolename | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

postgres=#  select datname,oid from pg_database where datname='test123';
 datname |  oid
---------+-------
 test123 | 16385
(1 row)

Physical Architecture

Bir veritabanı kümesi temelde bazı alt dizinleri ve dosyaları içeren temel bir dizin olarak kabul edilebilir. Program başlatmayı gerçekleştirmek için inittab’ı çalıştırırsanız, belirtilen dizinde, genellikle PGDATA ortam değişkenine ayarlanan temel bir dizin oluşturulur.
Şekilde gösterildiği gibi, veritabanı kümesinin temel dizini $ PGDATA’dır. Veritabanı, $ PGDATA altındaki Temel dizine yerleştirilir. Sayılarla başlayan ve pg_database’i kontrol ederek bulduğumuz oid numarasına karşılık gelen birkaç dizin var. Örneğin, 16385 test123 database temsil eden OID dir

$ pwd
/database/data/base
$ ls
1  14171  14172  16385

PG_VERSION PostgreSQL’in ana sürüm numarasını içeren bir dosya
pg_hba.conf PosgreSQL’in istemci kimlik doğrulamasını kontrol etmek için bir dosya
pg_ident.conf PostgreSQL’in kullanıcı adı eşlemesini kontrol etmek için bir dosya
postgresql.conf Yapılandırma parametrelerini ayarlamak için bir dosya
postgresql.auto.conf ALTER SYSTEM’de (sürüm 9.4 veya üzeri) ayarlanan yapılandırma parametrelerini depolamak için kullanılan bir dosya
postmaster.opts Sunucunun en son başlatıldığı komut satırı seçeneklerini kaydeden bir dosya

Process Architecture

Biraz veritabanı içerisine girip kurcalayalım 🙂

Postgresql Kullanıcı İşlemleri

psql Komut Listesi

  • Login
$ psql -d veritabani -U kullanici –W  (-h bağlanılacak sunucu, -U bağlantı kurulacak kullanıcı, -p port numarası) 

# -----------------------------------------------------------------------------
# pgsql temel komutlar
# -----------------------------------------------------------------------------
 \?                      Yardım
 \h                      SQL komutlarının listesi
 \h KOMUT                SQL komutu ile ilgili yardım
 \l                      Veritabanlarını listele
 \c VERITABANI           Veritabanina geçer
 \dt                     Tablo listesi
 \dtS                    Tablo listesi, sistem tablolari dahil
 \d NESNE                Tablo, view sequence, index için detay bilgi
 \dv                     View listesi
 \di                     Indeks listesi
 \dn                     Sema listesi
 \du                     Role/user listesi
 \dp                     Yetki (privileges) listesi
 \password KULLANICI     Kullanıcı parolasını değiştirir
 \q                      Çıkış

Veritabanı Oluşturma

postgres=# create database testdb;
CREATE DATABASE

--- listeleyeliim databaseleri

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

Kullanıcı Oluşturma

postgres=# create user testuser;
CREATE ROLE

Kullanıcı oluşturdıuk ancak CREATE ROLE dedi postgresql de user ile role aynıdır create role demekle create user demek arasında hiçbir fark yoktur. (user=role=group üç kavram aynı anlama gelir). aşağıda drop edip tekrar bir role create ederek bu durumu görelim.

postgres=# drop user testuser;
DROP ROLE
postgres=# create role testuser password 'sifre123';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  | Cannot login                                               | {}

Şu an oluşturduğumuz user ya da role ün hiçbir yetkisi yok bu role yetki ataması yapalım;

Yetki İşlemleri : (GRANT)

GRANT …. TO…. komutu ile yetki verilir, REVOKE …. FROM …. komutu ile yetkiler alınır. Örneğin testdb database için tüm yetkileri testuser a verelim

postgres=# grant all privileges on database testdb to testuser;
GRANT

TABLESPACE OLUŞTURMA

Postgresql deki en büyük mantıksal depolama alanı Tablespace alanıdır. Veritabanı nesneleri bu mantıksal alanda tutulur. Bir veritabanı nesnesi oluşturulurken tablespace alanını belirtebiliriz, eğer belirtilmez ise veritabanı kurulumunda varsayılan olarak gelen tablespace alanlarını kullanırlar. Varsayılan Tablespace alanları Pg_default ve pg_global tablespace alanlarıdır. pg_global sisttem tabloları için kullanılan tablespce iken pg_default template0 ve template1 veritabanlarının ve bunlardan oluşturulan veritabanları için tablespace alanıdır. Bir veritabanı oluşturulurken varsayılann olarak template1 den kopyalanarak oluşturulur ve tablespace alanıda doğal olarak pg_default olur.

CREATE TABLESPACE tbs_testuser
  OWNER testuser
  LOCATION '/database/data';

ALTER TABLESPACE tbs_testuser
  OWNER TO testuser;

postgres=# \db+
                                      List of tablespaces
     Name     |  Owner   |    Location    | Access privileges | Options |  Size   | Description
--------------+----------+----------------+-------------------+---------+---------+-------------
 pg_default   | postgres |                |                   |         | 32 MB   |
 pg_global    | postgres |                |                   |         | 559 kB  |
 tbs_testuser | testuser | /database/data |                   |         | 0 bytes |
(3 rows)

Bu tablespace alanı içerisinde bir tabo oluşturalım

CREATE TABLE public.testtable
(
    id integer,
    "ADI" character(64),
    "SOYADI" character(64),
    "TELNO" character(20),
    PRIMARY KEY (id)
)

TABLESPACE tbs_testuser;

ALTER TABLE public.testtable
    OWNER to postgres;

--Şimdi tablespace sorgulayalım;

postgres=# \db+
                                       List of tablespaces
     Name     |  Owner   |    Location    | Access privileges | Options |   Size   | Description
--------------+----------+----------------+-------------------+---------+----------+-------------
 pg_default   | postgres |                |                   |         | 32 MB    |
 pg_global    | postgres |                |                   |         | 559 kB   |
 tbs_testuser | testuser | /database/data |                   |         | 19 bytes |
(3 rows)

SCHEMA KAVRAMI

Veritabanı nesneleri için mantıksal bir mainspace alanıdır. Herhangi bir veritabanı nesnesine erişmek istediğimizde SCHEMA_ADI.OBJECT_ADI şeklinde erişim sağlarız. VArsayılan olarak public schema vardır ve bir veritabanı içerisinde eğer bir schema oluşturmamış is oluşturulacakolan veritabanı nesnesi (tablo, view, index vb.) public schema altında oluşacaktır.

postgres=# create schema fschema;
CREATE SCHEMA
postgres=# \dt
Did not find any relations.
postgres=# create table test123 (id integer);
CREATE TABLE
postgres=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | test123 | table | postgres
(1 row)


postgres=# create table fschema.test123 (id integer);
CREATE TABLE
postgres=# \dt fschema.test123
          List of relations
 Schema  |  Name   | Type  |  Owner
---------+---------+-------+----------
 fschema | test123 | table | postgres
(1 row)

Her kullanıcı (role) kendi scheması altındaki nesneler üzerinde hak sahibidir ancak farklı kullanıcının schmelarına erişebilmesi için yetki verilmesi USAGE yetkisinin verilmesi gerekmektedir. Bir user başka bir user altındaki schema içerisinde bir nesne oluşturabimesi için ise CREATE yetkisinin verilmesi gerekmetedir.

GRANT USAGE ON SCHEMA <schema_name> to <user> ;
GRANT CREATE ON SCHEMA <schema_name> TO <user>;