PostgreSQL  
Hem Güçü Hem Güzel!
  

6 Mayıs 2015 

Ben Kimim?

DBA @ Markafoni   Blogger @ Kadın Yazılımcı

 Twitter: @apatheticmagpie

PostgreSQL Tarihi


University of California at Berkeley (UCB) 1977.

Michael Stonebraker Ingres Projesi'ni başlattı.

PostgreSQL Çok Güçlü!


  • Dünyanın en gelişmiş açık kaynak kodlu veri tabanıdır.
  • İlişkisel bir veri tabanı yönetim sistemidir  (ORDBMS)
  • Çok büyük verileri ve anlık işlem yükü yüksek olan projeleri PostgreSQL ile yönetmek mümkündür.

PostgreSQL Çok Güçlü

  • Güçlü, sağlam, ACID prensiplerine uygun, hata yapmaya engel olan güvenli bir yapısı vardır.
  • Aktif bir topluluk tarafından desteklenmektedir.
  • PostgreSQL için çok fazla eklenti/araç geliştirilmektedir.
  • PostgreSQL belge ve dokümantasyon olarak iyidir.
  • SQL Standartlarına en çok uyum gösteren veri tabanıdır.
  • Sorgu planlayıcısı harikadır!
  • Performans iyileştirmek için idealdir.
  • Hata mesajları ve log sistemi açıktır, anlaşılırdır.
  • Replikasyon metodları zengindir.
  • Yönetimi kolaydır.

PostgreSQL Öğrenmek

Postgres mail listelerine kaydolun. Günde 1 mail ile ne kadar çok şey öğrenebileceğinize inanamazsınız.
Liste Tanımı
pgsql-admin PostgreSQL yönetimi
pgsql-general Kullanıcılar için genel bir tartışma alanı
psql-announce Postgres ve ona bağlı servis duyuruları
psql-performance Postgres performansına ilişkin konular

PostgreSQL Öğrenmek

Planet PostgreSQL blogunu takip edin.  (2ndQuadrant,pgExperts,VMware,OmniTI,EnterpriseDB,  EndPoint,credativ,Cybertec,CommandPrompt,OpenSCG)


PostgreSQL bloglarının tümü bu blogda toplanır.

PostgreSQL Öğrenmek


  • Karşılaştığınız her konsept için yazılmış PostgreSQL belgesini bulup okuyun. 
  • Postgres belgeleme konusunda en zengin açık kaynak kodlu projelerden biridir.
  • Çok sık kullandığınız özellikleri bile okuyun çünkü ufak bir detay hayat kurtarıcı olabilir.

"Why do I love Postgres?
- Because I'm not a DBA. "

Postgres Neden Güzel?


Kurulumu Çok Kolay

Mac
Postgres.app basit ve 'native' bir Mac OS X uygulaması ve yükleyiciye gerek duymuyor. Uygulamayı açtığınızda yeni bağlantılara açık bir PostreSQL sunucu hazır oluyor. Uygulamayı kapadığınızda sunucu kapanıyor.
Linux
apt-get install postgresql-9.3 (Debian)yum install postgresql-server (Red Hat ailesi)service postgresql initdb chkconfig postgresql on
Windows

Window ile yüklemek için linki inceleyiniz.

pgAdmin 

Postgres'in grafik arayüzüdür. Sorgu analiz etme, çalışan sorguları gözlemleme özellikleri mevcuttur. Kullandığınız özelliklerin kodlarını görebilir, değiştirebilir ve düzenleyebilirsiniz.
 

pgAdmin



Postgres Çok Güzel

Parçalı dizin (partial indexes) kullanımı
Create index index_1 on conference (presentation_id) where isactive = true;
Create unique index tests_success_constraint on tests (subject, target) where success;
Fonksiyonel dizin (indexes on expressions) kullanımı
Create index concurrently index_2 on users to_lower(email);
Şema (Schemas)
Create schema womentechmakers;
Create tablespace women location '/data/women';Create index concurrently ariteknokent_idx on conference (speaker_id) tablespace women;

Postgres Çok Güzel

ACID , Savepoint , PITR  ( WAL ) 


Rollback to savepoint my_svp;
Release savepoint my_svp;
Enum tipi, Boolean tipi, Hstore modülü (Key/Value çiftleri)
Veri bölümleme (Partitioning), Inheritance
Window Functions, CTE (with, with recursive) 

PostgreSQL vs MySQL



"One of the nice things about Postgres is that it tries very hard to get you to do the right thing. MySQL doesn't care as much."

Farklar ile Öğrenme


User vs Role

Create user devfest with password 'i<3postgres!';
Fark, 'user' olarak yarattığımızda 'login' özelliği olan bir 'role' yaratıyor olmamız; 'role'  yarattığımızda 'login' olabilmesi için onu rolü yaratırken belirtmek gerekir. 
Create role devfestw with password 'i<3linux!'
Create role devfestwomen with password 'i<3linux!' login;

gulcin=# \du
List of roles
Role name    |  Attributes      |  Member of
-------------+------------------+--------------
devfest      |                  | {}  
devfestw     | Cannot login     | {}
devfestwomen |                  | {}

Nelere Bakmalıyım?

Bu rolleri nasıl yetkilendirebilirim?
Hepsini topluca yetkilendirsem güzel olmaz mı?
gulcin=# Create role gdg;
CREATE ROLE
gulcin=# Grant gdg to devfest;
GRANT ROLE
gulcin=# Grant gdg to devfestw;
GRANT ROLE
gulcin=# Grant gdg to devfestwomen;
GRANT ROLE

gulcin=# \du
List of roles
Role name    |  Attributes      |  Member of
-------------+------------------+--------------
devfest      |                  | {gdg}  
devfestw     | Cannot login     | {gdg}
devfestwomen |                  | {gdg}  
gdg          | Cannot login     | {}

Nelere Bakmalıyım?

 
Create role gdg option;
SUPERUSER NOSUPERUSER
CREATEDB NOCREATEDB
CREATEROLE KNOCREATEROLE
CREATEUSER NOCREATEUSER
INHERIT NOINHERIT
LOGIN NOLOGIN
REPLICATION NOREPLICATION

Nelere Bakmalıyım?

Yetkilendirdim, nasıl geri alırım?
gulcin=# Alter role gdg superuser;
ALTER ROLE


gulcin=# \du
List of roles
Role name    |  Attributes             |  Member of
-------------+-------------------------+------------
gdg          | Superuser, Cannot login | {} 

gulcin=# Alter role gdg nosuperuser;
ALTER ROLE


gulcin=# \du
List of roles
Role name    |  Attributes             |  Member of
-------------+-------------------------+------------
gdg          | Cannot login            | {}

Nelere Bakmalıyım?

Tabloları, sıralı dizileri, fonksiyonları, dizinleri nasıl yetkilendirebilirim?
Verdiğim yetkileri nasıl geri alabilirim?

Grant select,update on table table_1 to gdg;
Grant insert on all tables in schema public to devfestwomen;
Grant usage on table_1_id_seq to devfestw;
Grant select on all sequences in schema google to devfest;


Revoke select,update on table table_1 from gdg;
Revoke insert on all tables in schema public from devfestwomen;
Revoke usage on table_1_id_seq from devfestw;
Revoke select on all tables in schema google from devfest;
Şema kullanımını da öğreneyim o zaman.

 Explain vs Explain Analyze


gulcin=# explain select * from table_1;
                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan on table_1 (cost=0.00..4740.30 rows=86430 width=140)
(1 row)


gulcin=# explain analyze select * from table_1;
                            QUERY PLAN
-------------------------------------------------------------------
 Seq Scan on table_1 (cost=0.00..4740.30 rows=86430 width=140) 
 (actual time=0.274..35.983 rows=86430 loops=1)
 Total runtime: 40.492 ms
(2 rows)


Begin;
Explain analyze delete from table_1; 
Rollback;


Explain (format JSON) select * from table_1;

Farklar ile Öğrenme

Konseptlerin ortak yönlerini, kullanım amaçlarını, birbirlerinden ayrıldıkları noktaları bulmak detayı görmenize yardım edecektir. 
Bu farkları bulmakla devam edebilirsiniz.

Postgres'i Anlamak

"When elephants fight it is the grass that suffers."
"Filler dövüşür çimenler ezilir."

Postgres'i Anlamak

Uzun uzuuuun log incelemektir.
tail -f postgresql-Fri.log | perl -pe 's/.*ERROR.*/\e[1;31m$&\e[0m/g'
                        

Postgres'i Anlamak

Postgres eklentisi kurmak istedik. Belge bize ne önerdi?
Select * from pg_available_extensions;
Select * from pg_available_extension_versions;

gulcin=# Create extension pg_stat_statements;
CREATE EXTENSION
gulcin=# Select * from pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

gulcin=# Show config_file;
               config_file
-----------------------------------------
 /var/lib/pgsql/9.2/data/postgresql.conf
(1 row)

gulcin=# vim  /var/lib/pgsql/9.2/data/postgresql.conf

#---------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------
# - Memory -
shared_preload_libraries ='pg_stat_statements'#(change requires restart)

Postgres'i Anlamak

Diyelim ki bir tabloyu kaldırmak istedik...
gulcin=# select * from pg_stat_all_tables where relname = 'table_1';
-[ RECORD 1 ]-----+-------------------------------------------------
relid             | 28193
schemaname        | public
relname           | table_1
seq_scan          | 30686799 /* okuma ile ilgili parametreler */
seq_tup_read      | 28979969846 /* okuma ile ilgili parametreler */
idx_scan          | 2541503146 /* okuma ile ilgili parametreler */
idx_tup_fetch     | 1119955873 /* okuma ile ilgili parametreler */
n_tup_ins         | 129 /* kaç kayıt eklenmiş */
n_tup_upd         | 123199 /* kaç kayıt güncellenmiş */
n_tup_del         | 0 /* kaç kayıt silinmiş */
n_tup_hot_upd     | 122323
n_live_tup        | 1011
n_dead_tup        | 58
last_vacuum       | 2014-03-01 06:03:44.979917+02
last_autovacuum   | 2014-03-01 17:08:39.960498+02
last_analyze      | 2014-03-01 06:03:45.003016+02
last_autoanalyze  | 2014-03-01 17:15:39.937703+02
vacuum_count      | 184
autovacuum_count  | 591
analyze_count     | 185
autoanalyze_count | 754
gulcin=# select * from pg_stat_statements where query like '%table_1%'; 
(No rows)
Loglara bakarım, yetkilerini alırım. Kaldırırım.

Postgres'i Anlamak

Konfigürasyon parametrelerini anlamaktır.
Loglara bakabilmek için log parametrelerini doğru ayarlamak gerek.
Where, When, What to Log
 
#-------------------------------------------------
# ERROR REPORTING AND LOGGING
#-------------------------------------------------

# - Where to Log -

log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'             
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on           
log_rotation_age = 1d                   
log_rotation_size = 0                   

# - When to Log -

log_min_duration_statement = 500

# - What to Log -

log_checkpoints = on
log_duration = off
log_line_prefix = '%t [%v]-[%p]: [%l-1] user=%u,db=%d ' # special values:
                                        #   %a = application name
                                        #   %u = user name
                                        #   %d = database name
                                        #   %r = remote host and port
                                        #   %h = remote host
                                        #   %p = process ID
                                        #   %t = timestamp without ms
                                        #   %m = timestamp with ms
                                        #   %i = command tag
                                        #   %e = SQL state
                                        #   %c = session ID
                                        #   %l = session line number
                                        #   %s = session start timestamp
                                        #   %v = virtual transaction ID
                                        #   %x = transaction ID (0 if none)
                                        #   %q = stop here in non-session
                                        #        processes
                                        #   %% = '%'
                                        # e.g. '<%u%%%d> '
log_lock_waits = on                     # log lock waits >=deadlock_timeout
log_timezone = 'Turkey'

Postgres'i Anlamak


#----------------------------------------------
# RUNTIME STATISTICS
#-----------------------------------------------
# - Query/Index Statistics Collector -
track_activities = on
track_counts = on
track_functions = pl  # none, pl, all

Konfigürasyon dosyasındaki bu parametreler sunucu çapındaki koleksiyon özelliklerini kontrol eder. İstatistik toplama açık ise, üretilen veri pg_stat ve pg_statio ailesindeki (pg_stat_activity, pg_stat_database, pg_statio_all_indexes) sistem tabloları tarafından erişilebilir.

Postgres'i Anlamak

Kilitleri görmek ve yönetmektir.
SELECT pg_stat_activity.datname,
       pg_class.relname,
       pg_locks.mode,
       pg_locks.granted,
       pg_stat_activity.usename,
       substr(pg_stat_activity.query,1,10),
       pg_stat_activity.query_start,
       age(now(),pg_stat_activity.query_start) AS "age",
       pg_stat_activity.pid
FROM pg_stat_activity,
     pg_locks
LEFT
OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE pg_locks.pid=pg_stat_activity.pid
  AND MODE LIKE 'Exclusive%'
  AND datname= 'warehouse'
ORDER BY query_start;

Select * from pg_stat_activity where pid = 0001Select pg_terminate_backend(0001);

Postgres'i Anlamak


Nagios, PRTG, New Relic, Cacti gibi yazılımlarla veri tabanı sunucularımızı ve veri tabanlarımızı kontrol edebilirsiniz.

Tavsiye: check_postgres bir bucardo projesidir.

check_postgres veri tabanımızın çeşitli özelliklerini izleyip kontrol edebilmemize yarayan bir betiktir.  Nagios  gibi yazılımlarla veya bağımsız betiklerle çalışabilecek şekilde tasarlanmıştır.

Postgres'i Anlamak

Nagios


Postgres'i Anlamak

Logları toplayıp analiz edersek çok daha iyi anlamış oluruz. Bunun için aşağıdaki araçları kullanabilirsiniz.


Log analiz etmek üzere tasarlanmış bu araçlar log incelemenizi ve bunlardan yola çıkarak kararlar almanızı sağlayacaktır.

pgbadger ve pg_fouine


Postgres'i Anlamak

Onunla aynı dili konuşabilmektir.
 SQL dili ile sorgulama yapabileceğimiz Postgres çeşitli prosedürel dilleri de temel dağıtımında içermektedir.
PL/pgSQL ,  PL/Tcl ,  PL/Perl  ve  PL/Python 
Adı Dili
PL/Java Java
PL/PHP PHP
PL/R R
PL/Ruby Ruby
PL/sh Unix shell


Postgres ve DBA hayatı

"PostgreSQL: making very hard things possible, and simple things hard."

"PostgreSQL: çok zor şeyleri mümkün, çok basit şeyleri zor kılar."

Postgres ve DBA Hayatı

  • İşleri olabildiğince otomatize edin.
  • Bir yedekleme politikanız olsun.
  • Yedekleriniz güvenli ve kullanılabilir olsun.
  • Tablo ve dizin (index) boyutlarının artışını kontrol edin.
  • Uzun süren sorguları loglayın ve bu sorguları düzenli aralıklarla iyileştirin.
  • Loglara düşen hataları inceleyin ve nasıl bir soruna işaret ettiklerini anlamaya çalışın, araştırın.
  • Vacuum analyze, autovacuum, full vacuum işlerini planlayın.
  • Veri tabanı kilitleri hakkında fikir edinin ve nasıl izleyip kontrol edeceğinizi bilin.

Postgres ve DBA Hayatı

İşleri otomatize etmek için 'crontab' komutu çok faydalı olacaktır. Yedekleme, vacuum yapma gibi rutinler her DBA'in hayatını kurtaran faydalı alışkanlıklardır. 
gulcin# crontab -l
00 02  * * *  sh /home/postgres/scripts/getbackup.sh
0 05  * * *  sh /home/postgres/scripts/daily_vacuum_analyze.sh
#WAL Archive CleanUp
0 02  * * *  sh /var/lib/pgsql/removewal_archive.sh

 crontab -e // crontab dosyasını düzenlemeye yarar.
# * * * * *  çalıştırılacak komut
# ┬ ┬ ┬ ┬ ┬
# │ │ │ │ │
# │ │ │ │ │
# │ │ │ │ └───── haftanın günü (0 - 7) (0'dan 6'ya Pazar'dan Cumartesi'ye demektir; 7 Pazar, 0 da Pazar.)
# │ │ │ └────────── ay (1 - 12)
# │ │ └─────────────── ayın günü (1 - 31)
# │ └──────────────────── saat (0 - 23)
# └───────────────────────── dakika (0 - 59)

Cron için örnek

Ne kadar çok planlanmış iş o kadar çok kolaylık demektir.

exec &> /tmp/devfestwomen.log
echo -e "Script Started at\t`date`\n\n"
drop_database() # dbname
{
    echo " "
    echo "Restoring the database \"$1\""
    echo " "
    psql -U postgres -d postgres -c "ALTER DATABASE $1 CONNECTION LIMIT 0;"
    psql -U postgres -d postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity where datname='$1';"
    psql -U postgres -d postgres -c "DROP DATABASE $1;"
}
create_database() # dbname,owner,dumpfile
{
    psql -U postgres -d postgres -c "CREATE DATABASE $1 OWNER $2 TABLESPACE new_tablespace;"
    psql -U postgres -d postgres -c "ALTER DATABASE $1 CONNECTION LIMIT 0;"
    psql -U postgres -d postgres -c "COMMENT ON DATABASE $1 IS 'DB updated at `date`'; "
    pg_restore -U postgres -j 4 -x -O --no-tablespaces -d $1 $3
}
open_database() # dbname
{
    psql -U postgres -d $1 -c "ANALYZE;"
    psql -U postgres -d postgres -c "ALTER DATABASE $1 CONNECTION LIMIT -1;"
}
reload_devfestwomen()
{
    pg_dump -U postgres -h 127.0.0.1  -F c -b -f "/tmp/devfestwomen.dump" devfestwomen
    drop_database devfestwomen
    create_database devfestwomen postgres /tmp/devfestwomen.dump
    psql -U postgres -d devfestwomen < /scripts/devfestwomen_extra.txt
    open_database devfestwomen
}
reload_devfestwomen
ls -lh /tmp/*.dump
cat /tmp/devfestwomen.log | mail -s "DevFest Women Log" yildirim.gulcin88@gmail.com
rm -f /tmp/devfestwomen.dump

Git kullanın!

 
apt-get install git // Debian tabanlı dağıtım Ubuntu gibi.
yum install git // RHEL
yum install git-core // Fedora vs.
Gitlab çok güzel. Demo ile hemen öğren.

ssh-keygen -t rsa -C "yildirim.gulcin88@gmail.com"
cat ~/.ssh/id_rsa.pub


git config --global user.name "Gulcin Yildirim"
git config --global user.email "yildirim.gulcin88@gmail.com"


mkdir devfestwomen
cd devfestwomen
git init
touch README
git add README
git commit -m 'Welcome to DevFest Women 2014!'
git remote add origin git@demo.gitlab.com:gitlab/devfestwomen.git
git push -u origin master

Terminal ve psql


Terminal ve psql

psql Postgres'in etkileşimli (interaktif) terminalidir.

 psql -h 127.0.0.1 -U gulcin -d database_1
Komutlar size çok hız kazandıracaktır.

\l ve \l+
\c 
\dt ve \dt+
\di ve \di+
\d ve \d+
\du
\dp
\df
\db
\x
\q
\?

Linux Komutları

psql ile çalışıyorsanız terminalde çalıştıracağınız komutlar ile istediklerinizi daha hızlı yapabilirsiniz.

ls -la
ls -lh
pwd
locate
which
df -kh
free -g
htop/top/atop
ping
traceroute
mount
tail  
less 
vim
nano
cat
scp
rsync
cp
mv
rm -rf
mkdir
rmdir
iostat
ifconfig

Teşekkürler.

Soru var mı?



Beni bul!

  Gulcin Yildirim
apatheticmagpie
@apatheticmagpie

Mail : gulcin.yildirim@markafoni.com

PostgreSQL Hem Güçlü Hem Güzel!

By Gülçin Yıldırım Jelínek

Made with Slides.com

PostgreSQL Hem Güçlü Hem Güzel!

Bu sunum Van Yüzüncü Yıl Üniversitesi Bilişim Şenliği 2015 etkinliği için hazırlanmıştır. 6 Mayıs 2015'te sunulmuştur.

  • 4,978

More from Gülçin Yıldırım Jelínek