قدم اول را برداشته‌اید: نصب PostgreSQL روی اوبونتو با دو دستور apt تمام شده و psql جواب می‌دهد. اما عددهای حافظه هنوز پیش‌فرض‌اند. نسخه 16 روی اوبونتو 24.04 با shared_buffers برابر 128MB و work_mem برابر 4MB شروع می‌کند، چه سرورتان 2GB رم داشته باشد چه 16GB.

  • ✅ مقدار shared_buffers: شروع از ۲۵ درصد رم سرور اختصاصی.
  • ✅ مقدار work_mem: سقف هر عملیات sort یا hash؛ یک اتصال می‌تواند چند سهم بگیرد.
  • ✅ مقدار effective_cache_size: فقط تخمین برنامه‌ریز کوئری.
  • ✅ مقدار maintenance_work_mem: حافظه VACUUM و CREATE INDEX.
  • ✅ دسترسی ریموت: اول pg_hba.conf، بعد listen_addresses.
  • ✅ بکاپ: pg_dump -Fc و یک بار تمرین بازگردانی.
  • ✅ کوئری کند: روشن کردن log_min_duration_statement.

💡 سه حافظه با اسم‌های شبیه و رفتار متفاوت

پارامتر shared_buffers حافظه مشترک همه اتصال‌هاست و فقط با ری‌استارت عوض می‌شود.
پارامتر work_mem برای هر عملیات sort یا hash جدا حساب می‌شود.
پارامتر effective_cache_size هیچ حافظه‌ای نمی‌گیرد.

هدف، دو عدد و یک فهرست است: shared_buffers و work_mem سرور خودتان و IPهایی که حق اتصال دارند، هر کدام با دلیل. پیش‌فرض‌ها و خروجی‌های این متن همه از یک اوبونتو 24.04 تازه است.

نصب postgresql، تقسیم رم سرور مجازی بین حافظه مشترک دیتابیس، حافظه کاری کوئری‌ها و کش سیستم‌عامل

بعد از نصب PostgreSQL روی اوبونتو، اول چه چیزی را باید ببینیم؟

پاسخ کوتاه: نسخه‌ای که واقعاً نصب شده و مقدار فعلی پارامترهای حافظه و اتصال؛ بدون این دو، هر تغییری حدس است.

نسخه و خوشه‌ای که اوبونتو ساخته است

در اوبونتو 24.04 بسته postgresql به postgresql-16 وابسته است. دستور اول فهرست بسته‌ها را تازه می‌کند و دومی سرور را نصب می‌کند؛ نسخه نصب‌شده 16.15 بود، با خوشه main روی پورت 5432.

sudo apt update
sudo apt install postgresql
pg_lsclusters

خروجی pg_lsclusters وضعیت خوشه و مسیر لاگ را نشان می‌دهد: /var/log/postgresql/postgresql-16-main.log، که برای کوئری کند لازم می‌شود.

فایل‌های تنظیمات کجا هستند و الان چه می‌گویند؟

اوبونتو تنظیمات را جدا از داده‌ها در /etc/postgresql/16/main/ نگه می‌دارد. مسیر و مقدارها را از خود سرور بپرسید:

sudo -u postgres psql
SHOW config_file;
SELECT name, setting, unit, context FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem',
               'effective_cache_size', 'listen_addresses', 'max_connections');

ستون context از خود عدد مهم‌تر است: postmaster یعنی تغییر فقط با ری‌استارت اعمال می‌شود (shared_buffers و listen_addresses) و user یعنی هر جلسه می‌تواند با SET عوضش کند (work_mem). برای باز کردن این فایل‌ها در یک ویرایشگر کامل، ویرایش فایل‌های سرور با vs code remote ssh جای nano را می‌گیرد.

کاربر و دیتابیسی که مثال‌های بعدی لازم دارند

مثال‌های بعدی کاربر appuser و دیتابیس shop را لازم دارند؛ بسازیدشان یا نام‌های خودتان را بگذارید. با گزینه –pwprompt، createuser رمز را جداگانه می‌خواهد و چیزی در تاریخچه شل نمی‌ماند؛ یک رمز قوی تازه تایپ کنید.

sudo -u postgres createuser --pwprompt appuser
sudo -u postgres createdb -O appuser shop

⚠️ ستاره‌ای که همه رابط‌های شبکه را باز می‌کند

مقدار ‘*’ یعنی گوش دادن روی همه رابط‌های شبکه. اگر pg_hba.conf محدود نشده و پورت 5432 باز باشد، هر کسی که به آن IP برسد می‌تواند برای ورود تلاش کند.

سهم shared_buffers و work_mem از رم چقدر باشد؟

سهم shared_buffers و work_mem از رم چقدر باشد؟

پاسخ کوتاه: مستندات مصرف منابع PostgreSQL برای سرور اختصاصی دیتابیس با 1GB رم یا بیشتر، ۲۵ درصد رم را «نقطه شروع معقول» shared_buffers می‌داند. برای work_mem درصدی نمی‌دهد و هشدار می‌دهد که مصرف کل می‌تواند چند برابر همین عدد شود.

پارامترپیش‌فرض در PostgreSQL 16 اوبونتونوع حافظهاعمال تغییر
shared_buffers128MBمشترک بین همه اتصال‌هابا ری‌استارت
work_mem4MBبرای هر عملیات sort یا hashبا reload یا SET
maintenance_work_mem64MBبرای VACUUM و CREATE INDEXبا reload یا SET
effective_cache_size4GBبدون تخصیص؛ فقط تخمینبا reload یا SET

مقدار shared_buffers را چطور عوض کنیم؟

پیش‌فرض نصب PostgreSQL برای shared_buffers، یعنی 128MB، روی سرور 4GB حدود سه درصد رم است. اگر سرور فقط دیتابیس اجرا می‌کند، ۲۵ درصد یعنی 1GB؛ اگر اپلیکیشن هم کنارش است، کمتر بگذارید. به گفته مستندات، بیش از ۴۰ درصد بعید است بهتر جواب بدهد، چون کش سیستم‌عامل هم به کار می‌آید.

sudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '1GB';"
sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"

دستور ALTER SYSTEM مقدار را در postgresql.auto.conf می‌نویسد. اگر به‌جای ری‌استارت فقط SELECT pg_reload_conf(); بزنید، SHOW هنوز 128MB نشان می‌دهد و ستون pending_restart مقدار t دارد. یعنی سرور منتظر ری‌استارت است. مستندات یادآوری می‌کند shared_buffers بزرگ‌تر معمولاً max_wal_size بزرگ‌تری هم می‌خواهد.

چرا work_mem را نباید مثل رم تقسیم کرد؟

در سالن مطالعه یک کتابخانه، قفسه مشترک کنار میزها shared_buffers است. میز هر نفر work_mem است؛ ده نفر با سه کتاب باز یعنی سی سهم از سالن. طبق مستندات، هر عملیات sort یا hash در هر جلسه تا سقف work_mem حافظه می‌گیرد.

rough ≈ work_mem × ops_per_query × (1 + parallel_workers) × active_connections

💡 برآورد تقریبی مصرف work_mem

مستندات فرمولی نمی‌دهد و فقط می‌گوید مصرف کل «می‌تواند چند برابر» work_mem باشد. عملیات hash تا دو برابر می‌گیرد (hash_mem_multiplier با پیش‌فرض 2.0) و هر worker موازی سقف خودش را دارد. مثال: 8MB × دو عملیات × ۲۰ اتصال یعنی حدود 320MB، پیش از حساب hash و workerها.

مقدار effective_cache_size و maintenance_work_mem

در مستندات تنظیمات برنامه‌ریز کوئری، effective_cache_size فرض برنامه‌ریز درباره کش دیسکی است و عدد بزرگ‌ترش ایندکس را محتمل‌تر می‌کند. درصدی پیشنهاد نشده؛ shared_buffers و سهم PostgreSQL از کش هسته را با هم حساب کنید. پیش‌فرض 4GB روی سرور 2GB از کل رم بیشتر است؛ با ALTER SYSTEM و reload آن را زیر رم سرور بیاورید.

هر worker از autovacuum (پیش‌فرض سه تا) می‌تواند به اندازه maintenance_work_mem بگیرد؛ autovacuum_work_mem این را جدا مهار می‌کند. برای انتخاب رم از اول، راهنمای انتخاب رم برای سرور دیتابیس را ببینید.

چطور دسترسی ریموت را باز کنیم و دیتابیس را جلوی همه نگذاریم؟

پاسخ کوتاه: در pg_hba.conf فقط IP مشخص اپلیکیشن را با روش scram-sha-256 مجاز کنید، خط تازه را با pg_hba_file_rules بسنجید، بعد listen_addresses را از localhost به آدرس سرور تغییر دهید و ری‌استارت کنید.

در نصب PostgreSQL از مخزن اوبونتو، listen_addresses روی localhost است و دیتابیس فقط از خود سرور در دسترس است. کاربر postgres هم با روش peer وارد می‌شود؛ نام کاربر سیستم‌عامل ملاک است و رمز لازم نیست.

یک خط در pg_hba.conf، برای یک IP

در فایل pg_hba.conf اولین خط منطبق به کار می‌رود و اگر احراز هویت در آن شکست بخورد، خط بعدی بررسی نمی‌شود. خط زیر را به انتهای فایل اضافه کنید؛ 198.51.100.7 آدرس نمونه اپلیکیشن است:

sudo nano /etc/postgresql/16/main/pg_hba.conf
# add at the end:
host    shop    appuser    198.51.100.7/32    scram-sha-256

روش trust را برای شبکه به کار نبرید؛ طبق مستندات، هر کسی را بدون رمز راه می‌دهد. مستندات نسخه 18 هم پشتیبانی از رمزهای MD5 را منسوخ اعلام کرده است. اگر کلاینت TLS دارد، hostssl اتصال بدون رمزنگاری را نمی‌پذیرد.

بررسی خط، باز کردن listen_addresses و فایروال

ستون error در pg_hba_file_rules خطای خط تازه را پیش از ری‌استارت نشان می‌دهد؛ در آزمایش ما ماسک اشتباه /33 همین‌جا invalid CIDR mask داد. ستون خالی بود؟ ادامه دهید:

sudo -u postgres psql -c "SELECT line_number, address, auth_method, error FROM pg_hba_file_rules;"
sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses = 'localhost,203.0.113.10';"
sudo systemctl restart postgresql
sudo ufw allow proto tcp from 198.51.100.7 to any port 5432

آدرس 203.0.113.10 نمونه IP سرور است؛ listen_addresses فهرست با کاما می‌پذیرد و ‘*’ لازم نیست. قانون ufw پورت 5432 را فقط برای اپلیکیشن باز می‌کند. برای تغییر بعدی pg_hba.conf، SELECT pg_reload_conf(); کافی است.

بکاپ با pg_dump و پیدا کردن کوئری کند از کجا شروع می‌شود؟

بکاپ با pg_dump و پیدا کردن کوئری کند از کجا شروع می‌شود؟

پاسخ کوتاه: بکاپ با یک دستور pg_dump -Fc برای هر دیتابیس شروع می‌شود. کوئری کند را log_min_duration_statement در لاگ نشان می‌دهد و pg_stat_statements آمار همه کوئری‌ها را جمع می‌کند.

بکاپ و بازگردانی با pg_dump

بکاپ اول را پیش از ورود داده واقعی بگیرید؛ سرور تازه بعد از نصب PostgreSQL بهترین جا برای تمرین بازگردانی است. طبق مستندات pg_dump، این ابزار حین کار دیتابیس خروجی سازگار می‌گیرد و خواننده‌ها و نویسنده‌ها را مسدود نمی‌کند. قالب custom (-Fc) فشرده است:

sudo -u postgres pg_dump -Fc -f /var/lib/postgresql/shop.dump shop
sudo -u postgres pg_restore --list /var/lib/postgresql/shop.dump
sudo -u postgres createdb shop_test
sudo -u postgres pg_restore -d shop_test /var/lib/postgresql/shop.dump

خروجی –list باید سطر «Format: CUSTOM» را نشان دهد. نقش‌ها (roles) با pg_dumpall خروجی گرفته می‌شوند. یک نسخه را با scp یا rsync بیرون از سرور نگه دارید.

لاگ کوئری‌های کند و pg_stat_statements

پارامتر log_min_duration_statement پیش‌فرض -1 دارد، یعنی خاموش. با مقدار 250ms هر دستوری که ۲۵۰ میلی‌ثانیه یا بیشتر طول بکشد در لاگ ثبت می‌شود و reload کافی است:

sudo -u postgres psql -c "ALTER SYSTEM SET log_min_duration_statement = '250ms';"
sudo -u postgres psql -c "SELECT pg_reload_conf();"
sudo tail -f /var/log/postgresql/postgresql-16-main.log

روی سرور آزمایشی خود ما، SELECT pg_sleep(0.3); سطر duration: 300.836 ms را ثبت کرد. برای کوئری‌های سریع اما پرتکرار، افزونه pg_stat_statements تصویر کامل‌تری می‌دهد.

🔸 خطایی که بدون shared_preload_libraries می‌بینید

این افزونه باید در shared_preload_libraries بیاید و ری‌استارت می‌خواهد. بدون این مرحله، اولین SELECT با خطای pg_stat_statements must be loaded via shared_preload_libraries متوقف می‌شود. اول مقدار فعلی را با SHOW ببینید و اگر خالی نبود، نام افزونه را به همان فهرست اضافه کنید.

sudo -u postgres psql -c "SHOW shared_preload_libraries;"
sudo -u postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"
sudo systemctl restart postgresql
sudo -u postgres psql -d shop -c "CREATE EXTENSION pg_stat_statements;"
sudo -u postgres psql -d shop -c "SELECT query, calls, total_exec_time
  FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

جواب این پرسش‌ها در مستندات یک خط است، ولی پیدا کردنش وقت می‌برد:

اوبونتو 24.04 کدام نسخه PostgreSQL را نصب می‌کند؟

نسخه 16؛ هنگام نوشتن این متن، 16.15.

ورود محلی با روش peer است و رمز نمی‌خواهد؛ برای شبکه، کاربر جدا با createuser –pwprompt بسازید.

این پارامتر فقط هنگام شروع سرور خوانده می‌شود و ری‌استارت لازم است.

اگر فقط دیتابیس اجرا می‌شود، حدود 512MB (۲۵ درصد) نقطه شروع مستندات است؛ با اپلیکیشن کنارش کمتر بگذارید.

خیر؛ طبق مستندات حافظه‌ای تخصیص یا رزرو نمی‌کند و فقط در تخمین هزینه به کار می‌رود.

خیر، خواننده‌ها و نویسنده‌ها مسدود نمی‌شوند؛ فقط روی دیسک بار می‌گذارد.

دستور pg_dump یک دیتابیس را خروجی می‌گیرد؛ نقش‌ها و کل خوشه با pg_dumpall.

جمع‌بندی؛ بعد از نصب PostgreSQL چه عددهایی را عوض کنیم: اول مقدارهای فعلی و ستون context را در pg_settings بخوانید تا بدانید کدام تغییر ری‌استارت می‌خواهد. برای shared_buffers روی سرور اختصاصی دیتابیس، ۲۵ درصد رم نقطه شروع خود مستندات است. برای work_mem فرمول رسمی وجود ندارد؛ با ضرب در عملیات و اتصال‌های فعال یک برآورد تقریبی بسازید و hash و workerهای موازی را هم در نظر بگیرید. پیش از باز کردن دسترسی ریموت، خط IP مشخص را در pg_hba.conf بنویسید و بسنجید، و از روز اول با pg_dump -Fc بکاپ بگیرید. لاگ کوئری کند و pg_stat_statements جای رفتن وقت سرور را نشان می‌دهند.

🚀 فایل‌های تنظیمات را در ویرایشگر خودتان باز کنید

پوشه /etc/postgresql را مستقیم در VS Code روی سرور باز کنید.

سرور دیتابیس شما چند گیگابایت رم دارد و shared_buffers را روی چه عددی گذاشته‌اید؟ اگر هنوز 128MB است، خجالت ندارد؛ همین دو عدد را بنویسید تا با هم حسابش کنیم.

وب داده
وب داده

جدید ترین مطالب آموزشی و کاربردی را در اینجا بخوانید !
ما با بهره‌گیری از دانش روز و تجربه متخصصان حوزه فناوری، مجموعه‌ای از آموزش‌های کاربردی و مقالات تخصصی را گردآوری کرده‌ایم که هر یک، پاسخی دقیق به پرسش‌های شماست. ؛ از مفاهیم پایه تا پیچیده‌ترین تکنیک‌های حرفه‌ای. اینجا، دانش با زبانی ساده اما عمیق در اختیار شما قرار می‌گیرد.

مقاله‌ها: 316
پاسخی بگذارید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *