لوگوی پایگاه داده PostgreSQL با طرح مدرن
برنامه نویسی, پایگاه داده

چگونه تمام اسکیماها (Schemas) را در پایگاه داده PostgreSQL فهرست کنیم؟

توسط . ارسال شده در

PostgreSQL، یک سامانه مدیریت پایگاه داده شیء-رابطه‌ای، از SQL برای کوئری‌زنی داده‌های رابطه‌ای و JSON برای داده‌های غیررابطه‌ای پشتیبانی می‌کند. این انعطاف‌پذیری به شما کمک می‌کند تا انواع داده‌های پیچیده را برای برنامه‌هایی از اپلیکیشن‌های وب تا سیستم‌های سازمانی مدیریت کنید.

مدیریت مؤثر اسکیمای پایگاه داده برای استفاده کامل از قابلیت‌های PostgreSQL در توسعه برنامه‌ها ضروری است. با مدیریت صحیح اسکیمای پایگاه داده، می‌توانید یکپارچگی داده‌ها را تضمین و مقیاس‌پذیری سیستم‌های پیچیده را بهبود دهید.

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

اسکیمای PostgreSQL چیست؟

اسکیمای PostgreSQL، کانتینرهای منطقی هستند که به شما امکان می‌دهند اشیاء پایگاه داده مانند جدول‌ها، نماها، نوع‌های داده و عملگرها را سازماندهی کنید. اشیائی مانند نماها، نماهای مادی، جدول‌ها، توالی‌ها و جدول‌های خارجی باید در داخل یک اسکیمای مشخص نام‌های یکتا داشته باشند.

اگرچه اشیاء از یک نوع نمی‌توانند در یک اسکیمای واحد نام مشترک داشته باشند، اما نام‌های یکسان می‌توانند در اسکیمای مختلف وجود داشته باشند. به عنوان مثال، هم schema_info و هم my_schema می‌توانند جدولی با نام my_table داشته باشند بدون هیچ تداخلی.

هر پایگاه داده به طور پیش‌فرض یک اسکیمای عمومی دارد. شما همچنین می‌توانید با استفاده از دستور CREATE SCHEMA اسکیمای جدید با مالکیت و دسترسی مشخص ایجاد کنید. اگر دیگر نیازی به اسکیمایی نداشتید، با دستور DROP SCHEMA آن را حذف کنید؛ اگر اسکیمای حاوی اشیاء بود، گزینه CASCADE را اضافه کنید.

برای ایجاد یا دسترسی به اشیاء داخل یک اسکیمای مشخص، از نام کاملاً مشخص استفاده کنید:

schema.object

مثال:

my_schema.my_table

برای ایجاد my_table در داخل my_schema:

CREATE TABLE my_schema.my_table (
id INT,
name TEXT
);

از آنجا که نوشتن نام‌های کاملاً مشخص خسته‌کننده است، PostgreSQL به شما اجازه می‌دهد از نام‌های غیر مشخص (فقط نام جدول) استفاده کنید. مسیر جستجو (search path) به PostgreSQL می‌گوید هنگام ارجاع به یک شیء بدون پیشوند اسکیمای مشخص، کدام اسکیم‌ها را بررسی کند.

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

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

استفاده از information_schema

information_schema مجموعه‌ای از نماهای فقط‌خواندنی استاندارد ANSI-SQL است که متادیتای پایگاه داده جاری را ارائه می‌دهد.

SELECT schema_name
FROM information_schema.schemata;

نتیجه اجرای دستور SQL برای نمایش شِماها

استفاده از pg_catalog

pg_catalog اسکیمای کاتالوگ سیستم داخلی است و همیشه اولین مسیر جستجو است.

SELECT nspname AS schema_name
FROM pg_catalog.pg_namespace;

 

 

اجرای دستور SQL در پوسته پستگرس و خروجی آن

 

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

SELECT
schemata.schema_name,
schema_privileges.grantee,
schema_privileges.privilege_type
FROM
information_schema.schemata
LEFT JOIN
information_schema.schema_privileges
ON
schemata.schema_name = schema_privileges.schema_name
ORDER BY
schemata.schema_name,
schema_privileges.grantee;

LEFT JOIN تضمین می‌کند که اسکیم‌هایی که مجوز صریح ندارند نیز ظاهر شوند.

چگونه اسکیم‌ها را همراه با اندازه‌هایشان لیست کنیم؟

SELECT
nspname AS schema_name,
pg_size_pretty(
SUM(pg_total_relation_size(pg_class.oid))
) AS size
FROM pg_catalog.pg_namespace
JOIN pg_catalog.pg_class
ON pg_class.relnamespace = pg_namespace.oid
GROUP BY nspname;

pg_total_relation_size مصرف کل دیسک را محاسبه می‌کند و pg_size_pretty آن را قالب‌بندی می‌کند (KB، MB، GB).

چگونه تشخیص خودکار انحراف اسکیمای پایگاه داده می‌تواند ناسازگاری‌ها را جلوگیری کند؟

تشخیص انحراف اسکیمای پایگاه داده

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

چارچوب مفهومی تشخیص انحراف اسکیمای پایگاه داده

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

این سیستم‌ها گزارش‌های تفاوتی ایجاد می‌کنند که ستون‌های اضافه یا حذف شده، محدودیت‌های تغییر یافته یا ایندکس‌های مفقود را نشان می‌دهند. پیاده‌سازی‌های پیشرفته سیاست‌محور می‌توانند استقرارها را مسدود کرده یا دستورات DDL اصلاحی خودکار تولید کنند.

استراتژی‌های پیاده‌سازی برای تشخیص انحراف

  • Flyway Community Integration بررسی‌های انحراف را قبل از اعمال مهاجرت‌ها اجرا می‌کند. ابزار، مقایسه چک‌سام با تاریخچه مهاجرت انجام داده و گزارش‌های اختلاف سطح شیء تولید می‌کند.

  • StreamSets Synchronization Engine از پایپ‌لاین‌های اعلانی استفاده می‌کند که ساختار رکوردهای ورودی را اسکن کرده، جدول‌ها یا ستون‌های مفقود را شناسایی و دستورات DDL را خودکار تولید می‌کنند.

مزایا و کاربردهای عملی

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

روش‌های پیشرفته بازسازی آنلاین اسکیمای پایگاه داده

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

درک معماری pg_repack

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

جریان‌های کاری برای عملیات بدون وقفه

  • ایمنی تراکنشی: تمام تغییرات اسکیمای پایگاه داده درون تراکنش‌ها انجام می‌شوند. دستورات DDL مانند ALTER TABLE users ADD COLUMN mfa_secret TEXT با به‌روزرسانی آنی متادیتا کامل می‌شوند در حالی که برنامه آنلاین باقی می‌ماند.

  • قابلیت پردازش موازی: کار بازسازی روی چند هسته CPU با دستوری مانند pg_repack -j 8 -t shipments توزیع می‌شود و زمان بازسازی را کاهش می‌دهد.

  • الگوهای پیاده‌سازی سازمانی: پلتفرم‌های SaaS از بازسازی آنلاین برای کاهش فضای اشغال شده بدون وقفه استفاده می‌کنند. پروژه‌های مهاجرت قدیمی از این تکنیک‌ها برای تبدیل نوع داده‌ها مانند INT به BIGINT استفاده می‌کنند.

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

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

  • عملیات ایندکس همزمان:

CREATE INDEX CONCURRENTLY idx_schema_name ON my_table (column_name);
DROP INDEX CONCURRENTLY idx_old_schema;

  • تکثیر منطقی برای تغییرات اسکیمای پایگاه داده: نگهداری نسخه‌های همگام‌شده با ساختار متفاوت.

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

  • مدیریت اسکیمای مبتنی بر وضعیت: ابزارهایی مانند Atlas مهاجرت تفاضلی از طریق تعریف اسکیمای اعلامی ارائه می‌دهند:

schema "production" {
table "users" {
column "id" {
type = bigint
primary_key = true
}
column "email" {
type = varchar(255)
unique = true
}
}
}

  • جریان‌های کاری مبتنی بر GitOps: ابزارهایی مانند Bytebase، بررسی، تشخیص انحراف و کنترل دسترسی نقش‌بنیاد را فراهم می‌کنند.

شناسایی انحراف و بازسازی

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

لیست کردن تمام اسکیم‌ها با Python (psycopg2)

  1. نصب درایور:

pip install psycopg2

  1. نمونه اسکریپت:

import psycopg2

conn = psycopg2.connect(
dbname="postgres_database_name",
user="postgresDB_username",
password="postgresDB_password",
host="host_address",
port="port_number"
)

cur = conn.cursor()

cur.execute("""
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema', 'pg_catalog');
""")

schemas = cur.fetchall()

for schema in schemas:
print(schema[۰])

cur.close()
conn.close()

چرا لیست کردن اسکیم‌ها مفید است؟

  • درک ساختار پایگاه داده – دید سطح بالا برای مدیریت و مقیاس‌پذیری آسان‌تر

  • شناسایی اسکیمای موجود – مشاهده سریع اسکیم‌ها در محیط‌های چند اسکیمایی

  • کنترل دسترسی و مجوزها – بررسی یا تنظیم دسترسی‌ها

  • نظارت عملکرد – شناسایی ناکارآمدی‌های ذخیره‌سازی و بهینه‌سازی کوئری‌ها

  • دسترسی به ایندکس‌های جستجوی متنی – اطمینان از قرارگیری GIN یا GiST در اسکیمای مناسب

نتیجه‌گیری

اکنون با روش‌های مختلف برای لیست کردن اسکیمای PostgreSQL از طریق meta-command های psql، نماهای information_schema و جدول‌های pg_catalog آشنا شدید و همچنین می‌توانید جزئیاتی مانند مجوزها و اندازه‌ها را بازیابی کرده و حتی با Python این کار را خودکار کنید. روش‌های مدرن مانند تشخیص خودکار انحراف، بازسازی آنلاین اسکیمای پایگاه داده، مهاجرت بدون وقفه و مدیریت اسکیمای اعلامی، قابلیت‌های پیشرفته‌ای برای محیط‌های پیچیده تولید ارائه می‌دهند. درک و پایش اسکیم‌ها سازماندهی، امنیت و عملکرد پایگاه داده PostgreSQL را بهبود می‌بخشد.

پرسش‌های متداول

انحراف اسکیمای PostgreSQL چیست و چرا مسئله حیاتی است؟
انحراف اسکیمای پایگاه داده به تغییرات ناخواسته یا مستندسازی نشده در ساختار پایگاه داده اشاره دارد، مانند ستون‌های اضافه، حذف یا تغییر یافته که از تعریف اسکیمای مورد انتظار منحرف می‌شوند. این موضوع ۷۸٪ از اختلالات را در محیط‌های PostgreSQL ایجاد می‌کند و اغلب باعث خطا در برنامه‌ها می‌شود.

چگونه می‌توانم تمام اسکیم‌ها را در PostgreSQL لیست کرده و ویژگی‌های آن‌ها را درک کنم؟
PostgreSQL روش‌های مختلفی برای لیست کردن اسکیم‌ها ارائه می‌دهد، از جمله نماهای استاندارد SQL (information_schema.schemata) و کاتالوگ‌های سیستم داخلی (pg_catalog.pg_namespace). این دستورات نام، مجوز و مصرف دیسک اسکیم‌ها را نشان می‌دهند و می‌توان آن‌ها را با JOIN برای مشاهده داده‌های کنترل دسترسی یا اندازه‌ها ترکیب کرد.

چه ابزارهایی انحراف اسکیمای پایگاه داده را شناسایی و از ناسازگاری‌ها جلوگیری می‌کنند؟
ابزارهای مدرن تشخیص انحراف اسکیمای پایگاه داده، مقایسه مداوم اسکیمای واقعی با طراحی ذخیره شده در کنترل نسخه را انجام می‌دهند. ابزارهایی مانند Flyway و StreamSets می‌توانند استقرارها را هنگام تشخیص اختلاف مسدود کرده یا دستورات DDL اصلاحی خودکار تولید کنند.

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

 

قبلیتفاوت‌های مهم Oracle و SQL Server در چه زمینه‌هایی است؟
بعدیشاردینگ در پایگاه داده (Sharding in Database) چیست؟

دیدگاهتان را بنویسید

سبد خرید
علاقه‌مندی‌ها
مشاهدات اخیر
دسته بندی ها