{"id":2293,"date":"2024-06-02T14:20:27","date_gmt":"2024-06-02T11:20:27","guid":{"rendered":"https:\/\/www.fibacloud.com\/community\/tutorials\/?p=2293"},"modified":"2026-09-20T21:55:54","modified_gmt":"2026-09-20T18:55:54","slug":"postgresql-kurulumu-ve-optimizasyonu-nasil-yapilir","status":"publish","type":"post","link":"https:\/\/www.fibacloud.com\/community\/tutorials\/postgresql-kurulumu-ve-optimizasyonu-nasil-yapilir\/","title":{"rendered":"Ubuntu 24.04: PostgreSQL 16 Kurulumu ve Optimizasyonu"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"2293\" class=\"elementor elementor-2293\" data-elementor-post-type=\"post\">\n\t\t\t\t<div class=\"elementor-element elementor-element-1c1e11f e-flex e-con-boxed e-con e-parent\" data-id=\"1c1e11f\" data-element_type=\"container\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"elementor-element elementor-element-651c44c elementor-widget elementor-widget-text-editor\" data-id=\"651c44c\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<style>\/*! elementor - v3.21.0 - 08-05-2024 *\/\n.elementor-widget-text-editor.elementor-drop-cap-view-stacked .elementor-drop-cap{background-color:#69727d;color:#fff}.elementor-widget-text-editor.elementor-drop-cap-view-framed .elementor-drop-cap{color:#69727d;border:3px solid;background-color:transparent}.elementor-widget-text-editor:not(.elementor-drop-cap-view-default) .elementor-drop-cap{margin-top:8px}.elementor-widget-text-editor:not(.elementor-drop-cap-view-default) .elementor-drop-cap-letter{width:1em;height:1em}.elementor-widget-text-editor .elementor-drop-cap{float:left;text-align:center;line-height:1;font-size:50px}.elementor-widget-text-editor .elementor-drop-cap-letter{display:inline-block}<\/style>\t\t\t\t<p>Bu rehber, yeni bir Ubuntu 24.04 LTS sunucusunda PostgreSQL 16 kurulumunu ve ayn\u0131 sunucuda \u00e7al\u0131\u015fan bir uygulaman\u0131n ba\u011flant\u0131s\u0131n\u0131 ele al\u0131r. Uygulama i\u00e7in s\u0131n\u0131rl\u0131 bir hesap olu\u015fturur, yanl\u0131\u015f parola ve yetkisiz i\u015flem denemelerini kontrol eder, ard\u0131ndan yede\u011fi ayr\u0131 bir veritaban\u0131na geri y\u00fckleriz. Son b\u00f6l\u00fcmde performans ayarlar\u0131n\u0131 \u00f6l\u00e7erek de\u011fi\u015ftirme y\u00f6ntemi vard\u0131r.<\/p>\n<p>Mevcut veritaban\u0131n\u0131 y\u00fckseltme, CentOS\/RHEL kurulumu, y\u00fcksek eri\u015filebilirlik ve ba\u015fka sunuculardan ba\u011flant\u0131 bu rehberin kapsam\u0131 d\u0131\u015f\u0131ndad\u0131r. Bu durumlarda mevcut veriye dokunmadan \u00fcr\u00fcn\u00fcn ilgili s\u00fcr\u00fcm belgelerini ve bir ge\u00e7i\u015f plan\u0131n\u0131 kullan\u0131n.<\/p>\n<p><strong>Do\u011frulama \u2014 20 Eyl\u00fcl 2026:<\/strong> Ubuntu 24.04 ortam\u0131nda PostgreSQL 16.15 ile SQL ad\u0131mlar\u0131, TCP \u00fczerinden SCRAM parola kontrol\u00fc, yetki retleri, yedek geri y\u00fckleme ve <code>pg_ctlcluster<\/code> ile yeniden ba\u015flatma denendi. \u0130zole ortamda <code>systemctl<\/code> ve AppArmor entegrasyonu u\u00e7tan uca s\u0131nanmad\u0131\u011f\u0131ndan, servis ve i\u015fletim sistemi g\u00fcvenlik kontrollerini hedef sunucunuzda ayr\u0131ca yap\u0131n.<\/p>\n<h2>1. \u00d6n ko\u015fullar ve s\u00fcr\u00fcm kontrol\u00fc<\/h2>\n<p>Sudo yetkili bir Linux hesab\u0131, yeterli disk alan\u0131 ve g\u00fcncel Ubuntu paket kaynaklar\u0131 gerekir. Komutlar tek bir PostgreSQL 16 <code>main<\/code> k\u00fcmesini ve 5432 numaral\u0131 yerel portu varsayar. Mevcut servis bulunan bir sunucuda \u00f6nce veri yede\u011fini ve geri y\u00fckleme plan\u0131n\u0131 do\u011frulay\u0131n; farkl\u0131 k\u00fcme veya s\u00fcr\u00fcm varsa a\u015fa\u011f\u0131daki ad\u0131mlar\u0131 do\u011frudan uygulamay\u0131n.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-0793655 elementor-widget elementor-widget-text-editor\" data-id=\"0793655\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">cat \/etc\/os-release\ndf -h \/var\/lib<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-6d4ab6f elementor-widget elementor-widget-text-editor\" data-id=\"6d4ab6f\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Disk kontrol\u00fc varsay\u0131lan veri dizininin yerle\u015fece\u011fi <code>\/var\/lib<\/code> dosya sistemini g\u00f6sterir. \u0130\u015fletim sistemi Ubuntu 24.04 olmal\u0131d\u0131r. Da\u011f\u0131t\u0131m\u0131n PostgreSQL 16 paketini a\u00e7\u0131k s\u00fcr\u00fcm ad\u0131yla kurun; bu i\u015flem b\u00fcy\u00fck s\u00fcr\u00fcm y\u00fckseltmesi yapma talimat\u0131 de\u011fildir.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-902fe3e elementor-widget elementor-widget-text-editor\" data-id=\"902fe3e\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">sudo apt update\napt-cache policy postgresql-16\nsudo apt install postgresql-16 postgresql-client-16\npg_lsclusters<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-75ff88c elementor-widget elementor-widget-text-editor\" data-id=\"75ff88c\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Ubuntu paketleri normal yeni kurulumda <code>16 main<\/code> k\u00fcmesini olu\u015fturur. \u00c7\u0131kt\u0131da bu k\u00fcmenin veri dizinini, portunu ve durumunu kontrol edin. K\u00fcme yoksa veya ba\u015fka s\u00fcr\u00fcm\/port g\u00f6r\u00fcn\u00fcyorsa durun ve paket kurulum g\u00fcnl\u00fc\u011f\u00fcn\u00fc inceleyin. Var olan veri dizininde <code>initdb<\/code> \u00e7al\u0131\u015ft\u0131rmay\u0131n ve k\u00fcmeyi silip yeniden olu\u015fturmay\u0131n.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-ed1403c elementor-widget elementor-widget-text-editor\" data-id=\"ed1403c\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">sudo systemctl start postgresql@16-main\nsudo systemctl is-active postgresql@16-main\nsudo -u postgres psql -X -p 5432 -d postgres -c 'SELECT version();'<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-0061d8f elementor-widget elementor-widget-text-editor\" data-id=\"0061d8f\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p><code>is-active<\/code> \u00e7\u0131kt\u0131s\u0131 <code>active<\/code>, sunucu s\u00fcr\u00fcm\u00fc <code>16.x<\/code> olmal\u0131d\u0131r. <code>postgresql.service<\/code> \u00fcst birimi tek ba\u015f\u0131na ger\u00e7ek k\u00fcmenin sa\u011fl\u0131kl\u0131 oldu\u011funu kan\u0131tlamaz; SQL ba\u011flant\u0131s\u0131n\u0131 da do\u011frulay\u0131n. Paket depolar\u0131n\u0131n sundu\u011fu g\u00fcncel 16.x g\u00fcvenlik d\u00fczeltmelerini bak\u0131m politikan\u0131za g\u00f6re uygulay\u0131n.<\/p>\n<h2>2. Yerel eri\u015fimi ve kimlik do\u011frulamay\u0131 kontrol edin<\/h2>\n<p>PostgreSQL y\u00f6neticisi olarak yerel Unix soketinden ba\u011flan\u0131n. Bu ba\u011flant\u0131 i\u00e7in Linux <code>postgres<\/code> hesab\u0131n\u0131n peer kimlik do\u011frulamas\u0131 kullan\u0131l\u0131r; uygulamaya bu hesab\u0131 vermeyece\u011fiz.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-40fc46a elementor-widget elementor-widget-text-editor\" data-id=\"40fc46a\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">sudo -u postgres psql -X -p 5432 -d postgres<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c0ad52b elementor-widget elementor-widget-text-editor\" data-id=\"c0ad52b\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-sql\">SHOW listen_addresses;\nSHOW port;\nSHOW password_encryption;\nSHOW config_file;\nSHOW hba_file;\nSELECT line_number, type, database, user_name, address, auth_method, error\nFROM pg_hba_file_rules\nORDER BY line_number;<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-0e1ebc6 elementor-widget elementor-widget-text-editor\" data-id=\"0e1ebc6\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Yeni Ubuntu kurulumunda beklenen de\u011ferler <code>localhost<\/code>, <code>5432<\/code> ve <code>scram-sha-256<\/code> \u015feklindedir. Kurallarda <code>error<\/code> alan\u0131 bo\u015f olmal\u0131; yerel y\u00f6netim i\u00e7in <code>peer<\/code>, loopback TCP ba\u011flant\u0131lar\u0131 i\u00e7in <code>scram-sha-256<\/code> kullan\u0131lmal\u0131d\u0131r. <code>pg_hba.conf<\/code> ilk e\u015fle\u015fen kural\u0131 uygular: daha \u00f6nceki bir <code>trust<\/code> sat\u0131r\u0131n\u0131 g\u00f6zden ka\u00e7\u0131rmay\u0131n.<\/p>\n<p>A\u015fa\u011f\u0131daki sat\u0131rlar beklenen kural t\u00fcrlerini g\u00f6sterir; t\u00fcm dosyan\u0131n yerine yap\u0131\u015ft\u0131r\u0131lacak bir yap\u0131land\u0131rma de\u011fildir.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-b8f4c71 elementor-widget elementor-widget-text-editor\" data-id=\"b8f4c71\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-text\">local   all   postgres                         peer\nhost    all   all       127.0.0.1\/32            scram-sha-256\nhost    all   all       ::1\/128                 scram-sha-256<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-0515832 elementor-widget elementor-widget-text-editor\" data-id=\"0515832\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>De\u011ferler farkl\u0131ysa yeni hesap olu\u015fturmadan \u00f6nce nedenini belirleyin. Bu yerel kurulum i\u00e7in <code>listen_addresses='*'<\/code>, herkese a\u00e7\u0131k 5432 veya <code>trust<\/code> kimlik do\u011frulamas\u0131 gerekmez. Dosya de\u011fi\u015fikli\u011fi gerekiyorsa \u00f6nce mevcut dosyan\u0131n izinlerini koruyan bir yede\u011fini al\u0131n; yaln\u0131z gerekli sat\u0131rlar\u0131 d\u00fczenleyin ve s\u00f6zdizimini tekrar kontrol edin. HBA de\u011fi\u015fiklikleri reload, <code>listen_addresses<\/code> de\u011fi\u015fikli\u011fi servis restart gerektirir. Uzaktan eri\u015fim gerekiyorsa \u00f6zel a\u011f, dar kaynak IP kurallar\u0131 ve do\u011frulanm\u0131\u015f TLS i\u00e7eren ayr\u0131 bir tasar\u0131m kullan\u0131n.<\/p>\n\n<figure class=\"fc-editorial-figure\" data-fc-visual=\"20260920\" aria-labelledby=\"fc-editorial-caption-2293\"><a href=\"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-postgres-tr-inline-20260920.webp\" target=\"_blank\" rel=\"noopener\" aria-label=\"G\u00f6rseli tam boyutta a\u00e7\"><picture><source media=\"(max-width: 600px)\" srcset=\"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-postgres-tr-mobile-20260920.webp\" width=\"720\" height=\"1140\"><img loading=\"lazy\" src=\"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-postgres-tr-inline-20260920.webp\" srcset=\"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-postgres-tr-inline-20260920.webp 1440w, https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-postgres-tr-inline-20260920-300x242.webp 300w, https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-postgres-tr-inline-20260920-1024x825.webp 1024w, https:\/\/www.fibacloud.com\/community\/tutorials\/wp-content\/uploads\/community-editorial-20260920\/fc-postgres-tr-inline-20260920-768x619.webp 768w\" sizes=\"auto, (max-width: 900px) 100vw, 820px\" width=\"1440\" height=\"1160\" alt=\"Sahiplik ile uygulama eri\u015fimini ay\u0131r\u0131n\" class=\"wp-image-2608\"  decoding=\"async\"><\/picture><\/a><figcaption id=\"fc-editorial-caption-2293\"><strong>Sahiplik ile uygulama eri\u015fimini ay\u0131r\u0131n<\/strong> Veritaban\u0131 sahibi ile \u00e7al\u0131\u015fma hesab\u0131 farkl\u0131 roller \u00fcstlenir. SCRAM kimlik do\u011frulamas\u0131 yetki s\u0131n\u0131r\u0131n\u0131n yerine ge\u00e7mez; a\u011f eri\u015fimi, rol izinleri ve geri y\u00fckleme testi ayr\u0131ca do\u011frulan\u0131r.<details><summary>G\u00f6rselin ad\u0131m ad\u0131m a\u00e7\u0131klamas\u0131<\/summary><ol><li>Ay\u0131r: Sahiplik ve \u00e7al\u0131\u015fma rollerini ay\u0131r.<\/li><li>Do\u011frula: SCRAM ile kimlik do\u011frulamay\u0131 yap\u0131land\u0131r.<\/li><li>S\u0131n\u0131rla: A\u011f ve nesne izinlerini birlikte denetle.<\/li><li>Geri y\u00fckle: Yede\u011fi ayr\u0131 veritaban\u0131nda test et.<\/li><\/ol><\/details><\/figcaption><\/figure>\n<h2>3. Uygulama hesab\u0131n\u0131 veri sahibi rol\u00fcnden ay\u0131r\u0131n<\/h2>\n<p>A\u015fa\u011f\u0131daki adlar \u00f6rnektir ve yeni kurulumda hen\u00fcz bulunmamal\u0131d\u0131r: <code>fc_app_owner<\/code>, <code>fc_app<\/code>, <code>fc_appdb<\/code>. Hata \u00e7\u0131karsa komutlar\u0131 tekrar tekrar \u00e7al\u0131\u015ft\u0131rmay\u0131n; \u00e7ak\u0131\u015fan mevcut nesneyi inceleyin. <code>ON_ERROR_STOP<\/code> hata halinde kalan beti\u011fin s\u00fcrmesini \u00f6nler.<\/p>\n<p>Y\u00f6netici psql oturumunda \u00e7al\u0131\u015ft\u0131r\u0131n. <code>\\password<\/code> parolay\u0131 g\u00f6r\u00fcnmeden sorar; g\u00fc\u00e7l\u00fc ve benzersiz bir parola \u00fcretin. Parolay\u0131 SQL metnine veya kabuk komut sat\u0131r\u0131na yazmay\u0131n.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-3c84af2 elementor-widget elementor-widget-text-editor\" data-id=\"3c84af2\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-sql\">\\set ON_ERROR_STOP on\nCREATE ROLE fc_app_owner NOLOGIN;\nCREATE ROLE fc_app LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;\n\\password fc_app\nCREATE DATABASE fc_appdb OWNER fc_app_owner;\n\\connect fc_appdb\nREVOKE ALL ON DATABASE fc_appdb FROM PUBLIC;\nGRANT CONNECT ON DATABASE fc_appdb TO fc_app;\nREVOKE CREATE ON SCHEMA public FROM PUBLIC;\nSET ROLE fc_app_owner;\nCREATE SCHEMA app AUTHORIZATION fc_app_owner;\nCREATE TABLE app.healthcheck (\n    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n    message text NOT NULL\n);\nINSERT INTO app.healthcheck (message) VALUES ('ilk kontrol');\nGRANT USAGE ON SCHEMA app TO fc_app;\nGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO fc_app;\nGRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO fc_app;\nALTER DEFAULT PRIVILEGES FOR ROLE fc_app_owner IN SCHEMA app\n    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO fc_app;\nALTER DEFAULT PRIVILEGES FOR ROLE fc_app_owner IN SCHEMA app\n    GRANT USAGE, SELECT ON SEQUENCES TO fc_app;\nRESET ROLE;\n\\quit<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-39c324c elementor-widget elementor-widget-text-editor\" data-id=\"39c324c\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p><code>fc_app<\/code> uygulaman\u0131n veri okuma\/yazma hesab\u0131d\u0131r; superuser de\u011fildir, rol veya veritaban\u0131 olu\u015fturamaz ve tablo sahibi de\u011fildir. \u015eema de\u011fi\u015fiklikleri ayr\u0131 yetkili bak\u0131m oturumunda <code>fc_app_owner<\/code> rol\u00fcyle yap\u0131l\u0131r. Varsay\u0131lan yetkiler yaln\u0131z bu rol\u00fcn <code>app<\/code> \u015femas\u0131nda daha sonra olu\u015fturaca\u011f\u0131 nesnelere uygulan\u0131r; farkl\u0131 bir sahip rol\u00fcyle olu\u015fturulan tablolar\u0131 otomatik kapsamaz.<\/p>\n<h2>4. Ba\u015far\u0131l\u0131 ve reddedilmesi gereken ba\u011flant\u0131lar\u0131 deneyin<\/h2>\n<p>Normal Linux hesab\u0131n\u0131zdan TCP loopback ba\u011flant\u0131s\u0131 a\u00e7\u0131n. <code>-W<\/code> parolay\u0131 etkile\u015fimli sorar; <code>-h 127.0.0.1<\/code> sayesinde peer yerine parola do\u011frulamas\u0131n\u0131 denemi\u015f oluruz.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-95d535b elementor-widget elementor-widget-text-editor\" data-id=\"95d535b\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">psql -X -h 127.0.0.1 -p 5432 -U fc_app -d fc_appdb -W<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-b557693 elementor-widget elementor-widget-text-editor\" data-id=\"b557693\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-sql\">SELECT current_user, current_database();\nSELECT * FROM app.healthcheck ORDER BY id;\nINSERT INTO app.healthcheck (message) VALUES ('uygulama ba\u011flant\u0131s\u0131 ba\u015far\u0131l\u0131');\nSELECT * FROM app.healthcheck ORDER BY id;<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-2fac3a7 elementor-widget elementor-widget-text-editor\" data-id=\"2fac3a7\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Kullan\u0131c\u0131 <code>fc_app<\/code>, veritaban\u0131 <code>fc_appdb<\/code> ve iki sat\u0131r beklenir. Ard\u0131ndan a\u015fa\u011f\u0131daki iki komutu <strong>ayr\u0131 ayr\u0131<\/strong> deneyin. \u0130kisi de yetki hatas\u0131 vermelidir; ba\u015far\u0131l\u0131 olursa kurulumu tamamlanm\u0131\u015f saymay\u0131n.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-37efcdd elementor-widget elementor-widget-text-editor\" data-id=\"37efcdd\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-sql\">CREATE TABLE app.must_not_exist (id integer);\nCREATE ROLE fc_must_not_exist;<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c7fe576 elementor-widget elementor-widget-text-editor\" data-id=\"c7fe576\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Oturumdan <code>\\quit<\/code> ile \u00e7\u0131k\u0131n. Ayn\u0131 ba\u011flant\u0131y\u0131 tekrar a\u00e7\u0131p bilerek yanl\u0131\u015f parola girin: <code>password authentication failed<\/code> beklenir. Bu testin ba\u015far\u0131s\u0131z olmas\u0131, do\u011fru parolan\u0131n ba\u015far\u0131s\u0131 kadar \u00f6nemlidir. Uygulaman\u0131z\u0131n ba\u011flant\u0131 ayarlar\u0131nda kendi hesab\u0131n\u0131 kullan\u0131n; <code>postgres<\/code> parolas\u0131n\u0131 uygulamaya ta\u015f\u0131may\u0131n.<\/p>\n<h2>5. Yede\u011fi al\u0131n ve ayr\u0131 veritaban\u0131na geri y\u00fckleyin<\/h2>\n<p>Yede\u011fin al\u0131nm\u0131\u015f olmas\u0131 geri y\u00fcklenebildi\u011fini kan\u0131tlamaz. A\u015fa\u011f\u0131daki \u00f6rnek, veri kayna\u011f\u0131n\u0131 de\u011fi\u015ftirmeden yeni <code>fc_restore_check<\/code> veritaban\u0131na d\u00f6n\u00fc\u015f\u00fc s\u0131nar. Bu adlar ve yedek dosyalar\u0131 \u00f6nceden bulunuyorsa farkl\u0131, a\u00e7\u0131k\u00e7a belirlenmi\u015f bir test ad\u0131 se\u00e7in.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-78e6373 elementor-widget elementor-widget-text-editor\" data-id=\"78e6373\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">sudo install -d -o postgres -g postgres -m 700 \/var\/lib\/postgresql\/fc-backups\nsudo -u postgres sh -c 'umask 077; test ! -e \/var\/lib\/postgresql\/fc-backups\/fc_appdb.dump &amp;&amp; pg_dump -p 5432 -Fc -f \/var\/lib\/postgresql\/fc-backups\/fc_appdb.dump fc_appdb'\nsudo -u postgres pg_restore --list \/var\/lib\/postgresql\/fc-backups\/fc_appdb.dump\nsudo -u postgres createdb -p 5432 -T template0 -O fc_app_owner fc_restore_check\nsudo -u postgres pg_restore -p 5432 --exit-on-error --single-transaction -d fc_restore_check \/var\/lib\/postgresql\/fc-backups\/fc_appdb.dump\nsudo -u postgres psql -X -p 5432 -d fc_restore_check -c 'SELECT * FROM app.healthcheck ORDER BY id;'<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-b040128 elementor-widget elementor-widget-text-editor\" data-id=\"b040128\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Komutlar hatas\u0131z tamamlanmal\u0131 ve geri y\u00fcklenen tabloda \u00f6nceki iki sat\u0131r g\u00f6r\u00fcnmelidir. <code>--single-transaction<\/code> ile geri y\u00fcklemedeki hata yar\u0131m veri b\u0131rakmadan i\u015flemi geri al\u0131r. Var olan uygulama veritaban\u0131na <code>--clean<\/code> ile yazarak test yapmay\u0131n. \u00d6rnekte ayn\u0131 k\u00fcmedeki roller zaten vard\u0131r; ba\u015fka sunucuya d\u00f6n\u00fc\u015fte sahip ve yetki rollerini \u00f6nceden g\u00fcvenle olu\u015fturmak gerekir.<\/p>\n<p><code>pg_dump<\/code> tek veritaban\u0131n\u0131 yedekler; k\u00fcme rollerini ve yap\u0131land\u0131rma dosyalar\u0131n\u0131 i\u00e7ermez. Roller i\u00e7in ayr\u0131ca korumal\u0131 bir \u00e7\u0131kt\u0131 al\u0131n:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-36375de elementor-widget elementor-widget-text-editor\" data-id=\"36375de\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">sudo -u postgres sh -c 'umask 077; test ! -e \/var\/lib\/postgresql\/fc-backups\/globals.sql &amp;&amp; pg_dumpall -p 5432 --globals-only -f \/var\/lib\/postgresql\/fc-backups\/globals.sql'<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-078135b elementor-widget elementor-widget-text-editor\" data-id=\"078135b\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Bu dosya parola \u00f6zetleri ve yetkiler i\u00e7erebilir. Yedekleri \u015fifreli bi\u00e7imde sunucu d\u0131\u015f\u0131na kopyalay\u0131n; eri\u015fim, saklama s\u00fcresi ve d\u00fczenli geri y\u00fckleme denemesini planlay\u0131n. Yap\u0131land\u0131rma dosyalar\u0131n\u0131 da izinlerini koruyarak ayr\u0131 yedekleyin. Ayn\u0131 diskteki \u00f6rnek dosyalar disk kayb\u0131na kar\u015f\u0131 koruma sa\u011flamaz. S\u00fcrekli ar\u015fivleme\/PITR ve kesinti hedefleri i\u00e7in PostgreSQL&#8217;in WAL yedekleme belgelerini izleyin. Kayna\u011f\u0131 g\u00fcvenilmeyen SQL veya dump dosyas\u0131n\u0131 y\u00f6netici olarak geri y\u00fcklemeyin.<\/p>\n<p>Test veritaban\u0131n\u0131 kald\u0131rmak isterseniz \u00f6nce ad\u0131n\u0131 ve i\u00e7eri\u011fini tekrar do\u011frulay\u0131n; uygulama veritaban\u0131na y\u00f6nelik silme komutu bu rehberde yoktur.<\/p>\n<h2>6. Performans\u0131 \u00f6l\u00e7erek iyile\u015ftirin<\/h2>\n<p>Her sunucuya ayn\u0131 <code>shared_buffers<\/code>, <code>work_mem<\/code> veya <code>max_connections<\/code> de\u011ferini vermek do\u011fru de\u011fildir. \u00d6nce normal y\u00fck alt\u0131nda gecikme, aktif ba\u011flant\u0131, RAM ve disk kullan\u0131m\u0131n\u0131 kaydedin. Mevcut ayarlar\u0131 g\u00f6rmek i\u00e7in y\u00f6netici oturumunda:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-8d76670 elementor-widget elementor-widget-text-editor\" data-id=\"8d76670\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-sql\">SELECT name, setting, unit, context, pending_restart\nFROM pg_settings\nWHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem',\n               'effective_cache_size', 'max_connections', 'autovacuum')\nORDER BY name;\nSELECT datname, numbackends, xact_commit, xact_rollback, deadlocks\nFROM pg_stat_database\nWHERE datname = 'fc_appdb';<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-1c70ded elementor-widget elementor-widget-text-editor\" data-id=\"1c70ded\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<ul>\n<li>Yava\u015f sorgunun plan\u0131n\u0131 \u00f6nce <code>EXPLAIN<\/code> ile inceleyin; <code>EXPLAIN ANALYZE<\/code> sorguyu ger\u00e7ekten \u00e7al\u0131\u015ft\u0131r\u0131r. Veri de\u011fi\u015ftiren sorgularda \u00fcretim \u00fczerinde deneme yapmay\u0131n.<\/li>\n<li>\u0130ndeksleri ger\u00e7ek filtre ve s\u0131ralama ihtiyac\u0131na g\u00f6re ekleyin. Yazma maliyeti ve disk kullan\u0131m\u0131n\u0131 da \u00f6l\u00e7\u00fcn; her s\u00fctuna indeks eklemek \u00e7\u00f6z\u00fcm de\u011fildir.<\/li>\n<li><code>work_mem<\/code> t\u00fcm sunucu i\u00e7in tek bellek havuzu de\u011fildir: ayn\u0131 anda bir\u00e7ok sorgu ve i\u015flem d\u00fc\u011f\u00fcm\u00fc bu belle\u011fi kullanabilir. Art\u0131\u015f\u0131 e\u015fzamanl\u0131 y\u00fckle s\u0131nay\u0131n.<\/li>\n<li><code>max_connections<\/code> de\u011ferini y\u00fckseltmeden \u00f6nce ba\u011flant\u0131 havuzu ve uygulaman\u0131n ba\u011flant\u0131lar\u0131 kapatmas\u0131n\u0131 inceleyin.<\/li>\n<li>Autovacuum&#8217;u a\u00e7\u0131k tutun. Disk dolulu\u011fu, uzun i\u015flemler ve eski sat\u0131r birikimi i\u00e7in izleme kurun; <code>fsync<\/code> veya <code>full_page_writes<\/code> kapatarak h\u0131z elde etmeye \u00e7al\u0131\u015fmay\u0131n.<\/li>\n<\/ul>\n<p>Tek seferde tek de\u011fi\u015fiklik yap\u0131n; eski de\u011feri ve dosya yede\u011fini kaydedin. <code>context<\/code> ve <code>pending_restart<\/code> alanlar\u0131na g\u00f6re reload veya planl\u0131 restart uygulay\u0131n. Sonra ayn\u0131 y\u00fck\u00fc tekrar \u00f6l\u00e7\u00fcn. Gecikme, hata veya bellek kullan\u0131m\u0131 k\u00f6t\u00fcle\u015firse \u00f6nceki ayara d\u00f6n\u00fcn. Bu rehber belirli bir donan\u0131m i\u00e7in performans art\u0131\u015f\u0131 garantisi vermez.<\/p>\n<h2>7. Hata giderme ve tamamlanma kontrol\u00fc<\/h2>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-fa16542 elementor-widget elementor-widget-text-editor\" data-id=\"fa16542\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<pre><code class=\"language-bash\">pg_lsclusters\nsudo journalctl -u postgresql@16-main --since '15 minutes ago' --no-pager\nsudo -u postgres psql -X -p 5432 -d postgres -c 'SELECT pg_is_in_recovery();'<\/code><\/pre>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-243a6f5 elementor-widget elementor-widget-text-editor\" data-id=\"243a6f5\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Yeni ba\u011f\u0131ms\u0131z sunucuda <code>pg_is_in_recovery()<\/code> de\u011feri <code>false<\/code> olmal\u0131d\u0131r. Servis ba\u015flam\u0131yorsa yanl\u0131\u015f s\u00fcr\u00fcm, port \u00e7ak\u0131\u015fmas\u0131, izin, disk veya yap\u0131land\u0131rma hatas\u0131n\u0131 g\u00fcnl\u00fcklerden belirleyin; veri dizinini silmek hata giderme ad\u0131m\u0131 de\u011fildir. HBA de\u011fi\u015fikli\u011finden sonra ba\u011flant\u0131 kesildiyse a\u00e7\u0131k yerel y\u00f6netim oturumundan \u00f6nceki dosyay\u0131 geri getirin ve reload edin. Parola hatas\u0131n\u0131 <code>trust<\/code> a\u00e7arak \u00e7\u00f6zmeyin.<\/p>\n<p>Bak\u0131m penceresinde kontroll\u00fc bir k\u00fcme restart&#8217;\u0131ndan sonra do\u011fru\/yanl\u0131\u015f parola, uygulama okuma\/yazma ve s\u0131n\u0131rl\u0131 yetki kontrollerini tekrarlay\u0131n. Kurulumu; yerel dinleme, SCRAM do\u011frulamas\u0131, s\u0131n\u0131rl\u0131 uygulama hesab\u0131, hatas\u0131z geri y\u00fckleme ve izleme birlikte do\u011fruland\u0131\u011f\u0131nda tamamlanm\u0131\u015f kabul edin.<\/p>\n<h2>Resm\u00ee kaynaklar<\/h2>\n<ul>\n<li><a href=\"https:\/\/www.postgresql.org\/download\/linux\/ubuntu\/\" rel=\"nofollow noopener\" target=\"_blank\">PostgreSQL Ubuntu paketleri ve desteklenen da\u011f\u0131t\u0131mlar<\/a><\/li>\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/16\/auth-pg-hba-conf.html\" rel=\"nofollow noopener\" target=\"_blank\">PostgreSQL 16: istemci kimlik do\u011frulama kurallar\u0131<\/a><\/li>\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/16\/database-roles.html\" rel=\"nofollow noopener\" target=\"_blank\">PostgreSQL 16: veritaban\u0131 rolleri<\/a><\/li>\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/16\/backup-dump.html\" rel=\"nofollow noopener\" target=\"_blank\">PostgreSQL 16: pg_dump, rol yedekleri ve geri y\u00fckleme<\/a><\/li>\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/16\/runtime-config-resource.html\" rel=\"nofollow noopener\" target=\"_blank\">PostgreSQL 16: kaynak kullan\u0131m\u0131 ayarlar\u0131<\/a><\/li>\n<li><a href=\"https:\/\/www.postgresql.org\/docs\/16\/using-explain.html\" rel=\"nofollow noopener\" target=\"_blank\">PostgreSQL 16: EXPLAIN kullan\u0131m\u0131<\/a><\/li>\n<\/ul>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Ubuntu 24.04 \u00fczerinde PostgreSQL 16: s\u0131n\u0131rl\u0131 uygulama hesab\u0131, SCRAM ba\u011flant\u0131s\u0131, yedek geri y\u00fckleme ve \u00f6l\u00e7\u00fcme dayal\u0131 performans ayarlar\u0131.<\/p>\n","protected":false},"author":1,"featured_media":2607,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[206,204,150,52],"class_list":["post-2293","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-bulut","tag-postgresql","tag-veritabani","tag-veritabanlari"],"lang":"tr","translations":{"tr":2293},"pll_sync_post":[],"_links":{"self":[{"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/posts\/2293","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/comments?post=2293"}],"version-history":[{"count":29,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/posts\/2293\/revisions"}],"predecessor-version":[{"id":2322,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/posts\/2293\/revisions\/2322"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/media\/2607"}],"wp:attachment":[{"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/media?parent=2293"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/categories?post=2293"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fibacloud.com\/community\/tutorials\/wp-json\/wp\/v2\/tags?post=2293"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}