آموزش پستگرس‌ کیوال

آنچه در این صفحه می خوانید:

معرفی پستگرس‌ کیوال (PostgreSQL)

پستگرس‌ کیوال (PostgreSQL) یک سیستم مدیریت پایگاه داده رابطه ای رایگان و اوپن سورس (RDBMS) است که بر توسعه پذیری و انطباق استانداردهای فنی تأکید دارد. این سیستم برای رسیدگی به طیف وسیعی از بارهای کاری، از دستگاه های تک کاربره گرفته تا انبارهای داده یا وب سرویس ها با کاربران همزمان زیاد طراحی شده است. PostgreSQL، همچنین به عنوان Postgres شناخته می شود،به هیچ شرکتی یا سایر نهادهای خصوصی تعلق ندارد و تحت کنترل آنها نیست و دسترسی به سورس آن رایگان است. طراحی پستگرس‌ کیوال براساس POSTGRES 4.2 در بخش علوم کامپیوتر دانشگاه برکلی کالیفرنیا تکمیل شده است. این پایگاه داده پیش فرض برای macOS Server است و بر روی سیستم عامل های متعددی شامل لینوکس، اکثر برنامه های کاربردی یونیکس، ویندوز، MacOS X ،Solaris ،Tru64T ،FreeBSD ،OpenBSD اجرا می شود.

پستگرس‌کیوال از متن، تصاویر، صدا و ویدئو پشتیبانی می کند و شامل رابط های برنامه نویسی برای سی و سی پلاس پلاس، جاوا، پرل، پایتون، روبی، Tcl و ODBC است. اگر شما برنامه های خود را مبتنی بر PostgreSQL توسعه می دهید، هزینه پرداختی در مقایسه با سایر سیستم های مدیریت پایگاه داده بسیار کمتر است. PostgreSQL اولین سیستم مدیریت پایگاه داده است که چندین نسخه کنترل همزمان (MVCC) را حتی قبل از اوراکل اجرا می کند. PostgreSQL طراحی شده است تا قابل گسترش باشد. در PostgreSQL شما می توانید نوع داده های خود، انواع شاخص ها، زبان های عملکردی و غیره را تعریف کنید.

اگر هیچ بخشی از سیستم را دوست نداشته باشید، می توانید همیشه یک افزونه سفارشی برای ارتقاء آن به منظور برآورده ساختن نیازهای خود، مثلا اضافه کردن یک بهینه ساز جدید بسازید. شما همیشه می توانید از انجمن PostgreSQL برای موضوعاتی که ممکن است در هنگام کار با PostgreSQL داشته باشید، استفاده کنید. علاوه بر PostgreSQL تمامی سیستم های مدیریت پایگاه داده رابطه ای (RDMS) مانند مای اس کیوال (MySQL)، اس کیو لایت (SQLite)،MS Access، اوراکل (Oracle)، Sybase، Informix و اس کیوال سرور (SQL Server) از SQL به عنوان زبان پایگاه داده استاندارد خود استفاده می کنند. اس کیوال (SQL) زبان استاندارد برای سیستم پایگاه داده رابطه ای است.

تاریخچه پستگرس‌ کیوال (PostgreSQL)

PostgreSQL از پروژه Ingres در دانشگاه کالیفرنیا، برکلی تکامل یافت. در سال 1982، رهبر تیم Ingres، مایکل استون برکر، برکلی را ترک کرد تا نسخه اختصاصی اینگرس را تهیه کند. وی در سال 1985 به برکلی بازگشت و یک پروژه post-Ingres را برای برطرف کردن مشکلات سیستم های پایگاه داده معاصر که در اوایل دهه 1980 به طور فزاینده ای مشخص شده بود، آغاز کرد. وی برای این پروژه ها و پروژه های دیگر در سال 2014 جایزه تورینگ را بدست آورد و تکنیک هایی که در آنها پیشگام بود.

در سال 1996، این پروژه به PostgreSQL تغییر نام داد تا منعکس کننده پشتیبانی آن از SQL باشد. حضور آنلاین در وب سایت PostgreSQL.org در تاریخ 22 اکتبر 1996 آغاز شد. اولین نسخه PostgreSQL نسخه 6.0 را در 29 ژانویه 1997 تشکیل داد. از آن زمان توسعه دهندگان و داوطلبان در سراسر جهان این نرم افزار را به عنوان گروه توسعه جهانی PostgreSQL تشکیل داده اند.

MVCC چیست؟

PostgreSQL با کنترل همزمان چند نسخه (MVCC)، که به هر معامله یک "عکس فوری" از پایگاه داده می دهد، اجازه می دهد تا بدون تأثیرگذاری بر سایر معاملات، تغییرات ایجاد شود. این امر تا حد زیادی نیاز به قفل خواندن را از بین می برد و تضمین می کند که بانک اطلاعات اصول ACID را حفظ می کند. PostgreSQL سه سطح از جداسازی معامله را ارائه می دهد: خواندن تعهد، تکرار خواندن و Serializable. از آنجا که PostgreSQL از خواندن کثیف مصون است، درخواست سطح جدا سازی تراکنش خوانده نشده به جای آن، خواندن متعهد را فراهم می کند. PostgreSQL از روش جداسازی عکس فوری سریالیزاسیون (SSI) از سریال سازی کامل پشتیبانی می کند.

معرفی Pgadmin

PgAdmin یک برنامه رابط کاربری رایگان با اوپن سورس PostgreSQL برای سیستم عامل Microsoft Windows،Mac OS X و Linux است. این برنامه بازیابی اطلاعات سرور بانک اطلاعاتی، توسعه، تست و نگهداری مداوم را ارائه می دهد. pgAdmin یک پروژه جامعه ای است که از تعدادی داوطلب از سراسر جهان تشکیل شده است. pgadmin ابزاری با هدف کلی برای طراحی، نگهداری و اجرای بانک های اطلاعاتی Postgres است.

امکانات آن شامل:

  • ورود SQL دلخواه
  • مرورگرهای اطلاعات برای پایگاه داده ها، جداول، فهرست ها، توالی ها، نمایش ها، محرک ها، عملکردها و زبان ها
  • دیالوگ های پیکربندی کاربر، گروه و Privilege
  • ردیابی نسخه با تولید نسخه بروزرسانی اسکریپت
  • تنظیمات جدول مایکروسافت MSysConf
  • ویزارد ایمپورت و اکسپورت داده ها
  • ویزارد Migration پایگاه داده
  • گزارش های از پیش تعریف شده در پایگاه داده ها، جداول، ایندکس ها، توالی ها، زبان ها و ویوو ها

ویژگی های پستگرس‌ کیوال (PostgreSQL)

PostgreSQL دارای بسیاری از ویژگی های پیشرفته است که سایر سیستم های مدیریت پایگاه داده سازمانی ارائه می دهند، مانند:

  • تایپ تعریف شده توسط کاربر
  • وراثت جدول
  • مکانیسم قفل پیشرفته
  • تمامیت ارجاعی کلید خارجی
  • دیدگاه ها، قوانین، اشیا فرعی
  • معاملات تودرتو (پس انداز)
  • کنترل همزمان چند نسخه (MVCC)
  • تکثیر ناهمزمان

نسخه های اخیر PostgreSQL از ویژگی های زیر پشتیبانی می کند:

  • نسخه سرور مایکروسافت ویندوز
  • جداول
  • بازیابی نقطه به موقع

و در هر نسخه جدید ویژگی های جدید دیگری اضافه می شود.

امنیت در پستگرس‌ کیوال (PostgreSQL)

PostgreSQL امنیت داخلی خود را بر اساس هر نقش مدیریت می کند. به طور کلی یک نقش به عنوان یک کاربر (نقشی که می تواند وارد سیستم شود) یا یک گروه (نقشی است که سایر نقش ها عضو هستند) در نظر گرفته می شود. مجوزها را می توان در هر موردی به سطح ستون اعطا یا ابطال کرد، همچنین می تواند از ایجاد اشیاء جدید در سطح دیتابیس، شماتیک یا جدول مجوز جلوگیری کند.

ویژگی SECURITY LABEL PostgreSQL (گسترش به استانداردهای SQL)، امنیت بیشتری را فراهم می آورد. با یک ماژول قابل دانلود همراه است که از کنترل دسترسی اجباری مبتنی بر برچسب (MAC) مبتنی بر سیاست امنیتی Security Enhanced Linux (SELinux) پشتیبانی می کند.

PostgreSQL بصورت بومی تعداد زیادی مکانیزم تأیید اعتبار خارجی را پشتیبانی می کند، از جمله:

  • پسورد: SCRAM-SHA-256 (از زمان PostgreSQL 10)، MD5 یا متن ساده
  • رابط برنامه برنامه های خدمات عمومی امنیتی (GSSAPI)
  • رابط ارائه دهنده پشتیبانی امنیتی (SSPI)
  • Kerberos
  • ident (نام کاربر O / S همانطور که توسط یک سرور شناسایی به نام کاربر در پایگاه داده ارائه شده است)
  • peer (نقشه نام محلی کاربر به نام کاربر بانک اطلاعاتی نقشه برداری می کند)
  • پروتکل دسترسی به فهرست سبک (LDAP)
    • اکتیو دایرکتوری (AD)
  • RADIUS
  • Certificate
  • ماژول احراز هویت قابل نصب (PAM)

روش های GSSAPI، SSPI، Kerberos، peer، ident و Certificate همچنین می توانند از فایل "نقشه" مشخص شده استفاده كنند كه لیست در آن قرار دارد كه کاربران با آن سیستم تأیید اعتبار مطابقت دارند و به عنوان یك کاربر خاص در پایگاه داده مجاز به اتصال هستند.

این روش ها در فایل تنظیمات تأیید هویت مبتنی بر میزبان خوشه (pg_hba.conf) مشخص شده است، که تعیین می کند چه اتصالی مجاز است. این اجازه می دهد تا کنترل کنید که کاربر می تواند به کدام بانک اطلاعاتی متصل شود، از کجا می تواند از طریق آن (آدرس IP، محدوده آدرس IP، سوکت دامنه)، کدام سیستم تأیید صحت اجرا شود، و اینکه آیا این اتصال باید از Security Layer Security (TLS) استفاده کند، استفاده کند.

معیارها و عملکرد پستگرس‌ کیوال (PostgreSQL)

پیکربندی پیش فرض PostgreSQL فقط از مقدار کمی حافظه اختصاصی برای اهداف مهم عملکرد مانند ذخیره بلوک های پایگاه داده و مرتب سازی استفاده می کند. این محدودیت در درجه اول به این دلیل است که سیستم عامل های قدیمی نیاز به تغییر هسته دارند تا بتوانند بلوک های بزرگی از حافظه مشترک را تخصیص دهند.

معماری پستگرس‌ کیوال (PostgreSQL)

معماری پستگرس کیوال

پردازش داده ها به شرح زیر انجام می شود:

اتصال به نمونه

برنامه های Client، که روی سرور متفاوت از نمونه کار می کنند، از پروتکل IP برای اتصال به آن استفاده می کنند. اگر برنامه کلاینت و به عنوان مثال در همان سرور اجرا شوند، روش اتصال مشابه امکان پذیر است. همچنین استفاده از اتصال از طریق سوکت محلی نیز امکان پذیر است. در مرحله اول، برنامه به فرآیند postmaster متصل می شود. postmaster حقوق برنامه را بررسی می کند و در صورت موفقیت، یک فرآیند Postgres جدید را آغاز کرده و آن را با برنامه مشتری متصل می کند.

دسترسی به داده ها

فرایندهای کلاینت، داده ها را به عنوان نمونه ارسال و درخواست می کنند. به دلیل نوع عملکرد، به عنوان مثال داده های درخواست شده را مستقیماً از فایل های روی دیسک خواندن و نوشتن نمی کند. در عوض، آنها را در یک مکان حافظه مشترک که به آن بافر مشترک گفته می شود، اصطلاحا بافر می کند. برای انجام درخواست کلاینت، فرایند Postgres مربوطه روی بافرهای مشترک و بافرهای WAL عمل می کند و محتویات آنها را دستکاری می کند. وقتی کلاینت درخواستی را COMMIT می کند، فرایند WAL Writer همه سوابق WAL حاصل از این معامله را در فایل WAL می نویسد و پخش می کند. از آنجا که فایل WAL، بر خلاف فایل های داده، کاملاً متوالی نوشته شده است، این عملیات نسبتاً سریع است. پس از آن مشتری تأییدیه COMMIT خود را دریافت می کند. در این مرحله، بانک اطلاعاتی متناقض است، به این معنی که بین بافرهای مشترک و فایل های داده مربوطه تفاوت هایی وجود دارد.

بطور دوره ای فرآیند نوشتن پس زمینه بافرهای مشترک را برای صفحات "Dirty" بررسی می کند و آنها را در فایل های داده مناسب می نویسد. صفحات "Dirty" صفحاتی هستند که محتوای آنها پس از انتقال آنها از دیسک به حافظه توسط یکی از مراحل Postgres اصلاح شده است. روند بازرسی نیز به صورت دوره ای انجام می شود. با شروع، مانع از تغییر بیشتر بافر می شود، فرایند نوشتن پس زمینه را مجبور به نوشتن و ریختن تمام صفحات "Dirty" می کند و نویسنده WAL را مجبور می کند که یک فایلCHECKPOINT را در فایل WAL بنویسد و پس از آن پایگاه داده سازگار می باشد، این بدان معنی است : الف) محتوای بافرهای اشتراکی همانند داده های موجود در فایل ها است. ب) کلیه تغییرات بافرهای WAL به فایل های WAL نوشته می شوند و ج) داده های جدول با داده های فهرست ارتباط دارند. این ثبات هدف از بازرسی ها است. در اصل، نمونه شامل حداقل سه فرآیند نویسنده WAL، نویسنده پس زمینه و بازرسی و یک پردازشگر Postgres برای هر اتصال است. در بیشتر موارد، چندین فرآیند در حال اجرا است.

فرآیندهای اختیاری

فرآیند پرتاب خلاء تعدادی از فرآیندهای کارگر را شروع می کند. آنها نسخه های سطر سطحی را مطابق معماری MVCC PostgreSQL حذف می کنند. این کار در حافظه مشترک انجام می شود و صفحات "Dirty" نوشته می شوند تا مانند سایر صفحات "Dirty" مانند صفحاتی که ناشی از تغییر داده ها توسط مشتری ها هستند، ذخیره شوند. فرآیند logger پیام های ورود، هشدار و خطا را در فایل log (و نه به فایل WAL!) می نویسد. فرایند بایگانی کننده فایل های WAL را که کاملاً توسط نویسنده WAL پر شده است، در یک مکان قابل تنظیم برای ذخیره سازی میان دوره ای کپی می کند. فرآیند جمع آوری آمار به طور مداوم اطلاعات مربوط به تعداد دسترسی به جداول و شاخص ها، تعداد کل ردیف ها در جداول را جمع آوری می کند و با هماهنگی با VACUUM / ANALYZE و ANALYZE کار می کند.

فرآیندهای گیرنده WAL و گیرنده WAL بخشی از ویژگی Streaming Replication هستند. آنها با دور زدن فایل های WAL روی دیسک، اطلاعات مربوط به تغییرات در سرور اصلی را تبادل می کنند. از آنجا که از نسخه 9.6 امکان اجرای نمایش داده ها به صورت موازی بر روی چندین CPU وجود دارد. در این حالت، آن بخش از برنامه، که به طور موازی اجرا می شود، توسط فرآیندهای اضافی کارگر پس زمینه اجرا می شوند. آنها به همان روشی که Postgres اصلی پردازش می کند به بافرهای مشترک دسترسی پیدا کرده و همزمان با صفحات بافر مختلف اداره می شوند.

مقایسه پستگرس‌ کیوال (PostgreSQL) با MySQL

معرفی مای اس کیوال (MySQL)

مای اس کیو ال نوعی سیستم مدیریت پایگاه داده‌ های اوپن سورس و رابطه ای می باشد که امکانات زیادی را برای کاربران فراهم می کند. نام آن ترکیبی از "My"، نام دختر بنیانگذار مایکل Widenius ، و "SQL" است که مخفف Structured Query Language است. در واقع، یک سرور چند کاربره و چند ریسمانه است. MySQL همچنین توسط بسیاری از وب سایت های محبوب از جمله فیسبوک، فلیکر، مدیاویکی، توییتر و یوتیوب استفاده می شود. علاوه بر MySQL تمامی سیستم های مدیریت پایگاه داده رابطه ای (RDMS) مانند، MS Access، اوراکل، Sybase، Informix، پستگرس کیوال، SQLite و اس کیو ال سرور از SQL به عنوان زبان پایگاه داده استاندارد خود استفاده می کنند. اس کیو ال زبان استاندارد برای سیستم پایگاه داده رابطه ای است.

چه کسانی از این پایگاه داده ها استفاده می کند؟

در اینجا چند نمونه از شرکت هایی که از این بانک های اطلاعاتی استفاده می کنند آورده شده است:

  • PostgreSQL: اپل، BioPharm،Etsy،IMDB،Macworld، دبیان، فوجیتسو، رد هت، سان میکروسیستم، سیسکو، اسکایپ.
  • MySQL: نیروی دریایی ایالات متحده، GitHub، ناسا، تسلا، نتفلیکس، WeChat، فیس بوک، زندزک، توییتر، Zappos، یوتیوب، Spotify.

ساختار بانک اطلاعاتی چیست؟

  • PostgreSQL: یک سیستم مدیریت پایگاه داده شی-رابطه ای (ORDBMS) با تأکید بر توسعه پذیری و انطباق استانداردها است. PostgreSQL سازگار با ACID، معامله گر است، دارای نماها، به روزرسانی ها و کلیدهای خارجی به روز شده و تحقق یافته است. همچنین از توابع و مراحل ذخیره شده پشتیبانی می کند. PostgreSQL از جداول، محدودیت ها، محرک ها، نقش ها، رویه ها و نمایش های ذخیره شده به عنوان مؤلفه های اصلی کار با شما استفاده می کند. یک جدول از ردیف ها تشکیل شده است و هر ردیف شامل ستون های یکسانی است. PostgreSQL از کلیدهای اصلی برای شناسایی منحصر به فرد هر سطر (ضبط a.k.a.) در یک جدول و کلیدهای خارجی برای اطمینان از یکپارچگی مرجع بین دو جدول مرتبط استفاده می کند. شایان ذکر است که PostgreSQL همچنین از بسیاری از ویژگی های NoSQL پشتیبانی می کند.
  • MySQL: MySQL یک سیستم مدیریت پایگاه داده رابطه ای اوپن سورس (RDBMS) است. دقیقاً مانند PostgreSQL و سایر بانک های اطلاعاتی رابطه ای برای آن موضوع، MySQL از جداول به عنوان یک جزء اصلی استفاده می کند و کمتر از همان ویژگی تنظیم شده با PostgreSQL استفاده می کند. برای اینکه منصفانه باشید، PostgreSQL را در مقابل MySQL نگذارید. به طوری که همه چیز درباره PostgreSQL بسیار عالی است، شایان ذکر است که نسخه های جدیدتر MySQL (5.7+) از برخی از ویژگی های NoSQL نیز پشتیبانی می کنند.

عملکرد: آیا ایندکس ها لازم هستند؟

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

  • PostgreSQL: PostgreSQL شامل پشتیبانی داخلی برای نمایه های منظم درخت B و هش است. فهرست های PostgreSQL همچنین از ویژگی های زیر پشتیبانی می کنند:
  1. شاخص های بیان: به جای صرفاً یک مقدار ستون، می توان با یک شاخص نتیجه یک عبارت یا عملکرد ایجاد کرد.
  2. فهرست های جزئی: فقط بخشی از جدول را فهرست بندی کنید.
  • MySQL: اکثر شاخصهای MySQL (PRIMARY KEY،UNIQUE،INDEX و FULLTEXT) در درختان B ذخیره می شوند. استثنائات شامل ایندکس در انواع داده های مکانی است که از درختان R استفاده می کنند. MySQL همچنین از شاخص های هش پشتیبانی می کند و موتور InnoDB از لیست های معکوس برای شاخص های FULLTEXT استفاده می کند.
    نظرتون درباره این نوشته چیه؟ عالیه بد نیست خوب نبود