AWS Uzak makinede Server oluşturarak SQL ile Server bağlantısı kurup Datayı Manipüle Etme

Günümüzün veri odaklı dünyasında, veri tabanlarına erişim ve bunlar üzerinde manipülasyon yapabilme yeteneği, özellikle data science ve analitik projelerde kritik bir öneme sahiptir. Bu yazımızda, bulut teknolojilerinin öncü isimlerinden biri olan Amazon Web Services (AWS) üzerinde bir PostgreSQL veritabanını nasıl kuracağınızı ve bu veritabanına DBeaver gibi popüler bir SQL istemcisi kullanarak nasıl bağlanıp veri manipülasyonu yapabileceğinizi adım adım ele alacağız. AWS üzerinde bir EC2 Instance kullanarak oluşturduğumuz PostgreSQL veritabanımıza uzaktan erişim sağlamak, bu sayede veri analizi ve işleme süreçlerinizi daha esnek ve güçlü bir yapıya kavuşturmak mümkün.

AWS üzerinde bir EC2 Instance aracılığıyla PostgreSQL veritabanı kurarak uzaktan erişim sağlamak, veri bilimcilerin ve analistlerin çalışmalarını ölçeklendirme ve adaptasyon yeteneklerini artırır. Bu yapılandırma, küresel erişim sağlayarak ekiplerin coğrafi sınırlardan bağımsız çalışmasına olanak tanır ve böylece daha dinamik ve işbirlikçi bir çalışma ortamı oluşturur. Ayrıca, bulut tabanlı çözümler kullanarak veri güvenliğini ve yedeklemelerini kolaylaştırır, böylece kritik verilerin korunması ve felaket kurtarma süreçleri optimize edilir. AWS ve DBeaver gibi güçlü araçlar kullanmak, veri setleri üzerinde daha hızlı ve etkili manipülasyonlar yapılmasına imkan tanır, bu da veri odaklı karar alma süreçlerini hızlandırır ve geliştirir. Bu teknik altyapı, her ölçekteki organizasyonun veri yönetimi kapasitesini genişleterek rekabet avantajı elde etmesine yardımcı olur.

Hazırsanız, veri bilimi dünyasında bu yolculuğa çıkarken size rehberlik edecek bu yazıya başlayalım.

Bu makalede yapacağımız çalışmayı genel hatlarıyla özetleyen alttaki resimdeki gibi bir akış diyagramı üzerinden görelim. Genel olarak biz kendi local bilgisayarımızdaki SQL altyapısı ile AWS uzak makinede oluşturacağımız altyapıyı birbirine bağlayarak iletişim kurmalarını sağlayacağız. Local bilgisayarımızda SQL ile ilgili işlemler için DBeaver programını kullanacağız. Tabii ki bununla beraber bilgisayarımızda bir SQL Database de yüklenmiş olmalıdır. Bu çalışmada PostgreSQL ile database işlemlerini gerçekleştireceğiz. Siz diğer database’ leri de kullanabilirsiniz. Local bilgisayarda Dbeaver ile data manipülasyonu yapılacaktır. Uzak makinede ise bir server oluşturacağız. Bunun için daha önce bir AWS Free hesabınızın var olması gereklidir. Bu konuda önceki yazımıza bakabilirsiniz. AWS uzak makinemize PostgreSQL kurulumu yaparak sonrasında oluşturacağımız database’lere dışardan erişim sağlanabilmesi için 2 adet configuration dosyasını düzenleyeceğiz.

Çalışma için akış diyagramı

Önce AWS üzerinde EC2 oluşturalım

Bunun için adımlar:

1. EC2 servisine gidip sayfada turuncu renkle görülen yerden “Launch Instance” diyelim.

2. PostgreSQL i yükleyeceğimiz makineyi oluşturalım. Adını postgres olarak verelim

Uzak makineyi Ubuntu olarak Free tier makineyi seçelim. Bu makine Postgre için yeterli olacaktır ve işlemleri kolayca yapmamıza imkan tanıyacaktır. Diğer Free makinelerde seçilebilir.

Instance tipi gelen şekliyle kalsın (alt resimdeki gibi)

3. Key pair seçiminde ya eski bir key dosyasını kullanın ya da yeni bir key üretin. Key üretimi için bu amaçla önceki yazımıza bakabilirsiniz

4. Network setting kısmında mevcut 22 portunu muhafaza etmeye devam edeceğiz ve ilave olarak PostgreSQL için 5432 portunu oluşturacağız. Bunun için resmin sağ üst köşesindeki Edit kısmına tıklayıp sayfanın altındaki “Add Securtiy Group “ kısmına tıklayacağız. Sonrasında ise bir sonraki resimdeki gibi 5432 portunu resimde gösterilen şekilde ekleyeceğiz

5. Geri kalan kısımlar default haliyle kalabilir. En son sayfanın altından “Launch Instance” diyeceğiz. Böylece uzak makineyi ayağa kaldırmış olacağız.

6. Yeni gelen sayfada Successfully olduğunu göreceğiz ve sağ alt köşede “View all Instances” diyeceğiz. Gelen sayfada biraz bekleyince oluşturduğumuz postgres Instance ı görmüş olacağız.

EC2 ile AWS üzerinde makineyi oluşturduktan sonra şimdi burayı bu haliyle bırakalım ve terminal bağlantısı yapalım. Daha sonra bu terminal üzerinden bilgisayarımızı uzak makine olarak oluşturduğumuz EC2 instance ile birbirine bağlayacağız.

Neden VS Code ? çünkü ubuntu sisteminde klasör oluşturma işlemlerini veya dosya işlemlerini çok kolayca VS Code arayüzü üzerinden yapabilirsiniz. Normalde terminal üzerinden bunları yapmak daha zor olur. VS Code bize bu noktada kolaylıklar sağlamaktadır. Alternatif olarak önceki makaledeki gibi Windows terminal üzerinden de SSH bağlantısı sağlanabilir (SSH bağlantısı bizim bilgisayarımızı uzak makineye bağlayacak olan bağlantı tipidir)

VS Code İşlemleri

1. VS Code üzerinde eklenti olarak extension yükleme kısmından Remote-SSH eklentisini Install edelim.

Ben daha önce yüklediğim için alttaki resimde sizde Install seçeneği gelmiş olacaktır.

2. VS Code’u SSH ile bağlantısını yapalım. Bunun için VS Code un sol altındaki resimdeki okla gösterilen connection işaretine tıklayalım.

Yukarda VS code’un penceresinin ortasında açılan listede en üstteki Connect to Host seçeneğini tıklayalım

Aynı yerde yeni gelen listeden “Add new Host” seçeneği ile ilerleyelim. Eğer bu işlemi 2. kez yapıyorsak yani daha önce bir Host oluşturduysanız o zaman “ Configure SSH Host” diyebiliriz.

Yeni Host’umuzun adını yazıp Enter ile devam edelim

3. Gelen listede en üsttekini seçelim ve config dosyasını yeniden düzenleyelim. Buraya yeni bağlantı portunu tanımlayacağız ki VS Code bağlanabilsin.

Yeni oluşturduğumuz Host için gereken bilgileri bu Config dosyası içine girip txt dosyası ile düzenleyeceğiz. Üstteki adımın neticesinde bu config dosyasını açmış olacağız. Bu config dosyası techubuntu isimli host ayarlarını değişmemize imkan tanıyacak. Bu config dosyasında başka host bilgileri de olabilir ilerleyen zamanlarda, bu hiç sorun değil. Siz hangi host ile devam edecekseniz sadece onun bilgilerini değiştireceksiniz.

Host techubuntu
HostName 44.201.137.117
  IdentityFile ~/.ssh/streamlit4.pem
   User ubuntu
  ServerAliveInterval 60

BU config dosyasındaki ilgili alanlarda:

Host: Bizim şimdi veya daha önceden oluşturduğumuz hostname in ismi burada yazıyor olmalı

Hostname: İşte bu kısım çok önemli. AWS deki EC2 sayfasındaki Instance oluşturduğumuz sayfaya geri gidelim. Alttaki resimdeki ok ile gösterilen yerdeki Public IPv4 adress numarasını copy yapıp alacağız (Not: rakamın üstündeki ikili kutuya tıklayınca copy yapmış olursunuz)

ÖNEMLİ: Tüm işleriniz bitince AWS deki Instance makinenizi Terminate yapmayı (ortadan kaldırmayı) unutmayın, aksi halde hoşunuza gitmeyen bir fatura ile karşılaşabilirsiniz 😊

IdentityFile: config dosyasındaki bu kısımda AWS Instance oluştururken kullandığımız key.pem dosyasının yerini göstereceğiz. Bu dosyanın alttaki resimde görüldüğü gibi ssh klasörünün olduğu yerde olması tercih edilir. Config dosyasında bu nokta gösterilmiştir. (C:\Users\pc\.ssh) . Bu çalışma için daha önce oluşturduğum streamlit4.pem dosyasını kulandım. Siz ya yeni oluşturmuştunuz ya da eski pem dosyasını kullanabilirsiniz diye önceki satırlarda ifade etmiştim.

Config dosyasındaki diğer alanlar aynı şekilde kalacaktır.

4. Şimdi VS Code ekranındaki config dosyasını bu şekilde oluşturunca Ctrl+s ile kaydedelim.

5. Tekrar VS Code ekranın sol alt köşesindeki Connection tuşuna basalım.

Ekranın üst ortasında gelen seçeneklerden yine “Connect to Host” seçeneğine tıklayalım.

Şimdi gelen listede az önce oluşturduğumuz techubuntu isimi host geldi. Onu seçip devam edelim.

Yeni açılan VSCode penceresindeki seçeneklerden Linux seçerek devam edelim. Yani biz birazdan Linux komutlarıyla Server içindeki kısımları göreceğiz demektir.
Yeni pencerenin sol alt köşesindeki SSH:techubuntu yazısı bu işlemi başarıyla yaptığımızı göstermektedir.

Bu yeni pencerenin menü kısmından şekilde gösterilen yerden New Terminal açarak AWS deki server işlemlerimizi buradan yürüteceğiz.

Bu pencerenin okla gösterilen yerlerine tıklayarak Open Folder ile bu server daki dosya işlemlerini kolayca yönetebiliriz.

Şimdi sırayla alttaki pencerelerdeki işlemleri de yapınız

Gerekirse tekrar terminal kısmını açınız.

Terminal bağlantımızın gerçekleştiğine dair alttaki resimdeki yeşil satırı görebilirsiniz. Bu bölüm bizim AWS deki uzak makinemizin içini göstermektedir.

6. Bundan sonraki kısımları bu terminal ekranından yürüteceğiz.
İlk etapta uzak makinemize PostgreSQL kuracağız. Çünkü şu an uzak makinemizin (yani Server) içi boş.
“pwd” komutunu uygulayarak makinenin dosya yapısını görelim

Önce son paketlerin makineye inmesi için alt komutu çalıştıralım

sudo apt update -y

PostgreSQL i kurmak için alttaki komutu yazalım. (copy paste yapınız)

sudo apt install postgresql -y

Postgre başarılı biçimde kurulduktan sonra alttaki kod ile versiyon kontrolü yapalım.

psql -V

Alt resime göre Postgre 14.11 versiyonun yüklendiğini görüyoruz

7. Şimdi bizim uzaktan bu makineye bağlantı yapılabilmesi için yani server olan bu makineye herkesin bağlanabilmesi için Configurasyonda 2 önemli değişikliği 2 dosya ile yapmamız gerekiyor. Bu değişiklikler uzaktan bağlantıya izin verecektir.

AWS uzak makinedeki postgresql ‘e ulaşmak için alttaki komutu uygulayalım ve postgresql içine giriş yapalım. Alttaki görüntünün okla gösterilen en alt satırı postgreye bağlandığınızı gösterir. Eğer ilerde bu kısımdan tekrar ubuntu komut satırına dönmek isterseniz \q yazmanız gerekecektir.

sudo -u postgres psql

Eğer her kullanıcı SuperUser yetkisi ile Database’e bağlanırsa bu sorun oluşturulabilir. O yüzden bu database bağlanabilmek üzere alttaki kod ile USER oluşturalım. Kodu yazınca alt satırda CREATE ROLE yazısı oluşmalıdır.

CREATE USER techubuntu  WITH PASSWORD 'tech1234';

Bu rolü görmek için komut bölümüne \du yazalım.

Bu User’a superuser yetkisi vermek için alttaki komutu yazalım: (sonra yine \du komutu ile altta görelim sonucu)

ALTER ROLE techubuntu SUPERUSER;

8. Şimdi artık Database oluşturma aşamasına geldik. Bu database’i öncelikle Linux komutlarıyla ve VSCode terminalinde yazarak oluşturacağız. Sonra SQL arayüzü olarak kullanacağımız DBeaver üzerinden bu database’ in başarılı olup olmadığını veya diğer kayıtları başarıyla girip girmediğimizi görmüş olacağız.
Database oluşturmak için:

CREATE DATABASE mydatabase;

Sonra \l yazarak database’leri listeleyelim: (mydatabase listede görülmektedir, diğerleri default oalrak vardır)

Şimdi de az önce oluşturduğumuz techubuntu user rolünü mydatabase üzerinde işlem yapabilmesi (data manipulation) için yetkilendiriyoruz. Önce komut satırını yazıp enter yapalım sonra \du ile user’ları ve yetki durumlarını listeleyelim

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO techubuntu;
\du -->> kullanıcıları listeler

9. Şimdi SQL arayüzü olarak kullandığımız DBeaver üzerinden techubuntu user bilgileri ile bağlanacağız. Bunun için öncelikle VSCode terminaldeki postgre den çıkış yapalım. Bu amaçla \q komutu kullanacağız.

10. Uzak makineye herkesin ulaşabilmesi için Configurasyon ayarlarını herkesin ulaşabileceği şekle dönüştürmemiz gerekiyor. Bu amaçla 2 adet configurasyon dosyasında mini değişiklikler yapacağız. Bütün toolların bir config dosyası vardır ve dışardan erişimi bunlar kontrol ederler.

İlk olarak 1.configurasyon dosyasını alttaki komut ile açalım.

sudo vim /etc/postgresql/14/main/postgresql.conf

Dosya açılınca terminal ekranı alttaki gibi olur:

Bu config dosyasının içinde gezinmek ve değişiklik için klavyedeki Insert tuşuna basmak gerekir. Bu dosyada tek bir variable değişeceğiz ve bu sayede dışardan bağlantı kurmaya izin vermiş olacağız. Ekranda aşağı inince alttaki görülen listen_adresses =’localhost’ yazan yeri ‘*’ olarak değişeceğiz. * (yıldız) işareti tüm makinelerin bağlantı kurabilecekleri anlamındadır. Eğer sadece tek bir makineye izin vermek isteseydik o zaman ilgili makinenin IP numarasını buraya yazmamız gerekirdi.

ÖNEMLİ NOT: Üstteki resimde #listen_adressses yazısının başındaki # işaretini silmeniz gereklidir. Aksi halde bu satırdaki işleminiz geçersiz sayılır, çünkü bu satır dosyada yine yorum satırı olarak kalacaktır.

Dosyayı kaydetmek için önce ESC ye basıp düzenleme modundan çıkacağız, sonra da komut satırına :wq yazacağız.

11. Gelelim 2.config dosyasının değiştirmeye. Alttaki komutu girelim

sudo vim /etc/postgresql/14/main/pg_hba.conf

Bu dosyada da az önce * ile belirttiğimiz şeyi şimdi farklı biçimde yapacağız. Bu dosyanın içinde en altta yeni satır ekleyeceğiz. Bu satır md5 olan satır olacak. Sayfanın en altına gelelim ve alttaki satırı en alta ilave edelim. Bunun için yine klavyeden Insert tuşunu kullanıp alt satıra geçmemiz gerekir.

host    all             all              0.0.0.0/0               md5

En alt satırı biz ekledik.

Şimdi yine ESC ile çıkış yapıp :wq yazarak buradan çıkacağız.

12. Yeni ayarları UPDATE yapmak için alttaki kodu terminale yazalım.

sudo systemctl restart postgresql

Son sistem durumuna bakmak için ise alttaki kodu yazalım.

sudo systemctl status postgresql

13. Terminal ekranında son kez database e giriş yapma durumumuzu check edelim. Bunun için alttaki komutu yazalım.

psql -U techubuntu -h localhost -d mydatabase

Bunun devamında bize şifre soracaktır, şifre yerinde tech1234 yazalım (yukarda tanımlamıştık) ama ekranda yazdığımızda görünmeyecektir. Siz şifreyi yazıp enter yaptığınızda alttaki ekran gelecek yani database giriş yapılmış olacaktır. İlerleyen aşamalarda database den tekrar ubuntu komut satırına dönmek için \q yazmanız gerekir. Ama terminalin bu şekilde kalmasını tercih edelim şimdilik.

14. Terminal ekranımız açık halde dururken şimdi Dbeaver üzerinden uzak makinedeki Database’e bağlantımızı gerçekleştirelim. Bu amaçla daha önce bilgisayarımızda kurulu olan Dbeaver ı açalım ve resimdeki yerden New Database Connection yapalım. Sonraki pencereden ise PostgreSQL simgesini tıklayarak devam edelim.

AWS Uzak makinedeki penceremize gidip daha önce oradan copy yaptığımız Instance a ait IPv4 numarasını tekrar kopyalayalım.

Şimdi de alt resimde görülen ilgili yerlere gereken bilgileri girelim. Bu ekranda ok ile gösterilen yerleri değiştirdik. Host kısmına AWS den aldığım IPv4 numarasını yazalım, database ismini mydatabase olarak değişelim, Username ve password kısmına yukarda CREATE User kısmında oluşturduğumuz kullanıcı adı ve şifre olarak techubuntu ve tech1234 bilgilerini girelim. Sonra sol alttaki Test Connection tuşuna basıp başarılı bir connection yapılmasını bekleyelim. Sonra finish diyeceğiz.

Dbeaver ekranın solunda uzak makinemizdeki mydatabase ve tabloları görmüş oluyoruz.

15. Şimdi de uzak makineye Dbeaver üzerinden Table Create edelim.

Dbeaver da yeni bir SQL Scripti resimdeki gibi mydatabase connection isminin olduğu yerde sağ tuş yaparak açalım (SQL Editor/Open SQL Script) ve içine önce alttaki kodu yapıştırarak tablo oluşturalım.

CREATE TABLE Users (
       id SERIAL PRIMARY KEY,
       name VARCHAR(100),
       email VARCHAR(100)
                             );

Daha sonra da alttaki satır ile bir data girişi yapalım.

INSERT INTO Users (name, email) VALUES ('John Doe', 'john.doe@example.com');

Oluşturduğumuz tabloyu görelim:

SELECT * FROM Users;

Başarılı biçimde Dbeaver üzerinden tablo oluşturup giriş yapmış olduk.

16. Şimdi Dbeaver da yaptıklarımızın gerçekten uzak makinede gerçekleşme durumunu sorgulayalım. Bunun için alttaki kodu VS Code daki terminale yazalım. Kodu yazınca techubuntu kullanıcına ait şifreyi soracaktır. Yukarıda yaptığımız gibi şifre görünmeden yazıp enter yapacağız (aslında halen o kısımı yukarıdaki maddede açık halde bırakmıştım, o yüzden benim ekranımda yeniden yazmaya gerek yok)

psql -U techubuntu -h localhost -d mydatabase 

mydatabase gelince \dt yazarak tabloları görebiliriz. Hatta Dbeaver tarafında bir farklı değişim uygulayınca burada terminal satırına yeniden \dt yazınca yeni değişimi bir kez daha görebiliriz.

Terminalde komut satırına SELECT * FROM users; yazarak tablodaki dataları da böylece görmüş oluruz. Dbeaver da yaptığımız tüm işlemlerin aynı zamanda burada da yansımasını görmüş oluruz.

Çıkış için alttaki komutu kullanabiliriz.

\q -->> çıkış

Terminaldeki bağlantıyı sonlandırmak için de resimde sol alttaki connection tuşuna tıklayıp Close Remote Connection diyelim

NOT: İşleriniz bitince en son ASW ekranına gidip çalıştığınız Instance ı seçip Terminate etmeyi unutmayınız.

Bu çalışma sayesinde biz hem uzak makine oluşturmayı, hem bu uzak makineye SQL altyapısı kurmayı, sonra Dbeaver gibi üçüncü part uygulama üzerinden data manipülasyonunu görmüş olduk. Sektörel işlerde akış bu şekilde gerçekleşebilmektedir.

Biz bu çalışmada uzak makine yönetimini manuel olarak EC2 ile tesis ettik ama AWS nin RDS diye bir hizmeti SQL ile alakalı her işlemi kendi bünyesinde otomatize eden bir sisteme de sahiptir. EC2 kısmındaki tüm işlemlerin yönetimi ve sorumluluğu bize aittir. RDS kısmında ise sorumluluk AWS ye aittir. Elbette bu kısmın ücretlendirmesi de farklı olmaktadır. Ekstra bakım onarım işlemleri için AWS ücret almaktadır. RDS üzerinden bir Free SQL otomasyonu kurmak isterseniz alttaki resimlerle oluşturabilirsiniz.

Resimdeki PostgreSQL i seçince postgresql i herhangi bir AWS makinesinde kendisi kurar, biz müdahale edemeyiz. Alttaki Multi A-Z seçeneğinde farklı ülkelerdeki makinelerde aynı anda kurarak daha sağlam yapılmış olur ama elbette ücreti artar.

Alt resimdeki Free Tier özeliğini seçip bu sayfadaki özellikleri kurabiliriz.

Tüm ayarları yaptıktan sonra vereceği IP ile yine Dbeaver a gidip yine aynı bağlantı işlemlerini yukarıdaki gibi yapmış olacağız.

Bu makalede detaylıca AWS uzak makinedeki bir server’a local makinemiz üzerinden bağlantı sağlayıp orada data manipülasyon yapmayı gördük.

Faydalı olması dileklerimle.