درس بعدی

معرفی PSQL‌- ابزار اصلی خط فرمان پستگرس 🎥

دوره آموزشی راهبری و مدیریت پستگرس – DBA پستگرس از صفر: آشنایی اولیه، نصب و ابزارها معرفی PSQL‌- ابزار اصلی خط فرمان پستگرس 🎥

PSQL رابط خط فرمان قدرتمند PostgreSQL است که امکانات زیر را در خط فرمان در اختیار شما می‌گذارد:

اتصال به پایگاه‌های داده PostgreSQL
اجرای پرس و جوهای SQL و دستورات
مدیریت اشیاء پایگاه داده (جداول، ایندکس‌ها، نماها)
انجام وظایف اداری
اجرای اسکریپت‌ها و عملیات دسته‌ای
نظارت بر عملکرد و آمار پایگاه داده

PSQL هم حالت تعاملی و هم غیرتعاملی ارائه می‌دهد و برای توسعه، مدیریت و اتوماسیون کاملاً مناسب است.

🚀 راهنمای نصب

Ubuntu/Debian (سیستم‌های مبتنی بر apt)

گزینه ۱: نصب سرور کامل PostgreSQL

# به‌روزرسانی لیست بسته‌ها
sudo apt update

# افزودن مخزن رسمی PostgreSQL
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# وارد کردن کلید مخزن
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# به‌روزرسانی و نصب
sudo apt update
sudo apt install postgresql-18 postgresql-contrib-18

گزینه ۲: نصب فقط ابزارهای کلاینت (توصیه شده برای اتصال به سرورهای راه دور)

# به‌روزرسانی لیست بسته‌ها
sudo apt update

# افزودن مخزن رسمی PostgreSQL
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# وارد کردن کلید مخزن
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# به‌روزرسانی و نصب فقط ابزارهای کلاینت
sudo apt update
sudo apt install postgresql-client-18

macOS (Homebrew)

# نصب کلاینت PostgreSQL
brew install postgresql@18

# یا نصب libpq (گزینه سبک‌تر فقط کلاینت)
brew install libpq

Windows

دانلود برنامه نصب کننده اصلی پستگرس از: https://www.postgresql.org/download/windows/
اجرای نصب‌کننده و انتخاب “Command Line Tools” در حین نصب
افزودن به PATH: پوشه bin از محل نصب پستگرس که آدرسی مشابه با C:\Program Files\PostgreSQL\18\bin\ خواهد داشت را به متغیر محیطی PATH ویندوز اضافه کنید.

🔗 پارامترهای اتصال و بهترین روش‌ها

ساختار پایه اتصال

psql [connection-options] [database-name]

پارامترهای ضروری اتصال

پارامتر	کوتاه	توضیح	مثال
`--host`	`-h`	نام میزبان/آدرس IP سرور	`-h localhost`
`--port`	`-p`	پورت سرور	`-p 5432`
`--username`	`-U`	نام کاربری پایگاه داده	`-U postgres`
`--dbname`	`-d`	نام پایگاه داده	`-d mydb`
`--password`	`-W`	اجبار نمایش درخواست رمز عبور	`-W`

مثال‌های اتصال

# اتصال محلی (تنظیمات پیش‌فرض)
psql -U postgres -d postgres

# اتصال راه دور با پارامترهای صریح
psql -h db.example.com -p 5432 -U myuser -d mydatabase

# اتصال با درخواست رمز عبور
psql -h localhost -U postgres -d postgres -W

# استفاده از رشته اتصال (فرمت URI)
psql "postgresql://username:password@host:port/database"

# اتصال از طریق سوکت Unix (فقط محلی)
psql -U postgres -d postgres -h /var/run/postgresql

متغیرهای محیطی

این متغیرها را برای اتصالات پیش‌فرض تنظیم کنید:

export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=mypassword
export PGDATABASE=mydb

بهترین روش‌های اتصال

هرگز رمزهای عبور را در اسکریپت‌ها هاردکد نکنید – از فایل .pgpass یا متغیرهای محیطی استفاده کنید
از connection pooling برای برنامه‌ها استفاده کنید (PgBouncer, Pgpool-II)
SSL/TLS را برای اتصالات راه دور فعال کنید: psql "sslmode=require host=..."
timeout های مناسب تنظیم کنید: --connect-timeout=10
از .psqlrc برای دستورات راه‌اندازی سفارشی استفاده کنید

تنظیم فایل .pgpass

فایل ~/.pgpass را با مجوزهای محدود ایجاد کنید:

# فرمت: hostname:port:database:username:password
echo "localhost:5432:mydb:myuser:mypassword" > ~/.pgpass
chmod 0600 ~/.pgpass

📚 مرجع سریع دستورات

مدیریت اتصال و نشست

دستور	توضیح	مثال
`\q`	خروج از PSQL	`\q`
`\c dbname`	اتصال به پایگاه داده	`\c mydb`
`\conninfo`	نمایش اطلاعات اتصال	`\conninfo`
`\password`	تغییر رمز عبور	`\password username`

اطلاعات پایگاه داده و Schema

دستور	توضیح	مثال
`\l`	فهرست پایگاه‌های داده	`\l`
`\l+`	فهرست پایگاه‌های داده با جزئیات	`\l+`
`\dn`	فهرست شمای‌ها	`\dn`
`\dn+`	فهرست شمای‌ها با جزئیات	`\dn+`
`\dt`	فهرست جداول	`\dt`
`\dt+`	فهرست جداول با جزئیات	`\dt+`
`\dv`	فهرست نماها	`\dv`
`\df`	فهرست توابع	`\df`
`\di`	فهرست ایندکس‌ها	`\di`
`\di+`	فهرست ایندکس‌ها با جزئیات	`\di+`

اطلاعات جدول و ستون

دستور	توضیح	مثال
`\d table_name`	توصیف جدول	`\d users`
`\d+ table_name`	توصیف جدول (جزئی)	`\d+ users`
`\x`	تغییر خروجی گسترش‌یافته	`\x`

اجرای پرس و جو و خروجی

دستور	توضیح	مثال
`\e`	ویرایش پرس و جو در ویرایشگر	`\e`
`\i filename`	اجرای SQL از فایل	`\i script.sql`
`\o filename`	ذخیره خروجی در فایل	`\o results.txt`
`\H`	تغییر خروجی HTML	`\H`
`\T`	تنظیم تگ جدول HTML	`\T 'class="table"'`
`\pset format`	تنظیم فرمت خروجی	`\pset format csv`

عملکرد و نظارت

دستور	توضیح	مثال
`\timing`	تغییر نمایش زمان	`\timing`
`\watch delay`	تکرار پرس و جو هر N ثانیه	`\watch 5`

تاریخچه و متغیرها

دستور	توضیح	مثال
`\s`	نمایش تاریخچه دستورات	`\s`
`\set variable value`	تنظیم متغیر PSQL	`\set PROMPT1 '%n@%M:%> %x%# '`
`\unset variable`	حذف متغیر PSQL	`\unset PROMPT1`

ویژگی‌های پیشرفته

دستور	توضیح	مثال
`\copy`	کپی داده‌ها از/به فایل‌ها	`\copy table_name TO 'file.csv' CSV`
`\g`	اجرای پرس و جو (جایگزین)	`SELECT * FROM users \g`
`\gexec`	اجرای نتیجه به عنوان SQL	`SELECT 'SELECT * FROM ' \|\| tablename FROM pg_tables \gexec`
`\setenv`	تنظیم متغیر محیطی	`\setenv PAGER less`

سفارشی‌سازی PSQL

فایل ~/.psqlrc را برای دستورات راه‌اندازی ایجاد کنید:

# فعال‌سازی زمان
\timing

# تنظیم پرامت برای نمایش پایگاه داده و کاربر
\set PROMPT1 '%n@%M:%> %x%# '

# تنظیم مسیر جستجو
SET search_path TO public, myschema;

# دستورات سفارشی
\set show_tables 'SELECT tablename FROM pg_tables WHERE schemaname = \'public\';'

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

# اجرای فایل SQL
psql -U postgres -d mydb -f script.sql

# اجرای چندین فایل
psql -U postgres -d mydb -f schema.sql -f data.sql

# اجرای پرس و جو و ذخیره نتایج
psql -U postgres -d mydb -c "SELECT * FROM users" -o users.txt

# پشتیبان‌گیری از پایگاه داده
pg_dump -U postgres mydb > backup.sql

# بازیابی پایگاه داده
psql -U postgres mydb < backup.sql

📎 پیکربندی pg_hba.conf برای برقرای امکان دسترسی به پستگرس

فایل pg_hba.conf احراز هویت کلاینت را کنترل می‌کند. بنابراین اگر با psql امکان اتصال به یک سرور راه دور را ندارید، باید تنظیمات دسترسی آن سرور را از طریق این فایل طوری تغییر دهید که آی‌پی شما به عنوان یک آی پی مجاز برای اتصال در نظر گرفته شود. این فایل، معمولا در مکان‌های زیر قرار دارد:

Linux/macOS: /etc/postgresql/18/main/pg_hba.conf (یا /var/lib/pgsql/18/data/pg_hba.conf)
Windows: C:\Program Files\PostgreSQL\18\data\pg_hba.conf

درک ساختار pg_hba.conf

هر خط از فرمت زیر پیروی می‌کند: TYPE DATABASE USER ADDRESS METHOD [OPTIONS]

روش‌های رایج احراز هویت

روش	توضیح	مورد استفاده
`trust`	بدون نیاز به رمز عبور	فقط توسعه محلی
`peer`	استفاده از نام کاربری OS	اتصالات محلی Linux
`md5`	هش رمز عبور MD5	احراز هویت استاندارد رمز عبور
`scram-sha-256`	احراز هویت SCRAM	احراز هویت امن مدرن
`cert`	گواهی‌های کلاینت	محیط‌های امنیت بالا

نمونه پیکربندی‌های pg_hba.conf

توسعه محلی (امنیت کمتر)

# اجازه اتصالات محلی بدون رمز عبور
local   all             postgres                                trust
local   all             all                                     trust

# اجازه اتصالات راه دور با رمز عبور
host    all             all             0.0.0.0/0               scram-sha-256

پیکربندی حرفه‌ای تر

# اتصالات محلی
local   all             postgres                                peer
local   all             all                                     peer

# اتصالات راه دور از شبکه‌های خاص
host    all             all             192.168.1.0/24          scram-sha-256
host    all             all             10.0.0.0/8              scram-sha-256

# رد تمام اتصالات دیگر
host    all             all             0.0.0.0/0               reject

دسترسی مخصوص برنامه

# اجازه کاربر خاص از وب سرور
host    myapp_db        myapp_user      192.168.1.100/32        scram-sha-256

# دسترسی کاربر فقط خواندنی
host    mydb            readonly_user   192.168.1.0/24          scram-sha-256

مراحل پیکربندی دسترسی

ویرایش pg_hba.conf:

   sudo nano /etc/postgresql/18/main/pg_hba.conf

افزودن قانون خود (مثال برای دسترسی راه دور):

   host    all             myuser          0.0.0.0/0               md5

به‌روزرسانی postgresql.conf برای اتصالات راه دور:

   sudo nano /etc/postgresql/18/main/postgresql.conf
   # تغییر این خط:
   listen_addresses = '*'

راه‌اندازی مجدد PostgreSQL:

   sudo systemctl restart postgresql
   # یا
   sudo service postgresql restart

تست اتصال:

   psql -h your-server-ip -U myuser -d mydb -W

رفع اشکال مشکلات دسترسی

“psql: FATAL: no pg_hba.conf entry for host”

افزودن آی پی میزبان مناسب به pg_hba.conf
بررسی تنظیمات شبکه/فایروال

“psql: FATAL: password authentication failed”

تأیید نام کاربری/رمز عبور
بررسی روش احراز هویت در pg_hba.conf
اطمینان از تنظیم رمز عبور: ALTER USER username PASSWORD 'newpassword';

“psql: could not connect to server”

تأیید اجرای PostgreSQL: sudo systemctl status postgresql
بررسی تنظیمات پورت و میزبان
تست با telnet host port

محتوای ویدئویی

در فیلم آموزشی زیر، ابزار کاربردی psql را به صورت مختصر مرور کرده‌ایم و در ادامه دوره، به صورت حرفه‌ای تر با آن آشنا خواهیم شد.

نکته : اگر احیانا فیلم آموزشی در بخش زیر نمایش داده نمی شود، اطمینان حاصل کنید که با آی پی ایران در حال مشاهده آن هستید.

موضوع قبلی

بازگشت به درس