دانلود کتاب آموزشی پیشرفته نصب و پیکربندی پایگاه داده PostgreSQL جلد دوم

۱. ابزار pg_stat_activity

ابزار pg_stat_activity یکی از نمایه‌های (View) داخلی PostgreSQL است که اطلاعات کاملی درباره تمامی فرآیندهای جاری در پایگاه داده فراهم می‌کند. این ابزار برای شناسایی مشکلاتی مانند پرس‌وجوهای طولانی‌مدت، فرآیندهای معلق، و قفل‌ها (Locks) بسیار مفید است.

دسترسی به اطلاعات pg_stat_activity

می‌توانید اطلاعات این نمایه را با یک کوئری ساده مشاهده کنید:

SELECT * FROM pg_stat_activity;

ستون‌های مهم در pg_stat_activity:

• datid و datname: شناسه و نام پایگاه داده‌ای که فرآیند به آن متصل است.
• pid: شناسه فرآیند.
• usename: نام کاربری که اتصال را ایجاد کرده است.
• application_name: نام برنامه‌ای که اتصال را ایجاد کرده است.
• client_addr: آدرس IP کلاینت متصل.
• state: وضعیت فعلی فرآیند (مانند active, idle, idle in transaction).
• query: متن پرس‌وجوی در حال اجرا.
• backend_start: زمان شروع فرآیند.

کاربردها:

• شناسایی پرس‌وجوهای طولانی‌مدت:

  SELECT pid, usename, query, state, now() - query_start AS duration
  FROM pg_stat_activity
  WHERE state = 'active'
    AND now() - query_start > interval '1 minute';

• شناسایی قفل‌های پایگاه داده:

  SELECT pid, usename, query, state
  FROM pg_stat_activity
  WHERE wait_event_type = 'Lock';

۲. ابزار pg_stat_statements

ابزار pg_stat_statements افزونه‌ای قدرتمند برای PostgreSQL است که اطلاعاتی درباره عملکرد پرس‌وجوها فراهم می‌کند. این ابزار عملکرد پرس‌وجوهای اجرا شده را جمع‌آوری و تحلیل می‌کند و امکان شناسایی پرس‌وجوهای پرهزینه را فراهم می‌سازد.

فعال‌سازی pg_stat_statements:

برای استفاده از این ابزار، ابتدا باید آن را فعال کنید:

1. افزونه را نصب کنید:

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

2. پارامترهای زیر را در فایل پیکربندی postgresql.conf تنظیم کنید:

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all

3. پایگاه داده را مجدداً راه‌اندازی کنید.

دسترسی به اطلاعات pg_stat_statements:

SELECT * FROM pg_stat_statements;

ستون‌های مهم در pg_stat_statements:

• queryid: شناسه یکتای هر پرس‌وجو.
• query: متن پرس‌وجو.
• calls: تعداد دفعات اجرای پرس‌وجو.
• total_time: مجموع زمان صرف شده برای اجرای پرس‌وجو (بر حسب میلی‌ثانیه).
• rows: تعداد ردیف‌های بازگشتی.
• shared_blks_hit و shared_blks_read: اطلاعات مرتبط با استفاده از بلوک‌های کش.

کاربردها:

• شناسایی پرس‌وجوهای کند:

  SELECT query, calls, total_time, mean_time
  FROM pg_stat_statements
  ORDER BY total_time DESC
  LIMIT 5;

• بررسی پرس‌وجوهای پرهزینه از نظر منابع:

  SELECT query, shared_blks_hit, shared_blks_read, calls
  FROM pg_stat_statements
  ORDER BY shared_blks_read DESC
  LIMIT 5;

جمع‌بندی

ابزارهای داخلی PostgreSQL مانند pg_stat_activity و pg_stat_statements ابزارهای ارزشمندی برای نظارت و تحلیل عملکرد پایگاه داده هستند. با استفاده از این ابزارها می‌توانید:

• فرآیندهای جاری را مدیریت کنید.
• پرس‌وجوهای پرهزینه و گلوگاه‌ها را شناسایی کنید.
• قفل‌ها و مشکلات عملکردی را تحلیل و رفع کنید.

این ابزارها در کنار یکدیگر، دید جامعی از عملکرد پایگاه داده ارائه می‌دهند و بهینه‌سازی سیستم را تسهیل می‌کنند.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”استفاده از ابزارهای خارجی مانند pg_top و pgBadger برای تحلیل دقیق” subtitle=”توضیحات کامل”]برای نظارت پیشرفته و تحلیل عمیق عملکرد پایگاه داده PostgreSQL، ابزارهای خارجی قدرتمندی مانند pg_top و pgBadger بسیار مفید هستند. این ابزارها اطلاعات جامع و دقیقی درباره فرآیندها، مصرف منابع، و الگوهای عملکردی پایگاه داده ارائه می‌دهند و به مدیران کمک می‌کنند تا مشکلات را شناسایی و بهینه‌سازی‌های لازم را اعمال کنند.

۱. ابزار pg_top

pg_top یک ابزار خط فرمان است که عملکرد PostgreSQL را در زمان واقعی (Real-Time) نمایش می‌دهد. این ابزار شباهت زیادی به ابزار معروف top در سیستم‌عامل‌های لینوکسی دارد اما برای PostgreSQL طراحی شده است.

ویژگی‌های اصلی pg_top:

• نمایش فرآیندهای فعال PostgreSQL همراه با مصرف CPU و حافظه.
• نظارت بر پرس‌وجوهای در حال اجرا.
• نمایش اطلاعات مرتبط با قفل‌ها (Locks).
• نمایش صف‌های انتظار و اطلاعات مربوط به I/O.

نصب و راه‌اندازی:

برای نصب pg_top، بسته به سیستم‌عامل خود از دستور زیر استفاده کنید:

در سیستم‌های Debian/Ubuntu:

sudo apt install pgtop

در سیستم‌های RHEL/CentOS/AlmaLinux:

sudo yum install pg_top

نحوه استفاده:

برای اجرا، کافی است دستور زیر را اجرا کنید:

pg_top -h <hostname> -U <username> -d <database>

اطلاعات کلیدی نمایش داده شده:

• Process ID (PID): شناسه فرآیند در حال اجرا.
• Query: متن پرس‌وجوی در حال اجرا.
• CPU Usage: میزان مصرف CPU توسط هر فرآیند.
• Memory Usage: میزان مصرف حافظه.
• Locks: اطلاعات مربوط به قفل‌ها.

کاربردها:

• شناسایی پرس‌وجوهای پرمصرف:

  pg_top

  در پنجره نمایش داده شده، پرس‌وجوهایی که بیشترین مصرف CPU و حافظه دارند قابل مشاهده هستند.

• مشاهده قفل‌های فعال: در محیط pg_top کلید l را فشار دهید تا اطلاعات قفل‌ها نمایش داده شود.

۲. ابزار pgBadger

pgBadger یک ابزار تحلیل لاگ (Log Analyzer) قدرتمند برای PostgreSQL است که گزارش‌های گرافیکی و مفصلی درباره عملکرد پایگاه داده ارائه می‌دهد. این ابزار لاگ‌های PostgreSQL را تجزیه و تحلیل کرده و اطلاعات ارزشمندی درباره پرس‌وجوهای کند، مصرف منابع و الگوهای دسترسی کاربران ارائه می‌دهد.

ویژگی‌های اصلی pgBadger:

• تولید گزارش‌های گرافیکی HTML.
• تحلیل پرس‌وجوهای کند و پرهزینه.
• نمایش روندهای مصرف CPU، I/O، و حافظه.
• شناسایی قفل‌ها و Deadlockها.
• نمایش الگوهای بارگذاری پایگاه داده در طول زمان.

نصب و راه‌اندازی:

برای نصب pgBadger، از دستورات زیر استفاده کنید:

در سیستم‌های Debian/Ubuntu:

sudo apt install pgbadger

در سیستم‌های RHEL/CentOS/AlmaLinux:

sudo yum install pgbadger

پیکربندی لاگ PostgreSQL:

قبل از استفاده از pgBadger، اطمینان حاصل کنید که تنظیمات لاگ در فایل postgresql.conf به درستی پیکربندی شده‌اند:

logging_collector = on
log_min_duration_statement = 0
log_statement = 'all'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

سپس سرویس PostgreSQL را ری‌استارت کنید:

sudo systemctl restart postgresql

تولید گزارش:

برای تولید گزارش، دستور زیر را اجرا کنید:

pgbadger -o report.html /path/to/postgresql.log

کاربردها:

• شناسایی پرس‌وجوهای کند: در گزارش HTML، بخش مربوط به Slowest Queries را مشاهده کنید.
• تحلیل روند بارگذاری پایگاه داده: در نمودارهای Load by Hour یا Load by User می‌توانید الگوهای بارگذاری را بررسی کنید.
• شناسایی قفل‌ها و Deadlockها: در گزارش، بخش Locks and Deadlocks به وضوح این اطلاعات را نمایش می‌دهد.

جمع‌بندی

ابزارهای pg_top و pgBadger امکانات گسترده‌ای برای نظارت و تحلیل دقیق عملکرد PostgreSQL ارائه می‌دهند:

• pg_top مناسب برای نظارت بلادرنگ بر فرآیندها و پرس‌وجوها.
• pgBadger ابزاری قدرتمند برای تحلیل لاگ‌ها و تولید گزارش‌های جامع.

این ابزارها در کنار یکدیگر دید جامعی از وضعیت پایگاه داده فراهم می‌کنند و به مدیران کمک می‌کنند تا مشکلات عملکردی را شناسایی و بهینه‌سازی‌های لازم را انجام دهند.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”مشاهده و تحلیل صف انتظار (Locks) و Deadlocks” subtitle=”توضیحات کامل”]یکی از موارد مهم در مدیریت پایگاه داده PostgreSQL، شناسایی قفل‌ها (Locks) و مدیریت بن‌بست‌ها (Deadlocks) است. قفل‌ها برای حفظ یکپارچگی داده‌ها ضروری هستند اما می‌توانند باعث کاهش کارایی سیستم شوند. در این بخش، به نحوه مشاهده و تحلیل صف انتظار و Deadlockها می‌پردازیم و ابزارها و روش‌های مدیریت این مسائل را معرفی می‌کنیم.

۱. آشنایی با انواع قفل‌ها در PostgreSQL

PostgreSQL برای مدیریت تراکنش‌ها و اطمینان از همزمانی ایمن داده‌ها، از مکانیسم قفل‌گذاری استفاده می‌کند. انواع رایج قفل‌ها شامل موارد زیر است:

• Access Share Lock: برای خواندن داده‌ها.
• Row Exclusive Lock: برای درج یا حذف داده‌ها.
• Exclusive Lock: برای عملیات خاصی مانند تغییر ساختار جداول.
• Deadlock: هنگامی که دو یا چند تراکنش منتظر قفل یکدیگر باشند، بن‌بست رخ می‌دهد.

۲. مشاهده قفل‌ها با ابزارهای داخلی PostgreSQL

مشاهده قفل‌های فعال:

برای مشاهده قفل‌های فعال در پایگاه داده، می‌توانید از نمای سیستم pg_locks استفاده کنید:

SELECT 
    pid,
    locktype,
    relation::regclass AS table_name,
    mode,
    granted,
    transactionid AS xid,
    virtualtransaction AS vtx,
    virtualxid AS vxid
FROM pg_locks;

ستون‌های کلیدی:

• locktype: نوع قفل (مانند relation یا transaction).
• table_name: نام جدولی که قفل روی آن اعمال شده.
• mode: نوع قفل (مانند AccessShareLock یا ExclusiveLock).
• granted: نشان می‌دهد آیا قفل تخصیص داده شده است یا خیر.

مشاهده اطلاعات تراکنش‌ها:

برای شناسایی تراکنش‌های مربوط به قفل‌ها، از نمای pg_stat_activity استفاده کنید:

SELECT 
    pid,
    usename AS username,
    application_name,
    client_addr,
    state,
    query,
    waiting
FROM pg_stat_activity;

ستون‌های مهم:

• pid: شناسه فرآیند تراکنش.
• state: وضعیت تراکنش (مانند active یا idle).
• waiting: نشان می‌دهد آیا فرآیند منتظر قفل است یا خیر.

۳. تحلیل Deadlockها

شناسایی Deadlockها:

Deadlockها در لاگ‌های PostgreSQL ثبت می‌شوند. برای فعال‌سازی ثبت Deadlock، تنظیمات زیر را در فایل postgresql.conf اعمال کنید:

log_lock_waits = on
deadlock_timeout = '1s'

مشاهده گزارش Deadlock در لاگ‌ها:

پس از فعال‌سازی، Deadlockها در لاگ پایگاه داده ثبت می‌شوند. می‌توانید با استفاده از ابزارهایی مانند pgBadger این لاگ‌ها را تحلیل کنید.

۴. رفع مشکلات قفل‌ها و Deadlockها

شناسایی و خاتمه دادن به فرآیندهای مشکل‌دار:

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

1. شناسایی فرآیند مشکل‌دار:

   SELECT pid, query FROM pg_stat_activity WHERE waiting = true;

2. خاتمه دادن به فرآیند:

   SELECT pg_terminate_backend(<pid>);

بهینه‌سازی طراحی تراکنش‌ها:

• کاهش طول تراکنش‌ها: تراکنش‌های طولانی احتمال ایجاد Deadlock را افزایش می‌دهند.
• ترتیب یکسان قفل‌گذاری: همه تراکنش‌ها باید قفل‌ها را به ترتیب یکسانی اعمال کنند تا از بن‌بست جلوگیری شود.

۵. ابزارهای خارجی برای تحلیل قفل‌ها

استفاده از pg_top:

ابزار pg_top می‌تواند فرآیندهای قفل‌گذاری و انتظار را نمایش دهد:

1. اجرای pg_top:

   pg_top

2. فشار دادن کلید l برای مشاهده قفل‌ها.

استفاده از pgBadger:

pgBadger می‌تواند گزارش‌های دقیق از قفل‌ها و Deadlockها ارائه دهد. لاگ‌های تولید شده توسط PostgreSQL را تحلیل کرده و اطلاعات ارزشمندی درباره گلوگاه‌ها ارائه می‌دهد.

۶. جمع‌بندی

مدیریت قفل‌ها و Deadlockها در PostgreSQL برای حفظ کارایی و جلوگیری از توقف عملیات ضروری است. با استفاده از ابزارهای داخلی مانند pg_locks و pg_stat_activity و همچنین ابزارهای خارجی مانند pg_top و pgBadger، می‌توانید قفل‌ها را شناسایی و مدیریت کنید. در کنار این، بهینه‌سازی طراحی تراکنش‌ها و تنظیم پارامترهای مناسب، به کاهش مشکلات مرتبط با قفل‌ها کمک خواهد کرد.[/cdb_course_lesson][cdb_course_lesson title=”فصل 2: تحلیل و بهینه‌سازی پرس‌وجوها”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”استفاده از ابزار EXPLAIN و EXPLAIN ANALYZE برای تحلیل طرح اجرایی پرس‌وجوها” subtitle=”توضیحات کامل”]PostgreSQL ابزارهای قدرتمندی برای بررسی و بهینه‌سازی عملکرد پرس‌وجوها ارائه می‌دهد. EXPLAIN و EXPLAIN ANALYZE از جمله مهم‌ترین ابزارها برای تحلیل طرح اجرایی (Query Plan) هستند. این ابزارها اطلاعات ارزشمندی درباره نحوه اجرای پرس‌وجو، تخمین هزینه‌ها، تعداد ردیف‌ها، و زمان‌بندی اجرای عملیات ارائه می‌کنند. در این بخش، نحوه استفاده از این ابزارها و تفسیر خروجی آن‌ها را بررسی خواهیم کرد.

۱. ابزار EXPLAIN

معرفی:

• دستور EXPLAIN اطلاعاتی درباره طرح اجرایی تخمینی پرس‌وجو ارائه می‌دهد.
• این ابزار پرس‌وجو را اجرا نمی‌کند؛ بلکه طرح اجرایی را نمایش می‌دهد.

استفاده پایه:

EXPLAIN SELECT * FROM users WHERE age > 30;

خروجی:

• Seq Scan (Sequential Scan): نشان‌دهنده اسکن ترتیبی روی جدول.
• Filter: فیلتر اعمال‌شده روی داده‌ها.
• Rows: تعداد ردیف‌های تخمینی برای پردازش.

۲. ابزار EXPLAIN ANALYZE

معرفی:

• EXPLAIN ANALYZE علاوه بر نمایش طرح اجرایی، پرس‌وجو را اجرا کرده و اطلاعات واقعی اجرای آن را نمایش می‌دهد.
• این ابزار برای مقایسه داده‌های واقعی و تخمینی مفید است.

استفاده پایه:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

خروجی:

• Actual Time: زمان واقعی صرف‌شده برای هر عملیات.
• Rows: تعداد واقعی ردیف‌های پردازش‌شده.
• Loops: تعداد دفعات اجرای هر بخش از پرس‌وجو.

۳. تفسیر خروجی‌های EXPLAIN و EXPLAIN ANALYZE

مثال خروجی:

Seq Scan on users  (cost=0.00..100.00 rows=50 width=64) (actual time=0.015..0.200 rows=40 loops=1)
  Filter: (age > 30)

توضیحات:

1. Seq Scan on users: اجرای اسکن ترتیبی روی جدول users.
2. Cost: محدوده هزینه تخمینی اجرای این بخش از پرس‌وجو:
   • مقدار اول: هزینه شروع.
   • مقدار دوم: هزینه کامل شدن.
3. Rows: تعداد ردیف‌های تخمینی که این بخش پردازش می‌کند.
4. Actual Time: زمان واقعی اجرای این بخش (شروع و پایان).
5. Loops: تعداد دفعاتی که این عملیات تکرار شده است.

۴. شناسایی گلوگاه‌ها با EXPLAIN ANALYZE

۱. استفاده از ایندکس‌ها:

اگر پرس‌وجو از Seq Scan استفاده کند، ممکن است ایندکس‌ها به بهبود کارایی کمک کنند:

CREATE INDEX idx_age ON users (age);

سپس:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

خروجی ممکن است نشان دهد که به جای Seq Scan از Index Scan استفاده شده است.

۲. Joinها:

پرس‌وجوهای با Join پیچیده ممکن است هزینه بالایی داشته باشند:

EXPLAIN ANALYZE 
SELECT * 
FROM orders o 
JOIN customers c ON o.customer_id = c.id;

برای بهبود:

• استفاده از Indices روی ستون‌های Join.
• بررسی Nested Loop Join و Hash Join برای انتخاب کاراترین روش.

۵. بهینه‌سازی پرس‌وجوها با استفاده از اطلاعات EXPLAIN ANALYZE

۱. شناسایی عملیات پرهزینه:

• ستون Cost و Actual Time را بررسی کنید.
• بخش‌هایی که زمان بیشتری می‌برند را بهینه کنید.

۲. کاهش تعداد ردیف‌های پردازش:

• از فیلترهای موثر در WHERE استفاده کنید.
• داده‌های غیرضروری را حذف کنید.

۳. بررسی ایندکس‌ها:

• Index Scan به جای Seq Scan در صورت امکان استفاده شود.
• ایندکس‌های Composite برای فیلترهای چندگانه ایجاد کنید.

۴. تنظیم پارامترهای پرس‌وجو:

• پارامترهایی مانند work_mem و parallel_workers را برای افزایش سرعت پردازش تنظیم کنید.

۶. نمونه عملی برای تحلیل پرس‌وجو

پرس‌وجو:

EXPLAIN ANALYZE
SELECT * 
FROM orders 
WHERE order_date > '2025-01-01' AND amount > 1000;

بهینه‌سازی:

1. ایندکس ترکیبی:

   CREATE INDEX idx_order_date_amount ON orders (order_date, amount);

2. استفاده مجدد از EXPLAIN ANALYZE برای بررسی تاثیر ایندکس:

   EXPLAIN ANALYZE
   SELECT * 
   FROM orders 
   WHERE order_date > '2025-01-01' AND amount > 1000;

جمع‌بندی

ابزارهای EXPLAIN و EXPLAIN ANALYZE برای تحلیل و بهینه‌سازی عملکرد پرس‌وجوها در PostgreSQL بسیار ارزشمند هستند. این ابزارها با نمایش طرح اجرایی پرس‌وجو و داده‌های واقعی اجرای آن، امکان شناسایی گلوگاه‌ها و بهبود کارایی پایگاه داده را فراهم می‌کنند. برای استفاده موثر از این ابزارها:

• به تفاوت بین داده‌های تخمینی و واقعی توجه کنید.
• عملیات پرهزینه را شناسایی و بهینه کنید.
• ایندکس‌ها و تنظیمات سیستم را برای بهبود عملکرد اعمال کنید.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”شناسایی پرس‌وجوهای کند و بهینه‌سازی آنها” subtitle=”توضیحات کامل”]یکی از جنبه‌های کلیدی مدیریت پایگاه داده در PostgreSQL، شناسایی پرس‌وجوهای کند و بهبود عملکرد آن‌هاست. این فرآیند شامل شناسایی گلوگاه‌ها، تحلیل عملکرد و اعمال تغییرات برای بهینه‌سازی است. در ادامه به روش‌های مختلف شناسایی و بهینه‌سازی این پرس‌وجوها پرداخته‌ایم.

۱. شناسایی پرس‌وجوهای کند

۱.۱ استفاده از pg_stat_statements

افزونه pg_stat_statements اطلاعات جامعی درباره عملکرد پرس‌وجوها ارائه می‌دهد.

فعال‌سازی:

1. افزونه را فعال کنید:

   CREATE EXTENSION pg_stat_statements;

2. فایل تنظیمات PostgreSQL را ویرایش کنید:

   shared_preload_libraries = 'pg_stat_statements'
   track_activity_query_size = 2048

3. PostgreSQL را ری‌استارت کنید:

   systemctl restart postgresql

نمایش اطلاعات:

SELECT query, calls, total_time, mean_time, rows 
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

• query: متن پرس‌وجو.
• calls: تعداد دفعات اجرای پرس‌وجو.
• total_time: مجموع زمان اجرای پرس‌وجو.
• mean_time: میانگین زمان اجرای پرس‌وجو.
• rows: تعداد ردیف‌های بازگشتی.

۱.۲ استفاده از pg_stat_activity

جدول سیستمی pg_stat_activity اطلاعاتی درباره پرس‌وجوهای جاری ارائه می‌دهد.

SELECT pid, query, state, now() - query_start AS duration 
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY duration DESC;

• pid: شناسه فرآیند.
• query: متن پرس‌وجو.
• state: وضعیت پرس‌وجو (active، idle و …).
• duration: مدت‌زمان اجرای پرس‌وجو.

۱.۳ استفاده از ابزارهای خارجی مانند pgBadger

برای تحلیل دقیق‌تر، ابزارهایی مانند pgBadger می‌توانند لاگ‌های سرور را تحلیل کرده و گزارش‌های جامع ارائه دهند.

۲. تحلیل و بهینه‌سازی پرس‌وجوهای کند

۲.۱ استفاده از EXPLAIN و EXPLAIN ANALYZE

EXPLAIN ANALYZE طرح اجرایی و زمان اجرای واقعی پرس‌وجو را ارائه می‌دهد.

EXPLAIN ANALYZE
SELECT * 
FROM orders 
WHERE order_date > '2025-01-01' AND amount > 1000;

۲.۲ بهبود عملکرد با استفاده از Indices

ایجاد ایندکس مناسب می‌تواند اجرای پرس‌وجو را بهینه کند:

• ایندکس ساده:

  CREATE INDEX idx_order_date ON orders (order_date);

• ایندکس ترکیبی:

  CREATE INDEX idx_order_date_amount ON orders (order_date, amount);

۲.۳ کاهش تعداد ردیف‌های پردازش‌شده

• از محدودیت‌ها و فیلترهای مناسب در WHERE استفاده کنید.
• ردیف‌های غیرضروری را با LIMIT و OFFSET حذف کنید.

۲.۴ بهبود Joinها

• از ایندکس روی ستون‌های Join استفاده کنید.
• بررسی نوع Join (Nested Loop، Hash Join و …) و بهینه‌سازی آن:

  SET enable_nestloop = OFF;

۲.۵ تقسیم پرس‌وجوهای پیچیده به بخش‌های کوچک‌تر

پرس‌وجوهای پیچیده را به چند پرس‌وجوی کوچک‌تر تقسیم کنید تا بار محاسباتی کاهش یابد.

۳. رفع گلوگاه‌های رایج

۳.۱ گلوگاه در Sequential Scan

• دلیل: عدم وجود ایندکس مناسب.
• راه‌حل: ایجاد ایندکس یا تغییر فیلترها.

۳.۲ گلوگاه در Join

• دلیل: استفاده نادرست از Join یا داده‌های بزرگ.
• راه‌حل: استفاده از ایندکس یا بازنویسی پرس‌وجو.

۳.۳ گلوگاه در Sort

• دلیل: داده‌های حجیم بدون ایندکس مرتب‌سازی.
• راه‌حل: ایجاد ایندکس مرتب‌شده:

  CREATE INDEX idx_order_date_desc ON orders (order_date DESC);

۳.۴ گلوگاه در Disk I/O

• دلیل: حجم زیاد داده‌ها و عدم استفاده از کش.
• راه‌حل: افزایش work_mem و effective_cache_size.

۴. بهینه‌سازی با تقسیم بار کاری

۴.۱ استفاده از Parallel Query Execution

فعال‌سازی اجرای موازی برای توزیع بار کاری:

SET max_parallel_workers_per_gather = 4;

۴.۲ استفاده از Partitioning

تقسیم جداول بزرگ به پارتیشن‌های کوچک‌تر:

CREATE TABLE orders_2025 PARTITION OF orders FOR VALUES FROM ('2025-01-01') TO ('2025-12-31');

جمع‌بندی

شناسایی و بهینه‌سازی پرس‌وجوهای کند در PostgreSQL نیازمند استفاده از ابزارهای داخلی و خارجی مانند pg_stat_statements، pg_stat_activity و pgBadger است. تحلیل طرح اجرایی با EXPLAIN ANALYZE و ایجاد ایندکس‌های مناسب می‌تواند تأثیر قابل‌توجهی در بهبود عملکرد داشته باشد. در نهایت، تنظیم پارامترهای سیستم و تقسیم بار کاری می‌تواند از ایجاد گلوگاه‌ها جلوگیری کرده و کارایی پایگاه داده را بهبود بخشد.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”بهبود عملکرد پرس‌وجوها با استفاده از Indices” subtitle=”توضیحات کامل”]

VACUUM ANALYZE;

SELECT relname AS table_name, indexrelname AS index_name, idx_scan AS index_scans 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

EXPLAIN SELECT * FROM orders WHERE order_date > '2025-01-01';

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2025-01-01';

DROP INDEX idx_unused_index;

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تحلیل Query Plan و شناسایی گلوگاه‌ها” subtitle=”توضیحات کامل”]تحلیل Query Plan در PostgreSQL یکی از ابزارهای حیاتی برای شناسایی گلوگاه‌های عملکردی (Performance Bottlenecks) و بهینه‌سازی پرس‌وجوها است. با استفاده از دستورات EXPLAIN و EXPLAIN ANALYZE می‌توان طرح اجرایی پرس‌وجوها را مشاهده و فرآیند اجرای آن‌ها را بررسی کرد.

۱. مفهوم Query Plan

Query Plan نمایش گام‌به‌گام نحوه اجرای یک پرس‌وجو توسط PostgreSQL است. این طرح شامل اطلاعاتی درباره:

• روش‌های دسترسی به داده‌ها (مانند Sequential Scan یا Index Scan)
• تخمین زمان و هزینه‌های اجرای مراحل مختلف
• نحوه ترکیب جداول (Join)
• تعداد ردیف‌های پردازش‌شده

با تحلیل این اطلاعات می‌توان گلوگاه‌های عملکردی را شناسایی و پرس‌وجوها را بهینه کرد.

۲. استفاده از دستور EXPLAIN

دستور EXPLAIN طرح اجرایی یک پرس‌وجو را بدون اجرای آن نشان می‌دهد.

• دستور پایه:

  EXPLAIN SELECT * FROM orders WHERE order_date > '2025-01-01';

۲.۱ خروجی EXPLAIN

خروجی شامل:

• Operation: نوع عملیات (مانند Seq Scan، Index Scan، یا Hash Join).
• Cost: تخمین هزینه اجرای مرحله‌ای و کل پرس‌وجو (cost=شروع..پایان).
• Rows: تعداد تخمینی ردیف‌هایی که پردازش می‌شوند.
• Width: اندازه هر ردیف (بر حسب بایت).

۳. استفاده از EXPLAIN ANALYZE

دستور EXPLAIN ANALYZE علاوه بر طرح اجرایی، پرس‌وجو را اجرا کرده و اطلاعات واقعی (Real-Time) شامل زمان اجرا و تعداد ردیف‌های واقعی پردازش‌شده را ارائه می‌دهد.

• دستور نمونه:

  EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date > '2025-01-01';

۳.۱ خروجی EXPLAIN ANALYZE

• Actual Time: زمان واقعی اجرا برای هر مرحله.
• Rows: تعداد واقعی ردیف‌های پردازش‌شده.
• Loops: تعداد دفعات تکرار هر مرحله.
• Buffers: استفاده از حافظه و I/O.

۴. شناسایی گلوگاه‌ها در Query Plan

۴.۱ اسکن‌های ناکارآمد

• Sequential Scan (Seq Scan): زمانی که PostgreSQL مجبور به اسکن کل جدول شود.
  • راه‌حل: ایجاد ایندکس مناسب برای ستون‌های مورد جستجو.
  • مثال: اگر پرس‌وجو از فیلتر زیر استفاده می‌کند:

    SELECT * FROM orders WHERE order_date > '2025-01-01';

    یک ایندکس برای order_date ایجاد کنید:

    CREATE INDEX idx_order_date ON orders (order_date);

۴.۲ استفاده ناکارآمد از ایندکس‌ها

• Index Scan: اگر ایندکس استفاده شود اما مقدار زیادی داده بازگردانده شود، ممکن است کند باشد.
  • راه‌حل: بهبود فیلترها یا استفاده از Partial Index.
  • مثال:

    CREATE INDEX idx_orders_partial ON orders (order_date) WHERE amount > 1000;

۴.۳ ترکیب جداول (Joins)

• Nested Loop Join: مناسب برای جداول کوچک، اما برای جداول بزرگ ممکن است کند باشد.
  • راه‌حل: استفاده از Hash Join یا Merge Join با توجه به شرایط داده‌ها.
  • مثال: اطمینان از ایندکس روی کلیدهای ترکیب.

    CREATE INDEX idx_orders_customer_id ON orders (customer_id);
    CREATE INDEX idx_customers_id ON customers (id);

۴.۴ مرتب‌سازی و گروه‌بندی

• Sort: زمانی که مرتب‌سازی بدون ایندکس انجام می‌شود.
  • راه‌حل: ایجاد ایندکس برای ستون‌های مرتب‌شده.
  • مثال:

    CREATE INDEX idx_orders_date_amount ON orders (order_date, amount);

• Group Aggregate: عملیات گروه‌بندی با اسکن کامل جدول.
  • راه‌حل: استفاده از ایندکس‌های ترکیبی یا Partial Index.

۵. ابزارهای کمکی برای تحلیل گلوگاه‌ها

۵.۱ pg_stat_statements

ابزاری برای مانیتورینگ پرس‌وجوهای کند و پرتکرار.

• فعال‌سازی:

  CREATE EXTENSION pg_stat_statements;

• مشاهده پرس‌وجوهای کند:

  SELECT query, total_time, calls 
  FROM pg_stat_statements 
  ORDER BY total_time DESC 
  LIMIT 10;

۵.۲ pgBadger

ابزاری برای تحلیل لاگ‌ها و شناسایی گلوگاه‌ها.

۵.۳ pg_top

برای مشاهده بار پردازشی پرس‌وجوها در لحظه.

۶. بهترین روش‌ها برای تحلیل Query Plan

۶.۱ استفاده از ANALYZE و به‌روزرسانی آمار

اطلاعات قدیمی درباره جداول می‌تواند منجر به تصمیم‌گیری‌های اشتباه در Query Plan شود. دستور زیر را به طور منظم اجرا کنید:

ANALYZE;

۶.۲ تنظیم پارامترهای PostgreSQL

• work_mem: افزایش حافظه موقت برای مرتب‌سازی‌ها و هش‌ها.
• effective_cache_size: تعیین حافظه کش در دسترس سیستم.

۶.۳ بررسی Parallel Queries

فعال‌سازی اجرای موازی برای پرس‌وجوهای بزرگ:

SET parallel_setup_cost = 1000;
SET parallel_tuple_cost = 0.1;

جمع‌بندی

تحلیل Query Plan در PostgreSQL یک گام ضروری برای بهینه‌سازی عملکرد پایگاه داده است. با استفاده از ابزارهایی مانند EXPLAIN و EXPLAIN ANALYZE می‌توان گلوگاه‌های عملکردی را شناسایی و بهینه‌سازی‌های لازم را انجام داد. ایجاد ایندکس‌های مناسب، تنظیم پارامترهای پایگاه داده، و استفاده از ابزارهای تحلیل پیشرفته مانند pg_stat_statements و pgBadger می‌تواند به بهبود کارایی سیستم کمک شایانی کند.[/cdb_course_lesson][cdb_course_lesson title=”فصل 3: مدیریت منابع حافظه”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تنظیم مقادیر مناسب برای Shared Buffers” subtitle=”توضیحات کامل”]Shared Buffers یکی از مهم‌ترین پارامترهای تنظیمی در PostgreSQL است که نقش کلیدی در عملکرد پایگاه داده دارد. این پارامتر تعیین می‌کند چه مقدار از حافظه سیستم به عنوان کش برای ذخیره داده‌های پرتکرار (Hot Data) استفاده شود. داده‌هایی که در Shared Buffers نگهداری می‌شوند، دسترسی سریع‌تر دارند و نیاز به I/O دیسک کاهش می‌یابد.

۱. مفهوم Shared Buffers

• Shared Buffers بخشی از حافظه اصلی (RAM) است که PostgreSQL برای ذخیره بلوک‌های داده و ایندکس‌های مورد نیاز استفاده می‌کند.
• زمانی که یک پرس‌وجو اجرا می‌شود، PostgreSQL ابتدا داده‌ها را از Shared Buffers می‌خواند. اگر داده موجود نباشد، از دیسک خوانده شده و در این بافر ذخیره می‌شود.

۲. تعیین مقدار مناسب برای Shared Buffers

۲.۱ مقدار پیش‌فرض

مقدار پیش‌فرض این پارامتر در PostgreSQL معمولاً 128 مگابایت یا 25٪ از کل RAM سیستم (هر کدام که کمتر باشد) است. این مقدار برای سیستم‌های بزرگ کافی نیست.

۲.۲ توصیه عمومی

• برای سیستم‌های تک‌کاربره یا توسعه:
  مقدار 25٪ از کل RAM کافی است.
• برای سیستم‌های پرترافیک یا سرور تولیدی: تنظیم بین 25٪ تا 40٪ از RAM معمولاً عملکرد بهتری ایجاد می‌کند.

۲.۳ تأثیر افزایش بیش از حد

اگر مقدار Shared Buffers بیش از حد افزایش یابد:

• ممکن است سیستم‌عامل به جای PostgreSQL کش را مدیریت کند.
• عملکرد حافظه کاهش می‌یابد، به‌خصوص اگر RAM برای سایر فرآیندها کافی نباشد.

۳. نحوه تنظیم Shared Buffers

۳.۱ تغییر مقدار Shared Buffers

برای تنظیم مقدار مناسب:

1. فایل تنظیمات PostgreSQL (معمولاً postgresql.conf) را باز کنید:

   sudo nano /etc/postgresql/<version>/main/postgresql.conf

2. مقدار پارامتر shared_buffers را تغییر دهید:

   shared_buffers = 4GB

3. سرویس PostgreSQL را مجدداً راه‌اندازی کنید:

   sudo systemctl restart postgresql

۳.۲ مشاهده مقدار فعلی Shared Buffers

از دستور زیر برای بررسی مقدار تنظیم‌شده استفاده کنید:

SHOW shared_buffers;

۴. ابزارهای بررسی تأثیر Shared Buffers

۴.۱ pg_stat_bgwriter

این ابزار اطلاعاتی درباره استفاده از Shared Buffers و نرخ نوشتن به دیسک ارائه می‌دهد:

SELECT * FROM pg_stat_bgwriter;

• buffers_allocated: تعداد بلوک‌های جدید تخصیص‌یافته.
• buffers_checkpoint: تعداد بلوک‌هایی که در زمان Checkpoint نوشته شده‌اند.
• buffers_clean: تعداد بلوک‌های تمیز شده توسط Background Writer.

۴.۲ ابزارهای سیستم‌عامل

می‌توانید از ابزارهایی مانند htop و free -m برای مانیتورینگ مصرف حافظه استفاده کنید و اطمینان حاصل کنید که مقدار RAM کافی برای سیستم‌عامل و سایر فرآیندها باقی‌مانده است.

۵. تنظیمات مرتبط با Shared Buffers

۵.۱ wal_buffers

• وظیفه: ذخیره تغییرات داده قبل از نوشتن در فایل‌های WAL (Write-Ahead Logging).
• مقدار پیشنهادی: 3٪ از مقدار shared_buffers (حداکثر تا 16MB).

۵.۲ effective_cache_size

• وظیفه: تخمین میزان حافظه کش در دسترس برای سیستم‌عامل.
• مقدار پیشنهادی: 50٪ تا 75٪ از کل RAM.

۵.۳ work_mem

• وظیفه: تنظیم حافظه موقت برای مرتب‌سازی و Join.
• مقدار پیشنهادی: بسته به تعداد کاربر همزمان، معمولاً بین 4MB تا 64MB.

جمع‌بندی

• مقدار مناسب Shared Buffers تأثیر زیادی بر عملکرد PostgreSQL دارد. برای اکثر سیستم‌ها، تخصیص 25٪ تا 40٪ از RAM کافی است.
• پس از تنظیم، با استفاده از ابزارهای PostgreSQL و سیستم‌عامل عملکرد را بررسی کنید و در صورت نیاز تغییرات بیشتری اعمال کنید.
• توجه: همیشه قبل از اعمال تغییرات در سیستم تولیدی، تنظیمات را در یک محیط آزمایشی بررسی کنید.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”پیکربندی work_mem و maintenance_work_mem برای افزایش سرعت پردازش” subtitle=”توضیحات کامل”]work_mem و maintenance_work_mem دو پارامتر حیاتی در PostgreSQL هستند که تنظیم درست آنها تأثیر بسزایی در بهبود عملکرد پرس‌وجوها و عملیات پایگاه داده دارد. این پارامترها حافظه تخصیص داده شده برای عملیات موقت، مرتب‌سازی داده‌ها، و پردازش‌های سنگین مانند بازسازی ایندکس‌ها را مشخص می‌کنند.

۱. مفهوم work_mem و maintenance_work_mem

work_mem

• حافظه اختصاص‌یافته برای هر عملیات موقت مانند مرتب‌سازی (Sort) و پردازش Join.
• مقدار این پارامتر به ازای هر کاربر همزمان و هر پرس‌وجو تخصیص داده می‌شود.
• اگر مقدار حافظه تخصیص‌یافته کافی نباشد، PostgreSQL داده‌ها را به دیسک منتقل می‌کند که موجب کاهش سرعت می‌شود.

maintenance_work_mem

• حافظه اختصاص‌یافته برای عملیات‌های خاص مدیریتی مانند:
  • VACUUM
  • CREATE INDEX
  • ALTER TABLE
  • CLUSTER
• این پارامتر معمولاً برای عملیات حجیم و غیرهمزمان استفاده می‌شود.

۲. تعیین مقدار مناسب برای work_mem و maintenance_work_mem

۲.۱ تعیین مقدار برای work_mem

• مقدار مناسب به تعداد کاربران همزمان و پیچیدگی پرس‌وجوها بستگی دارد.
• مقدار پیش‌فرض معمولاً کم است (4MB یا 8MB).
• توصیه می‌شود برای سیستم‌های پرترافیک:
  • ۴ تا ۱۶ مگابایت برای هر پرس‌وجوی ساده.
  • ۳۲ تا ۶۴ مگابایت برای پرس‌وجوهای پیچیده.

محاسبه مقدار کل حافظه مصرفی

Total Memory = work_mem × Number of Simultaneous Queries

به عنوان مثال:

• اگر مقدار work_mem برابر 16MB و تعداد پرس‌وجوهای همزمان 50 باشد:

  Total Memory = 16MB × 50 = 800MB

• اطمینان حاصل کنید که مقدار کل حافظه مصرفی کمتر از کل RAM موجود باشد.

۲.۲ تعیین مقدار برای maintenance_work_mem

• این پارامتر معمولاً بزرگ‌تر از work_mem تنظیم می‌شود، زیرا برای عملیات مدیریتی از آن استفاده می‌شود.
• مقدار پیشنهادی:
  • ۲۵۶ مگابایت تا ۱ گیگابایت برای سرورهای تولیدی.
  • اگر عملیات حجیم‌تر باشد (مانند بازسازی ایندکس)، مقدار بیشتری اختصاص دهید.

۳. نحوه تنظیم work_mem و maintenance_work_mem

۳.۱ تنظیم مقادیر در فایل postgresql.conf

1. فایل تنظیمات PostgreSQL را باز کنید:

   sudo nano /etc/postgresql/<version>/main/postgresql.conf

2. مقادیر پارامترها را تنظیم کنید:

   work_mem = 16MB
   maintenance_work_mem = 512MB

3. سرویس PostgreSQL را مجدداً راه‌اندازی کنید:

   sudo systemctl restart postgresql

۳.۲ تنظیم مقدار موقت در جلسات SQL

برای تغییر موقت این مقادیر در یک جلسه SQL:

SET work_mem = '32MB';
SET maintenance_work_mem = '1GB';

۴. تحلیل تأثیر work_mem

۴.۱ استفاده از ابزار EXPLAIN ANALYZE

برای بررسی نیاز به تنظیم work_mem، می‌توانید از ابزار EXPLAIN ANALYZE استفاده کنید:

EXPLAIN ANALYZE SELECT * FROM large_table ORDER BY column_name;

• اگر در طرح اجرایی (Query Plan) پیغام‌هایی مانند Disk: Sort مشاهده شود، مقدار work_mem کافی نیست.

۴.۲ مشاهده مقدار حافظه مصرف‌شده

می‌توانید از دیدگاه‌های سیستمی PostgreSQL برای بررسی مقدار حافظه مصرف‌شده استفاده کنید:

SELECT name, setting FROM pg_settings WHERE name LIKE '%work_mem%';

۵. تنظیمات مرتبط با work_mem و maintenance_work_mem

۵.۱ تنظیم effective_cache_size

• وظیفه: تخمین مقدار کش موجود برای استفاده توسط سیستم‌عامل.
• مقدار پیشنهادی: 50٪ تا 75٪ از کل RAM.

۵.۲ تنظیم temp_buffers

• وظیفه: حافظه تخصیص‌یافته برای جداول موقت.
• مقدار پیشنهادی: 8MB تا 32MB.

۶. نکات کاربردی برای بهینه‌سازی

1. مقدار را برای هر سناریو تنظیم کنید:
   مقادیر بالاتر برای سیستم‌های با RAM بیشتر و تعداد کاربران کمتر مناسب است.
2. بررسی بار سیستم:
   از ابزارهایی مانند htop یا free -m برای بررسی مصرف حافظه استفاده کنید.
3. تنظیم موقت برای پرس‌وجوهای خاص:
   برای پرس‌وجوهای پیچیده، از تنظیمات موقت work_mem استفاده کنید تا سایر پرس‌وجوها تحت تأثیر قرار نگیرند.
4. آزمایش و اندازه‌گیری:
   بعد از تغییر مقادیر، عملکرد سیستم را تحت بار واقعی بررسی کنید.

جمع‌بندی

• تنظیم صحیح work_mem و maintenance_work_mem می‌تواند تأثیر چشمگیری بر سرعت پردازش و کاهش I/O دیسک داشته باشد.
• مقدار work_mem باید بر اساس تعداد پرس‌وجوهای همزمان و منابع موجود تنظیم شود.
• maintenance_work_mem برای عملیات مدیریتی بزرگ تنظیم می‌شود و معمولاً مقدار بیشتری نسبت به work_mem دارد.
• پس از تنظیم این پارامترها، از ابزارهای تحلیل مانند EXPLAIN ANALYZE و pg_stat_activity برای بررسی تأثیر تغییرات استفاده کنید.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”بهینه‌سازی استفاده از کش با تنظیم effective_cache_size” subtitle=”توضیحات کامل”]پارامتر effective_cache_size یکی از مهم‌ترین تنظیمات در PostgreSQL است که تأثیر قابل توجهی بر عملکرد سیستم دارد. این پارامتر به PostgreSQL نشان می‌دهد که چه مقدار از حافظه سیستم می‌تواند برای کش داده‌ها استفاده شود، که به تخمین هزینه اجرای پرس‌وجوها کمک می‌کند. تنظیم صحیح این پارامتر، به بهبود استفاده از حافظه سیستم و کاهش نیاز به I/O دیسک کمک می‌کند.

۱. مفهوم effective_cache_size

• این پارامتر اندازه تقریبی کش موجود در سیستم‌عامل را به PostgreSQL اطلاع می‌دهد.
• تأثیر آن:
  PostgreSQL از این مقدار برای برآورد هزینه دسترسی به داده‌ها استفاده می‌کند. اگر کش مؤثر بزرگ‌تر باشد، موتور پایگاه داده اولویت بیشتری به استفاده از ایندکس‌ها می‌دهد.
• نکته مهم:
  effective_cache_size نشان‌دهنده میزان واقعی حافظه نیست؛ بلکه یک تخمین است که PostgreSQL را در تصمیم‌گیری‌های مربوط به طرح اجرایی پرس‌وجوها کمک می‌کند.

۲. تعیین مقدار مناسب برای effective_cache_size

۲.۱ راهنمای کلی

• مقدار پیشنهادی ۵۰٪ تا ۷۵٪ از کل حافظه RAM سیستم است.
• اگر سیستم صرفاً برای PostgreSQL استفاده می‌شود و فرآیندهای دیگر مصرف حافظه ندارند، می‌توانید مقدار بیشتری برای این پارامتر اختصاص دهید.

۲.۲ بررسی مقدار کش قابل‌استفاده در سیستم‌عامل

برای تعیین میزان کش فعلی که سیستم‌عامل استفاده می‌کند:

1. از دستور زیر در لینوکس استفاده کنید:

   free -m

   خروجی:

                total        used        free      shared  buff/cache   available
   Mem:          16384        4096        2048         128        10240       14336

   مقدار buff/cache نشان‌دهنده کش فعلی سیستم است.

2. بر اساس این مقدار، می‌توانید مقدار مناسب برای effective_cache_size را تعیین کنید.

۲.۳ نمونه محاسبه

• اگر سرور دارای 32GB RAM باشد و مقدار buff/cache در سیستم‌عامل حدود 24GB باشد:
  • مقدار مناسب برای effective_cache_size می‌تواند حدود 24GB یا 25GB تنظیم شود.
  • مقدار به شکل زیر نوشته می‌شود:

    effective_cache_size = 25GB

۳. نحوه تنظیم effective_cache_size

۳.۱ تنظیم در فایل postgresql.conf

1. فایل تنظیمات PostgreSQL را باز کنید:

   sudo nano /etc/postgresql/<version>/main/postgresql.conf

2. مقدار effective_cache_size را تنظیم کنید:

   effective_cache_size = 24GB

۳.۲ تنظیم مقدار موقت در جلسات SQL

اگر می‌خواهید مقدار را به طور موقت تنظیم کنید:

SET effective_cache_size = '24GB';

۴. تأثیر effective_cache_size در عملکرد

۴.۱ تأثیر بر انتخاب ایندکس

• PostgreSQL از مقدار effective_cache_size برای تصمیم‌گیری درباره استفاده از ایندکس‌ها استفاده می‌کند.
• اگر مقدار این پارامتر کم تنظیم شود، موتور ممکن است از ایندکس‌ها استفاده نکند، زیرا هزینه دسترسی به داده‌ها را بیشتر از آنچه که واقعاً هست تخمین می‌زند.

۴.۲ بهبود طرح اجرایی پرس‌وجو

• می‌توانید از EXPLAIN برای بررسی تأثیر مقدار effective_cache_size استفاده کنید.
• مثال:

  EXPLAIN SELECT * FROM large_table WHERE column_name = 'value';

  طرح اجرایی را قبل و بعد از تنظیم effective_cache_size بررسی کنید.

۵. نظارت و بهینه‌سازی

۵.۱ بررسی تأثیر مقدار تنظیم‌شده

• از ابزارهای داخلی PostgreSQL مانند pg_stat_statements برای بررسی مدت زمان اجرای پرس‌وجوها استفاده کنید.
• ابزارهای خارجی مانند pgBadger نیز می‌توانند گزارش‌های تحلیلی ارائه دهند.

۵.۲ ترکیب با تنظیمات مرتبط

• shared_buffers:
  حافظه تخصیص‌یافته برای نگهداری داده‌های موقت در PostgreSQL. باید متناسب با effective_cache_size تنظیم شود (معمولاً ۲۵٪ از RAM).

۶. نکات کاربردی

1. تنظیم متناسب با بار کاری:
   اگر بار کاری سیستم متغیر است، مقدار effective_cache_size را مطابق با بیشترین میزان حافظه کش مورد انتظار تنظیم کنید.
2. آزمایش و اندازه‌گیری:
   پس از تغییر مقدار effective_cache_size، عملکرد پرس‌وجوها را تحت بار واقعی بررسی کنید.
3. عدم تخصیص بیش از حد:
   از تنظیم مقدار بیش از حد برای این پارامتر خودداری کنید، زیرا می‌تواند منجر به برآوردهای اشتباه توسط PostgreSQL شود.
4. بررسی عملکرد سیستم‌عامل:
   اطمینان حاصل کنید که مقدار RAM کافی برای کش و فرآیندهای دیگر سیستم باقی مانده باشد.

جمع‌بندی

• effective_cache_size به PostgreSQL کمک می‌کند تا تصمیمات بهتری درباره استفاده از ایندکس‌ها و اجرای پرس‌وجوها بگیرد.
• مقدار مناسب این پارامتر باید به‌گونه‌ای تنظیم شود که با مقدار کش موجود در سیستم‌عامل همخوانی داشته باشد.
• تنظیم درست این پارامتر به‌ویژه در سیستم‌هایی با حجم داده‌های زیاد و بار کاری سنگین، می‌تواند منجر به بهبود قابل‌توجهی در عملکرد پرس‌وجوها شود.
• برای دستیابی به بهترین نتیجه، این تنظیم را همراه با shared_buffers و سایر پارامترهای حافظه بهینه‌سازی کنید.

[/cdb_course_lesson][cdb_course_lesson title=”فصل 4: تنظیمات پیشرفته Autovacuum”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”توضیح عملکرد Autovacuum و نقش آن در مدیریت جدول‌ها” subtitle=”توضیحات کامل”]یکی از ویژگی‌های کلیدی PostgreSQL، مکانیزم Autovacuum است که برای مدیریت خودکار فضای ذخیره‌سازی و حفظ عملکرد پایگاه داده طراحی شده است. این مکانیزم به‌طور خودکار داده‌ها را در جداول تمیز می‌کند، شاخص‌ها را بهینه‌سازی می‌کند و به حذف داده‌های غیرضروری کمک می‌کند.

۱. مفهوم Autovacuum

چرا نیاز به Autovacuum داریم؟

PostgreSQL از مکانیزم MVCC (Multi-Version Concurrency Control) برای مدیریت همزمانی استفاده می‌کند.

• در این مدل، به جای حذف فوری رکوردها هنگام عملیات DELETE یا UPDATE، رکوردهای قدیمی همچنان در حافظه باقی می‌مانند.
• این مسئله باعث رشد فضای غیرضروری و کاهش کارایی پرس‌وجوها می‌شود.
  Autovacuum برای رفع این مشکل طراحی شده است.

وظایف اصلی Autovacuum:

1. پاک‌سازی داده‌های مرده (Dead Tuples):
   رکوردهای قدیمی که دیگر موردنیاز نیستند، حذف می‌شوند.
2. جلوگیری از تورم جداول (Table Bloat):
   فضای ذخیره‌سازی جداول و ایندکس‌ها بهینه می‌شود.
3. بازیابی فضای استفاده نشده:
   فضای آزاد شده به سیستم فایل باز نمی‌گردد، اما برای استفاده‌های بعدی در پایگاه داده آماده می‌شود.
4. به‌روزرسانی آمارها (Statistics):
   آمارهای مربوط به جداول و ایندکس‌ها به‌روز می‌شوند، که این امر به بهبود طرح اجرایی پرس‌وجوها کمک می‌کند.
5. جلوگیری از Wraparound:
   با پاک‌سازی مقادیر قدیمی Transaction ID، از مشکلاتی مانند Wraparound جلوگیری می‌کند.

۲. نحوه عملکرد Autovacuum

Autovacuum از طریق فرآیندهای پس‌زمینه (Background Processes) عمل می‌کند:

1. Autovacuum Daemon:
   این فرآیند به طور مداوم جداول پایگاه داده را برای بررسی نیاز به VACUUM یا ANALYZE نظارت می‌کند.
2. تشخیص جداول نیازمند Vacuum:
   Autovacuum معیارهای خاصی را بررسی می‌کند تا مشخص کند چه زمانی یک جدول نیاز به تمیزکاری دارد. مهم‌ترین این معیارها عبارت‌اند از:
   • تعداد رکوردهای حذف‌شده یا به‌روزرسانی‌شده (Dead Tuples).
   • نرخ تغییرات در جدول.
3. انجام عملیات Vacuum و Analyze:
   عملیات VACUUM فضای اشغال‌شده توسط رکوردهای حذف‌شده را بازیابی می‌کند. ANALYZE نیز آمارهای جدول را به‌روزرسانی می‌کند.

۳. تنظیمات اصلی Autovacuum

تنظیمات مربوط به Autovacuum در فایل postgresql.conf قرار دارد. برخی از تنظیمات کلیدی عبارت‌اند از:

۳.۱ فعال‌سازی یا غیرفعال‌سازی Autovacuum

• برای فعال کردن Autovacuum:

  autovacuum = on

• اگر بخواهید آن را غیرفعال کنید:

  autovacuum = off

  نکته مهم: غیرفعال کردن Autovacuum تنها در شرایط خاص توصیه می‌شود.

۳.۲ تنظیم آستانه‌های Vacuum و Analyze

• autovacuum_vacuum_threshold:
  حداقل تعداد تغییرات لازم برای اجرای VACUUM. مقدار پیش‌فرض:

  autovacuum_vacuum_threshold = 50

• autovacuum_analyze_threshold:
  حداقل تعداد تغییرات لازم برای اجرای ANALYZE. مقدار پیش‌فرض:

  autovacuum_analyze_threshold = 50

۳.۳ تنظیم نسبت تغییرات

• autovacuum_vacuum_scale_factor:
  نسبت تغییرات در جدول که باعث اجرای VACUUM می‌شود. مقدار پیش‌فرض:

  autovacuum_vacuum_scale_factor = 0.2

  به این معنا که اگر ۲۰٪ رکوردهای جدول تغییر کنند، VACUUM اجرا می‌شود.

• autovacuum_analyze_scale_factor:
  نسبت تغییرات برای اجرای ANALYZE. مقدار پیش‌فرض:

  autovacuum_analyze_scale_factor = 0.1

۳.۴ تنظیم منابع Autovacuum

• autovacuum_max_workers:
  تعداد فرآیندهای همزمان Autovacuum. مقدار پیش‌فرض:

  autovacuum_max_workers = 3

• autovacuum_naptime:
  فاصله زمانی بین هر چرخه Autovacuum. مقدار پیش‌فرض:

  autovacuum_naptime = 1min

• autovacuum_vacuum_cost_limit:
  حداکثر هزینه‌ای که هر فرآیند Autovacuum می‌تواند مصرف کند:

  autovacuum_vacuum_cost_limit = 200

۴. بررسی وضعیت Autovacuum

برای بررسی جداولی که تحت نظارت Autovacuum هستند:

SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 0;

• relname: نام جدول.
• last_autovacuum: زمان آخرین VACUUM.
• last_autoanalyze: زمان آخرین ANALYZE.
• n_dead_tup: تعداد رکوردهای حذف‌شده (Dead Tuples).

۵. مشکلات رایج و راه‌حل‌ها

۵.۱ Autovacuum به اندازه کافی سریع نیست

• مشکل: جداول بزرگ با نرخ تغییرات بالا ممکن است نیاز به تمیزکاری بیشتری داشته باشند.
• راه‌حل:
  • کاهش مقدار autovacuum_naptime.
  • افزایش مقدار autovacuum_max_workers.
  • تنظیم مقادیر پایین‌تر برای threshold و scale_factor.

۵.۲ تأخیر در اجرای پرس‌وجوها

• مشکل: فرآیند Autovacuum ممکن است باعث کاهش عملکرد سیستم شود.
• راه‌حل:
  • تنظیم پارامتر autovacuum_vacuum_cost_limit برای محدود کردن منابع.
  • تنظیم مقادیر مناسب برای autovacuum_vacuum_cost_delay.

۶. مزایا و اهمیت Autovacuum

1. حفظ کارایی:
   با حذف داده‌های غیرضروری و به‌روزرسانی آمارها، عملکرد پرس‌وجوها بهبود می‌یابد.
2. مدیریت خودکار:
   نیازی به اجرای دستی VACUUM و ANALYZE نیست.
3. جلوگیری از Wraparound:
   پایگاه داده به طور خودکار از مشکلات مربوط به Wraparound جلوگیری می‌کند.

جمع‌بندی

Autovacuum یک ابزار کلیدی در PostgreSQL است که با پاک‌سازی داده‌های مرده، به‌روزرسانی آمارها و مدیریت فضای ذخیره‌سازی، عملکرد سیستم را حفظ می‌کند. تنظیم مناسب این مکانیزم برای جداول با حجم بالا و بار کاری سنگین ضروری است. با نظارت و پیکربندی دقیق، می‌توان از کارایی بهینه پایگاه داده اطمینان حاصل کرد.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”پیکربندی پارامترهای Autovacuum برای پایگاه‌های داده بزرگ” subtitle=”توضیحات کامل”]مدیریت و بهینه‌سازی پایگاه‌های داده بزرگ (Large Databases) نیازمند تنظیمات دقیق برای مکانیزم Autovacuum است. در این تنظیمات، باید تعادل میان استفاده از منابع سیستم و بهبود عملکرد پایگاه داده برقرار شود. در ادامه، به پارامترهای مهم و نحوه پیکربندی آن‌ها برای پایگاه‌های داده بزرگ می‌پردازیم.

۱. معیارهای اساسی برای تنظیم Autovacuum

هنگام پیکربندی Autovacuum برای پایگاه‌های داده بزرگ، این معیارها را در نظر بگیرید:

• حجم داده‌ها: حجم جداول و ایندکس‌ها تعیین‌کننده مقدار منابع موردنیاز است.
• نرخ تغییرات: تعداد عملیات درج، حذف یا به‌روزرسانی در جداول.
• فشار روی سیستم: میزان بار کاری سرور و منابع موجود.
• زمان پاسخ‌دهی پرس‌وجوها: تأخیر ایجاد شده توسط Autovacuum نباید بر عملکرد اصلی پایگاه داده تأثیر منفی بگذارد.

۲. پارامترهای کلیدی برای پیکربندی Autovacuum

۲.۱ autovacuum_max_workers

• توضیح: تعداد فرآیندهای همزمان Autovacuum که می‌توانند اجرا شوند.
• پیکربندی: افزایش این مقدار برای پایگاه‌های داده بزرگ با تعداد زیادی جدول ضروری است.
• پیشنهاد:

  autovacuum_max_workers = 5

  نکته: تعداد بیش‌ازحد بالا ممکن است باعث رقابت برای منابع شود.

۲.۲ autovacuum_naptime

• توضیح: فاصله زمانی بین چرخه‌های بررسی Autovacuum.
• پیکربندی: کاهش این مقدار باعث می‌شود Autovacuum سریع‌تر جداول را بررسی کند.
• پیشنهاد:

  autovacuum_naptime = 30s

  نکته: مقدار کمتر از ۳۰ ثانیه برای سرورهای سنگین توصیه نمی‌شود.

۲.۳ autovacuum_vacuum_threshold و autovacuum_vacuum_scale_factor

• توضیح: این پارامترها تعیین می‌کنند چه زمانی Autovacuum روی یک جدول اجرا شود:
  • autovacuum_vacuum_threshold: حداقل تعداد تغییرات در یک جدول.
  • autovacuum_vacuum_scale_factor: درصدی از رکوردهای جدول که باید تغییر کرده باشد.
• پیکربندی: کاهش این مقادیر باعث تمیزکاری مکرر اما کمتر سنگین می‌شود.
• پیشنهاد:

  autovacuum_vacuum_threshold = 1000
  autovacuum_vacuum_scale_factor = 0.05

۲.۴ autovacuum_analyze_threshold و autovacuum_analyze_scale_factor

• توضیح: این پارامترها شرایط اجرای ANALYZE را تعیین می‌کنند:
  • autovacuum_analyze_threshold: حداقل تغییرات لازم برای اجرای ANALYZE.
  • autovacuum_analyze_scale_factor: درصدی از رکوردهای جدول که باید تغییر کرده باشد.
• پیشنهاد:

  autovacuum_analyze_threshold = 1000
  autovacuum_analyze_scale_factor = 0.02

۲.۵ autovacuum_vacuum_cost_limit

• توضیح: میزان هزینه‌ای که هر فرآیند Autovacuum می‌تواند مصرف کند.
• پیکربندی: افزایش این مقدار به Autovacuum اجازه می‌دهد تمیزکاری را سریع‌تر انجام دهد.
• پیشنهاد:

  autovacuum_vacuum_cost_limit = 2000

۲.۶ autovacuum_vacuum_cost_delay

• توضیح: تأخیر زمانی بین هر عملیات I/O در Autovacuum (بر حسب میلی‌ثانیه).
• پیکربندی: کاهش این مقدار باعث افزایش سرعت Autovacuum می‌شود.
• پیشنهاد:

  autovacuum_vacuum_cost_delay = 10ms

۲.۷ maintenance_work_mem

• توضیح: مقدار حافظه‌ای که فرآیندهای Autovacuum می‌توانند برای عملیات VACUUM و ANALYZE استفاده کنند.
• پیکربندی: افزایش این مقدار برای جداول بزرگ مفید است.
• پیشنهاد:

  maintenance_work_mem = 512MB

۳. نمونه تنظیمات پیشنهادی

یک تنظیم پیشنهادی برای پایگاه‌های داده بزرگ:

autovacuum = on
autovacuum_max_workers = 5
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 1000
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_threshold = 1000
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 10ms
maintenance_work_mem = 512MB

۴. بررسی عملکرد Autovacuum

پس از اعمال تنظیمات، وضعیت Autovacuum را بررسی کنید:

1. بررسی تعداد فرآیندهای Autovacuum فعال:

   SELECT * FROM pg_stat_activity WHERE query LIKE '%autovacuum%';

2. بررسی جداول تحت VACUUM یا ANALYZE:

   SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
   FROM pg_stat_user_tables
   WHERE n_dead_tup > 0;

3. بررسی لاگ‌های Autovacuum:
   با فعال کردن گزارش‌گیری:

   log_autovacuum_min_duration = 0

جمع‌بندی

پیکربندی Autovacuum برای پایگاه‌های داده بزرگ به دقت و تنظیمات مناسب منابع نیاز دارد. با استفاده از مقادیر پیشنهادی و نظارت بر عملکرد، می‌توان تمیزکاری کارآمدی انجام داد و از بروز مشکلاتی مانند تورم جداول یا کاهش عملکرد جلوگیری کرد. همچنین، نظارت مداوم بر لاگ‌ها و آمارهای Autovacuum برای بهینه‌سازی بیشتر ضروری است.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تشخیص مشکلات Autovacuum و رفع آن‌ها” subtitle=”توضیحات کامل”]مکانیزم Autovacuum در PostgreSQL برای مدیریت خودکار جداول، جلوگیری از تورم (bloat)، و بهبود عملکرد طراحی شده است. بااین‌حال، مشکلاتی ممکن است رخ دهند که نیازمند تشخیص و رفع آن‌ها هستند. در این بخش، به بررسی مشکلات رایج، نحوه تشخیص و راهکارهای رفع آن‌ها پرداخته می‌شود.

۱. مشکلات رایج در Autovacuum

۱.۱ تأخیر طولانی در اجرای Autovacuum

• نشانه‌ها: افزایش حجم جداول، کاهش عملکرد پایگاه داده، تأخیر در پاسخ‌دهی پرس‌وجوها.
• علت‌ها:
  • تعداد فرآیندهای کم برای Autovacuum.
  • زمان‌بندی نادرست چرخه‌های Autovacuum.
  • محدودیت‌های منابع (CPU، I/O یا حافظه).

۱.۲ تورم جداول (Bloat)

• نشانه‌ها: افزایش بیش از حد فضای استفاده شده توسط جداول بدون حذف داده‌ها.
• علت‌ها:
  • Autovacuum به دلیل محدودیت‌های منابع یا زمان‌بندی ناکافی قادر به تمیزکاری نیست.
  • نرخ تغییرات داده بسیار بالاست.

۱.۳ تداخل Autovacuum با عملیات دیگر

• نشانه‌ها: کاهش سرعت پرس‌وجوها در زمان اجرای Autovacuum.
• علت‌ها:
  • پارامترهای هزینه (Cost) Autovacuum به‌درستی تنظیم نشده‌اند.
  • منابع محدود سیستم.

۱.۴ عدم اجرای Autovacuum

• نشانه‌ها: Autovacuum برای برخی جداول اجرا نمی‌شود.
• علت‌ها:
  • Autovacuum به‌طور دستی غیرفعال شده است.
  • تنظیمات نادرست برای آستانه اجرای Autovacuum.

۲. نحوه تشخیص مشکلات Autovacuum

۲.۱ بررسی وضعیت Autovacuum

می‌توانید وضعیت فرآیندهای Autovacuum فعال را مشاهده کنید:

SELECT * 
FROM pg_stat_activity 
WHERE query LIKE '%autovacuum%';

۲.۲ شناسایی جداول با تورم بالا

برای پیدا کردن جداول با تعداد زیادی رکورد حذف‌شده:

SELECT relname AS table_name,
       n_dead_tup AS dead_tuples,
       pg_size_pretty(pg_total_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

۲.۳ بررسی آخرین زمان اجرای Autovacuum

برای شناسایی جداولی که Autovacuum روی آن‌ها اجرا نشده است:

SELECT relname AS table_name,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE last_autovacuum IS NULL OR last_autoanalyze IS NULL;

۲.۴ بررسی لاگ‌های Autovacuum

فعال‌سازی گزارش لاگ برای فرآیندهای Autovacuum:

log_autovacuum_min_duration = 0

این تنظیم تمام فرآیندهای Autovacuum را با مدت زمان اجرای آن‌ها در لاگ ثبت می‌کند.

۳. راهکارهای رفع مشکلات Autovacuum

۳.۱ رفع تأخیر در اجرای Autovacuum

• افزایش تعداد فرآیندهای Autovacuum:

  autovacuum_max_workers = 5

• کاهش فاصله زمانی بررسی:

  autovacuum_naptime = 30s

۳.۲ کاهش تورم جداول

• کاهش آستانه‌ها برای VACUUM و ANALYZE:

  autovacuum_vacuum_threshold = 500
  autovacuum_vacuum_scale_factor = 0.01

• استفاده از VACUUM دستی برای جداول بزرگ:

  VACUUM FULL table_name;

۳.۳ بهبود عملکرد Autovacuum

• تنظیم پارامترهای هزینه:

  autovacuum_vacuum_cost_limit = 2000
  autovacuum_vacuum_cost_delay = 5ms

• افزایش حافظه برای فرآیندهای Autovacuum:

  maintenance_work_mem = 512MB

۳.۴ اطمینان از اجرای Autovacuum

• بررسی غیرفعال بودن Autovacuum در سطح پایگاه داده:

  SHOW autovacuum;

  اگر Autovacuum غیرفعال است، آن را فعال کنید:

  autovacuum = on

• بررسی تنظیمات Autovacuum برای جداول خاص:

  SELECT relname, reloptions
  FROM pg_class
  WHERE reloptions IS NOT NULL;

  تنظیم مجدد Autovacuum برای جدول:

  ALTER TABLE table_name SET (autovacuum_enabled = true);

۴. بهینه‌سازی Autovacuum برای پایگاه داده‌های بزرگ

برای پایگاه‌های داده با حجم بالا و نرخ تغییرات زیاد، ترکیبی از تنظیمات زیر را پیشنهاد می‌شود:

autovacuum_max_workers = 8
autovacuum_naptime = 10s
autovacuum_vacuum_threshold = 1000
autovacuum_vacuum_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 3000
autovacuum_vacuum_cost_delay = 5ms
maintenance_work_mem = 1GB
log_autovacuum_min_duration = 0

جمع‌بندی

مدیریت صحیح Autovacuum نقش حیاتی در حفظ سلامت پایگاه داده و جلوگیری از مشکلاتی مانند تورم جداول یا کاهش عملکرد دارد. با نظارت مداوم بر فرآیندهای Autovacuum، تنظیمات مناسب، و استفاده از ابزارهای تشخیصی، می‌توانید مشکلات را به سرعت شناسایی و رفع کنید.[/cdb_course_lesson][cdb_course_lesson title=”فصل 5: مدیریت و مانیتورینگ I/O”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”بررسی عملکرد Disk I/O با ابزارهایی مانند iostat” subtitle=”توضیحات کامل”]Disk I/O یکی از مهم‌ترین عوامل موثر بر عملکرد پایگاه داده‌ها است. ابزار iostat یکی از ابزارهای قدرتمند در لینوکس برای مانیتورینگ و تحلیل عملکرد دیسک‌ها و منابع ورودی/خروجی (I/O) است. در این بخش، نحوه استفاده از این ابزار و تفسیر داده‌های آن برای بهبود عملکرد دیسک و پایگاه داده بررسی می‌شود.

۱. مقدمه‌ای بر iostat

iostat بخشی از بسته sysstat است که اطلاعات جامعی در مورد وضعیت پردازنده، دیسک‌ها و دستگاه‌های I/O ارائه می‌دهد. این ابزار می‌تواند برای موارد زیر استفاده شود:

• شناسایی گلوگاه‌های I/O.
• تحلیل کارایی دیسک‌های ذخیره‌سازی.
• اندازه‌گیری تأثیر عملیات پایگاه داده بر منابع I/O.

نصب iostat

برای نصب این ابزار در توزیع‌های لینوکس:

• Debian/Ubuntu:

  sudo apt update
  sudo apt install sysstat

• RHEL/CentOS/AlmaLinux:

  sudo yum install sysstat

۲. استفاده از iostat

نمایش اطلاعات اولیه

دستور زیر اطلاعات کلی در مورد پردازنده و I/O را نمایش می‌دهد:

iostat

این دستور خروجی مشابه زیر تولید می‌کند:

Linux 5.15.0-56-generic (hostname)   01/01/2025  _x86_64_   (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           10.24    0.00    2.56    3.12    0.00   84.08

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               12.00       1024.00       512.00     20480     10240

توضیحات ستون‌ها

• avg-cpu: درصد زمان صرف شده توسط پردازنده در حالت‌های مختلف.
  • %iowait: درصد زمانی که پردازنده منتظر عملیات I/O است. مقدار بالای این پارامتر نشان‌دهنده گلوگاه I/O است.
• Device Metrics:
  • tps: تعداد عملیات I/O (خواندن یا نوشتن) در ثانیه.
  • kB_read/s: نرخ خواندن از دیسک (کیلوبایت بر ثانیه).
  • kB_wrtn/s: نرخ نوشتن روی دیسک (کیلوبایت بر ثانیه).

۳. بررسی دقیق عملکرد دیسک

مانیتورینگ لحظه‌ای

برای مشاهده اطلاعات لحظه‌ای، می‌توانید iostat را با فاصله زمانی اجرا کنید:

iostat -x 2

این دستور اطلاعات هر ۲ ثانیه به‌روزرسانی می‌کند و شامل جزئیات بیشتری است.

استفاده از گزینه‌های پیشرفته

• -x: نمایش اطلاعات گسترده برای دستگاه‌ها.
• -d: نمایش اطلاعات مختص دیسک‌ها.
• -k: نمایش داده‌ها در واحد کیلوبایت.

۴. تحلیل ستون‌های کلیدی

ستون‌های مهم در حالت گسترده (-x):

• %util: نشان‌دهنده درصد زمانی است که دستگاه در حال استفاده است. مقدار نزدیک به 100% نشان‌دهنده فشار کاری بالا است.
• await: میانگین زمان انتظار برای عملیات I/O (به میلی‌ثانیه). مقدار بالا می‌تواند نشان‌دهنده گلوگاه باشد.
• svctm: میانگین زمان سرویس‌دهی به هر درخواست (به میلی‌ثانیه).
• r/s و w/s: تعداد عملیات خواندن و نوشتن در ثانیه.

مثال خروجی:

Device            r/s     w/s   await   svctm  %util
sda              120.0   240.0   5.12    0.85   90.0

• مقدار بالای %util (90%) و await (5.12 ms) نشان‌دهنده فشار بالا روی دیسک است.

۵. شناسایی گلوگاه‌های Disk I/O

گام‌های تشخیص:

1. مقدار بالای %iowait: اگر در خروجی avg-cpu مقدار %iowait زیاد باشد، احتمال گلوگاه در I/O وجود دارد.
2. زمان انتظار بالا: ستون await در خروجی iostat اگر بیش از 10 میلی‌ثانیه باشد، نشان‌دهنده کندی در عملیات دیسک است.
3. استفاده بیش از حد دیسک: مقدار بالای %util (بیش از 80%) نشان‌دهنده استفاده زیاد از دیسک است.

۶. راهکارهای بهبود Disk I/O

۶.۱ بهینه‌سازی پرس‌وجوهای پایگاه داده

• شناسایی و بهینه‌سازی پرس‌وجوهای سنگین.
• استفاده از ایندکس‌ها برای کاهش تعداد عملیات دیسک.

۶.۲ افزایش منابع ذخیره‌سازی

• استفاده از دیسک‌های سریع‌تر مانند SSD.
• استفاده از تکنولوژی‌های RAID برای بهبود کارایی و تحمل‌پذیری خطا.

۶.۳ پیکربندی کش

• تنظیم مقادیر مناسب shared_buffers و effective_cache_size در PostgreSQL.
• استفاده از ابزارهای کشینگ مانند pgbouncer.

۶.۴ تقسیم‌بندی جداول (Partitioning)

• کاهش حجم داده‌های پردازش شده با تقسیم‌بندی جداول بزرگ.

جمع‌بندی

ابزار iostat ابزاری حیاتی برای شناسایی مشکلات Disk I/O و مانیتورینگ منابع سیستم است. با استفاده از این ابزار می‌توانید گلوگاه‌های عملکردی را شناسایی کرده و از طریق بهینه‌سازی پرس‌وجوها، تنظیمات مناسب و استفاده از سخت‌افزارهای بهتر، عملکرد کلی سیستم را بهبود دهید.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”شناسایی و رفع مشکلات ناشی از Disk Bottleneck” subtitle=”توضیحات کامل”]Disk Bottleneck زمانی رخ می‌دهد که عملیات ورودی/خروجی (I/O) دیسک به یک گلوگاه تبدیل شده و باعث کندی عملکرد سیستم یا پایگاه داده می‌شود. این مشکلات می‌توانند بر اثر عوامل مختلفی مانند حجم بالای داده‌ها، پرس‌وجوهای ناکارآمد، یا تنظیمات نامناسب سخت‌افزار و نرم‌افزار ایجاد شوند. در این بخش، روش‌های شناسایی و رفع مشکلات ناشی از گلوگاه دیسک بررسی می‌شوند.

۱. شناسایی Disk Bottleneck

ابزارهای شناسایی

برای شناسایی مشکلات دیسک، ابزارهای زیر بسیار مفید هستند:

1. iostat: بررسی نرخ I/O، درصد استفاده از دیسک و زمان انتظار.
2. vmstat: نمایش وضعیت کلی سیستم شامل حافظه، پردازنده و دیسک.
3. dstat: نمایش اطلاعات لحظه‌ای دیسک، پردازنده و شبکه.
4. sar: مانیتورینگ عملکرد سیستم در بازه‌های زمانی طولانی.
5. PostgreSQL Views: استفاده از نماهایی مانند pg_stat_activity و pg_stat_statements برای شناسایی پرس‌وجوهای کند.

گام‌های تشخیص

1. مقدار بالای %iowait:
   • اگر مقدار %iowait در ابزارهایی مانند iostat یا vmstat بالا باشد (بیش از 10%)، نشان‌دهنده مشکلات در I/O دیسک است.
2. مقدار زیاد await:
   • ستون await در iostat اگر بیشتر از 10 میلی‌ثانیه باشد، نشان‌دهنده زمان زیاد برای پردازش درخواست‌ها است.
3. استفاده بالای دیسک:
   • مقدار %util نزدیک به 100% نشان‌دهنده فشار بیش از حد بر دیسک است.
4. تأخیر در پرس‌وجوها:
   • با استفاده از PostgreSQL Logs یا نماهای آماری، پرس‌وجوهایی که باعث I/O سنگین می‌شوند را شناسایی کنید.

۲. دلایل اصلی Disk Bottleneck

۲.۱ پرس‌وجوهای سنگین

• اجرای پرس‌وجوهایی که نیاز به خواندن یا نوشتن حجم زیادی از داده دارند.

۲.۲ تنظیمات نامناسب پایگاه داده

• مقدار نامناسب پارامترهایی مانند shared_buffers، work_mem و maintenance_work_mem.

۲.۳ سخت‌افزار ناکافی

• استفاده از دیسک‌های کند (مانند HDD) یا ظرفیت ناکافی برای پاسخگویی به نیازهای سیستم.

۲.۴ رقابت برای منابع

• استفاده از منابع دیسک توسط فرآیندهای دیگر به‌غیر از پایگاه داده.

۳. رفع مشکلات Disk Bottleneck

۳.۱ بهینه‌سازی پرس‌وجوها

• شناسایی پرس‌وجوهای سنگین: استفاده از pg_stat_statements برای یافتن پرس‌وجوهایی که بیشترین I/O را مصرف می‌کنند.
• استفاده از ایندکس‌ها: ایجاد ایندکس‌های مناسب برای کاهش خواندن داده‌های غیرضروری.
• بهینه‌سازی طرح پرس‌وجو: استفاده از EXPLAIN و EXPLAIN ANALYZE برای بهبود کارایی.

۳.۲ بهبود سخت‌افزار

• ارتقاء به SSD: جایگزینی دیسک‌های HDD با SSD برای افزایش سرعت خواندن و نوشتن.
• RAID: استفاده از RAID 10 برای ترکیب مزایای سرعت و تحمل‌پذیری خطا.
• افزایش تعداد دیسک‌ها: توزیع بار I/O بین دیسک‌های بیشتر.

۳.۳ پیکربندی پایگاه داده

• تنظیم پارامترهای مهم PostgreSQL:
  • shared_buffers: مقدار کافی برای کش داده‌های پرکاربرد.
  • work_mem: مقدار مناسب برای پردازش موقت پرس‌وجوها.
  • effective_cache_size: تنظیم بر اساس حافظه موجود سیستم.
• استفاده از کشینگ:
  • ابزارهایی مانند pgbouncer برای کاهش بار روی دیسک.

۳.۴ پارتیشن‌بندی داده‌ها

• تقسیم جداول بزرگ به پارتیشن‌های کوچک‌تر برای کاهش بار I/O.

۳.۵ نظارت بر Autovacuum

• اطمینان از اجرای منظم Autovacuum برای جلوگیری از افزایش حجم فایل‌ها.

۳.۶ توزیع بار

• Query Scheduling: زمان‌بندی اجرای پرس‌وجوهای سنگین در بازه‌های کم‌بار.
• Replication: استفاده از Replica برای توزیع بار خواندن.

۴. مثال‌های عملی

تحلیل iostat

iostat -x 2

اگر مقدار %util برای دیسک اصلی پایگاه داده نزدیک به 100% باشد:

1. پرس‌وجوهای سنگین را شناسایی کنید.
2. ایندکس‌های اضافی ایجاد کنید.
3. سخت‌افزار را بررسی و در صورت نیاز ارتقا دهید.

استفاده از pg_stat_statements

SELECT 
    query, 
    total_time, 
    calls 
FROM 
    pg_stat_statements 
ORDER BY 
    total_time DESC 
LIMIT 5;

• پرس‌وجوهایی که بیشترین زمان را صرف کرده‌اند شناسایی و بهینه‌سازی کنید.

تنظیم shared_buffers

ویرایش فایل postgresql.conf:

shared_buffers = 4GB

سپس سرویس PostgreSQL را مجدداً راه‌اندازی کنید:

sudo systemctl restart postgresql

جمع‌بندی

مشکلات ناشی از Disk Bottleneck می‌توانند تأثیر چشم‌گیری بر عملکرد پایگاه داده داشته باشند. با شناسایی دقیق علت مشکل (با ابزارهایی مانند iostat و pg_stat_statements) و اعمال راهکارهای مناسب مانند بهینه‌سازی پرس‌وجوها، ارتقاء سخت‌افزار و تنظیمات پایگاه داده، می‌توان این مشکلات را به‌طور موثری برطرف کرد. نظارت مداوم و تحلیل داده‌ها کلید جلوگیری از مشکلات مشابه در آینده است.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”بهینه‌سازی I/O برای پایگاه‌های داده حجیم” subtitle=”توضیحات کامل”]مدیریت I/O یکی از چالش‌های اساسی برای پایگاه‌های داده حجیم است. هنگامی که داده‌ها به شدت رشد می‌کنند، بهینه‌سازی عملیات ورودی/خروجی (I/O) ضروری است تا عملکرد کلی پایگاه داده تضمین شود. در این بخش، روش‌ها و ابزارهای کاربردی برای بهینه‌سازی I/O در پایگاه‌های داده حجیم بررسی می‌شوند.

۱. دلایل اصلی مشکلات I/O در پایگاه‌های داده حجیم

1. خواندن و نوشتن بیش از حد: به دلیل حجم بالای داده‌ها و عملیات.
2. پرس‌وجوهای ناکارآمد: اجرای پرس‌وجوهایی که به اسکن کامل جداول نیاز دارند.
3. استفاده ناکارآمد از کش: عدم ذخیره‌سازی داده‌های پرکاربرد در حافظه.
4. کمبود منابع سخت‌افزاری: استفاده از دیسک‌های کند یا منابع محدود.
5. فرآیندهای جانبی: فرآیندهایی مانند Autovacuum یا بکاپ‌گیری که بار اضافی روی دیسک ایجاد می‌کنند.

۲. استراتژی‌های بهینه‌سازی I/O

۲.۱ استفاده از ایندکس‌ها

• ایجاد ایندکس‌های مناسب:
  • B-Tree: برای جستجوهای دقیق.
  • GIN و GiST: برای داده‌های غیرساختاریافته و Full-Text Search.
  • BRIN: برای جداول بزرگ که داده‌ها به صورت ترتیبی ذخیره شده‌اند.

مثال:

CREATE INDEX idx_column_name ON table_name (column_name);

۲.۲ استفاده از کش

• shared_buffers: تنظیم مناسب این پارامتر برای ذخیره داده‌های پرکاربرد در حافظه.
  • مقدار پیشنهادی: 25-40% از کل حافظه سرور.

تنظیم در postgresql.conf:

shared_buffers = 8GB

• effective_cache_size: تنظیم این پارامتر برای برآورد کش سیستم‌عامل.
  • مقدار پیشنهادی: 50-75% از کل حافظه سرور.

۲.۳ پارتیشن‌بندی جداول

• تقسیم جداول بزرگ به پارتیشن‌های کوچک‌تر برای کاهش بار I/O.

مثال:

CREATE TABLE data_table (
    id SERIAL,
    data_column TEXT,
    created_date DATE
) PARTITION BY RANGE (created_date);

CREATE TABLE data_table_2024 PARTITION OF data_table
    FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

۲.۴ بهینه‌سازی Autovacuum

• افزایش یا کاهش threshold و cost_limit بر اساس نیاز.

تنظیم در postgresql.conf:

autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_threshold = 1000

۲.۵ Query Optimization

• استفاده از EXPLAIN و EXPLAIN ANALYZE برای بهینه‌سازی طرح پرس‌وجو.

مثال:

EXPLAIN ANALYZE
SELECT * FROM table_name WHERE column_name = 'value';

۲.۶ ذخیره‌سازی مناسب

• SSD: استفاده از SSD به جای HDD برای سرعت بالاتر.
• RAID: استفاده از RAID 10 برای ترکیب سرعت و تحمل‌پذیری خطا.

۲.۷ فشرده‌سازی داده‌ها

• استفاده از فشرده‌سازی داده‌ها برای کاهش فضای ذخیره‌سازی و بار I/O.

مثال:

CREATE TABLE compressed_table (
    id SERIAL,
    data_column TEXT
) WITH (autovacuum_enabled = false)
TABLESPACE compressed_storage;

۲.۸ ایزوله‌سازی I/O

• جداسازی فایل‌های WAL (Write Ahead Logs) از داده‌های اصلی:
  • ذخیره WAL روی دیسک سریع‌تر برای کاهش تأخیر.

۳. ابزارهای مانیتورینگ و بهینه‌سازی

۳.۱ ابزارهای داخلی PostgreSQL

• pg_stat_activity: شناسایی پرس‌وجوهای فعال.
• pg_stat_statements: بررسی پرس‌وجوهای سنگین.
• pg_stat_bgwriter: مانیتورینگ فعالیت‌های نوشتن پس‌زمینه.

۳.۲ ابزارهای خارجی

• iostat: بررسی نرخ خواندن و نوشتن دیسک.
• pgBadger: تحلیل لاگ‌های PostgreSQL برای شناسایی مشکلات.
• pg_top: مانیتورینگ مصرف منابع توسط PostgreSQL.

۴. مثال عملی برای بهینه‌سازی

۴.۱ شناسایی مشکلات I/O

اجرای iostat:

iostat -dx 2

تحلیل:

• ستون %util: نشان‌دهنده استفاده از دیسک.
• ستون await: تأخیر در عملیات I/O.

۴.۲ بهینه‌سازی با ایندکس

CREATE INDEX idx_user_id ON orders (user_id);

اجرای مجدد پرس‌وجو:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123;

۴.۳ تنظیم کش

ویرایش فایل تنظیمات:

shared_buffers = 8GB
effective_cache_size = 24GB

و سپس بازنشانی سرویس:

sudo systemctl restart postgresql

جمع‌بندی

بهینه‌سازی I/O برای پایگاه‌های داده حجیم یک فرآیند چندمرحله‌ای است که شامل بهبود پرس‌وجوها، استفاده بهینه از منابع سخت‌افزاری، و پیکربندی مناسب پایگاه داده می‌شود. با استفاده از ایندکس‌ها، کش مناسب، پارتیشن‌بندی و ابزارهای نظارتی می‌توان گلوگاه‌های I/O را شناسایی و برطرف کرد. نظارت مستمر و تحلیل دوره‌ای عملکرد سیستم، کلید حفظ بهره‌وری در پایگاه‌های داده حجیم است.[/cdb_course_lesson][cdb_course_lesson title=”فصل 6: تنظیمات پیشرفته Query Parallelism”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”فعال‌سازی و پیکربندی Parallel Query Execution” subtitle=”توضیحات کامل”]اجرای موازی پرس‌وجوها (Parallel Query Execution) یکی از ویژگی‌های مهم PostgreSQL است که می‌تواند عملکرد را با توزیع کار بین چندین پردازنده بهبود بخشد. این قابلیت برای پایگاه‌های داده حجیم و پرس‌وجوهای پیچیده بسیار مفید است.

max_parallel_workers = 8
max_parallel_workers_per_gather = 4
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
force_parallel_mode = off

sudo systemctl restart postgresql

EXPLAIN (ANALYZE, VERBOSE)
SELECT SUM(column_name)
FROM large_table
WHERE column_name > 100;

Gather
  Workers Planned: 4
  ->  Parallel Seq Scan on large_table
        Filter: (column_name > 100)

max_parallel_workers_per_gather = 6

parallel_setup_cost = 500

parallel_tuple_cost = 0.05

CREATE INDEX idx_column_name ON large_table (column_name);

max_parallel_workers = 16
max_parallel_workers_per_gather = 8
parallel_setup_cost = 500
parallel_tuple_cost = 0.05

EXPLAIN ANALYZE
SELECT AVG(column_name)
FROM large_table
WHERE another_column > 1000;

Gather
  Workers Planned: 8
  ->  Parallel Seq Scan on large_table
        Filter: (another_column > 1000)

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تنظیم پارامترهای parallel_workers_per_gather و max_parallel_workers” subtitle=”توضیحات کامل”]دو پارامتر مهم برای بهبود عملکرد Parallel Query Execution در PostgreSQL عبارتند از:

• parallel_workers_per_gather: تعداد فرآیندهای کاری (worker processes) که می‌توانند برای یک عملیات Gather به کار گرفته شوند. این مقدار به تعداد هسته‌های CPU سرور و حجم کاری بستگی دارد.
• max_parallel_workers: حداکثر تعداد فرآیندهای کاری که می‌توانند در کل سرور PostgreSQL همزمان اجرا شوند.

۱. مفهوم پارامترها

parallel_workers_per_gather

این پارامتر تعیین می‌کند که PostgreSQL برای یک Gather node چند فرآیند کاری می‌تواند اجرا کند.
مقدار این پارامتر باید به گونه‌ای تنظیم شود که استفاده بهینه از هسته‌های پردازنده سرور حاصل شود.

max_parallel_workers

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

۲. تنظیم مقادیر بهینه

۲.۱ مقدار پیشنهادی برای parallel_workers_per_gather

• سیستم‌های کوچک (4 هسته CPU): parallel_workers_per_gather = 2
• سیستم‌های متوسط (8-16 هسته CPU): parallel_workers_per_gather = 4
• سیستم‌های بزرگ (>16 هسته CPU): parallel_workers_per_gather = 6 یا بیشتر

۲.۲ مقدار پیشنهادی برای max_parallel_workers

• سیستم‌های کوچک: max_parallel_workers = 8
• سیستم‌های متوسط: max_parallel_workers = 16
• سیستم‌های بزرگ: max_parallel_workers = 32

۳. پیکربندی پارامترها در فایل postgresql.conf

1. فایل تنظیمات PostgreSQL را باز کنید:

   sudo nano /etc/postgresql/<version>/main/postgresql.conf

   یا مسیر مناسب برای نصب PostgreSQL خود را جایگزین کنید.

2. مقادیر زیر را تنظیم کنید:

   parallel_workers_per_gather = 4
   max_parallel_workers = 16

3. فایل را ذخیره کنید و سرویس PostgreSQL را بازنشانی کنید:

   sudo systemctl restart postgresql

۴. بررسی و تأیید تنظیمات

برای اطمینان از اعمال تنظیمات جدید:

1. اجرای دستورات زیر در PostgreSQL:

   SHOW parallel_workers_per_gather;
   SHOW max_parallel_workers;

   خروجی باید مقادیر تنظیم‌شده در فایل پیکربندی را نشان دهد:

   SHOW parallel_workers_per_gather;
   SHOW max_parallel_workers;

2. بررسی استفاده از Parallel Query Execution:

   EXPLAIN ANALYZE
   SELECT SUM(column_name)
   FROM large_table
   WHERE another_column > 100;

   در خروجی، وجود عبارت Gather یا Parallel Seq Scan نشان‌دهنده فعال بودن اجرای موازی است.

۵. تنظیم مقادیر پویا در حین اجرا (اختیاری)

در صورت نیاز می‌توانید مقادیر این پارامترها را بدون بازنشانی سرویس تغییر دهید:

SET parallel_workers_per_gather = 6;
SET max_parallel_workers = 20;

توجه: این تغییرات فقط برای جلسه جاری معتبر هستند.

۶. نکات بهینه‌سازی

1. تعداد هسته‌های CPU: مطمئن شوید که تعداد فرآیندهای موازی (از هر دو پارامتر) بیشتر از تعداد هسته‌های موجود نباشد، زیرا این امر می‌تواند به کاهش عملکرد منجر شود.
2. حافظه کافی: فرآیندهای موازی به حافظه بیشتری نیاز دارند. اطمینان حاصل کنید که مقدار کافی برای پارامترهای work_mem و maintenance_work_mem اختصاص داده شده است.
3. اندازه پرس‌وجو: Parallel Query Execution فقط برای پرس‌وجوهایی با حجم داده‌های بزرگ و عملیات پیچیده مفید است.

جمع‌بندی

پارامترهای parallel_workers_per_gather و max_parallel_workers نقش مهمی در بهینه‌سازی عملکرد اجرای موازی در PostgreSQL ایفا می‌کنند. تنظیم دقیق این مقادیر بر اساس منابع سخت‌افزاری و نیازهای پایگاه داده، می‌تواند به بهبود چشمگیر سرعت اجرای پرس‌وجوها کمک کند. با استفاده از ابزارهایی مانند EXPLAIN می‌توانید تأثیر این تنظیمات را بررسی کرده و بهینه‌سازی‌های لازم را انجام دهید.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تحلیل عملکرد Parallel Queries و تنظیمات مرتبط” subtitle=”توضیحات کامل”]PostgreSQL قابلیت اجرای موازی (Parallel Queries) را برای افزایش سرعت پردازش داده‌های حجیم فراهم کرده است. این ویژگی با توزیع کارها بین چندین فرآیند موازی (Worker Processes) عملکرد پایگاه داده را بهینه می‌کند. تحلیل عملکرد Parallel Queries شامل شناسایی مؤثر بودن این قابلیت، بررسی تنظیمات مرتبط و شناسایی مشکلات احتمالی است.

۱. بررسی عملکرد Parallel Queries

۱.۱ استفاده از EXPLAIN و EXPLAIN ANALYZE

این ابزارها اطلاعات دقیق در مورد طرح اجرایی پرس‌وجوها ارائه می‌دهند. برای تحلیل Parallel Queries:

EXPLAIN ANALYZE
SELECT SUM(column_name)
FROM large_table
WHERE another_column > 100;

در خروجی، به دنبال موارد زیر باشید:

• Gather: نشان‌دهنده جمع‌آوری نتایج از فرآیندهای موازی.
• Parallel Seq Scan: نشان‌دهنده اسکن موازی جدول.

۱.۲ تحلیل زمان اجرا

مقایسه زمان اجرای پرس‌وجو با و بدون Parallel Query Execution به شما کمک می‌کند که تأثیر این ویژگی را بسنجید. برای غیرفعال کردن اجرای موازی در حین آزمایش:

SET max_parallel_workers_per_gather = 0;

۱.۳ مانیتورینگ فرآیندها

با ابزارهای سیستم‌عامل مانند htop یا ps می‌توانید فرآیندهای موازی PostgreSQL را مشاهده کنید. فرآیندهایی که با نام postgres: parallel worker اجرا می‌شوند، نشان‌دهنده استفاده از Parallel Queries هستند.

۲. تنظیمات مرتبط با Parallel Queries

۲.۱ پارامترهای کلیدی

۲.۲ تنظیمات فایل postgresql.conf

برای فعال‌سازی Parallel Queries و تنظیم مقادیر بهینه:

max_parallel_workers = 16
max_parallel_workers_per_gather = 4
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1

بازنشانی سرویس PostgreSQL برای اعمال تغییرات:

sudo systemctl restart postgresql

۲.۳ تغییر تنظیمات در زمان اجرا

تنظیمات مرتبط با Parallel Queries را می‌توان به صورت موقت برای آزمایش تغییر داد:

SET max_parallel_workers_per_gather = 6;
SET parallel_tuple_cost = 0.05;

۳. شناسایی مشکلات Parallel Queries

۳.۱ شناسایی گلوگاه‌ها

• CPU: اگر تعداد فرآیندهای کاری موازی بیشتر از هسته‌های CPU باشد، ممکن است کاهش عملکرد رخ دهد.
• حافظه: فرآیندهای موازی به حافظه بیشتری نیاز دارند. اگر مقدار work_mem و maintenance_work_mem پایین باشد، ممکن است فرآیندها با مشکل مواجه شوند.

۳.۲ عدم استفاده از Parallel Queries

برخی شرایط باعث می‌شوند که PostgreSQL از Parallel Queries استفاده نکند:

• جداول بسیار کوچک (کمتر از حد مشخص).
• قیدهای وابسته به ترتیب داده‌ها مانند ORDER BY بدون INDEX.
• داده‌های بسیار حجیم در یک ستون خاص که نیاز به بازسازی دارند.

۳.۳ ابزارهای مانیتورینگ

برای تحلیل بیشتر می‌توانید از ابزارهای خارجی مانند pgBadger برای مشاهده گزارش‌های عملکرد و شناسایی مشکلات استفاده کنید.

۴. تحلیل و بهینه‌سازی

۴.۱ بررسی Query Plan

با استفاده از خروجی EXPLAIN می‌توانید مراحل اجرای پرس‌وجو را تحلیل کنید:

• بررسی کنید که آیا مراحل مهم مانند Seq Scan به Parallel Seq Scan تبدیل شده‌اند.
• بهینه‌سازی ایندکس‌ها برای پشتیبانی از عملیات موازی.

۴.۲ بهینه‌سازی پارامترها

• افزایش work_mem برای پردازش مؤثرتر داده‌ها.
• کاهش parallel_tuple_cost برای تشویق PostgreSQL به استفاده از موازی‌سازی در داده‌های حجیم.

۵. نمونه‌ای از اجرای موازی و تنظیمات

پرس‌وجوی نمونه:

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM sales_data
WHERE region = 'North America';

خروجی:

Gather
  Workers Planned: 4
  ->  Parallel Seq Scan on sales_data
        Filter: (region = 'North America')
Planning Time: 1.5 ms
Execution Time: 123.4 ms

این خروجی نشان می‌دهد که PostgreSQL از 4 فرآیند موازی برای اسکن جدول استفاده کرده و زمان اجرا بهینه شده است.

جمع‌بندی

Parallel Queries در PostgreSQL یک ویژگی قدرتمند برای افزایش کارایی پردازش داده‌های حجیم است. با تحلیل دقیق عملکرد پرس‌وجوها، پیکربندی پارامترهای مناسب و شناسایی گلوگاه‌ها، می‌توانید از این قابلیت به بهترین شکل ممکن بهره‌برداری کنید. ابزارهایی مانند EXPLAIN ANALYZE و مانیتورینگ فرآیندها کمک می‌کنند که تأثیر اجرای موازی را بسنجید و تنظیمات پایگاه داده خود را بهینه کنید[/cdb_course_lesson][cdb_course_lesson title=”فصل 7: تنظیمات و بهینه‌سازی Checkpoints”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”مفهوم Checkpoints و تأثیر آن بر عملکرد” subtitle=”توضیحات کامل”]Checkpoints یکی از مفاهیم کلیدی در مدیریت پایگاه داده PostgreSQL است که نقش مهمی در حفظ سازگاری داده‌ها و بهبود عملکرد پایگاه داده دارد. درک درست از این مفهوم و تنظیم صحیح آن می‌تواند تأثیر چشمگیری بر کارایی و عملکرد کلی پایگاه داده داشته باشد.

۱. مفهوم Checkpoints

۱.۱ تعریف

Checkpoint فرایندی است که در آن PostgreSQL وضعیت فعلی پایگاه داده را به دیسک می‌نویسد و اطلاعات لازم برای بازیابی داده‌ها در صورت خرابی سرور را تضمین می‌کند. در طی این فرایند:

• تمام داده‌های تغییر یافته در shared buffers به دیسک نوشته می‌شوند.
• یک رکورد جدید در فایل‌های WAL (Write-Ahead Logging) ایجاد می‌شود.

۱.۲ نقش Checkpoints

• پایداری داده‌ها: تضمین می‌کند که تغییرات اعمال‌شده در پایگاه داده در صورت وقوع یک خطای ناگهانی قابل بازیابی هستند.
• مدیریت WAL: تعیین می‌کند که چه مقدار از فایل‌های WAL باید نگهداری شود، زیرا Checkpoints اجازه می‌دهند فایل‌های قدیمی‌تر WAL حذف شوند.

۲. نحوه عملکرد Checkpoints

۲.۱ فرآیند Checkpoint

1. PostgreSQL شروع به نوشتن داده‌های موجود در حافظه (shared buffers) به دیسک می‌کند.
2. یک رکورد Checkpoint در فایل‌های WAL نوشته می‌شود.
3. فایل‌های WAL قدیمی که دیگر نیازی به آنها نیست، حذف یا آرشیو می‌شوند.

۲.۲ رخداد Checkpoints

Checkpoints به دو صورت رخ می‌دهند:

• خودکار: بر اساس پارامترهای تنظیم‌شده در فایل تنظیمات.
• دستی: با اجرای دستور زیر توسط مدیر پایگاه داده:

  CHECKPOINT;

۳. تنظیمات مرتبط با Checkpoints

۳.۱ پارامترهای اصلی

۳.۲ تنظیمات در فایل postgresql.conf

نمونه تنظیمات برای پایگاه داده با حجم بالا:

checkpoint_timeout = 10min
checkpoint_completion_target = 0.8
max_wal_size = 2GB
min_wal_size = 1GB

پس از اعمال تغییرات، سرویس PostgreSQL را مجدداً راه‌اندازی کنید:

sudo systemctl restart postgresql

۴. تأثیر Checkpoints بر عملکرد

۴.۱ تأثیرات مثبت

• بازیابی سریع‌تر پس از خرابی: به دلیل ذخیره شدن داده‌ها در فواصل منظم، زمان لازم برای بازیابی پایگاه داده کاهش می‌یابد.
• کاهش استفاده از دیسک: فایل‌های WAL قدیمی به موقع حذف می‌شوند، که به بهینه‌سازی فضای ذخیره‌سازی کمک می‌کند.

۴.۲ تأثیرات منفی (در صورت پیکربندی نامناسب)

• افزایش تأخیر ورودی/خروجی (I/O): اگر Checkpoints بسیار نزدیک به هم اتفاق بیفتند، فشار زیادی به دیسک وارد می‌شود.
• کاهش عملکرد: عملیات نوشتن هم‌زمان ممکن است باعث کاهش عملکرد سایر پرس‌وجوها شود.

۵. بهینه‌سازی Checkpoints

۵.۱ تنظیم مناسب checkpoint_timeout

• اگر مقدار آن بسیار کم باشد: Checkpoints مکرر رخ می‌دهند، که می‌تواند باعث فشار اضافی به دیسک شود.
• اگر مقدار آن بسیار زیاد باشد: در صورت خرابی سرور، فرآیند بازیابی طولانی‌تر خواهد شد.

۵.۲ افزایش max_wal_size

این پارامتر تعیین می‌کند که چه مقدار فایل WAL قبل از شروع Checkpoint ایجاد شود. افزایش آن می‌تواند تعداد Checkpoints را کاهش دهد.

۵.۳ تنظیم دقیق checkpoint_completion_target

این پارامتر تعیین می‌کند که PostgreSQL چه مدت زمان برای تکمیل Checkpoints در نظر بگیرد. مقدار مناسب (مانند 0.8) باعث کاهش فشار I/O می‌شود.

۶. مانیتورینگ Checkpoints

۶.۱ مشاهده وضعیت Checkpoints

با استفاده از View داخلی pg_stat_bgwriter می‌توانید اطلاعات مربوط به Checkpoints را بررسی کنید:

SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint
FROM pg_stat_bgwriter;

• checkpoints_timed: تعداد Checkpoints برنامه‌ریزی‌شده.
• checkpoints_req: تعداد Checkpoints اجباری به دلیل محدودیت WAL.

۶.۲ مانیتورینگ فشار دیسک

از ابزارهای سیستم‌عامل مانند iostat یا vmstat برای بررسی استفاده از دیسک هنگام رخداد Checkpoints استفاده کنید.

جمع‌بندی

Checkpoints نقشی حیاتی در عملکرد و پایداری پایگاه داده PostgreSQL دارند. تنظیم مناسب پارامترهای مرتبط، مانند checkpoint_timeout و max_wal_size، می‌تواند فشار روی منابع را کاهش دهد و زمان بازیابی پس از خرابی را به حداقل برساند. مانیتورینگ و تحلیل منظم Checkpoints به شناسایی گلوگاه‌ها و بهینه‌سازی عملکرد کلی پایگاه داده کمک می‌کند.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تنظیم پارامترهای checkpoint_timeout و max_wal_size” subtitle=”توضیحات کامل”]دو پارامتر checkpoint_timeout و max_wal_size تأثیر مستقیمی بر عملکرد و پایداری پایگاه داده دارند. تنظیم صحیح این پارامترها می‌تواند به بهبود کارایی، کاهش بار I/O و افزایش قابلیت بازیابی کمک کند.

۱. پارامتر checkpoint_timeout

۱.۱ تعریف

پارامتر checkpoint_timeout مشخص می‌کند که چقدر طول بکشد تا یک Checkpoint بعدی رخ دهد. اگر فواصل Checkpoint کوتاه باشد، بار I/O به دیسک افزایش می‌یابد و ممکن است عملکرد بهبود نیابد. اگر فاصله Checkpoint بیش از حد طولانی باشد، ممکن است بازیابی داده‌ها پس از خرابی به تأخیر بیفتد.

مقدار پیشنهادی

مقدار استاندارد برای این پارامتر ۵-۱۵ دقیقه است. در پایگاه‌های بزرگ یا حجیم، مقادیر ۱۰-۳۰ دقیقه می‌تواند مناسب‌تر باشد.

نمونه تنظیم در فایل postgresql.conf

checkpoint_timeout = 10min

اثر تنظیم

• مقدار کوچک‌تر: تعداد Checkpoint بیشتر می‌شود، که ممکن است باعث افزایش بار I/O شود.
• مقدار بزرگ‌تر: تعداد Checkpoint کاهش یافته و احتمال تأخیر در بازیابی کم‌تر می‌شود.

۲. پارامتر max_wal_size

۲.۱ تعریف

پارامتر max_wal_size مشخص می‌کند که PostgreSQL چقدر فایل‌های WAL را نگهداری کند. مقدار بالاتر باعث می‌شود فایل‌های WAL ذخیره شوند و کم‌تر نیاز به ایجاد Checkpoint باشد. در نتیجه، حجم فایل‌های WAL مدیریت‌شده و در عین حال بازیابی سریع‌تر انجام می‌شود.

مقدار پیشنهادی

مقدار ۲ تا ۴ برابر حافظه RAM به عنوان مقدار اولیه توصیه می‌شود.

نمونه تنظیم در فایل postgresql.conf

max_wal_size = 2GB

اثر تنظیم

• مقدار کوچک‌تر: Checkpoint زودتر رخ می‌دهد و فایل‌های WAL سریع‌تر پر می‌شوند.
• مقدار بزرگ‌تر: به کاهش تعداد Checkpoint کمک می‌کند و در نتیجه I/O کمتر درگیر می‌شود.

۳. مانیتورینگ و بهینه‌سازی پارامترها

۳.۱ بررسی تنظیمات فعلی

SHOW checkpoint_timeout;
SHOW max_wal_size;

۳.۲ تحلیل و تنظیم

• فاصله Checkpoint کوتاه: باعث افزایش فشار I/O و کاهش عملکرد می‌شود.
• فاصله Checkpoint طولانی: باعث تأخیر در بازیابی داده‌ها می‌شود.

برای پایگاه‌های حجیم:

• مقدار checkpoint_timeout را به ۱۰-۳۰ دقیقه افزایش دهید.
• مقدار max_wal_size را به ۲ تا ۴ برابر RAM تنظیم کنید.

۳.۳ مانیتورینگ فایل‌های WAL

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0');

این کوئری به شما نشان می‌دهد که فایل‌های WAL چقدر سریع پر می‌شوند و چقدر زمان باقی مانده است تا فایل‌های قدیمی‌تر حذف شوند.

جمع‌بندی

تنظیم پارامترهای checkpoint_timeout و max_wal_size در PostgreSQL می‌تواند به بهبود عملکرد سیستم کمک کند. با تنظیم مناسب این پارامترها، می‌توانید از بروز مشکلات ناشی از فشار I/O جلوگیری کرده و در عین حال قابلیت بازیابی سریع‌تر داده‌ها را حفظ کنید.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”مدیریت زمان‌بندی Checkpoints برای پایگاه‌های داده پرترافیک” subtitle=”توضیحات کامل”]Checkpoints در PostgreSQL فرآیندهایی هستند که برای نگهداری ثبات پایگاه داده و کاهش بار I/O به صورت دوره‌ای انجام می‌شوند. پایگاه‌های داده با ترافیک سنگین نیاز به تنظیم دقیق Checkpoint دارند تا عملکرد بهینه را حفظ کنند و از مشکلات احتمالی در بازیابی داده‌ها جلوگیری کنند.

۱. پارامترهای مربوط به Checkpoints

دو پارامتر کلیدی برای مدیریت زمان‌بندی Checkpoints وجود دارند:

1. checkpoint_timeout
   مشخص می‌کند که چه مدت زمانی طول بکشد تا یک Checkpoint جدید رخ دهد.
   مقدار استاندارد: ۵-۱۵ دقیقه
   پایگاه‌های پرترافیک ممکن است نیاز به افزایش این مقدار داشته باشند.
2. checkpoint_completion_target
   مشخص می‌کند که Checkpoint چقدر طول بکشد تا به طور کامل تکمیل شود.
   مقدار استاندارد: ۰.5 (۵۰%)
   برای پایگاه‌های پرترافیک ممکن است نیاز به تنظیم مقادیر پایین‌تر (مثلاً ۰.3) داشته باشید تا مدت زمان Checkpoint کاهش یابد.

۲. تنظیمات پیشنهادی برای پایگاه‌های پرترافیک

1. checkpoint_timeout
   افزایش مقدار این پارامتر باعث کاهش تعداد Checkpoint‌های مکرر می‌شود.
   مقدار پیشنهادی: ۳۰ دقیقه یا بیشتر در پایگاه‌های پرترافیک.
2. checkpoint_completion_target
   کاهش مقدار این پارامتر به کاهش زمان Checkpoint کمک می‌کند، بدون اینکه تأثیر زیادی بر پایداری داشته باشد.
   مقدار پیشنهادی: ۰.3 یا کمتر در پایگاه‌های پرترافیک.

۳. نمونه تنظیمات در postgresql.conf

checkpoint_timeout = 30min
checkpoint_completion_target = 0.3

۴. مانیتورینگ Checkpoints و تنظیمات مربوط به آن

• بررسی تعداد Checkpoint‌ها و زمان آن‌ها

  SELECT checkpoint_time, checkpoint_write_time, checkpoint_sync_time, redo_size, dirty_page_size
  FROM pg_stat_bgwriter;

• مانیتور حجم فایل‌های WAL و تأخیر Checkpoint‌ها

  SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0');

• بررسی تنظیمات Checkpoints فعلی

  SHOW checkpoint_timeout;
  SHOW checkpoint_completion_target;

جمع‌بندی

مدیریت صحیح زمان‌بندی Checkpoints در پایگاه‌های پرترافیک می‌تواند تأثیر زیادی بر کاهش بار I/O و بهبود عملکرد پایگاه داده داشته باشد. تنظیم پارامترهایی مانند checkpoint_timeout و checkpoint_completion_target با توجه به نیازهای سیستم می‌تواند به پایداری و کارایی سیستم کمک کند.[/cdb_course_lesson][cdb_course_lesson title=”فصل 8: مدیریت و مانیتورینگ لاگ‌ها”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”پیکربندی Logging برای ثبت اطلاعات عملکردی” subtitle=”توضیحات کامل”]یکی از روش‌های حیاتی برای تحلیل و بهینه‌سازی عملکرد پایگاه داده PostgreSQL، فعال‌سازی Logging و پیکربندی مناسب آن است. این لاگ‌ها می‌توانند اطلاعات ارزشمندی درباره فعالیت‌های سیستم، پرس‌وجوها، Deadlocks، Locks، و سایر مسائل عملکردی ارائه دهند.

logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000  # لاگ‌گیری برای پرس‌وجوهای بیشتر از ۱ ثانیه
log_statement = 'all'

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تحلیل لاگ‌های PostgreSQL برای شناسایی گلوگاه‌ها” subtitle=”توضیحات کامل”]لاگ‌های PostgreSQL می‌توانند اطلاعات ارزشمندی درباره عملکرد سیستم و مشکلات احتمالی ارائه دهند. با تحلیل دقیق این لاگ‌ها، می‌توانید گلوگاه‌ها و مسائل مرتبط با عملکرد را شناسایی کرده و به بهبود عملکرد پایگاه داده کمک کنید.

۱. انواع لاگ‌های PostgreSQL

1. log_statement: ثبت کلیه پرس‌وجوها (برای تحلیل پرس‌وجوهای پرهزینه و گلوگاه‌ها).
2. log_min_duration_statement: فقط پرس‌وجوهایی که مدت زمان آنها از مقدار مشخص شده بیشتر است ثبت می‌شوند.
3. deadlocks: ثبت Deadlockها برای شناسایی گلوگاه‌های همزمانی.

۲. ابزارهای تحلیل لاگ‌های PostgreSQL

1. pg_stat_statements: این ابزار به شما امکان می‌دهد پرس‌وجوهای سنگین و گلوگاه‌ها را شناسایی کنید.
   • فعال‌سازی pg_stat_statements:
     ابتدا در postgresql.conf تنظیمات زیر را اعمال کنید:

     shared_preload_libraries = 'pg_stat_statements'
     track_statements = all  # همه‌ی پرس‌وجوها را دنبال کنید

   • پرس‌وجو برای تحلیل:

     SELECT query, calls, total_time, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

2. pgBadger: یک ابزار قدرتمند برای تجزیه و تحلیل لاگ‌ها. این ابزار داده‌های لاگ را پردازش می‌کند و گزارش‌های کاملی از عملکرد پایگاه داده و گلوگاه‌ها ارائه می‌دهد.

   pgbadger /var/log/postgresql/postgresql-*.log

3. pg_locks و pg_stat_activity: برای شناسایی Deadlocks و Locks.

   SELECT * FROM pg_stat_activity WHERE state = 'locked';

۳. شناسایی گلوگاه‌ها در لاگ‌ها

1. پرس‌وجوهای طولانی و کند:بررسی پرس‌وجوهایی که زمان اجرا طولانی دارند:

   SELECT query, calls, total_time FROM pg_stat_statements WHERE total_time > 1000;

2. گلوگاه‌های Deadlock:شناسایی و بررسی Deadlockها برای رفع مشکلات همزمانی:

   SELECT * FROM pg_stat_activity WHERE state = 'dead';

3. صفوف (Locks):بررسی صف‌های قفل‌ها و شناسایی صف‌های طولانی:

   SELECT * FROM pg_locks;

۴. رفع گلوگاه‌ها

1. بهینه‌سازی پرس‌وجوها:
   • استفاده از ایندکس‌ها برای بهبود پرس‌وجوها.
   • بررسی طرح پرس‌وجو (EXPLAIN) و اصلاح آن.
2. مدیریت همزمانی:
   • تنظیم پارامترهای lock_timeout و deadlock_timeout برای کاهش تأخیرهای مرتبط با Deadlocks.

   lock_timeout = 5s
   deadlock_timeout = 1s

3. پیشگیری از Deadlocks:
   • استفاده از مکانیزم‌های Locking بهینه‌تر مانند “Row-Level Locking” و “در نظر گرفتن قفل‌های محدود”.

جمع‌بندی

تحلیل لاگ‌های PostgreSQL برای شناسایی گلوگاه‌ها نقش بسیار مهمی در بهینه‌سازی عملکرد پایگاه داده دارد. استفاده از ابزارهایی مانند pg_stat_statements و pgBadger به شما کمک می‌کند تا مشکلات مربوط به عملکرد و Deadlocks را شناسایی کرده و راهکارهایی برای بهبود آنها بیابید.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”استفاده از ابزارهایی مانند pgBadger برای تحلیل لاگ‌ها” subtitle=”توضیحات کامل”]ابزار pgBadger یکی از بهترین و قدرتمندترین ابزارهای تحلیل لاگ‌های PostgreSQL است. این ابزار به شما کمک می‌کند تا عملکرد پایگاه داده را با تجزیه و تحلیل دقیق لاگ‌ها ارزیابی کنید و گلوگاه‌های احتمالی را شناسایی کنید. در ادامه، به جزئیات نصب، پیکربندی و استفاده از این ابزار پرداخته می‌شود.

۱. ویژگی‌های کلیدی pgBadger

• تجزیه و تحلیل کامل لاگ‌های PostgreSQL: شامل پرس‌وجوهای کند، Deadlocks، Locks و موارد دیگر.
• گزارش‌های گرافیکی و تعاملی: ارائه گزارش‌های HTML با نمودارها و آمار قابل فهم.
• پشتیبانی از فایل‌های لاگ فشرده: امکان پردازش فایل‌های فشرده‌شده با فرمت‌های gzip یا bzip2.
• سازگاری با فرمت‌های مختلف لاگ PostgreSQL.

۲. پیش‌نیازها

1. PostgreSQL: لاگ‌برداری باید در PostgreSQL فعال باشد.
2. Perl: pgBadger بر اساس زبان Perl نوشته شده است. برای اجرای آن باید Perl نصب باشد.
3. ابزارهای اختیاری: برای ایجاد گزارش‌های گرافیکی، ممکن است نیاز به ابزارهایی مانند gnuplot داشته باشید.

۳. تنظیم PostgreSQL برای لاگ‌برداری

قبل از استفاده از pgBadger، باید لاگ‌برداری مناسب را در PostgreSQL فعال کنید. فایل پیکربندی postgresql.conf را به شکل زیر تنظیم کنید:

logging_collector = on
log_directory = 'log'          # مسیر ذخیره لاگ‌ها
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = 1000  # ثبت پرس‌وجوهایی که بیش از ۱۰۰۰ میلی‌ثانیه طول می‌کشند
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '  # پیشوند لاگ‌ها

سپس سرویس PostgreSQL را مجدداً راه‌اندازی کنید:

sudo systemctl restart postgresql

۴. نصب pgBadger

روش ۱: نصب از طریق پکیج‌ منیجر (در برخی توزیع‌ها)

sudo apt install pgbadger  # برای Debian/Ubuntu
sudo yum install pgbadger  # برای CentOS/RHEL

روش ۲: نصب دستی

1. دانلود pgBadger از GitHub:

   wget https://github.com/darold/pgbadger/archive/refs/heads/master.zip
   unzip master.zip
   cd pgbadger-master

2. اجرا بدون نیاز به نصب:

   perl pgbadger

۵. استفاده از pgBadger برای تحلیل لاگ‌ها

برای تحلیل لاگ‌ها، دستور زیر را اجرا کنید:

pgbadger /path/to/logfile.log -o report.html

• /path/to/logfile.log: مسیر فایل لاگ PostgreSQL.
• -o report.html: نام فایل خروجی گزارش.

تحلیل چندین فایل لاگ:

pgbadger /path/to/logs/*.log -o report.html

تحلیل فایل‌های لاگ فشرده:

pgbadger /path/to/logs/*.log.gz -o report.html

۶. ویژگی‌های خروجی pgBadger

گزارش تولیدشده شامل اطلاعات زیر است:

• پرس‌وجوهای کند: نمایش پرس‌وجوهایی که زمان زیادی صرف می‌کنند.
• Deadlocks و Locks: شناسایی مشکلات همزمانی.
• نمودارهای عملکردی: ارائه اطلاعات گرافیکی در مورد بار کاری و استفاده از منابع.
• فعالیت کاربران و برنامه‌ها: تحلیل عملکرد کاربران مختلف.

۷. پیکربندی‌های پیشرفته

• تنظیم تعداد پردازنده‌ها برای پردازش سریع‌تر:

  pgbadger --jobs 4 /path/to/logs/*.log -o report.html

• تحلیل بازه زمانی خاص:

  pgbadger --begin "2025-01-01 00:00:00" --end "2025-01-02 23:59:59" /path/to/logs/*.log -o report.html

جمع‌بندی

ابزار pgBadger ابزاری قدرتمند و انعطاف‌پذیر برای تحلیل لاگ‌های PostgreSQL است. این ابزار با ارائه گزارش‌های دقیق و گرافیکی، شناسایی و رفع گلوگاه‌ها را تسهیل می‌کند. با فعال‌سازی مناسب لاگ‌برداری و تحلیل آن با pgBadger، می‌توانید عملکرد پایگاه داده را به طور قابل توجهی بهبود دهید.[/cdb_course_lesson][cdb_course_lesson title=”فصل 9: بهینه‌سازی و تنظیم WAL”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” title=”نقش Write-Ahead Logging (WAL) در عملکرد سیستم” subtitle=”توضیحات کامل”]Write-Ahead Logging (WAL) یکی از مکانیزم‌های کلیدی PostgreSQL برای تضمین پایداری داده‌ها و بازیابی در برابر خرابی‌ها است. این مکانیزم به PostgreSQL کمک می‌کند تا عملیات نوشتن بر روی پایگاه داده را به شکلی ایمن و بهینه مدیریت کند، در حالی که تأثیر مثبتی بر عملکرد کلی سیستم دارد.

۱. Write-Ahead Logging (WAL) چیست؟

WAL یک فرآیند لاگ‌برداری است که قبل از اعمال تغییرات به پایگاه داده، ابتدا این تغییرات را در یک فایل لاگ (WAL فایل) ثبت می‌کند. این لاگ‌ها اطلاعاتی در مورد تغییرات انجام‌شده روی داده‌ها یا متادیتای پایگاه داده ذخیره می‌کنند.

۲. اهداف اصلی WAL

1. اطمینان از دوام داده‌ها:
   • در صورت وقوع خرابی (مانند قطع برق یا خرابی سخت‌افزار)، WAL تضمین می‌کند که داده‌ها از دست نمی‌روند. پس از بازیابی سیستم، داده‌ها از روی لاگ‌ها بازسازی می‌شوند.
2. افزایش عملکرد سیستم:
   • با نوشتن تغییرات ابتدا در WAL و سپس اعمال آنها در پایگاه داده، PostgreSQL عملیات نوشتن را به شکل بهینه مدیریت می‌کند.
3. پشتیبانی از بازیابی نقطه‌ای (Point-in-Time Recovery):
   • با استفاده از WAL، می‌توان پایگاه داده را به وضعیت یک نقطه خاص در گذشته بازگرداند.

۳. چگونه WAL کار می‌کند؟

WAL شامل مراحل زیر است:

1. ثبت تغییرات در WAL:
   • هنگام انجام عملیات نوشتن یا تغییر در داده‌ها، ابتدا تغییرات در فایل WAL ذخیره می‌شوند. این عملیات سبک‌تر از نوشتن مستقیم روی پایگاه داده است، زیرا فایل WAL به صورت ترتیبی نوشته می‌شود.
2. تأیید عملیات (fsync):
   • پس از نوشتن تغییرات در WAL، فایل لاگ بر روی دیسک فلش می‌شود تا از ذخیره‌سازی مطمئن اطمینان حاصل شود.
3. اعمال تغییرات به پایگاه داده:
   • داده‌ها در زمان‌های مشخص یا هنگام بار کم‌تر، از WAL به پایگاه داده اصلی اعمال می‌شوند.

۴. نقش WAL در بهبود عملکرد

1. کاهش عملیات نوشتن تصادفی:
   • نوشتن داده‌ها در WAL به صورت ترتیبی انجام می‌شود که نسبت به نوشتن مستقیم روی دیسک (به صورت تصادفی) سریع‌تر است.
2. امکان مدیریت همزمانی بهتر:
   • از آنجایی که تغییرات ابتدا در WAL ثبت می‌شوند، کاربران می‌توانند بدون تأخیر، نتایج عملیات خود را مشاهده کنند.
3. بهینه‌سازی بازنویسی صفحات:
   • PostgreSQL نیازی به اعمال فوری تغییرات در داده‌های اصلی ندارد، بلکه این تغییرات را در زمان‌های بهینه (مانند Checkpoints) اعمال می‌کند.

۵. پارامترهای مرتبط با WAL در PostgreSQL

برای بهبود عملکرد WAL و پیکربندی بهتر آن، پارامترهای زیر در فایل postgresql.conf قابل تنظیم هستند:

1. wal_level:
   • تعیین می‌کند چه مقدار اطلاعات در WAL ثبت شود. مقادیر ممکن:
     • minimal: برای محیط‌های تک‌کاربره.
     • replica: برای پشتیبانی از بازپخش لاگ‌ها.
     • logical: برای کاربردهای پیشرفته مانند replication منطقی.
2. wal_buffers:
   • مقدار حافظه‌ای که برای بافر کردن WAL در RAM اختصاص داده می‌شود.
3. checkpoint_timeout:
   • فاصله زمانی بین Checkpoints که زمان اعمال تغییرات WAL به پایگاه داده را مشخص می‌کند.
4. max_wal_size و min_wal_size:
   • اندازه حداقل و حداکثر فایل‌های WAL روی دیسک.
5. synchronous_commit:
   • تعیین می‌کند که آیا منتظر نوشتن تغییرات در دیسک بماند یا نه.

۶. مزایا و محدودیت‌های WAL

مزایا:

• امنیت داده‌ها: تضمین می‌کند که داده‌ها در صورت خرابی سیستم از بین نمی‌روند.
• بهبود عملکرد: کاهش سربار نوشتن مستقیم روی دیسک.
• پشتیبانی از بازیابی: امکان بازگردانی به وضعیت گذشته.

محدودیت‌ها:

• افزایش مصرف فضای دیسک: فایل‌های WAL فضای بیشتری اشغال می‌کنند.
• پیچیدگی پیکربندی: نیاز به تنظیم مناسب پارامترها برای دستیابی به تعادل بین عملکرد و پایداری.

جمع‌بندی

Write-Ahead Logging (WAL) یکی از اجزای کلیدی PostgreSQL است که تأثیر زیادی بر عملکرد و قابلیت اطمینان سیستم دارد. با مکانیزم WAL، تغییرات ابتدا در فایل لاگ ثبت می‌شوند و سپس به پایگاه داده اعمال می‌گردند. این روش نه تنها پایداری داده‌ها را تضمین می‌کند، بلکه با بهینه‌سازی عملیات نوشتن، باعث افزایش کارایی سیستم می‌شود. تنظیم دقیق پارامترهای مرتبط با WAL می‌تواند به بهبود بیشتر عملکرد پایگاه داده کمک کند.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تنظیم پارامترهای WAL مانند wal_buffers و wal_writer_delay” subtitle=”توضیحات کامل”]بهینه‌سازی پارامترهای مرتبط با Write-Ahead Logging (WAL)، از جمله wal_buffers و wal_writer_delay، می‌تواند به طور قابل‌توجهی عملکرد PostgreSQL را در مدیریت عملیات نوشتن و بازیابی داده‌ها بهبود دهد. این تنظیمات بر نحوه مدیریت حافظه و زمان‌بندی نوشتن لاگ‌ها به دیسک تأثیر می‌گذارند.

۱. پارامتر wal_buffers

wal_buffers مقدار حافظه‌ای را مشخص می‌کند که PostgreSQL برای بافر کردن داده‌های WAL در RAM استفاده می‌کند. این پارامتر بر نحوه عملکرد WAL هنگام نوشتن به دیسک تأثیر مستقیم دارد.

نکات مهم:

• مقدار پیش‌فرض:
  • ۳۲ کیلوبایت یا ۳۲ KB، که در نسخه‌های جدید PostgreSQL به طور خودکار مقدار مناسبی تعیین می‌شود.
• کاربرد:
  • داده‌های WAL ابتدا در این بافر ذخیره می‌شوند و سپس به دیسک نوشته می‌شوند. اگر این مقدار بسیار کوچک باشد، منجر به نوشتن مکرر به دیسک و کاهش عملکرد می‌شود.
• اندازه توصیه‌شده:
  • برای پایگاه‌های داده پرترافیک، مقدار ۱۶ مگابایت (16MB) یا بیشتر معمولاً مناسب است.
  • اگر از حجم بالای نوشتن داده استفاده می‌کنید، افزایش این مقدار می‌تواند سربار دیسک را کاهش دهد.

تنظیم wal_buffers:

در فایل postgresql.conf مقدار زیر را تنظیم کنید:

wal_buffers = 16MB

نکته:

تأثیر این پارامتر هنگام وقوع نوشتن‌های پرتکرار (Write-Intensive Workloads) بیشتر احساس می‌شود.

۲. پارامتر wal_writer_delay

wal_writer_delay فاصله زمانی بین دو عمل نوشتن داده‌های WAL توسط فرآیند wal_writer به دیسک را تعیین می‌کند. این پارامتر به بهینه‌سازی مصرف منابع و جلوگیری از نوشتن مکرر کمک می‌کند.

نکات مهم:

• مقدار پیش‌فرض:
  • ۲۰۰ میلی‌ثانیه (200ms)
• کاربرد:
  • تأخیر بین عملیات نوشتن WAL به دیسک را تعیین می‌کند. افزایش این مقدار می‌تواند سربار پردازشی را کاهش دهد اما ممکن است زمان پاسخ را برای نوشتن داده‌ها افزایش دهد.
• تنظیم توصیه‌شده:
  • برای سیستم‌های پرترافیک، کاهش این مقدار به حدود ۵۰ms ممکن است کارایی را بهبود بخشد.
  • در سیستم‌های کم‌ترافیک یا دارای دیسک سریع، مقدار پیش‌فرض کافی است.

تنظیم wal_writer_delay:

در فایل postgresql.conf مقدار زیر را تنظیم کنید:

wal_writer_delay = 50ms

نکته:

تنظیم این پارامتر باید با توجه به عملکرد دیسک و نیازهای سیستم انجام شود.

۳. چگونه این پارامترها را آزمایش کنیم؟

1. تغییر پارامترها:
   • فایل postgresql.conf را ویرایش کنید.
   • برای اعمال تغییرات، سرویس PostgreSQL را مجدداً راه‌اندازی کنید:

     sudo systemctl restart postgresql

2. مانیتورینگ عملکرد:
   • از ابزارهای نظارتی مانند pg_stat_activity، pg_stat_bgwriter یا ابزارهای خارجی مانند pgBadger برای مشاهده عملکرد استفاده کنید.
   • ابزار iostat یا sar را برای بررسی I/O دیسک به کار بگیرید.
3. آزمون بار:
   • بار کاری سنگین (Write-Heavy Workloads) را شبیه‌سازی کنید و تأثیر تغییرات را اندازه‌گیری کنید.

جمع‌بندی

تنظیم دقیق wal_buffers و wal_writer_delay می‌تواند تأثیر مثبتی بر عملکرد PostgreSQL داشته باشد، خصوصاً در سیستم‌هایی با بار نوشتن زیاد یا دیسک‌های کند. افزایش مقدار wal_buffers می‌تواند از نوشتن مکرر جلوگیری کند و تنظیم بهینه wal_writer_delay سربار پردازشی را کاهش می‌دهد. برای دستیابی به بهترین نتیجه، این مقادیر باید بر اساس نیازهای خاص سیستم شما و پس از آزمایش دقیق تنظیم شوند.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”کاهش تأخیر نوشتن و افزایش کارایی” subtitle=”توضیحات کامل”]کاهش تأخیر نوشتن (Write Latency) و افزایش کارایی (Performance) برای پایگاه‌های داده پرترافیک و برنامه‌هایی با حجم بالای نوشتن داده، اهمیت بالایی دارد. برای دستیابی به این هدف، می‌توان از تکنیک‌ها و تنظیمات زیر استفاده کرد:

۱. استفاده از Write-Ahead Logging (WAL) بهینه

• تنظیم wal_buffers:
  • افزایش مقدار wal_buffers باعث کاهش دفعات نوشتن به دیسک و افزایش کارایی می‌شود.
  • مقدار پیشنهادی: ۱۶MB یا بیشتر برای بارهای نوشتاری سنگین.
• تنظیم wal_writer_delay:
  • کاهش تأخیر بین نوشتن‌های WAL، زمان‌بندی بهتری ایجاد می‌کند.
  • مقدار پیشنهادی: ۵۰ms برای سیستم‌های پرترافیک.

۲. استفاده از حافظه بهینه

• work_mem:
  • افزایش مقدار work_mem برای بهینه‌سازی عملیات مرتب‌سازی (Sorts) و Join‌ها.
  • مقدار پیشنهادی: ۱۶MB یا بیشتر برای هر اتصال یا مرتب‌سازی.

  work_mem = 16MB

• maintenance_work_mem:
  • افزایش این مقدار برای تسریع عملیات Autovacuum و ایندکس‌سازی.
  • مقدار پیشنهادی: ۱۲۸MB یا بیشتر.

  maintenance_work_mem = 128MB

۳. استفاده از ایندکس‌ها (Indices)

• ایندکس‌های مناسب مانند B-Tree، GIN یا BRIN، عملکرد پرس‌وجوها و عملیات نوشتاری را بهبود می‌بخشند.
• مطمئن شوید که ایندکس‌ها با الگوی دسترسی داده‌ها هماهنگ هستند.

  CREATE INDEX idx_column ON table_name (column_name);

۴. بهینه‌سازی I/O دیسک

• استفاده از دیسک‌های SSD:
  • دیسک‌های SSD سرعت عملیات نوشتن را به طور چشمگیری افزایش می‌دهند.
• تنظیم پارامتر synchronous_commit:
  • تنظیم این پارامتر به حالت off برای عملیات نوشتن غیر بحرانی، تأخیر را کاهش می‌دهد.

  synchronous_commit = off

  هشدار: این تنظیم ممکن است موجب از دست رفتن داده‌ها در صورت بروز خطا شود. تنها برای داده‌های غیرحیاتی استفاده شود.

۵. تنظیم پارامترهای Checkpoint

• افزایش مقادیر checkpoint_timeout و max_wal_size باعث کاهش تعداد Checkpoint‌ها و تأخیر مرتبط با آنها می‌شود.

  checkpoint_timeout = 15min
  max_wal_size = 1GB

۶. استفاده از Parallel Query Execution

• فعال‌سازی Parallel Queries برای بهبود عملکرد پردازش پرس‌وجوهای پیچیده.

  max_parallel_workers_per_gather = 4

۷. پیکربندی کش سیستم

• تنظیم effective_cache_size برای بهره‌برداری بهتر از حافظه RAM:

  effective_cache_size = 4GB

۸. مانیتورینگ و شناسایی گلوگاه‌ها

• از ابزارهای داخلی مانند pg_stat_activity و pg_stat_statements برای شناسایی پرس‌وجوهای کند استفاده کنید.
• ابزارهای خارجی مانند pgBadger و pg_top می‌توانند لاگ‌ها و فعالیت‌های سیستم را تحلیل کنند.

۹. استفاده از Batch Insert

• برای وارد کردن حجم زیادی از داده‌ها، از Batch Insert استفاده کنید تا تعداد تراکنش‌ها کاهش یابد:

  INSERT INTO table_name (col1, col2) VALUES 
  (val1, val2), 
  (val3, val4), 
  (val5, val6);

۱۰. بهینه‌سازی Queries

• از ابزار EXPLAIN و EXPLAIN ANALYZE برای تحلیل Query Plans و شناسایی گلوگاه‌ها استفاده کنید.

  EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

جمع‌بندی

برای کاهش تأخیر نوشتن و افزایش کارایی در PostgreSQL، استفاده از ترکیبی از تنظیمات بهینه، مدیریت حافظه، بهینه‌سازی I/O دیسک و مانیتورینگ مداوم ضروری است. این اقدامات با شناسایی و رفع گلوگاه‌ها و استفاده از منابع سخت‌افزاری و نرم‌افزاری بهینه، عملکرد پایگاه داده را به طور چشمگیری بهبود می‌بخشند.[/cdb_course_lesson][cdb_course_lesson title=”فصل 10: پشتیبان‌گیری و بازیابی عملکردی”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”استفاده از Physical و Logical Backups برای حفظ عملکرد” subtitle=”توضیحات کامل”]حفظ عملکرد پایگاه داده در کنار ایجاد پشتیبان‌های قابل اطمینان، یکی از وظایف کلیدی مدیران پایگاه داده است. PostgreSQL از دو نوع اصلی پشتیبان‌گیری پشتیبانی می‌کند: Physical Backups و Logical Backups. هر کدام از این روش‌ها ویژگی‌های خاص خود را دارند و بسته به نیاز، در شرایط مختلف به کار می‌روند.

۱. پشتیبان‌گیری فیزیکی (Physical Backups)

پشتیبان‌گیری فیزیکی شامل کپی‌برداری از فایل‌های داده، WAL (Write-Ahead Logs) و تنظیمات پایگاه داده است. این روش برای پشتیبان‌گیری کامل و بازیابی سریع استفاده می‌شود.

ابزارهای مرتبط

• pg_basebackup:
  ابزار استاندارد PostgreSQL برای ایجاد Physical Backup.

  pg_basebackup -D /backup/location -F tar -z -P -X stream

  • -D: مسیر ذخیره‌سازی بکاپ.
  • -F: فرمت (tar یا plain).
  • -z: فشرده‌سازی.
  • -X stream: شامل WAL در بکاپ.
• rsync:
  برای کپی فایل‌های داده از یک سرور به سرور دیگر، مناسب برای پایگاه‌های داده بزرگ.

  rsync -av --progress /data/location /backup/location

ویژگی‌ها

• مناسب برای حجم بالای داده.
• امکان بازیابی سریع‌تر در مقایسه با Logical Backups.
• امکان استفاده از Point-In-Time Recovery (PITR) برای بازگرداندن پایگاه داده به یک لحظه خاص.

معایب

• نیاز به توقف سرور (در صورت استفاده از rsync بدون Streaming WAL).
• اندازه بزرگ‌تر بکاپ.

۲. پشتیبان‌گیری منطقی (Logical Backups)

پشتیبان‌گیری منطقی شامل استخراج داده‌ها و ساختار جداول به صورت متنی است. این روش برای انتقال داده‌ها بین نسخه‌های مختلف PostgreSQL یا مهاجرت به سرورهای جدید استفاده می‌شود.

ابزارهای مرتبط

• pg_dump:
  ابزار اصلی برای ایجاد Logical Backup از یک پایگاه داده.

  pg_dump -U username -F c -b -v -f /backup/location/dbname.backup dbname

  • -F c: فرمت Custom.
  • -b: شامل Binary Large Objects (BLOBs).
  • -v: حالت verbose.
• pg_dumpall:
  برای پشتیبان‌گیری از کل سرور.

  pg_dumpall -U username > /backup/location/all_dbs.sql

ویژگی‌ها

• امکان انتقال بین نسخه‌های مختلف PostgreSQL.
• قابلیت خواندن و ویرایش فایل‌های پشتیبان.
• مناسب برای داده‌های کوچک و متوسط.

معایب

• زمان‌برتر برای پشتیبان‌گیری و بازیابی.
• حجم بیشتر فایل‌ها نسبت به Physical Backup (بدون فشرده‌سازی).

۳. استراتژی ترکیبی

برای حفظ عملکرد و دسترسی بالا، می‌توان از یک استراتژی ترکیبی استفاده کرد:

• پشتیبان‌گیری فیزیکی:
  برای بازیابی سریع و استفاده از PITR.
• پشتیبان‌گیری منطقی:
  برای انتقال داده‌ها و محافظت در برابر فساد داده‌ها (Data Corruption).

۴. بهینه‌سازی فرآیند پشتیبان‌گیری

زمان‌بندی پشتیبان‌ها

• برای پایگاه‌های داده پرترافیک، از Scheduled Backups در زمان‌های کم‌ترافیک استفاده کنید.

  crontab -e
  0 2 * * * /usr/bin/pg_basebackup -D /backup/location -F tar -z -P -X stream

فشرده‌سازی و رمزنگاری

• استفاده از ابزارهای فشرده‌سازی مانند gzip یا zstd:

  pg_dump dbname | gzip > /backup/location/dbname.sql.gz

• رمزنگاری پشتیبان‌ها برای افزایش امنیت:

  gpg --encrypt --recipient user@example.com dbname.sql.gz

مانیتورینگ عملکرد پشتیبان‌گیری

• استفاده از ابزارهای مانیتورینگ مانند Zabbix یا Prometheus برای نظارت بر زمان و فضای مصرفی پشتیبان‌ها.

جمع‌بندی

پشتیبان‌گیری منظم و مناسب، نه تنها از داده‌ها محافظت می‌کند، بلکه از افت عملکرد ناشی از بازیابی‌های ناموفق جلوگیری می‌کند. انتخاب بین Physical و Logical Backup بستگی به نیازهای خاص شما دارد. ترکیب این دو روش و بهینه‌سازی فرآیند پشتیبان‌گیری، تضمینی برای حفظ کارایی و امنیت پایگاه داده PostgreSQL شماست.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”پیکربندی archive_mode و archive_command برای Continuous Archiving” subtitle=”توضیحات کامل”]Continuous Archiving در PostgreSQL یکی از روش‌های پیشرفته برای محافظت از داده‌ها و اجرای Point-In-Time Recovery (PITR) است. این روش مبتنی بر انتقال و ذخیره‌ی مداوم فایل‌های WAL (Write-Ahead Logs) در یک محل آرشیو خارجی است.

۱. فعال‌سازی Continuous Archiving

برای فعال‌سازی Continuous Archiving، باید دو پارامتر اصلی پیکربندی شوند:

• archive_mode: فعال‌سازی قابلیت آرشیو.
• archive_command: دستور برای انتقال فایل‌های WAL به محل آرشیو.

۲. مراحل پیکربندی

۱. ویرایش فایل postgresql.conf

فایل تنظیمات PostgreSQL که معمولاً در مسیر /var/lib/pgsql/data/postgresql.conf یا مشابه آن قرار دارد، باید به‌روزرسانی شود.

تنظیمات اولیه

archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

• %p: مسیر کامل فایل WAL در دایرکتوری داده‌ها.
• %f: نام فایل WAL.

۲. ایجاد دایرکتوری آرشیو

مسیر مقصد برای ذخیره فایل‌های WAL باید ایجاد شده و مجوزهای مناسب داشته باشد:

mkdir -p /path/to/archive
chown postgres:postgres /path/to/archive
chmod 700 /path/to/archive

۳. اعمال تغییرات

پس از ویرایش فایل تنظیمات، سرویس PostgreSQL باید بازنشانی شود:

systemctl restart postgresql

۳. نمونه‌های رایج archive_command

۱. انتقال به محل لوکال

برای کپی فایل‌های WAL به یک دایرکتوری لوکال:

archive_command = 'cp %p /backup/wal_archive/%f'

۲. فشرده‌سازی فایل‌های WAL

برای ذخیره فضای دیسک:

archive_command = 'gzip < %p > /backup/wal_archive/%f.gz'

۳. انتقال به سرور ریموت با استفاده از rsync

برای ارسال فایل‌های WAL به یک سرور راه دور:

archive_command = 'rsync %p user@remote-server:/backup/wal_archive/%f'

۴. ذخیره در سیستم‌های ابری

با استفاده از ابزارهای CLI (مانند aws s3 برای ذخیره در S3):

SELECT * FROM pg_stat_archiver;

۴. مانیتورینگ و رفع مشکلات

بررسی وضعیت آرشیو

برای مشاهده وضعیت فرآیند آرشیو:

SELECT * FROM pg_stat_archiver;

خروجی شامل:

• archived_count: تعداد فایل‌های موفق آرشیو.
• failed_count: تعداد خطاهای آرشیو.
• last_archived_wal: آخرین فایل WAL آرشیو شده.
• last_failed_wal: آخرین فایل WAL با خطا.

رفع مشکلات آرشیو

• بررسی لاگ‌های PostgreSQL:

  tail -f /var/log/postgresql/postgresql.log

• اطمینان از دسترسی به مسیر آرشیو.
• بررسی فضای ذخیره‌سازی کافی.

۵. تست و شبیه‌سازی فرآیند

ایجاد تغییر برای تولید فایل‌های WAL جدید

یک تغییر کوچک در پایگاه داده ایجاد کنید:

CREATE TABLE test_table (id SERIAL PRIMARY KEY, data TEXT);
INSERT INTO test_table (data) VALUES ('test data');

تأیید آرشیو شدن فایل‌ها

بررسی کنید که فایل‌های WAL به مسیر آرشیو منتقل شده‌اند:

ls -l /path/to/archive

جمع‌بندی

فعال‌سازی و پیکربندی صحیح Continuous Archiving با استفاده از پارامترهای archive_mode و archive_command، یک لایه حیاتی از پشتیبان‌گیری و بازیابی برای PostgreSQL فراهم می‌کند. این روش برای پایگاه‌های داده پرترافیک و حساس ایده‌آل است و امکان بازیابی دقیق به یک نقطه زمانی مشخص را فراهم می‌سازد.
استفاده از دستورات مناسب و مانیتورینگ مداوم فرآیند آرشیو، تضمینی برای عملکرد صحیح این قابلیت است.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”بازیابی داده‌ها با کمترین Downtime” subtitle=”توضیحات کامل”]بازیابی داده‌ها در PostgreSQL بدون وقفه طولانی، به ویژه در محیط‌های پرترافیک و حساس، نیازمند یک استراتژی مناسب است. در این بخش، مراحل و روش‌هایی برای بازیابی داده‌ها با کمترین Downtime توضیح داده می‌شود.

۱. مقدمه‌ای بر استراتژی‌های بازیابی

برای کاهش Downtime، باید از تکنیک‌هایی استفاده کرد که به‌جای توقف کامل سرویس، بازیابی به صورت موازی یا تدریجی انجام شود. برخی از این تکنیک‌ها عبارتند از:

• بازیابی به کمک سرورهای استندبای (Streaming Replication)
• بازیابی در حالت Hot Standby
• استفاده از ابزارهای پشتیبان‌گیری افزایشی
• Point-In-Time Recovery (PITR)

۲. استفاده از Streaming Replication

شرح

در این روش، یک سرور استندبای به سرور اصلی متصل شده و تغییرات را به‌صورت همزمان (Streaming) دریافت می‌کند. در صورت بروز مشکل در سرور اصلی، سرور استندبای می‌تواند بلافاصله جایگزین شود.

مراحل راه‌اندازی Streaming Replication

1. پیکربندی سرور اصلی
   • فعال‌سازی Replication در فایل postgresql.conf:

     wal_level = replica
     max_wal_senders = 5
     archive_mode = on

   • تعریف دسترسی برای سرور استندبای در فایل pg_hba.conf:

     host replication repl_user 192.168.1.0/24 md5

2. ایجاد پشتیبان از سرور اصلی با استفاده از ابزار pg_basebackup:

   pg_basebackup -h <primary_host> -D /path/to/standby_data -U repl_user -Fp -Xs -P

3. پیکربندی سرور استندبای
   • فایل recovery.conf یا تنظیمات مربوط به بازیابی:

     standby_mode = 'on'
     primary_conninfo = 'host=<primary_host> port=5432 user=repl_user password=<password>'

   • راه‌اندازی سرور استندبای:

     systemctl start postgresql

مزایا

• کاهش Downtime به چند ثانیه.
• انتقال خودکار به سرور استندبای.

۳. Point-In-Time Recovery (PITR)

شرح

PITR امکان بازیابی داده‌ها به یک نقطه زمانی خاص را فراهم می‌کند. این روش معمولاً با استفاده از Continuous Archiving انجام می‌شود.

مراحل اجرای PITR

1. تهیه پشتیبان اولیه
   • ایجاد یک نسخه پشتیبان پایه (Base Backup) از سرور اصلی:

     pg_basebackup -D /path/to/backup -F tar -X fetch -P

2. ذخیره فایل‌های WAL
   • فعال‌سازی archive_mode و انتقال فایل‌های WAL به مسیر آرشیو.
3. بازیابی داده‌ها
   • توقف سرور PostgreSQL:

     systemctl stop postgresql

   • بازگردانی نسخه پشتیبان:

     tar -xvf /path/to/backup/base.tar -C /var/lib/pgsql/data

   • تنظیم زمان بازیابی در فایل recovery.conf:

     restore_command = 'cp /path/to/archive/%f %p'
     recovery_target_time = '2025-01-05 12:00:00'

   • راه‌اندازی مجدد سرور:

     systemctl start postgresql

مزایا

• امکان بازیابی داده‌ها به یک نقطه زمانی مشخص.
• حداقل Downtime با پیش‌پیکربندی.

۴. بازیابی با ابزارهای پشتیبان‌گیری افزایشی

شرح

ابزارهایی مانند pgBackRest یا barman قابلیت پشتیبان‌گیری و بازیابی افزایشی را ارائه می‌دهند که زمان بازیابی را به شدت کاهش می‌دهند.

استفاده از pgBackRest

1. پشتیبان‌گیری

   pgbackrest --stanza=demo backup

2. بازیابی

   pgbackrest --stanza=demo restore --target-time="2025-01-05 12:00:00"

۵. استفاده از Load Balancer برای Downtime نزدیک به صفر

شرح

در محیط‌هایی با Replication، می‌توان از Load Balancer (مانند HAProxy یا Pgpool-II) استفاده کرد تا درخواست‌ها را به سرور استندبای هدایت کند.

مراحل راه‌اندازی

1. نصب HAProxy

   apt install haproxy

2. پیکربندی HAProxy فایل /etc/haproxy/haproxy.cfg:

   frontend pgsql_front
       bind *:5432
       mode tcp
       default_backend pgsql_back
   
   backend pgsql_back
       mode tcp
       balance roundrobin
       server primary 192.168.1.10:5432 check
       server standby 192.168.1.20:5432 check

3. راه‌اندازی HAProxy

   systemctl start haproxy

جمع‌بندی

برای بازیابی داده‌ها با کمترین Downtime در PostgreSQL، ترکیب روش‌هایی مانند Streaming Replication، PITR، و ابزارهای پیشرفته پشتیبان‌گیری، بهترین عملکرد را ارائه می‌دهند. همچنین استفاده از Load Balancer برای مدیریت انتقال ترافیک بین سرورها می‌تواند تجربه کاربران را بهبود بخشیده و وقفه‌ها را به حداقل برساند.[/cdb_course_lesson][cdb_course_lesson title=”فصل 11: استفاده از Extensions بهینه‌سازی”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”نصب و استفاده از افزونه‌هایی مانند pg_stat_statements و pg_repack” subtitle=”توضیحات کامل”]PostgreSQL افزونه‌های متعددی برای بهبود عملکرد، مدیریت و تحلیل پایگاه‌های داده ارائه می‌دهد. در این بخش، نصب و استفاده از دو افزونه مهم pg_stat_statements و pg_repack را بررسی می‌کنیم.

۱. نصب و استفاده از pg_stat_statements

معرفی pg_stat_statements

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

مراحل نصب و راه‌اندازی

1. اطمینان از نصب افزونه افزونه pg_stat_statements به‌صورت پیش‌فرض در بسته PostgreSQL موجود است. برای اطمینان، می‌توانید این دستور را اجرا کنید:

   SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

2. فعال‌سازی افزونه در فایل تنظیمات PostgreSQL (postgresql.conf):

   shared_preload_libraries = 'pg_stat_statements'

3. راه‌اندازی مجدد سرور PostgreSQL

   systemctl restart postgresql

4. ایجاد افزونه در پایگاه داده با اتصال به پایگاه داده موردنظر:

   CREATE EXTENSION pg_stat_statements;

استفاده از pg_stat_statements

برای مشاهده اطلاعات ثبت‌شده توسط افزونه:

SELECT
    query,
    calls,
    total_time,
    rows,
    shared_blks_read,
    shared_blks_hit
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

ویژگی‌ها

• Query Analysis: شناسایی کوئری‌های کند.
• Resource Usage: بررسی میزان مصرف منابع توسط هر کوئری.
• Optimization Insights: ارائه اطلاعات برای بهینه‌سازی عملکرد.

۲. نصب و استفاده از pg_repack

معرفی pg_repack

افزونه pg_repack برای بازسازی جداول و ایندکس‌ها بدون نیاز به قفل‌کردن (Lock) پایگاه داده استفاده می‌شود. این افزونه به بهینه‌سازی فضای دیسک و کاهش Fragmentation کمک می‌کند.

مراحل نصب و راه‌اندازی

1. نصب افزونه در سیستم‌های لینوکسی مانند Debian/Ubuntu:

   apt install postgresql-contrib postgresql-pgrepack

   در سیستم‌های RHEL/CentOS:

   yum install postgresql-contrib postgresql-pgrepack

2. ایجاد افزونه در پایگاه داده با اتصال به پایگاه داده موردنظر:

   CREATE EXTENSION pg_repack;

3. مجوزهای لازم کاربر موردنظر باید مجوزهای مناسب برای اجرای عملیات داشته باشد:

   GRANT USAGE ON SCHEMA pg_repack TO your_user;

استفاده از pg_repack

1. بازسازی جدول

   pg_repack -h localhost -U postgres -d your_database -t your_table

2. بازسازی کل پایگاه داده

   pg_repack -h localhost -U postgres -d your_database

ویژگی‌ها

• No Downtime Rebuilding: بازسازی جداول و ایندکس‌ها بدون ایجاد Downtime.
• Space Optimization: آزادسازی فضای دیسک.
• Performance Improvement: کاهش Fragmentation و افزایش کارایی.

مقایسه pg_stat_statements و pg_repack

جمع‌بندی

افزونه‌های pg_stat_statements و pg_repack ابزارهای ضروری برای مدیریت و بهینه‌سازی PostgreSQL هستند.

• pg_stat_statements برای تحلیل و بهینه‌سازی کوئری‌ها مناسب است.
• pg_repack برای بهبود فضای دیسک و عملکرد ساختارهای فیزیکی پایگاه داده بدون نیاز به Downtime استفاده می‌شود.
  ترکیب این دو افزونه می‌تواند به حفظ کارایی پایگاه داده و کاهش مشکلات عملکردی کمک کند.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” title=”نقش افزونه‌ها در بهینه‌سازی عملکرد پایگاه داده” subtitle=”توضیحات کامل”]

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” title=”ترکیب ابزارهای BI برای تحلیل داده‌ها” subtitle=”توضیحات کامل”]ابزارهای BI (Business Intelligence) به کسب‌وکارها کمک می‌کنند تا با تحلیل داده‌ها، تصمیم‌گیری‌های استراتژیک بهتری داشته باشند. PostgreSQL به‌عنوان یک پایگاه داده قدرتمند و متن‌باز، سازگاری بالایی با بسیاری از ابزارهای BI دارد. با ترکیب این ابزارها و قابلیت‌های PostgreSQL می‌توان به تحلیل‌های عمیق‌تر و مؤثرتری دست یافت.

مزایای ترکیب ابزارهای BI با PostgreSQL

1. نمایش تصویری داده‌ها
   ابزارهای BI امکان ایجاد داشبوردهای بصری و گزارش‌های تعاملی را فراهم می‌کنند که فهم داده‌ها را آسان‌تر می‌سازد.
2. تحلیل پیشرفته داده‌ها
   با استفاده از ابزارهایی مانند Power BI، Tableau یا Metabase می‌توان داده‌های PostgreSQL را با مدل‌سازی، پیش‌بینی، و تحلیل‌های چندبعدی بررسی کرد.
3. ادغام داده‌ها از منابع مختلف
   ابزارهای BI می‌توانند داده‌های PostgreSQL را با داده‌های سایر منابع (مانند فایل‌های Excel، Google Sheets، یا پایگاه‌های داده دیگر) ترکیب کنند.
4. بهینه‌سازی عملکرد کوئری‌ها
   ابزارهای BI با اجرای کوئری‌های بهینه و استفاده از قابلیت‌هایی مانند Materialized Views و Indices، به عملکرد سریع‌تر کمک می‌کنند.

ابزارهای BI محبوب برای PostgreSQL

1. Power BI

• ویژگی‌ها:
  • اتصال مستقیم به PostgreSQL.
  • ایجاد گزارش‌های پیشرفته و داشبوردهای تعاملی.
  • پشتیبانی از قابلیت‌های تحلیل پیش‌بینی و هوش مصنوعی.
• ترکیب با PostgreSQL:
  با استفاده از PostgreSQL ODBC Driver یا اتصال مستقیم، می‌توانید داده‌های پایگاه داده را وارد Power BI کنید.

2. Tableau

• ویژگی‌ها:
  • اتصال بلادرنگ به داده‌های PostgreSQL.
  • قابلیت‌های پیشرفته برای تجسم داده‌ها و کشف روندها.
  • پشتیبانی از داده‌های بزرگ.
• ترکیب با PostgreSQL:
  اتصال به Tableau از طریق رابط‌های JDBC یا ODBC امکان‌پذیر است.

[/cdb_course_lesson][cdb_course_lesson title=”فصل 12: پیکربندی محیط اجرا”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تنظیمات مناسب برای CPU و RAM” subtitle=”توضیحات کامل”]بهینه‌سازی تنظیمات مرتبط با CPU و RAM یکی از مهم‌ترین اقدامات برای افزایش عملکرد پایگاه داده PostgreSQL است. با پیکربندی صحیح پارامترها می‌توانید از منابع سخت‌افزاری موجود حداکثر بهره‌برداری را داشته باشید و عملکرد سیستم را بهینه کنید.

تنظیمات مربوط به CPU

1. Parallel Query Execution

• شرح:
  PostgreSQL از قابلیت اجرای موازی کوئری‌ها بهره می‌برد. این ویژگی امکان استفاده از چندین هسته CPU برای اجرای یک کوئری را فراهم می‌کند.
• پارامترهای کلیدی:
  • max_parallel_workers_per_gather:
    تعداد پردازشگرهای موازی که می‌توانند برای اجرای یک کوئری استفاده شوند. مقدار پیشنهادی بین 2 تا 4 برای بیشتر محیط‌ها است.

    SET max_parallel_workers_per_gather = 2;

  • parallel_setup_cost:
    هزینه نسبی برای تنظیم کوئری‌های موازی. کاهش مقدار این پارامتر باعث فعال شدن موازی‌سازی برای کوئری‌های کوچک‌تر می‌شود.

    SET parallel_setup_cost = 1000;

  • parallel_tuple_cost:
    هزینه نسبی پردازش هر Tuple در حالت موازی. مقدار پیش‌فرض معمولاً کافی است.

2. Max Worker Processes

• پارامتر:
  • max_worker_processes:
    تعداد کل پردازشگرهایی که PostgreSQL می‌تواند همزمان اجرا کند. این مقدار باید بر اساس تعداد هسته‌های CPU تنظیم شود.

    SET max_worker_processes = 8; -- تعداد هسته‌های CPU

3. Connection Pooling

• شرح:
  با محدود کردن تعداد اتصالات همزمان می‌توانید از فشار زیاد روی CPU جلوگیری کنید.
• ابزارهای پیشنهادی:
  • PgBouncer: برای مدیریت کارآمد اتصالات پایگاه داده.
  • PgPool-II: برای توزیع بار کوئری‌ها و مدیریت اتصالات.

تنظیمات مربوط به RAM

1. Shared Buffers

• پارامتر:
  • shared_buffers:
    این مقدار نشان‌دهنده فضایی از RAM است که برای کش کردن داده‌ها استفاده می‌شود.
    مقدار پیشنهادی معمولاً 25٪ از کل RAM سرور است.

    SET shared_buffers = '4GB';

2. Work Memory

• پارامتر:
  • work_mem:
    میزان RAM اختصاص داده شده به هر عملیات مرتب‌سازی یا هش در طول کوئری.
    این مقدار به ازای هر کوئری و هر عملیات مرتب‌سازی اعمال می‌شود.
    مقدار پیشنهادی برای سرورهای بزرگ بین 16MB تا 64MB است.

    SET work_mem = '32MB';

3. Maintenance Work Memory

• پارامتر:
  • maintenance_work_mem:
    حافظه‌ای که برای عملیات‌های تعمیر و نگهداری مانند Vacuum، Index Build و Analyze استفاده می‌شود.
    مقدار پیشنهادی برای سرورهای بزرگ 512MB تا 2GB است.

    SET maintenance_work_mem = '1GB';

4. Effective Cache Size

• پارامتر:
  • effective_cache_size:
    نشان‌دهنده میزان حافظه‌ای است که سیستم‌عامل برای کش کردن داده‌ها استفاده می‌کند.
    مقدار پیشنهادی معمولاً 50٪ تا 75٪ از کل RAM سرور است.

    SET effective_cache_size = '12GB';

بهینه‌سازی ترکیبی CPU و RAM

1. تنظیمات مناسب Autovacuum:
   عملیات Autovacuum نباید منابع CPU و RAM را اشباع کند. می‌توانید با تنظیم پارامترهای زیر عملکرد آن را بهینه کنید:
   • autovacuum_work_mem: حافظه مورد استفاده برای Autovacuum.
   • autovacuum_vacuum_cost_limit: محدود کردن مصرف CPU توسط Autovacuum.
2. توزیع بار کوئری‌ها:
   با استفاده از ابزارهای Load Balancing مانند PgPool-II، کوئری‌ها را به‌طور متعادل بین منابع سخت‌افزاری توزیع کنید.
3. ارتقای ایندکس‌ها:
   استفاده از B-Tree، GIN و BRIN Indexes می‌تواند به کاهش فشار بر CPU و RAM کمک کند.

جمع‌بندی

• تنظیمات مرتبط با CPU و RAM در PostgreSQL نقش حیاتی در بهبود عملکرد سیستم دارند.
• از پارامترهایی مانند shared_buffers، work_mem، و max_parallel_workers_per_gather برای تخصیص بهینه منابع استفاده کنید.
• برای محیط‌های بزرگ و پرترافیک، استفاده از ابزارهای Connection Pooling و Load Balancing توصیه می‌شود.
• تست و ارزیابی منظم عملکرد پس از اعمال تغییرات ضروری است تا مطمئن شوید که تنظیمات بهینه اعمال شده‌اند.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”پیکربندی Threading و Parallelism در PostgreSQL برای بارهای کاری سنگین” subtitle=”توضیحات کامل”]PostgreSQL به طور پیش‌فرض از مدل پردازشی فرآیند محور (Process-Based) استفاده می‌کند، به این معنا که هر اتصال به پایگاه داده یک فرآیند جداگانه ایجاد می‌کند. با این حال، ویژگی‌های مرتبط با Threading و Parallelism برای بهینه‌سازی بارهای کاری سنگین و استفاده حداکثری از منابع سخت‌افزاری (مانند CPU و RAM) وجود دارد. در این بخش، به پیکربندی Parallelism و استفاده بهینه از Threading برای بارهای سنگین می‌پردازیم.

Threading و Parallelism در PostgreSQL

PostgreSQL از Parallel Query Execution برای اجرای کوئری‌های سنگین با استفاده از چندین هسته CPU بهره می‌برد. این قابلیت برای عملیات‌هایی مانند Scan، Join، Aggregate و Sort مفید است.

1. فعال‌سازی Parallel Queries

Parallel Queries به PostgreSQL این امکان را می‌دهد که یک کوئری بزرگ را به بخش‌های کوچک‌تر تقسیم کرده و آنها را همزمان اجرا کند.

پارامترهای اصلی:

• max_parallel_workers_per_gather: حداکثر تعداد پردازشگرهای موازی برای یک عملیات Gather.
  مقدار پیشنهادی بین 2 تا 4 برای بیشتر بارهای کاری است.

  SET max_parallel_workers_per_gather = 4;

• parallel_setup_cost: هزینه نسبی برای آماده‌سازی Parallel Execution. مقدار پیش‌فرض این پارامتر 1000 است، اما کاهش آن می‌تواند Parallelism را حتی برای کوئری‌های کوچک فعال کند.

  SET parallel_setup_cost = 500;

• parallel_tuple_cost: هزینه نسبی انتقال Tuple‌ها در طول پردازش موازی. مقدار پیش‌فرض معمولاً کافی است، اما در صورت نیاز می‌توانید آن را کاهش دهید.

  SET parallel_tuple_cost = 0.1;

2. تنظیمات مرتبط با پردازشگرهای موازی (Workers)

• max_worker_processes: تعداد کل فرآیندهای کاری (Worker Processes) که PostgreSQL می‌تواند به صورت همزمان ایجاد کند.
  مقدار پیشنهادی برابر با تعداد هسته‌های CPU یا کمی بیشتر است.

  SET max_worker_processes = 16; -- برای سرور 16 هسته‌ای

• max_parallel_workers: تعداد کل پردازشگرهای موازی برای تمام کوئری‌های همزمان.
  این مقدار باید کمتر یا برابر با max_worker_processes باشد.

  SET max_parallel_workers = 8;

3. Parallelism برای اسکن جداول (Parallel Table Scan)

• parallel_leader_participation: تعیین می‌کند که آیا فرآیند اصلی (Leader) نیز در پردازش موازی شرکت کند. مقدار پیش‌فرض on است و معمولاً بهتر است تغییر نکند.

  SET parallel_leader_participation = on;

4. Parallelism در Joinها و Aggregateها

• Parallel Hash Join:
  برای بارهای کاری سنگین که شامل عملیات Join هستند، می‌توانید از Parallel Hash Join استفاده کنید. PostgreSQL به طور خودکار این ویژگی را فعال می‌کند، اما تنظیم مناسب پارامترهای work_mem و maintenance_work_mem ضروری است.
• Parallel Aggregate:
  برای اجرای Aggregateهای بزرگ (مانند SUM، COUNT و AVG) می‌توان از Parallel Aggregate بهره برد.

Threading در PostgreSQL

اگرچه PostgreSQL به صورت بومی از مدل Thread-Based استفاده نمی‌کند، ابزارها و تنظیمات زیر می‌توانند عملکرد آن را در شرایط بار سنگین بهبود بخشند:

1. Connection Pooling

مدیریت اتصالات با استفاده از ابزارهای Connection Pooling مانند PgBouncer یا PgPool-II می‌تواند به بهینه‌سازی مصرف منابع کمک کند.

2. ابزارهای Load Balancing

برای تقسیم بار کاری بین چندین سرور یا هسته CPU، می‌توانید از ابزارهایی مانند PgPool-II استفاده کنید که قابلیت Threading را شبیه‌سازی می‌کنند.

بهترین تنظیمات برای بارهای کاری سنگین

جمع‌بندی

• استفاده از Parallelism برای کوئری‌های سنگین می‌تواند تأثیر قابل‌توجهی در کاهش زمان اجرا داشته باشد.
• پیکربندی صحیح پارامترهایی مانند max_parallel_workers و max_worker_processes به حداکثر بهره‌وری از CPU کمک می‌کند.
• ابزارهای خارجی مانند PgBouncer و PgPool-II می‌توانند به مدیریت بهتر منابع کمک کنند.
• انجام تست‌های دوره‌ای برای ارزیابی تغییرات و تنظیم پارامترها بر اساس نوع بار کاری ضروری است.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”مدیریت فرآیندها و بار سیستم در PostgreSQL” subtitle=”توضیحات کامل”]

SELECT pid, usename, state, query_start, query 
FROM pg_stat_activity
WHERE state = 'idle';

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity
WHERE state = 'idle' AND now() - query_start > interval '10 minutes';

[/cdb_course_lesson][cdb_course_lesson title=”فصل 13: شناسایی و مدیریت Bottlenecks”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”روش‌های شناسایی Bottlenecks در CPU، حافظه و I/O” subtitle=”توضیحات کامل”]شناسایی گلوگاه‌ها (Bottlenecks) در سیستم پایگاه داده برای بهبود عملکرد PostgreSQL ضروری است. این گلوگاه‌ها ممکن است ناشی از محدودیت‌های CPU، حافظه، یا I/O باشند. در این بخش، روش‌های شناسایی این گلوگاه‌ها با ابزارها و تکنیک‌های مرتبط توضیح داده می‌شود.

1. شناسایی Bottlenecks در CPU

الف) بررسی بار CPU

استفاده از ابزارهای زیر برای مشاهده مصرف CPU:

ابزارهای خط فرمان:

• top: نمایش مصرف CPU توسط فرآیندهای PostgreSQL.

  top -u postgres

• htop: نسخه پیشرفته‌تر top با رابط گرافیکی بهتر.

pg_stat_activity:

شناسایی کوئری‌های سنگین:

SELECT pid, usename, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

ب) بررسی Parallel Queries

بررسی استفاده از Parallel Workers و شناسایی کوئری‌هایی که از چند هسته استفاده نمی‌کنند:

EXPLAIN (ANALYZE, VERBOSE, BUFFERS) <your_query>;

ج) بررسی پارامترهای مرتبط با CPU

• max_parallel_workers_per_gather: تعداد فرآیندهای موازی برای هر Query.
• parallel_setup_cost و parallel_tuple_cost: تأثیر هزینه فرآیندهای موازی.

2. شناسایی Bottlenecks در حافظه

الف) بررسی مصرف حافظه

ابزارهای خط فرمان:

• free -m: مشاهده وضعیت حافظه و Swap.
• vmstat: بررسی استفاده از حافظه در طول زمان.

  vmstat 1 10

pg_stat_activity:

شناسایی کوئری‌هایی که حافظه زیادی مصرف می‌کنند:

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend';

ب) تنظیمات مرتبط با حافظه

• work_mem: حافظه برای مرتب‌سازی و Hash Join.

  SET work_mem = '32MB';

• maintenance_work_mem: حافظه برای عملیات Vacuum و Index Rebuild.

  SET maintenance_work_mem = '512MB';

ج) شناسایی فرآیندهای بیکار و غیرضروری

فرآیندهایی که مدت طولانی بیکار هستند:

SELECT pid, usename, state, now() - state_change AS idle_duration
FROM pg_stat_activity
WHERE state = 'idle';

3. شناسایی Bottlenecks در I/O

الف) بررسی Disk I/O

ابزارهای خط فرمان:

• iostat: بررسی سرعت خواندن و نوشتن دیسک.

  iostat -x 1 5

• iotop: نمایش فرآیندهای با مصرف بالای I/O.

ابزارهای PostgreSQL:

• pg_stat_bgwriter: مشاهده فعالیت‌های نوشتن پس‌زمینه.

  SELECT * FROM pg_stat_bgwriter;

ب) شناسایی مشکلات مربوط به WAL

Write-Ahead Logging ممکن است باعث Bottleneck شود. بررسی پارامترهای مرتبط:

SELECT * FROM pg_stat_wal;

تنظیم پارامترها:

• wal_buffers: افزایش حافظه اختصاص‌یافته به WAL.
• wal_writer_delay: کاهش زمان تأخیر نوشتن.

4. استفاده از ابزارهای تحلیل و مانیتورینگ

الف) pg_stat_statements

شناسایی کوئری‌های سنگین:

SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

ب) pgBadger

ابزاری برای تحلیل لاگ‌ها و شناسایی گلوگاه‌های عملکردی:

pgbadger /path/to/logfile

ج) pg_top

ابزاری مشابه top برای مشاهده فعالیت‌های PostgreSQL.

5. تجزیه و تحلیل Query Plan

EXPLAIN و EXPLAIN ANALYZE

بررسی زمان اجرا و استفاده از منابع برای کوئری‌ها:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM my_table WHERE id = 1000;

شناسایی گلوگاه‌ها در Query Plan:

• Seq Scan: استفاده زیاد از اسکن ترتیبی.
• Index Scan: بهینه‌سازی ایندکس‌ها.
• Hash Join vs Nested Loop: بررسی الگوریتم‌های پیوند.

جمع‌بندی

1. CPU:
   • نظارت بر کوئری‌های سنگین با pg_stat_activity و ابزارهای سیستمی مانند top.
   • استفاده از فرآیندهای موازی برای بهبود عملکرد.
2. حافظه:
   • تنظیم مناسب work_mem و maintenance_work_mem.
   • جلوگیری از استفاده بیش از حد از حافظه با تحلیل کوئری‌ها.
3. I/O:
   • بررسی Disk I/O با ابزارهایی مانند iostat و pg_stat_bgwriter.
   • بهینه‌سازی WAL برای کاهش نوشتن‌های غیرضروری.
4. استفاده از ابزارهای مانیتورینگ و تحلیل مانند pgBadger و pg_top برای دید بهتر و تصمیم‌گیری‌های بهینه.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”رفع مشکلات Bottleneck برای افزایش سرعت پایگاه داده” subtitle=”توضیحات کامل”]گلوگاه‌ها (Bottlenecks) در پایگاه داده می‌توانند منجر به کاهش سرعت و کارایی سیستم شوند. این مشکلات ممکن است ناشی از محدودیت منابع CPU، حافظه، I/O یا طراحی نامناسب کوئری‌ها و جداول باشند. در این بخش، روش‌های رفع این گلوگاه‌ها برای بهبود سرعت پایگاه داده PostgreSQL ارائه شده است.

1. رفع Bottleneck در CPU

الف) بهینه‌سازی کوئری‌ها

• شناسایی کوئری‌های سنگین: با استفاده از ابزار pg_stat_statements، کوئری‌هایی که مدت زیادی از CPU استفاده می‌کنند را شناسایی کنید.

  SELECT query, calls, total_time, mean_time
  FROM pg_stat_statements
  ORDER BY total_time DESC
  LIMIT 5;

• بازنویسی کوئری‌ها:
  • استفاده از JOIN به جای زیردرخواست‌های متعدد.
  • اجتناب از کوئری‌های پیچیده و بدون ایندکس.
  • محدود کردن تعداد ستون‌های انتخاب‌شده.

ب) استفاده از Parallel Queries

فعال‌سازی و تنظیم کوئری‌های موازی برای استفاده بهینه از چند هسته CPU:

SET max_parallel_workers_per_gather = 4;

ج) تنظیم فرآیندهای پس‌زمینه

تنظیم تعداد فرآیندهای موازی برای عملیات:

ALTER SYSTEM SET max_parallel_workers = 8;

2. رفع Bottleneck در حافظه

الف) تنظیم پارامترهای حافظه

• work_mem: افزایش مقدار حافظه برای هر کوئری:

  SET work_mem = '64MB';

  توجه: مقدار این پارامتر باید با توجه به تعداد کوئری‌های همزمان تعیین شود.

• maintenance_work_mem: بهبود عملکرد عملیات‌های VACUUM و بازسازی ایندکس‌ها:

  SET maintenance_work_mem = '1GB';

ب) کاهش استفاده از حافظه Swap

از ابزارهایی مانند vmstat برای شناسایی فرآیندهای زیادی که از حافظه Swap استفاده می‌کنند، بهره بگیرید و مقدار حافظه فیزیکی را افزایش دهید.

ج) شناسایی و مدیریت فرآیندهای Idle

فرآیندهای بیکار که حافظه زیادی مصرف می‌کنند را شناسایی و خاتمه دهید:

SELECT pid, usename, state, now() - state_change AS idle_duration
FROM pg_stat_activity
WHERE state = 'idle';

3. رفع Bottleneck در Disk I/O

الف) افزایش کارایی Disk I/O

• فعال‌سازی ایندکس‌ها: استفاده از ایندکس‌های مناسب برای جداول بزرگ.

  CREATE INDEX idx_column ON my_table(column_name);

• تنظیم WAL: کاهش زمان تأخیر نوشتن در لاگ‌ها با تنظیم wal_writer_delay:

  ALTER SYSTEM SET wal_writer_delay = '10ms';

• افزایش حجم بافر WAL:

  ALTER SYSTEM SET wal_buffers = '16MB';

ب) توزیع داده‌ها روی دیسک‌های مختلف

• استفاده از Tablespace برای تقسیم داده‌ها روی دیسک‌های سریع‌تر.
• بهره‌گیری از RAID برای بهبود کارایی و امنیت داده‌ها.

ج) بهینه‌سازی Autovacuum

افزایش فرکانس Autovacuum برای جلوگیری از رشد بیش از حد جداول:

ALTER TABLE my_table SET (autovacuum_vacuum_cost_limit = 2000);

4. رفع Bottleneck در Query Design

الف) تحلیل و بهینه‌سازی Query Plan

استفاده از EXPLAIN و EXPLAIN ANALYZE برای شناسایی گلوگاه‌ها در کوئری:

EXPLAIN ANALYZE SELECT * FROM my_table WHERE column_name = 'value';

• بهبود موارد زیر:
  • جایگزینی Seq Scan با Index Scan.
  • کاهش استفاده از Nested Loop.
  • حذف کوئری‌های Redundant.

ب) بهبود ایندکس‌ها

• B-Tree Index: برای مقادیر برابر و نامساوی.
• GIN Index: برای جستجوی متن.
• BRIN Index: برای جداول بسیار بزرگ.

5. رفع Bottleneck در لاگ‌ها

الف) تنظیم پارامترهای Logging

تنظیم پارامترها برای جلوگیری از تولید لاگ‌های زیاد:

ALTER SYSTEM SET log_min_duration_statement = '500ms';

ب) تحلیل لاگ‌ها

استفاده از ابزار pgBadger برای شناسایی کوئری‌های کند:

pgbadger /path/to/logfile

6. رفع Bottleneck در فرآیندها و اتصال‌ها

الف) مدیریت تعداد اتصال‌ها

تنظیم max_connections برای جلوگیری از سربار اتصال‌های زیاد:

ALTER SYSTEM SET max_connections = 200;

ب) استفاده از Connection Pooling

استفاده از ابزارهایی مانند PgBouncer یا PgPool-II برای مدیریت اتصال‌ها.

7. رفع Bottleneck در ساختار جداول

الف) Normalization و Partitioning

• نرمال‌سازی جداول برای کاهش داده‌های تکراری.
• استفاده از Partitioning برای جداول بسیار بزرگ.

ب) تنظیم Fill Factor

تنظیم Fill Factor برای جداول پرتغییر:

ALTER TABLE my_table SET (FILLFACTOR = 70);

جمع‌بندی

1. CPU:
   • بازنویسی کوئری‌ها و استفاده از Parallelism.
   • افزایش تعداد فرآیندهای موازی.
2. حافظه:
   • بهبود پارامترهای حافظه مانند work_mem و maintenance_work_mem.
   • مدیریت فرآیندهای بیکار.
3. Disk I/O:
   • استفاده از ایندکس‌ها و بهینه‌سازی WAL.
   • توزیع داده‌ها روی دیسک‌های سریع‌تر.
4. Query Design:
   • تحلیل کوئری‌ها با EXPLAIN و بهینه‌سازی طرح اجرا.
5. Logging:
   • تحلیل لاگ‌ها با ابزارهایی مانند pgBadger.
6. فرآیندها:
   • استفاده از Connection Pooling و تنظیم تعداد اتصال‌ها.

با اجرای این راهکارها، می‌توان Bottleneckهای مختلف در PostgreSQL را شناسایی و رفع کرد و عملکرد کلی پایگاه داده را بهبود بخشید.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”بهبود کارایی سیستم با توزیع بهتر بار کاری” subtitle=”توضیحات کامل”]

pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb pool_size=20

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount NUMERIC
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

SET max_parallel_workers_per_gather = 4;

SET parallel_setup_cost = 1000;
SET parallel_tuple_cost = 0.1;

ALTER TABLE my_table SET (autovacuum_vacuum_threshold = 1000);

ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET maintenance_work_mem = '256MB';

ALTER SYSTEM SET max_parallel_workers = 8;

CREATE TABLESPACE fast_space LOCATION '/mnt/fast_disk';
ALTER TABLE my_table SET TABLESPACE fast_space;

[/cdb_course_lesson][/cdb_course_lessons]

1. پیش‌نیازها

برای پیکربندی SSL در PostgreSQL، نیاز به گواهی SSL معتبر دارید. می‌توانید از گواهی‌های خودامضا (Self-Signed) یا گواهی‌های صادرشده توسط یک مرجع معتبر (CA) استفاده کنید.

ابزارهای موردنیاز:

• OpenSSL برای ایجاد و مدیریت گواهی‌ها.
• دسترسی به فایل‌های پیکربندی PostgreSQL (postgresql.conf و pg_hba.conf).

2. مراحل پیکربندی SSL در PostgreSQL

مرحله 1: فعال‌سازی SSL در PostgreSQL

1. فایل تنظیمات PostgreSQL را باز کنید:

   sudo nano /etc/postgresql/<نسخه>/main/postgresql.conf

2. مقدار ssl را روی on تنظیم کنید:

   ssl = on
   ssl_cert_file = 'server.crt'
   ssl_key_file = 'server.key'
   ssl_ca_file = 'root.crt'  # در صورت استفاده از گواهی CA

مرحله 2: ایجاد گواهی‌های SSL

ایجاد گواهی خودامضا:

1. ایجاد کلید خصوصی:

   openssl genrsa -out server.key 2048

2. تنظیم مجوزهای امن روی فایل کلید:

   chmod 600 server.key

3. ایجاد گواهی خودامضا:

   openssl req -new -x509 -key server.key -out server.crt -days 365

4. انتقال گواهی‌ها به پوشه مناسب:

   sudo mv server.key server.crt /var/lib/postgresql/<نسخه>/main/

استفاده از گواهی CA:

• درخواست گواهی (Certificate Signing Request):

  openssl req -new -key server.key -out server.csr

• ارسال فایل CSR به مرجع صادرکننده گواهی (CA) و دریافت گواهی امضا شده.

مرحله 3: تنظیمات pg_hba.conf

فایل pg_hba.conf را برای استفاده از SSL تنظیم کنید:

# ارتباطات با SSL
hostssl  all  all  0.0.0.0/0  md5

این تنظیمات به PostgreSQL می‌گوید که فقط از اتصالات امن SSL برای تمام کاربران و شبکه‌ها استفاده کند.

مرحله 4: راه‌اندازی مجدد PostgreSQL

برای اعمال تنظیمات، PostgreSQL را مجدداً راه‌اندازی کنید:

sudo systemctl restart postgresql

3. آزمایش اتصال امن

با استفاده از ابزار psql:

1. اتصال به PostgreSQL با SSL:

   psql "sslmode=require host=<سرور> dbname=<پایگاه‌داده> user=<کاربر>"

2. بررسی وضعیت SSL: پس از اتصال، دستور زیر را در psql اجرا کنید:

   SHOW ssl;

   خروجی باید مقدار on باشد.

با استفاده از OpenSSL:

برای تست دستی، می‌توانید از OpenSSL استفاده کنید:

openssl s_client -connect <سرور>:5432

این دستور جزئیات مربوط به ارتباط SSL و گواهی استفاده‌شده را نمایش می‌دهد.

4. تنظیمات پیشرفته SSL

تنظیمات ssl_ciphers:

می‌توانید الگوریتم‌های رمزنگاری مجاز را با ssl_ciphers در فایل postgresql.conf مشخص کنید:

ssl_ciphers = 'HIGH:!aNULL:!MD5'

این تنظیمات الگوریتم‌های ضعیف مانند MD5 را غیرفعال می‌کند.

تنظیم ssl_prefer_server_ciphers:

این گزینه تعیین می‌کند که سرور اولویت الگوریتم‌های رمزنگاری را مشخص کند:

ssl_prefer_server_ciphers = on

5. نکات مهم امنیتی

1. کلید خصوصی را ایمن نگه دارید: حتماً مجوزهای دسترسی کلید خصوصی (server.key) را محدود کنید.
2. گواهی‌ها را به‌روز نگه دارید: برای جلوگیری از مشکلات امنیتی، گواهی‌ها را پیش از انقضا تمدید کنید.
3. تنظیمات فایروال: دسترسی به پورت PostgreSQL (معمولاً 5432) را فقط به آی‌پی‌های مجاز محدود کنید.

جمع‌بندی

پیکربندی SSL در PostgreSQL گامی مهم برای ایمن‌سازی ارتباطات میان کلاینت و سرور است. با استفاده از گواهی‌های معتبر و تنظیمات صحیح، می‌توانید از امنیت داده‌ها در محیط‌های تولیدی اطمینان حاصل کنید. علاوه بر این، آزمایش‌های منظم امنیتی برای بررسی صحت پیکربندی‌ها و جلوگیری از حملات احتمالی ضروری است.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”ایجاد و استفاده از گواهی‌های SSL در PostgreSQL” subtitle=”توضیحات کامل”]برای ایمن‌سازی ارتباطات بین کلاینت و سرور در PostgreSQL، استفاده از گواهی‌های SSL ضروری است. این گواهی‌ها داده‌ها را در حین انتقال رمزنگاری می‌کنند و ارتباط امن را تضمین می‌کنند. در این بخش، مراحل کامل برای ایجاد و استفاده از گواهی‌های SSL، از جمله گواهی‌های خودامضا و گواهی‌های صادرشده توسط CA، توضیح داده شده است.

1. گواهی‌های SSL چیست؟

گواهی‌های SSL شامل کلیدهای عمومی و خصوصی برای رمزنگاری داده‌ها و تایید هویت طرفین ارتباط هستند. این گواهی‌ها به دو دسته تقسیم می‌شوند:

• گواهی‌های خودامضا (Self-Signed Certificates): برای محیط‌های توسعه یا آزمایشی.
• گواهی‌های معتبر از مرجع صدور گواهی (CA): برای محیط‌های تولیدی.

2. ایجاد گواهی‌های خودامضا

مرحله 1: ایجاد کلید خصوصی سرور

کلید خصوصی برای رمزنگاری استفاده می‌شود و باید ایمن نگه داشته شود.

openssl genrsa -out server.key 2048

• فایل server.key حاوی کلید خصوصی سرور است.

مرحله 2: ایجاد فایل درخواست گواهی (CSR)

CSR اطلاعات موردنیاز برای ایجاد گواهی را شامل می‌شود.

openssl req -new -key server.key -out server.csr

هنگام اجرای این دستور، از شما اطلاعاتی خواسته می‌شود، مانند:

• Common Name (CN): نام دامنه یا آدرس IP سرور.
• Organization Name: نام سازمان شما.

مرحله 3: ایجاد گواهی خودامضا

برای تولید گواهی با استفاده از CSR:

openssl x509 -req -in server.csr -signkey server.key -out server.crt -days 365

• فایل server.crt حاوی گواهی خودامضا خواهد بود.

3. استفاده از گواهی‌های صادر شده توسط CA

مرحله 1: ایجاد درخواست گواهی (CSR)

مانند بخش قبل، فایل CSR ایجاد می‌شود:

openssl req -new -key server.key -out server.csr

مرحله 2: ارسال فایل CSR به CA

فایل CSR را به مرجع صدور گواهی ارسال کنید. پس از تایید اطلاعات، CA یک گواهی معتبر به شما ارائه می‌دهد.

مرحله 3: دریافت گواهی از CA

گواهی دریافت‌شده از CA را به همراه گواهی ریشه (Root Certificate) ذخیره کنید:

• server.crt: گواهی سرور.
• root.crt: گواهی ریشه (CA).

4. پیکربندی PostgreSQL برای استفاده از گواهی‌ها

تنظیم گواهی‌ها در PostgreSQL

گواهی‌های ایجادشده را در محل مناسب ذخیره کنید:

sudo mv server.key server.crt root.crt /var/lib/postgresql/<نسخه>/main/

• مطمئن شوید که فقط مالک سرور به کلید خصوصی دسترسی دارد:

  chmod 600 /var/lib/postgresql/<نسخه>/main/server.key

فایل postgresql.conf را ویرایش کنید:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

تنظیمات فایل pg_hba.conf

برای اطمینان از ارتباطات امن، فایل pg_hba.conf را به‌روزرسانی کنید:

hostssl all all 0.0.0.0/0 md5

راه‌اندازی مجدد PostgreSQL

برای اعمال تغییرات، PostgreSQL را مجدداً راه‌اندازی کنید:

sudo systemctl restart postgresql

5. تست گواهی‌های SSL

تست با استفاده از ابزار psql

اتصال به سرور با استفاده از SSL:

psql "sslmode=require host=<آدرس-سرور> dbname=<نام-پایگاه‌داده> user=<نام-کاربر>"

تست با OpenSSL

برای بررسی صحت گواهی و ارتباط، از دستور زیر استفاده کنید:

openssl s_client -connect <آدرس-سرور>:5432

این دستور اطلاعات مربوط به گواهی سرور و صحت ارتباط را نمایش می‌دهد.

6. بهترین شیوه‌ها در مدیریت گواهی‌ها

1. ایمن نگه‌داشتن کلید خصوصی: کلید خصوصی نباید با دیگران به اشتراک گذاشته شود.
2. به‌روزرسانی گواهی‌ها: گواهی‌ها را پیش از انقضا تمدید کنید.
3. استفاده از گواهی‌های CA در محیط‌های تولیدی: گواهی‌های معتبر امنیت و اعتماد بیشتری فراهم می‌کنند.
4. تنظیمات محدودکننده: دسترسی به فایل‌های گواهی را فقط برای کاربر PostgreSQL محدود کنید.

جمع‌بندی

ایجاد و استفاده از گواهی‌های SSL برای PostgreSQL به ایمن‌سازی ارتباطات کمک شایانی می‌کند. گواهی‌های خودامضا برای محیط‌های آزمایشی و گواهی‌های CA برای محیط‌های تولیدی پیشنهاد می‌شوند. با پیکربندی صحیح و آزمایش دوره‌ای، می‌توانید امنیت ارتباطات را تضمین کرده و از داده‌های حساس محافظت کنید.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تنظیمات ssl_ciphers و ssl_prefer_server_ciphers در PostgreSQL” subtitle=”توضیحات کامل”]استفاده از SSL/TLS برای ارتباط ایمن بین کلاینت‌ها و سرور PostgreSQL، یک ضرورت امنیتی است. در این بخش به بررسی و پیکربندی تنظیمات ssl_ciphers و ssl_prefer_server_ciphers می‌پردازیم که تأثیر مستقیمی بر امنیت و عملکرد ارتباطات رمزنگاری‌شده دارند.

1. ssl_ciphers

این پارامتر تعیین می‌کند که کدام cipher suites (الگوریتم‌های رمزنگاری) برای ارتباطات TLS استفاده شوند. انتخاب cipher suites مناسب از اهمیت بالایی برخوردار است، زیرا الگوریتم‌های ضعیف ممکن است در برابر حملات آسیب‌پذیر باشند.

نحوه پیکربندی

مقدار پیش‌فرض این پارامتر معمولاً یک لیست از cipher suites‌ ایمن است که با نسخه PostgreSQL و OpenSSL نصب‌شده هماهنگ است. با این حال، برای نیازهای خاص یا بهبود امنیت، می‌توانید این لیست را تغییر دهید.

• مسیر تنظیم: فایل postgresql.conf
• فرمت:

  ssl_ciphers = 'cipher1:cipher2:cipher3:...'

• مثال پیکربندی:

  ssl_ciphers = 'HIGH:!aNULL:!MD5:!RC4'

  این تنظیم:

  • الگوریتم‌های قوی را انتخاب می‌کند (HIGH).
  • الگوریتم‌هایی که احراز هویت ندارند (aNULL) را حذف می‌کند.
  • الگوریتم‌های منسوخ مانند MD5 و RC4 را غیرفعال می‌کند.

بررسی امنیت

برای بررسی الگوریتم‌های موجود در سرور و آزمایش تنظیمات، از ابزار OpenSSL استفاده کنید:

openssl ciphers -v 'HIGH:!aNULL:!MD5:!RC4'

این دستور لیستی از cipher suites پشتیبانی‌شده را نمایش می‌دهد.

2. ssl_prefer_server_ciphers

این پارامتر مشخص می‌کند که در زمان توافق بر سر یک cipher suite، سرور اولویت دارد یا کلاینت. فعال کردن این تنظیم به سرور اجازه می‌دهد تا کنترل بیشتری بر روی امنیت ارتباط داشته باشد.

تنظیم مقدار

• مقدار پیش‌فرض:

  ssl_prefer_server_ciphers = on

• مقدارهای ممکن:
  • on: سرور تصمیم می‌گیرد که کدام cipher suite انتخاب شود.
  • off: کلاینت تصمیم می‌گیرد.

مزایای فعال کردن ssl_prefer_server_ciphers

1. اطمینان حاصل می‌شود که ارتباط با استفاده از الگوریتم‌های امن برقرار می‌شود، حتی اگر کلاینت از الگوریتم‌های ضعیف پشتیبانی کند.
2. کاهش ریسک حملات مانند BEAST، که از ضعف در انتخاب cipher suites استفاده می‌کند.

مثال پیکربندی

ssl_prefer_server_ciphers = on

3. ترکیب این دو تنظیم

این دو تنظیم باید به صورت مکمل استفاده شوند:

• با استفاده از ssl_ciphers، لیستی از الگوریتم‌های امن و بهینه را مشخص کنید.
• با فعال کردن ssl_prefer_server_ciphers، اولویت انتخاب الگوریتم را به سرور بدهید.

پیکربندی نمونه

در فایل postgresql.conf:

ssl = on
ssl_ciphers = 'HIGH:!aNULL:!MD5:!RC4'
ssl_prefer_server_ciphers = on

4. آزمایش تنظیمات

پس از اعمال تنظیمات:

1. راه‌اندازی مجدد PostgreSQL:

   systemctl restart postgresql

2. تست ارتباط با ابزار OpenSSL:

   openssl s_client -connect <server_ip>:5432 -starttls postgres

   • این دستور وضعیت اتصال، cipher suite انتخاب‌شده، و گواهی استفاده‌شده را نمایش می‌دهد.
3. بررسی لاگ‌های PostgreSQL برای اطمینان از ارتباط ایمن:

   tail -f /var/log/postgresql/postgresql.log

جمع‌بندی

• تنظیم ssl_ciphers مشخص می‌کند که چه الگوریتم‌هایی برای ارتباطات رمزنگاری‌شده استفاده شوند.
• ssl_prefer_server_ciphers تضمین می‌کند که سرور کنترل بیشتری بر انتخاب الگوریتم‌ها دارد.
• پیکربندی دقیق این پارامترها امنیت ارتباطات PostgreSQL را افزایش می‌دهد و از استفاده از الگوریتم‌های منسوخ جلوگیری می‌کند.
• ابزارهایی مانند OpenSSL می‌توانند برای تست و اعتبارسنجی تنظیمات استفاده شوند.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تست امنیت ارتباط PostgreSQL با ابزارهای شبکه‌ای (مانند OpenSSL)” subtitle=”توضیحات کامل”]

CONNECTED(00000003)
---
Certificate chain
 0 s:/CN=example.com
   i:/CN=example_CA
---
Server certificate
-----BEGIN CERTIFICATE-----
<certificate content>
-----END CERTIFICATE-----
subject=/CN=example.com
issuer=/CN=example_CA
---
No client certificate CA names sent
---
SSL handshake has read 1234 bytes and written 456 bytes
---
New, TLSv1.3, Cipher is AES256-GCM-SHA384
Server public key is 2048 bit
Secure Renegotiation IS supported
---

openssl s_client -connect <IP>:5432 -starttls postgres -cipher <cipher_suite>

openssl s_client -connect 192.168.1.100:5432 -starttls postgres -cipher AES256-GCM-SHA384

[/cdb_course_lesson][cdb_course_lesson title=”فصل 2: مدیریت روش‌های احراز هویت (Authentication)”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”روش‌های احراز هویت پیشرفته در PostgreSQL” subtitle=”توضیحات کامل”]

host    all     all     0.0.0.0/0     md5

host    all     all     0.0.0.0/0     scram-sha-256

ALTER USER username WITH PASSWORD 'newpassword';

host    all     all     0.0.0.0/0     gss

host    all     all     0.0.0.0/0     ldap ldapserver=ldap://your-ldap-server

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”تنظیم فایل pg_hba.conf برای سیاست‌های مختلف احراز هویت” subtitle=”توضیحات کامل”]فایل pg_hba.conf (یا “PostgreSQL Host-Based Authentication Configuration File”) در PostgreSQL برای تنظیم سیاست‌های احراز هویت و تعیین اینکه کدام کاربران می‌توانند به پایگاه داده دسترسی داشته باشند، استفاده می‌شود. این فایل به‌طور ویژه برای مدیریت نحوه احراز هویت از منابع مختلف مانند اتصال‌های محلی، از راه دور، شبکه‌های مختلف و پروتکل‌های مختلف طراحی شده است.

در این بخش، نحوه تنظیم سیاست‌های مختلف احراز هویت در فایل pg_hba.conf برای متدهای مختلف احراز هویت از جمله MD5، SCRAM-SHA-256، GSSAPI/Kerberos و LDAP توضیح داده می‌شود.

ساختار فایل pg_hba.conf

هر خط در فایل pg_hba.conf به صورت زیر فرمت‌بندی می‌شود:

# اجازه دسترسی به تمام کاربران از هر IP با استفاده از MD5
host    all     all     0.0.0.0/0     md5

• TYPE: نوع اتصال (مانند host, local, hostssl).
• DATABASE: نام پایگاه داده یا all برای تمام پایگاه‌های داده.
• USER: نام کاربری یا all برای تمام کاربران.
• ADDRESS: آدرس IP یا دامنه‌ای که اتصال از آنجا مجاز است (یا all برای همه آدرس‌ها).
• METHOD: روش احراز هویت که می‌تواند یکی از متدهای مختلف مانند md5, scram-sha-256, gss, ldap باشد.

۱. احراز هویت با استفاده از MD5

در این روش، پسورد کاربران به صورت MD5 hash شده و در پایگاه داده ذخیره می‌شود. هنگام ورود، پسورد وارد شده با مقدار هش‌شده در پایگاه داده مقایسه می‌شود.

نمونه تنظیمات برای MD5:

# اجازه دسترسی به تمام کاربران از هر IP با استفاده از MD5
host    all     all     0.0.0.0/0     md5

این تنظیم به تمام کاربران اجازه می‌دهد که از هر آدرسی به پایگاه داده متصل شوند، مشروط به این‌که پسورد آنها با استفاده از MD5 تایید شود.

۲. احراز هویت با استفاده از SCRAM-SHA-256

روش SCRAM-SHA-256 از SHA-256 برای هش کردن پسورد استفاده می‌کند و از salt نیز برای امنیت بیشتر بهره می‌برد. این روش ایمن‌تر از MD5 است.

نمونه تنظیمات برای SCRAM-SHA-256:

# اجازه دسترسی به تمام کاربران از هر IP با استفاده از SCRAM-SHA-256
host    all     all     0.0.0.0/0     scram-sha-256

این تنظیم به تمام کاربران اجازه می‌دهد که از هر آدرسی به پایگاه داده متصل شوند و احراز هویت با استفاده از روش SCRAM-SHA-256 انجام می‌شود.

۳. احراز هویت با استفاده از GSSAPI/Kerberos

GSSAPI یک پروتکل احراز هویت است که معمولاً با Kerberos برای احراز هویت یکپارچه در شبکه‌های بزرگ استفاده می‌شود. این روش برای احراز هویت کاربران بدون نیاز به وارد کردن پسورد به‌طور خودکار از Kerberos ticketها استفاده می‌کند.

نمونه تنظیمات برای GSSAPI/Kerberos:

# اجازه دسترسی به تمام کاربران از هر IP با استفاده از GSSAPI (Kerberos)
host    all     all     0.0.0.0/0     gss

در این تنظیمات، کاربران باید از Kerberos برای احراز هویت استفاده کنند. این روش برای سازمان‌هایی که از سیستم‌های Kerberos استفاده می‌کنند بسیار مفید است.

۴. احراز هویت با استفاده از LDAP

در این روش، اطلاعات احراز هویت کاربران از یک سرور LDAP (مانند Active Directory یا OpenLDAP) دریافت می‌شود. این روش برای سازمان‌هایی که از سرویس‌های LDAP برای مدیریت کاربران استفاده می‌کنند بسیار مفید است.

نمونه تنظیمات برای LDAP:

# اجازه دسترسی به تمام کاربران از هر IP با استفاده از LDAP
host    all     all     0.0.0.0/0     ldap ldapserver=ldap://your-ldap-server

این تنظیم به PostgreSQL دستور می‌دهد که برای احراز هویت از سرویس LDAP استفاده کند. آدرس سرور LDAP در اینجا باید مشخص شود.

۵. احراز هویت با استفاده از Trust

روش trust به این معنی است که هیچ‌گونه احراز هویتی لازم نیست و به‌طور کامل به اتصال مجاز کاربران وابسته است. این روش برای توسعه و آزمایش مفید است اما برای محیط‌های تولیدی توصیه نمی‌شود.

نمونه تنظیمات برای Trust:

# اجازه دسترسی بدون احراز هویت از هر آدرسی
host    all     all     0.0.0.0/0     trust

۶. احراز هویت با استفاده از Peer

روش peer به این معنی است که تنها کاربران محلی سیستم می‌توانند به PostgreSQL دسترسی داشته باشند. این روش فقط برای اتصالات محلی (از همان ماشین سرور) کاربرد دارد.

نمونه تنظیمات برای Peer:

# اجازه دسترسی به کاربران سیستم عامل از همان ماشین
local   all     all                     peer

در این تنظیمات، تنها کاربران سیستم عامل می‌توانند به پایگاه داده دسترسی پیدا کنند.

نکات مهم در پیکربندی pg_hba.conf

• ترتیب خطوط: ترتیب خطوط در فایل pg_hba.conf اهمیت دارد. PostgreSQL به ترتیب از بالا به پایین فایل را پردازش می‌کند و اولین مطابقت را اعمال می‌کند.
• تنظیمات IP و subnet: در بخش ADDRESS، می‌توانید برای محدود کردن دسترسی کاربران از آدرس‌های خاص استفاده کنید. برای مثال، 192.168.1.0/24 به معنی دسترسی فقط از شبکه 192.168.1.0 است.
• پشتیبانی از اتصالات SSL: اگر می‌خواهید از SSL برای اتصالات امن استفاده کنید، از نوع hostssl به جای host استفاده کنید.

نمونه تنظیمات با SSL:

# اجازه دسترسی به کاربران با استفاده از SSL
hostssl  all     all     0.0.0.0/0     scram-sha-256

این تنظیمات تنها به اتصالاتی که از SSL استفاده می‌کنند، اجازه می‌دهند که با روش SCRAM-SHA-256 احراز هویت انجام دهند.

جمع‌بندی

تنظیم فایل pg_hba.conf بخش حیاتی در پیکربندی امنیت PostgreSQL است. با استفاده از این فایل، می‌توانید دسترسی‌ها را به دقت مدیریت کنید و روش‌های مختلف احراز هویت مانند MD5، SCRAM-SHA-256، GSSAPI/Kerberos و LDAP را تنظیم نمایید. همچنین، می‌توان دسترسی را از طریق ویژگی‌هایی نظیر محدود کردن آدرس‌های IP، استفاده از SSL و اعمال روش‌های احراز هویت مناسب برای هر نیاز خاص تنظیم کرد تا امنیت پایگاه داده به حداکثر برسد.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”بررسی و نظارت بر تلاش‌های ورود ناموفق” subtitle=”توضیحات کامل”]نظارت بر تلاش‌های ورود ناموفق به پایگاه داده یکی از جنبه‌های حیاتی امنیت است. این فرآیند می‌تواند به شناسایی حملات احتمالی مانند Brute Force، Dictionary attacks یا پیشروی در حملات کمک کند و به شما این امکان را می‌دهد که به موقع از بروز تهدیدات جلوگیری کنید. PostgreSQL ابزارهایی برای نظارت بر تلاش‌های ورود ناموفق و اعمال اقدامات امنیتی مختلف فراهم می‌آورد.

در این بخش، به بررسی روش‌های مختلف نظارت بر تلاش‌های ورود ناموفق و نحوه پیکربندی PostgreSQL برای شناسایی و واکنش به این تلاش‌ها می‌پردازیم.

۱. فعال‌سازی لاگ‌های ورود ناموفق

برای بررسی تلاش‌های ورود ناموفق، ابتدا باید لاگ‌های مربوط به این تلاش‌ها را فعال کنید. PostgreSQL برای این منظور از تنظیمات log_connections و log_disconnections استفاده می‌کند.

پیکربندی لاگ‌های تلاش‌های ورود ناموفق

در فایل پیکربندی PostgreSQL (postgresql.conf)، گزینه‌های زیر را برای فعال‌سازی لاگ‌ها تنظیم کنید:

# فعال‌سازی لاگ تمام اتصالات و قطع ارتباط‌ها
log_connections = on
log_disconnections = on

# لاگ کردن تلاش‌های ناموفق ورود
log_statement = 'none'
log_duration = on

• log_connections: این گزینه تمام تلاش‌های موفق ورود را در لاگ‌ها ثبت می‌کند.
• log_disconnections: این گزینه اطلاعات مربوط به قطع ارتباط‌ها را ثبت می‌کند.
• log_statement: این گزینه می‌تواند برای لاگ کردن تمام دستورات SQL استفاده شود، اما برای جلوگیری از پر شدن لاگ‌ها بهتر است فقط اتصال‌ها و قطع ارتباط‌ها را ثبت کنید.
• log_duration: زمان صرف‌شده برای هر اتصال و درخواست را ثبت می‌کند.

۲. استفاده از pgAudit برای لاگ‌گذاری دقیق‌تر

ابزار pgAudit یک افزونه قدرتمند برای PostgreSQL است که امکان ثبت دقیق‌تر عملیات‌های پایگاه داده را فراهم می‌کند. با استفاده از این افزونه می‌توان تمام دستورات و اقدامات حساس پایگاه داده را به‌ویژه تلاش‌های ناموفق ورود ثبت کرد.

نصب و پیکربندی pgAudit

برای استفاده از pgAudit، ابتدا باید افزونه را نصب و در PostgreSQL بارگذاری کنید. برای این کار از دستورات زیر استفاده کنید:

sudo apt-get install postgresql-contrib

# بارگذاری افزونه در فایل postgresql.conf
shared_preload_libraries = 'pgaudit'

پس از راه‌اندازی دوباره PostgreSQL، می‌توانید از pgAudit برای ثبت تلاش‌های ورود ناموفق و دیگر رویدادهای حساس استفاده کنید.

۳. استفاده از fail2ban برای مسدودسازی تلاش‌های ناموفق

fail2ban یک ابزار امنیتی است که برای مسدودسازی خودکار IPهایی که تلاش‌های ورود ناموفق زیادی انجام می‌دهند، طراحی شده است. این ابزار می‌تواند به طور خودکار آی‌پی‌هایی که تعداد زیادی تلاش ناموفق برای ورود به سیستم PostgreSQL دارند، مسدود کند.

پیکربندی fail2ban برای PostgreSQL

1. نصب fail2ban:

   sudo apt-get install fail2ban

2. پیکربندی fail2ban برای PostgreSQL با اضافه کردن فیلتر PostgreSQL در فایل /etc/fail2ban/filter.d/postgresql.conf:

   [Definition]
   failregex = ^%(__prefix_line)sFATAL:  password authentication failed for user ".*"
   ignoreregex =

3. اضافه کردن پیکربندی به فایل /etc/fail2ban/jail.local:

   [postgresql]
   enabled = true
   port    = 5432
   filter  = postgresql
   logpath = /var/log/postgresql/postgresql-*.log
   maxretry = 3
   bantime  = 600
   findtime = 600

در این پیکربندی:

• maxretry = 3: پس از ۳ تلاش ناموفق برای ورود، IP مسدود می‌شود.
• bantime = 600: مدت زمان مسدودسازی ۱۰ دقیقه است.
• findtime = 600: تلاش‌های ناموفق باید در ۱۰ دقیقه آخر ثبت شده باشند تا مسدودسازی انجام شود.

۴. تحلیل و بررسی لاگ‌ها

پس از فعال‌سازی لاگ‌های مورد نیاز، می‌توانید لاگ‌ها را برای شناسایی تلاش‌های ورود ناموفق تحلیل کنید. برای این کار می‌توانید از ابزارهای مختلفی مانند grep، awk، logwatch و یا ELK stack (Elasticsearch, Logstash, Kibana) استفاده کنید.

استفاده از grep برای جستجوی تلاش‌های ناموفق

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

grep "FATAL:  password authentication failed" /var/log/postgresql/postgresql-*.log

این دستور تمام تلاش‌های ناموفق برای ورود به PostgreSQL را در لاگ‌ها جستجو کرده و نمایش می‌دهد.

۵. هشدارها و اطلاع‌رسانی

برای اطلاع‌رسانی و هشدار در مورد تلاش‌های ورود ناموفق، می‌توانید از ابزارهایی مثل syslog یا Logwatch استفاده کنید. این ابزارها می‌توانند به‌طور خودکار هرگونه تلاش ورود ناموفق به PostgreSQL را به مدیر سیستم گزارش دهند.

پیکربندی Logwatch

با پیکربندی Logwatch می‌توانید خلاصه‌ای از تلاش‌های ناموفق ورود به PostgreSQL را در قالب ایمیل دریافت کنید.

1. نصب Logwatch:

   sudo apt-get install logwatch

2. پیکربندی Logwatch برای نظارت بر لاگ‌های PostgreSQL:

   sudo nano /etc/logwatch/conf/logwatch.conf

   سپس تنظیمات مربوط به لاگ PostgreSQL را در این فایل وارد کنید تا Logwatch گزارش‌های مربوط به تلاش‌های ناموفق ورود را به شما ارسال کند.

جمع‌بندی

نظارت بر تلاش‌های ورود ناموفق در PostgreSQL برای شناسایی و واکنش به حملات احتمالی از اهمیت بالایی برخوردار است. با استفاده از روش‌های مختلفی مانند فعال‌سازی لاگ‌ها، نصب افزونه‌هایی همچون pgAudit، پیکربندی ابزارهایی مانند fail2ban و تحلیل لاگ‌ها می‌توانید به راحتی تلاش‌های ورود ناموفق را شناسایی کرده و به موقع اقدام کنید. این اقدامات به افزایش امنیت پایگاه داده و جلوگیری از حملات به سیستم کمک می‌کنند.

[/cdb_course_lesson][cdb_course_lesson title=”فصل 3: کنترل دسترسی پیشرفته”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” subtitle=”توضیحات کامل” title=”مدیریت دسترسی کاربران با استفاده از Row-Level Security (RLS)”]Row-Level Security (RLS) یکی از ویژگی‌های پیشرفته PostgreSQL است که به شما این امکان را می‌دهد تا دسترسی به داده‌ها را به سطح ردیف‌ها (rows) محدود کنید. با استفاده از RLS، می‌توانید سیاست‌های دسترسی بسیار دقیق‌تری برای کاربران و گروه‌ها ایجاد کنید، به طوری که هر کاربر تنها به داده‌هایی که مجاز به دیدن آن‌ها است دسترسی داشته باشد. این ویژگی به ویژه برای پایگاه‌های داده بزرگ و محیط‌های با نیاز به سطح بالای امنیت مفید است.

در این بخش، به بررسی روش‌های مختلف مدیریت دسترسی کاربران با استفاده از Row-Level Security (RLS) خواهیم پرداخت.

۱. فعال‌سازی RLS برای جدول‌ها

برای استفاده از RLS در PostgreSQL، ابتدا باید آن را برای هر جدول به‌طور خاص فعال کنید. این کار با استفاده از دستور ALTER TABLE انجام می‌شود. با فعال‌سازی RLS، همه کاربران تنها به داده‌هایی که مطابق با سیاست‌های تعریف‌شده برای آن‌ها است دسترسی خواهند داشت.

فعال‌سازی RLS

برای فعال‌سازی RLS برای یک جدول خاص، از دستور زیر استفاده کنید:

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

• table_name: نام جدولی است که می‌خواهید RLS را برای آن فعال کنید.

پس از فعال‌سازی RLS، به‌طور پیش‌فرض هیچ سیاست دسترسی برای جدول وجود ندارد، بنابراین باید سیاست‌هایی را برای کنترل دسترسی به ردیف‌ها ایجاد کنید.

۲. تعریف سیاست‌ها برای دسترسی به ردیف‌ها

سیاست‌های RLS با استفاده از دستور CREATE POLICY تعریف می‌شوند. هر سیاست می‌تواند برای یک یا چند عملیات خاص (SELECT، INSERT، UPDATE، DELETE) تعیین شود و شرایط خاصی را برای دسترسی به ردیف‌ها تنظیم کند.

تعریف سیاست برای انتخاب داده‌ها (SELECT)

در ابتدا، یک سیاست ساده برای دسترسی کاربران به ردیف‌های خاص جدول بر اساس ویژگی‌های آن‌ها می‌سازیم. به‌عنوان مثال، فرض کنید که جدول employees داریم که شامل ستون‌های id، name و department است و می‌خواهیم به هر کاربر اجازه داده شود که تنها ردیف‌هایی را مشاهده کند که متعلق به بخش خودشان باشد.

CREATE POLICY employee_access_policy
  ON employees
  FOR SELECT
  USING (department = current_setting('myapp.current_user_department'));

• FOR SELECT: این سیاست فقط برای عملیات SELECT اعمال می‌شود.
• USING: این بخش شرایط دسترسی را تعریف می‌کند. در اینجا، شرط این است که فقط ردیف‌هایی انتخاب شوند که department آن‌ها برابر با بخشی باشد که کاربر در آن قرار دارد.

تعریف سیاست برای به‌روزرسانی داده‌ها (UPDATE)

اگر بخواهید کاربران تنها مجاز به به‌روزرسانی ردیف‌هایی باشند که متعلق به بخش خودشان است، می‌توانید سیاست زیر را برای عملیات UPDATE تعریف کنید:

CREATE POLICY employee_update_policy
  ON employees
  FOR UPDATE
  USING (department = current_setting('myapp.current_user_department'));

تعریف سیاست برای وارد کردن داده‌ها (INSERT)

اگر می‌خواهید کاربران تنها مجاز به وارد کردن داده‌هایی باشند که مربوط به بخش خودشان است، از سیاست زیر استفاده کنید:

CREATE POLICY employee_insert_policy
  ON employees
  FOR INSERT
  WITH CHECK (department = current_setting('myapp.current_user_department'));

• WITH CHECK: این بخش شرایطی را برای ورودی‌های جدید تعیین می‌کند. به این معنا که هنگام وارد کردن داده‌های جدید، department باید با بخش کاربر مطابقت داشته باشد.

تعریف سیاست برای حذف داده‌ها (DELETE)

اگر می‌خواهید کاربران تنها مجاز به حذف ردیف‌هایی باشند که متعلق به بخش خودشان است، از سیاست زیر استفاده کنید:

CREATE POLICY employee_delete_policy
  ON employees
  FOR DELETE
  USING (department = current_setting('myapp.current_user_department'));

۳. مدیریت سیاست‌ها

برای مدیریت سیاست‌ها در PostgreSQL، شما می‌توانید سیاست‌ها را تغییر دهید یا حذف کنید. در اینجا نحوه انجام این کار آورده شده است:

مشاهده سیاست‌های موجود

برای مشاهده سیاست‌های فعال شده روی یک جدول خاص، می‌توانید از دستور زیر استفاده کنید:

SELECT * FROM pg_policies WHERE tablename = 'employees';

غیرفعال‌سازی RLS

اگر بخواهید RLS را برای یک جدول غیرفعال کنید، می‌توانید از دستور زیر استفاده کنید:

ALTER TABLE table_name DISABLE ROW LEVEL SECURITY;

حذف سیاست

برای حذف یک سیاست خاص از یک جدول، از دستور زیر استفاده کنید:

DROP POLICY policy_name ON table_name;

۴. مدیریت دسترسی به داده‌ها با استفاده از current_setting

در PostgreSQL، می‌توانید از تابع current_setting برای دسترسی به تنظیمات محیطی استفاده کنید. برای مثال، در سیاست‌های بالا، از current_setting('myapp.current_user_department') استفاده کردیم تا بخش جاری کاربر را بررسی کنیم. این کار به شما این امکان را می‌دهد که دسترسی‌ها را به‌صورت پویا براساس پارامترهای محیطی مدیریت کنید.

برای تنظیم یک مقدار محیطی خاص برای هر کاربر، از دستور SET استفاده کنید:

SET myapp.current_user_department = 'HR';

این دستور مقدار محیطی را برای این جلسه خاص تعیین می‌کند و می‌تواند برای اعمال سیاست‌های دسترسی دقیق‌تر در RLS استفاده شود.

۵. کنترل بیشتر با استفاده از pg_catalog

برای انجام کنترل‌های بیشتر، می‌توانید از سیستم‌داده‌های pg_catalog برای دسترسی به اطلاعات بیشتری درباره کاربران، رول‌ها و مجوزهای آن‌ها استفاده کنید.

جمع‌بندی

Row-Level Security (RLS) یک ابزار قدرتمند برای مدیریت دسترسی به داده‌ها در PostgreSQL است که به شما این امکان را می‌دهد تا سیاست‌های دسترسی بسیار دقیق و پویا برای کاربران و گروه‌ها ایجاد کنید. با استفاده از RLS، می‌توانید دسترسی به داده‌ها را به ردیف‌ها محدود کرده و امنیت پایگاه داده خود را تقویت کنید. این ویژگی به ویژه در محیط‌های چندکاربری و پایگاه‌های داده با حجم بالا که نیاز به کنترل دقیق دسترسی دارند، بسیار مفید است.

[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”پیاده‌سازی سیاست‌های دسترسی دقیق با دستورات GRANT و REVOKE” subtitle=”توضیحات کامل”]در PostgreSQL، مدیریت دسترسی به داده‌ها و منابع پایگاه داده یکی از جنبه‌های حیاتی امنیت است. برای اعمال سیاست‌های دسترسی دقیق و کنترل‌کننده، از دستورات GRANT و REVOKE استفاده می‌شود. این دستورات به مدیران پایگاه داده این امکان را می‌دهند که به کاربران و نقش‌ها مجوزهای خاصی اعطا کنند یا آن‌ها را لغو کنند.

در این بخش، به‌طور جامع نحوه پیاده‌سازی سیاست‌های دسترسی دقیق با استفاده از دستورات GRANT و REVOKE در PostgreSQL توضیح داده می‌شود.

۱. دستور GRANT

دستور GRANT برای اعطای مجوزهای مختلف به کاربران، نقش‌ها یا گروه‌ها در PostgreSQL استفاده می‌شود. این مجوزها می‌توانند برای جداول، نماها، توابع، و سایر اشیاء پایگاه داده اعمال شوند. این دستور به شما امکان می‌دهد که دقیقاً تعیین کنید که هر کاربر یا نقش چه نوع دسترسی‌ای به داده‌ها یا اشیاء مختلف پایگاه داده دارد.

ساختار کلی دستور GRANT

ساختار دستور GRANT به‌صورت زیر است:

GRANT privileges ON object TO role [WITH GRANT OPTION];

• privileges: نوع مجوزهایی که می‌خواهید اعطا کنید، مانند SELECT، INSERT، UPDATE، DELETE، ALL و غیره.
• object: شیء پایگاه داده که می‌خواهید دسترسی به آن اعطا شود (مانند جدول، نما، تابع).
• role: نقش یا کاربری که می‌خواهید به آن مجوز بدهید.
• WITH GRANT OPTION: اگر می‌خواهید کاربر یا نقش مورد نظر بتواند این دسترسی‌ها را به دیگران واگذار کند، از این گزینه استفاده می‌شود.

مثال‌های دستور GRANT

1. اعطای دسترسی SELECT به یک جدول برای کاربر: در این مثال، دسترسی خواندن (SELECT) به جدول employees برای کاربر john اعطا می‌شود.

   GRANT SELECT ON employees TO john;

2. اعطای دسترسی INSERT و UPDATE به یک جدول برای گروهی از کاربران (نقش): در اینجا، دسترسی درج (INSERT) و به‌روزرسانی (UPDATE) به جدول employees برای نقش hr_team اعطا می‌شود.

   GRANT INSERT, UPDATE ON employees TO hr_team;

3. اعطای تمام مجوزها به یک کاربر (تمام مجوزهای دسترسی): این دستور به کاربر admin_user تمامی مجوزها برای انجام هر عملیات روی جدول employees می‌دهد.

   GRANT ALL ON employees TO admin_user;

4. اعطای دسترسی و امکان واگذاری مجوزها به دیگران: در این مثال، به کاربر manager_user دسترسی SELECT به جدول employees اعطا می‌شود و این امکان را دارد که این دسترسی را به دیگران واگذار کند.

   GRANT SELECT ON employees TO manager_user WITH GRANT OPTION;

۲. دستور REVOKE

دستور REVOKE برای لغو مجوزهایی است که قبلاً با دستور GRANT اعطا شده‌اند. با استفاده از REVOKE، می‌توان دسترسی‌ها را از کاربران یا نقش‌ها حذف کرد یا از آن‌ها سلب اختیار کرد.

ساختار کلی دستور REVOKE

ساختار دستور REVOKE به‌صورت زیر است:

REVOKE privileges ON object FROM role;

• privileges: نوع مجوزهایی که می‌خواهید لغو کنید.
• object: شیء پایگاه داده که دسترسی‌های آن لغو می‌شود.
• role: نقش یا کاربری که مجوز از آن لغو می‌شود.

مثال‌های دستور REVOKE

1. لغو دسترسی SELECT از یک کاربر: در این مثال، دسترسی خواندن (SELECT) از جدول employees برای کاربر john لغو می‌شود.

   REVOKE SELECT ON employees FROM john;

2. لغو دسترسی INSERT و UPDATE از یک گروه کاربری: این دستور دسترسی‌های درج و به‌روزرسانی از نقش hr_team برای جدول employees را لغو می‌کند.

   REVOKE INSERT, UPDATE ON employees FROM hr_team;

3. لغو تمام مجوزها از یک کاربر: با استفاده از این دستور، تمام مجوزهای داده شده به کاربر manager_user برای جدول employees لغو می‌شود.

   REVOKE ALL ON employees FROM manager_user;

4. لغو دسترسی واگذاری مجوزها (WITH GRANT OPTION): اگر مجوز واگذاری به یک کاربر داده شده باشد و بخواهید این امکان را از آن‌ها سلب کنید، می‌توانید از دستور زیر استفاده کنید:

   REVOKE GRANT OPTION FOR SELECT ON employees FROM manager_user;

۳. استفاده از دستور GRANT و REVOKE برای پیاده‌سازی سیاست‌های دسترسی دقیق

یکی از روش‌های اصلی استفاده از دستورات GRANT و REVOKE برای پیاده‌سازی سیاست‌های دسترسی دقیق، استفاده از دسترسی‌های مختلف برای هر نقش و کاربر است. شما می‌توانید با استفاده از این دستورات، برای هر کاربر و گروه کاری مجوزهای مختلفی تعریف کرده و تنها به داده‌ها و عملیات خاصی که نیاز دارند دسترسی بدهید.

نمونه سناریو ۱: سیاست دسترسی به داده‌های حساس

فرض کنید که در یک سازمان اطلاعات حساس در یک جدول به نام employee_data ذخیره شده است. برخی از کاربران باید فقط به بخش خاصی از این داده‌ها دسترسی داشته باشند. شما می‌توانید از GRANT برای ایجاد دسترسی محدود برای این کاربران استفاده کنید.

• به عنوان مثال، کاربران در نقش manager_role تنها باید به ردیف‌های مربوط به بخش خودشان دسترسی داشته باشند. این دسترسی می‌تواند با استفاده از RLS و مجوزهای SELECT در کنار هم ترکیب شود.

نمونه سناریو ۲: سیاست دسترسی به عملیات خاص

در یک سناریو دیگر، فرض کنید که می‌خواهید کاربری فقط مجاز به انجام عملیات SELECT روی یک جدول باشد و هیچ‌گونه دسترسی به عملیات INSERT، UPDATE یا DELETE نداشته باشد. این کار را می‌توانید با استفاده از دستور GRANT انجام دهید:

GRANT SELECT ON sensitive_table TO user1;
REVOKE INSERT, UPDATE, DELETE ON sensitive_table FROM user1;

جمع‌بندی

استفاده از دستورات GRANT و REVOKE در PostgreSQL ابزار قدرتمندی برای مدیریت سیاست‌های دسترسی دقیق به داده‌ها و منابع پایگاه داده است. این دستورات به مدیران پایگاه داده این امکان را می‌دهند که به‌صورت گرانول و دقیق دسترسی‌های مختلف را به کاربران و نقش‌ها تخصیص دهند. با استفاده از این دستورات می‌توان سیاست‌های امنیتی قوی‌تری ایجاد کرد و به طور مؤثری امنیت داده‌ها را افزایش داد.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”اعمال سیاست‌های Column-Level Security” subtitle=”توضیحات کامل”]Column-Level Security (CLS) یکی از قابلیت‌های پیشرفته در PostgreSQL است که به شما این امکان را می‌دهد که دسترسی به سطرها یا ستون‌های خاص یک جدول را برای کاربران یا نقش‌های مختلف محدود کنید. این قابلیت به ویژه در مواردی که داده‌ها در یک جدول شامل اطلاعات حساس هستند، مفید واقع می‌شود. به‌طور خاص، این قابلیت امکان مدیریت دسترسی‌ها در سطح ستون‌ها را فراهم می‌آورد تا هر کاربر یا نقش بتواند فقط به ستون‌های خاصی از یک جدول دسترسی داشته باشد.

در این بخش، به‌طور جامع نحوه پیاده‌سازی و اعمال سیاست‌های Column-Level Security در PostgreSQL توضیح داده خواهد شد.

۱. چرا Column-Level Security مهم است؟

گاهی اوقات ممکن است که یک کاربر یا نقش نیاز داشته باشد که به تنها بخشی از اطلاعات موجود در یک جدول دسترسی داشته باشد، اما نه به تمام ستون‌ها. به‌طور مثال:

• یک کاربر در یک سیستم منابع انسانی ممکن است تنها به نام و شماره شناسنامه کارمندان دسترسی داشته باشد و نیازی به مشاهده داده‌های مربوط به حقوق و دستمزد نداشته باشد.
• در یک پایگاه داده مالی، برخی از کاربران ممکن است تنها مجاز به مشاهده بخش‌هایی از داده‌های مالی خاص باشند و از دسترسی به سایر داده‌های حساس نظیر شماره حساب‌ها یا موجودی‌ها محروم شوند.

در این شرایط، استفاده از Column-Level Security به‌عنوان یک راهکار امنیتی مفید خواهد بود.

۲. اعمال Column-Level Security با استفاده از مجوزهای GRANT

در PostgreSQL، دسترسی به ستون‌ها به‌طور خاص با استفاده از دستورات GRANT و REVOKE اعمال می‌شود. در حالی که دستور GRANT به شما این امکان را می‌دهد که دسترسی به ستون‌ها را اعطا کنید، دستور REVOKE برای لغو دسترسی‌ها استفاده می‌شود.

ساختار کلی دستور GRANT برای ستون‌ها

GRANT privilege ON table(column) TO role;

• privilege: نوع مجوزی که می‌خواهید اعطا کنید (مثل SELECT، UPDATE).
• table: نام جدول.
• column: نام ستونی که می‌خواهید دسترسی به آن را مشخص کنید.
• role: نام نقش یا کاربری که به آن دسترسی می‌دهید.

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

1. اعطای دسترسی SELECT به یک ستون خاص: به‌طور مثال، فرض کنید که جدول employees شامل ستون‌های name، salary و address باشد. شما می‌توانید دسترسی فقط به ستون name را برای کاربر user1 اعطا کنید:

   GRANT SELECT (name) ON employees TO user1;

2. اعطای دسترسی UPDATE به یک ستون خاص: اگر بخواهید فقط به یک کاربر امکان ویرایش ستون salary در جدول employees را بدهید، دستور زیر را استفاده کنید:

   GRANT UPDATE (salary) ON employees TO user2;

3. اعطای دسترسی چندین ستون به یک نقش: در صورتی که بخواهید دسترسی به چندین ستون را به یک نقش اعطا کنید، می‌توانید این کار را به صورت زیر انجام دهید:

   GRANT SELECT (name, address) ON employees TO hr_team;

4. لغو دسترسی به یک ستون خاص: اگر بخواهید دسترسی به ستون salary از کاربر user2 لغو شود، از دستور REVOKE استفاده کنید:

   REVOKE SELECT (salary) ON employees FROM user2;

۳. استفاده از View‌ها برای محدود کردن دسترسی به ستون‌ها

گاهی اوقات نمی‌توان به‌طور مستقیم سیاست‌های Column-Level Security را در سطح ستون‌ها اعمال کرد، مخصوصاً زمانی که نیاز دارید چندین سیاست دسترسی پیچیده را برای گروه‌های مختلف کاربران پیاده‌سازی کنید. در این مواقع، می‌توانید از Views برای ایجاد یک لایه دسترسی استفاده کنید.

یک View می‌تواند مجموعه‌ای از ستون‌ها را در اختیار کاربران قرار دهد و دسترسی به ستون‌های دیگر را مخفی کند. در این صورت، کاربران به جای دسترسی به جدول اصلی، به View دسترسی دارند که فقط ستون‌های مورد نیاز را نمایش می‌دهد.

ساخت View برای محدود کردن دسترسی به ستون‌ها

فرض کنید که می‌خواهید به گروه hr_team تنها دسترسی به ستون‌های name و address از جدول employees را بدهید و از دسترسی به ستون salary جلوگیری کنید. می‌توانید یک View ایجاد کنید که فقط این دو ستون را شامل شود:

CREATE VIEW employees_view AS
SELECT name, address
FROM employees;

سپس دسترسی به این View را به گروه hr_team اعطا کنید:

GRANT SELECT ON employees_view TO hr_team;

حالا گروه hr_team می‌تواند فقط داده‌های ستون‌های name و address را مشاهده کند و از داده‌های ستون salary بی‌خبر خواهند بود.

۴. استفاده از Row-Level Security (RLS) همراه با Column-Level Security

برای پیاده‌سازی یک سیاست امنیتی جامع و پیچیده‌تر، می‌توان از Row-Level Security (RLS) همراه با Column-Level Security استفاده کرد. در این حالت، علاوه بر محدود کردن دسترسی به ستون‌ها، می‌توانید دسترسی به ردیف‌های خاص داده‌ها را هم برای کاربران مختلف کنترل کنید.

به عنوان مثال، اگر بخواهید هم دسترسی به ردیف‌ها و هم دسترسی به ستون‌ها را مدیریت کنید، می‌توانید از ترکیب RLS و GRANT به‌طور هم‌زمان استفاده کنید. این امر به‌ویژه در شرایطی که داده‌ها باید برای کاربران مختلف از نظر دسترسی به سطح ردیف و ستون‌ها تفکیک شوند، مفید خواهد بود.

۵. محدودیت‌ها و چالش‌های Column-Level Security در PostgreSQL

در PostgreSQL، قابلیت Column-Level Security به‌طور مستقیم وجود ندارد، اما با استفاده از GRANT برای ستون‌ها و Views می‌توان این امنیت را پیاده‌سازی کرد. اما این روش‌ها نیز محدودیت‌هایی دارند:

• عملکرد: استفاده از Views برای محدود کردن دسترسی به ستون‌ها می‌تواند عملکرد را تحت تأثیر قرار دهد، به‌ویژه زمانی که تعداد زیادی از کاربران به Views دسترسی دارند.
• انعطاف‌پذیری: اعمال محدودیت‌های دقیق‌تر در سطح ستون‌ها ممکن است پیچیدگی‌های مدیریتی ایجاد کند و نیاز به برنامه‌ریزی دقیق داشته باشد.

جمع‌بندی

Column-Level Security یک قابلیت حیاتی برای مدیریت دقیق دسترسی‌ها در PostgreSQL است که به شما این امکان را می‌دهد که به‌طور انتخابی دسترسی کاربران به ستون‌های خاص در جداول را کنترل کنید. با استفاده از دستورات GRANT و REVOKE و همچنین Views می‌توان این سیاست‌ها را پیاده‌سازی کرد. این روش‌ها به‌ویژه برای محافظت از داده‌های حساس و اجرای سیاست‌های امنیتی دقیق در سطح ستون‌ها بسیار موثر هستند.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”استفاده از Policy‌های امنیتی سفارشی” subtitle=”توضیحات کامل”]Policy‌های امنیتی سفارشی یکی از ویژگی‌های پیشرفته PostgreSQL است که به شما این امکان را می‌دهد تا کنترل دقیق‌تری بر دسترسی کاربران و نحوه تعامل آنها با داده‌ها داشته باشید. این سیاست‌ها به شما این امکان را می‌دهند که به‌طور خاص و با جزئیات بیشتر، قوانینی را برای مدیریت دسترسی به داده‌ها و انجام عملیات‌های مختلف اعمال کنید.

در PostgreSQL، سیاست‌های امنیتی سفارشی به‌ویژه برای پیاده‌سازی Row-Level Security (RLS) و کنترل دقیق‌تر دسترسی به داده‌ها استفاده می‌شود. این سیاست‌ها به شما این امکان را می‌دهند که بر اساس شرایط خاصی، دسترسی به ردیف‌های خاص یک جدول را مدیریت کنید.

در این بخش، نحوه ایجاد و استفاده از Policy‌های امنیتی سفارشی در PostgreSQL را توضیح خواهیم داد.

۱. مقدمه‌ای بر Row-Level Security (RLS)

Row-Level Security (RLS) به شما این امکان را می‌دهد که دسترسی به ردیف‌های خاص یک جدول را براساس شرایط مشخص محدود کنید. این شرایط می‌تواند شامل نقش‌ها، ویژگی‌های کاربر، داده‌های موجود در ردیف‌ها یا ترکیبی از این موارد باشد.

برای ایجاد و مدیریت این سیاست‌ها در PostgreSQL، شما باید ابتدا Row-Level Security را فعال کنید و سپس Policy‌های امنیتی سفارشی را برای اعمال دسترسی‌ها و محدودیت‌ها به ردیف‌ها و داده‌ها تعریف کنید.

۲. فعال‌سازی Row-Level Security

قبل از اینکه بتوانید سیاست‌های RLS را برای یک جدول پیاده‌سازی کنید، باید Row-Level Security را برای آن جدول فعال کنید. برای این کار، از دستور زیر استفاده می‌کنید:

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

این دستور قابلیت RLS را برای جدول فعال می‌کند. بعد از فعال‌سازی این ویژگی، همه دسترسی‌ها به داده‌ها محدود به سیاست‌های تعریف‌شده برای RLS خواهند شد.

۳. ایجاد Policy‌های امنیتی سفارشی

برای اعمال محدودیت‌های امنیتی سفارشی، باید از POLICY استفاده کنید که به شما این امکان را می‌دهد که سیاست‌های خاصی را برای دسترسی به داده‌ها بر اساس شرایط دلخواه ایجاد کنید.

ساختار کلی دستور CREATE POLICY

CREATE POLICY policy_name
    ON table_name
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    TO role_name
    USING (expression)
    WITH CHECK (expression);

• policy_name: نام سیاست امنیتی.
• table_name: نام جدول.
• FOR: نوع عملیاتی که این سیاست اعمال می‌شود (مثل SELECT، INSERT، UPDATE، DELETE).
• TO: نقش‌هایی که این سیاست به آنها اختصاص داده می‌شود.
• USING: شرایطی که باید برای انجام عملیات‌های مشخص روی ردیف‌ها برآورده شود.
• WITH CHECK: شرایطی که باید هنگام انجام عملیات‌هایی مانند INSERT یا UPDATE رعایت شوند.

مثال‌هایی از استفاده از Policy‌های سفارشی

1. سیاست برای محدود کردن دسترسی به ردیف‌ها بر اساس نقش کاربر:فرض کنید که یک جدول employees دارید و می‌خواهید فقط کاربران با نقش admin به همه ردیف‌ها دسترسی داشته باشند، اما کاربران با نقش hr تنها بتوانند ردیف‌هایی را ببینند که در آن‌ها نام کارمند خودشان وجود دارد.ابتدا RLS را فعال کنید:

   ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

   سپس یک سیاست برای دسترسی به ردیف‌ها بر اساس نقش کاربر ایجاد کنید:

   CREATE POLICY hr_access_policy
       ON employees
       FOR SELECT
       TO hr
       USING (employee_id = current_setting('myapp.current_employee_id')::int);

   در اینجا، current_setting('myapp.current_employee_id') یک متغیر تنظیم‌شده است که به‌طور داینامیک شناسه کاربر جاری را ذخیره می‌کند. کاربران با نقش hr فقط می‌توانند ردیف‌هایی را که شناسه کارمندشان با employee_id تطابق دارد مشاهده کنند.

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

   CREATE POLICY update_own_data
       ON employees
       FOR UPDATE
       TO hr
       USING (employee_id = current_setting('myapp.current_employee_id')::int)
       WITH CHECK (employee_id = current_setting('myapp.current_employee_id')::int);

   در این سیاست، علاوه بر اینکه برای مشاهده ردیف‌ها از شرط USING استفاده می‌شود، برای اعمال تغییرات در داده‌ها نیز از شرط WITH CHECK استفاده شده است تا کاربران نتوانند اطلاعات دیگران را تغییر دهند.

3. سیاست برای دسترسی محدود به ردیف‌ها برای مدیران فقط:اگر بخواهید فقط مدیران به تمامی ردیف‌های جدول employees دسترسی داشته باشند، می‌توانید یک سیاست ایجاد کنید که این دسترسی را به نقش admin محدود کند:

   CREATE POLICY admin_access_policy
       ON employees
       FOR ALL
       TO admin
       USING (TRUE);

   در اینجا، سیاست به‌طور کلی برای تمامی عملیات‌ها (ALL) و برای نقش admin اعمال می‌شود. شرط USING (TRUE) به این معنی است که هیچ محدودیتی برای دسترسی به داده‌ها برای این نقش وجود ندارد.

۴. اعمال و بررسی Policy‌های امنیتی

پس از تعریف سیاست‌های امنیتی، PostgreSQL به‌طور خودکار این سیاست‌ها را برای کاربران و نقش‌ها اعمال خواهد کرد. می‌توانید وضعیت سیاست‌های RLS را برای هر جدول با استفاده از دستور زیر بررسی کنید:

SELECT * FROM pg_policies WHERE tablename = 'employees';

این دستور به شما کمک می‌کند تا اطلاعات مربوط به سیاست‌های اعمال‌شده برای جدول employees را مشاهده کنید.

۵. لغو سیاست‌های امنیتی سفارشی

در صورت لزوم، می‌توانید سیاست‌های امنیتی را لغو کنید. برای این کار از دستور DROP POLICY استفاده می‌شود:

DROP POLICY policy_name ON table_name;

جمع‌بندی

استفاده از Policy‌های امنیتی سفارشی در PostgreSQL یکی از روش‌های موثر برای مدیریت دسترسی دقیق‌تر به داده‌ها و ردیف‌ها است. با استفاده از این قابلیت، می‌توانید سیاست‌های امنیتی سفارشی‌سازی شده برای هر جدول و عملیات مختلف ایجاد کنید که این امکان را فراهم می‌کند که بر اساس شرایط خاص، دسترسی به داده‌ها محدود شود. استفاده از این سیاست‌ها، به‌ویژه در پیاده‌سازی Row-Level Security (RLS)، می‌تواند امنیت داده‌ها را به‌طور چشمگیری افزایش دهد و از دسترسی‌های غیرمجاز جلوگیری کند.[/cdb_course_lesson][cdb_course_lesson title=”فصل 4: رمزنگاری داده‌ها”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”رمزنگاری داده‌ها در سطح ستون” subtitle=”توضیحات کامل”]در برخی از سناریوهای امنیتی، ممکن است نیاز داشته باشید که داده‌ها را به‌طور خاص در سطح ستون رمزنگاری کنید تا از دسترسی غیرمجاز به اطلاعات حساس جلوگیری شود. PostgreSQL به‌طور پیش‌فرض از رمزنگاری در سطح ستون پشتیبانی نمی‌کند، اما با استفاده از افزونه‌هایی مانند pgcrypto، می‌توان این ویژگی را به راحتی پیاده‌سازی کرد. در این بخش، نحوه استفاده از افزونه pgcrypto برای رمزنگاری و رمزگشایی داده‌ها در سطح ستون و همچنین روش استفاده از AES برای رمزنگاری داده‌ها توضیح داده خواهد شد.

۱. نصب افزونه pgcrypto

برای استفاده از قابلیت‌های رمزنگاری در PostgreSQL، ابتدا باید افزونه pgcrypto را نصب و فعال کنید. این افزونه به شما امکانات متعددی برای رمزنگاری داده‌ها در سطح ستون فراهم می‌کند. برای نصب این افزونه، از دستور زیر استفاده می‌کنید:

CREATE EXTENSION pgcrypto;

این دستور افزونه pgcrypto را برای پایگاه داده فعال می‌کند.

۲. استفاده از AES برای رمزنگاری داده‌ها

یکی از محبوب‌ترین الگوریتم‌های رمزنگاری برای رمزنگاری داده‌ها در سطح ستون، AES (Advanced Encryption Standard) است. با استفاده از pgcrypto، شما می‌توانید داده‌ها را با استفاده از AES رمزنگاری کرده و در صورت نیاز آنها را رمزگشایی کنید.

۲.۱ رمزنگاری داده‌ها با استفاده از AES

برای رمزنگاری داده‌ها با استفاده از الگوریتم AES، از تابع pgp_sym_encrypt در افزونه pgcrypto استفاده می‌شود. این تابع داده‌ها را با یک کلید مخفی (Password) رمزنگاری می‌کند.

ساختار کلی دستور رمزنگاری با AES به این صورت است:

SELECT pgp_sym_encrypt('plain_text', 'encryption_key') AS encrypted_data;

مثال:

فرض کنید می‌خواهید داده‌های یک نام کاربری را رمزنگاری کنید. می‌توانید از دستور زیر برای این کار استفاده کنید:

SELECT pgp_sym_encrypt('john_doe', 'secret_key') AS encrypted_username;

این دستور، مقدار john_doe را با استفاده از کلید secret_key رمزنگاری می‌کند.

۲.۲ ذخیره داده‌های رمزنگاری شده در پایگاه داده

برای ذخیره داده‌های رمزنگاری‌شده در یک جدول، ابتدا باید جدول مورد نظر را ایجاد کرده و سپس داده‌های رمزنگاری‌شده را در آن ذخیره کنید.

مثال:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username BYTEA,  -- نوع داده BYTEA برای ذخیره داده‌های رمزنگاری‌شده
    email BYTEA
);

حال برای وارد کردن داده‌ها به این جدول با رمزنگاری AES، از دستور INSERT استفاده می‌کنید:

INSERT INTO users (username, email)
VALUES (
    pgp_sym_encrypt('john_doe', 'secret_key'),
    pgp_sym_encrypt('john@example.com', 'secret_key')
);

۲.۳ رمزگشایی داده‌ها با استفاده از AES

برای رمزگشایی داده‌ها، از تابع pgp_sym_decrypt استفاده می‌شود. این تابع به شما این امکان را می‌دهد که داده‌های رمزنگاری‌شده را به متن اصلی خود بازگردانید.

ساختار کلی دستور رمزگشایی به صورت زیر است:

SELECT pgp_sym_decrypt(encrypted_data, 'encryption_key') AS decrypted_data;

مثال:

برای رمزگشایی نام کاربری ذخیره‌شده در جدول users:

SELECT pgp_sym_decrypt(username, 'secret_key') AS decrypted_username
FROM users
WHERE id = 1;

در اینجا، داده‌های رمزنگاری‌شده از ستون username رمزگشایی می‌شوند و به صورت متنی مانند john_doe نمایش داده می‌شود.

۳. مزایای رمزنگاری داده‌ها در سطح ستون

• حفظ حریم خصوصی اطلاعات حساس: رمزنگاری در سطح ستون به شما این امکان را می‌دهد که داده‌های حساس مانند شماره‌های کارت اعتباری، اطلاعات شخصی یا داده‌های پزشکی را محافظت کنید.
• کنترل دقیق‌تر دسترسی: با رمزنگاری ستون‌های خاصی از جدول، می‌توانید کنترل دقیق‌تری بر دسترسی به این داده‌ها داشته باشید و فقط به کاربران مجاز اجازه دهید تا داده‌ها را مشاهده یا تغییر دهند.
• امنیت بالا: رمزنگاری AES به‌طور گسترده به‌عنوان یکی از امن‌ترین روش‌های رمزنگاری شناخته می‌شود و به شما اطمینان می‌دهد که داده‌های شما در برابر دسترسی غیرمجاز محافظت می‌شوند.

۴. بررسی عملکرد و هزینه‌های پردازشی رمزنگاری

در هنگام استفاده از رمزنگاری در سطح ستون، باید به هزینه‌های پردازشی مربوط به رمزنگاری و رمزگشایی داده‌ها توجه کنید. عملیات‌های رمزنگاری معمولاً منابع بیشتری مصرف می‌کنند و ممکن است عملکرد پایگاه داده را کاهش دهند. به‌ویژه هنگامی که داده‌های زیادی نیاز به رمزنگاری و رمزگشایی دارند، باید از نظر عملکرد و منابع سیستم این هزینه‌ها را ارزیابی کنید.

برخی از نکات مربوط به عملکرد:

• عملیات رمزنگاری و رمزگشایی معمولاً در مقایسه با عملیات‌های ساده SELECT یا INSERT هزینه بیشتری دارند.
• حجم داده‌ها: اگر داده‌های رمزنگاری‌شده بزرگ باشند، ممکن است تأثیر قابل‌توجهی بر عملکرد داشته باشد.
• کاهش عملکرد برای عملیات‌های JOIN: هنگامی که داده‌های رمزنگاری‌شده باید با سایر جداول جوین شوند، عملکرد ممکن است کاهش یابد زیرا رمزگشایی باید قبل از انجام عملیات جوین انجام شود.

جمع‌بندی

رمزنگاری داده‌ها در سطح ستون با استفاده از افزونه pgcrypto و الگوریتم‌های رمزنگاری مانند AES، راه‌حلی قوی برای محافظت از داده‌های حساس در PostgreSQL است. این قابلیت به شما این امکان را می‌دهد که داده‌ها را به‌صورت ایمن ذخیره کرده و تنها به کاربران مجاز اجازه دهید تا آنها را رمزگشایی کنند. همچنین باید به هزینه‌های پردازشی مربوط به عملیات رمزنگاری و رمزگشایی توجه کنید تا از تأثیر آن بر عملکرد پایگاه داده جلوگیری شود.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”رمزنگاری Transparent Data Encryption (TDE)” subtitle=”توضیحات کامل”]Transparent Data Encryption (TDE) یکی از روش‌های پیشرفته برای رمزنگاری داده‌ها در سطح پایگاه داده است که به‌طور خودکار داده‌های ذخیره‌شده در دیسک را رمزنگاری می‌کند. این روش برای محافظت از داده‌ها در برابر دسترسی غیرمجاز، به‌ویژه در شرایطی که دسترسی به دیسک فیزیکی ممکن است برای مهاجمین فراهم باشد، طراحی شده است.

در PostgreSQL، به‌طور پیش‌فرض از قابلیت TDE پشتیبانی نمی‌شود، اما می‌توان این نوع رمزنگاری را از طریق ابزارها و افزونه‌های خارجی، مانند pgcrypto یا encryption at the filesystem level، پیاده‌سازی کرد. این بخش به نحوه شبیه‌سازی رمزنگاری مشابه TDE در PostgreSQL پرداخته و برخی روش‌ها و تکنیک‌ها را برای ایجاد چنین سیستمی توضیح می‌دهد.

۱. مفاهیم پایه Transparent Data Encryption (TDE)

در TDE، فرآیند رمزنگاری برای تمام داده‌هایی که در پایگاه داده ذخیره می‌شوند، به‌طور شفاف و بدون نیاز به تغییر در برنامه‌های کاربردی یا تغییر در ساختار پایگاه داده انجام می‌شود. TDE معمولاً شامل رمزنگاری فایل‌های دیتابیس (مانند فایل‌های داده و لاگ) است که از حملات به اطلاعات در حالت “استراحت” جلوگیری می‌کند.

ویژگی‌های اصلی TDE عبارتند از:

• رمزنگاری خودکار داده‌ها: داده‌ها به‌طور خودکار رمزنگاری می‌شوند و برای کاربران نیاز به اعمال تغییرات در برنامه‌ها یا SQL ندارند.
• حفاظت از داده‌ها در حالت استراحت: داده‌ها به‌طور امن در دیسک ذخیره می‌شوند، حتی اگر مهاجم به دیسک فیزیکی دسترسی پیدا کند.
• شفاف بودن برای کاربران: کاربران نمی‌توانند تفاوتی در نحوه دسترسی به داده‌ها قبل و بعد از رمزنگاری مشاهده کنند.

۲. راهکارهای پیاده‌سازی TDE در PostgreSQL

چون PostgreSQL به‌طور پیش‌فرض از TDE پشتیبانی نمی‌کند، باید به روش‌های جایگزین برای پیاده‌سازی این قابلیت روی آورد. در اینجا دو روش معمول برای شبیه‌سازی TDE در PostgreSQL آورده شده است:

۲.۱. رمزنگاری در سطح سیستم فایل با استفاده از LUKS یا dm-crypt

یکی از ساده‌ترین و امن‌ترین روش‌ها برای پیاده‌سازی TDE در PostgreSQL، استفاده از رمزنگاری در سطح سیستم فایل است. این روش شامل رمزنگاری کل سیستم فایل (به‌ویژه دایرکتوری‌هایی که داده‌های PostgreSQL در آن ذخیره می‌شوند) است.

• LUKS (Linux Unified Key Setup) یکی از ابزارهای محبوب برای رمزنگاری سیستم‌های فایل است.
• dm-crypt به‌عنوان یک لایه رمزنگاری برای سیستم‌عامل لینوکس شناخته شده است.

با استفاده از این ابزارها، تمامی داده‌های ذخیره‌شده در دیسک، شامل داده‌های پایگاه داده PostgreSQL، به‌طور شفاف رمزنگاری می‌شوند. در این روش، برای رمزنگاری و رمزگشایی داده‌ها به هیچ‌گونه تغییر در پایگاه داده یا اپلیکیشن نیازی نیست.

مزایا:

• پیاده‌سازی نسبتاً ساده و سریع.
• شفاف بودن برای کاربران و بدون نیاز به تغییر در برنامه‌ها.
• تضمین حفاظت از داده‌ها در سطح دیسک.

معایب:

• نیاز به تنظیمات اضافی و حفظ کلیدهای رمزنگاری در یک مکان امن.
• تأثیر بر عملکرد در مقیاس بزرگ.

۲.۲. استفاده از افزونه‌های رمزنگاری برای داده‌های درون پایگاه داده

روش دیگر برای شبیه‌سازی TDE در PostgreSQL استفاده از افزونه‌هایی مانند pgcrypto است که به شما امکان رمزنگاری داده‌ها در سطح ستون را می‌دهد. این افزونه می‌تواند برای رمزنگاری داده‌ها به‌طور خودکار استفاده شود، اما این کار نیاز به پیاده‌سازی در سطح کد SQL دارد.

در این روش، شما باید برای هر ستونی که می‌خواهید رمزنگاری شود، از دستورات رمزنگاری مانند pgp_sym_encrypt برای رمزنگاری و pgp_sym_decrypt برای رمزگشایی استفاده کنید. با این حال، این روش به اندازه TDE واقعی شفاف نیست و نیاز به تغییرات در برنامه و ساختار پایگاه داده دارد.

مزایا:

• امکان رمزنگاری دقیق داده‌های حساس در سطح ستون.
• دسترسی به الگوریتم‌های رمزنگاری مختلف از جمله AES.

معایب:

• نیاز به پیاده‌سازی کدهای SQL برای هر ستون و مدیریت کلیدهای رمزنگاری.
• پیچیدگی بیشتر نسبت به رمزنگاری در سطح سیستم فایل.

۳. بررسی عملکرد و هزینه‌های پردازشی TDE

با پیاده‌سازی TDE، چه از طریق رمزنگاری در سطح سیستم فایل و چه از طریق رمزنگاری در سطح ستون، تأثیرات عملکردی وجود دارد که باید مدنظر قرار گیرد:

• هزینه پردازشی رمزنگاری: عملیات رمزنگاری و رمزگشایی داده‌ها معمولاً پردازش‌های سنگینی هستند که می‌توانند منجر به کاهش سرعت پردازش‌های I/O شوند. به‌ویژه هنگام دسترسی به داده‌ها از روی دیسک یا انجام عملیات‌های پیچیده.
• تأثیر بر تأخیر (Latency): اگر TDE در سطح سیستم فایل پیاده‌سازی شود، ممکن است تأثیرات بیشتری بر تأخیر داشته باشد زیرا داده‌ها باید قبل از خواندن یا نوشتن رمزنگاری و رمزگشایی شوند.
• بار اضافی در سرورها: رمزنگاری در مقیاس بزرگ می‌تواند نیاز به منابع اضافی داشته باشد، به‌ویژه در سرورهایی که با حجم بالای داده سر و کار دارند.

جمع‌بندی

در PostgreSQL، پیاده‌سازی Transparent Data Encryption (TDE) به‌طور پیش‌فرض پشتیبانی نمی‌شود، اما می‌توان از روش‌های جایگزین مانند رمزنگاری در سطح سیستم فایل با استفاده از ابزارهایی مانند LUKS یا dm-crypt، یا استفاده از افزونه‌هایی مانند pgcrypto برای شبیه‌سازی این ویژگی استفاده کرد. هر کدام از این روش‌ها مزایا و معایب خاص خود را دارند و باید با توجه به نیازهای امنیتی و عملکردی سیستم انتخاب شوند. TDE در نهایت ابزاری قدرتمند برای حفاظت از داده‌ها در برابر دسترسی غیرمجاز است و می‌تواند به شما اطمینان دهد که اطلاعات حساس در هنگام ذخیره‌سازی در دیسک ایمن خواهند ماند.[/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”بررسی عملکرد و هزینه‌های پردازشی رمزنگاری” subtitle=”توضیحات کامل”]

[/cdb_course_lesson][cdb_course_lesson title=”فصل 5: مدیریت دسترسی شبکه”][/cdb_course_lesson][cdb_course_lesson icon=”fas fa-arrow-alt-circle-down” badge=”lecture” private_lesson=”true” title=”محدودسازی دسترسی به پایگاه داده از طریق فایروال” subtitle=”توضیحات کامل”]

# فقط به آدرس IP 192.168.1.100 اجازه دسترسی به پورت 5432 (PostgreSQL) داده شود
sudo iptables -A INPUT -p tcp -s 192.168.1.100 --dport 5432 -j ACCEPT

# دسترسی به پورت 5432 را از سایر آدرس‌ها مسدود کنید
sudo iptables -A INPUT -p tcp --dport 5432 -j REJECT

sudo ufw allow from 192.168.1.100 to any port 5432

sudo ufw deny 5432