PostgreSQL Giriş Ve Replika Kurulumu
Bu makaleyi, PostgreSQL öğrenmeye temelden başlayanlar için bir öğrenme kılavuzu olması için yazıyorum. En temel seviyeden başlayarak replikasyon’a kadar ilerleyeceğim bir giriş makalesi olacak.
Makaleye başlamadan önce PostgreSQL uzmanlarının çok takıldığı birşeyi paylaşayım. PostgreSQL’e Postgre demeyin. Postgre dediğinizde forumlarda sorularınıza bile cevap vermeyebiliyorlar. Postgre’nin Sonuna bir “S” ekleyeceğiz ve makalenin ilerleyen kısımlarında da Postgres olarak kullanacağız.
Postgres ilişkisel bir veritabanı yönetim sistemidir. Diğer ilişkisel veritabanları ile karşılaştırmalı olarak anlattığım “SQL Server Nedir?” isimli makalemi okumanızı tavsiye ederim.
İlk olarak Postgres’in temel mimarisini anlatarak başlayacağım. Postgres’i iki süreçte inceleyeceğiz.
Query Backend: Sorguların işlendiği Postgres’in ana süreçleridir.
Authentication(Kimlik Doğrulama) ve Authorization(Yetkilendirme) işlemleri gerçekleştirilir.
Authentication(Kimlik Doğrulama) ve Authorization(Yetkilendirme) işlemleri sırasında aşağıdaki kontroller yapılır.
KullanıcıAdı:
IP bilgisi:
DB:
Parola:
Doğrulama başarılı bir şekilde sağlandıysa, bağlantının kabulü için postgres ana sürecinden postgres alt süreci “fork” eder.
Postgres’de bir sorgunun 3 ana süreci vardır. Parse+Plan+Execute
Postgres süreci fork ettikten sonra gelen sorgu parse(sorgunun syntax ve anlam bütünlüğünün kontrol edilmesi) edilir ve sonrasında planlayıcı devreye girer. Sorgunun en hızlı çalışabilmesi için en uygun yöntemi bularak sorguyu çalıştırır. Planlayıcının işini doğru yapabilmesi için istatistiklerin doğru olması gerekir.
Örneğin bir select sorgusunun hikayesini inceleyelim.
Planlayacının Select sorgusu için en uygun plan’ı bulduğunu ve sorgunun çalıştığını düşünelim. Postgres process’i ilk olarak ihtiyaç duyulan verinin Shared Buffers’ta olup olmadığını kontrol eder.
Ram’in diğer kısımlardında OS Cache, Kernel Cache(İşletim Sistemi Linux) ve FS Cache vardır.
Postgres process’i Shared Buffer’dan çıkmaz. Sonraki işlemleri Kernel’a devreder. İstekler Shared Buffer’da yoksa OS Cache’e sorulur. OS Cache’de veri varsa Shared Buffer’a iletilir. Veri, Os Cache’de de yoksa diskten okunur.
Buffer’da block’lar 4KB’dır. Diskte page ise 8 KB’dır.
Shared Buffer’ın büyüklüğünü set ederken memory’nin %25’inden büyük, %50’sinden küçük olarak set edilmesi tavsiye edilir. Ama veri Shared Buffer’da yoksa bile disk’e gitmeden önce OS Cache’e gideceği için çok büyük set edilmesine gerek yoktur. Hatta bazı benchmark test’lerinde çok set edilmesinin perforamansı düşürdüğü gözlemlenmiştir. Production ortamlarda 8-12 GB arası yeterli olacaktır. Yoğun transaction’a sahip sistemlerde bunu 8 GB set ettikten sonra log’ları okuyarak yetip yetmediğini kontrol etmenizi tavsiye ederim.
Shared Buffer’da iki tip buffer vardır. Clean buffer ve dirty buffer.
Veri memory’e alındıktan sonra herhangi bir değişikliğe uğramamışsa clean buffer, memory’e alındığı andan itibaren bir değişikliğe uğramışsa dirty buffer olarak ifade ederiz.
Checkpoint işlemi ikinci kez tetiklendiğinde dirty buffer data disklerine yazılır. Checkpoint’in dışında dirty buffer’ı data disklerine yazan bgwriter isminde bir process daha var. bgwriter belirtilen zaman aralığında tekrarlanarak kısa kısa dirty blokları data diskine yazar. Checkpoint gibi bütün dirty page’leri data diskine yazmak yerine, dirty buffer’ı data diskine yazarken LRU(Last Recently Used) algoritmasını kullanır.
Checkpoint ile ilgili bilgilere data dizini altındaki pg_control dizini altından erişebilirsiniz.
Checkpoint süre’ye ya da WAL dosya sayısına göre tetiklenebilir. Default olarak süreye göre 20 dakikada bir tetiklenir.
Postgres’de, yapılan işlemlerin (update,delete,insert) tutulduğu WAL isminde dosya/lar vardır. WAL dosyaları, postgres’in verinin tutarlılığını garanti etmesi açısından büyük önem taşımaktadır.
WAL dosyaları 10.0 öncesinde default olarak data dizini altındaki pg_wal dizinindeydi. 10.0 ile birlikte default olarak data dizini altındaki pg_xlog dizini altında bulunuyor.
WAL dosyalarının default büyüklüğü 16 MB’dır. WAL dosyaları 24 karakterden oluşur. İlk 8 karakter timeline bilgisini, diğer 24 karakter ise wal dosyasının ismini belirtir.
WAL dosyalarına ne kadar bilgi yazılacağını data dizini altındaki postgresql.conf üzerinde wal_level parametresine aşağıdaki değerlerden birini yazarak belirleyebilirsiniz.
Minimal: Bu ayarı set ederseniz, beklenmedik bir kapanma sonucunda postgres cluster’ın recovery olabilmesi için gerekli bilgi wal dosyalarında saklanır. Bazı bulk işlemleri loglamayarak bulk işlemleri hızlandırır. Fakat bu wal level, verininin tamamını base backup ve WAL dosyaları ile yeniden yapılandırmak için yeterli bilgi sağlamaz. Replikasyon yapacaksanız replica değerini kullanmalısınız. Makalenin sonunda Replikasyon ile ilgili bir örnek yapacağım.
Replica: Bu wal level’i set ederseniz, minimal wal level’ine ek olarak wal archiving için gerekli bilgileri ve replikasyon yaptığınızda standby sunucudan read yapabilmeniz için gerekli bilgileri wal dosyalarında tutar.
Logical: Bu wal level’i set edereniz replica’ya ek olarak wal’deki mantıksal değişim kümelerinide bulabilirsiniz. Bu wal level’de wal büyüklükleri fazla olabilir. Özellikle bir çok tabloda REPLICA IDENTITY FULL özelliğini kullanıyorsanız ve update ve delete ifadeleri yoğun kullanılıyorsa wal büyüklüğü fazla olacaktır. REPLICA IDENTITY FULL özelliği set edilen tablo için wal’de update ve delete edilen satırlar hakkında bilgi tutar.
Bütün bu bilgileri verdikten sonra bir update sorgusunun kısaca hikayesine göz atalım.
Öncelikle postgres süreci, update edeceği veriyi her zaman select ettiği için verinin shared buffer’da olup olmadığını sorguluyor. Eğer shared buffer’da yoksa işlemi kernel’a devrederek verinin OS Cache’de olup olmadığını sorguluyor. Veri OS Cache’de de yoksa data diskinden okunarak OS Cache’e oradadan da Shared buffer’a iletiliyor.
Veri shared buffer’a ilk geldiğinde henüz değişmediği için clean buffer olarak adlandırılıyor. Sonrasında update işlemi memory üzerinde gerçekleşiyor ve veri artık dirty buffer haline geliyor. Bu sırada yapılan update işlemi WAL dosyalarına yazılıyor. Ama commit işleminin henüz gerçekleşmediğini düşünün.
Veri commit edilmeden update işlemi bittikten sonra ikinci checkpoint tetiklendiğinde update edilmiş veriler data diskine yazılacaktır. (Bu noktaya daha sonra döneceğiz. X noktası dediğimde bu noktayı hatırlayın)
Daha sonra commit işleminin gerçekleştiğini düşünelim. Commit işleminin gerçekleşmesi ile WAL dosyalarına commit işlemlerinin gerçekleştiği bilgisi yazılır.
Eğer x noktasında iken servis bir şekilde kapanırsa ne olur?
Veri data dosyalarına yazıldı ama commit olmadı. Postgres açılırken WAL dosyalarına bakar ve bu update işlemi commit olmadığı için rollback işlemi gerçekleştirilir ve checkpoint sonucunda data disk’inde update’in yaptığı değişiklikler geri alınır.
Bu örnekle Checkpoint, Commit, WAL Dosyaları ve Database Recovery’nin birbiri ile ilişkisini ve postgres’in çalışma mimarisini anladığınızı umuyorum.
Utility Backend: Connection gelmese bile arka planda çalışan süreçlerdir. Aşağıda utility backend süreçlerini görebilirsiniz.
Logger,
Statistic collector,
Checkpointer,
Auto vacuum
Postgres’de tablo relation, satır tuple, kolon attiribute olarak adlandırılır. Bu kavramlara zaman içersinde aşina olacağız. Makalenin ilerleyen bölümlerinde sorgularımızda bu kavramlarla karşılacağız.
MVCC(Multi Version Concurrency Control) : Aynı tablo üzerinde birden fazla işlem yapıldığında veri bütünlüğünün korunması için MVCC kullanılır.
MVCC ile bir transaction başladığı anda verinin bir snapshot’ını alır ve transaction bitene kadar bu değeri görür. Transaction bitene kadar veri başkaları tarafından değiştirilmiş olsa bile kendi transaction’ı başladığı anda okuduğu değer sabit kalır.
MVCC ile transaction’lar arası izolasyon sağlanır. Okuma ve yazma işlemleri bu sayede birbirini etkilemez.
Vacuum: Postgres’te bir delete işlemi gerçekleştiğinde silinen veriler direk olarak disk’ten silinmez. Satırın visibility(görünürlüğü)’si kalkar.
Vacuum, visibility’si kalkan satırlar için, visibility mapping’e şu data bloğunda şu blok silindi gibi bir pointer koyar. Daha sonra vm’deki satırları gerçekten siler ve sildiği yerden boşalan alanların veritabanı tarafından tekrar kullanılabilmesi için boşalttığı yerleri free space mapping(fsm)’e yazar. Veritanında bir alana ihtiyaç duyulduğu anda fsm’de ki alanlar kullanılır.
Vacum’u full parametresi ile çalıştırırsak boşalan alanlar shrink edilir.
Vacuum’un nasıl çalıştığını görmek isterseniz aşağıdaki komutlar yardımıyla tabloda auto vacuum’u disable edip insert edip tablo size’ını görebilirsiniz.
CREATE TABLE vaccum_test (col1 int);
ALTER TABLE vacuum_test SET (autovacuum_enabled=false,toast.autovacuum_enabled=false);
INSERT INTO vacuum_test SELECT * FROM generate_series(1,10000);
SELECT pg_size_pretty(pg_relation_size(‘vacuum_test’));
VACUUM vacuum_test; komutu ile tablomuz için vacuum işlemini yapıyoruz.
ANALYZE vacuum_test; komutu ile tablodaki istatistikleri güncelliyoruz.
Daha sonra tekrar pg_size_pretty’den tablomuzun size’ını kontrol ettiğimizde küçüldüğünü göreceğiz
Centos 7 üzerine PostgreSQL 10 ve Replica Kurulumu:
Öncelikle https://yum.postgresql.org adresine giderek repo’sunu indireceğimiz postgresql’in içine giriyoruz. Biz örneğimizde postgresql 10’u kullanacağımız için 10’a tıklıyoruz.
Daha sonra aşağıdaki ekranda “Centos 7 – x86_64” ‘a tıklayarak copy link location diyoruz.
Centos sunucumuzda terminal’e geçiyoruz ve su – root komutunu kullanarak root oluyoruz.
Daha sonra yum install yazıyoruz ve bir boşluk bıraktıktan sonra sağ tıklayarak kopyadığımız link’i yapıştırıyoruz. Bu şekilde repo’yu sunucuya indirmiş oluyoruz.
Örneğin;
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
Daha sonra paketimizin inip inmediğini kontrol etmek için aşağıdaki komut yardımıyla paketimizin indiği klasöre gidiyoruz.
Cd /etc/yum.repos.d
ve aşağıdaki komut yardımıyla klasör içindeki dosyaları listeliyoruz.
ll ya da ls – al
karşınıza çıkan listede pgdg-10-centos.repo isimli paketi görmeniz gerekiyor.
Daha sonra aşağıdaki komut yardımıyla indirdiğimiz repoyu kullanarak postgresql 10’u ve contrib’i kuruyoruz. ****contrib’in açıklaması word’de var.
yum install postgresql10-server.x86_64 postgresql10-contrib.x86_64
Aşağıdaki komut yardımıyla ana dizine geçiyoruz.
Cd /
Burada data ve log dosyası için sistem ekibinin size iki ayrı dizin tanımlaması gerekiyor. Ben örnek yaptığım için tek dizinde iki dosya oluşturacağım.
Home dizinin altında postgres isminde bir dizinde aşağıdaki komutlar yardımıyla iki adet klasör oluşturuyoruz.
Mkdir pg_data
Mkdir pg_xlog
Daha sonra cd .. komutuyla tekrar bir üst dizine çıkarak aşağıdaki komut yardımıyla postgres dizini ve altında oluşan her dosya ve klasör için postgres kullanıcısını owner olarak atıyoruz.
chown -R postgres:postgres /postgres
Daha sonra bu klasörleri initdb yaparken -D ve -X parametreleri ile belirteceğiz.
pg_data (data dizini -D)
pg_xlog (wal dizini X)
ana dizindeyken /postgres ve altındaki dizinlerin owner’ını postgres kullanıcısı olarak tanımlamak için aşağıdaki komut’u kullanıyoruz.
chown -R postgres /postgres
ve yine ana dizindeyken aşağıdaki komut yardımıyla sadece postgres’in çalıştırabilmesini sağlıyoruz.
chmod -R 700 /postgres
passwd postgres ile postgres’e bir şifre veriyoruz.
su – postgres komutu ile postgres kullanıcısına geçiyoruz.
initdb yapmak için aşağıdaki komut yardımıyla executable file’ların olduğu dizine gidiyoruz.
cd /usr/pgsql-10/bin
Aşağıdaki komut yardımıyla da initdb yapıyoruz.
/usr/pgsql-10/bin /initdb -D /postgres/pg_data/ -X /postgres/pg_xlog/
ssh-keygen -t rsa komutunu kullarak public ve private key oluşuruyoruz. Bu key’leri standby sunucusuna kopyalayarak şifresiz geçişi sağlayacağız. Bu komutu girdikten sonra bir kaç kere enter’a basmamız gerekiyor.
Daha sonra aşağıdaki komut yardımıyla standby sunucusuna public key’i kopyaladık.
Ssh-copy-id 192.168.1.29 -> Buradaki IP standby sunucusunun IP’si Burada size postgres kullanıcısının şifresini soracak. Girdiğiniz şifreyi burada yazarsanız hata alırsınız. Çünkü standby sunucusunda şifre oluşturulmadı. Şifre oluşturma işlemini standby sunucusunda yaptıktan sonra yukardaki komutu çalıştırdığınızda sorulan şifre kısmına giriş yapabilirsiniz.
daha sonra test etmek için aşağıdaki komut yardımıyla şifresi bağlantıyı test ediyoruz.
eğer bağlantı sağlanmazsa diğer server’da aşağıdaki komut yardımıyla firewall’u kapatıyoruz.
service firewalld stop
birde selinux’u kapatmamız lazım.
Root iken! vim /etc/selinux/config komutu ile dosyaya giriyoruz enforcing yazan yere disabled yazıyoruz.
Daha sonra servisi pg_ctl ile başlatmak için aşağıdaki komut yardımyla executable file’ların olduğu path’e gidiyoruz.
Cd /usr/pgsql-10/bin
Ve postgres kullanıcısında iken aşağıdaki komut yardımıyla da posrgresql servisini başlatıyoruz.
/usr/pgsql-10/bin/pg_ctl -D /postgres/pg_data/ start
başlatma işlemi systemctl ile root’da iken yapmanızı tavsiye ederim.
Öncelikle postgresql servisi’nin path’ini düzeltiyoruz.
Bunu yapmak için aşağıdaki komut yardımıyla servisin içine giriyoruz.
vi /lib/systemd/system/postgresql-10.service
Environment satırını bulup aşağıdaki gibi değiştiriyoruz.
Environment=PGDATA=/postgres/pg_data/
Daha sonra da aşağıdaki komut yardımıyla servisi aktif edip başlatıyoruz. Aktif etmezseniz sunucu kapanıp açıldığında postgres servisi otomatik başlamaz.
systemctl enable postgresql-10.service
systemctl start postgresql-10.service
systemctl reload postgresql-10.service
Daha sonra su – postgres ile postgres kullanıcısınja geçerek psql komutunu kullanarak postgres’e bağlanıyoruz ve aşağıdaki script yardımıyla replika için master’ımıza bağlanacak replicauser isminde replication hakkına sahip bir kullanıcı oluşturuyoruz.
create user replicauser with replication encrypted password ‘rp’;
Daha sonra standby’dan replicauser ile master’dan backup alabilmek için master’da ki pg_hba dosyasına giriyoruz.
vim /postgres/pg_data/pg_hba.conf
ve replication olan kısımda aşağıdaki gibi bir satır ekliyoruz. Böylece standby master’ımıza replication için bağlanabilecek.
host replication replicauser 192.168.1.29/32 trust
ayrıca iki sunucunun birbirine bağlanabilmesi için her sunucuya aşağıdaki gibi diğerinin IP’sini içeren bir satır ekliyoruz. 192.168.1.28 için 192.168.1.29’u, 192.168.1.29 için 192.168.1.28’i ekliyoruz.
host all all 192.168.1.29/32 trust
Daha sonra değişikliklerin geçerli olması için aşağıdaki komutu postgres kullanıcısında iken çalıştırabilirsiniz.
/usr/pgsql-10/bin/pg_ctl reload -D /postgres/pg_data
root’da iken’de aşağıdaki komut’u kullanabilirsiniz.
Daha sonra aşağıdaki komut yardımıyla reload yapıyoruz.
Systemctl reload postgresql-10.service
Bir sonraki aşamada postgresql.conf dosyasında bazı paremetleri ayarlamak için aşağıdaki komut yardımıyla dosyanın içine giriyoruz.
vim /postgres/pg_data/postgresql.conf
ilk olarak listen_adress’i aşağıdaki gibi * yapıyoruz başıdnaki # işaretini kaldırıyoruz.
listen_addresses = ‘*’
Diğer ayarlarımız da aşağıdaki gibi;
archive_mode = on
wal_level=replika
max_wal_senders=2
wal_keep_segments=256
archieve_mode=on
archive_command = ‘/postgres/walaktarim.sh %p %f’
Daha sonra walaktarim.sh dosyasını oluşturmak için aşağıdaki komutu kullanalım.
vim /postgres/walaktarim.sh
walaktarim.sh dosyasını içi de aşağıki gibi olmalı.
#!/bin/bash
rsync -ave ssh $1 postgres@192.168.1.29:/postgres/xlogarchive/$2
walaktarim dosyasını postgres user’ını owner yapıp 700 hakkını yukardaki örnekteki gibi veriyoruz.
Daha sonra standby’ı oluşturmak için öncelikle standby sunucusuna geçiyoruz ve repoyu indirip yum install ile postgresql’I kuruyoruz. Postgres dizininde postgres kullanıcısını owner yapıp 700 hakkını veriyoruz.
Daha sonra aşağıdaki script’i kullanarak standby üzerinden master’ın backup’ını standby’daki pg_standby klasörüne alıyoruz.
/usr/pgsql-10/bin/pg_basebackup -D /postgres/pg_standby -c fast -P -Fp -h 192.168.1.28 -p 5432 -U replicauser
——————-
daha sonra standby’da /postgres/pg_standby path’ine gidip recovery.conf dosyasını içeriği aşağıdaki gibi olacak şekilde oluşturuyoruz.
standby_mode=’on’
primary_conninfo=’host=192.168.1.29 port=5432 user=replicauser password=rp’
restore_command=’cp /postgres/xlogarchive/ %f %p’
archive_cleanup_command=’/usr/pgsql-10/bin/pg_archivecleanup /postgre/xlogarchive %r’
daha sonra standby servisini pg_standby klasörünü göstererek başlatınca replikasyonun hatasız başladığını göreceksiniz. Tebrikler.
