
به اشتراک بگذارید

به اشتراک بگذارید
قدم اول را برداشتهاید: نصب PostgreSQL روی اوبونتو با دو دستور apt تمام شده و psql جواب میدهد. اما عددهای حافظه هنوز پیشفرضاند. نسخه 16 روی اوبونتو 24.04 با shared_buffers برابر 128MB و work_mem برابر 4MB شروع میکند، چه سرورتان 2GB رم داشته باشد چه 16GB.
pg_dump -Fc و یک بار تمرین بازگردانی.💡 سه حافظه با اسمهای شبیه و رفتار متفاوت
پارامتر shared_buffers حافظه مشترک همه اتصالهاست و فقط با ریاستارت عوض میشود.
پارامتر work_mem برای هر عملیات sort یا hash جدا حساب میشود.
پارامتر effective_cache_size هیچ حافظهای نمیگیرد.
هدف، دو عدد و یک فهرست است: shared_buffers و work_mem سرور خودتان و IPهایی که حق اتصال دارند، هر کدام با دلیل. پیشفرضها و خروجیهای این متن همه از یک اوبونتو 24.04 تازه است.
📌 چهار پرسش بعد از نصب
◀️ اولین بررسیها روی اوبونتو
◀️ سهم shared_buffers و work_mem از رم چقدر باشد؟
◀️ چطور دسترسی ریموت را باز کنیم و دیتابیس را جلوی همه نگذاریم؟
◀️ بکاپ با pg_dump و پیدا کردن کوئری کند از کجا شروع میشود؟

پاسخ کوتاه: نسخهای که واقعاً نصب شده و مقدار فعلی پارامترهای حافظه و اتصال؛ بدون این دو، هر تغییری حدس است.
در اوبونتو 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 برسد میتواند برای ورود تلاش کند.

پاسخ کوتاه: مستندات مصرف منابع PostgreSQL برای سرور اختصاصی دیتابیس با 1GB رم یا بیشتر، ۲۵ درصد رم را «نقطه شروع معقول» shared_buffers میداند. برای work_mem درصدی نمیدهد و هشدار میدهد که مصرف کل میتواند چند برابر همین عدد شود.
| پارامتر | پیشفرض در PostgreSQL 16 اوبونتو | نوع حافظه | اعمال تغییر |
|---|---|---|---|
| shared_buffers | 128MB | مشترک بین همه اتصالها | با ریاستارت |
| work_mem | 4MB | برای هر عملیات sort یا hash | با reload یا SET |
| maintenance_work_mem | 64MB | برای VACUUM و CREATE INDEX | با reload یا SET |
| effective_cache_size | 4GB | بدون تخصیص؛ فقط تخمین | با reload یا SET |
پیشفرض نصب 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 بزرگتری هم میخواهد.
در سالن مطالعه یک کتابخانه، قفسه مشترک کنار میزها 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 فرض برنامهریز درباره کش دیسکی است و عدد بزرگترش ایندکس را محتملتر میکند. درصدی پیشنهاد نشده؛ 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 اولین خط منطبق به کار میرود و اگر احراز هویت در آن شکست بخورد، خط بعدی بررسی نمیشود. خط زیر را به انتهای فایل اضافه کنید؛ 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 اتصال بدون رمزنگاری را نمیپذیرد.
ستون 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 -Fc برای هر دیتابیس شروع میشود. کوئری کند را log_min_duration_statement در لاگ نشان میدهد و pg_stat_statements آمار همه کوئریها را جمع میکند.
بکاپ اول را پیش از ورود داده واقعی بگیرید؛ سرور تازه بعد از نصب 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 بیرون از سرور نگه دارید.
پارامتر 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;"
جواب این پرسشها در مستندات یک خط است، ولی پیدا کردنش وقت میبرد:
نسخه 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 است، خجالت ندارد؛ همین دو عدد را بنویسید تا با هم حسابش کنیم.